The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(*)
INTO v_cnt
FROM az_product_phases azpp
WHERE application_id = p_application_id;
fnd_message.set_token('AZW_ERROR_STMT','select count(*) from az_product_phases');
SELECT wav.item_type, wav.name,
waav.text_default application_id,
waav1.text_default display_order,
waav2.text_default ctxt_type,
waav3.text_default parent_id,
waav4.text_default process_type
FROM wf_activities_vl wav,
wf_activity_attributes_vl waav,
wf_activity_attributes_vl waav1,
wf_activity_attributes_vl waav2,
wf_activity_attributes_vl waav3,
wf_activity_attributes_vl waav4
WHERE wav.item_type like 'AZW%'
AND wav.type = 'PROCESS'
AND wav.runnable_flag = 'Y'
AND wav.end_date is null
AND waav.activity_item_type = wav.item_type
AND waav.activity_name = wav.name
AND waav.name = 'AZW_IA_WFPROD'
AND waav.activity_version =
(SELECT MAX(activity_version)
FROM wf_activity_attributes_vl
WHERE activity_item_type = wav.item_type
AND activity_name = wav.name
AND name = 'AZW_IA_WFPROD')
AND waav1.activity_item_type = wav.item_type
AND waav1.activity_name = wav.name
AND waav1.name = 'AZW_IA_VAWM'
AND waav1.text_default IS NOT NULL
AND waav1.activity_version =
(SELECT MAX(activity_version)
FROM wf_activity_attributes_vl
WHERE activity_item_type = wav.item_type
AND activity_name = wav.name
AND name = 'AZW_IA_VAWM')
AND waav2.activity_item_type = wav.item_type
AND waav2.activity_name = wav.name
AND waav2.name = 'AZW_IA_CTXTYP'
AND exists
(SELECT lookup_code
FROM fnd_lookups
where lookup_code = waav2.text_default
AND lookup_type = 'AZ_CONTEXT_TYPE')
AND waav2.activity_version =
(SELECT MAX(activity_version)
FROM wf_activity_attributes_vl
WHERE activity_item_type = wav.item_type
AND activity_name = wav.name
AND name = 'AZW_IA_CTXTYP')
AND waav3.activity_item_type = wav.item_type
AND waav3.activity_name = wav.name
AND waav3.name = 'AZW_IA_PARENTID'
AND exists
(SELECT group_id
FROM az_groups ag
WHERE ag.group_id = waav3.text_default
AND ag.process_type = fnd_profile.value('AZ_CURRENT_MODE'))
AND waav3.activity_version =
(SELECT MAX(activity_version)
FROM wf_activity_attributes_vl
WHERE activity_item_type = wav.item_type
AND activity_name = wav.name
AND name = 'AZW_IA_PARENTID')
-- Start : added by Swarup
AND waav4.activity_item_type = wav.item_type
AND waav4.activity_name = wav.name
AND waav4.name = 'AZW_IA_FLOWTYPE'
AND exists
(SELECT meaning
FROM fnd_lookups
where lookup_code = waav4.text_default
AND lookup_type = 'AZ_PROCESS_TYPE')
AND waav4.activity_version =
(SELECT MAX(activity_version)
FROM wf_activity_attributes_vl
WHERE activity_item_type = wav.item_type
AND activity_name = wav.name
AND name = 'AZW_IA_FLOWTYPE')
-- End : added by Swarup
ORDER BY waav1.text_default;
SELECT distinct application_id
FROM fnd_application;
DELETE
FROM az_product_flows;
INSERT INTO az_product_phases
(application_id, phase, last_update_date, last_updated_by,
creation_date, created_by)
VALUES(v_installed_prod_id, 1, sysdate, 1, sysdate, 1);
--DBMS_OUTPUT.PUT_LINE('trying to insert process :' || id_count);
INSERT INTO az_product_flows(application_id, item_type, process_name,
display_order, context_type, parent_id, process_type)
VALUES(v_application_id, v_item_type, v_process_name,
v_display_number , v_ctxt_type, v_parent_id,
NVL(v_process_type,'IMP'));
--DBMS_OUTPUT.PUT_LINE('inserted: '||v_process_name);
DELETE from az_processes ap
WHERE not exists
(SELECT item_type from AZ_PRODUCT_FLOWS apf
where apf.item_type = ap.item_type)
OR not exists
(SELECT process_name from AZ_PRODUCT_FLOWS apf
where apf.process_name = ap.process_name);
fnd_message.set_token('AZW_ERROR_STMT','delete from az_processes');