Wednesday, June 16, 2010

Changing the Granularity of a View

Can you change the granularity of a view when you are not adding a new table to it? The most obvious scenario is when there is a Group by Flag associated with the query block which is pertinent to your version of Oracle Apps.
For instance, suppose an end user has requested to expose all of the descriptive flexfields associated with the table,” MTL_CUSTOMER_ITEM_XREFS”, associated with the view, “INV_Customer_Items”. It is important to run the get_data_tmpl.sql script for this view and see if the view is using a group by clause.
Suppose you do not have access to the get_data_tmpl.sql? Keeping in mind your version of Oracle Applications, run the following query:

SELECT
*
FROM
n_view_query_templates nqt
WHERE 1=1
and nqt.view_label = ' INV_Customer_Items’;
Since I am working with Oracle Apps 12.06 and since this query has the wildcard, ‘*’, returned for the product_version column, I am only concerned with query block 1. Also, the group by flag is ‘N’.
Consequently, for this example, the answer is, “No.” Satisfying a requirement to add the descriptive flexfields will not change the granularity.
For the sake of it, here is my DML to add all the descriptive flexfields for the “MTL_CUSTOMER_ITEM_XREFS” table to this view:

INSERT INTO n_view_column_templates
(view_label
,query_position
,column_label
,table_alias
,column_expression
,column_position
,column_type
,description
,group_by_flag
,profile_option
,product_version
,ref_application_label
,ref_table_name
,ref_lookup_column_name
,ref_description_column_name
,ref_lookup_type
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('INV_Customer_Items' -- view_label
,1 -- query_position
,'XREFS' -- column_label
,'XREFS' -- table_alias
,'All_FF' -- column_expression
,45 -- column_position
,'ATTR' -- column_type
,'All the table flexfields' -- description
,NULL -- group_by_flag
,NULL -- profile_option
,'%' -- product_version
,NULL -- ref_application_label
,NULL -- ref_table_name
,NULL -- ref_lookup_column_name
,NULL -- ref_description_column_name
,NULL -- ref_lookup_type
,'baconp' -- created_by
,SYSDATE -- creation_date
,'baconp' -- last_updated_by
,SYSDATE) -- last_update_date
;

COMMIT;

No comments:

Post a Comment