Wednesday, July 20, 2011

Are You Going to the Noetix View Customization Certification Class? Know about Template Tables

Template tables (tables in Noetix schema ending with the word, ‘TEMPLATES’) are like an object oriented programming language class (the non-template tables are an intermediate step).  The Noetix Views are the result of the Noetix View Administrator "instantiating" the template tables based on:

-Your Noetix Views purchased

-Your implementation of Oracle Applications

-Your version of Oracle Applications.

We will look at the view regeneration process as it pertains to the view label, 'INV_Items'.

 In stage 4 of the regeneration process, we see the following activities being completed (sort contents of the Noetix Views install home folder by creation dates):

-ycrtmpl.sql creates the n_%_templates tables.


-dat%.log files show the data loaded into the template tables (including 'INV_Items')


-wnoetxu2.sql is invoked (modifications to template tables, n_%_templates)

   Example text of what could be in an xu2 script for this view:

    INSERT INTO n_view_table_template
(view_label
,query_position
,table_alias
,from_clause_position
,application_label
,table_name
,product_version
,base_table_flag
,subquery_flag
,gen_search_by_col_flag
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('INV_Items' -- view_label
,1 -- query_position
,'CT' -- table_alias
,29 -- from_clause_position
,'APPS' -- application_label
,'MTL_DESCR_ELEMENT_VALUES' -- table_name
,'%' -- product_version
,'N' -- base_table_flag
,'N' -- subquery_flag
,'N' -- gen_search_by_col_flag
,'FLINSTONEF' -- created_by
,SYSDATE -- creation_date
,'FLINSTONEF' -- last_updated_by
,SYSDATE) -- last_update_date
;
COMMIT;


INSERT INTO n_view_where_templates
(view_label
,query_position
,where_clause_position
,where_clause
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('INV_Items' -- view_label
,1 -- query_position
,405 -- where_clause_position
,'AND CT.INVENTORY_ITEM_ID(+) = ITEM.INVENTORY_ITEM_ID' -- where_clause
,'' -- profile_option
,'%' -- product_version
,'FLINSTONEF' -- created_by
,SYSDATE -- creation_date
,'FLINSTONEF' -- last_updated_by
,SYSDATE) -- last_update_date
;

INSERT INTO n_view_where_templates
(view_label
,query_position
,where_clause_position
,where_clause
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('INV_Items' -- view_label
,1 -- query_position
,406 -- where_clause_position
,'AND CT.ELEMENT_NAME(+) = ''Coating''' -- where_clause
,'' -- profile_option
,'%' -- product_version
,'FLINSTONEF' -- created_by
,SYSDATE -- creation_date
,'FLINSTONEF' -- last_updated_by
,SYSDATE) -- last_update_date
;
COMMIT;

INSERT INTO n_view_column_templates
(view_label
,query_position
,column_label
,table_alias
,column_expression
,column_position
,column_type
,description
,group_by_flag
,gen_search_by_col_flag
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('INV_Items' -- view_label
,1 -- query_position
,'Coating' -- column_label
,'CT' -- table_alias
,'ELEMENT_VALUE' -- column_expression
,100 -- column_position
,'COL' -- column_type
,'Coating --(XXACME Column)' -- description
,'N' -- group_by_flag
,'N' -- gen_search_by_col_flag
,'' -- profile_option
,'%' -- product_version
,'FLINSTONEF' -- created_by
,SYSDATE -- creation_date
,'FLINSTONEF' -- last_updated_by
,SYSDATE) -- last_update_date
;

COMMIT;

-Noetix role prefixes are edited and query users are set-up:


..(more will be documented)



Knowing how this “instantiation” is done (sequence) is important in choosing which scripts to use.
Think of this fact as your primary node of information about Noetix and keep adding child nodes around this.


No comments:

Post a Comment