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.