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.

Are You Going to the Noetix View Customization Certification course? Review How to Use SQL*Plus

The Noetix View Customization Certification course uses SQL*Plus in lieu of the more user friendly GUI querying tools.

Are you used to nice GUI database querying tools? While there are non-database “tricks” to obtain metadata about the Noetix schema objects (e.g. examine various spool files), my personal feeling is to just be comfortable with sql statements that expose information from the data dictionary. In all honesty, some of these GUI tools encourage mental lethargy!

Suggestions:

Set-up SQL*Plus in way that enables you to work efficiently. 

-Use a login.sql script to provide some formatting

Here is the contents of my login.sql file:

--******login.sql start******************************

define _editor='C:\Program Files\Vim\vim73\gvim.exe'

set serveroutput on size 1000000
set trimspool on
set long 5000

set linesize 100
set pagesize 9999

column plan_plus_exp format a80
set termout off
SET SQLPROMPT '&_user@&_CONNECT_IDENTIFIER> '
set termout

--******login.sql end********************************

I personally like command mode in lieu of the GUI windows version of SQL*Plus.

With the editor set for Vim (or whatever text editor you want to use).

When you want to edit the SQL*Plus buffer, you would just type, edit

After your editing is complete, save and close your editor.

Next, type "r" or "/" to execute the contents of the buffer.  This is switch from the nice GUI tools, but after working with this for a little while, one can probably have a similar efficiency to a nice GUI tool.

I personally do all my text editing in Vim (or Notepad++).

************************************

Sample Data Dictionary Related Queries (here are more):

Use the dbms_metadata package if they are using the Oracle 9i Client (or later):

select

dbms_metadata.get_ddl('TABLE','N_VIEW_COLUMNS','NOETIX_SYS')

from

dual;

or for a view:

select

dbms_metadata.get_ddl('VIEW','INVG_ITEMS','NOETIX_SYS')

from
dual;

Otherwise use the DESC command to access a description of the columns of a view or table.

e.g.

DESC INVG_ITEMS

For the DDL associated with a view, this works well:

select

text

from
Dba_views
where
view_name = 'INVG_ITEMS'

Here is a query against constraints (a common question when executing DML against the template and non-template tables):  

select

*

from

dba_constraints

where 1=1

and owner = 'NOETIX_SYS'

and table_name like :table_name

;

/* types of constraints
C (check constraint on a table)

P (primary key)

U (unique key)

R (referential integrity)

V (with check option, on a view)

O (with read only, on a view)

*/

That is all.

Oracle Applications 12.1.3 and Noetix 6.0.1

We went live with Oracle Applications 12.1.3 using the 6.0.1 Noetix Views during the 4th of July holiday weekend. The direction I had received from Noetix (per the NVA documentation and talks and emails with various Noetix staff) was to do the following:

- Regenerate NoetixViews (stages 2, 3 &4)

- EUL Generation (Extract Views)

- Update Security

Just to be on the safe side (in case I was missing something), I submitted a ticket to Noetix to confirm these tasks. They corroborated the previous direction I had received.

After I performed these steps, I proceeded, with the usual post re-generation steps which I have posted here, Post Regeneration Steps with 6.01 Noetix Views.

Since our implementation, we have noticed that some of our planning (ASCP module) reports running somewhat slower.  Our DBA has indicated that there are a handful of indexes that are being put into an "unusable" state by partition maintenance.  While this is a problem, we have not identified any issue with our Noetix Views.

That is all for now.