[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MAX(DT) FROM
(SELECT nvl(last_run_date, (sysdate - 365*50)) as DT FROM jtm_con_request_data
WHERE package_name = 'CSM_NOTIFICATION_EVENT_PKG'
AND procedure_name = 'DOWNLOAD_MULTIPLAT_NFN'
UNION ALL
select sysdate - fnd_profile.value('CSM_PURGE_INTERVAL') as DT from dual);
FOR l_notf_rec IN ( SELECT DISTINCT wfn.notification_id ,au.USER_ID
FROM wf_notifications wfn,asg_user au
WHERE au.user_name IN (FROM_ROLE,RECIPIENT_ROLE)
AND au.enabled='Y' and au.multi_platform='Y'
AND wfn.STATUS='OPEN'
AND wfn.MESSAGE_TYPE='CSM_MSGS'
AND nvl(wfn.begin_date, sysdate) >= l_upd_date
AND NOT EXISTS(SELECT 1 FROM CSM_NOTIFICATIONS_ACC ACC
WHERE ACC.NOTIFICATION_ID = WFN.NOTIFICATION_ID
AND ACC.USER_ID = AU.USER_ID))
LOOP
INSERT_NOTIFICATIONS_ACC (l_notf_rec.notification_id, l_notf_rec.user_id);
UPDATE jtm_con_request_data
SET last_run_date = l_run_date
WHERE package_name = 'CSM_NOTIFICATION_EVENT_PKG'
AND procedure_name = 'DOWNLOAD_MULTIPLAT_NFN';
SELECT 1
FROM csm_notifications_acc
WHERE notification_id = b_notificationid
AND user_id = p_userid;
PROCEDURE INSERT_NOTIFICATIONS_ACC (p_notification_id wf_notifications.notification_id%TYPE,
p_user_id fnd_user.user_id%TYPE)
IS
l_sysdate DATE;
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_notification_pubi_name
,P_ACC_TABLE_NAME => g_notification_acc_table_name
,P_SEQ_NAME => g_notification_seq_name
,P_PK1_NAME => g_notification_pk1_name
,P_PK1_NUM_VALUE => p_notification_id
,P_USER_ID => p_user_id
);
CSM_UTIL_PKG.LOG( 'Exception occurred in CSM_NOTIFICATION_EVENT_PKG.INSERT_NOTIFICATIONS_ACC: '
|| sqlerrm|| ' for PK ' || to_char(p_notification_id),
'CSM_NOTIFICATION_EVENT_PKG.INSERT_NOTIFICATIONS_ACC',FND_LOG.LEVEL_EXCEPTION);
END INSERT_NOTIFICATIONS_ACC;-- end INSERT_NOTIFICATIONS_ACC
SELECT DISTINCT wfn.notification_id
FROM wf_notifications wfn,
asg_user au
WHERE au.user_id=b_user_id
AND au.user_name IN (FROM_ROLE,RECIPIENT_ROLE)
AND wfn.STATUS='OPEN'
AND wfn.MESSAGE_TYPE='CSM_MSGS'
AND (nvl(wfn.begin_date, sysdate) between
(sysdate - csm_profile_pkg.get_task_history_days(b_user_id))and sysdate)
AND NOT EXISTS(SELECT 1
FROM CSM_NOTIFICATIONS_ACC ACC
WHERE ACC.NOTIFICATION_ID = WFN.NOTIFICATION_ID
AND ACC.USER_ID = AU.USER_ID);
SELECT DISTINCT wfn.notification_id
FROM WF_NOTIFICATIONS wfn,
ASG_USER au
WHERE au.user_id=b_user_id
AND wfn.STATUS='OPEN'
AND wfn.MESSAGE_TYPE='CSM_MSGS'
AND RECIPIENT_ROLE LIKE 'JRES_GRP:%'
AND au.user_id =CSM_UTIL_PKG.get_group_owner(substr(WFN.RECIPIENT_ROLE,instr(WFN.RECIPIENT_ROLE,':')+1))
AND au.enabled='Y'
AND (nvl(wfn.begin_date, sysdate) between
(sysdate - csm_profile_pkg.get_task_history_days(b_user_id))and sysdate)
AND NOT EXISTS(SELECT 1
FROM CSM_NOTIFICATIONS_ACC ACC
WHERE ACC.NOTIFICATION_ID = WFN.NOTIFICATION_ID
AND ACC.USER_ID = AU.USER_ID);
INSERT_NOTIFICATIONS_ACC (l_notf_rec.notification_id, p_user_id);
INSERT_NOTIFICATIONS_ACC (l_notf_rec.notification_id, p_user_id);
SELECT DISTINCT au.user_id
FROM WF_NOTIFICATIONS wfn,
ASG_USER au
WHERE wfn.NOTIFICATION_ID=b_nid
AND wfn.STATUS='OPEN'
AND wfn.MESSAGE_TYPE='CSM_MSGS'
AND au.user_name IN (WFN.FROM_ROLE,WFN.RECIPIENT_ROLE)
AND au.enabled='Y'
AND (nvl(wfn.begin_date, sysdate) between
(sysdate - csm_profile_pkg.get_task_history_days(au.user_id))and sysdate)
AND NOT EXISTS(SELECT 1
FROM CSM_NOTIFICATIONS_ACC ACC
WHERE ACC.NOTIFICATION_ID = WFN.NOTIFICATION_ID
AND ACC.USER_ID = AU.USER_ID);
SELECT DISTINCT au.user_id
FROM WF_NOTIFICATIONS wfn,
ASG_USER au
WHERE wfn.NOTIFICATION_ID=b_nid
AND wfn.STATUS='OPEN'
AND wfn.MESSAGE_TYPE='CSM_MSGS'
AND RECIPIENT_ROLE LIKE 'JRES_GRP:%'
AND au.user_id =CSM_UTIL_PKG.get_group_owner(substr(WFN.RECIPIENT_ROLE,instr(WFN.RECIPIENT_ROLE,':')+1))
AND au.enabled='Y'
AND (nvl(wfn.begin_date, sysdate) between
(sysdate - csm_profile_pkg.get_task_history_days(au.user_id))and sysdate)
AND NOT EXISTS(SELECT 1
FROM CSM_NOTIFICATIONS_ACC ACC
WHERE ACC.NOTIFICATION_ID = WFN.NOTIFICATION_ID
AND ACC.USER_ID = AU.USER_ID);
INSERT_NOTIFICATIONS_ACC (p_notification_id, r_rec.user_id);
INSERT_NOTIFICATIONS_ACC (p_notification_id, r_rec.user_id);
SELECT acc.user_id
FROM CSM_NOTIFICATIONS_ACC acc
WHERE acc.NOTIFICATION_ID=b_nid;
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_notification_pubi_name
,P_ACC_TABLE_NAME => g_notification_acc_table_name
,P_PK1_NAME => g_notification_pk1_name
,P_PK1_NUM_VALUE => l_notification_id
,P_USER_ID => r_notification_rec.user_id
);
SELECT 1
FROM jtm_con_request_data
WHERE product_code = 'CSM'
AND package_name = 'CSM_NOTIFICATION_EVENT_PKG'
AND procedure_name = 'PURGE_NOTIFICATION_CONC'
FOR UPDATE OF last_run_date NOWAIT;
select profile_option_value from fnd_profile_option_values where profile_option_id in
(select profile_option_id from fnd_profile_options where profile_option_name='CSM_PURGE_INTERVAL')
and level_id=10001;
FOR rec IN ( SELECT acc.user_id,acc.notification_id,wfn.status
FROM csm_notifications_acc acc,wf_notifications wfn
WHERE acc.notification_id = wfn.notification_id
AND (
(wfn.RECIPIENT_ROLE NOT LIKE 'JRES_GRP:%')
OR
(wfn.status='CLOSED') --skip OPEN grp messages, let user delete/close it
)
AND (wfn.status='CLOSED' OR NVL(wfn.begin_date, SYSDATE) < (SYSDATE - csm_profile_pkg.get_task_history_days(acc.user_id))))
LOOP
IF rec.status= 'OPEN' THEN
begin
wf_notification.respond(rec.notification_id);
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_notification_pubi_name
,P_ACC_TABLE_NAME => g_notification_acc_table_name
,P_PK1_NAME => g_notification_pk1_name
,P_PK1_NUM_VALUE => rec.notification_id
,P_USER_ID => rec.user_id
);
DELETE FROM WF_LOCAL_ROLES B WHERE NAME LIKE 'CSM%ROLE'
AND NOT EXISTS( SELECT 1 FROM FND_USER U
WHERE USER_NAME=substr(B.NAME,5,length(B.NAME)-9)
AND sysdate between nvl(start_date,sysdate-1) and nvl(end_date,sysdate+1));
FOR nfn_rec IN (SELECT NOTIFICATION_ID,USER_ID FROM csm_auto_sync_nfn
WHERE CREATION_DATE < SYSDATE-l_days)
LOOP
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_AUTO_SYNC_NFN')
,P_ACC_TABLE_NAME => 'CSM_AUTO_SYNC_NFN_ACC'
,P_PK1_NAME => 'NOTIFICATION_ID'
,P_PK1_NUM_VALUE => nfn_rec.NOTIFICATION_ID
,P_USER_ID => nfn_rec.USER_ID
);
DELETE FROM csm_auto_sync_nfn WHERE CREATION_DATE < SYSDATE-l_days;
FOR nfn_rec IN (SELECT NOTIFICATION_ID,USER_ID FROM csm_client_nfn_log_acc acc
WHERE NOT EXISTS (SELECT 1 FROM csm_auto_sync_nfn b
WHERE b.NOTIFICATION_ID=acc.NOTIFICATION_ID))
LOOP
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_CLIENT_NFN_LOG')
,P_ACC_TABLE_NAME => 'CSM_CLIENT_NFN_LOG_ACC'
,P_PK1_NAME => 'NOTIFICATION_ID'
,P_PK1_NUM_VALUE => nfn_rec.NOTIFICATION_ID
,P_USER_ID => nfn_rec.USER_ID
);
DELETE FROM csm_client_nfn_log cl
WHERE NOT EXISTS (SELECT 1 FROM csm_auto_sync_nfn b WHERE b.NOTIFICATION_ID=cl.NOTIFICATION_ID);
FOR ntf_rec IN (SELECT NOTIFICATION_ID FROM WF_NOTIFICATIONS
WHERE MESSAGE_TYPE='CSM_MSGS'
AND (STATUS='OPEN' OR END_DATE IS NULL)
AND BEGIN_DATE < SYSDATE-l_days)
LOOP
wf_notification.respond(ntf_rec.NOTIFICATION_ID);
UPDATE jtm_con_request_data
SET last_run_date = l_last_run_date
WHERE CURRENT OF l_upd_last_run_date_csr;
select a.component_name,parameter_value,component_status,
decode(component_status, 'RUNNING',0,'STARTING',1,'DEACTIVATED_SYSTEM',2,'STOPPED_ERROR',2) status
from fnd_svc_components a, FND_SVC_COMP_PARAM_VALS_V b
where component_type='WF_MAILER'
and a.component_id=b.component_id
and b.parameter_name='REPLYTO'
and b.parameter_value IS NOT NULL
order by status;
select parameter_value
from FND_SVC_COMP_PARAM_VALS_V
where component_id=10006
and parameter_name='REPLYTO';
SELECT NOTIFICATION_ID
INTO l_nid
FROM WF_NOTIFICATIONS
WHERE MESSAGE_TYPE='CSM_MSGS'
AND MESSAGE_NAME=l_template
AND RECIPIENT_ROLE=p_recipient_role
AND ITEM_KEY = l_item_key;
SELECT wf.name,wf.email_address,wf.notification_preference INTO role_name,l_wf_email ,l_pref
FROM WF_LOCAL_ROLES wf, ASG_USER au
WHERE wf.name ='CSM_'||b_user_name||'_ROLE'
AND au.user_name=b_user_name
AND au.enabled='Y';
SELECT email_address INTO l_fnd_email
FROM FND_USER WHERE USER_NAME=b_user_name
AND sysdate between nvl(start_date,sysdate-1) and nvl(end_date,sysdate+1);
l_stmt:= 'UPDATE WF_LOCAL_ROLES SET DESCRIPTION=DESCRIPTION ';
SELECT 'CSM_'||fu.user_name||'_ROLE',wf.display_name,fu.email_address
INTO role_name,role_display_name,l_fnd_email
FROM FND_USER fu, Asg_user au ,wf_local_roles wf
WHERE fu.user_name=b_user_name
AND sysdate between nvl(fu.start_date,sysdate-1) and nvl(fu.end_date,sysdate+1)
AND wf.name=fu.USER_NAME AND au.USER_NAME=fu.USER_NAME AND au.ENABLED='Y' ;
select ADDRESS1 || NVL2(ADDRESS2,', '||ADDRESS2,'')
|| NVL2(ADDRESS3,', '||ADDRESS3,'') || NVL2(ADDRESS4,', '||ADDRESS4,'')
|| NVL2(CITY,', '||CITY,'') || NVL2(STATE,', '||STATE,'') || NVL2(COUNTRY,', '||COUNTRY,'')
|| NVL2(POSTAL_CODE,' '||POSTAL_CODE,'') ADDRESS from hz_locations where location_id= b_loc_id;
SELECT 1
FROM jtf_task_assignments asg, csm_auto_sync_nfn nfn,
asg_system_dirty_queue sdq,csm_auto_sync_nfn_acc acc,
jtf_tasks_b tsk
where tsk.source_object_id=b_sr_id
and tsk.source_object_type_code='SR'
and tsk.task_id=asg.task_id
and (
(nfn.object_name='SERVICE_REQUEST' and nfn.object_id=tsk.source_object_id and tsk.source_object_type_code='SR')
OR
(nfn.object_name='TASK' and nfn.object_id=asg.task_id)
OR
(nfn.object_name='TASK_ASSIGNMENT' and nfn.object_id=task_assignment_id )
)
and nfn.response IS NULL
and nvl(nfn.reminders_sent,0) < 3
and nfn.user_id=b_user_id
and acc.notification_id=nfn.notification_id
and acc.user_id=nfn.user_id
and sdq.access_id=acc.access_id
and sdq.transaction_id is NULL
and sdq.PUB_ITEM ='CSM_AUTO_SYNC_NFN'
and rownum < 2;
SELECT 1
FROM jtf_task_assignments asg, csm_auto_sync_nfn nfn,
asg_system_dirty_queue sdq,csm_auto_sync_nfn_acc acc
where task_id=b_task_id
and (
(nfn.object_name='TASK' and nfn.object_id=task_id)
OR
(nfn.object_name='TASK_ASSIGNMENT' and nfn.object_id=task_assignment_id )
)
and nfn.user_id=b_user_id
and nfn.response IS NULL
and nvl(nfn.reminders_sent,0) < 3
and acc.notification_id=nfn.notification_id
and acc.user_id=nfn.user_id
and sdq.access_id=acc.access_id
and sdq.transaction_id is NULL
and sdq.PUB_ITEM ='CSM_AUTO_SYNC_NFN'
and rownum < 2;
SELECT 1
FROM jtf_task_assignments asg, csm_auto_sync_nfn nfn,
asg_system_dirty_queue sdq,csm_auto_sync_nfn_acc acc,
jtf_tasks_b tsk
where task_assignment_id=b_task_ass_id
and tsk.task_id=asg.task_id
and nfn.object_name='TASK_ASSIGNMENT' and nfn.object_id=task_assignment_id
and nfn.response IS NULL
and nvl(nfn.reminders_sent,0) < 3
and nfn.user_id=b_user_id
and acc.notification_id=nfn.notification_id
and acc.user_id=nfn.user_id
and sdq.access_id=acc.access_id
and sdq.transaction_id is NULL
and sdq.PUB_ITEM ='CSM_AUTO_SYNC_NFN'
and rownum < 2;
SELECT 1 INTO l_yes FROM JTF_TASKS_B WHERE TASK_ID=l_pk AND SOURCE_OBJECT_TYPE_CODE='TASK';
SELECT 1 INTO l_yes FROM JTF_TASKS_B A,JTF_TASK_ASSIGNMENTS B
WHERE TASK_ASSIGNMENT_ID=l_pk AND B.TASK_ID=A.TASK_ID AND A.SOURCE_OBJECT_TYPE_CODE='TASK';
SELECT usr.USER_ID,usr.USER_NAME,usr.email_address, b.TASK_ID,TASK_NAME,TASK_NUMBER,
INCIDENT_NUMBER, INC_TL.SUMMARY,hp.party_name ,
decode(nvl(inc.incident_location_type,'HZ_PARTY_SITE'), 'HZ_PARTY_SITE',
(select location_id from hz_party_sites where party_site_id = NVL(inc.incident_location_id, inc.install_site_id)),
'HZ_LOCATION',
(select location_id from hz_locations where location_id = NVL(inc.incident_location_id, inc.install_site_id))
) location_id
FROM ASG_USER AU, JTF_TASK_ASSIGNMENTS b,CSM_TASK_ASSIGNMENTS_ACC ACC,
JTF_TASKS_B tsk,JTF_TASKS_TL tsk_tl,cs_incidents_all_b INC,
cs_incidents_all_tl INC_TL, HZ_PARTIES hp, fnd_user usr
WHERE au.RESOURCE_ID=b.RESOURCE_ID
AND b.TASK_ASSIGNMENT_ID=b_task_ass_id
AND b.TASK_ID=tsk.TASK_ID
AND acc.TASK_ASSIGNMENT_ID=b.TASK_ASSIGNMENT_ID
AND au.USER_ID=acc.USER_ID
AND b.TASK_ID=tsk_tl.TASK_ID
AND INC.incident_id=tsk.SOURCE_OBJECT_ID
AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
AND INC.incident_id=INC_TL.incident_id
AND tsk_tl.Language=AU.Language
AND inc_tl.Language=AU.Language
AND hp.party_id=inc.customer_id
AND usr.USER_ID=AU.user_id
AND NVL(AU.multi_platform,'N')='N'
AND AU.user_id <> b.last_updated_by;
SELECT usr.USER_ID,usr.USER_NAME,usr.email_address, acc.TASK_ID,TASK_NAME,TASK_NUMBER,
INCIDENT_NUMBER, INC_TL.SUMMARY,hp.party_name ,
decode(nvl(inc.incident_location_type,'HZ_PARTY_SITE'), 'HZ_PARTY_SITE',
(select location_id from hz_party_sites where party_site_id = NVL(inc.incident_location_id, inc.install_site_id)),
'HZ_LOCATION',
(select location_id from hz_locations where location_id = NVL(inc.incident_location_id, inc.install_site_id))
) location_id
FROM ASG_USER AU, CSM_TASKS_ACC ACC,
JTF_TASKS_B tsk,JTF_TASKS_TL tsk_tl,cs_incidents_all_b INC,
cs_incidents_all_tl INC_TL, HZ_PARTIES hp, fnd_user usr
WHERE acc.TASK_ID=b_task_id
AND acc.TASK_ID=tsk.TASK_ID
AND au.USER_ID=acc.USER_ID
AND tsk.TASK_ID=tsk_tl.TASK_ID
AND INC.incident_id=tsk.SOURCE_OBJECT_ID
AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
AND INC.incident_id=INC_TL.incident_id
AND tsk_tl.Language=AU.Language
AND inc_tl.Language=AU.Language
AND hp.party_id=inc.customer_id
AND usr.USER_ID=AU.user_id
AND NVL(AU.multi_platform,'N')='N'
AND AU.user_id <> tsk.last_updated_by;
SELECT usr.USER_ID,usr.USER_NAME,usr.email_address, acc.TASK_ID,TASK_NAME,TASK_NUMBER,
(select location_id from hz_party_sites where party_site_id =tsk.address_id) location_id
FROM ASG_USER AU, CSM_TASKS_ACC ACC,
JTF_TASKS_B tsk,JTF_TASKS_TL tsk_tl, fnd_user usr
WHERE acc.TASK_ID=b_task_id
AND acc.TASK_ID=tsk.TASK_ID
AND au.USER_ID=acc.USER_ID
AND tsk.TASK_ID=tsk_tl.TASK_ID
AND tsk_tl.Language=AU.Language
AND usr.USER_ID=AU.user_id
AND NVL(AU.multi_platform,'N')='N'
AND AU.user_id <> tsk.last_updated_by;
SELECT usr.USER_ID,usr.USER_NAME,usr.email_address, b.TASK_ID,TASK_NAME,TASK_NUMBER,
(select location_id from hz_party_sites where party_site_id =tsk.address_id) location_id
FROM ASG_USER AU, JTF_TASK_ASSIGNMENTS b,CSM_TASK_ASSIGNMENTS_ACC ACC,
JTF_TASKS_B tsk,JTF_TASKS_TL tsk_tl, fnd_user usr
WHERE au.RESOURCE_ID=b.RESOURCE_ID
AND b.TASK_ASSIGNMENT_ID=b_task_ass_id
AND b.TASK_ID=tsk.TASK_ID
AND acc.TASK_ASSIGNMENT_ID=b.TASK_ASSIGNMENT_ID
AND au.USER_ID=acc.USER_ID
AND b.TASK_ID=tsk_tl.TASK_ID
AND tsk_tl.Language=AU.Language
AND usr.USER_ID=AU.user_id
AND NVL(AU.multi_platform,'N')='N'
AND AU.user_id <> b.last_updated_by;
SELECT usr.USER_ID,usr.USER_NAME,usr.email_address,acc.INCIDENT_ID,INCIDENT_NUMBER, INC_TL.SUMMARY,hp.party_name,
decode(nvl(inc.incident_location_type,'HZ_PARTY_SITE'), 'HZ_PARTY_SITE',
(select location_id from hz_party_sites where party_site_id = NVL(inc.incident_location_id, inc.install_site_id)),
'HZ_LOCATION',
(select location_id from hz_locations where location_id = NVL(inc.incident_location_id, inc.install_site_id))
) location_id
FROM ASG_USER AU, CSM_INCIDENTS_ALL_ACC ACC,cs_incidents_all_b INC,
cs_incidents_all_tl INC_TL, HZ_PARTIES hp, fnd_user usr
WHERE acc.incident_id=b_inc_id
AND au.USER_ID=acc.USER_ID
AND INC.incident_id=acc.incident_id
AND INC.incident_id=INC_TL.incident_id
AND inc_tl.Language=AU.Language
AND hp.party_id=inc.customer_id
AND usr.user_id=au.user_id
AND NVL(AU.multi_platform,'N')='N'
AND AU.user_id <> INC.last_updated_by;
SELECT acc.USER_ID,usr.USER_NAME,usr.email_address,
qry.QUERY_NAME,acc.INSTANCE_NAME
FROM csm_query_b qry,csm_query_instances_acc acc,
FND_USER usr,asg_user au
WHERE acc.QUERY_ID=qry.QUERY_ID
AND acc.INSTANCE_ID=b_inst
AND au.user_id=usr.USER_ID
AND NVL(AU.multi_platform,'N')='N'
AND acc.USER_ID=usr.USER_ID;
SELECT acc.USER_ID,usr.USER_NAME,usr.email_address,
b.segment1 "ITEM_NAME",b.description,acc.SUBINVENTORY_CODE,acc.QUANTITY, b.primary_uom_code
FROM mtl_system_items_b b, CSM_MTL_ONHAND_QTY_ACC acc,FND_USER usr ,asg_user au
WHERE acc.access_id=b_acc_id
AND b.inventory_item_id=acc.inventory_item_id
AND b.organization_id=acc.organization_id
AND usr.USER_ID=au.USER_ID
AND NVL(AU.multi_platform,'N')='N'
AND acc.user_id=usr.USER_ID;
SELECT acc.USER_ID,usr.USER_NAME,usr.email_address,
acc.requirement_line_id,oh.order_number,
CSP_PICK_UTILS.get_order_status (ol.LINE_ID, ol.FLOW_STATUS_CODE) order_status,
nvl(ol.actual_arrival_date,ol.schedule_arrival_date) arrival_date
FROM csm_req_lines_acc acc
, CSP_REQ_LINE_DETAILS crld
, OE_ORDER_LINES_ALL ol, OE_ORDER_HEADERS_ALL oh
, fnd_user usr,asg_user au
WHERE acc.access_id=b_acc_id
AND acc.USER_ID = usr.USER_ID
AND acc.requirement_line_id = crld.requirement_line_id
AND crld.source_id = ol.line_id
AND au.USER_ID=usr.USER_ID
AND NVL(AU.multi_platform,'N')='N'
AND ol.header_id = oh.header_id;
SELECT ACCESS_ID
FROM CSM_REQ_LINES_ACC acc,
CSP_REQ_LINE_DETAILS dtl
WHERE REQ_LINE_DETAIL_ID = b_req_detail_id
AND acc.requirement_line_id = dtl.requirement_line_id;
SELECT APP_ID,RESPONSIBILITY_ID
FROM ASG_USER
WHERE USER_ID=b_user_id;
IF(l_entity= 'CSM_TASKS') THEN -- Only Update mode (Called from csmewfb.pls)
l_entity:='TASK';
ELSIF (l_entity= 'CSM_INCIDENTS_ALL') THEN -- Only Update mode (Called from csmewfb.pls)
l_entity:='SERVICE_REQUEST';
-- removed update-only check as this call is asynchronous --Bug 13651273
-- TA Init might return saying not downloadable but Event will still get raised as NEW TA if created by user is MFS user
-- TA Update on auto assign(in planning->to assgined)
-- actually inserts into acc raising new event again before the above event is processed
RETURN;
ELSIF (l_entity= 'CSM_QUERY_RESULTS') THEN -- Only Insert mode (called from csmqryb.pls)
l_entity:='QUERY_RESULT';
IF l_email_address IS NOT NULL THEN --only insert so no optimzation reqd
l_subject := 'MFS_ALERT:NOTIFICATION_ID=&'||'#NID:MODE=NEW:OBJECT_NAME=QUERY_RESULT:INSTANCE_ID='||l_pk_value;
ELSIF (l_entity= 'CSF_M_INVENTORY') THEN -- Both Insert and Update mode (called from csmemsib.pls)
l_entity:='INVENTORY'; --Access_id is the PK, so only one record
IF l_mode='UPDATE' THEN
RETURN;
ELSIF (l_entity= 'CSM_REQ_LINES') THEN --Only Update mode (Called from csmerlb.pls)
l_entity:='ORDER_STATUS'; --Access_id is the PK, so only one record
ELSIF (l_entity= 'CSM_REQ_LINE_DETAILS') THEN --Only Insert mode (Called from csmewfb.pls)
l_entity:='ORDER_STATUS'; --l_req_line_detail_id is the PK
IF l_email_address IS NOT NULL THEN --only insert so no optimzation reqd
l_subject := 'MFS_ALERT:NOTIFICATION_ID=&'||'#NID:MODE=NEW:OBJECT_NAME=ORDER_STATUS:REQUIREMENT_LINE_ID='||l_pk_value;
INSERT INTO csm_auto_sync_nfn(USER_ID,NOTIFICATION_ID,OBJECT_NAME,OBJECT_ID,DML,REMINDERS_SENT,CREATION_DATE,CREATED_BY
,LAST_UPDATE_DATE,LAST_UPDATED_BY)
VALUES(l_usr_tab(I),l_not_tab(I),l_entity,l_pk_value,l_mode,0,sysdate,1,sysdate,1);
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_AUTO_SYNC_NFN')
,P_ACC_TABLE_NAME => 'CSM_AUTO_SYNC_NFN_ACC'
,P_SEQ_NAME => 'CSM_AUTO_SYNC_NFN_ACC_S'
,P_PK1_NAME => 'NOTIFICATION_ID'
,P_PK1_NUM_VALUE => l_not_tab(I)
,P_USER_ID => l_usr_tab(I)
);
select notification_id,text_value INTO l_nid,l_text_value from wf_notification_attributes
where notification_id = (select notification_id from wf_notifications
where message_type='CSM_MSGS' and message_name='DOWNLOAD_INIT_MSG'
and item_key=p_item_key) and name='RESULT';
UPDATE CSM_AUTO_SYNC_NFN
SET RESPONSE= l_text_value
,RESPONDED_ON=SYSDATE
WHERE NOTIFICATION_ID=l_nid;
SELECT 1
FROM WF_NOTIFICATIONS
WHERE NOTIFICATION_ID=b_nid
AND STATUS='CLOSED';
SELECT APP_ID,RESPONSIBILITY_ID
FROM ASG_USER
WHERE USER_NAME=b_user_name;
UPDATE CSM_AUTO_SYNC_NFN
SET REMINDERS_SENT = REMINDERS_SENT + 1
WHERE NOTIFICATION_ID=l_nid;
select to_number(profile_option_value) INTO l_server_tz from fnd_profile_option_values where profile_option_id in
(select profile_option_id from fnd_profile_options where profile_option_name in ('SERVER_TIMEZONE_ID'))
and level_id=10001;
select next_day(add_months('1-JAN'||to_char(sysdate,'YYYY'),begin_dst_month-1) + ((begin_dst_week_of_month-1)*7)-1,
decode(begin_dst_day_of_week, 1,'SUNDAY',2,'MONDAY',3,'TUESDAY',4,'WEDNESDAY',5,'THURSDAY',6,'FRIDAY',7,'SATURDAY')) + (begin_dst_hour/24) DST_BEGIN_DATE,
next_day(add_months('1-JAN'||to_char(sysdate,'YYYY'),end_dst_month-1) + ((end_dst_week_of_month-1)*7)-1,
decode(end_dst_day_of_week, 1,'SUNDAY',2,'MONDAY',3,'TUESDAY',4,'WEDNESDAY',5,'THURSDAY',6,'FRIDAY',7,'SATURDAY')) + (end_dst_hour/24) DST_END_DATE,
GMT_DEVIATION_HOURS,DST_INCREMENT
into l_dst_begin,l_dst_end,l_deviation,l_increment
from jtm_hz_timezones_b where timezone_id=l_server_tz;
select nfn.deferred_tran_id,nfn.sequence, nfn.client_id, DECODE(NVL(usr.multi_platform,'N'),'Y','WEBKIT-Based',NVL(usr.cookie,'WINCE')) as DEVICE_TYPE,
wfrl.display_name, nfn.object_name, pi.primary_key_column, nfn.object_id, nfn.error_msg,nfn.creation_date
from csm_deferred_nfn_info nfn , asg_pub_item pi, asg_user usr, wf_roles wfrl
where tracking_id=b_id
and wfrl.name=nfn.client_id
and nfn.object_name=pi.item_id
and usr.user_name=nfn.client_id;
SELECT APP_ID,RESPONSIBILITY_ID
FROM ASG_USER
WHERE USER_NAME=b_user_name;
SELECT fnd_profile.value_specific('CSM_NOTIFY_DEFERRED',NULL,l_resp_id,l_app_id)
INTO l_user_string FROM DUAL;
select decode(p_parent_pubitem,'CSM_INCIDENTS_ALL','SR','X')
INTO l_code from dual;
select decode(p_parent_pubitem,'CSM_INCIDENTS_ALL','SR','CSM_TASKS','TASK',
'CSM_DEBRIEF_HEADERS', 'SD','X') INTO l_code from dual;
select decode(p_parent_pubitem,'CSM_INCIDENTS_ALL','CS_INCIDENTS','CSM_TASKS','JTF_TASKS_B',
'CSM_DEBRIEF_HEADERS', 'CSF_DEBRIEF_HEADERS','X') INTO l_code from dual;
SELECT lookup_name
FROM CSM_ERROR_NFN_LOOKUPS
WHERE LOOKUP_TYPE='RELATIONSHIP'
AND LOOKUP_CODE='PARENT_OF'
AND LOOKUP_VALUE=b_child;
SELECT APP_ID,RESPONSIBILITY_ID
FROM ASG_USER
WHERE USER_NAME=b_user_name;
SELECT tracking_id INTO l_tracking_id
FROM CSM_DEFERRED_NFN_INFO
WHERE CLIENT_ID=p_user_name
AND DEFERRED_TRAN_ID=p_tranid
AND DML=p_dml_type
AND SEQUENCE=p_sequence;
SELECT primary_key_column INTO l_pk_col
FROM ASG_PUB_ITEM
WHERE item_id=p_pubitem;
l_sql:= 'SELECT TRACKING_ID FROM CSM_DEFERRED_NFN_INFO WHERE OBJECT_NAME='''||l_parent_pi ||''''
||' AND OBJECT_ID=(SELECT '||get_source_object_column(l_parent_pi,p_pubitem)|| ' FROM '
||p_pubitem||'_INQ WHERE '||l_pk_clause||' '||get_source_object_code_str(l_parent_pi,p_pubitem) ||') '
||' AND DEFERRED_TRAN_ID ='||p_tranid ||' AND CLIENT_ID='''||p_user_name||'''';
select CSM_DEFERRED_NFN_INFO_S.nextval into l_tracking_id from dual;
select STATUS INTO l_autosync_enabled from wf_events
where name='oracle.apps.csm.download.startsync';
l_mode := 'INSERT';
l_mode := 'UPDATE';
l_mode := 'DELETE';
-- insert into auto sync table
INSERT INTO csm_auto_sync_nfn(USER_ID,NOTIFICATION_ID,OBJECT_NAME,OBJECT_ID,DML,REMINDERS_SENT,CREATION_DATE,CREATED_BY
,LAST_UPDATE_DATE,LAST_UPDATED_BY)
VALUES(asg_base.get_user_id(p_user_name),l_notification_id,'DEFERRED_TRANSACTION',l_tracking_id,'NEW',0,sysdate,1,sysdate,1);
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_AUTO_SYNC_NFN')
,P_ACC_TABLE_NAME => 'CSM_AUTO_SYNC_NFN_ACC'
,P_SEQ_NAME => 'CSM_AUTO_SYNC_NFN_ACC_S'
,P_PK1_NAME => 'NOTIFICATION_ID'
,P_PK1_NUM_VALUE => l_notification_id
,P_USER_ID => asg_base.get_user_id(p_user_name)
);
INSERT INTO CSM_DEFERRED_NFN_INFO(TRACKING_ID,CLIENT_ID,NOTIFICATION_ID,OBJECT_NAME , OBJECT_ID,
DEFERRED_TRAN_ID , SEQUENCE, DML , PARENT_ID , ERROR_MSG ,CREATION_DATE )
VALUES(l_tracking_id,p_user_name,l_notification_id,p_pubitem,p_pk,p_tranid,p_sequence,
p_dml_type,l_parent_tracking_id,p_error_msg,sysdate);
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_DEFERRED_TRANSACTIONS')
,P_ACC_TABLE_NAME => 'CSM_DEFERRED_TRANSACTIONS_ACC'
,P_SEQ_NAME => 'CSM_DEFERRED_TXNS_ACC_S'
,P_PK1_NAME => 'TRACKING_ID'
,P_PK1_NUM_VALUE => l_tracking_id
,P_USER_ID => asg_base.get_user_id(p_user_name)
);
CSM_UTIL_PKG.LOG('Inserted Tracking Id - '||l_tracking_id,'CSM_NOTIFICATION_EVENT_PKG.notify_deferred',FND_LOG.LEVEL_PROCEDURE);
delete from csm_auto_sync_nfn_acc where user_id=p_user_id;
delete from csm_deferred_transactions_acc where user_id=p_user_id;
delete from csm_deferred_nfn_info where client_id=csm_util_pkg.get_user_name(p_user_id);
for rec in (select nfn.notification_id from csm_auto_sync_nfn nfn, wf_notifications wfn
where nfn.user_id=p_user_id and nfn.notification_id = wfn.notification_id
and wfn.status='OPEN')
loop
wf_notification.respond(rec.notification_id);
DELETE FROM csm_client_nfn_log cl
WHERE EXISTS (SELECT 1 FROM csm_auto_sync_nfn b WHERE b.NOTIFICATION_ID=cl.NOTIFICATION_ID and user_id=p_user_id);
delete from csm_auto_sync_nfn where user_id=p_user_id;
delete from CSM_SYNC_ERROR_NFN_INFO where client_id=csm_util_pkg.get_user_name(p_user_id);
SELECT APP_ID,RESPONSIBILITY_ID
FROM ASG_USER
WHERE USER_NAME=b_user_name;
l_sql:= 'select upper(device_name) from (select a.NAME as DEVICE_NAME '
||' from '||asg_base.G_OLITE_SCHEMA||'.dm$all_devices a, '||asg_base.G_OLITE_SCHEMA||'.dm$user_device b, '||asg_base.G_OLITE_SCHEMA||'.users c '
||' where a.ID=b.DEVICE_ID and b.USER_ID=c.id and c.DISPLAY_NAME =:1 order by A.ACCESS_TIME desc ) where rownum < 2';
SELECT fnd_profile.value_specific('CSM_NFN_SYNC_ERROR',NULL,l_resp_id,l_app_id)
INTO l_user_string FROM DUAL;
INSERT INTO CSM_SYNC_ERROR_NFN_INFO(NOTIFICATION_ID,RECIPIENT_NAME, SYNC_SESSION_ID ,CLIENT_ID)
VALUES(l_notification_id,l_user_list(I),l_session_id,l_client_id);
SELECT APP_ID,RESPONSIBILITY_ID
FROM ASG_USER
WHERE USER_NAME=b_user_name;
SELECT NVL(LAST_RUN_DATE,TO_DATE(1,'J')) INTO l_last_run_date
FROM jtm_con_request_data
WHERE product_code = 'CSM'
AND package_name = 'CSM_NOTIFICATION_EVENT_PKG'
AND procedure_name = 'EMAIL_SYNC_ERRORS_CONC';
OPEN cur FOR 'SELECT SESSION_ID,client_id,DECODE(DEVICE_PLATFORM,''WCE'',''WINCE'',''LAPTOP'') as DEVICE_TYPE,
dbms_lob.substr(message,1990,1)||''...'' as ERROR_MSG, START_TIME AS SYNC_DATE
FROM '||asg_base.G_OLITE_SCHEMA||'.c$sync_history HIST , ASG_USER au
WHERE RESULT<>''SUCCESS''
AND trim(CLIENT_ID) IS NOT NULL
AND CLIENT_ID = AU.USER_NAME
AND START_TIME > AU.CREATION_DATE
AND START_TIME > :1
AND NOT EXISTS(SELECT 1 FROM CSM_SYNC_ERROR_NFN_INFO
WHERE SYNC_SESSION_ID=HIST.SESSION_ID) ORDER BY CLIENT_ID,SESSION_ID' USING l_last_run_date;
l_sql:= 'select upper(device_name) from (select a.NAME as DEVICE_NAME '
||' from '||asg_base.G_OLITE_SCHEMA||'.dm$all_devices a, '||asg_base.G_OLITE_SCHEMA||'.dm$user_device b, '||asg_base.G_OLITE_SCHEMA||'.users c '
||' where a.ID=b.DEVICE_ID and b.USER_ID=c.id and c.DISPLAY_NAME =:1 order by A.ACCESS_TIME desc ) where rownum < 2';
SELECT fnd_profile.value_specific('CSM_NFN_SYNC_ERROR',NULL,l_resp_id,l_app_id)
INTO l_user_string FROM DUAL;
l_user_list.DELETE;
INSERT INTO CSM_SYNC_ERROR_NFN_INFO(NOTIFICATION_ID,RECIPIENT_NAME, SYNC_SESSION_ID ,CLIENT_ID)
VALUES(l_notification_id,l_user_list(I),l_session_id,l_client_id);
UPDATE jtm_con_request_data
SET LAST_RUN_DATE=sysdate
WHERE product_code = 'CSM'
AND package_name = 'CSM_NOTIFICATION_EVENT_PKG'
AND procedure_name = 'EMAIL_SYNC_ERRORS_CONC';