[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
--Select escalation_owner_ID's
-- The following cursor changed by MSENGUPT on 12/08/2001 to change okc_rules_v to okc_rules_b
Cursor escal_owner_id(p_tve_id IN NUMBER) is
select rul.rule_information6 escalate_owner1_id
,rul.rule_information7 escalate_owner2_id
,rul.dnz_chr_id
from okc_rules_b rul
where to_char(p_tve_id) = rul.rule_information2
and rul.rule_information_category = 'NTN';
--Select Owner_names
Cursor escal_owner_cur(p_escal_owner_id IN NUMBER) is
Select fnd.user_name escalate_owner, okx.name full_name
from okx_resources_v okx, fnd_user fnd
where okx.user_id = fnd.user_id
and okx.id1 = p_escal_owner_id;
Select jtb.object_version_number
,jtb.task_id
,jtb.task_number
,jtb.task_name
,jtb.source_object_id
,jtb.owner_id
,jtb.planned_end_date
,jtb.notification_period
,res.tve_id
from jtf_tasks_vl jtb,
jtf_task_types_vl jttl,
jtf_task_statuses_vl jtsl,
okc_resolved_timevalues res
where jtb.actual_end_date IS NULL
and res.id = jtb.source_object_id
and jtb.workflow_process_id IS NULL
and jtb.source_object_type_code = 'OKC_RESTIME'
and jtb.task_type_id = jttl.task_type_id
and jttl.task_type_id = 23
--and jttl.name = 'OKCSCHRULE'
and jtb.task_status_id = jtsl.task_status_id
and jtsl.task_status_id = 10;
--Select all the escalation owner id's
IF NOT escal_owner_id%ISOPEN THEN
--Get escalation owner1 ID
OPEN escal_owner_id(l_tve_id);
--Select the owner of the Task
IF NOT escal_owner_cur%ISOPEN THEN
--Get Task Owner
OPEN escal_owner_cur(l_owner_id);
--Select escalation owner1
IF NOT escal_owner_cur%ISOPEN THEN
--Get escalation owner1
OPEN escal_owner_cur(l_escalation_owner1_id);
--Select the item key
select okc_wf_notify_s1.nextval
into l_item_key
from dual;
OKC_DEBUG.log('290: before OKC_TASK_PUB.update_task .....');
--Update workflow_process_id in the tasks table
OKC_TASK_PUB.update_task(p_api_version => g_api_version,
p_object_version_number => l_object_version_number,
p_init_msg_list => p_init_msg_list,
p_task_id => l_task_id,
p_task_number => l_task_number,
p_workflow_process_id => l_item_key,
x_return_status => g_return_status,
x_msg_count => g_msg_count,
x_msg_data => g_msg_data);
OKC_DEBUG.log('300: after OKC_TASK_PUB.update_task return_status is '|| g_return_status );
-- not completed by the task owner. It also updates the
-- workflow process id and alarm fired count in tasks table
-- Version : 1.0
-- End of comments
--------------------------------------------------------------------------------
PROCEDURE task_escalation1(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_wf_name IN VARCHAR2,
p_wf_process IN VARCHAR2) IS
-- Replaced name with seeded ids to avoid translation issues - Bug 1683539
-- Read OKCSCHRULE - Contract Schedule Rule - Bug 1683539
CURSOR escalate_owner1_cur is
Select jtb.object_version_number
,jtb.task_id
,jtb.task_number
,jtb.task_name
,jtb.source_object_id
,jtb.owner_id
,jtb.planned_end_date
,jtb.actual_end_date
,jtb.alarm_interval
,res.tve_id
from jtf_tasks_vl jtb,
jtf_task_types_tl jttl,
jtf_task_statuses_tl jtsl,
okc_resolved_timevalues res
where jtb.actual_end_date IS NULL
and res.id = jtb.source_object_id
and jtb.workflow_process_id IS NOT NULL
and jtb.alarm_fired_count IS NULL
and jtb.source_object_type_code = 'OKC_RESTIME'
and jtb.task_type_id = jttl.task_type_id
and jttl.task_type_id = 23
--and jttl.name = 'OKCSCHRULE'
and jtb.task_status_id = jtsl.task_status_id
and jtsl.task_status_id = 10;
--Select the owner of the Task
IF NOT escal_owner_cur%ISOPEN THEN
--Get Task Owner
OPEN escal_owner_cur(l_owner_id);
select okc_wf_notify_s1.nextval
into l_item_key
from dual;
OKC_DEBUG.log('790: before OKC_TASK_PUB.update_task .....');
--Update workflow_process_id, alarm_fired_count in the tasks table
OKC_TASK_PUB.update_task(p_api_version => g_api_version,
p_object_version_number => l_object_version_number,
p_init_msg_list => p_init_msg_list,
p_task_id => l_task_id,
p_task_number => l_task_number,
p_workflow_process_id => l_item_key,
p_alarm_fired_count => 1,
x_return_status => g_return_status,
x_msg_count => g_msg_count,
x_msg_data => g_msg_data);
OKC_DEBUG.log('800: after OKC_TASK_PUB.update_task return_status is '|| g_return_status);
Select jtb.object_version_number
,jtb.task_id
,jtb.task_number
,jtb.task_name
,jtb.owner_id
,jtb.source_object_id
,jtb.planned_end_date
,jtb.actual_end_date
,jtb.alarm_interval
,res.tve_id
from jtf_tasks_vl jtb,
jtf_task_types_vl jttl,
jtf_task_statuses_vl jtsl,
okc_resolved_timevalues res
where jtb.actual_end_date IS NULL
and res.id = jtb.source_object_id
and jtb.workflow_process_id IS NOT NULL
and jtb.alarm_fired_count = 1
and jtb.source_object_type_code = 'OKC_RESTIME'
and jtb.task_type_id = jttl.task_type_id
and jttl.task_type_id = 23
--and jttl.name = 'OKCSCHRULE'
and jtb.task_status_id = jtsl.task_status_id
and jtsl.task_status_id = 10;
--Select the owner of the Task
IF NOT escal_owner_cur%ISOPEN THEN
--Get Task Owner
OPEN escal_owner_cur(l_owner_id);
select okc_wf_notify_s1.nextval
into l_item_key
from dual;
OKC_DEBUG.log('2090: before OKC_TASK_PUB.update_task .....');
--Update alarm fired count, workflow_process_id in the tasks table
OKC_TASK_PUB.update_task(p_api_version => g_api_version,
p_object_version_number => l_object_version_number,
p_init_msg_list => p_init_msg_list,
p_task_id => l_task_id,
p_task_number => l_task_number,
p_workflow_process_id => l_item_key,
p_alarm_fired_count => 2,
x_return_status => g_return_status,
x_msg_count => g_msg_count,
x_msg_data => g_msg_data);
OKC_DEBUG.log('3000: after OKC_TASK_PUB.update_task return_status is '|| g_return_status);
SELECT planned_end_date, source_object_id
from jtf_tasks_b
where source_object_type_code = 'OKC_RESTIME';