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;