[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_hierarchy_status(p_item_type IN VARCHAR2,
p_process_name IN VARCHAR2,
p_context_id IN VARCHAR2);
SELECT azg1.group_id
INTO v_parent_gr_id
FROM az_groups azg1, az_groups azg2
WHERE azg2.group_id = p_parent_id
AND azg1.process_type = azg2.process_type
AND azg1.process_type = g_current_mode
AND azg1.group_id = azg2.hierarchy_parent_id;
fnd_message.set_token('AZW_ERROR_STMT','select group_id from az_groups');
SELECT count(*)
INTO v_cnt
FROM az_processes ap
WHERE ap.item_type = proc.item_type
AND ap.process_name = proc.process_name
AND ap.context_id = p_ctx_id;
fnd_message.set_token('AZW_ERROR_STMT','select count(*) from az_processes');
SELECT parent_id
INTO v_process_parent_id
FROM az_processes
WHERE item_type = v_process_item_type
AND process_name = v_process_name
AND context_id = v_process_ctxt_id;
fnd_message.set_token('AZW_ERROR_STMT','select parent_id from az_processes');
SELECT color_code
INTO v_group_color
FROM az_groups
WHERE group_id = v_process_parent_id
AND process_type = g_current_mode;
fnd_message.set_token('AZW_ERROR_STMT','select color_code from az_groups');
SELECT waav.text_default
INTO v_ctxt_type
FROM wf_activity_attributes_vl waav
WHERE waav.activity_item_type = p_item_type
AND waav.activity_name = p_process_name
AND waav.name = 'AZW_IA_CTXTYP'
AND waav.activity_version =
(SELECT MAX(activity_version)
FROM wf_activity_attributes_vl
WHERE activity_item_type = p_item_type
AND activity_name = p_process_name
AND name = 'AZW_IA_CTXTYP');
fnd_message.set_token('AZW_ERROR_STMT','select text_default from wf_activity_attributes_vl');
SELECT sql_statement
INTO sqlstatement
FROM az_contexts_sql
WHERE context = ctx_type
AND purpose = 'GET_NAME';
SELECT name INTO v_ctxt_name
FROM per_business_groups
WHERE date_from < SYSDATE
AND (date_to IS NULL
OR date_to > SYSDATE)
AND organization_id = ctx_id;
fnd_message.set_token('AZW_ERROR_STMT','select name from per_business_groups');
SELECT organization_name INTO v_ctxt_name
FROM org_organization_definitions
WHERE user_definition_enable_date < SYSDATE
AND (disable_date IS NULL
OR disable_date > SYSDATE)
AND organization_id = ctx_id;
fnd_message.set_token('AZW_ERROR_STMT','select name from org_organization_definitions');
SELECT name INTO v_ctxt_name
FROM hr_operating_units
WHERE organization_id = ctx_id
AND date_from < SYSDATE
AND (date_to IS NULL
OR date_to > SYSDATE);
fnd_message.set_token('AZW_ERROR_STMT','select name from hr_operating_units');
SELECT name INTO v_ctxt_name
FROM gl_sets_of_books
WHERE set_of_books_id = ctx_id;
fnd_message.set_token('AZW_ERROR_STMT','select name from gl_sets_of_books');
fnd_message.set_token('AZW_ERROR_STMT','select name from sy_orgn_mst');
SELECT application_name
INTO v_application_name
FROM fnd_application_vl
WHERE application_id = appl_id;
fnd_message.set_token('AZW_ERROR_STMT','select application_name from fnd_application_vl');
SELECT meaning
INTO v_meaning
FROM fnd_lookups
WHERE lookup_type = 'AZ_PROCESS_GROUPS'
AND lookup_code = code;
fnd_message.set_token('AZW_ERROR_STMT','select meaning from fnd_lookups');
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 substr(text, 1, 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 into v_days');
SELECT substr(text, 1, 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 into v_done');
SELECT substr(text, 1, 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 into v_skip');
SELECT organization_id, name
FROM per_business_groups
WHERE date_from < SYSDATE
AND (date_to IS NULL
OR date_to > SYSDATE)
ORDER BY organization_id;
SELECT organization_id, organization_name
FROM org_organization_definitions
WHERE user_definition_enable_date < SYSDATE
AND (disable_date IS NULL
OR disable_date > SYSDATE)
ORDER BY organization_id;
SELECT organization_id, name
FROM hr_operating_units
WHERE date_from < SYSDATE
AND (date_to IS NULL
OR date_to > SYSDATE)
ORDER BY organization_id;
SELECT set_of_books_id, name
FROM gl_sets_of_books
ORDER BY set_of_books_id;
SELECT sql_statement
INTO sqlstatement
FROM az_contexts_sql
WHERE context = ctx_type
AND purpose = 'POPULATE';
SELECT distinct azpf.item_type, azpf.process_name, azpf.parent_id,
azpf.context_type, azpf.display_order, azpf.process_type
FROM az_product_flows azpf, fnd_product_installations fpi
WHERE azpf.application_id = fpi.application_id
AND fpi.status = 'I';
PROCEDURE insert_az_processes(proc IN process_rec_t,ctxts IN context_tbl_t) IS
BEGIN
FOR j IN 1..ctxts.COUNT LOOP
IF process_not_found(proc, ctxts(j).context_id) THEN
BEGIN
INSERT INTO az_processes(item_type, process_name, context_id,
display_order, complete_flag,
context_type, context_name, comments,parent_id,
status_code, process_type)
VALUES (proc.item_type, proc.process_name,
ctxts(j).context_id, proc.display_order, 'N', proc.context_type,
ctxts(j).context_name, NULL, proc.parent_id,
'N', proc.process_type);
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.insert_az_processes(param1, param2)');
fnd_message.set_token('AZW_ERROR_STMT','insert into az_processes');
UPDATE az_processes
SET context_name = ctxts(j).context_name
WHERE item_type = proc.item_type
AND process_name = proc.process_name
AND context_id = ctxts(j).context_id
AND context_name <> ctxts(j).context_name;
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.insert_az_processes(param1, param2)');
fnd_message.set_token('AZW_ERROR_STMT','update az_processes');
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.insert_az_processes(param1, param2)');
END insert_az_processes;
PROCEDURE insert_az_processes(proc IN process_rec_t) IS
BEGIN
IF process_not_found(proc, -1) THEN
BEGIN
INSERT INTO az_processes (item_type, process_name, context_id,
display_order, complete_flag,
context_type, context_name, comments, parent_id,
status_code, process_type)
VALUES (proc.item_type, proc.process_name, -1, proc.display_order, 'N',
'NONE', NULL, NULL, proc.parent_id,
'N', proc.process_type);
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.insert_az_processes(param1)');
fnd_message.set_token('AZW_ERROR_STMT','insert into az_processes');
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.insert_az_processes(param1)');
END insert_az_processes;
insert_az_processes(processes(i), bg_ctx);
insert_az_processes(processes(i), io_ctx);
insert_az_processes(processes(i), ou_ctx);
insert_az_processes(processes(i), sob_ctx);
insert_az_processes(processes(i), opmcom_ctx);
insert_az_processes(processes(i), opmorg_ctx);
insert_az_processes(processes(i));
SELECT sql_statement
INTO sqlstatement
FROM az_contexts_sql
WHERE context = ctx_type
AND purpose = 'DELETE';
SELECT ap.item_type, ap.process_name, ap.context_id
FROM az_processes ap,
per_business_groups pbg
WHERE ap.context_type = 'BG'
AND ap.process_type = g_current_mode
AND ap.context_id > -1
AND ap.context_id = pbg.organization_id
AND (pbg.date_to IS NOT NULL
AND pbg.date_from IS NOT NULL
AND (pbg.date_from > SYSDATE
OR pbg.date_to < SYSDATE));
SELECT ap.item_type, ap.process_name, ap.context_id
FROM az_processes ap,
org_organization_definitions ood
WHERE ap.context_type = 'IO'
AND ap.process_type = g_current_mode
AND ap.context_id > -1
AND ap.context_id = ood.organization_id
AND (ood.user_definition_enable_date IS NOT NULL
AND ood.disable_date IS NOT NULL
AND (ood.user_definition_enable_date > SYSDATE
OR ood.disable_date < SYSDATE));
SELECT ap.item_type, ap.process_name, ap.context_id
FROM az_processes ap,
hr_operating_units hou
WHERE ap.context_type = 'OU'
AND ap.process_type = g_current_mode
AND ap.context_id > -1
AND ap.context_id = hou.organization_id
AND (hou.date_from IS NOT NULL
AND hou.date_to IS NOT NULL
AND (hou.date_from > SYSDATE
OR hou.date_to < SYSDATE));
DELETE from az_processes
WHERE context_type = 'BG'
AND context_id not in
( select distinct organization_id
from per_business_groups);
DELETE from az_processes
WHERE context_type = 'IO'
AND context_id not in
( select distinct organization_id
from org_organization_definitions);
DELETE from az_processes
WHERE context_type = 'OU'
AND context_id not in
( select distinct organization_id
from hr_operating_units);
SELECT COUNT(*)
INTO cnt
FROM az_processes;
fnd_message.set_token('AZW_ERROR_STMT','select count(*) from az_processes');
DELETE from az_processes ap
WHERE ap.item_type = invalid_process.item_type
AND ap.process_name = invalid_process.process_name
AND ap.context_id = invalid_process.context_id;
DELETE from az_processes ap
WHERE ap.item_type = invalid_process.item_type
AND ap.process_name = invalid_process.process_name
AND ap.context_id = invalid_process.context_id;
DELETE from az_processes ap
WHERE ap.item_type = invalid_process.item_type
AND ap.process_name = invalid_process.process_name
AND ap.context_id = invalid_process.context_id;
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');
group_status_tbl.delete;
update_hierarchy_status(group_status_tbl(i).item_type,
group_status_tbl(i).process_name,
group_status_tbl(i).context_id);
group_status_tbl.delete;
update_hierarchy_status(group_status_tbl(i).item_type,
group_status_tbl(i).process_name,
group_status_tbl(i).context_id);
PROCEDURE update_process_phase(node_id IN VARCHAR2, value IN NUMBER) IS
p_type az_processes.item_type%TYPE;
UPDATE az_processes ap
SET ap.phase = value
WHERE ap.item_type = p_type
AND ap.process_name = p_name
AND ap.context_id = TO_NUMBER(ctx_id);
END update_process_phase;
PROCEDURE update_process_comments(node_id IN VARCHAR2, value IN VARCHAR2) IS
p_type az_processes.item_type%TYPE;
UPDATE az_processes ap
SET ap.comments = value
WHERE ap.item_type = p_type
AND ap.process_name = p_name
AND ap.context_id = TO_NUMBER(ctx_id);
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_process_comments');
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_process_comments');
END update_process_comments;
PROCEDURE update_groups_status(p_group_id IN VARCHAR2) IS
v_parent_id az_groups.group_id%TYPE;
SELECT ag.hierarchy_parent_id
INTO v_parent_id
FROM az_groups ag
WHERE ag.group_id = p_group_id
AND ag.process_type = g_current_mode;
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_groups_status');
fnd_message.set_token('AZW_ERROR_STMT','select hierarchy_parent_id from az_groups');
SELECT COUNT(*)
INTO v_total_kids
FROM az_groups ag
WHERE ag.hierarchy_parent_id = v_parent_id
AND ag.process_type = g_current_mode;
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_groups_status');
fnd_message.set_token('AZW_ERROR_STMT','select count(*) into v_total_kids from az_groups');
SELECT COUNT(*)
INTO v_cnt
FROM az_groups ag
WHERE ag.hierarchy_parent_id = v_parent_id
AND ag.process_type = g_current_mode
AND ag.complete_flag <> 'Y';
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_groups_status');
fnd_message.set_token('AZW_ERROR_STMT','select count(*) into v_cnt from az_groups');
UPDATE az_groups
SET complete_flag = 'Y'
WHERE group_id = v_parent_id
AND process_type = g_current_mode;
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_groups_status');
fnd_message.set_token('AZW_ERROR_STMT','update az_groups set complete_flag = Y');
SELECT ag.hierarchy_parent_id
INTO v_parent_id
FROM az_groups ag
WHERE ag.group_id = v_parent_id
AND ag.process_type = g_current_mode;
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_groups_status');
fnd_message.set_token('AZW_ERROR_STMT','select hierarchy_parent_id from az_groups');
UPDATE az_groups
SET complete_flag = 'N'
WHERE group_id = v_parent_id
AND process_type = g_current_mode;
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_groups_status');
fnd_message.set_token('AZW_ERROR_STMT','update az_groups set complete_flag = N');
SELECT ag.hierarchy_parent_id
INTO v_parent_id
FROM az_groups ag
WHERE ag.group_id = v_parent_id
AND ag.process_type = g_current_mode;
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_groups_status');
fnd_message.set_token('AZW_ERROR_STMT','select hierarchy_parent_id from az_groups');
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_groups_status');
END update_groups_status;
PROCEDURE update_hierarchy_status(p_item_type IN VARCHAR2,
p_process_name IN VARCHAR2,
p_context_id IN VARCHAR2) IS
v_group_id az_groups.group_id%TYPE;
SELECT ap.parent_id
INTO v_group_id
FROM az_processes ap
WHERE ap.item_type = p_item_type
AND ap.process_name = p_process_name
AND ap.process_type = g_current_mode
AND ap.context_id = p_context_id;
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_hierarchy_status');
fnd_message.set_token('AZW_ERROR_STMT','select parent_id from az_processes');
SELECT COUNT(*)
INTO v_cnt
FROM az_processes ap
WHERE ap.status_code <> 'C'
AND ap.process_type = g_current_mode
AND ap.parent_id = v_group_id;
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_hierarchy_status');
fnd_message.set_token('AZW_ERROR_STMT','select count(*) from az_processes');
UPDATE az_groups ag
SET ag.complete_flag = v_status
WHERE ag.group_id = v_group_id
AND ag.process_type = g_current_mode;
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_hierarchy_status');
fnd_message.set_token('AZW_ERROR_STMT','update az_groups set complete_flag');
-- update groups complete status, starting with the leaf group id
update_groups_status(v_group_id);
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_hierarchy_status');
END update_hierarchy_status;
PROCEDURE update_process_status(node_id IN VARCHAR2, value IN VARCHAR2) IS
p_type az_processes.item_type%TYPE;
SELECT count(*)
INTO v_count
FROM wf_items wfi, wf_item_attribute_values wiav
WHERE wfi.item_type = p_type
AND wfi.root_activity = p_name
AND wiav.item_type = p_type
AND wiav.item_key = wfi.item_key
AND wiav.name = 'AZW_IA_CTXT_ID'
AND wiav.text_value = ctx_id;
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_process_status');
fnd_message.set_token('AZW_ERROR_STMT','select count(*) into v_count from wf_items,wf_item_attribute_values');
UPDATE az_processes ap
SET ap.status_code = value
WHERE ap.item_type = p_type
AND ap.process_name = p_name
AND ap.context_id = TO_NUMBER(ctx_id);
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_process_status');
fnd_message.set_token('AZW_ERROR_STMT','update az_processes set status_code = value');
UPDATE az_processes ap
SET ap.status_code = 'A'
WHERE ap.item_type = p_type
AND ap.process_name = p_name
AND ap.context_id = TO_NUMBER(ctx_id);
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_process_status');
fnd_message.set_token('AZW_ERROR_STMT','update az_processes set status_code = A');
UPDATE az_processes ap
SET ap.status_code = 'N'
WHERE ap.item_type = p_type
AND ap.process_name = p_name
AND ap.context_id = TO_NUMBER(ctx_id);
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_process_status');
fnd_message.set_token('AZW_ERROR_STMT','update az_processes set status_code = N');
update_hierarchy_status(p_type, p_name, ctx_id);
fnd_message.set_token('AZW_ERROR_PROC','azw_proc.update_process_status');
fnd_message.set_token('AZW_ERROR_STMT','select into v_ctx_id');
END update_process_status;
select AZ_WF_ITEMKEY_S.nextval into itemkey from dual;
AZW_UTIL.UpdateDocUrl(p_itemtype, p_workflow);
SELECT round( months_between(sysdate, wfi.begin_date)* 31),
wias.activity_status
FROM wf_items wfi, wf_process_activities wpa,
wf_item_activity_statuses wias
WHERE wfi.item_type = p_type
AND wfi.root_activity = p_name
AND wfi.item_key = p_key
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 =p_type
AND wpa.process_name = 'ROOT';
SELECT wav.display_name,
wias.begin_date
FROM wf_process_activities wpa, wf_item_activity_statuses wias,
wf_activities_vl wav
WHERE wias.item_type = p_type
AND wias.item_key = p_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;
SELECT COUNT(*)
FROM wf_item_activity_statuses wias, wf_notification_attributes wna,
wf_notifications wn
WHERE wias.item_type = p_type
AND wias.item_key = p_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 = p_type
AND wias.item_key = p_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,
wias.begin_date
FROM wf_process_activities wpa, wf_item_activity_statuses wias,
wf_activities_vl wav
WHERE wias.item_type = p_type
AND wias.item_key = v_new_task_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.type = 'NOTICE'
AND wav.end_date is NULL
AND wav.begin_date is not NULL
ORDER BY wias.begin_date desc;
SELECT distinct organization_code, chart_of_accounts_id
INTO org_code, coa_id
FROM org_access_view
WHERE organization_id = ctx_id;
fnd_message.set_token('AZW_ERROR_STMT','select organization_code from org_access_view');
/* Now update the status */
update_process_status(node_id, 'A');
SELECT wias.activity_status
FROM wf_items wfi, wf_process_activities wpa,
wf_item_activity_statuses wias
WHERE wfi.item_type = v_type
AND wfi.root_activity = p_name
AND wfi.item_key = v_key
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 =v_type
AND wpa.process_name = 'ROOT';
SELECT ap.item_type, ap.process_name, ap.context_id
FROM az_processes ap,wf_items wfi, wf_item_attribute_values wiav
WHERE wfi.item_type = ap.item_type
AND wfi.root_activity = ap.process_name
AND wiav.item_type = wfi.item_type
AND wiav.item_key = wfi.item_key
AND wiav.name = 'AZW_IA_CTXT_ID'
AND wiav.text_value = ap.context_id
AND ap.complete_flag = 'N';
UPDATE az_processes
SET status_code = 'A'
WHERE item_type = p_item_type
AND process_name = p_process_name
AND context_id = p_context_id;
-- Update all 'Complete' processes
UPDATE az_processes
SET status_code = 'C'
WHERE complete_flag = 'Y';
fnd_message.set_token('AZW_ERROR_STMT','update az_processes status code C');
-- Update all 'Not Started' Processes
UPDATE az_processes
SET status_code = 'N'
WHERE status_code NOT IN ('A', 'C');
fnd_message.set_token('AZW_ERROR_STMT','update az_processes status code N');
SELECT distinct wi.item_type, wi.item_key
FROM wf_items wi
WHERE wi.item_type like 'AZW%'
AND wi.end_date is NULL;
SELECT COUNT(*) INTO v_count
FROM az_tasks_v
WHERE item_type = v_type
AND root_activity = v_name
AND status = 'A';
fnd_message.set_token('AZW_ERROR_STMT','SELECT COUNT(*) FROM AZ_TASKS_V');