[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_context( ctx_type IN VARCHAR2,
meaning IN VARCHAR2,
msg IN OUT message_tbl_t,
i IN OUT INTEGER,
p_disp_order IN INTEGER);
PROCEDURE insert_groups_for_context( ctx_type IN VARCHAR2,
msg IN OUT message_tbl_t,
i IN OUT INTEGER);
PROCEDURE insert_proc_task_for_context( msg IN OUT message_tbl_t,
i IN OUT INTEGER );
SELECT COUNT(*)
INTO v_cnt
FROM az_groups
WHERE hierarchy_parent_id = p_group_id
AND process_type = g_current_mode;
fnd_message.set_token('AZW_ERROR_STMT','select count(*) from az_groups');
SELECT group_id
FROM az_groups
WHERE hierarchy_parent_id is not null
START WITH group_id = p_process_group
AND process_type = g_current_mode
CONNECT BY PRIOR group_id = hierarchy_parent_id
AND process_type = g_current_mode
ORDER BY group_id;
INSERT INTO az_webform_messages (mesg)
VALUES (v_group_id);
fnd_message.set_token('AZW_ERROR_STMT','insert into az_webform_messages');
SELECT DISTINCT azp.item_type,
azp.process_name,
azp.context_id,
azp.display_order,
azp.status_code,
azp.context_type,
azp.context_name,
azp.comments,
azp.parent_id,
azfpv.phase,
wav.display_name
FROM az_processes azp,
az_flow_phases_v azfpv,
wf_activities_vl wav
WHERE azfpv.item_type = azp.item_type
AND azfpv.process_name = azp.process_name
AND azp.parent_id = p_process_parent_id
AND azp.process_type = g_current_mode
AND wav.item_type = azp.item_type
AND wav.name = azp.process_name
AND wav.end_date is NULL
ORDER BY 4, 6, 7;
SELECT DISTINCT azp.item_type,
azp.process_name,
azp.context_id,
azp.display_order,
azp.status_code,
azp.context_type,
azp.context_name,
azp.comments,
azp.parent_id,
azfpv.phase,
wav.display_name
FROM az_processes azp,
az_flow_phases_v azfpv,
wf_activities_vl wav
WHERE azfpv.item_type = azp.item_type
AND azfpv.process_name = azp.process_name
AND azp.process_type = g_current_mode
AND wav.item_type = azp.item_type
AND wav.name = azp.process_name
AND wav.end_date is NULL
ORDER BY wav.display_name, azp.context_type, azp.context_name;
SELECT DISTINCT azp.item_type,
azp.process_name,
azp.context_id,
azp.display_order,
azp.status_code,
azp.context_type,
azp.context_name,
azp.comments,
azp.parent_id,
azfpv.phase,
wav.display_name
FROM az_processes azp,
az_flow_phases_v azfpv,
wf_activities_vl wav,
az_webform_messages azm
WHERE azfpv.item_type = azp.item_type
AND azfpv.process_name = azp.process_name
AND azp.process_type = g_current_mode
AND wav.item_type = azp.item_type
AND wav.name = azp.process_name
AND wav.end_date is NULL
AND azp.parent_id = azm.mesg
ORDER BY wav.display_name, azp.context_type, azp.context_name;
SELECT SUBSTR(group_id, 1, 60),
SUBSTR(hierarchy_parent_id, 1, 60),
lookup_code, display_order, application_id
FROM az_groups
WHERE hierarchy_parent_id = process_group
AND process_type = g_current_mode
ORDER BY 1;
-- insert this group as a node
group_rollup_flag := 'N';
-- dbms_output.put_line('Inserted group : ' || v_node_id);
-- insert this group as a node
group_rollup_flag := 'N';
-- dbms_output.put_line('Inserted group : ' || v_node_id);
SELECT lookup_code, application_id, display_order
INTO v_lookup_code, v_application_id, v_display_order
FROM az_groups
WHERE group_id = v_node_id
AND process_type = g_current_mode;
fnd_message.set_token('AZW_ERROR_STMT','select lookup_code ... from az_groups');
INSERT INTO az_webform_messages (mesg)
VALUES (v_node_id);
fnd_message.set_token('AZW_ERROR_STMT','insert into az_webform_messages');
select distinct language_code, nls_language
into v_language_code, v_language
from fnd_languages
where NLS_LANGUAGE =
SUBSTR(USERENV('LANGUAGE'), 1, INSTR(USERENV('LANGUAGE'), '_')-1);
fnd_message.set_token('AZW_ERROR_STMT','select language_code ... from fnd_languages');
SELECT SUBSTRB(text, 0, 8)
INTO v_days
FROM wf_resources
WHERE language = v_language_code
AND type = 'WFTKN'
AND name = 'DAYS';
fnd_message.set_token('AZW_ERROR_STMT','select text into v_days from wf_resources');
SELECT SUBSTRB(text, 0, 8)
INTO v_done
FROM wf_resources
WHERE language = v_language_code
AND type = 'WFTKN'
AND name = 'WFMON_DONE';
fnd_message.set_token('AZW_ERROR_STMT','select text into v_done from wf_resources');
SELECT SUBSTRB(text, 0, 8)
INTO v_skip
FROM wf_resources
WHERE language = v_language_code
AND type = 'WFTKN'
AND name = 'WFMON_SKIP';
fnd_message.set_token('AZW_ERROR_STMT','select text into v_skip from wf_resources');
SELECT SUBSTRB(text, 0, 16)
INTO v_priority_display
FROM wf_resources
WHERE language = v_language_code
AND type = 'WFTKN'
AND name = 'PRIORITY';
fnd_message.set_token('AZW_ERROR_STMT','select text into v_priority_display from wf_resources');
SELECT SUBSTR(group_id, 1, 60),
SUBSTR(dependency_parent_id, 1, 60),
display_order, application_id, lookup_code
FROM az_groups
WHERE hierarchy_parent_id is null
AND process_type = g_current_mode
ORDER BY 1;
SELECT SUBSTR(group_id, 1, 60),
SUBSTR(hierarchy_parent_id, 1, 60),
display_order, application_id, lookup_code
FROM az_groups
WHERE hierarchy_parent_id is not null
AND process_type = g_current_mode
ORDER BY 1;
SELECT DISTINCT azp.item_type,
azp.process_name,
azp.context_id,
azp.display_order,
azp.status_code,
azp.context_type,
azp.context_name,
azp.comments,
azp.parent_id,
azfpv.phase,
wav.display_name
FROM wf_activities_vl wav,
az_processes azp,
az_flow_phases_v azfpv
WHERE azfpv.item_type = azp.item_type
AND azfpv.process_name = azp.process_name
AND azp.process_type = g_current_mode
AND wav.item_type = azp.item_type
AND wav.name = azp.process_name
AND wav.end_date is NULL
ORDER BY 4, 6, 7;
SELECT DISTINCT azp.item_type,
azp.process_name,
azp.context_id,
azp.display_order,
azp.status_code,
azp.context_type,
azp.context_name,
azp.comments,
azp.parent_id,
azfpv.phase,
wav.display_name
FROM wf_activities_vl wav,
az_processes azp,
az_flow_phases_v azfpv
WHERE azfpv.item_type = azp.item_type
AND azfpv.process_name = azp.process_name
AND azp.process_type = g_current_mode
AND wav.item_type = azp.item_type
AND wav.name = azp.process_name
AND wav.end_date is NULL
ORDER BY wav.display_name, azp.context_type, azp.context_name;
--dbms_output.put_line('inserted process ' || label);
SELECT distinct item_type
FROM az_processes;
SELECT DISTINCT wfi.item_key,
wfi.root_activity,
wfi.begin_date,
round( months_between(sysdate, wfi.begin_date)* 31),
wias.activity_status,
wiav2.text_value,
azpfv.phase,
azp.status_code,
azp.item_type,
azp.context_id
FROM wf_item_attribute_values wiav1,
wf_item_attribute_values wiav2,
wf_item_activity_statuses wias,
wf_process_activities wpa,
az_processes azp,
az_flow_phases_v azpfv,
wf_items wfi
WHERE wfi.item_type = azp.item_type
AND wfi.root_activity = azp.process_name
AND azpfv.item_type = azp.item_type
AND azpfv.process_name = azp.process_name
AND wiav1.item_type = azp.item_type
AND wiav1.item_key = wfi.item_key
AND wiav1.name = 'AZW_IA_CTXT_ID'
AND wiav1.text_value = to_char(azp.context_id)
AND wiav2.item_type = wfi.item_type
AND wiav2.item_key = wfi.item_key
AND wiav2.name = 'AZW_IA_ROLE'
AND wias.item_type = wfi.item_type
AND wias.item_key = wfi.item_key
AND wpa.instance_id = wias.process_activity
AND wpa.activity_name = wfi.root_activity
AND wpa.process_item_type = azp.item_type
AND wpa.process_name = 'ROOT'
AND azp.item_type = v_item_type_p
ORDER BY wfi.begin_date;
SELECT COUNT(*)
FROM wf_item_activity_statuses wias, wf_notification_attributes wna,
wf_notifications wn
WHERE wias.item_type = v_item_type
AND wias.item_key = v_item_key
AND wias.notification_id IS NOT NULL
AND wna.notification_id = wias.notification_id
AND wn.notification_id = wna.notification_id
AND wn.status = 'CLOSED'
AND wna.name = 'RESULT'
AND wna.text_value LIKE '%DONE%';
SELECT COUNT(*)
FROM wf_item_activity_statuses wias, wf_notification_attributes wna,
wf_notifications wn
WHERE wias.item_type = v_item_type
AND wias.item_key = v_item_key
AND wias.notification_id is not NULL
AND wna.notification_id = wias.notification_id
AND wn.notification_id = wna.notification_id
AND wn.status = 'CLOSED'
AND wna.name = 'RESULT'
AND wna.text_value like '%SKIP%';
SELECT wav.display_name,
round( months_between(sysdate, wias.begin_date)* 31),
wias.begin_date
FROM wf_process_activities wpa, wf_item_activity_statuses wias,
wf_activities_vl wav
WHERE wias.item_type = v_item_type
AND wias.item_key = v_item_key
AND wias.process_activity = wpa.instance_id
AND wpa.activity_name = wav.name
AND wpa.activity_item_type = wav.item_type
AND wpa.process_name <> 'ROOT'
AND wpa.activity_name <> 'START'
AND wav.begin_date is not NULL
AND wav.end_date is NULL
AND wav.type = 'NOTICE'
ORDER BY wias.begin_date desc;
-- dbms_output.put_line('inserting task');
fnd_message.set_token('AZW_ERROR_STMT','while inserting task to hierarchy table');
SELECT lookup_code,
meaning,
DECODE(lookup_code, 'NONE', 1, 'BG', 2, 'SOB', 3, 'OU', 4, 5)
display_order
FROM fnd_lookups
WHERE lookup_type = 'AZ_CONTEXT_TYPE'
ORDER BY display_order;
select distinct language_code, nls_language
into v_language_code, v_language
from fnd_languages
where NLS_LANGUAGE =
SUBSTR(USERENV('LANGUAGE'), 1, INSTR(USERENV('LANGUAGE'), '_')-1);
fnd_message.set_token('AZW_ERROR_STMT','select language_code .. from fnd_languages');
hierarchy_table.delete;
msg.delete;
ctx_table.delete ;
insert_context( v_context_type, v_meaning, msg, i, disp_order);
-- Now insert groups for each context
-- unnecessary groups will be rolled up by java
-- from hierarchy table
insert_groups_for_context(v_context_type, msg, i);
-- Now insert processes/tasks for each their respective context
insert_proc_task_for_context(msg, i);
INSERT INTO az_webform_messages (mesg)
VALUES (msg(k));
fnd_message.set_token('AZW_ERROR_STMT','insert into az_webform_messages');
INSERT_PROC_TASK_FOR_CONTEXT
private procedure inserts all processes and tasks
---------------------------------------------------------------*/
PROCEDURE insert_proc_task_for_context(
msg IN OUT message_tbl_t,
i IN OUT INTEGER ) IS
p_context_name VARCHAR2(200);
fnd_message.set_token('AZW_ERROR_PROC','azw_hier.insert_proc_task_for_context');
END insert_proc_task_for_context;
* INSERT_CONTEXT
Private procedure : added by swarup for context sort
This procedure adds five node at the root level
1. NONE
2. BG
3. SOB
4. OU
5. IO
Then all contexts are added below respective types
EXCEPT 'NONE', which doesn't have context
-------------------------------------------------------------- */
PROCEDURE insert_context( ctx_type IN VARCHAR2,
meaning IN VARCHAR2,
msg IN OUT message_tbl_t,
i IN OUT INTEGER,
p_disp_order IN INTEGER
) IS
l_disp_order INTEGER DEFAULT 0;
fnd_message.set_token('AZW_ERROR_PROC','azw_hier.insert_context');
END insert_context;
select group_id from az_groups
where
hierarchy_parent_id = p_node_id
and process_type = g_current_mode;
SELECT count(distinct context_type)
INTO v_count
FROM az_processes
WHERE context_type = ctx_type
AND process_type = g_current_mode
AND parent_id = p_node_id ;
* INSERT_NODES_FOR_CONTEXT_TYPE
Private procedure : added by swarup for context sort
This procedure loops thru all the nodes of hierarchy table
( as retrieved as a hieararchy ), adds all groups for each
context type : name pair , and processes/tasks for their
own context type : name pair only
-------------------------------------------------------------- */
PROCEDURE insert_groups_for_context(
ctx_type IN VARCHAR2,
msg IN OUT message_tbl_t,
i IN OUT INTEGER
) IS
parent_id VARCHAR2(220); -- id := context_name || context_type
fnd_message.set_token('AZW_ERROR_PROC','azw_hier.insert_groups_for_context');
END insert_groups_for_context;
SELECT DISTINCT context_id, context_name
FROM az_processes
WHERE
context_type = ctx_type
ORDER BY context_name;
ctx_table.delete;
fnd_message.set_token('AZW_ERROR_STMT','cursor select from az_processes ');