Noetix: Adding a new Z$ Column Reference

Sometimes you need to add an additional Z$ column to link between two view. This Google Knol tells you what you need to know to make a simple customisation.

NOTE: It's worth pointing out that there are a narrow range of customisations supported by Noetix and this isn't one of them. If you have purchased NCM (Noetix Customisation Maintenance) then it's likely that you can get them to support this enhancement but it's at the discretion of whoever handles your NCM files.


We will be making this change to the template for a view - what this means is if multiple views are generated from the template all will contain the additional column. This is by far the simplest way to add a new column (we shy away from customising individual views).

For the purposes of this example I'll be adding in a link between the PO_Receipts and the PO_PO_Distributions templates, I'm working against Oracle e-Business Suite version 12.1.3 and as things stand at the moment (Noetix views 6.0.1) there is no existing link.

Looking at the SQL of the built view I can see that the table alias POD maps to PO.PO_DISTRIBUTIONS_ALL which is the oracle base table that I want to publish the ROWID from in order to link to PO_PO_Distributions (if you open the target view, from the PO_PO_Distributions template, you can see the existing Z$ column Z$NPLPO_PO_Distributions is based on the PDSTR alias which in turn maps to PO.PO_DISTRIBUTIONS_ALL which is correct).

If you look into look into the N_VIEW_TABLE_TEMPLATES table (which is a Noetix object) you can see that the active query position at R12 for the view PO_Receipts is 2. This can be tricky to spot but you were provided with a script called get_data_tmpl.sql during the Noetix training which provides the answer.

Finally the SQL;

UPDATE N_VIEW_TABLE_TEMPLATES
SET
  BASE_TABLE_FLAG = 'Y',
  GEN_SEARCH_BY_COL_FLAG = 'Y',
  KEY_VIEW_LABEL = 'PO_PO_Distributions' -- the target template
WHERE 1=1
AND VIEW_LABEL = 'PO_Receipts' -- template name
AND QUERY_POSITION = 2 -- From get_data_tmpl.sql
AND TABLE_ALIAS = 'POD' -- table alias of PO.PO_DISTRIBUTIONS_ALL from PO_Receipts
;

You'll need to save this in a script can call it from wnoetxu2.sql in the normal way.