[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
P_LAST_UPDATE_DATE IN DATE DEFAULT NULL
)
IS
--** This cursor represents the Associate work items that are closed and purged **--
--** Here work items whose parents are not open are picked **--
/*Cursor c1_purge_assct_wi(p_ws_id IN NUMBER,p_parent_ws_id IN NUMBER) is
Select workitem_pk_id, workitem_obj_code, source_object_id, source_object_type_code,
owner_id, owner_type, assignee_id, assignee_type, status_id, priority_id, due_date,
reschedule_time, distribution_status_id,work_item_number, work_item_id
from ieu_uwqm_items child
where ws_id = p_ws_id
and status_id in (3, 4)
and ( to_date(trunc(last_update_date), 'dd-mm-rrrr') <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr') )
and not exists
(select 1
from ieu_uwqm_items parent
where ws_id = p_parent_ws_id
and status_id in (0,5)
and parent.workitem_pk_id = child.source_object_id
and parent.workitem_obj_code = child.source_object_type_code);
Select workitem_pk_id, workitem_obj_code, source_object_id, source_object_type_code
from ieu_uwqm_items
where ws_id = p_ws_id
and status_id in (3, 4)
and ( to_date(trunc(last_update_date), 'dd-mm-rrrr') <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr') )
and (source_object_id, source_object_type_code) IN
(select workitem_pk_id, workitem_obj_code
from ieu_uwqm_items
where ws_id = p_parent_ws_id
and status_id in (0,5) );
Select workitem_pk_id, workitem_obj_code, source_object_id, source_object_type_code,
owner_id, owner_type, assignee_id, assignee_type, status_id, priority_id, due_date,
reschedule_time, distribution_status_id,work_item_number,work_item_id
from ieu_uwqm_items parent
where ws_id = p_ws_id
and status_id in (3, 4)
and ( to_date(trunc(last_update_date), 'dd-mm-rrrr') <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr') )
and not exists
(select source_object_id, source_object_type_code
from ieu_uwqm_items child
where ws_id = p_child_ws_id
and status_id in (0,5)
and child.workitem_pk_id = parent.source_object_id
and child.workitem_obj_code = parent.source_object_type_code);
/*Select workitem_pk_id, workitem_obj_code, source_object_id, source_object_type_code
from ieu_uwqm_items
where ws_id = p_ws_id
and status_id in (3, 4)
and ( to_date(trunc(last_update_date), 'dd-mm-rrrr') <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr') )
and (workitem_pk_id, workitem_obj_code) in
(select source_object_id, source_object_type_code
from ieu_uwqm_items
where ws_id = p_child_ws_id
and status_id in (0,5) );*/
Select parent.workitem_pk_id, parent.workitem_obj_code, child.workitem_pk_id, child.workitem_obj_code
from ieu_uwqm_items parent , ieu_uwqm_items child
where parent.ws_id = p_ws_id
and parent.status_id in (3, 4)
and ( to_date(trunc(parent.last_update_date), 'dd-mm-rrrr') <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr') )
and parent.workitem_pk_id = child.source_object_id
and parent.workitem_obj_code = child.source_object_type_code
and child.ws_id = p_child_ws_id
and child.status_id in (0,5) ;
Select workitem_pk_id, workitem_obj_code, source_object_id, source_object_type_code,
owner_id, owner_type, assignee_id, assignee_type, status_id, priority_id, due_date,
reschedule_time, distribution_status_id,work_item_number,work_item_id
from ieu_uwqm_items
where status_id in(3,4)
and ws_id = p_ws_id
and to_date(trunc(last_update_date), 'dd-mm-rrrr') <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr') ;
FND_MESSAGE.SET_TOKEN('LU_DATE', p_last_update_date);
SELECT WS_B.WS_ID, WS_B.WS_TYPE, WS_A.DIST_ST_BASED_ON_PARENT_FLAG,
WS_A.PARENT_WS_ID, WS_A.CHILD_WS_ID
INTO l_ws_id, l_ws_type, l_dist_st_based_on_parent, l_parent_ws_id, l_child_ws_id
FROM IEU_UWQM_WORK_SOURCES_B WS_B, IEU_UWQM_WS_ASSCT_PROPS WS_A
WHERE ws_b.ws_code = p_ws_code
AND ws_b.not_valid_flag = 'N'
AND ws_b.ws_id = ws_a.ws_id(+);
select count(*)
into l_closed_item_exists
from ieu_uwqm_items
where ws_id = l_parent_ws_id
and status_id in (3, 4)
and to_date(trunc(last_update_date), 'dd-mm-rrrr')
<= to_date(trunc(p_last_update_date), 'dd-mm-rrrr') ;
select count(*)
into l_wi_exists
from ieu_uwqm_items main
where status_id in (3,4)
and ws_id = l_parent_ws_id
and ( workitem_pk_id, workitem_obj_code) in
(Select source_object_id,source_object_type_code
from ieu_uwqm_items
where status_id in (3, 4)
and to_date(trunc(last_update_date), 'dd-mm-rrrr')
<= to_date(trunc(sysdate), 'dd-mm-rrrr') )
and not exists (select 1 from ieu_uwqm_items
where status_id in (0,5)
and source_object_id = main.workitem_pk_id
and source_object_type_code = main.workitem_obj_code
and to_date(trunc(last_update_date), 'dd-mm-rrrr')
<= to_date(trunc(sysdate), 'dd-mm-rrrr')) ;
select ws_code
into l_parent_ws_code
from ieu_uwqm_work_sources_b
where ws_id = l_parent_ws_id;
insert into IEU_UWQM_AUDIT_LOG
(AUDIT_LOG_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ACTION_KEY,
EVENT_KEY,
MODULE,
WS_CODE,
APPLICATION_ID,
WORKITEM_PK_ID,
WORKITEM_OBJ_CODE,
WORK_ITEM_NUMBER,
WORKITEM_STATUS_ID_PREV,
WORKITEM_STATUS_ID_CURR,
OWNER_ID_PREV,
OWNER_ID_CURR,
OWNER_TYPE_PREV,
OWNER_TYPE_CURR,
ASSIGNEE_ID_PREV,
ASSIGNEE_ID_CURR,
ASSIGNEE_TYPE_PREV,
ASSIGNEE_TYPE_CURR,
SOURCE_OBJECT_ID_PREV,
SOURCE_OBJECT_ID_CURR,
SOURCE_OBJECT_TYPE_CODE_PREV,
SOURCE_OBJECT_TYPE_CODE_CURR,
PARENT_WORKITEM_STATUS_ID_PREV,
PARENT_WORKITEM_STATUS_ID_CURR,
PARENT_DIST_STATUS_ID_PREV,
PARENT_DIST_STATUS_ID_CURR,
WORKITEM_DIST_STATUS_ID_PREV,
WORKITEM_DIST_STATUS_ID_CURR,
PRIORITY_ID_PREV,
PRIORITY_ID_CURR,
DUE_DATE_PREV,
DUE_DATE_CURR,
RESCHEDULE_TIME_PREV,
RESCHEDULE_TIME_CURR,
IEU_COMMENT_CODE1,
IEU_COMMENT_CODE2,
IEU_COMMENT_CODE3,
IEU_COMMENT_CODE4,
IEU_COMMENT_CODE5,
WORKITEM_COMMENT_CODE1,
WORKITEM_COMMENT_CODE2,
WORKITEM_COMMENT_CODE3,
WORKITEM_COMMENT_CODE4,
WORKITEM_COMMENT_CODE5,
RETURN_STATUS,
ERROR_CODE,
LOGGING_LEVEL)
values
(
IEU_UWQM_AUDIT_LOG_S1.NEXTVAL,
1,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
l_action_key,
l_event_key,
l_module,
P_WS_CODE,
690,
l_task_det_rec.l_workitem_pk_id_list(i),
l_task_det_rec.l_workitem_obj_code_list(i),
l_task_det_rec.l_work_item_number_list(i),
l_task_det_rec.l_status_id_list(i),
l_task_det_rec.l_status_id_list(i),
l_task_det_rec.l_owner_id_list(i),
l_task_det_rec.l_owner_id_list(i),
l_task_det_rec.l_owner_type_list(i),
l_task_det_rec.l_owner_type_list(i),
l_task_det_rec.l_assignee_id_list(i),
l_task_det_rec.l_assignee_id_list(i),
l_task_det_rec.l_assignee_type_list(i),
l_task_det_rec.l_assignee_type_list(i),
l_task_det_rec.l_source_object_id_list(i),
l_task_det_rec.l_source_object_id_list(i),
l_task_det_rec.l_source_object_type_code_list(i),
l_task_det_rec.l_source_object_type_code_list(i),
NULL,
NULL,
NULL,
NULL,
l_task_det_rec.l_distribution_status_id_list(i),
l_task_det_rec.l_distribution_status_id_list(i),
l_task_det_rec.l_priority_id_list(i),
l_task_det_rec.l_priority_id_list(i),
l_task_det_rec.l_due_date_list(i),
l_task_det_rec.l_due_date_list(i),
l_task_det_rec.l_reschedule_time_list(i),
l_task_det_rec.l_reschedule_time_list(i),
NULL,
NULL,
NULL,
NULL,
NULL,
l_workitem_comment_code1,
l_workitem_comment_code2,
l_workitem_comment_code3,
l_workitem_comment_code4,
l_workitem_comment_code5,
fnd_api.g_ret_sts_success,
NULL,
FND_PROFILE.VALUE('IEU_WR_DIST_AUDIT_LOG'));
delete
from ieu_uwqm_items child
where ws_id = l_ws_id
and status_id in (3, 4)
and to_date(trunc(last_update_date), 'dd-mm-rrrr') <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr')
and not exists
(select 1
from ieu_uwqm_items parent
where ws_id = l_parent_ws_id
and status_id in (0,5)
and parent.workitem_pk_id = child.source_object_id
and parent.workitem_obj_code = child.source_object_type_code);
SELECT WS_A.WS_ASSOCIATION_PROP_ID,WS_A.WS_ID
INTO l_assct_ws_id,l_child_ws_id
FROM IEU_UWQM_WS_ASSCT_PROPS WS_A
WHERE parent_ws_id = l_ws_id;
insert into IEU_UWQM_AUDIT_LOG
(AUDIT_LOG_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ACTION_KEY,
EVENT_KEY,
MODULE,
WS_CODE,
APPLICATION_ID,
WORKITEM_PK_ID,
WORKITEM_OBJ_CODE,
WORK_ITEM_NUMBER,
WORKITEM_STATUS_ID_PREV,
WORKITEM_STATUS_ID_CURR,
OWNER_ID_PREV,
OWNER_ID_CURR,
OWNER_TYPE_PREV,
OWNER_TYPE_CURR,
ASSIGNEE_ID_PREV,
ASSIGNEE_ID_CURR,
ASSIGNEE_TYPE_PREV,
ASSIGNEE_TYPE_CURR,
SOURCE_OBJECT_ID_PREV,
SOURCE_OBJECT_ID_CURR,
SOURCE_OBJECT_TYPE_CODE_PREV,
SOURCE_OBJECT_TYPE_CODE_CURR,
PARENT_WORKITEM_STATUS_ID_PREV,
PARENT_WORKITEM_STATUS_ID_CURR,
PARENT_DIST_STATUS_ID_PREV,
PARENT_DIST_STATUS_ID_CURR,
WORKITEM_DIST_STATUS_ID_PREV,
WORKITEM_DIST_STATUS_ID_CURR,
PRIORITY_ID_PREV,
PRIORITY_ID_CURR,
DUE_DATE_PREV,
DUE_DATE_CURR,
RESCHEDULE_TIME_PREV,
RESCHEDULE_TIME_CURR,
IEU_COMMENT_CODE1,
IEU_COMMENT_CODE2,
IEU_COMMENT_CODE3,
IEU_COMMENT_CODE4,
IEU_COMMENT_CODE5,
WORKITEM_COMMENT_CODE1,
WORKITEM_COMMENT_CODE2,
WORKITEM_COMMENT_CODE3,
WORKITEM_COMMENT_CODE4,
WORKITEM_COMMENT_CODE5,
RETURN_STATUS,
ERROR_CODE,
LOGGING_LEVEL)
values
(
IEU_UWQM_AUDIT_LOG_S1.NEXTVAL,
1,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
l_action_key,
l_event_key,
l_module,
P_WS_CODE,
690,
l_task_det_rec.l_workitem_pk_id_list(i),
l_task_det_rec.l_workitem_obj_code_list(i),
l_task_det_rec.l_work_item_number_list(i),
l_task_det_rec.l_status_id_list(i),
l_task_det_rec.l_status_id_list(i),
l_task_det_rec.l_owner_id_list(i),
l_task_det_rec.l_owner_id_list(i),
l_task_det_rec.l_owner_type_list(i),
l_task_det_rec.l_owner_type_list(i),
l_task_det_rec.l_assignee_id_list(i),
l_task_det_rec.l_assignee_id_list(i),
l_task_det_rec.l_assignee_type_list(i),
l_task_det_rec.l_assignee_type_list(i),
l_task_det_rec.l_source_object_id_list(i),
l_task_det_rec.l_source_object_id_list(i),
l_task_det_rec.l_source_object_type_code_list(i),
l_task_det_rec.l_source_object_type_code_list(i),
NULL,
NULL,
NULL,
NULL,
l_task_det_rec.l_distribution_status_id_list(i),
l_task_det_rec.l_distribution_status_id_list(i),
l_task_det_rec.l_priority_id_list(i),
l_task_det_rec.l_priority_id_list(i),
l_task_det_rec.l_due_date_list(i),
l_task_det_rec.l_due_date_list(i),
l_task_det_rec.l_reschedule_time_list(i),
l_task_det_rec.l_reschedule_time_list(i),
NULL,
NULL,
NULL,
NULL,
NULL,
l_workitem_comment_code1,
l_workitem_comment_code2,
l_workitem_comment_code3,
l_workitem_comment_code4,
l_workitem_comment_code5,
fnd_api.g_ret_sts_success,
NULL,
FND_PROFILE.VALUE('IEU_WR_DIST_AUDIT_LOG'));
delete
from ieu_uwqm_items parent
where ws_id = l_ws_id
and status_id in (3, 4)
and to_date(trunc(last_update_date), 'dd-mm-rrrr') <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr')
and not exists
(select 1
from ieu_uwqm_items child
where ws_id = l_child_ws_id
and status_id in (0,5)
and child.source_object_id = parent.workitem_pk_id
and child.source_object_type_code = parent.workitem_obj_code );
insert into IEU_UWQM_AUDIT_LOG
(AUDIT_LOG_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ACTION_KEY,
EVENT_KEY,
MODULE,
WS_CODE,
APPLICATION_ID,
WORKITEM_PK_ID,
WORKITEM_OBJ_CODE,
WORK_ITEM_NUMBER,
WORKITEM_STATUS_ID_PREV,
WORKITEM_STATUS_ID_CURR,
OWNER_ID_PREV,
OWNER_ID_CURR,
OWNER_TYPE_PREV,
OWNER_TYPE_CURR,
ASSIGNEE_ID_PREV,
ASSIGNEE_ID_CURR,
ASSIGNEE_TYPE_PREV,
ASSIGNEE_TYPE_CURR,
SOURCE_OBJECT_ID_PREV,
SOURCE_OBJECT_ID_CURR,
SOURCE_OBJECT_TYPE_CODE_PREV,
SOURCE_OBJECT_TYPE_CODE_CURR,
PARENT_WORKITEM_STATUS_ID_PREV,
PARENT_WORKITEM_STATUS_ID_CURR,
PARENT_DIST_STATUS_ID_PREV,
PARENT_DIST_STATUS_ID_CURR,
WORKITEM_DIST_STATUS_ID_PREV,
WORKITEM_DIST_STATUS_ID_CURR,
PRIORITY_ID_PREV,
PRIORITY_ID_CURR,
DUE_DATE_PREV,
DUE_DATE_CURR,
RESCHEDULE_TIME_PREV,
RESCHEDULE_TIME_CURR,
IEU_COMMENT_CODE1,
IEU_COMMENT_CODE2,
IEU_COMMENT_CODE3,
IEU_COMMENT_CODE4,
IEU_COMMENT_CODE5,
WORKITEM_COMMENT_CODE1,
WORKITEM_COMMENT_CODE2,
WORKITEM_COMMENT_CODE3,
WORKITEM_COMMENT_CODE4,
WORKITEM_COMMENT_CODE5,
RETURN_STATUS,
ERROR_CODE,
LOGGING_LEVEL)
values
(
IEU_UWQM_AUDIT_LOG_S1.NEXTVAL,
1,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
l_action_key,
l_event_key,
l_module,
P_WS_CODE,
690,
l_task_det_rec.l_workitem_pk_id_list(i),
l_task_det_rec.l_workitem_obj_code_list(i),
l_task_det_rec.l_work_item_number_list(i),
l_task_det_rec.l_status_id_list(i),
l_task_det_rec.l_status_id_list(i),
l_task_det_rec.l_owner_id_list(i),
l_task_det_rec.l_owner_id_list(i),
l_task_det_rec.l_owner_type_list(i),
l_task_det_rec.l_owner_type_list(i),
l_task_det_rec.l_assignee_id_list(i),
l_task_det_rec.l_assignee_id_list(i),
l_task_det_rec.l_assignee_type_list(i),
l_task_det_rec.l_assignee_type_list(i),
l_task_det_rec.l_source_object_id_list(i),
l_task_det_rec.l_source_object_id_list(i),
l_task_det_rec.l_source_object_type_code_list(i),
l_task_det_rec.l_source_object_type_code_list(i),
NULL,
NULL,
NULL,
NULL,
l_task_det_rec.l_distribution_status_id_list(i),
l_task_det_rec.l_distribution_status_id_list(i),
l_task_det_rec.l_priority_id_list(i),
l_task_det_rec.l_priority_id_list(i),
l_task_det_rec.l_due_date_list(i),
l_task_det_rec.l_due_date_list(i),
l_task_det_rec.l_reschedule_time_list(i),
l_task_det_rec.l_reschedule_time_list(i),
NULL,
NULL,
NULL,
NULL,
NULL,
l_workitem_comment_code1,
l_workitem_comment_code2,
l_workitem_comment_code3,
l_workitem_comment_code4,
l_workitem_comment_code5,
fnd_api.g_ret_sts_success,
NULL,
FND_PROFILE.VALUE('IEU_WR_DIST_AUDIT_LOG'));
delete from ieu_uwqm_items
where status_id in (3,4)
and ws_id = l_ws_id
and ( to_date(trunc(last_update_date), 'dd-mm-rrrr') <= to_date(trunc(p_last_update_date), 'dd-mm-rrrr') );
delete
from ieu_uwqm_audit_log
where workitem_pk_id in (select workitem_pk_id
from ieu_uwqm_audit_log
where trunc(to_date(creation_date, 'dd-mm-rrrr')) <=
trunc(to_date(p_creation_date, 'dd-mm-rrrr'))
and ((workitem_status_id_curr = 3) or (workitem_status_id_curr = 4)))
and workitem_obj_code = p_obj_code;