[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_deleted_flag jtf_tasks_b.deleted_flag%TYPE;
--Select the rule details
/* Cursor rules_cur(p_tve_id IN NUMBER) is
select rule_information1 task_name
,rule_information3 notification_period
,rule_information4 resource_id
,rule_information5 escalate_days
from okc_rules_v
where rule_information_category = 'NTN'
and rule_information2 = p_tve_id;
--Select the task_type
Cursor task_cur is
select task_type_id, name
from jtf_task_types_vl
where task_type_id = 23;
--Select the task status
Cursor status_cur is
select task_status_id, name
from jtf_task_statuses_vl
where task_status_id = 10;
--Select the object code
Cursor object_cur is
select object_code
from jtf_objects_vl
where object_code = 'OKC_RESTIME'
and object_code in (select object_code
from jtf_object_usages
where object_user_code = 'TASK');
--Select the owner type code
Cursor owner_type_cur is
select object_code
from jtf_objects_vl
where object_code = 'OKX_TASKRES'
and object_code in (select object_code
from jtf_object_usages
where object_user_code = 'RESOURCES');
SELECT UOM_CODE FROM OKC_TIME_CODE_UNITS_B
where tce_code = 'DAY'
and rownum < 2;
-- Select the rule details
-- Resolve all values related to contracts
-- Get the application_id and rule definition names
l_app_id := OKC_TIME_UTIL_PUB.get_app_id;
l_sql_string := 'select r.rule_information1 task_name,r.rule_information3 notification_period,rule_information4 resource_id,rule_information5 escalate_days ' ||
'from okc_rules_b r '||
'where r.rule_information2 = to_char(:p_tve_id) ' ||
'and r.rule_information_category in '|| l_list_of_rules ||
'and r.rule_information_category in '|| l_list_of_rules1 ;
l_deleted_flag jtf_tasks_b.deleted_flag%TYPE;
--Select task_type
Cursor task_cur is
select task_type_id, name
from jtf_task_types_vl
where task_type_id = 18;
--Select task status
Cursor status_cur is
select task_status_id, name
from jtf_task_statuses_vl
where task_status_id = 9;
--Select object code
Cursor object_cur is
select object_code
from jtf_objects_vl
where object_code = 'OKC_COND_OCCR'
and object_code in (select object_code
from jtf_object_usages
where object_user_code = 'TASK');
--Select the owner type code
Cursor owner_type_cur is
select object_code
from jtf_objects_vl
where object_code = 'OKX_TASKRES'
and object_code in (select object_code
from jtf_object_usages
where object_user_code = 'RESOURCES');
l_deleted_flag jtf_tasks_b.deleted_flag%TYPE;
--Select task_type
Cursor task_cur is
select task_type_id, name
from jtf_task_types_vl
where task_type_id = 24;
--Select task status
Cursor status_cur is
select task_status_id, name
from jtf_task_statuses_vl
where task_status_id = 9;
--Select object code
Cursor object_cur is
select object_code
from jtf_objects_vl
where object_code = 'OKC_K_HEADER'
and object_code in (select object_code
from jtf_object_usages
where object_user_code = 'TASK');
--Select the owner type code
Cursor owner_type_cur is
select object_code
from jtf_objects_vl
where object_code = 'OKX_TASKRES'
and object_code in (select object_code
from jtf_object_usages
where object_user_code = 'RESOURCES');
-- Procedure Name : update_task
-- Description : Procedure to update a Task
-- Version : 1.0
-- End of comments
PROCEDURE update_task(p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_object_version_number IN OUT NOCOPY NUMBER
,p_task_id IN NUMBER
,p_task_number IN NUMBER
,p_workflow_process_id IN NUMBER
,p_actual_end_date IN DATE
,p_alarm_fired_count IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_task';
--Select task status
Cursor update_status_cur is
select task_status_id, name
from jtf_task_statuses_vl
where task_status_id = 9;
--Select task_status
Cursor status_cur is
select task_status_id, name
from jtf_task_statuses_vl
where task_status_id = 10;
Select SOURCE_OBJECT_ID
,SOURCE_OBJECT_NAME
From jtf_tasks_b
Where task_id = p_task_id;
--If the actual date is not null then update the status to Closed
IF p_actual_end_date IS NOT NULL THEN
OPEN update_status_cur;
FETCH update_status_cur into l_close_status_id, l_close_status_name;
l_notfound := update_status_cur%NOTFOUND;
CLOSE update_status_cur;
--Call to the procedure of public API JTF_TASKS_PUB to update a task
jtf_tasks_pub.update_task(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_object_version_number => p_object_version_number
,p_task_id => p_task_id
,p_task_number => p_task_number
,p_workflow_process_id => p_workflow_process_id
,p_actual_end_date => p_actual_end_date
,p_alarm_fired_count => p_alarm_fired_count
,p_task_status_id => l_task_status_id
,p_task_status_name => l_task_status_name
,p_source_object_id => l_source_object_id
,p_source_object_name => l_source_object_name
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
END update_task;
-- Procedure Name : delete_task
-- Description : Procedure to delete a Task/s
-- Version : 1.0
-- End of comments
--Pass the p_tve_id(Time value ID) to delete multiple tasks(ex: When a rule is deleted)
--Pass the p_rtv_id(Resolved Time ID) to delete a single task(ex: When a contract is terminated)
PROCEDURE delete_task(p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_tve_id IN NUMBER
,p_rtv_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_task';
--Select task for a given resolved timevalue id
Cursor delete_tasks_cur(p_rtv_id IN NUMBER, p_status_id IN NUMBER) IS
select jtf.task_id, jtf.task_number, jtf.object_version_number
from jtf_tasks_b jtf
where jtf.source_object_id = p_rtv_id
and jtf.source_object_type_code = 'OKC_RESTIME'
and jtf.task_status_id = p_status_id;
--Select task status
Cursor delete_status_cur is
select task_status_id
from jtf_task_statuses_vl
where task_status_id = 10;
--Select all the resolved time values for a given timevalue id
Cursor delete_rule_cur(p_tve_id IN NUMBER) IS
select rtv.id
from okc_resolved_timevalues rtv
where rtv.tve_id = p_tve_id;
TYPE delete_rec_type IS RECORD(
task_id jtf_tasks_b.task_id%TYPE,
task_number jtf_tasks_b.task_number%TYPE,
object_version_number jtf_tasks_b.object_version_number%TYPE);
TYPE delete_tasks_tbl_type IS TABLE OF delete_rec_type
INDEX BY BINARY_INTEGER;
delete_tasks_tbl delete_tasks_tbl_type;
delete_ctr NUMBER := 0;
--If the timevalue ID is not null then delete all the tasks for a rule with status = 'Open'
IF p_tve_id IS NOT NULL THEN
OPEN delete_status_cur;
FETCH delete_status_cur into l_status_id;
CLOSE delete_status_cur;
FOR delete_rule_rec in delete_rule_cur(p_tve_id) LOOP
FOR delete_tasks_rec in delete_tasks_cur(p_rtv_id => delete_rule_rec.id,
p_status_id => l_status_id) LOOP
delete_ctr := delete_ctr + 1;
delete_tasks_tbl (delete_ctr).task_id := delete_tasks_rec.task_id;
delete_tasks_tbl (delete_ctr).task_number := delete_tasks_rec.task_number;
delete_tasks_tbl (delete_ctr).object_version_number := delete_tasks_rec.object_version_number;
IF delete_tasks_tbl.COUNT > 0 THEN
i := delete_tasks_tbl.FIRST;
--Call the procedure of public API JTF_TASKS_PUB to delete tasks
jtf_tasks_pub.delete_task(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_object_version_number => delete_tasks_tbl(i).object_version_number
,p_task_id => delete_tasks_tbl(i).task_id
,p_task_number => delete_tasks_tbl(i).task_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
EXIT WHEN (i = delete_tasks_tbl.LAST);
i := delete_tasks_tbl.NEXT(i);
--If the resolved timevalue id is not null then delete a single task
-- where source_object_id(JTF_TASKS_B) = p_rtv_id(resolved timevalue ID)
-- and the status is OKCOPEN
IF p_rtv_id IS NOT NULL THEN
OPEN delete_status_cur;
FETCH delete_status_cur into l_status_id;
CLOSE delete_status_cur;
FOR delete_tasks_rec in delete_tasks_cur(p_rtv_id => p_rtv_id, p_status_id => l_status_id) LOOP
delete_ctr := delete_ctr + 1;
delete_tasks_tbl (delete_ctr).task_id := delete_tasks_rec.task_id;
delete_tasks_tbl (delete_ctr).task_number := delete_tasks_rec.task_number;
delete_tasks_tbl (delete_ctr).object_version_number := delete_tasks_rec.object_version_number;
IF delete_tasks_tbl.COUNT > 0 THEN
i := delete_tasks_tbl.FIRST;
--Call to the procedure of public API JTF_TASKS_PUB to delete tasks
jtf_tasks_pub.delete_task(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_object_version_number => delete_tasks_tbl(i).object_version_number
,p_task_id => delete_tasks_tbl(i).task_id
,p_task_number => delete_tasks_tbl(i).task_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
EXIT WHEN (i = delete_tasks_tbl.LAST);
i := delete_tasks_tbl.NEXT(i);
END delete_task;