[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
if jtf_tasks_pub.p_task_user_hooks.date_selected = 'P' then
l_due_date := jtf_tasks_pub.p_task_user_hooks.planned_end_date;
elsif jtf_tasks_pub.p_task_user_hooks.date_selected = 'A' then
l_due_date := jtf_tasks_pub.p_task_user_hooks.actual_end_date;
elsif jtf_tasks_pub.p_task_user_hooks.date_selected = 'S' then
l_due_date := jtf_tasks_pub.p_task_user_hooks.scheduled_end_date;
elsif jtf_tasks_pub.p_task_user_hooks.date_selected is null then
-- Niraj Bug 4609285 Commented following 2 lines. Making default as Scheduled Date in case of Null value
-- l_due_date := null;
select importance_level
into l_importance_level
from jtf_task_priorities_vl
where task_priority_id = jtf_tasks_pub.p_task_user_hooks.task_priority_id;
select priority_code
into l_priority_code
from ieu_uwqm_priorities_b
where priority_level = l_importance_level;
select priority_code
into l_priority_code
from ieu_uwqm_priorities_b
where priority_level = 4;
select 'CLOSE' into l_task_status
from jtf_task_statuses_vl
where (nvl(closed_flag, 'N') = 'Y'
or nvl(completed_flag, 'N') = 'Y'
or nvl(cancelled_flag, 'N') = 'Y'
or nvl(rejected_flag, 'N') = 'Y')
and task_status_id = jtf_tasks_pub.p_task_user_hooks.task_status_id;
select 'SLEEP' into l_task_status
from jtf_task_statuses_vl
where nvl(on_hold_flag, 'N') = 'Y'
and task_status_id = jtf_tasks_pub.p_task_user_hooks.task_status_id;
Select ws_id
into l_ws_id1
from ieu_uwqm_work_sources_b
--where object_code = 'TASK'
--and nvl(not_valid_flag,'N') = 'N';
Select ws_id
into l_ws_id2
from ieu_uwqm_work_sources_b
where object_code = jtf_tasks_pub.p_task_user_hooks.source_object_type_code
-- and nvl(not_valid_flag,'N') = 'N';
SELECT a.ws_id, b.ws_code
INTO l_association_ws_id, l_association_ws_code
FROM ieu_uwqm_ws_assct_props a, ieu_uwqm_work_sources_b b
WHERE child_ws_id = l_ws_id1
AND parent_ws_id = l_ws_id2
AND a.ws_id = b.ws_id
--AND nvl(b.not_valid_flag,'N') = 'N';
SELECT ws_b.tasks_rules_function
INTO l_tasks_rules_func
FROM ieu_uwqm_ws_assct_props ws_b
WHERE ws_b.ws_id = l_association_ws_id;
jtf_tasks_pub.p_task_user_hooks.date_selected,
jtf_tasks_pub.p_task_user_hooks.planned_start_date,
jtf_tasks_pub.p_task_user_hooks.planned_end_date,
jtf_tasks_pub.p_task_user_hooks.scheduled_start_date,
jtf_tasks_pub.p_task_user_hooks.scheduled_end_date,
jtf_tasks_pub.p_task_user_hooks.actual_start_date,
jtf_tasks_pub.p_task_user_hooks.actual_end_date,
null,
null,
null);
Select owner_id
into l_orig_grp_owner
from ieu_uwqm_items
where WORKITEM_PK_ID = jtf_tasks_pub.p_task_user_hooks.task_id
-- and workitem_obj_code = 'TASK'
and workitem_obj_code = l_workitem_obj_code
-- and owner_type_actual = 'RS_GROUP';
PROCEDURE update_task_uwqm_pre ( x_return_status OUT NOCOPY VARCHAR2 ) As
l_work_item_id NUMBER; L_MSG_COUNT NUMBER;
l_update_task_reqd_flag VARCHAR2(1);
l_update_task_reqd_flag := 'n';
/*** This procedure will sync up any updates on the Task Work item
** First the Activation Status is checked
** If the Work Source is activated, then check if any Tasks Rules Function is registered
** If the task Rules function is present, then execute it
** If there is not Tasks Rules Function registered, update the work repository item
** In certain cases like Work items in 'Closed' status, the work item may not be present in the work repository
** If the work item does not exist then create them
***/
-- reset del task pkg lvl variable
l_del_task_id := null;
if jtf_tasks_pub.p_task_user_hooks.date_selected = 'P' then
l_due_date := jtf_tasks_pub.p_task_user_hooks.planned_end_date;
elsif jtf_tasks_pub.p_task_user_hooks.date_selected = 'A' then
l_due_date := jtf_tasks_pub.p_task_user_hooks.actual_end_date;
elsif jtf_tasks_pub.p_task_user_hooks.date_selected = 'S' then
l_due_date := jtf_tasks_pub.p_task_user_hooks.scheduled_end_date;
elsif jtf_tasks_pub.p_task_user_hooks.date_selected is null then
-- Niraj Bug 4609285 Commented following 2 lines. Making default as Scheduled Date in case of Null value
-- l_due_date := null;
select importance_level
into l_importance_level
from jtf_task_priorities_vl
where task_priority_id = jtf_tasks_pub.p_task_user_hooks.task_priority_id;
select priority_code, priority_id
into l_priority_code, l_priority_id
from ieu_uwqm_priorities_b
where priority_level = l_importance_level;
select priority_code, priority_id
into l_priority_code, l_priority_id
from ieu_uwqm_priorities_b
where priority_level = 4;
select task_status_id into l_task_status_id from jtf_tasks_b
where task_id = jtf_tasks_pub.p_task_user_hooks.task_id;
select 'CLOSE' into l_task_status
from jtf_task_statuses_vl
where (nvl(closed_flag, 'N') = 'Y'
or nvl(completed_flag, 'N') = 'Y'
or nvl(cancelled_flag, 'N') = 'Y'
or nvl(rejected_flag, 'N') = 'Y')
and task_status_id = jtf_tasks_pub.p_task_user_hooks.task_status_id;
select 'SLEEP' into l_task_status
from jtf_task_statuses_vl
where nvl(on_hold_flag, 'N') = 'Y'
and task_status_id = jtf_tasks_pub.p_task_user_hooks.task_status_id;
Select ws_id
into l_ws_id1
from ieu_uwqm_work_sources_b
--where object_code = 'TASK'
--and nvl(not_valid_flag,'N') = 'N';
Select ws_id
into l_ws_id2
from ieu_uwqm_work_sources_b
where object_code = jtf_tasks_pub.p_task_user_hooks.source_object_type_code
--and nvl(not_valid_flag,'N') = 'N';
SELECT a.ws_id, b.ws_code
INTO l_association_ws_id, l_association_ws_code
FROM ieu_uwqm_ws_assct_props a, ieu_uwqm_work_sources_b b
WHERE child_ws_id = l_ws_id1
AND parent_ws_id = l_ws_id2
AND a.ws_id = b.ws_id
--AND nvl(b.not_valid_flag,'N') = 'N';
SELECT ws_b.tasks_rules_function
INTO l_tasks_rules_func
FROM ieu_uwqm_ws_assct_props ws_b
WHERE ws_b.ws_id = l_association_ws_id;
'UPDATE_TASK',
jtf_tasks_pub.p_task_user_hooks.task_id,
null,
jtf_tasks_pub.p_task_user_hooks.task_number,
jtf_tasks_pub.p_task_user_hooks.task_name,
jtf_tasks_pub.p_task_user_hooks.task_type_id,
jtf_tasks_pub.p_task_user_hooks.task_status_id,
jtf_tasks_pub.p_task_user_hooks.task_priority_id,
jtf_tasks_pub.p_task_user_hooks.owner_id,
jtf_tasks_pub.p_task_user_hooks.owner_type_code,
jtf_tasks_pub.p_task_user_hooks.source_object_id,
jtf_tasks_pub.p_task_user_hooks.source_object_type_code,
jtf_tasks_pub.p_task_user_hooks.customer_id,
jtf_tasks_pub.p_task_user_hooks.date_selected,
jtf_tasks_pub.p_task_user_hooks.planned_start_date,
jtf_tasks_pub.p_task_user_hooks.planned_end_date,
jtf_tasks_pub.p_task_user_hooks.scheduled_start_date,
jtf_tasks_pub.p_task_user_hooks.scheduled_end_date,
jtf_tasks_pub.p_task_user_hooks.actual_start_date,
jtf_tasks_pub.p_task_user_hooks.actual_end_date,
null,
null,
null);
Select owner_id
into l_orig_grp_owner
from ieu_uwqm_items
where WORKITEM_PK_ID = jtf_tasks_pub.p_task_user_hooks.task_id
-- and workitem_obj_code = 'TASK'
-- and owner_type_actual = 'RS_GROUP';
if jtf_tasks_pub.p_task_user_hooks.date_selected = 'P' then
l_due_date := jtf_tasks_pub.p_task_user_hooks.planned_end_date;
elsif jtf_tasks_pub.p_task_user_hooks.date_selected = 'A' then
l_due_date := jtf_tasks_pub.p_task_user_hooks.actual_end_date;
elsif jtf_tasks_pub.p_task_user_hooks.date_selected = 'S' then
l_due_date := jtf_tasks_pub.p_task_user_hooks.scheduled_end_date;
elsif jtf_tasks_pub.p_task_user_hooks.date_selected is null then
-- Niraj Bug 4609285 Commented following 2 lines. Making default as Scheduled Date in case of Null value
-- l_due_date := null;
select assignee_id,
assignee_type,
due_date,
priority_id,
party_id,
owner_id,
title,
status_id,
owner_type_actual,
source_object_id,
source_object_type_code
into l_assignee_id,
l_assignee_type,
l_wr_due_date,
l_wr_priority_id,
l_wr_party_id,
l_wr_owner_id,
l_wr_title,
l_wr_status_id,
l_wr_owner_type_actual,
l_wr_source_object_id,
l_wr_source_object_type_code
from ieu_uwqm_items
where workitem_pk_id = jtf_tasks_pub.p_task_user_hooks.task_id
-- and workitem_obj_code = 'TASK';
l_update_task_reqd_flag := 'y';
l_update_task_reqd_flag := 'n';
if (l_update_task_reqd_flag = 'y') THEN
-- Start IF-2
if (jtf_tasks_pub.p_task_user_hooks.entity = 'TASK')
then
-- Start IF-3
if ((l_task_status = 'CLOSE') and (l_count = 0)) THEN
x_return_status := fnd_api.g_ret_sts_success;
select c.resource_id, c.resource_type_code
into l_assignee_id, l_assignee_type
from jtf_task_assignments c
where c.task_id = jtf_tasks_pub.p_task_user_hooks.task_id
and c.assignee_role = 'ASSIGNEE'
and c.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
and c.resource_id in ( select resource_id
from jtf_rs_group_members
where group_id = jtf_tasks_pub.p_task_user_hooks.owner_id
and nvl(delete_flag,'N') <> 'Y')
and c.last_update_date = (select max(a.last_update_date)
from jtf_task_assignments a,jtf_task_statuses_vl b
where a.task_id = jtf_tasks_pub.p_task_user_hooks.task_id
and a.assignee_role = 'ASSIGNEE'
and a.assignment_status_id = b.task_status_id
and a.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
and a.resource_id in ( select resource_id
from jtf_rs_group_members
where group_id = jtf_tasks_pub.p_task_user_hooks.owner_id
and nvl(delete_flag,'N') <> 'Y')
and (nvl(b.closed_flag, 'N') = 'N'
and nvl(b.completed_flag, 'N') = 'N'
and nvl(b.cancelled_flag, 'N') = 'N'
and nvl(b.rejected_flag, 'N') = 'N'
and b.task_status_id = c.assignment_status_id))
and rownum < 2;
l_event_key := 'UPDATE_WR_ITEM';
('WORKITEM_UPDATE',
l_event_key,
690,
'IEU_TASKS_USERHOOKS.UPDATE_TASK_UWQM_PRE',
l_workitem_comment_code1,
l_workitem_comment_code2,
l_workitem_comment_code3,
l_workitem_comment_code4,
null);
IEU_WR_PUB.UPDATE_WR_ITEM(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_workitem_obj_code => 'TASK',
p_workitem_pk_id => jtf_tasks_pub.p_task_user_hooks.task_id,
p_title => jtf_tasks_pub.p_task_user_hooks.task_name,
p_party_id => jtf_tasks_pub.p_task_user_hooks.customer_id,
p_priority_code => l_priority_code,
p_due_date => l_due_date,
p_owner_id => jtf_tasks_pub.p_task_user_hooks.owner_id,
p_owner_type => jtf_tasks_pub.p_task_user_hooks.owner_type_code,
p_assignee_id => l_assignee_id,
p_assignee_type => l_assignee_type,
p_source_object_id => jtf_tasks_pub.p_task_user_hooks.source_object_id,
p_source_object_type_code => jtf_tasks_pub.p_task_user_hooks.source_object_type_code,
p_application_id => 690,
p_work_item_status => l_task_status,
p_user_id => FND_GLOBAL.USER_ID,
p_login_id => FND_GLOBAL.LOGIN_ID,
p_audit_trail_rec => l_audit_trail_rec,
x_msg_count => L_MSG_COUNT,
x_msg_data => L_MSG_DATA,
x_return_status => L_RETURN_STATUS);
end update_task_uwqm_pre;
PROCEDURE delete_task_uwqm_pre ( x_return_status OUT NOCOPY VARCHAR2 ) As
l_msg_count NUMBER;
select source_object_type_code
into l_task_source_obj_type_code
from jtf_tasks_b
where task_id = jtf_tasks_pub.p_task_user_hooks.task_id;
Select ws_id
into l_ws_id1
from ieu_uwqm_work_sources_b
-- where object_code = 'TASK'
--and nvl(not_valid_flag,'N') = 'N';
Select ws_id
into l_ws_id2
from ieu_uwqm_work_sources_b
where object_code = l_task_source_obj_type_code
--and nvl(not_valid_flag,'N') = 'N';
SELECT a.ws_id, b.ws_code
INTO l_association_ws_id, l_association_ws_code
FROM ieu_uwqm_ws_assct_props a, ieu_uwqm_work_sources_b b
WHERE child_ws_id = l_ws_id1
AND parent_ws_id = l_ws_id2
AND a.ws_id = b.ws_id
--AND nvl(b.not_valid_flag,'N') = 'N';
SELECT ws_b.tasks_rules_function
INTO l_tasks_rules_func
FROM ieu_uwqm_ws_assct_props ws_b
WHERE ws_b.ws_id = l_association_ws_id;
--insert into p_temp values ('act sts: '||l_activation_status||' rules func: '||l_tasks_rules_func);
'DELETE_TASK',
jtf_tasks_pub.p_task_user_hooks.task_id,
null,
null,
FND_API.G_MISS_CHAR,
FND_API.G_MISS_NUM,
FND_API.G_MISS_NUM,
FND_API.G_MISS_NUM,
FND_API.G_MISS_NUM,
FND_API.G_MISS_CHAR,
FND_API.G_MISS_NUM,
l_task_source_obj_type_code,
FND_API.G_MISS_NUM,
NULL,
FND_API.G_MISS_DATE,
FND_API.G_MISS_DATE,
FND_API.G_MISS_DATE,
FND_API.G_MISS_DATE,
FND_API.G_MISS_DATE,
FND_API.G_MISS_DATE,
null,
null,
null);
Select owner_id
into l_orig_grp_owner
from ieu_uwqm_items
where WORKITEM_PK_ID = jtf_tasks_pub.p_task_user_hooks.task_id
-- and workitem_obj_code = 'TASK'
-- and owner_type_actual = 'RS_GROUP';
'DELETE',
FND_API.G_MISS_CHAR,
FND_API.G_MISS_DATE,
'TASKS',
l_orig_grp_owner
);
select 'Y'
into l_wi_exists
from ieu_uwqm_items
where workitem_pk_id = jtf_tasks_pub.p_task_user_hooks.task_id
and workitem_obj_code = 'TASK';
--insert into p_temp(msg) values ('WI exists in table: '||l_wi_exists);
-- Closed Work Items are not currently migrated. No updates will be done to UWQ Metaphor table
-- if the Task is in closed/deleted status and the Work Item is not present in UWQ table.
if (l_wi_exists = 'Y')
then
begin
select task_type_id, entity into l_task_type_id, l_entity
from jtf_tasks_b
where task_id = jtf_tasks_pub.p_task_user_hooks.task_id;
('WORKITEM_UPDATE',
'DELETE_WR_ITEM',
690,
'IEU_TASKS_USERHOOKS.DELETE_TASK_UWQM_PRE',
l_workitem_comment_code1,
null,
null,
null,
null);
IEU_WR_PUB.UPDATE_WR_ITEM(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_workitem_obj_code => 'TASK',
p_workitem_pk_id => jtf_tasks_pub.p_task_user_hooks.task_id,
p_title => FND_API.G_MISS_CHAR,
p_party_id => FND_API.G_MISS_NUM,
p_priority_code => FND_API.G_MISS_CHAR,
p_due_date => FND_API.G_MISS_DATE,
p_owner_id => FND_API.G_MISS_NUM,
p_owner_type => FND_API.G_MISS_CHAR,
p_assignee_id => FND_API.G_MISS_NUM,
p_assignee_type => FND_API.G_MISS_CHAR,
p_source_object_id => FND_API.G_MISS_NUM,
p_source_object_type_code => FND_API.G_MISS_CHAR,
p_application_id => 690,
p_work_item_status => 'DELETE',
p_user_id => FND_GLOBAL.USER_ID,
p_login_id => FND_GLOBAL.LOGIN_ID,
p_audit_trail_rec => l_audit_trail_rec,
x_msg_count => L_MSG_COUNT,
x_msg_data => L_MSG_DATA,
x_return_status => L_RETURN_STATUS);
--insert into p_temp values('ret success');
--insert into p_temp values ('del tsk id: '||l_del_task_id);
--insert into p_temp values('excep');
end delete_task_uwqm_pre;
l_ins_date_selected varchar2(1);
l_update_wr_item_call varchar2(5); -- Niraj, Bug 4220060, Added
select source_object_type_code
into l_task_source_obj_type_code
from jtf_tasks_b
where task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id;
Select ws_id
into l_ws_id1
from ieu_uwqm_work_sources_b
-- where object_code = 'TASK'
-- and nvl(not_valid_flag,'N') = 'N';
Select ws_id
into l_ws_id2
from ieu_uwqm_work_sources_b
where object_code = l_task_source_obj_type_code
--and nvl(not_valid_flag,'N') = 'N';
SELECT a.ws_id, b.ws_code
INTO l_association_ws_id, l_association_ws_code
FROM ieu_uwqm_ws_assct_props a, ieu_uwqm_work_sources_b b
WHERE child_ws_id = l_ws_id1
AND parent_ws_id = l_ws_id2
AND a.ws_id = b.ws_id
-- AND nvl(b.not_valid_flag,'N') = 'N';
SELECT ws_b.tasks_rules_function
INTO l_tasks_rules_func
FROM ieu_uwqm_ws_assct_props ws_b
WHERE ws_b.ws_id = l_association_ws_id;
/* insert into p_temp(msg) values ('assignee_role: '||jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role||
' booking end date: '|| jtf_task_assignments_pub.p_task_assignments_user_hooks.booking_end_date); */
SELECT booking_end_date
INTO l_ins_due_date
FROM JTF_TASK_ALL_ASSIGNMENTS
WHERE task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
AND assignee_role = 'OWNER';
--insert into p_temp(msg) values ('due date: '||l_ins_due_date);
Select owner_id
into l_orig_grp_owner
from ieu_uwqm_items
where WORKITEM_PK_ID = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
-- and workitem_obj_code = 'TASK'
-- and owner_type_actual = 'RS_GROUP';
select task_status_id, deleted_flag
into l_tsk_sts_id, l_del_flag
from jtf_tasks_b
where task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id;
select 'CLOSE' into l_task_status
from jtf_task_statuses_vl
where (nvl(closed_flag, 'N') = 'Y'
or nvl(completed_flag, 'N') = 'Y'
or nvl(cancelled_flag, 'N') = 'Y'
or nvl(rejected_flag, 'N') = 'Y')
and task_status_id = l_tsk_sts_id;
select 'Y'
into l_wi_exists
from ieu_uwqm_items
where workitem_pk_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
and workitem_obj_code = 'TASK';
-- insert into p_temp(msg) values ('Task Status Id from Tasks table: '||l_tsk_sts_id||' l_wi_exists: '||l_wi_exists||' l_del_flag: '||l_del_flag);
-- Closed Work Items are not currently migrated. No updates will be done to UWQ Metaphor table
-- if the Task is in closed/deleted status and the Work Item is not present in UWQ table return Success.
if ( ( (l_task_status = 'CLOSE') or (l_del_flag = 'Y') )
and (l_wi_exists = 'N'))
then
-- insert into p_temp(msg) values ('close/del and rec does not exists.. ret success');
-- insert into p_temp(msg) values (' else condn');
-- insert into p_temp(msg) values (' selecting data from tasks table');
select tb.task_id, tb.task_number, tb.customer_id, tb.owner_id, tb.owner_type_code,
tb.source_object_id, tb.source_object_type_code,
tb.planned_start_date, tb.planned_end_date, tb.actual_start_date, tb.actual_end_date,
tb.scheduled_start_date, tb.scheduled_end_date,tb.task_type_id,
tb.task_status_id, tt.task_name, tp.importance_level, ip.priority_code
into l_ins_task_id, l_ins_task_number, l_ins_customer_id, l_ins_owner_id, l_ins_owner_type_code,
l_ins_source_object_id, l_ins_source_object_type_code, l_ins_planned_start_date, l_ins_planned_end_date,
l_ins_actual_ins_start_date, l_ins_actual_ins_end_date, l_ins_scheduled_start_date, l_ins_scheduled_end_date,
l_ins_task_type_id, l_ins_task_status_id, l_ins_task_name, l_ins_importance_level, l_ins_priority_code
from jtf_tasks_b tb, jtf_tasks_tl tt, jtf_task_priorities_vl tp, ieu_uwqm_priorities_b ip
where tb.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
and tb.entity = 'TASK'
and tb.task_id = tt.task_id
and tt.language = userenv('LANG')
and tp.task_priority_id = nvl(tb.task_priority_id, 4)
and least(tp.importance_level, 4) = ip.priority_level;
-- insert into p_temp(msg) values('raising err ');
select 'CLOSE' into l_ins_task_status
from jtf_task_statuses_vl
where (nvl(closed_flag, 'N') = 'Y'
or nvl(completed_flag, 'N') = 'Y'
or nvl(cancelled_flag, 'N') = 'Y'
or nvl(rejected_flag, 'N') = 'Y')
and task_status_id = l_ins_task_status_id;
select 'SLEEP' into l_ins_task_status
from jtf_task_statuses_vl
where nvl(on_hold_flag, 'N') = 'Y'
and task_status_id = l_ins_task_status_id;
insert into p_temp(msg) values('1'||l_ins_task_id||' '|| l_ins_task_number||' '|| l_ins_customer_id||' '|| l_ins_owner_id||' '|| l_ins_owner_type_code);
insert into p_temp(msg) values('2'||l_ins_source_object_id||' '|| l_ins_source_object_type_code||' '|| l_ins_due_date||' '|| l_ins_planned_start_date);
insert into p_temp(msg) values('3'||l_ins_planned_end_date||' '|| l_ins_actual_ins_start_date||' '|| l_ins_actual_ins_end_date||' '|| l_ins_scheduled_start_date||' '|| l_ins_scheduled_end_date);
insert into p_temp(msg) values('4'||l_ins_task_type_id||' '|| l_ins_task_status_id||' '|| l_ins_task_name||' '|| l_ins_importance_level||' '|| l_ins_priority_code);
select owner_id, owner_type, source_object_id, source_object_type_code, assignee_id -- Niraj, 4220060, Added assignee_id
into l_owner_id, l_owner_type, l_source_object_id, l_source_object_type_code, l_wr_assignee_id -- Niraj, 4220060, Added
from ieu_uwqm_items
where workitem_pk_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
-- and workitem_obj_code = 'TASK';
select 'OPEN' INTO l_status
from jtf_task_statuses_vl b
where nvl(b.closed_flag, 'N') = 'N'
and nvl(b.completed_flag, 'N') = 'N'
and nvl(b.cancelled_flag, 'N') = 'N'
and nvl(b.rejected_flag, 'N') = 'N'
and b.task_status_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.assignment_status_id;
-- insert into p_temp(msg) values('selecting asg id from jtf tsk asg table');
select c.resource_id, c.resource_type_code
into l_assignee_id, l_assignee_type
from jtf_task_assignments c
where c.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
and c.last_update_date = (select max(a.last_update_date)
from jtf_task_assignments a,jtf_task_statuses_vl b
where a.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
and a.assignee_role = 'ASSIGNEE'
and a.resource_type_code <> 'RS_TEAM'
and a.assignment_status_id = b.task_status_id
and (nvl(b.closed_flag, 'N') = 'N'
and nvl(b.completed_flag, 'N') = 'N'
and nvl(b.cancelled_flag, 'N') = 'N'
and nvl(b.rejected_flag, 'N') = 'N'
and b.task_status_id = c.assignment_status_iD))
and assignee_role = 'ASSIGNEE'
and c.resource_type_code <> 'RS_TEAM'
and c.task_assignment_id <> jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id
and rownum < 2;
--insert into p_temp(msg) values('setting asg id to hooks data');
select task_type_id, entity into l_task_type_id, l_entity
from jtf_tasks_b
where task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id;
select count(0) into l_task_asg_count
from jtf_task_assignments
where task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
and assignee_role = 'ASSIGNEE';
--insert into p_temp(msg) values('asg type: '||l_assignee_type||' asg id: '||l_assignee_id|| ' own id: '||l_owner_id);
select 'Y' into l_group_id
from jtf_rs_group_members
where resource_id = l_assignee_id
and group_id = l_owner_id
and nvl(delete_flag,'N') <> 'Y'
and rownum < 2;
select assignee_id, assignee_type_actual
into l_assignee_id, l_assignee_type
from ieu_uwqm_items
where workitem_pk_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
-- and workitem_obj_code = 'TASK';
select c.resource_id, c.resource_type_code
into l_assignee_id, l_assignee_type
from jtf_task_assignments c
where c.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
and c.assignee_role = 'ASSIGNEE'
and c.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
and c.resource_id in ( select resource_id
from jtf_rs_group_members
where group_id = l_owner_id
and nvl(delete_flag,'N') <> 'Y')
--and c.task_assignment_id <> jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id
and c.last_update_date = (select max(a.last_update_date)
from jtf_task_assignments a,jtf_task_statuses_vl b
where a.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
and a.assignee_role = 'ASSIGNEE'
and a.task_assignment_id <> jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id
and a.assignment_status_id = b.task_status_id
and a.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
and a.resource_id in ( select resource_id
from jtf_rs_group_members
where group_id = l_owner_id
and nvl(delete_flag,'N') <> 'Y')
and (nvl(b.closed_flag, 'N') = 'N'
and nvl(b.completed_flag, 'N') = 'N'
and nvl(b.cancelled_flag, 'N') = 'N'
and nvl(b.rejected_flag, 'N') = 'N'
and b.task_status_id = c.assignment_status_id)
)
and rownum < 2;
-- insert into p_temp(msg) values ('excep: '||l_SQL_ERR|| l_SQL_CODE);
l_update_wr_item_call := 'Y';
l_update_wr_item_call := 'N';
-- insert into p_temp(msg) values ('update flag: '||l_update_wr_item_call);
-- Start 'l_update_wr_item_call' check
IF (l_update_wr_item_call = 'Y') Then -- Niraj, 4220060
l_workitem_comment_code2 := 'GRP_MAX_ASSGN';
('WORKITEM_UPDATE',
'UPDATE_WR_ITEM',
690,
'IEU_TASKS_USERHOOKS.CREATE_TASK_ASSIGN_UWQM_PRE',
l_workitem_comment_code1,
l_workitem_comment_code2,
null,
null,
null);
/* insert into p_temp(msg) values ('asg role: '||jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role ||
' entity: '||l_entity|| ' group id: '||l_group_id); */
-- insert into p_temp(msg) values (' calling insert ');
--insert into p_temp(msg) values (' calling update ');
IEU_WR_PUB.UPDATE_WR_ITEM(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_workitem_obj_code => 'TASK',
p_workitem_pk_id => jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id,
p_title => FND_API.G_MISS_CHAR,
p_party_id => FND_API.G_MISS_NUM,
p_priority_code => FND_API.G_MISS_CHAR,
p_due_date => l_ins_due_date,
p_owner_id => FND_API.G_MISS_NUM,
p_owner_type => FND_API.G_MISS_CHAR,
p_assignee_id => l_assignee_id,
p_assignee_type => l_assignee_type,
p_source_object_id => FND_API.G_MISS_NUM,
p_source_object_type_code => FND_API.G_MISS_CHAR,
p_application_id => 690,
p_user_id => FND_GLOBAL.USER_ID,
p_login_id => FND_GLOBAL.LOGIN_ID,
p_work_item_status => FND_API.G_MISS_CHAR,
p_audit_trail_rec => l_audit_trail_rec,
x_msg_count => L_MSG_COUNT,
x_msg_data => L_MSG_DATA,
x_return_status => L_RETURN_STATUS);
-- Create or Update Task User Hook should have been invoked
-- before invoking Create/Update Task Assignment if the assignee role is owner.
-- So update the rec only if its present in IEU_UWQM_ITEMS
if (l_wi_exists <> 'N')
then
--insert into p_temp(msg) values (' calling update ');
IEU_WR_PUB.UPDATE_WR_ITEM(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_workitem_obj_code => 'TASK',
p_workitem_pk_id => jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id,
p_title => FND_API.G_MISS_CHAR,
p_party_id => FND_API.G_MISS_NUM,
p_priority_code => FND_API.G_MISS_CHAR,
p_due_date => l_ins_due_date,
p_owner_id => FND_API.G_MISS_NUM,
p_owner_type => FND_API.G_MISS_CHAR,
p_assignee_id => l_assignee_id,
p_assignee_type => l_assignee_type,
p_source_object_id => FND_API.G_MISS_NUM,
p_source_object_type_code => FND_API.G_MISS_CHAR,
p_application_id => 690,
p_user_id => FND_GLOBAL.USER_ID,
p_login_id => FND_GLOBAL.LOGIN_ID,
p_work_item_status => FND_API.G_MISS_CHAR,
p_audit_trail_rec => l_audit_trail_rec,
x_msg_count => L_MSG_COUNT,
x_msg_data => L_MSG_DATA,
x_return_status => L_RETURN_STATUS);
-- insert into p_temp(msg) values (' wr proc was not called..returning success ');
-- End 'l_update_wr_item_call' check
end if; /* Work Item exists in UWQ Work Repository */
PROCEDURE update_task_assign_uwqm_pre ( x_return_status OUT NOCOPY VARCHAR2 ) As
l_msg_count NUMBER;
l_other_asg_update_flag varchar2(5);
l_update_wr_item_call varchar2(5);
l_ins_date_selected varchar2(1);
/*** Update Task Assignee
** First the Activation Status is checked
** If the Work Source is activated, then check if any Tasks Rules Function is registered
** If the task Rules function is present, then execute it
** If the Task Rules function is not registered then the assignee will be created based on the following rules for Standard Tasks
** 1. Update assignee in UWQ if Owner is a Group and the assignee should be a member of the group
** 2. If the Owner is not a group, then Assignee will not be created in UWQ
** 3. If the Assignee is not a member of the Group, then UWQ Assignee will be the most recent Group member if it exists.
** If there are no group members present, then the assignee will be null
***/
-- reset del task pkg lvl variable
l_del_task_id := null;
select source_object_type_code
into l_task_source_obj_type_code
from jtf_tasks_b
where task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id;
Select ws_id
into l_ws_id1
from ieu_uwqm_work_sources_b
--where object_code = 'TASK'
--and nvl(not_valid_flag,'N') = 'N';
Select ws_id
into l_ws_id2
from ieu_uwqm_work_sources_b
where object_code = l_task_source_obj_type_code
--and nvl(not_valid_flag,'N') = 'N';
SELECT a.ws_id, b.ws_code
INTO l_association_ws_id, l_association_ws_code
FROM ieu_uwqm_ws_assct_props a, ieu_uwqm_work_sources_b b
WHERE child_ws_id = l_ws_id1
AND parent_ws_id = l_ws_id2
AND a.ws_id = b.ws_id
--AND nvl(b.not_valid_flag,'N') = 'N';
SELECT ws_b.tasks_rules_function
INTO l_tasks_rules_func
FROM ieu_uwqm_ws_assct_props ws_b
WHERE ws_b.ws_id = l_association_ws_id;
/* insert into p_temp(msg) values ('assignee_role: '||jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role||
' booking end date: '|| jtf_task_assignments_pub.p_task_assignments_user_hooks.booking_end_date); */
SELECT booking_end_date
INTO l_ins_due_date
FROM JTF_TASK_ALL_ASSIGNMENTS
WHERE task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
AND assignee_role = 'OWNER';
'UPDATE_TASK_ASG',
jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id,
null,
FND_API.G_MISS_CHAR,
FND_API.G_MISS_CHAR,
FND_API.G_MISS_NUM,
FND_API.G_MISS_NUM,
FND_API.G_MISS_NUM,
FND_API.G_MISS_NUM,
FND_API.G_MISS_CHAR,
FND_API.G_MISS_NUM,
l_task_source_obj_type_code,
FND_API.G_MISS_NUM,
NULL,
FND_API.G_MISS_DATE,
FND_API.G_MISS_DATE,
FND_API.G_MISS_DATE,
FND_API.G_MISS_DATE,
FND_API.G_MISS_DATE,
FND_API.G_MISS_DATE,
jtf_task_assignments_pub.p_task_assignments_user_hooks.resource_type_code,
jtf_task_assignments_pub.p_task_assignments_user_hooks.resource_id,
jtf_task_assignments_pub.p_task_assignments_user_hooks.assignment_status_id);
Select owner_id
into l_orig_grp_owner
from ieu_uwqm_items
where WORKITEM_PK_ID = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
-- and workitem_obj_code = 'TASK'
-- and owner_type_actual = 'RS_GROUP';
select task_status_id, deleted_flag
into l_tsk_sts_id, l_del_flag
from jtf_tasks_b
where task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id;
select 'CLOSE' into l_task_status
from jtf_task_statuses_vl
where (nvl(closed_flag, 'N') = 'Y'
or nvl(completed_flag, 'N') = 'Y'
or nvl(cancelled_flag, 'N') = 'Y'
or nvl(rejected_flag, 'N') = 'Y')
and task_status_id = l_tsk_sts_id;
select 'Y'
into l_wi_exists
from ieu_uwqm_items
where workitem_pk_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
and workitem_obj_code = 'TASK';
-- insert into p_temp(msg) values ('Task Status Id from Tasks table: '||l_tsk_sts_id||' l_wi_exists: '||l_wi_exists||' l_del_flag: '||l_del_flag);
-- Closed Work Items are not currently migrated. No updates will be done to UWQ Metaphor table
-- if the Task is in closed/deleted status and the Work Item is not present in UWQ table.
if ( ( (l_task_status = 'CLOSE') or (l_del_flag = 'Y') )
and (l_wi_exists = 'N'))
then
-- insert into p_temp(msg) values ('close/del and rec does not exists.. ret success');
-- If the work item status is not closed or deleted and if the work item does not exist in Work Repository
-- then create the work item
if (l_wi_exists = 'N')
then
--insert into p_temp values (' selecting data from tasks table');
select tb.task_id, tb.task_number, tb.customer_id, tb.owner_id, tb.owner_type_code,
tb.source_object_id, tb.source_object_type_code,
tb.planned_start_date, tb.planned_end_date, tb.actual_start_date, tb.actual_end_date,
tb.scheduled_start_date, tb.scheduled_end_date,tb.task_type_id,
tb.task_status_id, tt.task_name, tp.importance_level, ip.priority_code
into l_ins_task_id, l_ins_task_number, l_ins_customer_id, l_ins_owner_id, l_ins_owner_type_code,
l_ins_source_object_id, l_ins_source_object_type_code,
l_ins_planned_start_date,
l_ins_planned_end_date, l_ins_actual_ins_start_date, l_ins_actual_ins_end_date, l_ins_scheduled_start_date, l_ins_scheduled_end_date,
l_ins_task_type_id, l_ins_task_status_id, l_ins_task_name, l_ins_importance_level, l_ins_priority_code
from jtf_tasks_b tb, jtf_tasks_tl tt, jtf_task_priorities_vl tp, ieu_uwqm_priorities_b ip
where tb.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
and tb.entity = 'TASK'
and tb.task_id = tt.task_id
and tt.language = userenv('LANG')
and tp.task_priority_id = nvl(tb.task_priority_id, 4)
and least(tp.importance_level, 4) = ip.priority_level;
-- insert into p_temp values('raising err ');
select 'CLOSE' into l_ins_task_status
from jtf_task_statuses_vl
where (nvl(closed_flag, 'N') = 'Y'
or nvl(completed_flag, 'N') = 'Y'
or nvl(cancelled_flag, 'N') = 'Y'
or nvl(rejected_flag, 'N') = 'Y')
and task_status_id = l_ins_task_status_id;
select 'SLEEP' into l_ins_task_status
from jtf_task_statuses_vl
where nvl(on_hold_flag, 'N') = 'Y'
and task_status_id = l_ins_task_status_id;
insert into p_temp values('1'||l_ins_task_id||' '|| l_ins_task_number||' '|| l_ins_customer_id||' '|| l_ins_owner_id||' '|| l_ins_owner_type_code);
insert into p_temp values('2'||l_ins_source_object_id||' '|| l_ins_source_object_type_code||' '|| l_ins_due_date||' '|| l_ins_planned_start_date);
insert into p_temp values('3'||l_ins_planned_end_date||' '|| l_ins_actual_ins_start_date||' '|| l_ins_actual_ins_end_date||' '|| l_ins_scheduled_start_date||' '|| l_ins_scheduled_end_date);
insert into p_temp values('4'||l_ins_task_type_id||' '|| l_ins_task_status_id||' '|| l_ins_task_name||' '|| l_ins_importance_level||' '|| l_ins_priority_code);
select owner_id, owner_type, source_object_id, source_object_type_code, assignee_id -- Niraj, 4220060, Added assignee_id
into l_owner_id, l_owner_type, l_source_object_id, l_source_object_type_code, l_wr_assignee_id -- Niraj, 4220060, Added
from ieu_uwqm_items
where workitem_pk_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
-- and workitem_obj_code = 'TASK';
select 'OPEN' INTO l_status
from jtf_task_statuses_vl b
where nvl(b.closed_flag, 'N') = 'N'
and nvl(b.completed_flag, 'N') = 'N'
and nvl(b.cancelled_flag, 'N') = 'N'
and nvl(b.rejected_flag, 'N') = 'N'
and b.task_status_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.assignment_status_id;
select c.resource_id, c.resource_type_code
into l_assignee_id, l_assignee_type
from jtf_task_assignments c
where c.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
and c.last_update_date = (select max(a.last_update_date)
from jtf_task_assignments a,jtf_task_statuses_vl b
where a.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
and a.assignee_role = 'ASSIGNEE'
and a.resource_type_code <> 'RS_TEAM'
and a.assignment_status_id = b.task_status_id
and (nvl(b.closed_flag, 'N') = 'N'
and nvl(b.completed_flag, 'N') = 'N'
and nvl(b.cancelled_flag, 'N') = 'N'
and nvl(b.rejected_flag, 'N') = 'N'
and b.task_status_id = c.assignment_status_iD))
and assignee_role = 'ASSIGNEE'
and c.resource_type_code <> 'RS_TEAM'
and c.task_assignment_id <> jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id
and rownum < 2;
select task_type_id, entity into l_task_type_id, l_entity
from jtf_tasks_b
where task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id;
select 'Y' into l_group_id
from jtf_rs_group_members
where resource_id = l_assignee_id
and group_id = l_owner_id
and nvl(delete_flag,'N') <> 'Y'
and rownum < 2;
-- insert into p_temp values(' grp member: '||l_group_id);
select c.resource_id, c.resource_type_code
into l_assignee_id, l_assignee_type
from jtf_task_assignments c
where c.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
and c.assignee_role = 'ASSIGNEE'
and c.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
and c.resource_id in ( select resource_id
from jtf_rs_group_members
where group_id = l_owner_id
and nvl(delete_flag,'N') <> 'Y')
--and c.task_assignment_id <> jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id
and c.last_update_date = (select max(a.last_update_date)
from jtf_task_assignments a,jtf_task_statuses_vl b
where a.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
and a.assignee_role = 'ASSIGNEE'
and a.task_assignment_id <> jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id
and a.assignment_status_id = b.task_status_id
and a.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
and a.resource_id in ( select resource_id
from jtf_rs_group_members
where group_id = l_owner_id
and nvl(delete_flag,'N') <> 'Y')
and (nvl(b.closed_flag, 'N') = 'N'
and nvl(b.completed_flag, 'N') = 'N'
and nvl(b.cancelled_flag, 'N') = 'N'
and nvl(b.rejected_flag, 'N') = 'N'
and b.task_status_id = c.assignment_status_id)
)
and rownum < 2;
-- insert into p_temp values ('excep: '||l_SQL_ERR|| l_SQL_CODE);
/** insert into p_temp values('task id: '||jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id||
' task asg id: '||jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id||
' owner id: '||l_owner_id ||
' asg id: '||l_assignee_id||' asg type: '|| l_assignee_type); **/
l_update_wr_item_call := 'Y';
l_update_wr_item_call := 'N';
-- Start 'l_update_wr_item_call' check
IF (l_update_wr_item_call = 'Y') Then -- Niraj, 4220060
l_audit_trail_rec := SYSTEM.WR_AUDIT_TRAIL_NST();
('WORKITEM_UPDATE',
'UPDATE_WR_ITEM',
690,
'IEU_TASKS_USERHOOKS.UPDATE_TASK_ASSIGN_UWQM_PRE',
l_workitem_comment_code1,
l_workitem_comment_code2,
null,
null,
null);
-- and (nvl(l_update_wr_item_call, 'Y') = 'Y')
then
if (l_wi_exists = 'N')
then
-- insert into p_temp values (' calling insert ');
-- insert into p_temp values(' calling update wr item' );
IEU_WR_PUB.UPDATE_WR_ITEM(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_workitem_obj_code => 'TASK',
p_workitem_pk_id => jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id,
p_title => FND_API.G_MISS_CHAR,
p_party_id => FND_API.G_MISS_NUM,
p_priority_code => FND_API.G_MISS_CHAR,
p_due_date => l_ins_due_date,
p_owner_id => FND_API.G_MISS_NUM,
p_owner_type => FND_API.G_MISS_CHAR,
p_assignee_id => l_assignee_id,
p_assignee_type => l_assignee_type,
p_source_object_id => FND_API.G_MISS_NUM,
p_source_object_type_code => FND_API.G_MISS_CHAR,
p_application_id => 690,
p_user_id => FND_GLOBAL.USER_ID,
p_login_id => FND_GLOBAL.LOGIN_ID,
p_work_item_status => FND_API.G_MISS_CHAR,
p_audit_trail_rec => l_audit_trail_rec,
x_msg_count => L_MSG_COUNT,
x_msg_data => L_MSG_DATA,
x_return_status => L_RETURN_STATUS);
-- insert into p_temp values(' called update wr items.. ret sts: '||x_return_status);
-- Create or Update Task User Hook should have been invoked
-- before invoking Create/Update Task Assignment if the assignee role is owner.
-- So update the rec only if its present in IEU_UWQM_ITEMS
if (l_wi_exists <> 'N')
then
-- insert into p_temp(msg) values(' calling update wr item' );
IEU_WR_PUB.UPDATE_WR_ITEM(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_workitem_obj_code => 'TASK',
p_workitem_pk_id => jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id,
p_title => FND_API.G_MISS_CHAR,
p_party_id => FND_API.G_MISS_NUM,
p_priority_code => FND_API.G_MISS_CHAR,
p_due_date => l_ins_due_date,
p_owner_id => FND_API.G_MISS_NUM,
p_owner_type => FND_API.G_MISS_CHAR,
p_assignee_id => l_assignee_id,
p_assignee_type => l_assignee_type,
p_source_object_id => FND_API.G_MISS_NUM,
p_source_object_type_code => FND_API.G_MISS_CHAR,
p_application_id => 690,
p_user_id => FND_GLOBAL.USER_ID,
p_login_id => FND_GLOBAL.LOGIN_ID,
p_work_item_status => FND_API.G_MISS_CHAR,
p_audit_trail_rec => l_audit_trail_rec,
x_msg_count => L_MSG_COUNT,
x_msg_data => L_MSG_DATA,
x_return_status => L_RETURN_STATUS);
-- End 'l_update_wr_item_call' check
end if; /* Task Work Item exists in UWQ Metaphor */
end update_task_assign_uwqm_pre;
PROCEDURE delete_task_assign_uwqm_pre (x_return_status OUT NOCOPY VARCHAR2 ) As
l_msg_count number;
l_other_asg_deleted_flag varchar2(5);
l_ins_date_selected varchar2(1);
select task_id
into l_task_id
from jtf_task_all_assignments
where task_assignment_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id;
select source_object_type_code
into l_task_source_obj_type_code
from jtf_tasks_b
where task_id = l_task_id;
Select ws_id
into l_ws_id1
from ieu_uwqm_work_sources_b
--where object_code = 'TASK'
--and nvl(not_valid_flag,'N') = 'N';
Select ws_id
into l_ws_id2
from ieu_uwqm_work_sources_b
where object_code = l_task_source_obj_type_code
--and nvl(not_valid_flag,'N') = 'N';
SELECT a.ws_id, b.ws_code
INTO l_association_ws_id, l_association_ws_code
FROM ieu_uwqm_ws_assct_props a, ieu_uwqm_work_sources_b b
WHERE child_ws_id = l_ws_id1
AND parent_ws_id = l_ws_id2
AND a.ws_id = b.ws_id
--AND nvl(b.not_valid_flag,'N') = 'N';
SELECT ws_b.tasks_rules_function
INTO l_tasks_rules_func
FROM ieu_uwqm_ws_assct_props ws_b
WHERE ws_b.ws_id = l_association_ws_id;
/* insert into p_temp(msg) values ('assignee_role: '||jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role||
' booking end date: '|| jtf_task_assignments_pub.p_task_assignments_user_hooks.booking_end_date); */
SELECT booking_end_date
INTO l_ins_due_date
FROM JTF_TASK_ALL_ASSIGNMENTS
WHERE task_id = (select task_id from jtf_task_all_assignments
where task_assignment_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id)
AND assignee_role = 'OWNER';
--insert into p_temp(msg) values (' selected due date: '||l_ins_due_date);
'DELETE_TASK_ASG',
l_task_id,
null,
FND_API.G_MISS_CHAR,
FND_API.G_MISS_CHAR,
FND_API.G_MISS_NUM,
FND_API.G_MISS_NUM,
FND_API.G_MISS_NUM,
FND_API.G_MISS_NUM,
FND_API.G_MISS_CHAR,
FND_API.G_MISS_NUM,
l_task_source_obj_type_code,
FND_API.G_MISS_NUM,
NULL,
FND_API.G_MISS_DATE,
FND_API.G_MISS_DATE,
FND_API.G_MISS_DATE,
FND_API.G_MISS_DATE,
FND_API.G_MISS_DATE,
FND_API.G_MISS_DATE,
null,
null,
null);
Select owner_id
into l_orig_grp_owner
from ieu_uwqm_items
where WORKITEM_PK_ID = l_task_id
-- and workitem_obj_code = 'TASK'
-- and owner_type_actual = 'RS_GROUP';
select task_status_id, deleted_flag
into l_tsk_sts_id, l_del_flag
from jtf_tasks_b
where task_id = l_task_id;
select 'CLOSE' into l_task_status
from jtf_task_statuses_vl
where (nvl(closed_flag, 'N') = 'Y'
or nvl(completed_flag, 'N') = 'Y'
or nvl(cancelled_flag, 'N') = 'Y'
or nvl(rejected_flag, 'N') = 'Y')
and task_status_id = l_tsk_sts_id;
select 'Y'
into l_wi_exists
from ieu_uwqm_items
where workitem_pk_id =l_task_id
and workitem_obj_code = 'TASK';
-- insert into p_temp(msg) values ('del tsk asg..Task Status Id from Tasks table: '||l_tsk_sts_id||' l_wi_exists: '||l_wi_exists||' l_del_flag: '||l_del_flag);
-- Closed Work Items are not currently migrated. No updates will be done to UWQ Metaphor table
-- if the Task is in closed/deleted status and the Work Item is not present in UWQ table.
if (l_wi_exists = 'N')
then
if ( (l_task_status = 'CLOSE') or (l_del_flag = 'Y') )
then
--insert into p_temp(msg) values ('close/del and rec does not exists.. ret success');
-- This work item is being deleted
begin
select count(0) into l_task_asg_count
from jtf_task_assignments
where task_id = l_task_id
and assignee_role = 'ASSIGNEE';
--insert into p_temp values ('del based on l_del_task_id.. task asg cnt: '||l_task_asg_count);
-- Set the del_task_id to null if this is the last assignee being deleted.
if (l_task_asg_count = 1)
then
l_del_task_id := null;
--insert into p_temp values (' selecting data from tasks table');
select tb.task_id, tb.task_number, tb.customer_id, tb.owner_id, tb.owner_type_code,
tb.source_object_id, tb.source_object_type_code,
-- decode(tb.date_selected, 'P', tb.planned_end_date,
-- 'A', tb.actual_end_date, 'S', tb.scheduled_end_date, null, tb.scheduled_end_date) due_date,
tb.planned_start_date, tb.planned_end_date, tb.actual_start_date, tb.actual_end_date,
tb.scheduled_start_date, tb.scheduled_end_date,tb.task_type_id,
tb.task_status_id, tt.task_name, tp.importance_level, ip.priority_code, tb.entity
into l_ins_task_id, l_ins_task_number, l_ins_customer_id, l_ins_owner_id, l_ins_owner_type_code,
l_ins_source_object_id, l_ins_source_object_type_code,
l_ins_planned_start_date, l_ins_planned_end_date, l_ins_actual_ins_start_date, l_ins_actual_ins_end_date, l_ins_scheduled_start_date, l_ins_scheduled_end_date,
l_ins_task_type_id, l_ins_task_status_id, l_ins_task_name, l_ins_importance_level, l_ins_priority_code, l_entity
from jtf_tasks_b tb, jtf_tasks_tl tt, jtf_task_priorities_vl tp, ieu_uwqm_priorities_b ip
where tb.task_id = l_task_id
and tb.entity = 'TASK'
and tb.task_id = tt.task_id
and tt.language = userenv('LANG')
and tp.task_priority_id = nvl(tb.task_priority_id, 4)
and least(tp.importance_level, 4) = ip.priority_level;
-- insert into p_temp values('raising err ');
select 'CLOSE' into l_ins_task_status
from jtf_task_statuses_vl
where (nvl(closed_flag, 'N') = 'Y'
or nvl(completed_flag, 'N') = 'Y'
or nvl(cancelled_flag, 'N') = 'Y'
or nvl(rejected_flag, 'N') = 'Y')
and task_status_id = l_ins_task_status_id;
select 'SLEEP' into l_ins_task_status
from jtf_task_statuses_vl
where nvl(on_hold_flag, 'N') = 'Y'
and task_status_id = l_ins_task_status_id;
insert into p_temp values('1'||l_ins_task_id||' '|| l_ins_task_number||' '|| l_ins_customer_id||' '|| l_ins_owner_id||' '|| l_ins_owner_type_code);
insert into p_temp values('2'||l_ins_source_object_id||' '|| l_ins_source_object_type_code||' '|| l_ins_due_date||' '|| l_ins_planned_start_date);
insert into p_temp values('3'||l_ins_planned_end_date||' '|| l_ins_actual_ins_start_date||' '|| l_ins_actual_ins_end_date||' '|| l_ins_scheduled_start_date||' '|| l_ins_scheduled_end_date);
insert into p_temp values('4'||l_ins_task_type_id||' '|| l_ins_task_status_id||' '|| l_ins_task_name||' '|| l_ins_importance_level||' '|| l_ins_priority_code);
select c.resource_id, c.resource_type_code
into l_assignee_id, l_assignee_type
from jtf_task_assignments c
where c.task_id = l_task_id
and c.assignee_role = 'ASSIGNEE'
and c.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
and c.resource_id in ( select resource_id
from jtf_rs_group_members
where group_id = l_owner_id
and nvl(delete_flag,'N') <> 'Y')
--and c.task_assignment_id <> jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id
and c.last_update_date = (select max(a.last_update_date)
from jtf_task_assignments a,jtf_task_statuses_vl b
where a.task_id = l_task_id
and a.assignee_role = 'ASSIGNEE'
and a.task_assignment_id <> jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id
and a.assignment_status_id = b.task_status_id
and a.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
and a.resource_id in ( select resource_id
from jtf_rs_group_members
where group_id = l_owner_id
and nvl(delete_flag,'N') <> 'Y')
and (nvl(b.closed_flag, 'N') = 'N'
and nvl(b.completed_flag, 'N') = 'N'
and nvl(b.cancelled_flag, 'N') = 'N'
and nvl(b.rejected_flag, 'N') = 'N'
and b.task_status_id = c.assignment_status_id)
)
and rownum < 2;
-- insert into p_temp values ('excep: '||l_SQL_ERR|| l_SQL_CODE);
('WORKITEM_UPDATE',
'UPDATE_WR_ITEM',
690,
'IEU_TASKS_USERHOOKS.DELETE_TASK_ASSIGN_UWQM_PRE',
l_workitem_comment_code1,
l_workitem_comment_code2,
null,
null,
null);
--insert into p_temp values ('entity: '||l_entity);
--insert into p_temp values ('create wr item');
select resource_id, resource_type_code
into l_assignee_id, l_assignee_type
from jtf_task_assignments c
where c.task_id = l_task_id
and task_assignment_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id;
select task_type_id, entity into l_task_type_id, l_entity
from jtf_tasks_b
where task_id = l_task_id;
select owner_id, owner_type, source_object_id, source_object_type_code
into l_owner_id, l_owner_type, l_source_object_id, l_source_object_type_code
from ieu_uwqm_items
where workitem_pk_id = l_task_id
-- and workitem_obj_code = 'TASK';
select assignee_id, assignee_type_actual
into l_uwq_assignee_id, l_uwq_assignee_type
from ieu_uwqm_items
where workitem_pk_id = l_task_id
-- and workitem_obj_code = 'TASK';
l_other_asg_deleted_flag := 'N';
select c.resource_id, c.resource_type_code
into l_assignee_id, l_assignee_type
from jtf_task_assignments c
where c.task_id = l_task_id
and c.assignee_role = 'ASSIGNEE'
and c.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
and c.resource_id in ( select resource_id
from jtf_rs_group_members
where group_id = l_owner_id
and nvl(delete_flag,'N') <> 'Y')
and c.last_update_date = (select max(a.last_update_date)
from jtf_task_assignments a,jtf_task_statuses_vl b
where a.task_id = l_task_id
and a.assignee_role = 'ASSIGNEE'
and a.assignment_status_id = b.task_status_id
and a.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
and a.resource_id in ( select resource_id
from jtf_rs_group_members
where group_id = l_owner_id
and nvl(delete_flag,'N') <> 'Y')
and (nvl(b.closed_flag, 'N') = 'N'
and nvl(b.completed_flag, 'N') = 'N'
and nvl(b.cancelled_flag, 'N') = 'N'
and nvl(b.rejected_flag, 'N') = 'N'
and b.task_status_id = c.assignment_status_id)
and task_assignment_id <> jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id)
and rownum < 2;
l_other_asg_deleted_flag := 'Y';
('WORKITEM_UPDATE',
'UPDATE_WR_ITEM',
690,
'IEU_TASKS_USERHOOKS.DELETE_TASK_ASSIGN_UWQM_PRE',
l_workitem_comment_code1,
l_workitem_comment_code2,
null,
null,
null);
if (l_entity = 'TASK') and (nvl(l_other_asg_deleted_flag, 'N') = 'N')then
IEU_WR_PUB.UPDATE_WR_ITEM(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_workitem_obj_code => 'TASK',
p_workitem_pk_id => l_task_id,
p_title => FND_API.G_MISS_CHAR,
p_party_id => FND_API.G_MISS_NUM,
p_priority_code => FND_API.G_MISS_CHAR,
p_due_date => l_ins_due_date,
p_owner_id => FND_API.G_MISS_NUM,
p_owner_type => FND_API.G_MISS_CHAR,
p_assignee_id => l_assignee_id,
p_assignee_type => l_assignee_type,
p_source_object_id => FND_API.G_MISS_NUM,
p_source_object_type_code => FND_API.G_MISS_CHAR,
p_application_id => 690,
p_user_id => FND_GLOBAL.USER_ID,
p_login_id => FND_GLOBAL.LOGIN_ID,
p_work_item_status => FND_API.G_MISS_CHAR,
p_audit_trail_rec => l_audit_trail_rec,
x_msg_data => L_MSG_DATA,
x_msg_count => l_msg_count,
x_return_status => L_RETURN_STATUS);
(nvl(l_other_asg_deleted_flag, 'N') = 'Y') then
x_return_status := fnd_api.g_ret_sts_success;
end delete_task_assign_uwqm_pre;