[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
LAST_UPDATE_DATE DATE ,
CREATION_DATE DATE
);
SELECT 1
FROM jtf_tasks_b jt
WHERE jt.task_id = p_task_id
AND (jt.source_object_type_code = 'TASK' OR jt.source_object_type_code IS NULL)
UNION
SELECT 1
FROM jtf_task_statuses_b jts
WHERE jts.task_status_id = b_status_id
AND ( jts.ASSIGNED_FLAG = 'Y'
OR jts.COMPLETED_FLAG = 'Y'
OR jts.CLOSED_FLAG = 'Y');
SELECT 1
FROM jtf_tasks_b jt
WHERE jt.task_id = p_task_id
AND (jt.source_object_type_code = 'TASK' OR jt.source_object_type_code IS NULL)
UNION
SELECT 1
FROM jtf_task_statuses_b jts
WHERE jts.task_status_id = b_status_id
AND ( jts.ASSIGNED_FLAG = 'Y'
OR jts.COMPLETED_FLAG = 'Y'
OR jts.CLOSED_FLAG = 'Y');
SELECT 1
FROM JTF_TASK_TYPES_B
WHERE TASK_TYPE_ID = b_type_id
AND (RULE = 'DISPATCH' OR private_flag = 'Y');
SELECT jt.CREATED_BY, jtt.private_flag, jt.source_object_type_code -- 22 means Escalation task
FROM JTF_TASKS_B jt,
jtf_task_types_b jtt
WHERE jt.TASK_ID = b_task_id
AND jtt.task_type_id = jt.task_type_id;
SELECT USER_ID
FROM ASG_USER usr,
CS_INCIDENTS_ALL_B inc,
JTF_TASKS_B tsk
WHERE tsk.TASK_ID=b_task_id
AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
AND usr.GROUP_ID=inc.owner_group_id --is_mfs_grp
AND usr.USER_ID=usr.OWNER_ID
UNION ALL
SELECT USER_ID --not mfs grp, get SR created by
FROM ASG_USER usr,
CS_INCIDENTS_ALL_B inc,
JTF_TASKS_B tsk
WHERE tsk.TASK_ID=b_task_id
AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
AND usr.USER_ID=inc.created_by;
csm_task_event_pkg.acc_insert(p_task_id=>l_task_id,p_user_id=>l_user_id);
SELECT USER_ID
FROM ASG_USER usr,
CS_INCIDENTS_ALL_B inc,
JTF_TASKS_B tsk
WHERE tsk.TASK_ID=b_task_id
AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
AND usr.GROUP_ID=inc.owner_group_id --is_mfs_grp
AND usr.USER_ID=usr.OWNER_ID
UNION ALL
SELECT USER_ID --not mfs grp, get SR created by
FROM ASG_USER usr,
CS_INCIDENTS_ALL_B inc,
JTF_TASKS_B tsk
WHERE tsk.TASK_ID=b_task_id
AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
AND usr.USER_ID=inc.created_by;
select task_id,resource_id
from JTF_TASK_ASSIGNMENTS
where TASK_ASSIGNMENT_ID=b_task_assignment_id;
SELECT RESOURCE_ID
FROM JTF_RS_RESOURCE_EXTNS
WHERE USER_ID=b_user_id;
SELECT USER_ID
FROM JTF_RS_RESOURCE_EXTNS
WHERE RESOURCE_ID=b_resource_id;
csm_task_assignment_event_pkg.acc_insert(p_task_assignment_id=>l_task_assignment_id,p_user_id=>l_user_id);
CSM_USER_EVENT_PKG.INSERT_ACC(l_assignee_user_id,l_user_id);
JTF_TASK_ASSIGNMENTS_IUHK.update_task_assignment_pre
We retrieve the old record by selecting from db with task_assignment_id
Then, we compare the old resource id and new resource id for whether the resource has changed.
Parameter(s):
x_return_status
------------------------------------------------------------------*/
Procedure Task_Assignment_Pre_Upd(x_return_status OUT NOCOPY VARCHAR2)
IS
l_task_assignment_id NUMBER;
SELECT RESOURCE_ID, assignment_status_id, task_id
FROM JTF_TASK_ASSIGNMENTS
WHERE TASK_ASSIGNMENT_ID = b_task_assignment_id;
SELECT jtrs.user_id
FROM jtf_rs_resource_extns jtrs
WHERE jtrs.resource_id = p_resource_id;
l_is_resource_updated VARCHAR2(1);
l_is_assg_status_updated VARCHAR2(1);
SELECT RESOURCE_ID, assignment_status_id, task_id
FROM JTF_TASK_ASSIGNMENTS
WHERE TASK_ASSIGNMENT_ID = b_task_assignment_id;
SELECT access_id
FROM csm_task_assignments_acc
WHERE task_assignment_id = p_task_assignment_id;
SELECT USER_ID
FROM ASG_USER usr,
CS_INCIDENTS_ALL_B inc,
JTF_TASKS_B tsk
WHERE tsk.TASK_ID=b_task_id
AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
AND usr.GROUP_ID=inc.owner_group_id --is_mfs_grp
AND usr.USER_ID=usr.OWNER_ID
UNION ALL
SELECT USER_ID --not mfs grp, get SR created by
FROM ASG_USER usr,
CS_INCIDENTS_ALL_B inc,
JTF_TASKS_B tsk
WHERE tsk.TASK_ID=b_task_id
AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
AND usr.USER_ID=inc.created_by;
select task_id
from JTF_TASK_ASSIGNMENTS
where TASK_ASSIGNMENT_ID=b_task_assignment_id;
SELECT RESOURCE_ID
FROM JTF_RS_RESOURCE_EXTNS
WHERE USER_ID=b_user_id;
SELECT ACCESS_ID
FROM CSM_TASK_ASSIGNMENTS_ACC
WHERE TASK_ASSIGNMENT_ID=b_task_assignment_id
AND USER_ID=b_user_id;
SELECT USER_ID
FROM JTF_RS_RESOURCE_EXTNS
WHERE RESOURCE_ID=b_resource_id;
l_is_resource_updated := 'N';
l_is_assg_status_updated := 'N';
-- delete old resource_id from acc if he's not in any of his grp.
OPEN c_resource_id(l_owner_id);
CSM_USER_EVENT_PKG.DELETE_ACC(l_assignee_user_id,l_owner_id);
CSM_USER_EVENT_PKG.INSERT_ACC(l_assignee_user_id,l_owner_id);
CSM_UTIL_PKG.LOG('Resource updated for task_assg_id:' || l_task_assignment_id ,
'CSM_WF_PKG.Task_Assignment_Post_Upd',FND_LOG.LEVEL_PROCEDURE);
l_is_resource_updated := 'Y';
l_is_resource_updated := 'N';
CSM_UTIL_PKG.LOG('Status updated for task_assg_id:' || l_task_assignment_id ,
'CSM_WF_PKG.Task_Assignment_Post_Upd',FND_LOG.LEVEL_PROCEDURE);
l_is_assg_status_updated := 'Y';
AND l_is_resource_updated = 'N') THEN
CSM_UTIL_PKG.LOG('Status updated to downloadable for task_assg_id:' || l_task_assignment_id ,
'CSM_WF_PKG.Task_Assignment_Post_Upd',FND_LOG.LEVEL_PROCEDURE);
AND l_is_resource_updated = 'N') THEN
CSM_UTIL_PKG.LOG('Status updated to non-downloadable for task_assg_id:' || l_task_assignment_id ,
'CSM_WF_PKG.Task_Assignment_Post_Upd',FND_LOG.LEVEL_PROCEDURE);
l_is_assg_status_updated := 'N';
IF l_is_resource_updated = 'N' THEN
OPEN l_check_acc_exists(l_task_assignment_id);
RAISE_START_AUTO_SYNC_EVENT('CSM_TASK_ASSIGNMENTS',to_char(l_task_assignment_id),'UPDATE');
ELSIF l_is_resource_updated = 'Y' THEN
-- check if the new resource is a mobile resource; if it is then do a insert
Invoked by JTF_TASK_ASSIGNMENTS_IUHK.delete_task_assignment_post
and by concurrent program to purge closed task assignments
older than specified in profile: CSF_M_HISTORY.
Parameter(s):
x_return_status
--------------------------------------------------------*/
PROCEDURE Task_Assignment_Post_Del(
x_return_status OUT NOCOPY VARCHAR2
)
IS
-- define the primary key and assign value from the global variable
l_task_assignment_id number(15);
Invoked by jtf_tasks_iuhk.update_task_pre
The global variable for IUHK is: jtf_tasks_pub.p_task_user_hooks(.task_id)
we check all the task_assignments, whether they are mobile users.
No - nothing
Yes - check task_status. Whether status changed
No - if (task_assignment_acc record exists ) do UPDATE
Yes - check old status and new status
old downloadable, new NOT, TASK_ASSIGNMENT_PURGE
old new both not downloadable, do nothing
old new both downloadable, (if acc record exists) do UPDATE
old NOT, new downloadable, TASK_ASSIGNMENT_INS
Parameter(s):
x_return_status
------------------------------------------------------------------*/
Procedure Task_Pre_Upd ( x_return_status OUT NOCOPY VARCHAR2)
IS
l_jtf_task_id NUMBER;
SELECT TASK_STATUS_ID, TASK_TYPE_ID, SCHEDULED_START_DATE, SCHEDULED_END_DATE, task_id
FROM JTF_TASKS_B
WHERE TASK_ID = b_task_id;
SELECT rs.RESOURCE_ID, rs.USER_ID, tas.TASK_ASSIGNMENT_ID
FROM JTF_TASK_ASSIGNMENTS tas, JTF_RS_RESOURCE_EXTNS rs
WHERE TASK_ID = b_task_id
AND tas.RESOURCE_ID = rs.RESOURCE_ID;
SELECT TASK_STATUS_ID, TASK_TYPE_ID, SCHEDULED_START_DATE, SCHEDULED_END_DATE, task_id
FROM JTF_TASKS_B
WHERE TASK_ID = b_task_id;
SELECT USER_ID
FROM ASG_USER usr,
CS_INCIDENTS_ALL_B inc,
JTF_TASKS_B tsk
WHERE tsk.TASK_ID=b_task_id
AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
AND usr.GROUP_ID=inc.owner_group_id --is_mfs_grp
AND usr.USER_ID=usr.OWNER_ID
UNION ALL
SELECT USER_ID --not mfs grp, get SR created by
FROM ASG_USER usr,
CS_INCIDENTS_ALL_B inc,
JTF_TASKS_B tsk
WHERE tsk.TASK_ID=b_task_id
AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
AND usr.USER_ID=inc.created_by;
SELECT TASK_ASSIGNMENT_ID,resource_id
FROM JTF_TASK_ASSIGNMENTS
WHERE TASK_ID=b_task_id;
SELECT RESOURCE_ID
FROM JTF_RS_RESOURCE_EXTNS
WHERE USER_ID=b_user_id;
SELECT USER_ID
FROM JTF_RS_RESOURCE_EXTNS
WHERE RESOURCE_ID=b_resource_id;
csm_task_event_pkg.acc_insert(p_task_id=>l_jtf_task_id,p_user_id=>l_owner_id);
csm_task_assignment_event_pkg.acc_insert(p_task_assignment_id=>assign_rec.task_assignment_id,p_user_id=>l_owner_id);
CSM_USER_EVENT_PKG.INSERT_ACC(l_assignee_user_id,l_owner_id);
csm_task_event_pkg.acc_delete(p_task_id=>l_jtf_task_id,p_user_id=>l_owner_id);
csm_task_assignment_event_pkg.acc_delete(p_task_assignment_id=>assign_rec.task_assignment_id,p_user_id=>l_owner_id);
/*Other grp's member Resource to be deleted from acc if assigned to him*/
OPEN c_resource_id(l_owner_id);
CSM_USER_EVENT_PKG.DELETE_ACC(l_assignee_user_id,l_owner_id);
l_task_assignment_list.DELETE;
l_user_list.DELETE;
l_resource_list.DELETE;
old new both downloadable, (if acc record exists) do UPDATE
-- old NOT, new downloadable, TASK_ASSIGNMENT_INS
*/
-- CSM_UTIL_PKG.pvt_log('Compare old and new data');
CSM_UTIL_PKG.LOG('Do updates for task_id:' || l_jtf_task_id ,
'CSM_WF_PKG.Task_Post_Upd',FND_LOG.LEVEL_PROCEDURE);
RAISE_START_AUTO_SYNC_EVENT('CSM_TASKS',to_char(l_jtf_task_id),'UPDATE');
SELECT owner_group_id,created_by
FROM cs_incidents_all_b
WHERE incident_id = p_incident_id;
* The user hook interface for SR pre_update
*/
--12.1
PROCEDURE SR_Pre_Upd( x_return_status OUT NOCOPY VARCHAR2)
IS
l_incident_id cs_incidents_all.incident_id%TYPE;
SELECT incident_id,
customer_id,
install_site_id,
customer_product_id,
inventory_item_id,
inv_organization_id,
contract_service_id,
incident_location_id,
customer_id,
incident_location_id,
owner_group_id
FROM cs_incidents_all_b csi
WHERE incident_id = p_incident_id;
SELECT ADDRESS_ID
FROM JTF_TASKS_B
WHERE SOURCE_OBJECT_TYPE_CODE = 'SR'
AND SOURCE_OBJECT_ID = b_incident_id;
SELECT ADDRESS_ID,LOCATION_ID
FROM JTF_TASKS_B
WHERE SOURCE_OBJECT_TYPE_CODE = 'SR'
AND SOURCE_OBJECT_ID = b_incident_id;
SELECT sr_contact_point_id,
party_id,
contact_point_id,
primary_flag,
contact_point_type,
contact_type,
last_update_date,
creation_date
FROM cs_hz_sr_contact_points
WHERE incident_id = p_incident_id;
g_sr_cont_points_pre_upd_tbl(l_rec_count).last_update_date := l_sr_cont_pts_pre_upd_rec.last_update_date;
p_incident_id: INCIDENT_ID of the SR updated
p_old_install_site_id: Old value of the INSTALL_SITE_ID
p_is_sr_customer_updated: true, if the customer has been updated, false otherwise
p_old_sr_customer_id: Old value of the CUSTOMER_ID
p_is_sr_instance_updated: true, if the instance has been updated, false otherwise
p_old_instance_id: Old value of the INSTANCE_ID
p_is_inventory_item_updated: true, if the inventory item has been updated, false otherwise
p_old_inventory_item_id: Old value of the INVENTORY_ITEM_ID
*********************************************************/
--12.1
Procedure SR_Post_Upd( x_return_status OUT NOCOPY VARCHAR2)
IS
l_incident_id cs_incidents_all.incident_id%TYPE;
l_is_install_site_updated char(1);
l_is_sr_customer_updated char(1);
l_is_inventory_item_updated char(1);
l_is_sr_instance_updated char(1);
l_is_contr_service_id_updated char(1);
l_is_incident_location_updated char(1);
SELECT incident_id,
customer_id,
install_site_id,
customer_product_id,
inventory_item_id,
inv_organization_id,
contract_service_id,
incident_location_id,
owner_group_id,
created_by
FROM cs_incidents_all_b
WHERE incident_id = p_incident_id;
SELECT ADDRESS_ID
FROM JTF_TASKS_B
WHERE SOURCE_OBJECT_TYPE_CODE = 'SR'
AND SOURCE_OBJECT_ID = b_incident_id;
SELECT ADDRESS_ID,LOCATION_ID
FROM JTF_TASKS_B
WHERE SOURCE_OBJECT_TYPE_CODE = 'SR'
AND SOURCE_OBJECT_ID = b_incident_id;
SELECT sr_contact_point_id,
party_id,
contact_point_id,
primary_flag,
contact_point_type,
contact_type,
last_update_date,
creation_date
FROM cs_hz_sr_contact_points
WHERE incident_id = p_incident_id;
SELECT 1
FROM cs_incidents_all_b csa
WHERE csa.incident_id = p_incident_id
AND EXISTS(SELECT 1 FROM ASG_USER WHERE GROUP_ID=csa.OWNER_GROUP_ID AND NVL(MULTI_PLATFORM,'N')='N')
UNION ALL
SELECT 1
FROM cs_incidents_all_b csa
WHERE csa.incident_id = p_incident_id
AND EXISTS(SELECT 1 FROM ASG_USER WHERE USER_ID=csa.created_by AND NVL(MULTI_PLATFORM,'N')='N')
UNION ALL
SELECT 1
FROM jtf_tasks_b jt
WHERE jt.source_object_id = p_incident_id
AND jt.source_object_type_code = 'SR'
AND EXISTS(SELECT 1 FROM ASG_USER WHERE USER_ID=jt.created_by AND NVL(MULTI_PLATFORM,'N')='N')
UNION ALL
SELECT 1
FROM jtf_task_assignments jta,
jtf_tasks_b jt
WHERE jt.source_object_id = p_incident_id
AND jt.source_object_type_code = 'SR'
AND jta.task_id = jt.task_id
AND jta.resource_type_code='RS_EMPLOYEE'
AND EXISTS(SELECT 1 FROM ASG_USER WHERE resource_id = jta.resource_id);
l_is_install_site_updated := 'N';
l_is_sr_customer_updated := 'N';
l_is_inventory_item_updated := 'N';
l_is_sr_instance_updated := 'N';
l_is_contr_service_id_updated := 'N';
l_is_incident_location_updated := 'N';
l_is_install_site_updated := 'Y';
l_is_incident_location_updated := 'Y';
l_is_sr_customer_updated := 'Y';
l_is_inventory_item_updated := 'Y';
l_is_sr_instance_updated := 'Y';
l_is_contr_service_id_updated := 'Y';
p_is_incident_location_updated=>l_is_incident_location_updated,
p_old_incident_location_id=> g_sr_pre_upd_rec.incident_location_id,
p_is_install_site_updated=>l_is_install_site_updated,
p_old_install_site_id=>g_sr_pre_upd_rec.install_site_id,
p_is_sr_customer_updated=>l_is_sr_customer_updated,
p_old_sr_customer_id=>g_sr_pre_upd_rec.customer_id,
p_is_sr_instance_updated=>l_is_sr_instance_updated,
p_old_instance_id=>g_sr_pre_upd_rec.customer_product_id,
p_is_inventory_item_updated=>l_is_inventory_item_updated,
p_old_inventory_item_id=>g_sr_pre_upd_rec.inventory_item_id,
p_old_organization_id=>g_sr_pre_upd_rec.inv_organization_id,
p_old_party_id=>g_sr_pre_upd_rec.party_id,
p_old_location_id=>g_sr_pre_upd_rec.location_id,
p_is_contr_service_id_updated=>l_is_contr_service_id_updated,
p_old_contr_service_id=>g_sr_pre_upd_rec.contract_service_id);
g_sr_cont_points_post_upd_tbl(l_rec_count).last_update_date := l_sr_cont_pts_post_upd_rec.last_update_date;
IF g_sr_cont_points_post_upd_tbl(post).last_update_date > g_sr_cont_points_pre_upd_tbl(pre).last_update_date
AND g_sr_cont_points_post_upd_tbl(post).creation_date = g_sr_cont_points_pre_upd_tbl(pre).creation_date THEN
-- record has been updated
-- SR_Contact_Pre_Upd(g_sr_cont_points_pre_upd_tbl(pre).sr_contact_point_id);
RAISE_START_AUTO_SYNC_EVENT('CSM_INCIDENTS_ALL',to_char(l_incident_id),'UPDATE');
group resource member is deleted.
Invoked by JTM_RS_GROUP_MEMBER_VUHK.delete_group_members_pre
Parameter(s):
p_group_id,
p_resource_id,
x_return_status
----------------------------------------------------*/
PROCEDURE JTF_RS_Group_Member_Pre_Del(p_group_id IN jtf_rs_group_members.group_id%TYPE,
p_resource_id IN jtf_rs_group_members.resource_id%TYPE,
x_return_status OUT nocopy VARCHAR2)
IS
l_sqlerrno VARCHAR2(20);
SELECT acc.debrief_header_id,
acc.user_id
FROM csm_debrief_headers_acc acc
WHERE acc.debrief_header_id = p_debrief_header_id;
SELECT dhdr.task_assignment_id, jtrs.user_id, jta.resource_id
FROM csf_debrief_headers dhdr,
jtf_task_assignments jta,
jtf_rs_resource_extns jtrs
WHERE dhdr.debrief_header_id = p_debrief_header_id
AND jta.task_assignment_id = dhdr.task_assignment_id
AND jtrs.resource_id = jta.resource_id
;
SELECT dhdr.debrief_header_id, acc.user_id
FROM csf_debrief_headers dhdr,
csm_debrief_headers_acc acc
WHERE dhdr.debrief_header_id = p_debrief_header_id
AND acc.debrief_header_id = dhdr.debrief_header_id;
SELECT debrief_line_id,
inventory_item_id,
instance_id
FROM csf_debrief_lines
WHERE debrief_line_id = p_debrief_line_id;
l_is_inventory_item_updated varchar2(1);
l_is_debrief_instance_updated varchar2(1);
SELECT debrief_line_id,
inventory_item_id,
instance_id
FROM csf_debrief_lines
WHERE debrief_line_id = p_debrief_line_id;
l_is_inventory_item_updated := 'N';
l_is_debrief_instance_updated := 'N';
l_is_inventory_item_updated := 'Y';
l_is_debrief_instance_updated := 'Y';
p_is_inventory_item_updated=>l_is_inventory_item_updated,
p_old_instance_id=>g_debrief_line_pre_upd_rec.instance_id,
p_is_instance_updated=>l_is_debrief_instance_updated);
* Post Delete of CS_DEBRIEF_LINES records.
*/
Procedure CSF_Debrief_Line_Post_Del (x_return_status OUT NOCOPY VARCHAR2)
IS
l_debrief_line_id csf_debrief_lines.debrief_line_id%TYPE;
SELECT acc.user_id, dbl.debrief_line_id
FROM csf_debrief_lines dbl,
csm_debrief_lines_acc acc
WHERE dbl.debrief_line_id = p_debrief_line_id
AND acc.debrief_line_id = dbl.debrief_line_id;
SELECT organization_id,
subinventory_code,
effective_date_start,
effective_date_end,
default_code
FROM csp_inv_loc_assignments cila,
asg_user au
WHERE cila.csp_inv_loc_assignment_id = p_csp_inv_loc_assignment_id
AND au.resource_id = cila.resource_id;
SELECT cila.organization_id,
cila.subinventory_code,
cila.effective_date_start,
cila.effective_date_end,
cila.resource_id,
au.user_id,
cila.default_code
FROM csp_inv_loc_assignments cila,
asg_user au
WHERE cila.csp_inv_loc_assignment_id = p_csp_inv_loc_assignment_id
AND au.resource_id = cila.resource_id;
ELSE -- update pub item if org/subinventory/default code is updated
CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_UPD_INIT(p_csp_inv_loc_assignment_id=>l_csp_inv_loc_assignment_id);
SELECT pla.location_id ,
pla.org_id ,
decode(rcr.resource_type,'RS_GROUP','G','E') resource_type,
au.user_id user_id,
cas.party_site_id party_site_id
FROM po_location_associations_all pla,
hz_cust_site_uses_all csu,
hz_cust_acct_sites_all cas,
csp_rs_cust_relations rcr,
asg_user au
WHERE pla.location_id = p_locationid
AND csu.site_use_id = pla.site_use_id
AND csu.site_use_code = 'SHIP_TO'
AND csu.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = rcr.customer_id
AND cas.status = 'A' -- only active sites
AND ((rcr.resource_type='RS_EMPLOYEE' AND au.resource_id = rcr.resource_id)
OR
(rcr.resource_type='RS_GROUP' AND rcr.resource_id IN (SELECT group_id FROM jtf_rs_group_members_vl WHERE category='EMPLOYEE' AND resource_id = au.resource_id)))
AND au.enabled='Y'
AND NOT EXISTS
(SELECT 1
FROM csm_po_loc_ass_all_acc acc
WHERE acc.user_id = au.user_id
AND acc.location_id = pla.location_id
AND acc.org_id = pla.org_id
AND acc.RESOURCE_TYPE= decode(rcr.resource_type,'RS_GROUP','G','E'));
SELECT acc.location_id,
acc.org_id,
acc.resource_type,
acc.user_id
FROM csm_po_loc_ass_all_acc acc
WHERE acc.location_id = p_locationid;
/* --Party Sites need not be updated
csm_party_site_event_pkg.party_sites_acc_u
(p_party_site_id=>r_ship_locations_upd_rec.party_site_id,
p_user_id=>r_ship_locations_upd_rec.user_id,
p_error_msg=>l_error_msg,
x_return_status=>l_return_status);*/
SELECT hdr.resource_id,
jtrs.user_id
FROM csp_requirement_headers hdr,
jtf_rs_resource_extns jtrs
WHERE hdr.requirement_header_id = p_req_header_id
AND jtrs.resource_id = hdr.resource_id
UNION
SELECT ta.resource_id,
jtrs.user_id
FROM csp_requirement_headers hdr,
jtf_tasks_b jt,
jtf_task_assignments ta,
jtf_rs_resource_extns jtrs
WHERE hdr.requirement_header_id = p_req_header_id
AND jt.task_id = hdr.task_id
AND ta.task_id = jt.task_id
AND jtrs.resource_id = ta.resource_id
;
SELECT acc.requirement_header_id,
acc.user_id
FROM csm_req_headers_acc acc
WHERE acc.requirement_header_id = p_req_header_id;
SELECT acc.requirement_header_id,
acc.user_id
FROM csm_req_headers_acc acc
WHERE acc.requirement_header_id = p_req_header_id;
SELECT hdr.requirement_header_id,
hdr.resource_id,
jtrs.user_id
FROM csp_requirement_headers hdr,
csp_requirement_lines line,
jtf_rs_resource_extns jtrs
WHERE hdr.requirement_header_id = line.requirement_header_id
AND line.requirement_line_id = p_req_line_id
AND jtrs.resource_id = hdr.resource_id
UNION
SELECT hdr.requirement_header_id,
ta.resource_id,
jtrs.user_id
FROM csp_requirement_headers hdr,
csp_requirement_lines line,
jtf_tasks_b jt,
jtf_task_assignments ta,
jtf_rs_resource_extns jtrs
WHERE hdr.requirement_header_id = line.requirement_header_id
AND line.requirement_line_id = p_req_line_id
AND jt.task_id = hdr.task_id
AND ta.task_id = jt.task_id
AND jtrs.resource_id = ta.resource_id
;
SELECT acc.requirement_line_id,
acc.user_id
FROM csm_req_lines_acc acc
WHERE acc.requirement_line_id = p_req_line_id;
SELECT acc.requirement_line_id,
acc.user_id
FROM csm_req_lines_acc acc
WHERE acc.requirement_line_id = p_req_line_id;
NOTE is updated.
Invoked by JTM_NOTES_VUHK.update_note_post
Parameter(s):
p_api_version
, p_init_msg_list
, p_commit
, p_validation_level
, x_msg_count
, x_msg_data
, x_return_status
,p_jtf_note_id
----------------------------------------------------*/
PROCEDURE JTF_Note_PRE_Upd(p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, p_validation_level IN NUMBER
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
,p_jtf_note_id in jtf_notes_b.jtf_note_id%type)
IS
l_sqlerrno VARCHAR2(20);
SELECT CSM_ACTIVITY_SEQ.nextval
FROM dual;
SELECT counters.COUNTER_ID
FROM CS_COUNTERS counters
, CS_COUNTER_GROUPS counter_groups
WHERE counters.counter_group_id = counter_groups.counter_group_id
AND counter_groups.counter_group_id = b_ctr_grp_id
AND counters.TYPE = 'REGULAR';
SELECT COUNT(1)
FROM CS_COUNTER_GROUPS counter_groups
, csm_item_instances_acc acc
WHERE b_source_object_cd = 'CP'
AND counter_groups.counter_group_id = b_ctr_grp_id
AND counter_groups.source_object_code = b_source_object_cd
AND acc.instance_id = counter_groups.source_object_id
AND counter_groups.source_object_id = b_source_object_id;
SELECT counters.counter_id
FROM CS_COUNTER_GROUPS counter_groups,
cs_counters counters,
csm_item_instances_acc acc
WHERE counter_groups.source_object_code = b_source_object_cd
AND counter_groups.source_object_id = b_source_object_id
AND acc.instance_id = counter_groups.source_object_id
AND counters.counter_group_id = counter_groups.counter_group_id
AND counters.TYPE = 'REGULAR';
SELECT counters.counter_id
FROM CS_COUNTERS counters
, CS_COUNTER_GROUPS counter_groups
, csm_item_instances_acc acc
WHERE counter_groups.counter_group_id = b_ctr_grp_id
AND counters.counter_group_id = counter_groups.counter_group_id
AND counter_groups.source_object_code = 'CP'
AND acc.instance_id = counter_groups.source_object_id;
If yes, do update
------------*/
Procedure CS_Counter_Pre_Upd(
P_Api_Version IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
P_Commit IN VARCHAR2 := FND_API.G_FALSE,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2,
p_counter_id IN NUMBER,
p_object_version_number IN NUMBER,
p_cascade_upd_to_instances IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_object_version_number OUT NOCOPY NUMBER
-- p_user_id in number,
-- p_cs_counters cs_counters%rowtype
)
IS
/* CURSOR l_acc_del_csr(b_counter_id NUMBER) IS
SELECT acc.user_id
FROM CS_COUNTERS counters
, CS_COUNTER_GROUPS counter_groups
, csm_item_instances_acc acc
WHERE counters.counter_group_id = counter_groups.counter_group_id
AND counter_groups.source_object_code = 'CP'
AND SYSDATE NOT BETWEEN NVL(counters.start_date_active, SYSDATE) AND NVL(counters.end_date_active, SYSDATE)
and acc.instance_id = counter_groups.source_object_id
and counters.counter_id = b_counter_id;
SELECT acc.user_id
FROM CS_COUNTERS counters
, CS_COUNTER_GROUPS counter_groups
, csm_item_instances_acc acc
WHERE counters.counter_group_id = counter_groups.counter_group_id
AND counter_groups.source_object_code = 'CP'
AND SYSDATE BETWEEN NVL(counters.start_date_active, SYSDATE) AND NVL(counters.end_date_active, SYSDATE)
and acc.instance_id = counter_groups.source_object_id
and counters.counter_id = b_counter_id;
SELECT CSM_ACTIVITY_SEQ.nextval
FROM dual;
SELECT COUNT(1)
FROM CS_COUNTERS counters
, CS_COUNTER_GROUPS counter_groups
, csm_item_instances_acc acc
WHERE counters.counter_group_id = counter_groups.counter_group_id
AND counter_groups.source_object_code = 'CP'
AND acc.instance_id = counter_groups.source_object_id
AND counters.counter_id = b_counter_id;
counter value is updated
Arguments:
*********************************************************/
PROCEDURE CS_Counter_Value_Pre_Upd(p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, p_validation_level IN NUMBER
, p_counter_grp_log_id IN NUMBER
, p_object_version_number IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2 )
IS
l_counter_grp_log_id cs_counter_grp_log.counter_grp_log_id%TYPE;
Called when a Field Service Palm user is deleted
Parameter(s):
User_ID
--------------------------------------------------------*/
Procedure User_Del(p_user_id IN NUMBER)
IS
l_sqlerrno VARCHAR2(20);
SELECT ACCESS_ID
FROM CSM_TASK_ASSIGNMENTS_ACC
WHERE TASK_ASSIGNMENT_ID=b_task_assignment_id;