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.