More on XU2 Scripts Associated with Custom Tables: What Application Label Should be Used?

We have a materialized view that was created to aid in creating BOM explosions (we have implemented Oracle OPM). This was created by our APEX staff. For performance reasons, this materialized view is the most reasonable object to use for a Noetix View I am creating.


Presently, I am faced with the issue of how do I add this custom owned object in the table, N_VIEW_TABLE_TEMPLATES?

The most difficult problem with adding an insert statement to the N_VIEW_TABLE_TEMPLATES table is to identify an acceptable application label for this custom object.

What does one enter for the application label column in the N_VIEW_TABLE_TEMPLATES? First, there is a foreign key constraint on the N_VIEW_TABLE_TEMPLATES (see below). Specifically, the parent table associated with this constraint is N_APPLICATION_OWNER_TEMPLATES and the column associated with this key is the APPLICATION_LABEL column. Consequently, we need to select an application label that is already set-up based on our Noetix profile. Of course, Noetix does not anticipate our need to do this and include application labels for our custom applications.

Presently, the custom table is in a custom schema, XXAPEX.

For curiosities sake, I could run this query (to no avail) to see what application labels are seeded:

select
application_label
from
noetix_sys_test.n_application_owner_templates
order by 1;

I do notice that I could select ‘NOETIX’ and ‘APPS’ as the application label that will be used. It is important to be aware that the NVA then associates the table_name which I provide in the N_VIEW_TABLE_TEMPLATES insertion statement with the Noetix database account or APPS when regenerating views. Consequently, a synonym will be needed (and select grants with grant options) to either the Noetix or Apps database account.

While our Apex staff chose not to create a synonym of this materialized view in APPS, we are left with the question of where to place a synonym, either APPS or NOETIX_SYS.

While NOETIX_SYS is acceptable solution, I anticipate that I might not be the only consumer of this materialized view, so I create scripts to place a synonym in APPS:


grant select on custom_owner.custom_object_name to APPS with grant option;
grant select on custom_owner.custom_object_name to NOETIX_SYS with grant option;
create synonym apps.custom_object_name for custom_owner.custom_object_name;


This seems to be the most reasonable approach.

Consequently, my solution is to creates a synonym in APPS associated with my materialized view owned by the Apex account and indicate in the XU2 script that APPS is the application_label.

Now I am ready to finish creating my xu2 script.

Adding Custom Tables to a Noetix View

Suppose one is adding a custom table’s column(s) to a view.  Again, one has checked to make sure that this does not change the views granularity.  Are there any additional steps needed to insure that this modification will work successfully?

1.        Of course run the script, get_data_tmpl.sql, which gives you a good perspective of the query blocks applicable for your version of Oracle Applications.

2.       In addition to this, it is important to be cognizant that a select grant with grant option is given from the table’s owner to the Noetix_Sys database account.

Why is step two necessary?

Part of the view security setup is to grant select to the Noetix Roles (these are actually database roles). 

If one peruses the results of this query, this granting that occurs through the NVA can be seen:

select

 *

 from

 dba_tab_privs

 where

 owner = 'NOETIX_SYS'

 and grantee = &favorite_Noetix_Role;

If a column from a custom table is added to a Noetix View, it is important to grant select (with grant option) to this custom table to the Noetix Sys database account. 

A query like this can reveal that this privilege has been granted  (here the custom table is 'XXMES_WIP_ONHAND' and its owner is 'XXMESXFER') :

select

 *

 from

 DBA_TAB_PRIVS

 where

 owner = 'XXMESXFER'

 and table_name = 'XXMES_WIP_ONHAND'

and grantee = 'NOETIX_SYS'

and grantable = ‘YES’

This will enable the Noetix Sys database account to grant select to various Noetix roles.