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.
Tuesday, March 1, 2011
Friday, September 24, 2010
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;
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;
Thursday, August 5, 2010
Modifying an ALOOK Column Type with an Xu2 Script
Back in June, I identified (and notified Noetix) two columns that are not populating (are always NULL) in our Noetix 5.87/Oracle Apps version 12.06 of the view GMD_Routing_Resources. Here they are:
Resource_Process_UOM
Resource_Usage_UOM
When I research the table they are associated with,GMD_OPERATION_RESOURCES, it appears that between Oracle and our implementers the following holds:
-GMD_OPERATION_RESOURCES.RESOURCE_PROCESS_UOM stores UOM information on resource processes and not GMD_OPERATION_RESOURCES.PROCESS_UOM.
-GMD_OPERATION_RESOURCES.RESOURCE_USAGE_UOM stores resource usage UOM information and not GMD_OPERATION_RESOURCES.USAGE_UM.
When I query how the n_view_column_templates table has these columns set-up, I see the following information:
Resource_Process_UOM
The column, view_label, is GMD_Routing_Resources
The column, column_expression, is PROCESS_UOM.
The column, Column_type, is ALOOK.
The column, ref_application_label, is INV.
The column, ref_table_name, is MTL_UNITS_OF_MEASURE_TL
The column, ref_lookup_column_name, is UOM_CODE.
The column, ref_description_column_name, is UOM_CODE.
Resource_Usage_UOM
The column, view_label, is GMD_Routing_Resources.
The column, column_expression, is USAGE_UM.
The column, Column_type, is ALOOK.
The column, ref_application_label, is INV.
The column, ref_table_name, is MTL_UNITS_OF_MEASURE_TL
The column, ref_lookup_column_name, is UOM_CODE.
The column, ref_description_column_name, is UOM_CODE.
It is always good to have some perspective on how this undocumented column type, ‘ALOOK’, behaves by looking at this query:
select
*
from
n_view_column_templates
where
column_type= 'ALOOK'
I looked up how these column types express themselves in the views and in the end user layer.
In examining these columns, it appears that these are very dissimilar to the LOOK column type. It appears that reference columns exist for the purpose of communicating to the reporting tool information on the list of values. I will have to do more examining of this.
In any event, for this particular view (and associated with our version of apps and our implementation), these columns are wrong and need to be fixed.
While I wait for Noetix to provide a correction to this, here is my correction script (called by xu2):
update N_view_column_templates
set column_expression = 'RESOURCE_PROCESS_UOM'
where view_label = 'GMD_Routing_Resources'
and column_label = 'Resource_Process_UOM';
COMMIT;
update N_view_column_templates
set column_expression = 'RESOURCE_USAGE_UOM'
where view_label = 'GMD_Routing_Resources'
and column_label = 'Resource_Usage_UOM';
COMMIT;
I first run this script using SQL Developer just to confirm the syntactical accuracy. Next, I run this script through the NVA. My testing of this validates its accuracy.
Resource_Process_UOM
Resource_Usage_UOM
When I research the table they are associated with,GMD_OPERATION_RESOURCES, it appears that between Oracle and our implementers the following holds:
-GMD_OPERATION_RESOURCES.RESOURCE_PROCESS_UOM stores UOM information on resource processes and not GMD_OPERATION_RESOURCES.PROCESS_UOM.
-GMD_OPERATION_RESOURCES.RESOURCE_USAGE_UOM stores resource usage UOM information and not GMD_OPERATION_RESOURCES.USAGE_UM.
When I query how the n_view_column_templates table has these columns set-up, I see the following information:
Resource_Process_UOM
The column, view_label, is GMD_Routing_Resources
The column, column_expression, is PROCESS_UOM.
The column, Column_type, is ALOOK.
The column, ref_application_label, is INV.
The column, ref_table_name, is MTL_UNITS_OF_MEASURE_TL
The column, ref_lookup_column_name, is UOM_CODE.
The column, ref_description_column_name, is UOM_CODE.
Resource_Usage_UOM
The column, view_label, is GMD_Routing_Resources.
The column, column_expression, is USAGE_UM.
The column, Column_type, is ALOOK.
The column, ref_application_label, is INV.
The column, ref_table_name, is MTL_UNITS_OF_MEASURE_TL
The column, ref_lookup_column_name, is UOM_CODE.
The column, ref_description_column_name, is UOM_CODE.
It is always good to have some perspective on how this undocumented column type, ‘ALOOK’, behaves by looking at this query:
select
*
from
n_view_column_templates
where
column_type= 'ALOOK'
I looked up how these column types express themselves in the views and in the end user layer.
In examining these columns, it appears that these are very dissimilar to the LOOK column type. It appears that reference columns exist for the purpose of communicating to the reporting tool information on the list of values. I will have to do more examining of this.
In any event, for this particular view (and associated with our version of apps and our implementation), these columns are wrong and need to be fixed.
While I wait for Noetix to provide a correction to this, here is my correction script (called by xu2):
update N_view_column_templates
set column_expression = 'RESOURCE_PROCESS_UOM'
where view_label = 'GMD_Routing_Resources'
and column_label = 'Resource_Process_UOM';
COMMIT;
update N_view_column_templates
set column_expression = 'RESOURCE_USAGE_UOM'
where view_label = 'GMD_Routing_Resources'
and column_label = 'Resource_Usage_UOM';
COMMIT;
I first run this script using SQL Developer just to confirm the syntactical accuracy. Next, I run this script through the NVA. My testing of this validates its accuracy.
Wednesday, August 4, 2010
Wednesday, July 28, 2010
Modifying Views with an Xu2 Script
I ran into a problem with the EAM_Work_Order_Resources view label recently. In working with Noetix Support, I created documentation to re-create the problem and correct this view. The end result is that the where clause has a slight problem.
My conclusion was the following:
This statement should be commented out:
" AND EMP.EFFECTIVE_START_DATE(+) = BREMP.EFFECTIVE_START_DATE"
And replaced with:
" AND EMP.START_DATE(+) = BREMP.EFFECTIVE_START_DATE"
While I wait for Noetix to fix this, I thought I would provide my users with a temporary fix to correct this.
Ultimately, I want to just comment out a line and add a new line. Here is a query I created to find this line:
select
*
from
n_view_where_templates
where 1=1
and Upper(view_label) = 'EAM_WORK_ORDER_RESOURCES'
and where_clause = 'AND EMP.EFFECTIVE_END_DATE(+) = BREMP.EFFECTIVE_END_DATE'
order by where_clause_position;
Here is my temporary XU2 script which adds a new where clause statement to all views which share this view label (while I wait for Noetix):
INSERT INTO n_view_where_templates
(view_label
,query_position
,where_clause_position
,where_clause
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('EAM_Work_Order_Resources' -- view_label
,1 -- query_position
,19.5 -- where_clause_position
,'AND EMP.START_DATE(+) = BREMP.EFFECTIVE_START_DATE' -- where_clause
, TO_CHAR(NULL) --profile_option
,'%' --product_version
,'flintstonef' --created_by
, SYSDATE --creation_date
,'flintstonef' --last_updated_by
, SYSDATE --last_update_date
);
Because I want to perpetuate this change to all views associated with this view label, an xu2 script should be used. I had problems with originally using an xu2 script to modify this and the certification course spent a lot more time regarding the modification of specific views (xu5 script). Consequently, I erroneously thought the best approach was to use an xu5 script.
This has the form:
update n_view_where_templates
set
where_clause = '--'|| where_clause
where
view_label = 'EAM_Work_Order_Resources'
and where_clause = 'AND EMP.EFFECTIVE_START_DATE(+) = BREMP.EFFECTIVE_START_DATE';
COMMIT;
I just throw these statements in SQL Developer and make sure they do not throw an error. Now that I know that there is no syntactical error, I throw these scripts into the Noetix View Administrator.
My conclusion was the following:
This statement should be commented out:
" AND EMP.EFFECTIVE_START_DATE(+) = BREMP.EFFECTIVE_START_DATE"
And replaced with:
" AND EMP.START_DATE(+) = BREMP.EFFECTIVE_START_DATE"
While I wait for Noetix to fix this, I thought I would provide my users with a temporary fix to correct this.
Ultimately, I want to just comment out a line and add a new line. Here is a query I created to find this line:
select
*
from
n_view_where_templates
where 1=1
and Upper(view_label) = 'EAM_WORK_ORDER_RESOURCES'
and where_clause = 'AND EMP.EFFECTIVE_END_DATE(+) = BREMP.EFFECTIVE_END_DATE'
order by where_clause_position;
Here is my temporary XU2 script which adds a new where clause statement to all views which share this view label (while I wait for Noetix):
INSERT INTO n_view_where_templates
(view_label
,query_position
,where_clause_position
,where_clause
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('EAM_Work_Order_Resources' -- view_label
,1 -- query_position
,19.5 -- where_clause_position
,'AND EMP.START_DATE(+) = BREMP.EFFECTIVE_START_DATE' -- where_clause
, TO_CHAR(NULL) --profile_option
,'%' --product_version
,'flintstonef' --created_by
, SYSDATE --creation_date
,'flintstonef' --last_updated_by
, SYSDATE --last_update_date
);
Because I want to perpetuate this change to all views associated with this view label, an xu2 script should be used. I had problems with originally using an xu2 script to modify this and the certification course spent a lot more time regarding the modification of specific views (xu5 script). Consequently, I erroneously thought the best approach was to use an xu5 script.
This has the form:
update n_view_where_templates
set
where_clause = '--'|| where_clause
where
view_label = 'EAM_Work_Order_Resources'
and where_clause = 'AND EMP.EFFECTIVE_START_DATE(+) = BREMP.EFFECTIVE_START_DATE';
COMMIT;
I just throw these statements in SQL Developer and make sure they do not throw an error. Now that I know that there is no syntactical error, I throw these scripts into the Noetix View Administrator.
Wednesday, June 16, 2010
Changing the Granularity of a View
Can you change the granularity of a view when you are not adding a new table to it? The most obvious scenario is when there is a Group by Flag associated with the query block which is pertinent to your version of Oracle Apps.
For instance, suppose an end user has requested to expose all of the descriptive flexfields associated with the table,” MTL_CUSTOMER_ITEM_XREFS”, associated with the view, “INV_Customer_Items”. It is important to run the get_data_tmpl.sql script for this view and see if the view is using a group by clause.
Suppose you do not have access to the get_data_tmpl.sql? Keeping in mind your version of Oracle Applications, run the following query:
SELECT
*
FROM
n_view_query_templates nqt
WHERE 1=1
and nqt.view_label = ' INV_Customer_Items’;
Since I am working with Oracle Apps 12.06 and since this query has the wildcard, ‘*’, returned for the product_version column, I am only concerned with query block 1. Also, the group by flag is ‘N’.
Consequently, for this example, the answer is, “No.” Satisfying a requirement to add the descriptive flexfields will not change the granularity.
For the sake of it, here is my DML to add all the descriptive flexfields for the “MTL_CUSTOMER_ITEM_XREFS” table to this view:
INSERT INTO n_view_column_templates
(view_label
,query_position
,column_label
,table_alias
,column_expression
,column_position
,column_type
,description
,group_by_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
,last_updated_by
,last_update_date)
VALUES
('INV_Customer_Items' -- view_label
,1 -- query_position
,'XREFS' -- column_label
,'XREFS' -- table_alias
,'All_FF' -- column_expression
,45 -- column_position
,'ATTR' -- column_type
,'All the table flexfields' -- description
,NULL -- group_by_flag
,NULL -- profile_option
,'%' -- product_version
,NULL -- ref_application_label
,NULL -- ref_table_name
,NULL -- ref_lookup_column_name
,NULL -- ref_description_column_name
,NULL -- ref_lookup_type
,'baconp' -- created_by
,SYSDATE -- creation_date
,'baconp' -- last_updated_by
,SYSDATE) -- last_update_date
;
COMMIT;
For instance, suppose an end user has requested to expose all of the descriptive flexfields associated with the table,” MTL_CUSTOMER_ITEM_XREFS”, associated with the view, “INV_Customer_Items”. It is important to run the get_data_tmpl.sql script for this view and see if the view is using a group by clause.
Suppose you do not have access to the get_data_tmpl.sql? Keeping in mind your version of Oracle Applications, run the following query:
SELECT
*
FROM
n_view_query_templates nqt
WHERE 1=1
and nqt.view_label = ' INV_Customer_Items’;
Since I am working with Oracle Apps 12.06 and since this query has the wildcard, ‘*’, returned for the product_version column, I am only concerned with query block 1. Also, the group by flag is ‘N’.
Consequently, for this example, the answer is, “No.” Satisfying a requirement to add the descriptive flexfields will not change the granularity.
For the sake of it, here is my DML to add all the descriptive flexfields for the “MTL_CUSTOMER_ITEM_XREFS” table to this view:
INSERT INTO n_view_column_templates
(view_label
,query_position
,column_label
,table_alias
,column_expression
,column_position
,column_type
,description
,group_by_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
,last_updated_by
,last_update_date)
VALUES
('INV_Customer_Items' -- view_label
,1 -- query_position
,'XREFS' -- column_label
,'XREFS' -- table_alias
,'All_FF' -- column_expression
,45 -- column_position
,'ATTR' -- column_type
,'All the table flexfields' -- description
,NULL -- group_by_flag
,NULL -- profile_option
,'%' -- product_version
,NULL -- ref_application_label
,NULL -- ref_table_name
,NULL -- ref_lookup_column_name
,NULL -- ref_description_column_name
,NULL -- ref_lookup_type
,'baconp' -- created_by
,SYSDATE -- creation_date
,'baconp' -- last_updated_by
,SYSDATE) -- last_update_date
;
COMMIT;
Friday, June 11, 2010
Descriptive Flexfields Matching a Pattern
I was looking at descriptive flexfields and it forced me to look at my Noetix Certification Course notes. Without looking at the view, fnd_descr_flex_view, and constraining it to the Oracle Application table which contains the descriptive flexfields (e.g. mtl_lot_numbers), some of the methods associated with exposing descriptive flexfields do not make sense (or at least are much easier to understand and implement).
For instance, if I perform the query:
SELECT
fdfv.*
FROM
fnd_descr_flex_view fdfv
WHERE 1=1
AND fdfv.application_table_name = 'MTL_LOT_NUMBERS'
I see all of the flexfields that are associated with the INV. MTL_LOT_NUMBERS table. Here they are:
Billet Length (IN) C_ATTRIBUTE1
Deviation Number C_ATTRIBUTE10
Customer Name C_ATTRIBUTE11
Conversion Indicator C_ATTRIBUTE18
Input PC Grandparent C_ATTRIBUTE19
Input Child Lot C_ATTRIBUTE2
Input GC Grandparent C_ATTRIBUTE20
Billet Heat # C_ATTRIBUTE3
Billet Cross Section C_ATTRIBUTE4
Disposition Code C_ATTRIBUTE5
PBL Supplier Parent Lot C_ATTRIBUTE6
Actual Billet Weight (LBS) C_ATTRIBUTE7
Input Parent Lot Number C_ATTRIBUTE8
Customer Lot Number C_ATTRIBUTE9
Consequently, if I look at the application column name in the result set, I see all of the descriptive flexfields application column names associated with this table which have been frozen and enabled during the most recent view regeneration.
In my most recent modification requirement, I wanted to expose all DFFs that match the pattern, C_ATTRIBUTE%, consequently I used the matching technique. Here is the DML for this:
INSERT INTO n_view_column_templates
(view_label
, query_position
, column_label
, table_alias
, column_expression
, column_position
, 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
,last_updated_by
,last_update_date
)
VALUES
('INV_Lot_Status_History' --view_label
,1 --query_position number
,'LOTS' --column_label
,'LOTS' --table_alias
,'LOTS' --column_expression
,41 --column_position
,'ATTR' --column_type
,'Lot number descriptive flexfield (MTL_Lot_Numbers).' --description
,'N' --group_by_flag
,'N' --gen_search_by_col_flag
, NULL --profile_option
,'*' --product_version
,NULL -- ref_application_label
,NULL --ref_table_name
,'C_ATTRIBUTE%' --ref_lookup_column_name
,NULL --ref_description_column_name
,NULL -- ref_lookup_type
,'flinstonef' -- created_by
, SYSDATE --creation_date
,'flinstonef' --last_updated_by
, SYSDATE --last_update_date
);
Commit;
Instead of running a full blown regeneration to test my DML script, often times I just go to my development instance and start SQL Developer and run this script using my Noetix admin database account. If it throws an exception, I know that something is not correct (this sure beats wasting time running the Noetix View Administrator).
With this method of testing my DML script, if it does modify the appropriate table (e.g. n_view_column_templates), then when I run the regeneration, my modification will be blown away (this table will be dropped and re-created during the regeneration).
All in all, this was not too hard to add some descriptive flexfields.
For instance, if I perform the query:
SELECT
fdfv.*
FROM
fnd_descr_flex_view fdfv
WHERE 1=1
AND fdfv.application_table_name = 'MTL_LOT_NUMBERS'
I see all of the flexfields that are associated with the INV. MTL_LOT_NUMBERS table. Here they are:
Billet Length (IN) C_ATTRIBUTE1
Deviation Number C_ATTRIBUTE10
Customer Name C_ATTRIBUTE11
Conversion Indicator C_ATTRIBUTE18
Input PC Grandparent C_ATTRIBUTE19
Input Child Lot C_ATTRIBUTE2
Input GC Grandparent C_ATTRIBUTE20
Billet Heat # C_ATTRIBUTE3
Billet Cross Section C_ATTRIBUTE4
Disposition Code C_ATTRIBUTE5
PBL Supplier Parent Lot C_ATTRIBUTE6
Actual Billet Weight (LBS) C_ATTRIBUTE7
Input Parent Lot Number C_ATTRIBUTE8
Customer Lot Number C_ATTRIBUTE9
Consequently, if I look at the application column name in the result set, I see all of the descriptive flexfields application column names associated with this table which have been frozen and enabled during the most recent view regeneration.
In my most recent modification requirement, I wanted to expose all DFFs that match the pattern, C_ATTRIBUTE%, consequently I used the matching technique. Here is the DML for this:
INSERT INTO n_view_column_templates
(view_label
, query_position
, column_label
, table_alias
, column_expression
, column_position
, 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
,last_updated_by
,last_update_date
)
VALUES
('INV_Lot_Status_History' --view_label
,1 --query_position number
,'LOTS' --column_label
,'LOTS' --table_alias
,'LOTS' --column_expression
,41 --column_position
,'ATTR' --column_type
,'Lot number descriptive flexfield (MTL_Lot_Numbers).' --description
,'N' --group_by_flag
,'N' --gen_search_by_col_flag
, NULL --profile_option
,'*' --product_version
,NULL -- ref_application_label
,NULL --ref_table_name
,'C_ATTRIBUTE%' --ref_lookup_column_name
,NULL --ref_description_column_name
,NULL -- ref_lookup_type
,'flinstonef' -- created_by
, SYSDATE --creation_date
,'flinstonef' --last_updated_by
, SYSDATE --last_update_date
);
Commit;
Instead of running a full blown regeneration to test my DML script, often times I just go to my development instance and start SQL Developer and run this script using my Noetix admin database account. If it throws an exception, I know that something is not correct (this sure beats wasting time running the Noetix View Administrator).
With this method of testing my DML script, if it does modify the appropriate table (e.g. n_view_column_templates), then when I run the regeneration, my modification will be blown away (this table will be dropped and re-created during the regeneration).
All in all, this was not too hard to add some descriptive flexfields.
Subscribe to:
Posts (Atom)