The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT status_id, source_object_id, source_object_type_code
INTO l_status_id, l_source_object_id, l_source_obj_type_code
FROM ieu_uwqm_items
WHERE work_item_id = p_work_item_id;
SELECT status_id, source_object_id, source_object_type_code
INTO l_status_id, l_source_object_id, l_source_obj_type_code
FROM ieu_uwqm_items
WHERE workitem_pk_id = p_workitem_pk_id
AND workitem_obj_code = p_workitem_obj_code;
/* SELECT WORK_ITEM_ID
INTO L_SOURCEOBJ_WORKITEM_ID
FROM IEU_UWQM_ITEMS
WHERE (WORKITEM_PK_ID, WORKITEM_OBJ_CODE) IN
(SELECT SOURCE_OBJECT_ID, SOURCE_OBJECT_TYPE_CODE
FROM IEU_UWQM_ITEMS
WHERE ( (WORK_ITEM_ID = P_WORK_ITEM_ID) OR
( (WORKITEM_PK_ID = P_WORKITEM_PK_ID) AND (WORKITEM_OBJ_CODE = P_WORKITEM_OBJ_CODE) )
)
);
SELECT WORK_ITEM_ID
INTO L_SOURCEOBJ_WORKITEM_ID
FROM IEU_UWQM_ITEMS
WHERE WORKITEM_PK_ID = l_source_object_id
AND WORKITEM_OBJ_CODE = l_source_obj_type_code;
select work_item_id, priority_level, to_char(due_date,'dd-mon-yyyy hh24:mi:ss') due_date,
workitem_obj_code
from ieu_uwqm_items
where owner_type = l_owner_type_ind
and owner_id = p_resource_id
and status_id in (0,1,2)
and reschedule_time <= sysdate
order by priority_level, due_date;
select work_item_id, priority_level, to_char(due_date,'dd-mon-yyyy hh24:mi:ss') due_date,
workitem_obj_code
from ieu_uwqm_items
where owner_type = l_owner_type_grp
and owner_id in (select group_id from jtf_rs_group_members where resource_id = p_resource_id)
and status_id in (0,1,2)
and reschedule_time <= sysdate
order by priority_level, due_date;
select work_item_id, priority_level, to_char(due_date,'dd-mon-yyyy hh24:mi:ss') due_date,
workitem_obj_code
from ieu_uwqm_items
where assignee_type = l_owner_type_ind
and assignee_id = p_resource_id
and status_id in (0,1,2)
and reschedule_time <= sysdate
order by priority_level, due_date;
select work_item_id, priority_level, to_char(due_date,'dd-mon-yyyy hh24:mi:ss') due_date,
workitem_obj_code
from ieu_uwqm_items
where assignee_type = l_owner_type_grp
and assignee_id in (select group_id from jtf_rs_group_members where resource_id =p_resource_id)
and status_id in (0,1,2)
and reschedule_time <= sysdate
order by priority_level, due_date;
l_nw_item_list.delete;
x_uwqm_workitem_data.delete;
SELECT UWQM.WORK_ITEM_ID,
UWQM.WORKITEM_OBJ_CODE,
UWQM.WORKITEM_PK_ID,
UWQM.STATUS_ID,
UWQM.PRIORITY_ID,
UWQM.PRIORITY_LEVEL,
PR.NAME PRIORITY,
UWQM.DUE_DATE,
UWQM.TITLE,
UWQM.PARTY_ID,
UWQM.OWNER_ID,
UWQM.OWNER_TYPE,
UWQM.ASSIGNEE_ID,
UWQM.ASSIGNEE_TYPE,
UWQM.SOURCE_OBJECT_ID,
UWQM.SOURCE_OBJECT_TYPE_CODE,
UWQM.OWNER_TYPE_ACTUAL,
UWQM.ASSIGNEE_TYPE_ACTUAL,
UWQM.APPLICATION_ID,
ENUM.ENUM_TYPE_UUID IEU_ENUM_TYPE_UUID,
UWQM.STATUS_UPDATE_USER_ID,
UWQM.WORK_ITEM_NUMBER,
UWQM.RESCHEDULE_TIME,
LKUPS.MEANING WORK_TYPE,
DECODE(STATUS_ID, 0, '', 1, LKUPS1.MEANING, 2, LKUPS1.MEANING) STATUS_CODE
INTO X_UWQM_WORKITEM_DATA(i)
FROM IEU_UWQM_ITEMS UWQM,
IEU_UWQ_SEL_ENUMERATORS ENUM,
IEU_UWQM_PRIORITIES_TL PR,
FND_LOOKUP_VALUES_VL LKUPS,
FND_LOOKUP_VALUES_VL LKUPS1
WHERE UWQM.WORK_ITEM_ID = l_nw_item_list(i).work_item_id
AND ENUM.ENUM_TYPE_UUID = UWQM.IEU_ENUM_TYPE_UUID
AND PR.PRIORITY_ID = UWQM.PRIORITY_ID
AND LKUPS.LOOKUP_TYPE(+) = ENUM.WORK_Q_LABEL_LU_TYPE
AND LKUPS.VIEW_APPLICATION_ID(+) = ENUM.APPLICATION_ID
AND LKUPS.LOOKUP_CODE(+) = ENUM.WORK_Q_LABEL_LU_CODE
AND LKUPS1.LOOKUP_TYPE = 'IEU_NODE_LABELS'
AND LKUPS1.VIEW_APPLICATION_ID = ENUM.APPLICATION_ID
AND LKUPS1.LOOKUP_CODE = 'IN_USE' ;
select work_item_id
from ieu_uwqm_items
where owner_type = l_owner_type_ind
and owner_id = p_resource_id
and status_id = p_status_id
and (p_node_type = 1 or p_node_type = 2)
and reschedule_time <= sysdate
union
select work_item_id
from ieu_uwqm_items
where owner_type = l_owner_type_grp
and owner_id in (select group_id from jtf_rs_group_members where resource_id = p_resource_id)
and status_id = p_status_id
and (p_node_type = 1 or p_node_type = 3)
and reschedule_time <= sysdate
union
select work_item_id
from ieu_uwqm_items
where assignee_type = l_owner_type_ind
and assignee_id = p_resource_id
and status_id = p_status_id
and (p_node_type = 1 or p_node_type = 4)
and reschedule_time <= sysdate
union
select work_item_id
from ieu_uwqm_items
where assignee_type = l_owner_type_grp
and assignee_id in (select group_id from jtf_rs_group_members where resource_id =p_resource_id)
and status_id = p_status_id
and (p_node_type = 1 or p_node_type = 5)
and reschedule_time <= sysdate;
-- Build the complete select stmt
/* Bug 10164373, 10634614 added alisas items to the below query*/
l_sql_stmt := 'SELECT /*+ first_rows */
WORK_ITEM_ID,
WORKITEM_OBJ_CODE,
WORKITEM_PK_ID,
STATUS_ID,
PRIORITY_ID,
PRIORITY_LEVEL,
null, -- Selecting null for pty code
DUE_DATE,
TITLE,
PARTY_ID,
OWNER_ID,
OWNER_TYPE,
ASSIGNEE_ID,
ASSIGNEE_TYPE,
SOURCE_OBJECT_ID,
SOURCE_OBJECT_TYPE_CODE,
APPLICATION_ID,
IEU_ENUM_TYPE_UUID,
WORK_ITEM_NUMBER,
RESCHEDULE_TIME,
WS_ID
FROM IEU_UWQM_ITEMS items '||
' WHERE ( '|| p_dist_to_extra_where_clause || ' ) '||
' AND DISTRIBUTION_STATUS_ID = :l_del_status' ||
' AND STATUS_ID = :l_open_status_id ' ||
' and reschedule_time <= sysdate ' ||
' order by priority_level, due_date ';
select ws_code
into l_ws_code1
from ieu_uwqm_work_sources_b
where ws_id = l_del_nw_item.ws_id;
SELECT INCIDENT_ID
INTO l_sec_incident
FROM CS_INCIDENTS_B_SEC
WHERE incident_id = l_del_nw_item.WORKITEM_PK_ID;
SELECT INCIDENT_ID
INTO l_sec_incident
FROM CS_INCIDENTS_B_SEC
WHERE incident_id = l_del_nw_item.SOURCE_OBJECT_ID;
-- Build the complete select stmt
/* Bug 10164373, 10634614 added alisas items to the below query*/
l_sql_stmt := 'SELECT /*+ first_rows */
WORK_ITEM_ID,
WORKITEM_OBJ_CODE,
WORKITEM_PK_ID,
STATUS_ID,
PRIORITY_ID,
PRIORITY_LEVEL,
null, -- Selecting null for pty code
DUE_DATE,
TITLE,
PARTY_ID,
OWNER_ID,
OWNER_TYPE,
ASSIGNEE_ID,
ASSIGNEE_TYPE,
SOURCE_OBJECT_ID,
SOURCE_OBJECT_TYPE_CODE,
APPLICATION_ID,
IEU_ENUM_TYPE_UUID,
WORK_ITEM_NUMBER,
RESCHEDULE_TIME,
WS_ID
FROM IEU_UWQM_ITEMS items '||
' WHERE ( '|| p_dist_from_extra_where_clause || ' ) '||
' AND DISTRIBUTION_STATUS_ID = :l_dist_status' ||
' AND STATUS_ID = :l_open_status_id ' ||
' and reschedule_time <= sysdate ' ||
' order by priority_level, due_date ' ||
' for update skip locked ';
-- insert into p_temp(msg) values ('dist from sql- '||l_sql_stmt|| ' res id: '||p_resource_id ||' dist st: '||l_dist_status
-- || ' open st: '||l_open_status_id); commit;
-- Select the top 5 Work Items for Distribution
-- OPEN l_dist_wr_cur FOR l_sql_stmt
-- USING IN l_dist_status, IN l_open_status_id;
-- insert into p_temp(msg) values ('Dist item: '||l_dist_nw_item.workitem_pk_id);
update ieu_uwqm_items
set distribution_status_id = 2
where work_item_id = l_dist_nw_item.WORK_ITEM_ID;
select priority_code
into l_priority_code
from ieu_uwqm_priorities_b
where priority_id = l_dist_nw_item.PRIORITY_ID;
--insert into p_temp(msg) values ('l_dist_items_flag: '||l_dist_items_flag ||' l_del_items_flag: '||l_del_items_flag ); commit;
-- insert into p_temp(msg) values(' l_delivery_only_flag : '||l_delivery_only_flag );
select ws_code
into l_ws_code
from ieu_uwqm_work_sources_b
where ws_id = l_del_nw_item.ws_id;
select reschedule_time, distribution_status_id
into l_reschedule_time, l_distribution_status_id
from ieu_uwqm_items
where workitem_pk_id = l_workitem_pk_id
and workitem_obj_code = l_workitem_obj_code;
IEU_UWQM_AUDIT_LOG_PKG.INSERT_ROW
(
P_ACTION_KEY => l_action_key,
P_EVENT_KEY => l_event_key,
P_MODULE => l_module,
P_WS_CODE => l_ws_code,
P_APPLICATION_ID => l_application_id,
P_WORKITEM_PK_ID => l_workitem_pk_id,
P_WORKITEM_OBJ_CODE => l_workitem_obj_code,
P_WORK_ITEM_STATUS_PREV => l_status_id,
P_WORK_ITEM_STATUS_CURR => l_status_id,
P_OWNER_ID_PREV => l_owner_id,
P_OWNER_ID_CURR => l_owner_id,
P_OWNER_TYPE_PREV => l_owner_type,
P_OWNER_TYPE_CURR => l_owner_type,
P_ASSIGNEE_ID_PREV => l_assignee_id,
P_ASSIGNEE_ID_CURR => l_assignee_id,
P_ASSIGNEE_TYPE_PREV => l_assignee_type,
P_ASSIGNEE_TYPE_CURR => l_assignee_type,
P_SOURCE_OBJECT_ID_PREV => l_source_object_id,
P_SOURCE_OBJECT_ID_CURR => l_source_object_id,
P_SOURCE_OBJECT_TYPE_CODE_PREV => l_source_object_type_code,
P_SOURCE_OBJECT_TYPE_CODE_CURR => l_source_object_type_code,
P_PARENT_WORKITEM_STATUS_PREV => null,
P_PARENT_WORKITEM_STATUS_CURR => null,
P_PARENT_DIST_STATUS_PREV => null,
P_PARENT_DIST_STATUS_CURR => null,
P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
P_PRIORITY_PREV => l_priority_id,
P_PRIORITY_CURR => l_priority_id,
P_DUE_DATE_PREV => l_due_date,
P_DUE_DATE_CURR => l_due_date,
P_RESCHEDULE_TIME_PREV => l_reschedule_time,
P_RESCHEDULE_TIME_CURR => l_reschedule_time,
P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
P_STATUS => 'S',
P_ERROR_CODE => l_msg_data,
X_AUDIT_LOG_ID => l_audit_log_id,
X_MSG_DATA => l_msg_data,
X_RETURN_STATUS => l_ret_sts); commit;
-- Select the Work Items with highest pty, due date
-- l_dist_flag = 'Y' if Distribution is required.
if (nvl(l_del_items_flag,'Y') = 'N')
then
l_dist_flag := 'Y';
--insert into p_temp(msg) values('Attempting Dist for ID: '||l_nw_items_list(z).workitem_pk_id); commit;
select priority_level
into l_priority_level
from ieu_uwqm_priorities_b
where priority_code = l_nw_items_list(z).priority_code;
--insert into p_temp(msg) values('dist pty lvl: '||l_priority_level);
--insert into p_temp(msg) values(' due date: '||l_nw_items_list(z).due_date);
--insert into p_temp(msg) values('del pty lvl: '||l_del_nw_item.priority_level||' due date: '||l_del_nw_item.due_date );
select ws_code
into l_ws_code
from ieu_uwqm_work_sources_b
where ws_id = l_del_nw_item.ws_id;
select reschedule_time, distribution_status_id
into l_reschedule_time, l_distribution_status_id
from ieu_uwqm_items
where workitem_pk_id = l_workitem_pk_id
and workitem_obj_code = l_workitem_obj_code;
IEU_UWQM_AUDIT_LOG_PKG.INSERT_ROW
(
P_ACTION_KEY => l_action_key,
P_EVENT_KEY => l_event_key,
P_MODULE => l_module,
P_WS_CODE => l_ws_code,
P_APPLICATION_ID => l_application_id,
P_WORKITEM_PK_ID => l_workitem_pk_id,
P_WORKITEM_OBJ_CODE => l_workitem_obj_code,
P_WORK_ITEM_STATUS_PREV => l_status_id,
P_WORK_ITEM_STATUS_CURR => l_status_id,
P_OWNER_ID_PREV => l_owner_id,
P_OWNER_ID_CURR => l_owner_id,
P_OWNER_TYPE_PREV => l_owner_type,
P_OWNER_TYPE_CURR => l_owner_type,
P_ASSIGNEE_ID_PREV => l_assignee_id,
P_ASSIGNEE_ID_CURR => l_assignee_id,
P_ASSIGNEE_TYPE_PREV => l_assignee_type,
P_ASSIGNEE_TYPE_CURR => l_assignee_type,
P_SOURCE_OBJECT_ID_PREV => l_source_object_id,
P_SOURCE_OBJECT_ID_CURR => l_source_object_id,
P_SOURCE_OBJECT_TYPE_CODE_PREV => l_source_object_type_code,
P_SOURCE_OBJECT_TYPE_CODE_CURR => l_source_object_type_code,
P_PARENT_WORKITEM_STATUS_PREV => null,
P_PARENT_WORKITEM_STATUS_CURR => null,
P_PARENT_DIST_STATUS_PREV => null,
P_PARENT_DIST_STATUS_CURR => null,
P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
P_PRIORITY_PREV => l_priority_id,
P_PRIORITY_CURR => l_priority_id,
P_DUE_DATE_PREV => l_due_date,
P_DUE_DATE_CURR => l_due_date,
P_RESCHEDULE_TIME_PREV => l_reschedule_time,
P_RESCHEDULE_TIME_CURR => l_reschedule_time,
P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
P_STATUS => 'S',
P_ERROR_CODE => l_msg_data,
X_AUDIT_LOG_ID => l_audit_log_id,
X_MSG_DATA => l_msg_data,
X_RETURN_STATUS => l_ret_sts); commit;
SELECT WS_B.DISTRIBUTION_FUNCTION ,
WS_A.DIST_ST_BASED_ON_PARENT_FLAG, WS_B.WS_CODE
INTO l_distribution_function,
l_dist_st_based_on_parent_flag, l_ws_code
FROM IEU_UWQM_WORK_SOURCES_B WS_B, IEU_UWQM_WS_ASSCT_PROPS WS_A
WHERE ws_b.ws_id = l_nw_items_list(z).WS_ID
AND ws_b.not_valid_flag = l_not_valid_flag
AND ws_b.ws_id = ws_a.ws_id(+);
l_work_item_status := 'DELETE';
IEU_UWQM_AUDIT_LOG_PKG.INSERT_ROW
(
P_ACTION_KEY => l_action_key,
P_EVENT_KEY => l_event_key,
P_MODULE => l_module,
P_WS_CODE => l_ws_code,
P_APPLICATION_ID => l_dist_items(k).application_id,
P_WORKITEM_PK_ID => l_dist_items(k).workitem_pk_id,
P_WORKITEM_OBJ_CODE => l_dist_items(k).workitem_obj_code,
P_WORK_ITEM_STATUS_PREV => l_status_id,
P_WORK_ITEM_STATUS_CURR => l_status_id,
P_OWNER_ID_PREV => l_dist_items(k).owner_id,
P_OWNER_ID_CURR => l_dist_items(k).owner_id,
P_OWNER_TYPE_PREV => l_dist_items(k).owner_type,
P_OWNER_TYPE_CURR => l_dist_items(k).owner_type,
P_ASSIGNEE_ID_PREV => null,
P_ASSIGNEE_ID_CURR => l_dist_items(k).assignee_id,
P_ASSIGNEE_TYPE_PREV => null,
P_ASSIGNEE_TYPE_CURR => l_dist_items(k).assignee_type,
P_SOURCE_OBJECT_ID_PREV => l_dist_items(k).source_object_id,
P_SOURCE_OBJECT_ID_CURR => l_dist_items(k).source_object_id,
P_SOURCE_OBJECT_TYPE_CODE_PREV => l_dist_items(k).source_object_type_code,
P_SOURCE_OBJECT_TYPE_CODE_CURR => l_dist_items(k).source_object_type_code,
P_PARENT_WORKITEM_STATUS_PREV => null,
P_PARENT_WORKITEM_STATUS_CURR => null,
P_PARENT_DIST_STATUS_PREV => null,
P_PARENT_DIST_STATUS_CURR => null,
P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
P_PRIORITY_PREV => l_dist_items(k).priority_id,
P_PRIORITY_CURR => l_dist_items(k).priority_id,
P_DUE_DATE_PREV => l_dist_items(k).due_date,
P_DUE_DATE_CURR => l_dist_items(k).due_date,
P_RESCHEDULE_TIME_PREV => l_reschedule_time,
P_RESCHEDULE_TIME_CURR => l_reschedule_time,
P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
P_STATUS => 'E',
P_ERROR_CODE => x_msg_data,
X_AUDIT_LOG_ID => l_audit_log_id_list(k),
X_MSG_DATA => x_msg_data,
X_RETURN_STATUS => l_ret_sts
);
elsif (l_dist_items(k).work_item_status = 'DELETE')
then
l_status_id := 4;
update ieu_uwqm_items
set distribution_status_id = 1
where work_item_id = l_dist_items(k).work_item_id;
select reschedule_time, distribution_status_id, priority_id
into l_reschedule_time, l_distribution_status_id, l_priority_id
from ieu_uwqm_items
where workitem_pk_id = l_workitem_pk_id
and workitem_obj_code = l_workitem_obj_code;
IEU_UWQM_AUDIT_LOG_PKG.UPDATE_ROW
(
P_AUDIT_LOG_ID => l_audit_log_id_list(k),
P_ACTION_KEY => l_action_key,
P_EVENT_KEY => l_event_key,
P_MODULE => l_module,
P_WS_CODE => l_ws_code,
P_APPLICATION_ID => l_application_id,
P_WORKITEM_PK_ID => l_workitem_pk_id,
P_WORKITEM_OBJ_CODE => l_workitem_obj_code,
P_WORK_ITEM_STATUS_PREV => l_status_id,
P_WORK_ITEM_STATUS_CURR => l_status_id,
P_OWNER_ID_PREV => l_owner_id,
P_OWNER_ID_CURR => l_owner_id,
P_OWNER_TYPE_PREV => l_owner_type,
P_OWNER_TYPE_CURR => l_owner_type,
P_ASSIGNEE_ID_PREV => l_assignee_id,
P_ASSIGNEE_ID_CURR => l_assignee_id,
P_ASSIGNEE_TYPE_PREV => l_assignee_type,
P_ASSIGNEE_TYPE_CURR => l_assignee_type,
P_SOURCE_OBJECT_ID_PREV => l_source_object_id,
P_SOURCE_OBJECT_ID_CURR => l_source_object_id,
P_SOURCE_OBJECT_TYPE_CODE_PREV => l_source_object_type_code,
P_SOURCE_OBJECT_TYPE_CODE_CURR => l_source_object_type_code,
P_PARENT_WORKITEM_STATUS_PREV => null,
P_PARENT_WORKITEM_STATUS_CURR => null,
P_PARENT_DIST_STATUS_PREV => null,
P_PARENT_DIST_STATUS_CURR => null,
P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
P_PRIORITY_PREV => l_priority_id,
P_PRIORITY_CURR => l_priority_id,
P_DUE_DATE_PREV => l_due_date,
P_DUE_DATE_CURR => l_due_date,
P_RESCHEDULE_TIME_PREV => l_reschedule_time,
P_RESCHEDULE_TIME_CURR => l_reschedule_time,
P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
P_STATUS => 'E',
P_ERROR_CODE => x_msg_data); commit;
elsif (l_dist_items(j).work_item_status = 'DELETE')
then
l_status_id := 4;
select reschedule_time, distribution_status_id, priority_id
into l_reschedule_time, l_distribution_status_id, l_priority_id
from ieu_uwqm_items
where workitem_pk_id = l_workitem_pk_id
and workitem_obj_code = l_workitem_obj_code;
IEU_UWQM_AUDIT_LOG_PKG.UPDATE_ROW
(
P_AUDIT_LOG_ID => l_audit_log_id_list(j),
P_ACTION_KEY => l_action_key,
P_EVENT_KEY => l_event_key,
P_MODULE => l_module,
P_WS_CODE => l_ws_code,
P_APPLICATION_ID => l_application_id,
P_WORKITEM_PK_ID => l_workitem_pk_id,
P_WORKITEM_OBJ_CODE => l_workitem_obj_code,
P_WORK_ITEM_STATUS_PREV => l_status_id,
P_WORK_ITEM_STATUS_CURR => l_status_id,
P_OWNER_ID_PREV => l_owner_id,
P_OWNER_ID_CURR => l_owner_id,
P_OWNER_TYPE_PREV => l_owner_type,
P_OWNER_TYPE_CURR => l_owner_type,
P_ASSIGNEE_ID_PREV => l_assignee_id,
P_ASSIGNEE_ID_CURR => l_assignee_id,
P_ASSIGNEE_TYPE_PREV => l_assignee_type,
P_ASSIGNEE_TYPE_CURR => l_assignee_type,
P_SOURCE_OBJECT_ID_PREV => l_source_object_id,
P_SOURCE_OBJECT_ID_CURR => l_source_object_id,
P_SOURCE_OBJECT_TYPE_CODE_PREV => l_source_object_type_code,
P_SOURCE_OBJECT_TYPE_CODE_CURR => l_source_object_type_code,
P_PARENT_WORKITEM_STATUS_PREV => null,
P_PARENT_WORKITEM_STATUS_CURR => null,
P_PARENT_DIST_STATUS_PREV => null,
P_PARENT_DIST_STATUS_CURR => null,
P_WORKITEM_DIST_STATUS_PREV => 1,
P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
P_PRIORITY_PREV => l_priority_id,
P_PRIORITY_CURR => l_priority_id,
P_DUE_DATE_PREV => l_due_date,
P_DUE_DATE_CURR => l_due_date,
P_RESCHEDULE_TIME_PREV => l_reschedule_time,
P_RESCHEDULE_TIME_CURR => l_reschedule_time,
P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
P_STATUS => l_audit_log_sts,
P_ERROR_CODE => l_msg_data);commit;
ELSIF (l_dist_items(j).WORK_ITEM_STATUS = 'DELETE')
THEN
l_work_item_status_id := 4;
-- Update the same object
l_dist_items(l_dist_items.LAST) := SYSTEM.WR_ITEM_DATA_OBJ(l_dist_items(j).WORK_ITEM_ID,
l_dist_items(j).WORKITEM_OBJ_CODE,
l_dist_items(j).WORKITEM_PK_ID,
l_work_item_status_id,
l_dist_items(j).PRIORITY_ID,
l_dist_items(j).PRIORITY_LEVEL,
l_dist_items(j).PRIORITY_CODE,
l_dist_items(j).DUE_DATE,
l_dist_items(j).TITLE,
l_dist_items(j).PARTY_ID,
l_dist_items(j).OWNER_ID,
l_dist_items(j).OWNER_TYPE,
l_dist_items(j).ASSIGNEE_ID,
l_dist_items(j).ASSIGNEE_TYPE,
l_dist_items(j).SOURCE_OBJECT_ID,
l_dist_items(j).SOURCE_OBJECT_TYPE_CODE,
l_dist_items(j).APPLICATION_ID,
l_dist_items(j).IEU_ENUM_TYPE_UUID,
l_dist_items(j).WORK_ITEM_NUMBER,
l_dist_items(j).RESCHEDULE_TIME,
l_dist_items(j).WORK_SOURCE,
l_dist_items(j).DISTRIBUTED,
l_dist_items(j).ITEM_INCLUDED_BY_APP);
select reschedule_time, distribution_status_id, priority_id
into l_reschedule_time, l_distribution_status_id, l_priority_id
from ieu_uwqm_items
where workitem_pk_id = l_dist_items(j).workitem_pk_id
and workitem_obj_code = l_dist_items(j).workitem_obj_code;
IEU_UWQM_AUDIT_LOG_PKG.INSERT_ROW
(
P_ACTION_KEY => l_action_key,
P_EVENT_KEY => l_event_key,
P_MODULE => l_module,
P_WS_CODE => l_ws_code,
P_APPLICATION_ID => l_application_id,
P_WORKITEM_PK_ID =>l_dist_items(j).workitem_pk_id,
P_WORKITEM_OBJ_CODE =>l_dist_items(j).workitem_obj_code,
P_WORK_ITEM_STATUS_PREV =>l_work_item_status_id,
P_WORK_ITEM_STATUS_CURR =>l_work_item_status_id,
P_OWNER_ID_PREV =>l_dist_items(j).owner_id,
P_OWNER_ID_CURR =>l_dist_items(j).owner_id,
P_OWNER_TYPE_PREV =>l_dist_items(j).owner_type,
P_OWNER_TYPE_CURR =>l_dist_items(j).owner_type,
P_ASSIGNEE_ID_PREV =>l_dist_items(j).assignee_id,
P_ASSIGNEE_ID_CURR =>l_dist_items(j).assignee_id,
P_ASSIGNEE_TYPE_PREV =>l_dist_items(j).assignee_type,
P_ASSIGNEE_TYPE_CURR =>l_dist_items(j).assignee_type,
P_SOURCE_OBJECT_ID_PREV =>l_dist_items(j).source_object_id,
P_SOURCE_OBJECT_ID_CURR =>l_dist_items(j).source_object_id,
P_SOURCE_OBJECT_TYPE_CODE_PREV =>l_dist_items(j).source_object_type_code,
P_SOURCE_OBJECT_TYPE_CODE_CURR =>l_dist_items(j).source_object_type_code,
P_PARENT_WORKITEM_STATUS_PREV => null,
P_PARENT_WORKITEM_STATUS_CURR => null,
P_PARENT_DIST_STATUS_PREV => null,
P_PARENT_DIST_STATUS_CURR => null,
P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
P_PRIORITY_PREV => l_priority_id,
P_PRIORITY_CURR => l_priority_id,
P_DUE_DATE_PREV =>l_dist_items(j).due_date,
P_DUE_DATE_CURR =>l_dist_items(j).due_date,
P_RESCHEDULE_TIME_PREV => l_reschedule_time,
P_RESCHEDULE_TIME_CURR => l_reschedule_time,
P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
P_STATUS => l_audit_log_sts,
P_ERROR_CODE => l_msg_data,
X_AUDIT_LOG_ID => l_audit_log_id,
X_MSG_DATA => l_msg_data,
X_RETURN_STATUS => l_ret_sts);commit;
-- Set the status back to 'Distributable' for the Work Items Selected for Distribution except the Distributed Work Item
-- This check is required here for the following reasons
-- 1. Any Item out of the 5 we are selecting for Distribution can be Distributed. If for eg. the 2nd item is Distributed
-- then the Dist Status for all others should be reset here
-- 2. If No Distribution was done, then the Dist Status should be reset here.
if (x_uwqm_workitem_data.count >= 1)
then
for p in x_uwqm_workitem_data.first .. x_uwqm_workitem_data.last
loop
if (x_uwqm_workitem_data(p).param_name = 'WORK_ITEM_ID')
then
l_dist_work_item_id := x_uwqm_workitem_data(p).param_value;
update ieu_uwqm_items
set distribution_status_id = 1
where work_item_id = l_nw_items_list(y).work_item_id;
*** ex: sql_stmt := 'select .... where owner_id = :resource_id or assignee_id = :resource_id';
select WS_B.WS_ID, 'INDIVIDUAL_ASSIGNED' DISTRIBUTE_TO, 'GROUP_OWNED' DISTRIBUTE_FROM , WS_B.DISTRIBUTION_FUNCTION ,
WS_A.DIST_ST_BASED_ON_PARENT_FLAG, WS_B.WS_CODE
from IEU_UWQM_WORK_SOURCES_B WS_B, IEU_UWQM_WS_ASSCT_PROPS WS_A
where ws_b.not_valid_flag = l_not_valid_flag
and ws_b.ws_id = ws_a.ws_id(+);
l_sql_stmt := 'SELECT /*+ first_rows */
WORK_ITEM_ID,
WORKITEM_OBJ_CODE,
WORKITEM_PK_ID,
STATUS_ID,
PRIORITY_ID,
PRIORITY_LEVEL,
null, -- Selecting null for pty code
DUE_DATE,
TITLE,
PARTY_ID,
OWNER_ID,
OWNER_TYPE,
ASSIGNEE_ID,
ASSIGNEE_TYPE,
SOURCE_OBJECT_ID,
SOURCE_OBJECT_TYPE_CODE,
APPLICATION_ID,
IEU_ENUM_TYPE_UUID,
WORK_ITEM_NUMBER,
RESCHEDULE_TIME,
WS_ID
FROM IEU_UWQM_ITEMS '||
' WHERE ' || ' ( ' ||p_extra_where_clause || ' ) '||
' AND DISTRIBUTION_STATUS_ID = :l_dist_status' ||
' AND STATUS_ID = :l_status_id ' ||
' and reschedule_time <= sysdate ' ||
-- l_where_clause ||' ) '||
-- ' ) AND rownum <= '|| p_num_of_dist_items||
-- ' ) AND rownum <= :p_num_of_dist_items '||
' order by priority_level, due_date '||
' for update skip locked ';
update ieu_uwqm_items
set distribution_status_id = 2
where work_item_id = l_nw_item.WORK_ITEM_ID;
select priority_code
into l_priority_code
from ieu_uwqm_priorities_b
where priority_id = l_nw_item.PRIORITY_ID;
select ws_code
into l_ws_name
from ieu_uwqm_work_sources_b
where ws_id = l_curr_ws_id;
l_work_item_status := 'DELETE';
IEU_UWQM_AUDIT_LOG_PKG.INSERT_ROW
(
P_ACTION_KEY => l_action_key,
P_EVENT_KEY => l_event_key,
P_MODULE => l_module,
P_WS_CODE => l_ws_code,
P_APPLICATION_ID => l_application_id,
P_WORKITEM_PK_ID => l_dist_items(k).workitem_pk_id,
P_WORKITEM_OBJ_CODE => l_dist_items(k).workitem_obj_code,
P_WORK_ITEM_STATUS_PREV => l_status_id,
P_WORK_ITEM_STATUS_CURR => l_status_id,
P_OWNER_ID_PREV => l_dist_items(k).owner_id,
P_OWNER_ID_CURR => l_dist_items(k).owner_id,
P_OWNER_TYPE_PREV => l_dist_items(k).owner_type,
P_OWNER_TYPE_CURR => l_dist_items(k).owner_type,
P_ASSIGNEE_ID_PREV => null,
P_ASSIGNEE_ID_CURR => l_dist_items(k).assignee_id,
P_ASSIGNEE_TYPE_PREV => null,
P_ASSIGNEE_TYPE_CURR => l_dist_items(k).assignee_type,
P_SOURCE_OBJECT_ID_PREV => l_dist_items(k).source_object_id,
P_SOURCE_OBJECT_ID_CURR => l_dist_items(k).source_object_id,
P_SOURCE_OBJECT_TYPE_CODE_PREV => l_dist_items(k).source_object_type_code,
P_SOURCE_OBJECT_TYPE_CODE_CURR => l_dist_items(k).source_object_type_code,
P_PARENT_WORKITEM_STATUS_PREV => null,
P_PARENT_WORKITEM_STATUS_CURR => null,
P_PARENT_DIST_STATUS_PREV => null,
P_PARENT_DIST_STATUS_CURR => null,
P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
P_PRIORITY_PREV => l_dist_items(k).priority_id,
P_PRIORITY_CURR => l_dist_items(k).priority_id,
P_DUE_DATE_PREV => l_dist_items(k).due_date,
P_DUE_DATE_CURR => l_dist_items(k).due_date,
P_RESCHEDULE_TIME_PREV => l_reschedule_time,
P_RESCHEDULE_TIME_CURR => l_reschedule_time,
P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
P_STATUS => 'S',
P_ERROR_CODE => x_msg_data,
X_AUDIT_LOG_ID => l_audit_log_id_list(k),
X_MSG_DATA => x_msg_data,
X_RETURN_STATUS => l_ret_sts
);
-- insert into p_temp(msg) values('exception');
elsif (l_dist_items(k).work_item_status = 'DELETE')
then
l_status_id := 4;
update ieu_uwqm_items
set distribution_status_id = 1
where work_item_id = l_dist_items(k).work_item_id;
--insert into p_temp('errcode: '||SQLCODE);
--insert inot p_temp('errm: '||SQLERRM);
select reschedule_time, distribution_status_id, priority_id
into l_reschedule_time, l_distribution_status_id, l_priority_id
from ieu_uwqm_items
where workitem_pk_id = l_workitem_pk_id
and workitem_obj_code = l_workitem_obj_code;
IEU_UWQM_AUDIT_LOG_PKG.UPDATE_ROW
(
P_AUDIT_LOG_ID => l_audit_log_id_list(k),
P_ACTION_KEY => l_action_key,
P_EVENT_KEY => l_event_key,
P_MODULE => l_module,
P_WS_CODE => l_ws_code,
P_APPLICATION_ID => l_application_id,
P_WORKITEM_PK_ID => l_workitem_pk_id,
P_WORKITEM_OBJ_CODE => l_workitem_obj_code,
P_WORK_ITEM_STATUS_PREV => l_status_id,
P_WORK_ITEM_STATUS_CURR => l_status_id,
P_OWNER_ID_PREV => l_owner_id,
P_OWNER_ID_CURR => l_owner_id,
P_OWNER_TYPE_PREV => l_owner_type,
P_OWNER_TYPE_CURR => l_owner_type,
P_ASSIGNEE_ID_PREV => null,
P_ASSIGNEE_ID_CURR => l_assignee_id,
P_ASSIGNEE_TYPE_PREV => null,
P_ASSIGNEE_TYPE_CURR => l_assignee_type,
P_SOURCE_OBJECT_ID_PREV => l_source_object_id,
P_SOURCE_OBJECT_ID_CURR => l_source_object_id,
P_SOURCE_OBJECT_TYPE_CODE_PREV => l_source_object_type_code,
P_SOURCE_OBJECT_TYPE_CODE_CURR => l_source_object_type_code,
P_PARENT_WORKITEM_STATUS_PREV => null,
P_PARENT_WORKITEM_STATUS_CURR => null,
P_PARENT_DIST_STATUS_PREV => null,
P_PARENT_DIST_STATUS_CURR => null,
P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
P_PRIORITY_PREV => l_priority_id,
P_PRIORITY_CURR => l_priority_id,
P_DUE_DATE_PREV => l_due_date,
P_DUE_DATE_CURR => l_due_date,
P_RESCHEDULE_TIME_PREV => l_reschedule_time,
P_RESCHEDULE_TIME_CURR => l_reschedule_time,
P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
P_STATUS => 'E',
P_ERROR_CODE => l_msg_data);
-- insert into p_temp(msg) values('l_msg_data3: '||x_msg_data);
elsif (l_dist_items(j).work_item_status = 'DELETE')
then
l_status_id := 4;
select reschedule_time, priority_id
into l_reschedule_time, l_priority_id
from ieu_uwqm_items
where workitem_pk_id = l_workitem_pk_id
and workitem_obj_code = l_workitem_obj_code;
IEU_UWQM_AUDIT_LOG_PKG.UPDATE_ROW
(
P_AUDIT_LOG_ID => l_audit_log_id_list(j),
P_ACTION_KEY => l_action_key,
P_EVENT_KEY => l_event_key,
P_MODULE => l_module,
P_WS_CODE => l_ws_code,
P_APPLICATION_ID => l_application_id,
P_WORKITEM_PK_ID => l_workitem_pk_id,
P_WORKITEM_OBJ_CODE => l_workitem_obj_code,
P_WORK_ITEM_STATUS_PREV => l_status_id,
P_WORK_ITEM_STATUS_CURR => l_status_id,
P_OWNER_ID_PREV => l_owner_id,
P_OWNER_ID_CURR => l_owner_id,
P_OWNER_TYPE_PREV => l_owner_type,
P_OWNER_TYPE_CURR => l_owner_type,
P_ASSIGNEE_ID_PREV => null,
P_ASSIGNEE_ID_CURR => l_assignee_id,
P_ASSIGNEE_TYPE_PREV => null,
P_ASSIGNEE_TYPE_CURR => l_assignee_type,
P_SOURCE_OBJECT_ID_PREV => l_source_object_id,
P_SOURCE_OBJECT_ID_CURR => l_source_object_id,
P_SOURCE_OBJECT_TYPE_CODE_PREV => l_source_object_type_code,
P_SOURCE_OBJECT_TYPE_CODE_CURR => l_source_object_type_code,
P_PARENT_WORKITEM_STATUS_PREV => null,
P_PARENT_WORKITEM_STATUS_CURR => null,
P_PARENT_DIST_STATUS_PREV => null,
P_PARENT_DIST_STATUS_CURR => null,
P_WORKITEM_DIST_STATUS_PREV => 1,
P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
P_PRIORITY_PREV => l_priority_id,
P_PRIORITY_CURR => l_priority_id,
P_DUE_DATE_PREV => l_due_date,
P_DUE_DATE_CURR => l_due_date,
P_RESCHEDULE_TIME_PREV => l_reschedule_time,
P_RESCHEDULE_TIME_CURR => l_reschedule_time,
P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
P_STATUS => l_audit_log_sts,
P_ERROR_CODE => l_msg_data
);
ELSIF (l_dist_items(j).WORK_ITEM_STATUS = 'DELETE')
THEN
l_work_item_status_id := 4;
update ieu_uwqm_items
set distribution_status_id = 1
where work_item_id = l_dist_items(j).work_item_id;
update ieu_uwqm_items
set distribution_status_id = 1
where work_item_id = l_nw_items_list(y).work_item_id
and distribution_status_id = 2;
SELECT enter_from_task, object_function, object_parameters
INTO l_enter_from_task, l_object_function, l_object_parameters
FROM JTF_OBJECTS_B
WHERE OBJECT_CODE = p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE;
SELECT enter_from_task, object_function, object_parameters
INTO l_enter_from_task, l_object_function, l_object_parameters
FROM JTF_OBJECTS_B
WHERE OBJECT_CODE = p_dist_workitem_data(n).WORKITEM_OBJ_CODE;
SELECT ws_id
INTO l_ws_id
FROM ieu_uwqm_work_sources_b
WHERE ws_code = p_dist_workitem_data(n).WORK_SOURCE
-- AND nvl(not_valid_flag,'N') = 'N';
SELECT enter_from_task, object_function, object_parameters
INTO l_enter_from_task, l_object_function, l_object_parameters
FROM JTF_OBJECTS_B
WHERE OBJECT_CODE = p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE;
SELECT enter_from_task, object_function, object_parameters
INTO l_enter_from_task, l_object_function, l_object_parameters
FROM JTF_OBJECTS_B
WHERE OBJECT_CODE = p_dist_del_workitem_data.WORKITEM_OBJ_CODE;
SELECT enter_from_task, object_function, object_parameters
INTO l_enter_from_task, l_object_function, l_object_parameters
FROM JTF_OBJECTS_B
WHERE OBJECT_CODE = p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE;
SELECT enter_from_task, object_function, object_parameters
INTO l_enter_from_task, l_object_function, l_object_parameters
FROM JTF_OBJECTS_B
WHERE OBJECT_CODE = p_dist_workitem_data(n).WORKITEM_OBJ_CODE;
SELECT LKUPS.MEANING
INTO L_WORK_TYPE
FROM FND_LOOKUP_VALUES_VL LKUPS, IEU_UWQ_SEL_ENUMERATORS ENUM
WHERE ENUM.ENUM_TYPE_UUID = p_dist_workitem_data(n).IEU_ENUM_TYPE_UUID
AND LKUPS.LOOKUP_TYPE(+) = ENUM.WORK_Q_LABEL_LU_TYPE
AND LKUPS.VIEW_APPLICATION_ID(+) = ENUM.APPLICATION_ID
AND LKUPS.LOOKUP_CODE(+) = WORK_Q_LABEL_LU_CODE;
SELECT enter_from_task, object_function, object_parameters
INTO l_enter_from_task, l_object_function, l_object_parameters
FROM JTF_OBJECTS_B
WHERE OBJECT_CODE = p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE;
SELECT enter_from_task, object_function, object_parameters
INTO l_enter_from_task, l_object_function, l_object_parameters
FROM JTF_OBJECTS_B
WHERE OBJECT_CODE = p_dist_del_workitem_data.WORKITEM_OBJ_CODE;
SELECT LKUPS.MEANING
INTO L_WORK_TYPE
FROM FND_LOOKUP_VALUES_VL LKUPS, IEU_UWQ_SEL_ENUMERATORS ENUM
WHERE ENUM.ENUM_TYPE_UUID = p_dist_del_workitem_data.IEU_ENUM_TYPE_UUID
AND LKUPS.LOOKUP_TYPE(+) = ENUM.WORK_Q_LABEL_LU_TYPE
AND LKUPS.VIEW_APPLICATION_ID(+) = ENUM.APPLICATION_ID
AND LKUPS.LOOKUP_CODE(+) = WORK_Q_LABEL_LU_CODE;
select WS_B.WS_ID, WS_B.DISTRIBUTE_TO, WS_B.DISTRIBUTE_FROM , WS_B.DISTRIBUTION_FUNCTION
from IEU_UWQM_WORK_SOURCES_B WS_B
where ws_b.not_valid_flag = 'N';
l_delete_flag_yes varchar2(1);
select group_id from jtf_rs_group_members
where resource_id = p_resource_id
and nvl(delete_flag, 'N') <> l_delete_flag_yes;
l_delete_flag_yes := 'Y';
1. owner_id in (select group_id from jtf_rs_group_members
where resource_id = :resource_id
and nvl(delete_flag,'N') <> 'Y');
2. exists (select 1 from jtf_rs_group_members
where resource_id = :resource_id
and nvl(delete_flag,'N') <> 'Y');
select WS_B.WS_ID
into l_ws_id
from IEU_UWQM_WORK_SOURCES_B WS_B
where ws_code = p_ws_det_list(i).ws_code
-- and ws_b.not_valid_flag = 'N';
x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':delete_flag'||l_bindvar_fm_ctr;
x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':delete_flag'||l_bindvar_fm_ctr;
(select group_id from jtf_rs_group_members
where resource_id = :resource_id'||
' and nvl(delete_flag,'||':delete_flag'||(l_bindvar_fm_ctr-1)||') <> '||':delete_flag'||l_bindvar_fm_ctr||')';
l_last_update_date date;
l_delete_flag_no varchar2(1);
select group_id from jtf_rs_group_members
where resource_id = p_resource_id
and nvl(delete_flag, 'N') = l_delete_flag_no;
l_delete_flag_no :='N';
l_last_update_date := sysdate - 10/1440;
l_sql_stmt := 'UPDATE IEU_UWQM_ITEMS
SET DISTRIBUTION_STATUS_ID = 1
WHERE '|| l_df_grp_id_clause ||
'AND DISTRIBUTION_STATUS_ID = '||':l_distribution_status_id '||
'AND STATUS_ID = '||':l_status_id'
||' and to_date(last_update_date'||','||''''||'DD-MON-YYYY HH24:MI:SS'||''''||') < '
||' to_date('||''''||l_last_update_date||''''||','||''''||'DD-MON-YYYY HH24:MI:SS'||''''||')' ;