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.

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.

Custom LOVs in Noetix

We have implemented Noetix with Discoverer, consequently we are at the mercy of what Noetix provides for its LOVs out of the box. In general, for non-Noetix Platform reporting tools, this is how Noetix generates LOVs:

1. Columns in a view that are indices have duplicate columns and classes created with this naming convention, ‘A$’ || column_name.

2. In the role associated with the view, a LOV object (e.g. in Discoverer, a class) is created by using the table associated with the indexed column in the Noetix View.

The problem with this that it is not always very elegant.  For example the class, A$Customer_Name (HZ_PARTIES), which creates the LOVs for a customer's name uses party_name from the table, AR.HZ_PARTIES. Specifically, if one looks at the party_name column, the result set is more than just a list of customers' names.  Fortunately, Noetix does have its own LOV Noetix Views generated during view generation including the view, ARX0_LOV_CUSTOMERS, but classes in Discoverer (objects used for LOVs in Discoverer) are not created. 

One of these views, ARX0_LOV_CUSTOMERS, appears to meet our needs quite well.  Consequently, I use this view for my custom class, XXACME_CUSTOMERS.  More than likely, for most of these LOV scenarios, Noetix has a LOV view which should meet your needs and probably is correctly shared with the appropriate Noetix role (e.g. the ARX0_LOV_CUSTOMERS view is shared with my ONTX0_ORDER_ENTRY role).

Unfortunately, after  I have created these custom classes (or LOVs), the mapping between the item (Noetix View column) and the custom class are blown away every time I use the EUL Generator.

I posed this problem to Noetix and they provided me with a script and a package to preserve these mappings associated with these custom classes during an EUL Generation.

The package they provided is titled, "n_eul_gen_item_class_pkg".  The script they provide is titled, n_crscript.sql. 

The methodology for using this script and package is the following:

1. Pre-EUL generation, run the script, n_crscript.sql, in Noetix System account, which creates a spool file titled, n_updlinks.sql.
2. Run the EUL Generator.
3. Run the n_updlinks.sql script in Noetix System account to recreate link between custom class and Noetix Views items.

Consequently, I run the script before I run the EUL Generator to keep track of these mappings between the columns and the custom classes (which are manually created).  It then spools this association with an invocation to the package, n_eul_gen_item_class_pkg.

Here is an example of one spooled row:
execute n_eul_gen_item_class_pkg.update_column_item_class('ONTX0_Order_Freight_Charges','A$Customer_Name','XXACME_CUSTOMERS');

While I was waiting for a regeneration to complete last night, it occurred to me that I do not need to ever manually associate my custom classes with Noetix View columns. Specifically, I just need to leverage this n_eul_gen_item_class_pkg package to do the heavy lifting.

Specifically, I do the following:

1. Create a custom class in a given Noetix role (Discoverer Business Area). It is important to know that Noetix does create a number of views matching the pattern ‘%Lov% ‘ which are used in Noetix Platform (yet, one can easily create a custom class from them). None of these views are used for non-Noetix Platform reporting tools.

2. I create a spool file with this script:

select
'execute n_eul_gen_item_class_pkg.update_column_item_class('''vc.view_name''','''vc.column_name
''',''xxacme_customers'');'
from
noetix_sys.n_roles r,
noetix_sys.n_role_views rv,
noetix_sys.n_view_columns vc
where
r.role_name = 'ONTX0_ORDER_ENTRY'
and r.role_name = rv.role_name
and rv.view_name = vc.view_name
and vc.view_name not like '%Lov%'
and VC.VIEW_NAME not like '%Base'
and upper(column_name) in ('CUSTOMER_NAME', 'CUSTOMER','A$CUSTOMER_NAME');


Of course, replace my constants with bind variables and this script is then more broadly useable.


Consequently, when I spool, I obtain this:

execute n_eul_gen_item_class_pkg.update_column_item_class('ONTX0_Order_Freight_Charges','A$Customer_Name','xxacme_customers');

execute n_eul_gen_item_class_pkg.update_column_item_class('ONTX0_Order_Freight_Charges','A$Customer_Name','xxacme_customers');

(and many more records)…


Using this method enables me to create a mapping of my Discoverer class to a view column quickly without all of the manual labor.

Cloning a View in Noetix

I cannot claim that I came up with this idea/approach, but it is very similar to the method I described regarding creating a new view in Noetix. What do I mean by cloning a view? It means taking the DDL of an existing view and copying it (typically with the intent of modifying it enough to justify a new name).

Here is an example:

Noetix provided an INV_Alias_Transactions view, but it did not go down to the lot granularity level. It is/was a perfectly fine view, but my user community insists (provided a requirement) that dictated that they need a view which provides lot information.

This leads me to have to make a decision. Do I build a new view from scratch? Do I just make a clone of the existing view and add lot information. Per the Noetix View Customization Certification course, changing the cardinality of a view is a very bad idea for a lot of reasons.

Here are some of them: it affects the joins (how will the totals behave once the modification has occurred?), how will the quantities behave once the new column(s) have been added. Will the quantities in the view be incorrect or misleading after the change?

It goes without saying that this method of cloning views is not supported by Noetix.

Let us begin

Just like creating a new view from scratch, the sequence of DML statements needed to create a cloned view follow a sequence determined by the foreign key constraints set-up in the template tables.

First enter your N_VIEW_TEMPLATES DML statements.

Notice the following Foreign Key Constraints with this table. In the blog entry titled, "Step by Step Creating a New View", I go into more detail about examing these constraints.  When composing this statement, be mindful that the N_VIEW_TEMPLATES_FK1 requires that your application label exist. Also, if the profile option is not null, then N_VIEW_TEMPLATES_FK2 requires that the option be set-up in N_PROFILE_OPTION_TEMPLATES.

-- output to INV_Alias_Lot_Trnsctns_xu2.lst file

@utlspon INV_Alias_Lot_Trnsctns_xu2

-- -----------------------------------------------------------------------------
-- Clone INV_Alias_Transactions view template to INV_Alias_Lot_Trnsctns
-- -----------------------------------------------------------------------------

INSERT INTO n_view_templates
(view_label,
application_label,
description,
profile_option,
essay,
keywords,
product_version,
include_flag,
export_view,
security_code,
special_process_code,
sort_layer,
freeze_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
original_version,
current_version
)
(SELECT 'INV_Alias_Lot_Trnsctns' view_label,
application_label,
description,
profile_option,
essay,
keywords,
product_version,
include_flag,
export_view,
security_code,
special_process_code,
sort_layer,
freeze_flag,
'Flinstonef' created_by,
sysdate creation_date,
'Flinstonef' last_updated_by,
sysdate last_update_date,
original_version,
current_version
FROM n_view_templates
WHERE view_label = 'INV_Alias_Transactions'
);

COMMIT;

--customize essay and description
UPDATE n_view_templates
SET description = 'ACME Custom - Modified version '||
'of INV_Alias_Lot_Trnsctns'||
essay = 'Big essay on what this view does. '||
CHR(10) ||CHR(10)||
'This view differs from the INV_Alias_Transaction '||
'view in that it does...'
WHERE view_label = 'INV_Alias_Lot_Trnsctns'
;

COMMIT;


Now that a view template record exists, I can then add arguments to insert records to the N_ROLE_VIEW_TEMPLATES table. Why this table?

Records added to this table require that an N_VIEW_TEMPLATES record exist for the view we are creating.

-- -----------------------------------------------------------------------------------------

-- Include INV_Alias_Lot_Trnsctns in all Roles that currently contain INV_Alias_Transactions



-- -----------------------------------------------------------------------------------------

INSERT INTO n_role_view_templates
(role_label,
view_label,
product_version,
include_flag,
created_by,
creation_date,
last_updated_by,
last_update_date
)
(SELECT role_label,
'INV_Alias_Lot_Trnsctns' view_label,
product_version,
include_flag,
'Flinstonef' created_by,
sysdate creation_date,
'Flinstonef' last_updated_by,
sysdate last_update_date
FROM n_role_view_templates
WHERE view_label = 'INV_Alias_Transactions'
);

COMMIT;

Next, records need to be added to the N_VIEW_QUERY_TEMPLATES table.

If you look at the foreign key constraints on this table, you will see that the view you are adding has a record in the N_VIEW_TEMPLATES and a record (if not null) in the N_PROFILE_OPTION_TEMPLATES table.

Here is my insertion argument to this table:

INSERT INTO n_view_query_templates
(view_label,
query_position,
union_minus_intersection,
group_by_flag,
profile_option,
product_version,
include_flag,
view_comment,
created_by,
creation_date,
last_updated_by,
last_update_date
)
(SELECT 'INV_Alias_Lot_Trnsctns' view_label,
query_position,
union_minus_intersection,
group_by_flag,
profile_option,
product_version,
include_flag,
view_comment,
'Flinstonef' created_by,
sysdate creation_date,
'Flinstonef' last_updated_by,
sysdate last_update_date
FROM n_view_query_templates
WHERE view_label = 'INV_Alias_Transactions'
);

COMMIT;

We are finally in familiar territory when we get to the N_VIEW_TABLE_TEMPLATES table (from a documentation perspective).

With this table, there is no shortage of foreign key constraints so it is imperative that the other records were added prior to this step.

If you look at this DML statement, you will see that our previous commands have allowed us to run this without throwing a foreign key constraint error.

Here is my insertion statement for this table:

INSERT INTO n_view_table_templates
(view_label,
query_position,
table_alias,
from_clause_position,
application_label,
table_name,
profile_option,
product_version,
include_flag,
base_table_flag,
key_view_label,
subquery_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
gen_search_by_col_flag
)
(SELECT 'INV_Alias_Lot_Trnsctns' view_label,
query_position,
table_alias,
from_clause_position,
application_label,
table_name,
profile_option,
product_version,
include_flag,
base_table_flag,
key_view_label,
subquery_flag,
'Flinstonef' created_by,
sysdate creation_date,
'Flinstonef' last_updated_by,
sysdate last_update_date,
gen_search_by_col_flag
FROM n_view_table_templates
WHERE view_label = 'INV_Alias_Transactions'
);

COMMIT;

Lastly, here is my insertion statement for N_VIEW_WHERE_TEMPLATES and N_VIEW_COLUMN_TEMPLATES:

INSERT INTO n_view_where_templates
(view_label,
query_position,
where_clause_position,
where_clause,
profile_option,
product_version,
include_flag,
created_by,
creation_date,
last_updated_by,
last_update_date
)
(SELECT 'INV_Alias_Lot_Trnsctns' view_label,
query_position,
where_clause_position,
where_clause,
profile_option,
product_version,
include_flag,
'Flinstonef' created_by,
sysdate creation_date,
'Flinstonef' last_updated_by,
sysdate last_update_date
FROM N_VIEW_WHERE_TEMPLATES
WHERE VIEW_LABEL = 'INV_Alias_Transactions'
);

COMMIT;


INSERT INTO n_view_column_templates
(view_label,
query_position,
column_label,
table_alias,
column_expression,
column_position,
column_type,
description,
ref_application_label,
ref_table_name,
key_view_label,
ref_lookup_column_name,
ref_description_column_name,
ref_lookup_type,
id_flex_application_id,
id_flex_code,
group_by_flag,
format_mask,
format_class,
gen_search_by_col_flag,
lov_view_label,
lov_column_label,
profile_option,
product_version,
include_flag,
created_by,
creation_date,
last_updated_by,
last_update_date
)
(SELECT 'INV_Alias_Lot_Trnsctns' view_label,
query_position,
column_label,
table_alias,
column_expression,
column_position,
column_type,
description,
ref_application_label,
ref_table_name,
key_view_label,
ref_lookup_column_name,
ref_description_column_name,
ref_lookup_type,
id_flex_application_id,
id_flex_code,
group_by_flag,
format_mask,
format_class,
gen_search_by_col_flag,
lov_view_label,
lov_column_label,
profile_option,
product_version,
include_flag,
'Flinstonef' created_by,
sysdate creation_date,
'Flinstonef' last_updated_by,
sysdate last_update_date
FROM n_view_column_templates
WHERE view_label = 'INV_Alias_Transactions'
);

COMMIT;


After you are done with this xu2 script,  I would run a regeneration with the intent of making sure no errors are thrown.  Next, one can add the additional components that would unacceptable if the changes where made in the existing view. 

That is all for now.