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.

Expedient XU2/XU5 Script Development

Well, you have taken the Noetix Certification Course. You want to develop xu2/xu5 scripts, but the method to uncover syntactical/table constraint generated errors is very inefficient.

Specifically, if one runs stage 2 through 4 of the Noetix View Administrator, it will take greater than 20 minutes to run a re-generation against an Oracle Applications ERP environment.

Like all programming, syntactical errors are the lowest level of errors. One can have no syntactical errors, but still create some really bad design or logical errors that are not caught.

How can I speed-up checking for syntactical/table constraint generated errors (not logical errors)? Here are three methods I have used to help me uncover errors quickly.

1. Run the script (excluding the commit) against the noetix_sys account using SQL Plus (or your favorite querying tool). After the script has completed, look for any errors being thrown. In any event, perform a “rollback” when done. This works well for both xu2 and xu5 scripts.

2. Run stages 2 to 4 in the NVA (specifically stop at the point where there is a prompt for the APPS login information during stage 4). If an error has been thrown with the xu2 script, opt to not enter the APPS password and select "Cancel". This method does not work very well for xu5 scripts (I would recommend method 1 above for xu5).

The key here is to check the corresponding generated spool file(s) for any Oracle errors being thrown. I watch the SQL Loader statistics and I know the xu2 scripts are ran immediately afterwards. I then look for the spool file(s). Within about 5 minutes, I know if my script has syntactical/table constraint errors.

Again, to do this properly, I will not enter the stage 4 prompt for the APPS password until I have performed a check of the spool file(s).

3. Identical to approach 2 (which is usually best), but use Windows command mode to search for errors instead of manually opening spool files. Typically, one is only working on one script at a time. Suppose you are working on multiple scripts at once (probably not advisable). Use a search string command like this within the base directory:

findstr /s “ORA-“ *.lst>error_check.txt

/* I am using Microsoft’s Windows Server 2003 R2 */

Translation of this search command: find a matching string of the form “ORA-“ in any spool file in this directory and redirect the output to the file, “error_check.txt”

I usually use method two to uncover syntactical and constraint related errors.

6.01 Noetix Views Post Regeneration Check List

Last night I performed my first regeneration of the 6.01 Noetix Views since we went live with them in February. I realized I should have a formal procedure to review the success of a regeneration.

Here is my first draft of this procedure.

1. Make sure security is behaving properly. My method is to sample the business areas and check the sharing.

2. Make sure all custom objects exist.

3. Make sure the wnoetx_gseg_flex_kff_cols.sql script exposes the pre-kff segment values. With the 6.01 Noetix Views, key flexfiled segment vales are exposed using the key flexfield cache table construct in lieu of the old methodology.

4. Review the file,” listcnfg.lst”, to confirm how the regeneration was done.
5. Makes sure that the security manager is working correctly. Run this query to view activity:

select
*
from
noetix_sys.n_sm_messages
where
trunc(creation_date) = trunc(sysdate);

I was obtaining errors associated with the concurrent programs set-up to maintain the security data cache (cf page 205 of the 6.01 Noetix Views Administrator Guide). I spooled this script and then ran it using the Noetix System account:

select
'grant execute on ' || object_name ||' to apps; '
from
all_objects
where
owner = 'NOETIX_SYS'
and object_type = 'PACKAGE'
order by 1;

6. Check to see if the KFF cache tables received their initial loading.

Query tables of the form, N_KFF%, and lookover records.

7. Check to see which concurrent programs were created in step 4:

select program_name from n_f_kff_flex_source_pgms

8. Schedule the "Enable Incremental Refresh-(NOETIX_SYS[UID-noetix_database_user_id])' concurrent program.

9. Schedule the key flexfiled cache table concurrent programs to correspond with the refresh approach decided on. The present configuration is that all incremental refresh programs are scheduled to run once a day (except the system item table which runs every 15 minutes). 

10. Check to see which concurrent programs are running:

select
pgms.program_type,
pgms.program_name,
rns.run_id,
rns.request_id,
rns.refresh_to_date,
rns.status, rns.message,
to_char(rns.creation_date,'dd-mon-yyyy hh:mm AM') created, to_char(rns.last_update_date,'dd-mon-yyyy hh:mm AM') updated
from
n_f_kff_program_runs rns,
n_f_kff_flex_source_pgms pgms
where
rns.program_id = pgms.program_id
--and pgms.program_type = 'INCREMENTAL'
--and rns.last_update_date >= sysdate - 1
order by updated

Here is similar information from an applsys perspective:

select
cp.concurrent_program_name,
p.request_id,
to_char ( p.actual_start_date,'dd-mon-yyyy hh:mm AM' ) start_date,
to_char(p.actual_completion_date,'dd-mon-yyyy hh:mm AM') end_date
from
applsys.fnd_concurrent_requests p,
applsys.fnd_concurrent_programs cp
where
cp.concurrent_program_id = p.concurrent_program_id
and trunc(p.actual_start_date) >= trunc(sysdate-1)
and cp.concurrent_program_name like 'N_KFF%'
order by
p.request_id desc

11. Check the N_KFF% tables to make sure incremental updates are occuring.

12. Update my Custom script repository and PVCS to correspond with any new scripts and make sure they are synchronized.

Other Noetix Bloggers

It is encouraging to see that Sumita is blogging about Noetix here. The Noetix community is rather small, but there are a lot features that a developer/administrator need to learn and an active community is quite valuable.

Going Live with 6.01 Noetix Views

Last week we went live with the 6.01 Noetix Views. Here is our high level process (our reporting tool, Discoverer, had steps included in this process as well):

1 Export Discoverer business areas and workbooks separately.

2 Using data pump, export the Noetix Administration and end user layer database schemas.

3 Run the NVA pre-update scripts.

4 Spool SQL (and run spooled file) pertaining to dropping old Noetix roles:

SELECT
'DROP ROLE '||GRANTED_ROLE || ';'
from
dba_role_privs
where
grantee IN ('NOETIX_SYS')
and granted_role not in ('CONNECT','RESOURCE')
order by 1

If these roles were not dropped, I have found that the Noetix View Administrator would throw errors during the regeneration process. This script could be potentially very bad if I am not careful that only Noetix created roles are dropped.

5 Drop the Noetix Administration and end user layer database schemas.

6 Create the end user layer user, EUL5_US and provide system grants.

7 Using the Discovere Administrator, set-up the end user layer schema, EUL5_US as a Discoverer, private EUL schema.

8 Run the eul5.sql script (utility) in EUL5_US.

9 Having access to the SYSTEM dba account, run the Noetix View Administrator, stage 1.

10 Run scripts that provides grant select to some custom objects with grant option for some. We have a lot of xu2 scripts and some of the scripts reference objects which are owned by custom schemas.

11 Complete Noetix View Administrator stages 2 through 4.

12 Run my grant execute script to APPS for Noetix Sys KFF and security packages. With the 6.01 release, the appropriate grants to invoke these packages (using corresponding concurrent programs created by the NVA) in Oracle Applications were not included. I compiled a list based on errors being thrown while attempting to run the corresponding concurrent programs.

13 Generate the EUL in the new EUL schema.

14 Create manual joins.

15 Import workbooks.

16 Test manual joins.

17 Run concurrent program which performs initial upload for KFF cache tables.

18 Schedule incremental refresh for concurrent programs which are associated with the kff cache tables.

19 Make sure the Discoverer role mapping corresponds with the mappings used in the tupd files.

20 Generate the Noetix Search file.

21 Test the top 20 workbooks to confirm that the imported workbooks can be associated with the new Noetix Administration account objects.

All in all, this was successful.

Creating a New View in Noetix Using an XU2 Script

I have been very busy preparing our reporting environment to be ready for Oracle Applications 12.1.2 (using Noetix 6.01). Consequently, I have had little time to post. Awhile ago, I created a method to create a new Noetix View. I created a first draft of this method prior to taking the Noetix View Customization Course and I revised it once afterwards (based on what I learned).

There is no documented method to do this, but I continually run into situations where a modification can change the granularity of a view. Coupled with this, my user community includes a prolific Continuous Improvement department whom are always submitting some new requirement.

Here it is:

1. Get requirements for new view.
2. Identify the most similar seeded Noetix View.
3. Run the “get_data_tmpl.sql” script to understand the structure (e.g. query blocks that are pertinent to your version of Oracle Apps).
4. Use a package and a driving anonymous block to spool all the template table DML that would be needed to recreate the most similarly seeded Noetix View.
5. Change the view label immediately and invoke this new xu2 script with the NVA.

Here is my package (should be owned by Noetix_Sys schema):

CREATE OR REPLACE PACKAGE XXCMFG_NOETIX_TOOL_PKG IS
PROCEDURE GENERATE_VIEW_COLS_DML (p_view_name in VARCHAR2, p_query_position1 in NUMBER, p_query_position2 in NUMBER, p_developer_name in VARCHAR2);
PROCEDURE GENERATE_VIEW_DML (p_view_name in VARCHAR2, p_developer_name in VARCHAR2);
PROCEDURE GENERATE_ROLE_VIEW_DML (p_view_name in VARCHAR2, p_developer_name in VARCHAR2);
PROCEDURE GENERATE_VIEW_QUERY_DML (p_view_name in VARCHAR2, p_query_position1 in NUMBER, p_query_position2 in NUMBER , p_developer_name in VARCHAR2);
PROCEDURE GENERATE_VIEW_TABLE_DML (p_view_name in VARCHAR2, p_query_position1 in NUMBER, p_query_position2 in NUMBER, p_developer_name in VARCHAR2);
PROCEDURE GENERATE_VIEW_WHERE_DML (p_view_name in VARCHAR2, p_query_position1 in NUMBER, p_query_position2 in NUMBER, p_developer_name in VARCHAR2);
END XXCMFG_NOETIX_TOOL_PKG;
/
CREATE OR REPLACE PACKAGE BODY XXCMFG_NOETIX_TOOL_PKG IS
PROCEDURE GENERATE_VIEW_COLS_DML (p_view_name in VARCHAR2, p_query_position1 in NUMBER, p_query_position2 in NUMBER , p_developer_name in VARCHAR2) IS


CURSOR c_noetix_template_records (cp_view_name in VARCHAR2, cp_query_position1 in NUMBER, cp_query_position2 in NUMBER) is
SELECT view_label
,query_position --number
,column_label
,table_alias
,column_expression
,column_position --number
,column_type
,description
,group_by_flag
,gen_search_by_col_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 --date
,last_updated_by
,last_update_date --date
FROM n_view_column_templates
WHERE 1=1
-- only for specific scenarios

-- only for specific scenarios
and view_label = cp_view_name
and query_position in ( cp_query_position1, cp_query_position2)
ORDER BY query_position, column_position;

v_noetix_template_records c_noetix_template_records%ROWTYPE;
v_errm VARCHAR2(200);


BEGIN
DBMS_OUTPUT.ENABLE(900000);


FOR v_noetix_template_records IN c_noetix_template_records(p_view_name, p_query_position1, p_query_position2) LOOP
BEGIN

dbms_output.put_line(chr(9));
dbms_output.put_line('-- ******insert****** ');
dbms_output.put_line(chr(9));
dbms_output.put_line(chr(9));

dbms_output.put_line(chr(9));

dbms_output.put_line('INSERT INTO n_view_column_templates ');

dbms_output.put_line(' (view_label ');
dbms_output.put_line(' , query_position');
dbms_output.put_line(' , column_label');
dbms_output.put_line(' , table_alias');
dbms_output.put_line(' , column_expression');
dbms_output.put_line(' , column_position');
dbms_output.put_line(' , column_type');
dbms_output.put_line(' , description');
dbms_output.put_line(' , group_by_flag');
dbms_output.put_line(' , gen_search_by_col_flag');
dbms_output.put_line(' , profile_option');
dbms_output.put_line(' , product_version');
IF v_noetix_template_records.ref_application_label IS NOT NULL THEN
dbms_output.put_line(' , ref_application_label');
END IF;
IF v_noetix_template_records.ref_table_name IS NOT NULL THEN
dbms_output.put_line(' , ref_table_name');
END IF;
IF v_noetix_template_records.ref_lookup_column_name IS NOT NULL THEN
dbms_output.put_line(' , ref_lookup_column_name');
END IF;
IF v_noetix_template_records.ref_description_column_name IS NOT NULL THEN
dbms_output.put_line(' , ref_description_column_name');
END IF;
IF v_noetix_template_records.ref_lookup_type IS NOT NULL THEN
dbms_output.put_line(' , ref_lookup_type');
END IF;
dbms_output.put_line(' , created_by');
dbms_output.put_line(' , creation_date');
dbms_output.put_line(' , last_updated_by');
dbms_output.put_line(' , last_update_date');
dbms_output.put_line(' ) ');
dbms_output.put_line(' VALUES ');

dbms_output.put_line(' (' || chr(39) || v_noetix_template_records.view_label || chr(39) || ' --view_label');
dbms_output.put_line(' ,' || to_char(v_noetix_template_records.query_position) || ' --query_position number');
dbms_output.put_line(' ,' || chr(39) || v_noetix_template_records.column_label|| chr(39) || ' --column_label');
IF v_noetix_template_records.table_alias IS NULL THEN
dbms_output.put_line(' , NULL --table_alias');
ELSE
dbms_output.put_line(' ,' || chr(39) || v_noetix_template_records.table_alias|| chr(39) || ' --table_alias');
END IF;
dbms_output.put_line(' ,'||chr(39) || replace(replace(v_noetix_template_records.column_expression, chr(39), chr(39)|| chr(39)), '&','''||chr(38)||''' ) || chr(39)|| ' --column_expression');
dbms_output.put_line(' ,' || to_char( v_noetix_template_records.column_position )|| ' --column_position number');
dbms_output.put_line(' ,' || chr(39) || v_noetix_template_records.column_type || chr(39)|| ' --column_type');
IF v_noetix_template_records.description IS NULL THEN
dbms_output.put_line(' , NULL --description');
ELSE
dbms_output.put_line(' ,' || chr(39) || v_noetix_template_records.description || chr(39) || ' -- description');
END IF;
IF v_noetix_template_records.group_by_flag IS NULL THEN
dbms_output.put_line(' , NULL --group_by_flag');
ELSE
dbms_output.put_line(' ,' || chr(39) || v_noetix_template_records.group_by_flag || chr(39)|| ' --group_by_flag');
END IF;
dbms_output.put_line(' ,' || chr(39) || v_noetix_template_records.gen_search_by_col_flag || chr(39)|| ' --gen_search_by_col_flag');
IF v_noetix_template_records.profile_option IS NULL THEN
dbms_output.put_line(' , NULL --profile_option');
ELSE
dbms_output.put_line(' ,' || chr(39) || v_noetix_template_records.profile_option || chr(39) || ' --profile_option');
END IF;
dbms_output.put_line(' ,' || chr(39) || v_noetix_template_records.product_version || chr(39) || ' --product_version');
IF v_noetix_template_records.ref_application_label IS NOT NULL THEN
dbms_output.put_line(' ,' || chr(39) || v_noetix_template_records.ref_application_label || chr(39) || ' --ref_application_label');
END IF;
IF v_noetix_template_records.ref_table_name IS NOT NULL THEN
dbms_output.put_line(' ,' || chr(39) || v_noetix_template_records.ref_table_name || chr(39) || ' --ref_table_name');
END IF;
IF v_noetix_template_records.ref_lookup_column_name IS NOT NULL THEN
dbms_output.put_line(' ,' || chr(39) || v_noetix_template_records.ref_lookup_column_name || chr(39) || ' --ref_lookup_column_name');
END IF;
IF v_noetix_template_records.ref_description_column_name IS NOT NULL THEN
dbms_output.put_line(' ,' || chr(39) || v_noetix_template_records.ref_description_column_name || chr(39) || ' --ref_description_column_name');
END IF;
IF v_noetix_template_records.ref_lookup_type IS NOT NULL THEN
dbms_output.put_line(' ,' || chr(39) || v_noetix_template_records.ref_lookup_type || chr(39) || ' --ref_lookup_type');
END IF;

dbms_output.put_line(' ,' || chr(39) || p_developer_name || chr(39) ||' -- created_by');
dbms_output.put_line(' , SYSDATE --creation_date');
dbms_output.put_line(' ,' || chr(39) || p_developer_name || chr(39) || ' --last_updated_by');
dbms_output.put_line(' , SYSDATE --last_update_date');
dbms_output.put_line('); ');
dbms_output.put_line(chr(9));
dbms_output.put_line('COMMIT; ');
EXCEPTION
WHEN INVALID_CURSOR THEN
dbms_output.put_line('Error with cursor for inserting new item records. ');
END;

END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('NO_DATA_FOUND error when inserting new records. ' );
WHEN OTHERS THEN
v_errm := SQLERRM;
dbms_output.put_line('Error is: '|| v_errm );
END GENERATE_VIEW_COLS_DML;

PROCEDURE GENERATE_VIEW_DML (p_view_name in VARCHAR2, p_developer_name in VARCHAR2) IS



CURSOR c_noetix_template_records (cp_view_name in VARCHAR2) is
SELECT application_label ,
current_version,
--description,
--essay,
export_view,
freeze_flag,
include_flag,
keywords,
original_version,
product_version,
profile_option,
security_code,
sort_layer,
special_process_code,
view_label,
created_by,
creation_date,
last_updated_by,
last_update_date
FROM N_VIEW_TEMPLATES
WHERE view_label = cp_view_name;

v_noetix_template_records c_noetix_template_records%ROWTYPE;
v_errm VARCHAR2(200);

BEGIN
DBMS_OUTPUT.ENABLE(900000);


FOR v_noetix_template_records IN c_noetix_template_records (p_view_name) LOOP

BEGIN

dbms_output.put_line(chr(9));
dbms_output.put_line('-- ******insert****** ');
dbms_output.put_line(chr(9));
dbms_output.put_line(chr(9));
dbms_output.put_line(chr(9));
dbms_output.put_line('INSERT INTO N_VIEW_TEMPLATES ');
dbms_output.put_line(' (view_label');
dbms_output.put_line(' , application_label ');
dbms_output.put_line(' , description');
dbms_output.put_line(' , profile_option');
dbms_output.put_line(' , essay');
dbms_output.put_line(' , keywords');
dbms_output.put_line(' , product_version');
dbms_output.put_line(' , include_flag');
dbms_output.put_line(' , export_view');
dbms_output.put_line(' , security_code');
dbms_output.put_line(' , special_process_code');
dbms_output.put_line(' , sort_layer');
dbms_output.put_line(' , freeze_flag');
dbms_output.put_line(' , created_by');
dbms_output.put_line(' , creation_date');
dbms_output.put_line(' , last_updated_by');
dbms_output.put_line(' , last_update_date');
dbms_output.put_line(' , original_version');
dbms_output.put_line(' , current_version');
dbms_output.put_line(' ) ');
dbms_output.put_line(' VALUES ');


dbms_output.put_line('( ' ||chr(39) || v_noetix_template_records.view_label || chr(39) || ' --view_label');

dbms_output.put_line(',' || chr(39) || v_noetix_template_records.application_label || chr(39) ||' -- application_label');

-- IF v_noetix_template_records.description IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --description');
-- ELSE
-- dbms_output.put_line(', ' || chr(39) || 'Description'/*substr(v_noetix_template_records.description,1,50)*/ || chr(39)|| ' --description');
-- END IF;

IF v_noetix_template_records.profile_option IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --profile_option');
ELSE
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.profile_option || chr(39) || ' --profile_option');
END IF;


-- IF v_noetix_template_records.essay IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --essay');
-- ELSE
-- dbms_output.put_line(',' || chr(39) || v_noetix_template_records.essay || chr(39) || ' --essay');
-- END IF;


IF v_noetix_template_records.keywords IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --keywords');
ELSE
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.keywords || chr(39) || ' --keywords');
END IF;


IF v_noetix_template_records.product_version IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --product_version');
ELSE
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.product_version || chr(39) || ' --product_version');
END IF;


IF v_noetix_template_records.include_flag IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --include_flag');
ELSE
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.include_flag || chr(39) || ' --include_flag');
END IF;

IF v_noetix_template_records.export_view IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --export_view');
ELSE
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.export_view || chr(39) || ' --export_view');
END IF;


IF v_noetix_template_records.security_code IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --security_code');
ELSE
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.security_code || chr(39) || ' --security_code');
END IF;


IF v_noetix_template_records.special_process_code IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --special_process_code');
ELSE
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.special_process_code || chr(39) || ' --special_process_code');
END IF;



IF v_noetix_template_records.sort_layer IS NULL THEN
dbms_output.put_line(' , TO_NUMBER(NULL) --sort_layer');
ELSE
dbms_output.put_line(',' ||TO_CHAR(v_noetix_template_records.sort_layer) || ' --sort_layer');
END IF;

dbms_output.put_line(',' || chr(39) || v_noetix_template_records.freeze_flag || chr(39) || ' --freeze_flag');

dbms_output.put_line(',' || chr(39) || p_developer_name || chr(39) || ' --created_by');
dbms_output.put_line(', SYSDATE --creation_date');
dbms_output.put_line(',' || chr(39) || p_developer_name || chr(39) || ' --last_updated_by');
dbms_output.put_line(', SYSDATE --last_update_date');


IF v_noetix_template_records.original_version IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --original_version');
ELSE
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.original_version || chr(39) || ' --original_version');
END IF;

IF v_noetix_template_records.current_version IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --current_version');
ELSE
dbms_output.put_line(', ' || chr(39) || v_noetix_template_records.current_version || chr(39)||' --current_version');
END IF;


dbms_output.put_line('); ');
dbms_output.put_line(chr(9));
dbms_output.put_line('COMMIT; ');

EXCEPTION
WHEN INVALID_CURSOR THEN
dbms_output.put_line('Error with cursor for inserting new item records. ');
END;

END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('NO_DATA_FOUND error when inserting new records. ' );
WHEN OTHERS THEN
v_errm := SQLERRM;
dbms_output.put_line('Error is: '|| v_errm );

END GENERATE_VIEW_DML;

PROCEDURE GENERATE_ROLE_VIEW_DML (p_view_name in VARCHAR2, p_developer_name in VARCHAR2) IS


CURSOR c_noetix_template_records (cp_view_name in VARCHAR2)is
SELECT created_by, creation_date, include_flag,
last_update_date, last_updated_by, product_version,
role_label, view_label
FROM N_ROLE_VIEW_TEMPLATES
WHERE view_label = cp_view_name;

v_noetix_template_records c_noetix_template_records%ROWTYPE;
v_errm VARCHAR2(200);

BEGIN
DBMS_OUTPUT.ENABLE(900000);


FOR v_noetix_template_records IN c_noetix_template_records (p_view_name) LOOP

BEGIN
dbms_output.put_line(chr(9));
dbms_output.put_line('-- ******insert****** ');
dbms_output.put_line(chr(9));
dbms_output.put_line(chr(9));
dbms_output.put_line(chr(9));

dbms_output.put_line('INSERT INTO N_ROLE_VIEW_TEMPLATES');
dbms_output.put_line('( role_label ');
dbms_output.put_line(' , view_label');
dbms_output.put_line(' , product_version');
dbms_output.put_line(' , include_flag');
dbms_output.put_line(' , created_by');
dbms_output.put_line(' , creation_date');
dbms_output.put_line(' , last_updated_by');
dbms_output.put_line(' , last_update_date');

dbms_output.put_line(' ) ');
dbms_output.put_line(' VALUES ');
dbms_output.put_line('( ' ||chr(39) || v_noetix_template_records.role_label || chr(39) || ' --role_label');
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.view_label || chr(39) || ' --view_label');
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.product_version || chr(39) || ' --product_version');

IF v_noetix_template_records.include_flag IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --include_flag');
ELSE
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.include_flag || chr(39) || ' --include_flag');
END IF;

dbms_output.put_line(',' || chr(39) || p_developer_name || chr(39) ||' --created_by');
dbms_output.put_line(', SYSDATE --creation_date');
dbms_output.put_line(',' || chr(39) || p_developer_name || chr(39) ||' --last_updated_by');
dbms_output.put_line(', SYSDATE --last_update_date');
dbms_output.put_line('); ');
dbms_output.put_line(chr(9));
dbms_output.put_line('COMMIT; ');

EXCEPTION
WHEN INVALID_CURSOR THEN
dbms_output.put_line('Error with cursor for inserting new item records. ');
END;
END LOOP;
END GENERATE_ROLE_VIEW_DML;

PROCEDURE GENERATE_VIEW_QUERY_DML (p_view_name in VARCHAR2, p_query_position1 in NUMBER, p_query_position2 in NUMBER , p_developer_name in VARCHAR2) IS


CURSOR c_noetix_template_records (cp_view_name in VARCHAR2, cp_query_position1 in NUMBER, cp_query_position2 in NUMBER) IS
SELECT created_by, creation_date, group_by_flag, include_flag,
last_updated_by, last_update_date, product_version, profile_option,
query_position, union_minus_intersection, view_comment, view_label
FROM N_VIEW_QUERY_TEMPLATES
WHERE view_label = cp_view_name
AND query_position in ( cp_query_position1, cp_query_position2)
ORDER BY query_position;

v_noetix_template_records c_noetix_template_records%ROWTYPE;
v_errm VARCHAR2(200);

BEGIN
DBMS_OUTPUT.ENABLE(900000);

FOR v_noetix_template_records IN c_noetix_template_records (p_view_name, p_query_position1, p_query_position2) LOOP

BEGIN
dbms_output.put_line(chr(9));
dbms_output.put_line('-- ******insert****** ');
dbms_output.put_line(chr(9));
dbms_output.put_line(chr(9));
dbms_output.put_line(chr(9));

dbms_output.put_line('INSERT INTO N_VIEW_QUERY_TEMPLATES ');
dbms_output.put_line('( ');
dbms_output.put_line(' view_label');
dbms_output.put_line(' , query_position');
dbms_output.put_line(' , union_minus_intersection ');
dbms_output.put_line(' , group_by_flag');
dbms_output.put_line(' , profile_option');
dbms_output.put_line(' , product_version');
dbms_output.put_line(' , include_flag');
dbms_output.put_line(' , view_comment');
dbms_output.put_line(' , created_by');
dbms_output.put_line(' , creation_date');
dbms_output.put_line(' , last_updated_by');
dbms_output.put_line(' , last_update_date');

dbms_output.put_line(' ) ');
dbms_output.put_line(' VALUES ');
dbms_output.put_line('( ' || chr(39) || v_noetix_template_records.view_label || chr(39) || ' --view_label');
dbms_output.put_line(',' ||to_char( v_noetix_template_records.query_position) ||' --query_position');

IF v_noetix_template_records.union_minus_intersection IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --union_minus_intersection');
ELSE
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.union_minus_intersection || chr(39) || ' --union_minus_intersection');
END IF;

IF v_noetix_template_records.group_by_flag IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --group_by_flag');
ELSE
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.group_by_flag || chr(39) || ' --group_by_flag');
END IF;

IF v_noetix_template_records.profile_option IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --profile_option');
ELSE
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.profile_option || chr(39) || ' --profile_option');
END IF;

IF v_noetix_template_records.product_version IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --product_version');
ELSE
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.product_version || chr(39) || ' --product_version');
END IF;

IF v_noetix_template_records.include_flag IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --include_flag');
ELSE
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.include_flag || chr(39) || ' --include_flag');
END IF;

IF v_noetix_template_records.view_comment IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --view_comment');
ELSE
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.view_comment || chr(39) || ' --view_comment');
END IF;

dbms_output.put_line(', '|| chr(39) || p_developer_name || chr(39) ||' -- created_by');
dbms_output.put_line(', SYSDATE --creation_date');
dbms_output.put_line(',' || chr(39) || p_developer_name || chr(39) ||' --last_updated_by');
dbms_output.put_line(', SYSDATE --last_update_date');
dbms_output.put_line('); ');
dbms_output.put_line(chr(9));
dbms_output.put_line('COMMIT; ');
EXCEPTION
WHEN INVALID_CURSOR THEN
dbms_output.put_line('Error with cursor for inserting new item records. ');
END;

END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('NO_DATA_FOUND error when inserting new records. ' );
WHEN OTHERS THEN
v_errm := SQLERRM;
dbms_output.put_line('Error is: '|| v_errm );

END GENERATE_VIEW_QUERY_DML;



PROCEDURE GENERATE_VIEW_TABLE_DML (p_view_name in VARCHAR2, p_query_position1 in NUMBER, p_query_position2 in NUMBER, p_developer_name in VARCHAR2) IS


CURSOR c_noetix_template_records (cp_view_name in VARCHAR2, cp_query_position1 in NUMBER, cp_query_position2 in NUMBER) is
SELECT
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
FROM n_view_table_templates
WHERE 1=1
-- only for specific scenarios

-- only for specific scenarios
and view_label = cp_view_name
and query_position in ( cp_query_position1, cp_query_position2)
ORDER BY query_position, from_clause_position, table_alias;

v_noetix_template_records c_noetix_template_records%ROWTYPE;
v_errm VARCHAR2(200);

BEGIN
DBMS_OUTPUT.ENABLE(900000);


FOR v_noetix_template_records IN c_noetix_template_records (p_view_name, p_query_position1, p_query_position2) LOOP
BEGIN
dbms_output.put_line(chr(9));
dbms_output.put_line('-- ******insert****** ');
dbms_output.put_line(chr(9));
dbms_output.put_line(chr(9));
dbms_output.put_line(chr(9));

dbms_output.put_line('INSERT INTO N_VIEW_TABLE_TEMPLATES ');

dbms_output.put_line(' (view_label ');
dbms_output.put_line(' , query_position');
dbms_output.put_line(' , table_alias');
dbms_output.put_line(' , from_clause_position');
dbms_output.put_line(' , application_label');
dbms_output.put_line(' , table_name');
dbms_output.put_line(' , product_version') ;
dbms_output.put_line(' , base_table_flag');
dbms_output.put_line(' , subquery_flag');
dbms_output.put_line(' , gen_search_by_col_flag');
dbms_output.put_line(' , created_by');
dbms_output.put_line(' , creation_date');
dbms_output.put_line(' , last_updated_by');
dbms_output.put_line(' , last_update_date');
dbms_output.put_line(' ) ');

dbms_output.put_line(' VALUES ');
dbms_output.put_line('(' ||chr(39) || v_noetix_template_records.view_label || chr(39)|| ' --view_label');
dbms_output.put_line(',' || to_char(v_noetix_template_records.query_position) || ' --query_position');
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.table_alias || chr(39) ||' -- table_alias');
dbms_output.put_line(',' || to_char(v_noetix_template_records.from_clause_position) ||' --from_clause_position');
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.application_label || chr(39) || ' -- application_label');
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.table_name || chr(39)|| ' --table_name');
IF v_noetix_template_records.product_version IS NULL THEN
dbms_output.put_line(' , NULL --product_version');
ELSE
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.product_version || chr(39) || ' --product_version');
END IF;
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.base_table_flag || chr(39)|| ' --base_table_flag');
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.subquery_flag || chr(39) || ' --sub_query_flag');
dbms_output.put_line(',' || chr(39) || v_noetix_template_records.gen_search_by_col_flag || chr(39) || ' --gen_search_by_col_flag');
dbms_output.put_line(',' || chr(39) || p_developer_name || chr(39) || ' --created_by');
dbms_output.put_line(', SYSDATE --created_date');
dbms_output.put_line(',' || chr(39) || p_developer_name || chr(39) || ' --last_updated_by');
dbms_output.put_line(', SYSDATE --last_updated_date ');

dbms_output.put_line('); ');
dbms_output.put_line(' ');
dbms_output.put_line('COMMIT; ');
dbms_output.put_line(' ');
EXCEPTION
WHEN INVALID_CURSOR THEN
dbms_output.put_line('Error with cursor for inserting new item records. ');
END;

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND THEN
dbms_output.put_line('NO_DATA_FOUND error when inserting new records. ' );
WHEN OTHERS THEN
v_errm := SQLERRM;
dbms_output.put_line('Error is: '|| v_errm );

END GENERATE_VIEW_TABLE_DML;


PROCEDURE GENERATE_VIEW_WHERE_DML (p_view_name in VARCHAR2, p_query_position1 in NUMBER, p_query_position2 in NUMBER, p_developer_name in VARCHAR2) IS
/* p_table_name is a noetix template table */

CURSOR c_noetix_template_records (cp_view_name in VARCHAR2, cp_query_position1 in NUMBER, cp_query_position2 in NUMBER) is
SELECT view_label
,query_position
,where_clause_position
,where_clause
,include_flag
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date
FROM N_VIEW_WHERE_TEMPLATES
WHERE 1=1
-- only for specific scenarios

-- only for specific scenarios
and view_label = cp_view_name
and query_position in ( cp_query_position1, cp_query_position2)
ORDER BY query_position, where_clause_position;

v_noetix_template_records c_noetix_template_records%ROWTYPE;
v_errm VARCHAR2(200);

BEGIN
DBMS_OUTPUT.ENABLE(900000);


FOR v_noetix_template_records IN c_noetix_template_records(p_view_name, p_query_position1, p_query_position2 ) LOOP
BEGIN

dbms_output.put_line(chr(9));
dbms_output.put_line('-- ******insert****** ');
dbms_output.put_line(chr(9));
dbms_output.put_line(chr(9));
dbms_output.put_line(chr(9));


dbms_output.put_line('INSERT INTO N_VIEW_WHERE_TEMPLATES ');
dbms_output.put_line(' (view_label ');
dbms_output.put_line(' , query_position');
dbms_output.put_line(' , where_clause_position');
dbms_output.put_line(' , where_clause');
dbms_output.put_line(' , include_flag');
dbms_output.put_line(' , profile_option');
dbms_output.put_line(' , product_version');
dbms_output.put_line(' , created_by');
dbms_output.put_line(' , creation_date');
dbms_output.put_line(' , last_updated_by');
dbms_output.put_line(' , last_update_date');
dbms_output.put_line(' ) ');

dbms_output.put_line(' VALUES ');
dbms_output.put_line('( ' || chr(39) || v_noetix_template_records.view_label || chr(39) ||' -- view_label');
dbms_output.put_line(',' || to_char(v_noetix_template_records.query_position) ||' --query_position');
dbms_output.put_line(',' || to_char(v_noetix_template_records.where_clause_position) || ' --where_clause_position');
dbms_output.put_line(',' || chr(39) || replace(replace(v_noetix_template_records.where_clause, chr(39), chr(39)|| chr(39)), '&','''||chr(38)||''' ) || chr(39) || ' --where_clause');
IF v_noetix_template_records.include_flag IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --include_flag');
ELSE
dbms_output.put_line(' ,' || chr(39) || v_noetix_template_records.include_flag || chr(39) || ' --include_flag');
END IF;
IF v_noetix_template_records.profile_option IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --profile_option');
ELSE
dbms_output.put_line(' ,' || chr(39) || v_noetix_template_records.profile_option || chr(39) || ' --profile_option');
END IF;
IF v_noetix_template_records.product_version IS NULL THEN
dbms_output.put_line(' , TO_CHAR(NULL) --product_version');
ELSE
dbms_output.put_line(' ,' || chr(39) || v_noetix_template_records.product_version || chr(39) || ' --product_version');
END IF;

dbms_output.put_line(',' || chr(39) || p_developer_name || chr(39) ||' --created_by');
dbms_output.put_line(', SYSDATE --creation_date');
dbms_output.put_line(',' || chr(39) || p_developer_name || chr(39) || ' --last_updated_by');
dbms_output.put_line(', SYSDATE --last_update_date');
dbms_output.put_line('); ');
dbms_output.put_line(chr(9));
dbms_output.put_line('COMMIT; ');
dbms_output.put_line(chr(9));

EXCEPTION
WHEN INVALID_CURSOR THEN
dbms_output.put_line('Error with cursor for inserting new item records. ');
END;

END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('NO_DATA_FOUND error when inserting new records. ' );
WHEN OTHERS THEN
v_errm := SQLERRM;
dbms_output.put_line('Error is: '|| v_errm );

END GENERATE_VIEW_WHERE_DML;

END XXCMFG_NOETIX_TOOL_PKG;


Here is my anonymous block (ran with SQL Plus with set serveroutput on while logged in as Noetix_Sys):

BEGIN
XXCMFG_NOETIX_TOOL_PKG.GENERATE_VIEW_DML('EAM_Work_Order_Resources', 'flinstonef');
XXCMFG_NOETIX_TOOL_PKG.GENERATE_ROLE_VIEW_DML('EAM_Work_Order_Resources', 'flinstonef');
XXCMFG_NOETIX_TOOL_PKG.GENERATE_VIEW_QUERY_DML ('EAM_Work_Order_Resources', 1,2, 'flinstonef');
XXCMFG_NOETIX_TOOL_PKG.GENERATE_VIEW_TABLE_DML ('EAM_Work_Order_Resources',1,2, 'flinstonef');
XXCMFG_NOETIX_TOOL_PKG.GENERATE_VIEW_WHERE_DML ('EAM_Work_Order_Resources',1,2, 'flinstonef');
XXCMFG_NOETIX_TOOL_PKG.GENERATE_VIEW_COLS_DML('EAM_Work_Order_Resources',1,2 , 'flinstonef');

END;