Knols about Noetix Views

Today I stumbled upon a number of Noetix View Knols written by Andy Pellew.  Just follow the link.  I enjoyed looking over his entries.

Common XU2 Script Error: ORA-00001

Have you written a hook script associated with adding a column to a Noetix view?  A common error I have seen is the ORA-00001 error.  I received this error most recently when I was adding some columns to a view with a command like this:

SQL> INSERT INTO n_view_column_templates

  2  (view_label

  3  , query_position

  4  , column_label

  5  , table_alias

  6  , column_expression

  7  , column_position

  8  , column_type

  9  , description

 10  , group_by_flag

 11  , gen_search_by_col_flag

 12  , profile_option

 13  , product_version

 14  , created_by

 15  , creation_date

 16  , last_updated_by

 17  , last_update_date

 18  )

 19  VALUES

 20  ('GME_MES_Melt_Batch_Xfer'    --view_label

 21  ,1     --query_position  number

 22  ,'Batch_Plan_Complete_Date'     --column_label

 23  ,'MELT'     --table_alias

 24  ,'PLAN_CMPLT_DATE'     --column_expression

 25  ,24    --column_position number

 26  ,'COL'    --column_type

 27  ,'The planned complete date associated a melt batch in '||

 28  'the transfer queue.  --XXACME'     -- description

 29  ,'N'     --group_by_flag

 30  ,'N'     --gen_search_by_col_flag

 31  , NULL  --profile_option

 32  ,'*'     --product_version

 33  ,'flinstonef'     -- created_by

 34  , SYSDATE                --creation_date

 35  ,'flinstonef'     --last_updated_by

 36  , SYSDATE                --last_update_date

 37  );

ORA-00001: unique constraint (NOETIX_SYS_TEST.N_VIEW_COLUMN_TEMPLATES_U1)

Violated

With this particular error, the constraint associated with this error are identified, so I investigate by querying this constraint name against the dba_constraints view:

  1  select
  2  constraint_name,
  3  constraint_type,
  4  table_name,
  5  index_owner,
  6  index_name
  7  from
  8  dba_constraints
  9  where 1=1
 10  and owner= 'NOETIX_SYS'
 11  and constraint_name = 'N_VIEW_COLUMN_TEMPLATES_U1'
 12* and constraint_type = 'U'

NOETIX_SYS@erptst> /

CONSTRAINT_NAME                              C TABLE_NAME                                   INDEX_OWNER
---------------------------                            --------------------                                  ------------------
N_VIEW_COLUMN_TEMPLATES_U1    U N_VIEW_COLUMN_TEMPLATES  NOETIX_SYS


INDEX_NAME
--------------------------
N_VIEW_COLUMN_TEMPLATES_U1

Sure enough, I have violated an unique constraint associated with the index, N_VIEW_COLUMN_TEMPLATES_U1, which is associated with the template table, n_view_column_templates.

To figure out the columns associated with this unique constraint, I query dba_ind_columns as follows:

This informs me that there needs to be a unique combination of values for the columns: view_label, query_position, column_label, product_version and profile_option.

Upon review of this SQL command where the error was thrown, I notice that I repeated the same insertion command twice.  After removing the erroneous command, no errors are thrown.

               

Role Suppression to Speed up Regeneration in a Development Environment

Role suppression suppresses views being generated associated with a role (or roles).

A peer of mine suggested that suppressing all roles not having any development in them would be helpful in a development environment.  Specifically, only have the role which has a xu2 or xu5 script which you are developing exposed.  The idea behind this is that the regeneration time should be significantly shortened.

Presently my average generation time is approximately 30 minutes.  When I use this role suppression approach, it goes down to about 10 minutes.

While this is a documented feature of Noetix, it requires some thought and consideration. 

Before initiating a task like this, it is important to “design” this suppression plan.

What do I mean by this? One should design how this will be undertaken.

Here is an example:

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

Scenario:

I specifically want to develop a custom freight rate view using an xu2 script through cloning the ARX0_Customer_Addresses view.

To do this, I know I need to suppress all roles except ARX0.  Before even jumping into the “tupdprfx.sql” file and making modifications (standard, documented method to suppress roles), it is important to consider any other changes that might be needed before doing any “development”.

Plan:

-Modify “tupdprfx.sql” file by editing the invocation to noetix_prefix_pkg.update_role_status procedure by changing the i_user_enabled_flag from 'Y' to 'N' as follows (for all roles being supressed):

Before

  noetix_prefix_pkg.update_role_status(
   ------------------------------------------------------------------------------
   i_user_enabled_flag      => 'Y',  /* +++ EDIT THIS LINE ONLY (Y or N) +++ */
   ------------------------------------------------------------------------------
   i_application_label      => 'AP',
   i_role_label             => 'PAYABLES',
   i_org_id                 => 84,
   i_instance_type          => 'S');
  --
END;

After

  noetix_prefix_pkg.update_role_status(
   ------------------------------------------------------------------------------
   i_user_enabled_flag      => 'N',  /* +++ EDIT THIS LINE ONLY (Y or N) +++ */
   ------------------------------------------------------------------------------
   i_application_label      => 'AP',
   i_role_label             => 'PAYABLES',
   i_org_id                 => 84,
   i_instance_type          => 'S');
  --
END;

-Through examination (and trial error), I know that most non-template tables will not have entries associated with views associated with suppressed roles.  This means views associated with suppressed roles should not have any xu5 invocations (they will throw foreign key constraint errors).

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

Execution: 

Edit “tupdprfx.sql” file and perform this modification (using Replace All):

Next, I look for the ARX0 entry, and makes sure this entry actually is “turned on”:

Remove all xu5 invocations to views associated with roles that are suppressed.

This plan worked.

********

Suppose I do not plan my execution of this suppression (or act in ignorance) and have xu5 invocations to views associated with suppressed roles?

Specifically, one can anticipate the ORA-02291 error being thrown.  Here is an example:

ARCM_Transaction_Line_Dtls_xu5.lst:ORA-02291: integrity constraint (NOETIX_SYS_TEST.N_VIEW_COLUMNS_FK1) violated

I check this foreign key constraint:

SELECT

*

FROM

DBA_CONSTRAINTS

WHERE 1=1

AND OWNER = 'NOETIX_SYS'

AND CONSTRAINT_TYPE = 'R' /* foreign key*/

AND CONSTRAINT_NAME = 'N_VIEW_COLUMNS_FK1';

As you can see, this is a foreign key constraint (‘constraint_type = ‘R’).  The dba constraints view actually has a r_constraint_name column (the primary key of the table for whom the constraint is a foreign key) and this is ‘N_VIEW_QUERIES_PK’.

I look this up, with this query:

select

*

from

DBA_CONSTRAINTS

WHERE 1=1

AND OWNER = 'NOETIX_SYS'

AND

CONSTRAINT_NAME = 'N_VIEW_QUERIES_PK'

And

Constraint_type = ‘P’ /*primary key */;

So I know that the corresponding record in the non-template table, ‘N_VIEW_QUERIES’, does not exist.

This tells me that my suppression script worked properly and the xu5 script has an invocation to a view whose role was suppressed.

A Framework for Managing Versioning and Processing of Noetix Hook Scripts through the Software Development Lifecycle

During the installation of the Noetix View Administrator, a custom development folder is created in the Master folder.   Here is a screenshot of how this file structure looks:

Noetix intends that the Noetix Administrator will use this as a repository for all custom hook scripts (see the 6.01 Noetix View Administrators User Guide, p. 46).  When starting the Noetix View Administrator and running stage 1, these hook scripts are transferred from this \\Master\Custom file to the SQL script home for that instance.

Development and Unit Testing Stages

In the \\Master\Custom folder, I maintain a development folder that only has scripts and any other data definition language associated with development that has not completed user acceptance testing.


When doing a regeneration using these scripts, I copy all scripts from the development folder and move them to the SQL script home associated with the development environment.

User Acceptance Testing Stage

When the new development is ready for user acceptance testing, I place the new scripts into the test erp SQL home.

After the new scripts have passed user acceptance testing, I move the new scripts to the \\Master\Custom folder and PVCS. 

Promote to Production

When I create the change management documentation, I reference the PVCS file locations.




Having a well defined process for maintaining and keeping track of versioning of hook scripts is very important in managing a Noetix environment.

Multiple Site Use Statuses Show-up in View, AR_CUSTOMER_ADDRESSES

Our user community noticed that the Noetix View, ARX0_CUSTOMER_ADDRESSES, shows all site use statuses, yet the site use status is not a column in this view.  This results in the view potentially having multiple rows with the same site use information.

Specifically, this view has the table, HZ_CUST_SITE_USES_ALL (alias of SITE), in its DDL. This table has a column titled, STATUS, which shows a value of ‘A’ if it is active and ‘I’ if it is inactive, yet there is no site_use_status column in this view.

To illistrate this multiple statuses issue, if you run the query below, there will be a non-null result set (at least with our implementation of Oracle Apps 12.1.3 and the 6.01 Noetix Views):



SELECT
SITE_NUMBER,
CUSTOMER_LOCATION,
CONTACT_LAST_NAME,
CUSTOMER,
BUSINESS_PURPOSE,
COUNT(1)

FROM
ARX0_CUSTOMER_ADDRESSES

GROUP BY SITE_NUMBER,
CUSTOMER_LOCATION,
CONTACT_LAST_NAME,
CUSTOMER,
BUSINESS_PURPOSE
HAVING COUNT(1) > 1;

I submitted a ticket to Noetix suggesting for them to modify this view so that it exposes the site use status to let the report end user decide which site use statuses they want to see.

If you notice this behavior, you might want to make this modification. Our Sales and Accounting staff were performing a customer master “clean-up” project and noticed this aberrant behavior.

Adding this column

I perform a query to see if Noetix exposes this column with other Noetix Views as follows:



SELECT
VCT.*

FROM
NOETIX_SYS.N_VIEW_TABLE_TEMPLATES VTT,
NOETIX_SYS.N_VIEW_COLUMN_TEMPLATES VCT

WHERE 1=1
AND VTT.QUERY_POSITION = VCT.QUERY_POSITION
AND VTT.VIEW_LABEL = VCT.VIEW_LABEL
AND VTT.TABLE_ALIAS = VCT.TABLE_ALIAS
AND TABLE_NAME ='HZ_CUST_SITE_USES_ALL'
AND COLUMN_LABEL LIKE '%Status';

The view, AR_Correspondences, does do this. This is a column of type, “LOOK”. You can see how Noetix adds this column type with this query:

SELECT
*
FROM
NOETIX_SYS.N_VIEW_COLUMN_TEMPLATES

WHERE
1=1

AND TABLE_ALIAS LIKE 'SITE'

AND VIEW_LABLE = 'AR_Customer_Addresses';

I add this column as follows:




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
('AR_Customer_Addresses' -- view_label
,2 -- query_position
,'Site_Use_Status' -- column_label
,'SITE' -- table_alias
,'STATUS' -- column_expression
,47 -- column_position
,'LOOK' -- column_type
,'Site use status. --XXACME' -- description
,NULL -- group_by_flag
,NULL -- profile_option
,'%' -- product_version
,'NOETIX' -- ref_application_label
,'N_AR_LOOKUPS_VL' -- ref_table_name
,NULL -- ref_lookup_column_name
,NULL -- ref_description_column_name
,'CODE_STATUS' -- ref_lookup_type
,'FLINSTONEF' -- created_by
,SYSDATE -- creation_date

,'FLINTSTONEF' -- last_updated_by

,SYSDATE) -- last_update_date
;
COMMIT;

That is all.

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.