[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(*) INTO cnt
FROM az_archive aap
where aap.node_id = p_node_id;
select count(*) into cnt
from az_archive_steps aas
where aas.item_key = p_item_key
and aas.activity_type = p_activity_type
and aas.step = p_step
and aas.begin_date = p_begin_date;
SELECT TO_CHAR(agv.display_order, '0000')||'.'||agv.group_id node_id
,agv.display_name
,TO_CHAR(ag.display_order, '0000')||'.'||
agv.hierarchy_parent_id parent_node_id
,agv.status
,fl.meaning status_display_name
FROM az_groups_v agv
,az_groups ag
,fnd_lookups fl
WHERE agv.hierarchy_parent_id = ag.group_id
AND agv.status = fl.lookup_code
AND fl.lookup_type = 'AZ_PROCESS_STATUS'
UNION ALL
SELECT TO_CHAR(agv.display_order, '0000')||'.'||agv.group_id node_id
,agv.display_name
,agv.hierarchy_parent_id parent_node_id
,agv.status
,fl.meaning status_display_name
FROM az_groups_v agv
,fnd_lookups fl
WHERE agv.hierarchy_parent_id is NULL
AND agv.status = fl.lookup_code
AND fl.lookup_type = 'AZ_PROCESS_STATUS';
SELECT DISTINCT TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type
||'.'||apv.process_name||'.'||apv.context_id node_id ,
apv.display_name,
TO_CHAR(ag.display_order, '0000')||'.'||apv.parent_id parent_node_id,
apv.context_type,
apv.context_type_name context_type_display_name,
apv.context_id,
apv.context_name context_display_name,
apv.status,
fl.meaning status_display_name,
apv.comments
FROM az_processes_all_v apv,
az_groups ag,
fnd_lookups fl
WHERE apv.parent_id = ag.group_id
AND apv.status = fl.lookup_code
AND fl.lookup_type = 'AZ_PROCESS_STATUS';
SELECT atv.item_type||'.'||atv.root_activity||'.'||atv.context_id||'.'||
TO_CHAR(TO_NUMBER(atv.item_key), '00000') node_id
,apv.display_name
,TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type||'.'||
apv.process_name||'.'||apv.context_id parent_node_id
,apv.context_type
,apv.context_type_name context_type_display_name
,atv.context_id
,atv.context_name context_display_name
,atv.status
,fl.meaning status_display_name
,atv.item_key
,atv.assigned_user
,wf_directory.GetRoleDisplayName(atv.assigned_user)
assigned_user_display_name
,atv.begin_date
,atv.end_date
,atv.duration
FROM az_tasks_v atv
,az_processes_all_v apv
,fnd_lookups fl
WHERE atv.item_type = apv.item_type
AND atv.root_activity = apv.process_name
AND atv.context_id = apv.context_id
AND atv.status = fl.lookup_code
AND fl.lookup_type = 'AZ_PROCESS_STATUS';
INSERT INTO az_archive
(node_id, node_type
,parent_node_id, node_name
,context_type, context_type_name
,context_id, context_name
,status_code, status_name
,item_key
,assigned_user, assigned_user_name
,start_date, end_date
,duration, comments
)
VALUES
(each_group.node_id, 'G',
each_group.parent_node_id, each_group.display_name,
NULL, NULL, NULL, NULL,
each_group.status, each_group.status_display_name,
NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO az_archive
(node_id, node_type
,parent_node_id, node_name
,context_type, context_type_name
,context_id, context_name
,status_code, status_name
,item_key
,assigned_user, assigned_user_name
,start_date, end_date
,duration, comments
)
VALUES
(each_proc.node_id, 'P',
each_proc.parent_node_id, each_proc.display_name,
each_proc.context_type, each_proc.context_type_display_name,
each_proc.context_id, each_proc.context_display_name,
each_proc.status, each_proc.status_display_name,
NULL, NULL, NULL, NULL, NULL, NULL,
each_proc.comments);
INSERT into az_archive
(node_id, node_type
,parent_node_id, node_name
,context_type, context_type_name
,context_id, context_name
,status_code, status_name
,item_key
,assigned_user, assigned_user_name
,start_date, end_date
,duration, comments
)
VALUES
(each_task.node_id, 'T',
each_task.parent_node_id, each_task.display_name,
each_task.context_type, each_task.context_type_display_name,
each_task.context_id, each_task.context_display_name,
each_task.status, each_task.status_display_name,
each_task.item_key,
each_task.assigned_user, each_task.assigned_user_display_name,
each_task.begin_date, each_task.end_date, each_task.duration, NULL);
SELECT atv.item_type||'.'||atv.root_activity||'.'||atv.context_id||'.'||
TO_CHAR(TO_NUMBER(atv.item_key), '00000') node_id,
atv.item_type item_type,
atv.item_key item_key
FROM az_tasks_v atv;
SELECT wiasv.activity_type_code type
--,wiasv.activity_type_display_name
,wiasv.activity_name name
,wiasv.activity_display_name display_name
,wiasv.assigned_user user_name
,wiasv.assigned_user_display_name user_display_name
,wna.text_value form_name
,wiasv.activity_status_code status
--,wiasv.activity_status_display_name status_display_name
,wiasv.activity_result_code result
--,wiasv.activity_result_display_name result_display_name
,wiasv.activity_begin_date begin_date
,wiasv.activity_end_date end_date
,wiasv.execution_time
,wn.user_comment
FROM wf_item_activity_statuses_v wiasv
,wf_notification_attributes wna
,wf_notifications wn
WHERE wiasv.item_type = l_item_type
AND wiasv.item_key = l_item_key
AND wiasv.notification_id = wn.notification_id (+)
AND wn.notification_id = wna.notification_id (+)
AND wna.name (+) = 'AZW_IA_FORM'
AND NOT (wiasv.activity_name in ('START', 'END'))
UNION
SELECT wiasv.activity_type type
--,wiasv.activity_type_display_name
,wiasv.activity_name name
,wiasv.activity_display_name display_name
,wiasv.recipient_role user_name
,wiasv.recipient_role_name user_display_name
,wna.text_value form_name
,wiasv.activity_status status
--,wiasv.activity_status_display_name status_display_name
--,wiasv.result result
,wiash.activity_result_code result
--,wiasv.activity_result_display_name result_display_name
,wiasv.begin_date begin_date
,wiasv.end_date end_date
,wiasv.execution_time
,wn.user_comment
FROM wf_item_activities_history_v wiasv
,wf_item_activity_statuses_h wiash
,wf_notification_attributes wna
,wf_notifications wn
WHERE wiasv.item_type = l_item_type
AND wiasv.item_key = l_item_key
AND wiasv.item_type = wiash.item_type
AND wiasv.item_key = wiash.item_key
AND wiasv.begin_date = wiash.begin_date
AND wiasv.notification_id = wiash.notification_id
AND wiasv.notification_id = wn.notification_id (+)
AND wn.notification_id = wna.notification_id (+)
AND wna.name (+) = 'AZW_IA_FORM'
AND NOT (wiasv.activity_name in ('START', 'END'));
INSERT into az_archive_steps
(item_key
,activity_type
,step, step_name
,assigned_user, assigned_user_name
,form_name
,status_code
,result_code
,begin_date
,end_date
,duration
,comments
,node_id
)
VALUES
(each_task.item_key
,each_step.type
,each_step.name, each_step.display_name
,each_step.user_name, each_step.user_display_name
,each_step.form_name
,each_step.status
,each_step.result
,each_step.begin_date, each_step.end_date
,each_step.execution_time
,each_step.user_comment
,each_task.node_id);