[Home] [Help]
PACKAGE BODY: APPS.CSM_NOTIFICATION_EVENT_PKG
Source
1 PACKAGE BODY CSM_NOTIFICATION_EVENT_PKG AS
2 /* $Header: csmentfb.pls 120.31.12020000.3 2013/04/09 10:57:22 saradhak ship $ */
3
4 g_notification_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_NOTIFICATIONS_ACC';
5 g_notification_table_name CONSTANT VARCHAR2(30) := 'WF_NOTIFICATIONS';
6 g_notification_seq_name CONSTANT VARCHAR2(30) := 'CSM_NOTIFICATIONS_ACC_S';
7 g_notification_pk1_name CONSTANT VARCHAR2(30) := 'NOTIFICATION_ID';
8 g_notification_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
9 CSM_ACC_PKG.t_publication_item_list('CSF_M_MAIL_MESSAGES', 'CSF_M_MAIL_RECIPIENTS');
10 g_switch_optimize_off BOOLEAN:=TRUE;
11 g_ItemType Varchar2(10) := 'CSM_MSGS';
12
13 --to DOWNLOAD ALL NON_MFS NFN TO HTML5 USERS
14 PROCEDURE DOWNLOAD_MULTIPLAT_NFN(p_status OUT NOCOPY VARCHAR2,p_message OUT NOCOPY VARCHAR2)
15 IS
16 PRAGMA AUTONOMOUS_TRANSACTION;
17 CURSOR l_last_run_date_csr
18 IS
19 SELECT MAX(DT) FROM
20 (SELECT nvl(last_run_date, (sysdate - 365*50)) as DT FROM jtm_con_request_data
21 WHERE package_name = 'CSM_NOTIFICATION_EVENT_PKG'
22 AND procedure_name = 'DOWNLOAD_MULTIPLAT_NFN'
23 UNION ALL
24 select sysdate - fnd_profile.value('CSM_PURGE_INTERVAL') as DT from dual);
25
26 l_run_date date;
27 l_upd_date date;
28 l_sqlerrno varchar2(20);
29 l_sqlerrmsg varchar2(4000);
30 BEGIN
31 l_run_date := SYSDATE;
32
33 -- get last conc program update date
34 OPEN l_last_run_date_csr;
35 FETCH l_last_run_date_csr INTO l_upd_date;
36 CLOSE l_last_run_date_csr;
37
38 CSM_UTIL_PKG.LOG('Entered DOWNLOAD_MULTIPLAT_NFN', 'CSM_NOTIFICATION_EVENT_PKG.DOWNLOAD_MULTIPLAT_NFN', FND_LOG.LEVEL_PROCEDURE);
39 -- set the program update date in jtm_con_request_data to sysdate
40
41 FOR l_notf_rec IN ( SELECT DISTINCT wfn.notification_id ,au.USER_ID
42 FROM wf_notifications wfn,asg_user au
43 WHERE au.user_name IN (FROM_ROLE,RECIPIENT_ROLE)
44 AND au.enabled='Y' and au.multi_platform='Y'
45 AND wfn.STATUS='OPEN'
46 AND wfn.MESSAGE_TYPE='CSM_MSGS'
47 AND nvl(wfn.begin_date, sysdate) >= l_upd_date
48 AND NOT EXISTS(SELECT 1 FROM CSM_NOTIFICATIONS_ACC ACC
49 WHERE ACC.NOTIFICATION_ID = WFN.NOTIFICATION_ID
50 AND ACC.USER_ID = AU.USER_ID))
51 LOOP
52 INSERT_NOTIFICATIONS_ACC (l_notf_rec.notification_id, l_notf_rec.user_id);
53 END LOOP;
54
55 UPDATE jtm_con_request_data
56 SET last_run_date = l_run_date
57 WHERE package_name = 'CSM_NOTIFICATION_EVENT_PKG'
58 AND procedure_name = 'DOWNLOAD_MULTIPLAT_NFN';
59
60 COMMIT;
61
62 p_status := 'FINE';
63 p_message := 'CSM_NOTIFICATION_EVENT_PKG.DOWNLOAD_MULTIPLAT_NFN Executed successfully';
64 CSM_UTIL_PKG.LOG('Leaving DOWNLOAD_MULTIPLAT_NFN', 'CSM_NOTIFICATION_EVENT_PKG.DOWNLOAD_MULTIPLAT_NFN', FND_LOG.LEVEL_PROCEDURE);
65
66 EXCEPTION
67 WHEN others THEN
68 l_sqlerrno := to_char(SQLCODE);
69 l_sqlerrmsg := substr(SQLERRM, 1,3000);
70 p_status := 'ERROR';
71 p_message := 'Error in CSM_NOTIFICATION_EVENT_PKG.DOWNLOAD_MULTIPLAT_NFN: ' || l_sqlerrno || ':' || l_sqlerrmsg;
72 CSM_UTIL_PKG.LOG('Exception: '||p_message, 'CSM_NOTIFICATION_EVENT_PKG.DOWNLOAD_MULTIPLAT_NFN', FND_LOG.LEVEL_PROCEDURE);
73 ROLLBACK;
74 fnd_file.put_line(fnd_file.log, 'CSM_NOTIFICATION_EVENT_PKG.DOWNLOAD_MULTIPLAT_NFN ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
75 END DOWNLOAD_MULTIPLAT_NFN;
76
77 FUNCTION check_if_notification_exists(p_notification_id IN number, p_user_id IN number)
78 RETURN boolean
79 IS
80 l_dummy number;
81
82 CURSOR l_csm_notifications_csr(b_notificationid IN number, p_userid IN number)
83 IS
84 SELECT 1
85 FROM csm_notifications_acc
86 WHERE notification_id = b_notificationid
87 AND user_id = p_userid;
88
89 BEGIN
90 OPEN l_csm_notifications_csr(p_notification_id, p_user_id);
91 FETCH l_csm_notifications_csr INTO l_dummy;
92 IF l_csm_notifications_csr%FOUND THEN
93 CLOSE l_csm_notifications_csr;
94 RETURN TRUE;
95 ELSE
96 CLOSE l_csm_notifications_csr;
97 RETURN FALSE;
98 END IF;
99
100 END check_if_notification_exists;
101
102 --Bug 5337816
103 PROCEDURE INSERT_NOTIFICATIONS_ACC (p_notification_id wf_notifications.notification_id%TYPE,
104 p_user_id fnd_user.user_id%TYPE)
105 IS
106 l_sysdate DATE;
107 l_count NUMBER;
108 BEGIN
109 CSM_ACC_PKG.Insert_Acc
110 ( P_PUBLICATION_ITEM_NAMES => g_notification_pubi_name
111 ,P_ACC_TABLE_NAME => g_notification_acc_table_name
112 ,P_SEQ_NAME => g_notification_seq_name
113 ,P_PK1_NAME => g_notification_pk1_name
114 ,P_PK1_NUM_VALUE => p_notification_id
115 ,P_USER_ID => p_user_id
116 );
117 EXCEPTION WHEN OTHERS THEN
118 CSM_UTIL_PKG.LOG( 'Exception occurred in CSM_NOTIFICATION_EVENT_PKG.INSERT_NOTIFICATIONS_ACC: '
119 || sqlerrm|| ' for PK ' || to_char(p_notification_id),
120 'CSM_NOTIFICATION_EVENT_PKG.INSERT_NOTIFICATIONS_ACC',FND_LOG.LEVEL_EXCEPTION);
121 RAISE;
122 END INSERT_NOTIFICATIONS_ACC;-- end INSERT_NOTIFICATIONS_ACC
123
124 --Bug 5337816
125 PROCEDURE NOTIFICATIONS_ACC_PROCESSOR(p_user_id IN NUMBER)
126 IS
127 l_sqlerrno VARCHAR2(20);
128 l_sqlerrmsg VARCHAR2(4000);
129 l_error_msg VARCHAR2(4000);
130 l_return_status VARCHAR2(2000);
131
132 -- get all notifications in which user is a recipient
133 CURSOR c_notf(b_user_id fnd_user.user_id%TYPE) IS
134 SELECT DISTINCT wfn.notification_id
135 FROM wf_notifications wfn,
136 asg_user au
137 WHERE au.user_id=b_user_id
138 AND au.user_name IN (FROM_ROLE,RECIPIENT_ROLE)
139 AND wfn.STATUS='OPEN'
140 AND wfn.MESSAGE_TYPE='CSM_MSGS'
141 AND (nvl(wfn.begin_date, sysdate) between
142 (sysdate - csm_profile_pkg.get_task_history_days(b_user_id))and sysdate)
143 AND NOT EXISTS(SELECT 1
144 FROM CSM_NOTIFICATIONS_ACC ACC
145 WHERE ACC.NOTIFICATION_ID = WFN.NOTIFICATION_ID
146 AND ACC.USER_ID = AU.USER_ID);
147
148 --12.1
149 CURSOR c_broadcast_notf(b_user_id fnd_user.user_id%TYPE) IS
150 SELECT DISTINCT wfn.notification_id
151 FROM WF_NOTIFICATIONS wfn,
152 ASG_USER au
153 WHERE au.user_id=b_user_id
154 AND wfn.STATUS='OPEN'
155 AND wfn.MESSAGE_TYPE='CSM_MSGS'
156 AND RECIPIENT_ROLE LIKE 'JRES_GRP:%'
157 AND au.user_id =CSM_UTIL_PKG.get_group_owner(substr(WFN.RECIPIENT_ROLE,instr(WFN.RECIPIENT_ROLE,':')+1))
158 AND au.enabled='Y'
159 AND (nvl(wfn.begin_date, sysdate) between
160 (sysdate - csm_profile_pkg.get_task_history_days(b_user_id))and sysdate)
161 AND NOT EXISTS(SELECT 1
162 FROM CSM_NOTIFICATIONS_ACC ACC
163 WHERE ACC.NOTIFICATION_ID = WFN.NOTIFICATION_ID
164 AND ACC.USER_ID = AU.USER_ID);
165
166 BEGIN
167 CSM_UTIL_PKG.LOG('Entering NOTIFICATIONS_ACC_PROCESSOR for user_id: ' || p_user_id,
168 'CSM_NOTIFICATION_EVENT_PKG.NOTIFICATIONS_ACC_PROCESSOR',FND_LOG.LEVEL_PROCEDURE);
169
170
171 -- get all notifications in which user is a recipient
172 FOR l_notf_rec IN c_notf(p_user_id)
173 LOOP
174 INSERT_NOTIFICATIONS_ACC (l_notf_rec.notification_id, p_user_id);
175 END LOOP;
176
177 --12.1
178 FOR l_notf_rec IN c_broadcast_notf(p_user_id)
179 LOOP
180 INSERT_NOTIFICATIONS_ACC (l_notf_rec.notification_id, p_user_id);
181 END LOOP;
182
183 CSM_UTIL_PKG.LOG('Leaving NOTIFICATIONS_ACC_PROCESSOR for user_id: ' || p_user_id,
184 'CSM_NOTIFICATION_EVENT_PKG.NOTIFICATIONS_ACC_PROCESSOR',FND_LOG.LEVEL_PROCEDURE);
185 EXCEPTION
186 WHEN OTHERS THEN
187 l_sqlerrno := to_char(SQLCODE);
188 l_sqlerrmsg := substr(SQLERRM, 1,2000);
189 l_error_msg := ' Exception in NOTIFICATIONS_ACC_PROCESSOR for for user_id: ' || p_user_id
190 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
191 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_NOTIFICATION_EVENT_PKG.NOTIFICATIONS_ACC_PROCESSOR',FND_LOG.LEVEL_EXCEPTION);
192 RAISE;
193 END NOTIFICATIONS_ACC_PROCESSOR;
194
195 --Bug 5337816
196 PROCEDURE DOWNLOAD_NOTIFICATION(p_notification_id IN NUMBER ,x_return_status OUT NOCOPY VARCHAR2)
197 IS
198 CURSOR c_users(b_nid NUMBER) IS
199 SELECT DISTINCT au.user_id
200 FROM WF_NOTIFICATIONS wfn,
201 ASG_USER au
202 WHERE wfn.NOTIFICATION_ID=b_nid
203 AND wfn.STATUS='OPEN'
204 AND wfn.MESSAGE_TYPE='CSM_MSGS'
205 AND au.user_name IN (WFN.FROM_ROLE,WFN.RECIPIENT_ROLE)
206 AND au.enabled='Y'
207 AND (nvl(wfn.begin_date, sysdate) between
208 (sysdate - csm_profile_pkg.get_task_history_days(au.user_id))and sysdate)
209 AND NOT EXISTS(SELECT 1
210 FROM CSM_NOTIFICATIONS_ACC ACC
211 WHERE ACC.NOTIFICATION_ID = WFN.NOTIFICATION_ID
212 AND ACC.USER_ID = AU.USER_ID);
213
214 --12.1
215 CURSOR c_broadcast_users(b_nid NUMBER) IS
216 SELECT DISTINCT au.user_id
217 FROM WF_NOTIFICATIONS wfn,
218 ASG_USER au
219 WHERE wfn.NOTIFICATION_ID=b_nid
220 AND wfn.STATUS='OPEN'
221 AND wfn.MESSAGE_TYPE='CSM_MSGS'
222 AND RECIPIENT_ROLE LIKE 'JRES_GRP:%'
223 AND au.user_id =CSM_UTIL_PKG.get_group_owner(substr(WFN.RECIPIENT_ROLE,instr(WFN.RECIPIENT_ROLE,':')+1))
224 AND au.enabled='Y'
225 AND (nvl(wfn.begin_date, sysdate) between
226 (sysdate - csm_profile_pkg.get_task_history_days(au.user_id))and sysdate)
227 AND NOT EXISTS(SELECT 1
228 FROM CSM_NOTIFICATIONS_ACC ACC
229 WHERE ACC.NOTIFICATION_ID = WFN.NOTIFICATION_ID
230 AND ACC.USER_ID = AU.USER_ID);
231
232 l_sqlerrno VARCHAR2(20);
233 l_sqlerrmsg VARCHAR2(4000);
234 l_error_msg VARCHAR2(4000);
235
236 BEGIN
237
238 CSM_UTIL_PKG.LOG('Entering DOWNLOAD_NOTIFICATION for notification_id: ' || p_notification_id,
239 'CSM_NOTIFICATION_EVENT_PKG.DOWNLOAD_NOTIFICATION',FND_LOG.LEVEL_PROCEDURE);
240
241 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
242 x_return_status :='SUCCESS';
243 RETURN;
244 END IF;
245
246 FOR r_rec IN c_users(p_notification_id)
247 LOOP
248 INSERT_NOTIFICATIONS_ACC (p_notification_id, r_rec.user_id);
249 END LOOP;
250
251 --12.1
252 FOR r_rec IN c_broadcast_users(p_notification_id)
253 LOOP
254 INSERT_NOTIFICATIONS_ACC (p_notification_id, r_rec.user_id);
255 END LOOP;
256
257 CSM_UTIL_PKG.LOG('Leaving DOWNLOAD_NOTIFICATION for notification_id: ' || p_notification_id,
258 'CSM_NOTIFICATION_EVENT_PKG.DOWNLOAD_NOTIFICATION',FND_LOG.LEVEL_PROCEDURE);
259 x_return_status :='SUCCESS';
260 EXCEPTION
261 WHEN OTHERS THEN
262 l_sqlerrno := TO_CHAR(SQLCODE);
263 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
264 l_error_msg := ' Exception in DOWNLOAD_NOTIFICATION for notification_id:' || to_char(p_notification_id)
265 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
266 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_NOTIFICATION_EVENT_PKG.DOWNLOAD_NOTIFICATION',FND_LOG.LEVEL_EXCEPTION);
267 x_return_status :='ERROR';
268 END DOWNLOAD_NOTIFICATION;
269
270
271 --Bug 5337816
272 -- subscription to the NOTIFICATION_ATTR_INS_RECEIVE WF event
273 FUNCTION NOTIFICATION_ATTR_WF_EVENT_SUB(p_subscription_guid IN RAW, p_event IN OUT NOCOPY WF_EVENT_T)
274 RETURN VARCHAR2
275 IS
276 l_sqlerrno VARCHAR2(20);
277 l_sqlerrmsg VARCHAR2(4000);
278 l_error_msg VARCHAR2(4000);
279 l_return_status VARCHAR2(80);
280
281 l_notification_id wf_notifications.notification_id%TYPE;
282
283 BEGIN
284 CSM_UTIL_PKG.LOG('Entering NOTIFICATION_ATTR_WF_EVENT_SUB',
285 'CSM_NOTIFICATION_EVENT_PKG.NOTIFICATION_ATTR_WF_EVENT_SUB',FND_LOG.LEVEL_PROCEDURE);
286
287 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
288 RETURN 'SUCCESS';
289 END IF;
290
291 l_notification_id := p_event.GetValueForParameter('NOTIFICATION_ID');
292
293 DOWNLOAD_NOTIFICATION(l_notification_id,l_return_status);
294
295 CSM_UTIL_PKG.LOG('Leaving NOTIFICATION_ATTR_WF_EVENT_SUB for notification_id: ' || TO_CHAR(l_notification_id),
296 'CSM_NOTIFICATION_EVENT_PKG.NOTIFICATION_ATTR_WF_EVENT_SUB',FND_LOG.LEVEL_PROCEDURE);
297
298 RETURN l_return_status;
299 EXCEPTION
300 WHEN OTHERS THEN
301 l_sqlerrno := TO_CHAR(SQLCODE);
302 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
303 l_error_msg := ' Exception in NOTIFICATION_ATTR_WF_EVENT_SUB for notification_id:' || to_char(l_notification_id)
304 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
305 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_NOTIFICATION_EVENT_PKG.NOTIFICATION_ATTR_WF_EVENT_SUB',FND_LOG.LEVEL_EXCEPTION);
306 RETURN 'ERROR';
307 END NOTIFICATION_ATTR_WF_EVENT_SUB;
308
309 --Bug 5337816
310 FUNCTION NOTIFICATION_DEL_WF_EVENT_SUB(p_subscription_guid IN RAW, p_event IN OUT NOCOPY WF_EVENT_T)
311 RETURN VARCHAR2
312 IS
313 l_sqlerrno VARCHAR2(20);
314 l_sqlerrmsg VARCHAR2(4000);
315 l_error_msg VARCHAR2(4000);
316 l_return_status VARCHAR2(2000);
317
318 l_notification_id wf_notifications.notification_id%TYPE;
319
320 -- get sender/recipient for this notification
321 CURSOR c_users(b_nid NUMBER) IS
322 SELECT acc.user_id
323 FROM CSM_NOTIFICATIONS_ACC acc
324 WHERE acc.NOTIFICATION_ID=b_nid;
325
326
327 BEGIN
328 CSM_UTIL_PKG.LOG('Entering NOTIFICATION_DEL_WF_EVENT_SUB',
329 'CSM_NOTIFICATION_EVENT_PKG.NOTIFICATION_DEL_WF_EVENT_SUB',FND_LOG.LEVEL_PROCEDURE);
330
331 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
332 RETURN 'SUCCESS';
333 END IF;
334
335 l_notification_id := p_event.GetValueForParameter('NOTIFICATION_ID');
336
337 FOR r_notification_rec IN c_users(l_notification_id) LOOP
338 CSM_ACC_PKG.Delete_Acc
339 ( P_PUBLICATION_ITEM_NAMES => g_notification_pubi_name
340 ,P_ACC_TABLE_NAME => g_notification_acc_table_name
341 ,P_PK1_NAME => g_notification_pk1_name
342 ,P_PK1_NUM_VALUE => l_notification_id
343 ,P_USER_ID => r_notification_rec.user_id
344 );
345 END LOOP;
346
347 CSM_UTIL_PKG.LOG('Leaving NOTIFICATION_DEL_WF_EVENT_SUB for notification_id: ' || TO_CHAR(l_notification_id),
348 'CSM_NOTIFICATION_EVENT_PKG.NOTIFICATION_DEL_WF_EVENT_SUB',FND_LOG.LEVEL_PROCEDURE);
349
350 RETURN 'SUCCESS';
351 EXCEPTION
352 WHEN OTHERS THEN
353 l_sqlerrno := TO_CHAR(SQLCODE);
354 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
355 l_error_msg := ' Exception in NOTIFICATION_DEL_WF_EVENT_SUB for notification_id:' || to_char(l_notification_id)
356 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
357 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_NOTIFICATION_EVENT_PKG.NOTIFICATION_DEL_WF_EVENT_SUB',FND_LOG.LEVEL_EXCEPTION);
358 RETURN 'ERROR';
359 END NOTIFICATION_DEL_WF_EVENT_SUB;
360
361 PROCEDURE PURGE_NOTIFICATION_CONC(p_status OUT NOCOPY VARCHAR2, p_message OUT NOCOPY VARCHAR2)
362 IS
363 PRAGMA AUTONOMOUS_TRANSACTION;
364 l_sqlerrno VARCHAR2(20);
365 l_sqlerrmsg VARCHAR2(4000);
366 l_error_msg VARCHAR2(4000);
367 l_return_status VARCHAR2(2000);
368 l_last_run_date DATE;
369
370 l_notification_id NUMBER;
371 l_user_id NUMBER;
372 l_dummy NUMBER;
373
374 CURSOR l_upd_last_run_date_csr
375 IS
376 SELECT 1
377 FROM jtm_con_request_data
378 WHERE product_code = 'CSM'
379 AND package_name = 'CSM_NOTIFICATION_EVENT_PKG'
380 AND procedure_name = 'PURGE_NOTIFICATION_CONC'
381 FOR UPDATE OF last_run_date NOWAIT;
382
383 CURSOR c_purge_days IS
384 select profile_option_value from fnd_profile_option_values where profile_option_id in
385 (select profile_option_id from fnd_profile_options where profile_option_name='CSM_PURGE_INTERVAL')
386 and level_id=10001;
387
388 l_days NUMBER;
389
390 BEGIN
391 l_last_run_date := SYSDATE;
392
393 FOR rec IN ( SELECT acc.user_id,acc.notification_id,wfn.status
394 FROM csm_notifications_acc acc,wf_notifications wfn
395 WHERE acc.notification_id = wfn.notification_id
396 AND (
397 (wfn.RECIPIENT_ROLE NOT LIKE 'JRES_GRP:%')
398 OR
399 (wfn.status='CLOSED') --skip OPEN grp messages, let user delete/close it
400 )
401 AND (wfn.status='CLOSED' OR NVL(wfn.begin_date, SYSDATE) < (SYSDATE - csm_profile_pkg.get_task_history_days(acc.user_id))))
402 LOOP
403
404 IF rec.status= 'OPEN' THEN
405 begin
406 wf_notification.respond(rec.notification_id);
407 exception
408 when others then
409 null;
410 end;
411 END IF;
412
413 CSM_ACC_PKG.Delete_Acc
414 ( P_PUBLICATION_ITEM_NAMES => g_notification_pubi_name
415 ,P_ACC_TABLE_NAME => g_notification_acc_table_name
416 ,P_PK1_NAME => g_notification_pk1_name
417 ,P_PK1_NUM_VALUE => rec.notification_id
418 ,P_USER_ID => rec.user_id
419 );
420 END LOOP;
421
422 COMMIT;
423
424 /*12.1.2 PURGE INACTIVE CSM WF ROLES*/
425 DELETE FROM WF_LOCAL_ROLES B WHERE NAME LIKE 'CSM%ROLE'
426 AND NOT EXISTS( SELECT 1 FROM FND_USER U
427 WHERE USER_NAME=substr(B.NAME,5,length(B.NAME)-9)
428 AND sysdate between nvl(start_date,sysdate-1) and nvl(end_date,sysdate+1));
429
430
431 /*12.1.2 PURGE AUTO SYNC Notifications
432 Step-1 : Purge all CSM_AUTO_SYNC_NFN/CLIENT notifications that are created earlier than
433 purge interval
434 Step-2 : Close unresponded notifications that are older than purge interval
435 These records will get purged from WF Notifications table
436 after another purge interval has elapsed
437 Step-3 : Purge all WF notifications that are responded/closed
438 with end_date earlier than purge interval
439 */
440 OPEN c_purge_days;
441 FETCH c_purge_days INTO l_days;
442 IF c_purge_days%FOUND AND l_days IS NOT NULL AND l_days>0 THEN
443
444 --STEP-1
445
446 FOR nfn_rec IN (SELECT NOTIFICATION_ID,USER_ID FROM csm_auto_sync_nfn
447 WHERE CREATION_DATE < SYSDATE-l_days)
448 LOOP
449 CSM_ACC_PKG.Delete_Acc
450 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_AUTO_SYNC_NFN')
451 ,P_ACC_TABLE_NAME => 'CSM_AUTO_SYNC_NFN_ACC'
452 ,P_PK1_NAME => 'NOTIFICATION_ID'
453 ,P_PK1_NUM_VALUE => nfn_rec.NOTIFICATION_ID
454 ,P_USER_ID => nfn_rec.USER_ID
455 );
456 END LOOP;
457
458 DELETE FROM csm_auto_sync_nfn WHERE CREATION_DATE < SYSDATE-l_days;
459
460 FOR nfn_rec IN (SELECT NOTIFICATION_ID,USER_ID FROM csm_client_nfn_log_acc acc
461 WHERE NOT EXISTS (SELECT 1 FROM csm_auto_sync_nfn b
462 WHERE b.NOTIFICATION_ID=acc.NOTIFICATION_ID))
463 LOOP
464 CSM_ACC_PKG.Delete_Acc
465 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_CLIENT_NFN_LOG')
466 ,P_ACC_TABLE_NAME => 'CSM_CLIENT_NFN_LOG_ACC'
467 ,P_PK1_NAME => 'NOTIFICATION_ID'
468 ,P_PK1_NUM_VALUE => nfn_rec.NOTIFICATION_ID
469 ,P_USER_ID => nfn_rec.USER_ID
470 );
471 END LOOP;
472
473 DELETE FROM csm_client_nfn_log cl
474 WHERE NOT EXISTS (SELECT 1 FROM csm_auto_sync_nfn b WHERE b.NOTIFICATION_ID=cl.NOTIFICATION_ID);
475
476 COMMIT;
477
478 --STEP-2
479 FOR ntf_rec IN (SELECT NOTIFICATION_ID FROM WF_NOTIFICATIONS
480 WHERE MESSAGE_TYPE='CSM_MSGS'
481 AND (STATUS='OPEN' OR END_DATE IS NULL)
482 AND BEGIN_DATE < SYSDATE-l_days)
483 LOOP
484 wf_notification.respond(ntf_rec.NOTIFICATION_ID);
485 END LOOP;
486
487
488 --STEP-3
489 wf_purge.total('CSM_MSGS',null,sysdate-l_days-1);
490 END IF;
491 CLOSE c_purge_days;
492
493
494 -- update last_run_date
495 OPEN l_upd_last_run_date_csr;
496 FETCH l_upd_last_run_date_csr INTO l_dummy;
497 IF l_upd_last_run_date_csr%FOUND THEN
498 UPDATE jtm_con_request_data
499 SET last_run_date = l_last_run_date
500 WHERE CURRENT OF l_upd_last_run_date_csr;
501 END IF;
502 CLOSE l_upd_last_run_date_csr;
503 COMMIT;
504
505 p_status := 'SUCCESS';
506 p_message := 'CSM_NOTIFICATION_EVENT_PKG.PURGE_NOTIFICATION_CONC Executed successfully';
507
508 EXCEPTION
509 WHEN OTHERS THEN
510 l_sqlerrno := TO_CHAR(SQLCODE);
511 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
512 ROLLBACK;
513 l_error_msg := ' Exception in PURGE_NOTIFICATION_CONC for notification:' || to_char(l_notification_id)
514 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
515 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_NOTIFICATION_EVENT_PKG.PURGE_NOTIFICATION_CONC',FND_LOG.LEVEL_EVENT);
516 p_status := 'ERROR';
517 p_message := 'Error in CSM_NOTIFICATION_EVENT_PKG.PURGE_NOTIFICATION_CONC: ' || l_error_msg;
518 END PURGE_NOTIFICATION_CONC;
519
520 --Bug 9435049
521 FUNCTION get_Mailer_EmailId return VARCHAR2
522 IS
523 l_email_id VARCHAR2(500):=NULL;
524 l_status VARCHAR2(200);
525 l_dummy NUMBER;
526 l_comp_name VARCHAR2(1000);
527
528 CURSOR c_mailer_email IS
529 select a.component_name,parameter_value,component_status,
530 decode(component_status, 'RUNNING',0,'STARTING',1,'DEACTIVATED_SYSTEM',2,'STOPPED_ERROR',2) status
531 from fnd_svc_components a, FND_SVC_COMP_PARAM_VALS_V b
532 where component_type='WF_MAILER'
533 and a.component_id=b.component_id
534 and b.parameter_name='REPLYTO'
535 and b.parameter_value IS NOT NULL
536 order by status;
537
538 CURSOR c_seeded_mailer IS
539 select parameter_value
540 from FND_SVC_COMP_PARAM_VALS_V
541 where component_id=10006
542 and parameter_name='REPLYTO';
543
544 BEGIN
545
546 OPEN c_mailer_email;
547 FETCH c_mailer_email INTO l_comp_name,l_email_id,l_status,l_dummy;
548 CLOSE c_mailer_email;
549
550 CSM_UTIL_PKG.LOG('Mailer by name: '||l_comp_name || ' ,with email id: '||l_email_id ||' and status: '
551 || l_status, 'CSM_NOTIFICATION_EVENT_PKG.get_Mailer_EmailId',FND_LOG.LEVEL_PROCEDURE);
552
553 IF l_email_id IS NULL OR l_email_id ='' THEN
554 OPEN c_seeded_mailer;
555 FETCH c_seeded_mailer INTO l_email_id;
556 CLOSE c_seeded_mailer;
557
558 CSM_UTIL_PKG.LOG('Email Id of running mailer is null so trying with seeded Mailer''s email address ='||l_email_id,
559 'CSM_NOTIFICATION_EVENT_PKG.get_Mailer_EmailId',FND_LOG.LEVEL_PROCEDURE);
560
561 END IF;
562
563 RETURN l_email_id;
564
565 END get_Mailer_EmailId;
566
567 --Bug 9435049
568 FUNCTION invoke_WF_NotifyProcess (p_recipient_role IN VARCHAR2, p_wf_param IN wf_event_t) return NUMBER
569 IS
570
571 l_item_key WF_ITEMS.ITEM_KEY%TYPE;
572 l_item_owner WF_USERS.NAME%TYPE := 'SYSADMIN';
573 l_processName VARCHAR2(200);
574 l_template Varchar2(100);
575 l_subject VARCHAR2(200);
576 l_body VARCHAR2(4000);
577 l_tran_id NUMBER;
578 l_seq NUMBER;
579 l_client_id VARCHAR2(100);
580 l_dev_type VARCHAR2(100);
581 l_disp_name VARCHAR2(500);
582 l_pub_name VARCHAR2(100);
583 l_pk_name VARCHAR2(100);
584 l_pk_value VARCHAR2(100);
585 l_err_msg VARCHAR2(2000);
586 l_tran_date VARCHAR2(100);
587 l_session_id NUMBER;
588 l_dev_name VARCHAR2(240);
589 l_sync_date VARCHAR2(100);
590
591 l_nid NUMBER;
592 l_status VARCHAR2(200);
593 l_result VARCHAR2(2000);
594 BEGIN
595
596 l_template := p_wf_param.getValueForParameter('TEMPLATE'); --wf_msg_name
597
598 l_item_key := l_template||'<->'||to_char(systimestamp)||'<->'||p_recipient_role;
599
600 CSM_UTIL_PKG.LOG('Creating WF Process for recipient :'||p_recipient_role||' with item key:'||l_item_key,
601 'CSM_NOTIFICATION_EVENT_PKG.invoke_WF_NotifyProcess',FND_LOG.LEVEL_PROCEDURE);
602
603 IF l_template <> 'DOWNLOAD_INIT_MSG' THEN
604 l_processName := 'CSM_NFN_PROCESS';
605 ELSE
606 l_processName := 'CSM_AS_NFN_PROCESS';
607 END IF;
608
609 wf_engine.CreateProcess(
610 itemtype => g_ItemType,
611 itemkey => l_item_key,
612 process => l_processName);
613
614 wf_engine.SetItemUserKey(
615 itemtype => g_ItemType,
616 itemkey => l_item_key,
617 userkey => l_item_key);
618
619 wf_engine.SetItemOwner(
620 itemtype => g_ItemType,
621 itemkey => l_item_key,
622 owner => l_item_owner);
623
624 wf_engine.SetItemAttrText(
625 itemtype => g_ItemType,
626 itemkey => l_item_key,
627 aname => 'MESSAGE',
628 avalue => l_template);
629
630 if l_template = 'DOWNLOAD_INIT_MSG'
631 then
632
633 l_subject := p_wf_param.getValueForParameter('SUBJECT');
634 wf_engine.SetItemAttrText(
635 itemtype => g_ItemType,
636 itemkey => l_item_key,
637 aname => 'SUBJECT',
638 avalue => l_subject);
639
640 l_body := p_wf_param.getValueForParameter('MESSAGE_BODY');
641 wf_engine.SetItemAttrText(
642 itemtype => g_ItemType,
643 itemkey => l_item_key,
644 aname => 'MESSAGE_BODY',
645 avalue => l_body);
646
647 elsif l_template = 'FYI_MESSAGE'
648 then
649
650 l_subject := p_wf_param.getValueForParameter('SUBJECT');
651 wf_engine.SetItemAttrText(
652 itemtype => g_ItemType,
653 itemkey => l_item_key,
654 aname => 'SUBJECT',
655 avalue => l_subject);
656
657 l_body := p_wf_param.getValueForParameter('MESSAGE_BODY');
658 wf_engine.SetItemAttrText(
659 itemtype => g_ItemType,
660 itemkey => l_item_key,
661 aname => 'MESSAGE_BODY',
662 avalue => l_body);
663
664 l_client_id := p_wf_param.getValueForParameter('#FROM_ROLE');
665 wf_engine.SetItemAttrText(
666 itemtype => g_ItemType,
667 itemkey => l_item_key,
668 aname => '#FROM_ROLE',
669 avalue => l_client_id);
670
671 elsif l_template = 'DEFERRED_ERROR_REPORT'
672 then
673
674 l_client_id := p_wf_param.getValueForParameter('USER_NAME');
675 wf_engine.SetItemAttrText(
676 itemtype => g_ItemType,
677 itemkey => l_item_key,
678 aname => 'USER_NAME',
679 avalue => l_client_id);
680
681 l_tran_id := to_number(p_wf_param.getValueForParameter('TRAN_ID'));
682 wf_engine.SetItemAttrNumber(
683 itemtype => g_ItemType,
684 itemkey => l_item_key,
685 aname => 'TRAN_ID',
686 avalue => l_tran_id);
687
688 l_seq := to_number(p_wf_param.getValueForParameter('SEQUENCE'));
689 wf_engine.SetItemAttrNumber(
690 itemtype => g_ItemType,
691 itemkey => l_item_key,
692 aname => 'SEQUENCE',
693 avalue => l_seq);
694
695 l_dev_type := p_wf_param.getValueForParameter('DEVICE_TYPE');
696 wf_engine.SetItemAttrText(
697 itemtype => g_ItemType,
698 itemkey => l_item_key,
699 aname => 'DEVICE_TYPE',
700 avalue => l_dev_type);
701
702 l_disp_name := p_wf_param.getValueForParameter('EMP_NAME');
703 wf_engine.SetItemAttrText(
704 itemtype => g_ItemType,
705 itemkey => l_item_key,
706 aname => 'EMP_NAME',
707 avalue => l_disp_name);
708
709 l_pub_name := p_wf_param.getValueForParameter('PUB_ITEM');
710 wf_engine.SetItemAttrText(
711 itemtype => g_ItemType,
712 itemkey => l_item_key,
713 aname => 'PUB_ITEM',
714 avalue => l_pub_name);
715
716 l_pk_name := p_wf_param.getValueForParameter('PK_COLUMN');
717 wf_engine.SetItemAttrText(
718 itemtype => g_ItemType,
719 itemkey => l_item_key,
720 aname => 'PK_COLUMN',
721 avalue => l_pk_name);
722
723 l_pk_value := p_wf_param.getValueForParameter('PK_VALUE');
724 wf_engine.SetItemAttrText(
725 itemtype => g_ItemType,
726 itemkey => l_item_key,
727 aname => 'PK_VALUE',
728 avalue => l_pk_value);
729
730 l_err_msg := p_wf_param.getValueForParameter('ERR_MSG');
731 wf_engine.SetItemAttrText(
732 itemtype => g_ItemType,
733 itemkey => l_item_key,
734 aname => 'ERR_MSG',
735 avalue => l_err_msg);
736
737 l_tran_date := p_wf_param.getValueForParameter('TRAN_DATE');
738 wf_engine.SetItemAttrText(
739 itemtype => g_ItemType,
740 itemkey => l_item_key,
741 aname => 'TRAN_DATE',
742 avalue => l_tran_date);
743
744 elsif l_template = 'SYNC_ERROR_REPORT'
745 then
746
747 l_session_id := to_number(p_wf_param.getValueForParameter('SESSION_ID'));
748 wf_engine.SetItemAttrNumber(
749 itemtype => g_ItemType,
750 itemkey => l_item_key,
751 aname => 'SESSION_ID',
752 avalue => l_session_id);
753
754 l_tran_id := to_number(p_wf_param.getValueForParameter('TRAN_ID'));
755 wf_engine.SetItemAttrNumber(
756 itemtype => g_ItemType,
757 itemkey => l_item_key,
758 aname => 'TRAN_ID',
759 avalue => l_tran_id);
760
761 l_client_id := p_wf_param.getValueForParameter('USER_NAME');
762 wf_engine.SetItemAttrText(
763 itemtype => g_ItemType,
764 itemkey => l_item_key,
765 aname => 'USER_NAME',
766 avalue => l_client_id);
767
768 l_dev_type := p_wf_param.getValueForParameter('DEVICE_TYPE');
769 wf_engine.SetItemAttrText(
770 itemtype => g_ItemType,
771 itemkey => l_item_key,
772 aname => 'DEVICE_TYPE',
773 avalue => l_dev_type);
774
775 l_dev_name := p_wf_param.getValueForParameter('DEVICE_NAME');
776 wf_engine.SetItemAttrText(
777 itemtype => g_ItemType,
778 itemkey => l_item_key,
779 aname => 'DEVICE_NAME',
780 avalue => l_dev_name);
781
782 l_err_msg := p_wf_param.getValueForParameter('ERROR_MSG');
783 wf_engine.SetItemAttrText(
784 itemtype => g_ItemType,
785 itemkey => l_item_key,
786 aname => 'ERROR_MSG',
787 avalue => l_err_msg);
788
789 l_sync_date := p_wf_param.getValueForParameter('SYNC_DATE');
790 wf_engine.SetItemAttrText(
791 itemtype => g_ItemType,
792 itemkey => l_item_key,
793 aname => 'SYNC_DATE',
794 avalue => l_sync_date);
795
796 end if;
797
798 wf_engine.SetItemAttrText(
799 itemtype => g_ItemType,
800 itemkey => l_item_key,
801 aname => 'RECIPIENT',
802 avalue => upper(p_recipient_role));
803
804 wf_engine.StartProcess(
805 itemtype => g_ItemType,
806 itemkey => l_item_key);
807
808
809 BEGIN
810 SELECT NOTIFICATION_ID
811 INTO l_nid
812 FROM WF_NOTIFICATIONS
813 WHERE MESSAGE_TYPE='CSM_MSGS'
814 AND MESSAGE_NAME=l_template
815 AND RECIPIENT_ROLE=p_recipient_role
816 AND ITEM_KEY = l_item_key;
817 EXCEPTION
818 WHEN OTHERS THEN
819 wf_engine.itemStatus(g_ItemType,l_item_key,l_status,l_result);
820 CSM_UTIL_PKG.LOG('Error in WF Process with item key:'||l_item_key ||' . Failed with status '||l_status
821 ||' , and result: '||l_result,'CSM_NOTIFICATION_EVENT_PKG.invoke_WF_NotifyProcess',FND_LOG.LEVEL_PROCEDURE);
822 return -1;
823 END;
824
825 return l_nid;
826 END invoke_WF_NotifyProcess;
827
828 FUNCTION createMobileWFUser(b_user_name IN VARCHAR2) RETURN BOOLEAN
829 IS
830 role_name VARCHAR2(30);
831 role_display_name VARCHAR2(1000);
832 l_fnd_email VARCHAR2(200);
833 l_wf_email VARCHAR2(200);
834 l_pref VARCHAR2(200);
835 l_stmt VARCHAR2(1000);
836 l_err_msg VARCHAR2(2000);
837 l_upd BOOLEAN:=false;
838 BEGIN
839 BEGIN
840 SELECT wf.name,wf.email_address,wf.notification_preference INTO role_name,l_wf_email ,l_pref
841 FROM WF_LOCAL_ROLES wf, ASG_USER au
842 WHERE wf.name ='CSM_'||b_user_name||'_ROLE'
843 AND au.user_name=b_user_name
844 AND au.enabled='Y';
845
846 SELECT email_address INTO l_fnd_email
847 FROM FND_USER WHERE USER_NAME=b_user_name
848 AND sysdate between nvl(start_date,sysdate-1) and nvl(end_date,sysdate+1);
849
850 IF(l_fnd_email is NULL) THEN
851 CSM_UTIL_PKG.LOG('No FND Email found for user CSM_'||b_user_name||'_ROLE in createMobileWFUser',
852 'CSM_NOTIFICATION_EVENT_PKG.createMobileWFUser',FND_LOG.LEVEL_PROCEDURE);
853 RETURN FALSE;
854 END IF;
855
856 l_stmt:= 'UPDATE WF_LOCAL_ROLES SET DESCRIPTION=DESCRIPTION ';
857
858
859 IF l_pref<> 'MAILTEXT' THEN
860 l_stmt := l_stmt||' , notification_preference=''MAILTEXT''';
861 l_upd:=TRUE;
862 END IF;
863
864 IF(l_wf_email IS NULL OR l_wf_email <> l_fnd_email) THEN
865 l_stmt := l_stmt||' , EMAIL_ADDRESS='''||l_fnd_email||'''';
866 l_upd:=TRUE;
867 END IF;
868
869 IF l_upd THEN
870 l_stmt := l_stmt||' WHERE NAME=:1';
871 EXECUTE IMMEDIATE l_stmt USING role_name;
872 END IF;
873
874 EXCEPTION
875 WHEN no_data_found THEN
876 SELECT 'CSM_'||fu.user_name||'_ROLE',wf.display_name,fu.email_address
877 INTO role_name,role_display_name,l_fnd_email
878 FROM FND_USER fu, Asg_user au ,wf_local_roles wf
879 WHERE fu.user_name=b_user_name
880 AND sysdate between nvl(fu.start_date,sysdate-1) and nvl(fu.end_date,sysdate+1)
881 AND wf.name=fu.USER_NAME AND au.USER_NAME=fu.USER_NAME AND au.ENABLED='Y' ;
882
883 IF(l_fnd_email is NULL) THEN
884 CSM_UTIL_PKG.LOG('No FND Email found for user '||b_user_name||' in createMobileWFUser.',
885 'CSM_NOTIFICATION_EVENT_PKG.createMobileWFUser',FND_LOG.LEVEL_PROCEDURE);
886 RETURN FALSE;
887 END IF;
888
889 wf_directory.CreateAdHocUser(name => role_name, display_name =>role_display_name,
890 notification_preference => 'MAILTEXT', email_address =>l_fnd_email);
891 END;
892
893 RETURN TRUE;
894 EXCEPTION
895 WHEN OTHERS THEN
896 l_err_msg := TO_CHAR(SQLCODE)|| ':'||SUBSTR(SQLERRM, 1,2000);
897 CSM_UTIL_PKG.LOG('Exception in createMobileWFUser for user'||b_user_name||': '||l_err_msg,
898 'CSM_NOTIFICATION_EVENT_PKG.createMobileWFUser',FND_LOG.LEVEL_PROCEDURE);
899 CSM_UTIL_PKG.LOG('If SQL DATA NOT FOUND error, then reason is that User '||b_user_name||' is not an active FND nor a mobile enabled user.',
900 'CSM_NOTIFICATION_EVENT_PKG.createMobileWFUser',FND_LOG.LEVEL_PROCEDURE);
901 RETURN FALSE;
902 END createMobileWFUser;
903
904 FUNCTION send_email(b_user_name IN VARCHAR2, subject VARCHAR2, message_body VARCHAR2) return NUMBER
905 IS
906 role_name VARCHAR2(30);
907 l_err_msg VARCHAR2(2000);
908 l_wf_param wf_event_t;
909 BEGIN
910
911 IF CSM_UTIL_PKG.is_new_mmu_user(b_user_name) THEN
912 CSM_UTIL_PKG.LOG(b_user_name||' is a new or not a mobile user, so skip sending notification.','CSM_NOTIFICATION_EVENT_PKG.SEND_EMAIL',FND_LOG.LEVEL_PROCEDURE);
913 RETURN -1;
914 END IF;
915
916 IF NOT createMobileWFUser(b_user_name) THEN
917 RETURN -1;
918 ELSE
919 role_name := 'CSM_'||b_user_name||'_ROLE';
920 END IF;
921
922 wf_event_t.initialize(l_wf_param);
923 l_wf_param.AddParameterToList('TEMPLATE','DOWNLOAD_INIT_MSG');
924 l_wf_param.AddParameterToList('SUBJECT',subject);
925 l_wf_param.AddParameterToList('MESSAGE_BODY',message_body);
926
927 return invoke_WF_NotifyProcess(role_name,l_wf_param);
928
929 EXCEPTION
930 WHEN OTHERS THEN
931 l_err_msg := TO_CHAR(SQLCODE)|| ':'||SUBSTR(SQLERRM, 1,2000);
932 CSM_UTIL_PKG.LOG('Exception in send_email while sending notification for user'||b_user_name||': '||l_err_msg,
933 'CSM_NOTIFICATION_EVENT_PKG.SEND_EMAIL',FND_LOG.LEVEL_PROCEDURE);
934 return -1;
935 END send_email;
936
937 FUNCTION GET_LOCATION(p_loc_id NUMBER) RETURN VARCHAR2
938 IS
939
940 l_location VARCHAR2(2000) := '';
941
942 CURSOR c_location (b_loc_id number)
943 IS
944 select ADDRESS1 || NVL2(ADDRESS2,', '||ADDRESS2,'')
945 || NVL2(ADDRESS3,', '||ADDRESS3,'') || NVL2(ADDRESS4,', '||ADDRESS4,'')
946 || NVL2(CITY,', '||CITY,'') || NVL2(STATE,', '||STATE,'') || NVL2(COUNTRY,', '||COUNTRY,'')
947 || NVL2(POSTAL_CODE,' '||POSTAL_CODE,'') ADDRESS from hz_locations where location_id= b_loc_id;
948
949 BEGIN
950 open c_location(p_loc_id);
951 fetch c_location INTO l_location;
952 close c_location;
953
954 return l_location;
955 END;
956
957 PROCEDURE NOTIFY_USER(entity varchar2, pk_value varchar2,p_mode varchar2)
958 IS
959 l_wf_param wf_event_t;
960 BEGIN
961 wf_event_t.initialize(l_wf_param);
962 l_wf_param.AddParameterToList('ENTITY',entity);
963 l_wf_param.AddParameterToList('PK_VALUE',pk_value);
964 l_wf_param.AddParameterToList('MODE',p_mode);
965 csm_notification_event_pkg.notify_user(l_wf_param);
966 END NOTIFY_USER;
967
968 FUNCTION email_optimization(p_user_id NUMBER,p_entity VARCHAR2,p_pk VARCHAR2) RETURN BOOLEAN
969 IS
970
971 CURSOR c_sr(b_sr_id NUMBER,b_user_id NUMBER)
972 IS
973 SELECT 1
974 FROM jtf_task_assignments asg, csm_auto_sync_nfn nfn,
975 asg_system_dirty_queue sdq,csm_auto_sync_nfn_acc acc,
976 jtf_tasks_b tsk
977 where tsk.source_object_id=b_sr_id
978 and tsk.source_object_type_code='SR'
979 and tsk.task_id=asg.task_id
980 and (
981 (nfn.object_name='SERVICE_REQUEST' and nfn.object_id=tsk.source_object_id and tsk.source_object_type_code='SR')
982 OR
983 (nfn.object_name='TASK' and nfn.object_id=asg.task_id)
984 OR
985 (nfn.object_name='TASK_ASSIGNMENT' and nfn.object_id=task_assignment_id )
986 )
987 and nfn.response IS NULL
988 and nvl(nfn.reminders_sent,0) < 3
989 and nfn.user_id=b_user_id
990 and acc.notification_id=nfn.notification_id
991 and acc.user_id=nfn.user_id
992 and sdq.access_id=acc.access_id
993 and sdq.transaction_id is NULL
994 and sdq.PUB_ITEM ='CSM_AUTO_SYNC_NFN'
995 and rownum < 2;
996
997 CURSOR c_task(b_task_id NUMBER,b_user_id NUMBER) IS
998 SELECT 1
999 FROM jtf_task_assignments asg, csm_auto_sync_nfn nfn,
1000 asg_system_dirty_queue sdq,csm_auto_sync_nfn_acc acc
1001 where task_id=b_task_id
1002 and (
1003 (nfn.object_name='TASK' and nfn.object_id=task_id)
1004 OR
1005 (nfn.object_name='TASK_ASSIGNMENT' and nfn.object_id=task_assignment_id )
1006 )
1007 and nfn.user_id=b_user_id
1008 and nfn.response IS NULL
1009 and nvl(nfn.reminders_sent,0) < 3
1010 and acc.notification_id=nfn.notification_id
1011 and acc.user_id=nfn.user_id
1012 and sdq.access_id=acc.access_id
1013 and sdq.transaction_id is NULL
1014 and sdq.PUB_ITEM ='CSM_AUTO_SYNC_NFN'
1015 and rownum < 2;
1016
1017
1018 CURSOR c_task_asg(b_task_ass_id NUMBER,b_user_id NUMBER) IS
1019 SELECT 1
1020 FROM jtf_task_assignments asg, csm_auto_sync_nfn nfn,
1021 asg_system_dirty_queue sdq,csm_auto_sync_nfn_acc acc,
1022 jtf_tasks_b tsk
1023 where task_assignment_id=b_task_ass_id
1024 and tsk.task_id=asg.task_id
1025 and nfn.object_name='TASK_ASSIGNMENT' and nfn.object_id=task_assignment_id
1026 and nfn.response IS NULL
1027 and nvl(nfn.reminders_sent,0) < 3
1028 and nfn.user_id=b_user_id
1029 and acc.notification_id=nfn.notification_id
1030 and acc.user_id=nfn.user_id
1031 and sdq.access_id=acc.access_id
1032 and sdq.transaction_id is NULL
1033 and sdq.PUB_ITEM ='CSM_AUTO_SYNC_NFN'
1034 and rownum < 2;
1035
1036 l_result NUMBER :=0;
1037 BEGIN
1038
1039 /*IF g_switch_optimize_off THEN RETURN FALSE; END IF; */
1040
1041 IF(p_entity='CSM_TASKS') THEN
1042 open c_task(to_number(p_pk),p_user_id);
1043 fetch c_task into l_result;
1044 close c_task;
1045 ELSIF (p_entity='CSM_INCIDENTS_ALL') THEN
1046 open c_sr(to_number(p_pk),p_user_id);
1047 fetch c_sr into l_result;
1048 close c_sr;
1049 ELSIF (p_entity='CSM_TASK_ASSIGNMENTS') THEN
1050 open c_task_asg(to_number(p_pk),p_user_id);
1051 fetch c_task_asg into l_result;
1052 close c_task_asg;
1053 END IF;
1054
1055 IF l_result=1 THEN
1056 CSM_UTIL_PKG.LOG('Email Optimized for '||p_entity||'-'||p_pk||'-'||p_user_id,
1057 'CSM_NOTIFICATION_EVENT_PKG.email_optimization',FND_LOG.LEVEL_PROCEDURE);
1058 RETURN TRUE;
1059 END IF;
1060
1061 RETURN FALSE;
1062
1063 END email_optimization;
1064
1065 FUNCTION is_personal_task (l_mode IN VARCHAR2,l_pk NUMBER) RETURN BOOLEAN
1066 IS
1067 l_yes NUMBER;
1068 BEGIN
1069 IF l_mode='TASK' THEN
1070 SELECT 1 INTO l_yes FROM JTF_TASKS_B WHERE TASK_ID=l_pk AND SOURCE_OBJECT_TYPE_CODE='TASK';
1071 ELSE
1072 SELECT 1 INTO l_yes FROM JTF_TASKS_B A,JTF_TASK_ASSIGNMENTS B
1073 WHERE TASK_ASSIGNMENT_ID=l_pk AND B.TASK_ID=A.TASK_ID AND A.SOURCE_OBJECT_TYPE_CODE='TASK';
1074 END IF;
1075 RETURN TRUE;
1076 EXCEPTION
1077 WHEN OTHERS THEN
1078 RETURN FALSE;
1079 END is_personal_task;
1080
1081 PROCEDURE NOTIFY_USER(p_wf_param wf_event_t)
1082 IS
1083
1084 CURSOR c_user_task_assigned(b_task_ass_id NUMBER)
1085 IS
1086 SELECT usr.USER_ID,usr.USER_NAME,usr.email_address, b.TASK_ID,TASK_NAME,TASK_NUMBER,
1087 INCIDENT_NUMBER, INC_TL.SUMMARY,hp.party_name ,
1088 decode(nvl(inc.incident_location_type,'HZ_PARTY_SITE'), 'HZ_PARTY_SITE',
1089 (select location_id from hz_party_sites where party_site_id = NVL(inc.incident_location_id, inc.install_site_id)),
1090 'HZ_LOCATION',
1091 (select location_id from hz_locations where location_id = NVL(inc.incident_location_id, inc.install_site_id))
1092 ) location_id
1093 FROM ASG_USER AU, JTF_TASK_ASSIGNMENTS b,CSM_TASK_ASSIGNMENTS_ACC ACC,
1094 JTF_TASKS_B tsk,JTF_TASKS_TL tsk_tl,cs_incidents_all_b INC,
1095 cs_incidents_all_tl INC_TL, HZ_PARTIES hp, fnd_user usr
1096 WHERE au.RESOURCE_ID=b.RESOURCE_ID
1097 AND b.TASK_ASSIGNMENT_ID=b_task_ass_id
1098 AND b.TASK_ID=tsk.TASK_ID
1099 AND acc.TASK_ASSIGNMENT_ID=b.TASK_ASSIGNMENT_ID
1100 AND au.USER_ID=acc.USER_ID
1101 AND b.TASK_ID=tsk_tl.TASK_ID
1102 AND INC.incident_id=tsk.SOURCE_OBJECT_ID
1103 AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
1104 AND INC.incident_id=INC_TL.incident_id
1105 AND tsk_tl.Language=AU.Language
1106 AND inc_tl.Language=AU.Language
1107 AND hp.party_id=inc.customer_id
1108 AND usr.USER_ID=AU.user_id
1109 AND NVL(AU.multi_platform,'N')='N'
1110 AND AU.user_id <> b.last_updated_by;
1111
1112 CURSOR c_user_task(b_task_id NUMBER)
1113 IS
1114 SELECT usr.USER_ID,usr.USER_NAME,usr.email_address, acc.TASK_ID,TASK_NAME,TASK_NUMBER,
1115 INCIDENT_NUMBER, INC_TL.SUMMARY,hp.party_name ,
1116 decode(nvl(inc.incident_location_type,'HZ_PARTY_SITE'), 'HZ_PARTY_SITE',
1117 (select location_id from hz_party_sites where party_site_id = NVL(inc.incident_location_id, inc.install_site_id)),
1118 'HZ_LOCATION',
1119 (select location_id from hz_locations where location_id = NVL(inc.incident_location_id, inc.install_site_id))
1120 ) location_id
1121 FROM ASG_USER AU, CSM_TASKS_ACC ACC,
1122 JTF_TASKS_B tsk,JTF_TASKS_TL tsk_tl,cs_incidents_all_b INC,
1123 cs_incidents_all_tl INC_TL, HZ_PARTIES hp, fnd_user usr
1124 WHERE acc.TASK_ID=b_task_id
1125 AND acc.TASK_ID=tsk.TASK_ID
1126 AND au.USER_ID=acc.USER_ID
1127 AND tsk.TASK_ID=tsk_tl.TASK_ID
1128 AND INC.incident_id=tsk.SOURCE_OBJECT_ID
1129 AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
1130 AND INC.incident_id=INC_TL.incident_id
1131 AND tsk_tl.Language=AU.Language
1132 AND inc_tl.Language=AU.Language
1133 AND hp.party_id=inc.customer_id
1134 AND usr.USER_ID=AU.user_id
1135 AND NVL(AU.multi_platform,'N')='N'
1136 AND AU.user_id <> tsk.last_updated_by;
1137
1138 CURSOR c_user_task2(b_task_id NUMBER)
1139 IS
1140 SELECT usr.USER_ID,usr.USER_NAME,usr.email_address, acc.TASK_ID,TASK_NAME,TASK_NUMBER,
1141 (select location_id from hz_party_sites where party_site_id =tsk.address_id) location_id
1142 FROM ASG_USER AU, CSM_TASKS_ACC ACC,
1143 JTF_TASKS_B tsk,JTF_TASKS_TL tsk_tl, fnd_user usr
1144 WHERE acc.TASK_ID=b_task_id
1145 AND acc.TASK_ID=tsk.TASK_ID
1146 AND au.USER_ID=acc.USER_ID
1147 AND tsk.TASK_ID=tsk_tl.TASK_ID
1148 AND tsk_tl.Language=AU.Language
1149 AND usr.USER_ID=AU.user_id
1150 AND NVL(AU.multi_platform,'N')='N'
1151 AND AU.user_id <> tsk.last_updated_by;
1152
1153
1154 CURSOR c_user_task_assigned2(b_task_ass_id NUMBER)
1155 IS
1156 SELECT usr.USER_ID,usr.USER_NAME,usr.email_address, b.TASK_ID,TASK_NAME,TASK_NUMBER,
1157 (select location_id from hz_party_sites where party_site_id =tsk.address_id) location_id
1158 FROM ASG_USER AU, JTF_TASK_ASSIGNMENTS b,CSM_TASK_ASSIGNMENTS_ACC ACC,
1159 JTF_TASKS_B tsk,JTF_TASKS_TL tsk_tl, fnd_user usr
1160 WHERE au.RESOURCE_ID=b.RESOURCE_ID
1161 AND b.TASK_ASSIGNMENT_ID=b_task_ass_id
1162 AND b.TASK_ID=tsk.TASK_ID
1163 AND acc.TASK_ASSIGNMENT_ID=b.TASK_ASSIGNMENT_ID
1164 AND au.USER_ID=acc.USER_ID
1165 AND b.TASK_ID=tsk_tl.TASK_ID
1166 AND tsk_tl.Language=AU.Language
1167 AND usr.USER_ID=AU.user_id
1168 AND NVL(AU.multi_platform,'N')='N'
1169 AND AU.user_id <> b.last_updated_by;
1170
1171 CURSOR c_user_sr(b_inc_id NUMBER)
1172 IS
1173 SELECT usr.USER_ID,usr.USER_NAME,usr.email_address,acc.INCIDENT_ID,INCIDENT_NUMBER, INC_TL.SUMMARY,hp.party_name,
1174 decode(nvl(inc.incident_location_type,'HZ_PARTY_SITE'), 'HZ_PARTY_SITE',
1175 (select location_id from hz_party_sites where party_site_id = NVL(inc.incident_location_id, inc.install_site_id)),
1176 'HZ_LOCATION',
1177 (select location_id from hz_locations where location_id = NVL(inc.incident_location_id, inc.install_site_id))
1178 ) location_id
1179 FROM ASG_USER AU, CSM_INCIDENTS_ALL_ACC ACC,cs_incidents_all_b INC,
1180 cs_incidents_all_tl INC_TL, HZ_PARTIES hp, fnd_user usr
1181 WHERE acc.incident_id=b_inc_id
1182 AND au.USER_ID=acc.USER_ID
1183 AND INC.incident_id=acc.incident_id
1184 AND INC.incident_id=INC_TL.incident_id
1185 AND inc_tl.Language=AU.Language
1186 AND hp.party_id=inc.customer_id
1187 AND usr.user_id=au.user_id
1188 AND NVL(AU.multi_platform,'N')='N'
1189 AND AU.user_id <> INC.last_updated_by;
1190
1191 CURSOR c_query(b_inst number)
1192 IS
1193 SELECT acc.USER_ID,usr.USER_NAME,usr.email_address,
1194 qry.QUERY_NAME,acc.INSTANCE_NAME
1195 FROM csm_query_b qry,csm_query_instances_acc acc,
1196 FND_USER usr,asg_user au
1197 WHERE acc.QUERY_ID=qry.QUERY_ID
1198 AND acc.INSTANCE_ID=b_inst
1199 AND au.user_id=usr.USER_ID
1200 AND NVL(AU.multi_platform,'N')='N'
1201 AND acc.USER_ID=usr.USER_ID;
1202
1203 CURSOR c_parts(b_acc_id NUMBER)
1204 IS
1205 SELECT acc.USER_ID,usr.USER_NAME,usr.email_address,
1206 b.segment1 "ITEM_NAME",b.description,acc.SUBINVENTORY_CODE,acc.QUANTITY, b.primary_uom_code
1207 FROM mtl_system_items_b b, CSM_MTL_ONHAND_QTY_ACC acc,FND_USER usr ,asg_user au
1208 WHERE acc.access_id=b_acc_id
1209 AND b.inventory_item_id=acc.inventory_item_id
1210 AND b.organization_id=acc.organization_id
1211 AND usr.USER_ID=au.USER_ID
1212 AND NVL(AU.multi_platform,'N')='N'
1213 AND acc.user_id=usr.USER_ID;
1214
1215 CURSOR c_req_lines(b_acc_id NUMBER)
1216 IS
1217 SELECT acc.USER_ID,usr.USER_NAME,usr.email_address,
1218 acc.requirement_line_id,oh.order_number,
1219 CSP_PICK_UTILS.get_order_status (ol.LINE_ID, ol.FLOW_STATUS_CODE) order_status,
1220 nvl(ol.actual_arrival_date,ol.schedule_arrival_date) arrival_date
1221 FROM csm_req_lines_acc acc
1222 , CSP_REQ_LINE_DETAILS crld
1223 , OE_ORDER_LINES_ALL ol, OE_ORDER_HEADERS_ALL oh
1224 , fnd_user usr,asg_user au
1225 WHERE acc.access_id=b_acc_id
1226 AND acc.USER_ID = usr.USER_ID
1227 AND acc.requirement_line_id = crld.requirement_line_id
1228 AND crld.source_id = ol.line_id
1229 AND au.USER_ID=usr.USER_ID
1230 AND NVL(AU.multi_platform,'N')='N'
1231 AND ol.header_id = oh.header_id;
1232
1233 CURSOR c_req_details(b_req_detail_id NUMBER)
1234 IS
1235 SELECT ACCESS_ID
1236 FROM CSM_REQ_LINES_ACC acc,
1237 CSP_REQ_LINE_DETAILS dtl
1238 WHERE REQ_LINE_DETAIL_ID = b_req_detail_id
1239 AND acc.requirement_line_id = dtl.requirement_line_id;
1240
1241
1242 CURSOR c_get_resp_id(b_user_id NUMBER) IS
1243 SELECT APP_ID,RESPONSIBILITY_ID
1244 FROM ASG_USER
1245 WHERE USER_ID=b_user_id;
1246
1247 l_task_id NUMBER; l_task_number VARCHAR2(100); l_task_name VARCHAR2(200);
1248 l_sr_id NUMBER; l_sr_number VARCHAR2(100); l_sr_summary VARCHAR2(200);
1249 l_customer_name VARCHAR2(200);
1250
1251 l_user_id NUMBER; l_user_name VARCHAR2(100);
1252 l_location_id NUMBER;
1253 l_query_name VARCHAR2(255); l_instance_name VARCHAR2(255);
1254 l_item_name VARCHAR2(100); l_item_desc VARCHAR2(255);
1255 l_inv_code VARCHAR2(100);
1256 l_qty NUMBER; l_uom VARCHAR2(10);
1257
1258 l_order_number NUMBER;
1259 l_arrival_date DATE;
1260 l_order_status VARCHAR2(4000);
1261
1262
1263 l_notification_id NUMBER;
1264 l_pk_value NUMBER;
1265 l_entity VARCHAR2(200);
1266 l_mode varchar2(10);
1267
1268 l_email_address VARCHAR2(100);
1269 l_subject VARCHAR2(200);
1270 l_body VARCHAR2(4000);
1271
1272 TYPE l_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1273 TYPE l_name_type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
1274 l_uname_tab l_name_type;
1275 l_not_tab l_tab_type;
1276 l_usr_tab l_tab_type;
1277 l_cnt NUMBER :=0;
1278 l_wftimer wf_event_t;
1279 l_timeout NUMBER;
1280 l_resp_id NUMBER;
1281 l_app_id NUMBER;
1282 BEGIN
1283
1284 l_entity := p_wf_param.getValueForParameter('ENTITY');
1285
1286 IF (l_entity IS NULL
1287 OR l_entity NOT IN ('CSM_TASKS','CSM_INCIDENTS_ALL','CSM_TASK_ASSIGNMENTS'
1288 ,'CSM_QUERY_RESULTS','CSF_M_INVENTORY','CSM_REQ_LINES','CSM_REQ_LINE_DETAILS')) THEN
1289 RETURN ;
1290 END IF;
1291
1292
1293 l_pk_value := to_number(p_wf_param.getValueForParameter('PK_VALUE'));
1294 l_mode := p_wf_param.getValueForParameter('MODE');
1295
1296 CSM_UTIL_PKG.LOG('Entering NOTIFY USER for NOTIFY '||l_mode||' OF:' || l_entity || ' for PK:'||l_pk_value,
1297 'CSM_NOTIFICATION_EVENT_PKG.NOTIFY_USER',FND_LOG.LEVEL_PROCEDURE);
1298
1299 IF(l_entity= 'CSM_TASKS') THEN -- Only Update mode (Called from csmewfb.pls)
1300 l_entity:='TASK';
1301
1302 IF is_personal_task('TASK', to_number(l_pk_value)) THEN
1303 OPEN c_user_task2(l_pk_value);
1304 LOOP
1305 FETCH c_user_task2 INTO l_user_id,l_user_name,l_email_address,l_task_id,l_task_name,l_task_number,l_location_id;
1306 EXIT WHEN c_user_task2%NOTFOUND;
1307
1308 IF l_email_address IS NOT NULL and NOT email_optimization(l_user_id,'CSM_TASKS',l_pk_value) THEN
1309 l_subject := 'MFS_ALERT:NOTIFICATION_ID=&'||'#NID:MODE='||l_mode||':OBJECT_NAME=TASK:TASK_ID='||l_task_id;
1310 l_body := 'MFS_ALERT_DETAILS:NOTIFICATION_ID=&'||'#NID:TASK_NUMBER='||l_task_number||':TASK_NAME='||replace(l_task_name,':','\:')
1311 ||':INCIDENT_NUMBER=:INCIDENT_SUMMARY=:CUSTOMER_NAME=:LOCATION='||replace(get_location(l_location_id),':','\:')
1312 ||':REPLY_TO='||get_mailer_emailId ||':MFS_ALERT_DETAILS';
1313
1314 l_notification_id:=send_email(l_user_name,l_subject,l_body);
1315 l_cnt:=l_cnt+1;
1316 l_not_tab(l_cnt) := l_notification_id;
1317 l_usr_tab(l_cnt) := l_user_id;
1318 l_uname_tab(l_cnt) := l_user_name;
1319 END IF;
1320 END LOOP;
1321 CLOSE c_user_task2;
1322 ELSE
1323 OPEN c_user_task(l_pk_value);
1324 LOOP
1325 FETCH c_user_task INTO l_user_id,l_user_name,l_email_address,l_task_id,l_task_name,l_task_number,l_sr_number,
1326 l_sr_summary,l_customer_name,l_location_id;
1327 EXIT WHEN c_user_task%NOTFOUND;
1328
1329 IF l_email_address IS NOT NULL and NOT email_optimization(l_user_id,'CSM_TASKS',l_pk_value) THEN
1330 l_subject := 'MFS_ALERT:NOTIFICATION_ID=&'||'#NID:MODE='||l_mode||':OBJECT_NAME=TASK:TASK_ID='||l_task_id;
1331 l_body := 'MFS_ALERT_DETAILS:NOTIFICATION_ID=&'||'#NID:TASK_NUMBER='||l_task_number||':TASK_NAME='||replace(l_task_name,':','\:')||':INCIDENT_NUMBER='||l_sr_number
1332 ||':INCIDENT_SUMMARY='||replace(l_sr_summary,':','\:')||':CUSTOMER_NAME='||replace(l_customer_name,':','\:')
1333 ||':LOCATION='||replace(get_location(l_location_id),':','\:')||':REPLY_TO='||get_mailer_emailId ||':MFS_ALERT_DETAILS';
1334
1335 l_notification_id:=send_email(l_user_name,l_subject,l_body);
1336 l_cnt:=l_cnt+1;
1337 l_not_tab(l_cnt) := l_notification_id;
1338 l_usr_tab(l_cnt) := l_user_id;
1339 l_uname_tab(l_cnt) := l_user_name;
1340 END IF;
1341 END LOOP;
1342 CLOSE c_user_task;
1343 END IF;
1344
1345 ELSIF (l_entity= 'CSM_INCIDENTS_ALL') THEN -- Only Update mode (Called from csmewfb.pls)
1346 l_entity:='SERVICE_REQUEST';
1347
1348 OPEN c_user_sr(l_pk_value);
1349 LOOP
1350 FETCH c_user_sr INTO l_user_id,l_user_name,l_email_address,l_sr_id,l_sr_number,
1351 l_sr_summary,l_customer_name,l_location_id;
1352 EXIT WHEN c_user_sr%NOTFOUND;
1353
1354 IF l_email_address IS NOT NULL AND NOT email_optimization(l_user_id,'CSM_INCIDENTS_ALL',l_pk_value) THEN
1355 l_subject := 'MFS_ALERT:NOTIFICATION_ID=&'||'#NID:MODE='||l_mode||':OBJECT_NAME=SERVICE_REQUEST:INCIDENT_ID='||l_sr_id;
1356 l_body := 'MFS_ALERT_DETAILS:NOTIFICATION_ID=&'||'#NID:INCIDENT_NUMBER='||l_sr_number||':INCIDENT_SUMMARY='||
1357 replace(l_sr_summary,':','\:')||':CUSTOMER_NAME='||replace(l_customer_name,':','\:') ||':LOCATION='||
1358 replace(get_location(l_location_id),':','\:')||':REPLY_TO='||get_mailer_emailId ||':MFS_ALERT_DETAILS';
1359
1360 l_notification_id:=send_email(l_user_name,l_subject,l_body);
1361 l_cnt:=l_cnt+1;
1362 l_not_tab(l_cnt) := l_notification_id;
1363 l_usr_tab(l_cnt) := l_user_id;
1364 l_uname_tab(l_cnt) := l_user_name;
1365 END IF;
1366 END LOOP;
1367 CLOSE c_user_sr;
1368
1369 ELSIF (l_entity= 'CSM_TASK_ASSIGNMENTS') THEN
1370 l_entity:='TASK_ASSIGNMENT';
1371
1372 IF is_personal_task('TA', to_number(l_pk_value)) THEN
1373 OPEN c_user_task_assigned2(l_pk_value);
1374 FETCH c_user_task_assigned2 INTO l_user_id,l_user_name,l_email_address,l_task_id,l_task_name,l_task_number,l_location_id;
1375 CLOSE c_user_task_assigned2;
1376 ELSE
1377 OPEN c_user_task_assigned(l_pk_value);
1378 FETCH c_user_task_assigned INTO l_user_id,l_user_name,l_email_address,l_task_id,l_task_name,l_task_number,l_sr_number,
1379 l_sr_summary,l_customer_name,l_location_id;
1380 CLOSE c_user_task_assigned;
1381 END IF;
1382
1383 IF l_email_address IS NULL THEN
1384
1385 CSM_UTIL_PKG.LOG('Leaving NOTIFY_USER as there is no email found for user - '||l_user_id,
1386 'CSM_NOTIFICATION_EVENT_PKG.NOTIFY_USER',FND_LOG.LEVEL_PROCEDURE);
1387 RETURN;
1388 END IF;
1389
1390 IF email_optimization(l_user_id,'CSM_TASK_ASSIGNMENTS',l_pk_value) THEN
1391 -- removed update-only check as this call is asynchronous --Bug 13651273
1392 -- TA Init might return saying not downloadable but Event will still get raised as NEW TA if created by user is MFS user
1393 -- TA Update on auto assign(in planning->to assgined)
1394 -- actually inserts into acc raising new event again before the above event is processed
1395 RETURN;
1396 END IF;
1397
1398 l_subject := 'MFS_ALERT:NOTIFICATION_ID=&'||'#NID:MODE='||l_mode||':OBJECT_NAME=TASK_ASSIGNMENT:TASK_ASSIGNMENT_ID='||l_pk_value;
1399
1400 IF l_sr_number IS NULL THEN --Personal Task;
1401 l_body := 'MFS_ALERT_DETAILS:NOTIFICATION_ID=&'||'#NID:TASK_ID='||l_task_id||':TASK_NUMBER='||l_task_number||':TASK_NAME='||replace(l_task_name,':','\:')
1402 ||':INCIDENT_NUMBER=:INCIDENT_SUMMARY=:CUSTOMER_NAME=:LOCATION='||replace(get_location(l_location_id),':','\:')
1403 ||':REPLY_TO='||get_mailer_emailId ||':MFS_ALERT_DETAILS';
1404 ELSE
1405 l_body := 'MFS_ALERT_DETAILS:NOTIFICATION_ID=&'||'#NID:TASK_ID='||l_task_id||':TASK_NUMBER='||l_task_number
1406 ||':TASK_NAME='||replace(l_task_name,':','\:')||':INCIDENT_NUMBER='||l_sr_number||':INCIDENT_SUMMARY='||
1407 replace(l_sr_summary,':','\:')||':CUSTOMER_NAME='||replace(l_customer_name,':','\:') ||':LOCATION='||
1408 replace(get_location(l_location_id),':','\:')||':REPLY_TO='||get_mailer_emailId ||':MFS_ALERT_DETAILS';
1409 END IF;
1410
1411 l_notification_id:=send_email(l_user_name,l_subject,l_body);
1412 l_cnt:=l_cnt+1;
1413 l_not_tab(l_cnt) := l_notification_id;
1414 l_usr_tab(l_cnt) := l_user_id;
1415 l_uname_tab(l_cnt) := l_user_name;
1416
1417 ELSIF (l_entity= 'CSM_QUERY_RESULTS') THEN -- Only Insert mode (called from csmqryb.pls)
1418 l_entity:='QUERY_RESULT';
1419
1420 OPEN c_query(l_pk_value);
1421 LOOP
1422 FETCH c_query INTO l_user_id,l_user_name,l_email_address,l_query_name,l_instance_name;
1423 EXIT WHEN c_query%NOTFOUND;
1424
1425 IF l_email_address IS NOT NULL THEN --only insert so no optimzation reqd
1426
1427 l_subject := 'MFS_ALERT:NOTIFICATION_ID=&'||'#NID:MODE=NEW:OBJECT_NAME=QUERY_RESULT:INSTANCE_ID='||l_pk_value;
1428 l_body := 'MFS_ALERT_DETAILS:NOTIFICATION_ID=&'||'#NID:INSTANCE_NAME='||replace(l_instance_name,':','\:')
1429 ||':QUERY_NAME='||replace(l_query_name,':','\:')||':REPLY_TO='||get_mailer_emailId ||':MFS_ALERT_DETAILS';
1430
1431 l_notification_id:=send_email(l_user_name,l_subject,l_body);
1432 l_cnt:=l_cnt+1;
1433 l_not_tab(l_cnt) := l_notification_id;
1434 l_usr_tab(l_cnt) := l_user_id;
1435 l_uname_tab(l_cnt) := l_user_name;
1436 END IF;
1437 END LOOP;
1438 CLOSE c_query;
1439
1440 ELSIF (l_entity= 'CSF_M_INVENTORY') THEN -- Both Insert and Update mode (called from csmemsib.pls)
1441 l_entity:='INVENTORY'; --Access_id is the PK, so only one record
1442
1443 OPEN c_parts(l_pk_value);
1444 FETCH c_parts INTO l_user_id,l_user_name,l_email_address,l_item_name,l_item_desc,l_inv_code,l_qty,l_uom;
1445 CLOSE c_parts;
1446
1447 IF l_email_address IS NULL THEN
1448 CSM_UTIL_PKG.LOG('Leaving NOTIFY_USER as there is no email found for user - '||l_user_id,
1449 'CSM_NOTIFICATION_EVENT_PKG.NOTIFY_USER',FND_LOG.LEVEL_PROCEDURE);
1450 RETURN;
1451 END IF;
1452
1453 IF l_mode='UPDATE' THEN
1454 RETURN;
1455 END IF;
1456
1457 l_subject := 'MFS_ALERT:NOTIFICATION_ID=&'||'#NID:MODE='||l_mode||':OBJECT_NAME=INVENTORY:GEN_PK='||l_pk_value;
1458 l_body := 'MFS_ALERT_DETAILS:NOTIFICATION_ID=&'||'#NID:ITEM_NAME='||replace(l_item_name,':','\:')
1459 ||':ITEM_DESCRIPTION='||replace(l_item_desc,':','\:')||':SUB_INVENTORY='||replace(l_inv_code,':','\:')
1460 ||':QUANTITY='||l_qty||':UOM='||l_uom||':REPLY_TO='||get_mailer_emailId ||':MFS_ALERT_DETAILS';
1461
1462 l_notification_id:=send_email(l_user_name,l_subject,l_body);
1463 l_cnt:=l_cnt+1;
1464 l_not_tab(l_cnt) := l_notification_id;
1465 l_usr_tab(l_cnt) := l_user_id;
1466 l_uname_tab(l_cnt) := l_user_name;
1467
1468 ELSIF (l_entity= 'CSM_REQ_LINES') THEN --Only Update mode (Called from csmerlb.pls)
1469 l_entity:='ORDER_STATUS'; --Access_id is the PK, so only one record
1470
1471 OPEN c_req_lines(l_pk_value); --l_pk_value is changed to req line id in FETCH
1472 FETCH c_req_lines INTO l_user_id,l_user_name,l_email_address,l_pk_value,
1473 l_order_number,l_order_status,l_arrival_date;
1474 CLOSE c_req_lines;
1475
1476 IF l_email_address IS NULL THEN
1477 CSM_UTIL_PKG.LOG('Leaving NOTIFY_USER as there is no email found for user - '||l_user_id,
1478 'CSM_NOTIFICATION_EVENT_PKG.NOTIFY_USER',FND_LOG.LEVEL_PROCEDURE);
1479 RETURN;
1480 END IF;
1481
1482
1483 l_subject := 'MFS_ALERT:NOTIFICATION_ID=&'||'#NID:MODE='||l_mode||':OBJECT_NAME=ORDER_STATUS:REQUIREMENT_LINE_ID='||l_pk_value;
1484 l_body := 'MFS_ALERT_DETAILS:NOTIFICATION_ID=&'||'#NID:ORDER_NUMBER='||l_order_number
1485 ||':STATUS='||replace(l_order_status,':','\:')||':ARRIVAL_DATE='||l_arrival_date||':REPLY_TO='||get_mailer_emailId ||':MFS_ALERT_DETAILS';
1486
1487 l_notification_id:=send_email(l_user_name,l_subject,l_body);
1488 l_cnt:=l_cnt+1;
1489 l_not_tab(l_cnt) := l_notification_id;
1490 l_usr_tab(l_cnt) := l_user_id;
1491 l_uname_tab(l_cnt) := l_user_name;
1492
1493 ELSIF (l_entity= 'CSM_REQ_LINE_DETAILS') THEN --Only Insert mode (Called from csmewfb.pls)
1494 l_entity:='ORDER_STATUS'; --l_req_line_detail_id is the PK
1495
1496 OPEN c_req_details(l_pk_value);
1497 LOOP
1498 FETCH c_req_details INTO l_pk_value; --l_pk_value is now access_id
1499 EXIT WHEN c_req_details%NOTFOUND;
1500
1501 OPEN c_req_lines(l_pk_value); --l_pk_value is changed to req line id in FETCH
1502 FETCH c_req_lines INTO l_user_id,l_user_name,l_email_address,l_pk_value,
1503 l_order_number,l_order_status,l_arrival_date;
1504 CLOSE c_req_lines;
1505
1506 IF l_email_address IS NOT NULL THEN --only insert so no optimzation reqd
1507
1508 l_subject := 'MFS_ALERT:NOTIFICATION_ID=&'||'#NID:MODE=NEW:OBJECT_NAME=ORDER_STATUS:REQUIREMENT_LINE_ID='||l_pk_value;
1509 l_body := 'MFS_ALERT_DETAILS:NOTIFICATION_ID=&'||'#NID:ORDER_NUMBER='||l_order_number
1510 ||':STATUS='||replace(l_order_status,':','\:')||':ARRIVAL_DATE='||l_arrival_date||':REPLY_TO='||get_mailer_emailId ||':MFS_ALERT_DETAILS';
1511
1512
1513 l_notification_id:=send_email(l_user_name,l_subject,l_body);
1514 l_cnt:=l_cnt+1;
1515 l_not_tab(l_cnt) := l_notification_id;
1516 l_usr_tab(l_cnt) := l_user_id;
1517 l_uname_tab(l_cnt) := l_user_name;
1518 END IF;
1519 END LOOP;
1520 CLOSE c_req_details;
1521
1522 END IF;
1523
1524
1525 FOR I in 1..l_cnt
1526 LOOP
1527 IF(l_not_tab(I) <> -1) THEN
1528 -- insert into auto sync table
1529 INSERT INTO csm_auto_sync_nfn(USER_ID,NOTIFICATION_ID,OBJECT_NAME,OBJECT_ID,DML,REMINDERS_SENT,CREATION_DATE,CREATED_BY
1530 ,LAST_UPDATE_DATE,LAST_UPDATED_BY)
1531 VALUES(l_usr_tab(I),l_not_tab(I),l_entity,l_pk_value,l_mode,0,sysdate,1,sysdate,1);
1532
1533 CSM_ACC_PKG.Insert_Acc
1534 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_AUTO_SYNC_NFN')
1535 ,P_ACC_TABLE_NAME => 'CSM_AUTO_SYNC_NFN_ACC'
1536 ,P_SEQ_NAME => 'CSM_AUTO_SYNC_NFN_ACC_S'
1537 ,P_PK1_NAME => 'NOTIFICATION_ID'
1538 ,P_PK1_NUM_VALUE => l_not_tab(I)
1539 ,P_USER_ID => l_usr_tab(I)
1540 );
1541
1542 CSM_UTIL_PKG.LOG('Invoke timer for '||l_not_tab(I),
1543 'CSM_NOTIFICATION_EVENT_PKG.NOTIFY_USER',FND_LOG.LEVEL_PROCEDURE);
1544
1545 OPEN c_get_resp_id(l_usr_tab(I));
1546 FETCH c_get_resp_id INTO l_app_id,l_resp_id;
1547 CLOSE c_get_resp_id;
1548
1549 l_timeout := to_number(fnd_profile.value_specific('CSM_ALERT_TIMEOUT_1',l_usr_tab(I),l_resp_id,l_app_id));
1550
1551 IF l_timeout > 0 THEN
1552 --timer logic - Fixed 3 tries + 1 original email
1553 wf_event_t.initialize(l_wftimer);
1554 l_wftimer.AddParameterToList('NOTIFICATION_ID',to_char(l_not_tab(I)));
1555 l_wftimer.AddParameterToList('SENT_TO','CSM_'||l_uname_tab(I)||'_ROLE');
1556 l_wftimer.AddParameterToList('TRIES','1');
1557
1558 wf_event.raise(p_event_name=>'oracle.apps.csm.download.timer',
1559 p_event_key=>to_char(l_not_tab(I)),p_parameters=>l_wftimer.getParameterList,
1560 p_event_data=>null,p_send_date=>(sysdate+(l_timeout*60*0.000011574)));
1561 END IF;
1562 END IF;
1563 END LOOP;
1564
1565 IF l_cnt > 0 THEN
1566 CSM_UTIL_PKG.LOG('Leaving NOTIFY_USER after notifying users to start sync',
1567 'CSM_NOTIFICATION_EVENT_PKG.NOTIFY_USER',FND_LOG.LEVEL_PROCEDURE);
1568 ELSE
1569 CSM_UTIL_PKG.LOG('Leaving NOTIFY_USER. There was no entity or non-multi-platform User found to be notified',
1570 'CSM_NOTIFICATION_EVENT_PKG.NOTIFY_USER',FND_LOG.LEVEL_PROCEDURE);
1571 END IF;
1572
1573 END NOTIFY_USER;
1574
1575 --call back api
1576 PROCEDURE NOTIFY_RESPONSE(item_type in varchar2, p_item_key in varchar2,
1577 activity_id in number, command in varchar2, resultout in out NOCOPY varchar2)
1578 IS
1579 l_text_value VARCHAR2(100);
1580 l_nid NUMBER;
1581 BEGIN
1582
1583 CSM_UTIL_PKG.LOG( 'In NOTIFY_RESPONSE for command:"'||command||'" with item_key :' || p_item_key
1584 ||' and activity_id :'||activity_id,'CSM_NOTIFICATION_EVENT_PKG.NOTIFY_RESPONSE',FND_LOG.LEVEL_PROCEDURE);
1585
1586 IF(item_type='CSM_MSGS' and command = 'RESPOND') THEN
1587
1588 BEGIN
1589
1590 select notification_id,text_value INTO l_nid,l_text_value from wf_notification_attributes
1591 where notification_id = (select notification_id from wf_notifications
1592 where message_type='CSM_MSGS' and message_name='DOWNLOAD_INIT_MSG'
1593 and item_key=p_item_key) and name='RESULT';
1594
1595 EXCEPTION
1596 WHEN Others THEN
1597 CSM_UTIL_PKG.LOG( 'Exception occurred in NOTIFY_RESPONSE for item_key :' || p_item_key ||' and activity_id :'
1598 ||activity_id|| '->'|| sqlerrm, 'CSM_NOTIFICATION_EVENT_PKG.NOTIFY_RESPONSE',FND_LOG.LEVEL_EXCEPTION);
1599 RETURN;
1600 END;
1601
1602
1603 UPDATE CSM_AUTO_SYNC_NFN
1604 SET RESPONSE= l_text_value
1605 ,RESPONDED_ON=SYSDATE
1606 WHERE NOTIFICATION_ID=l_nid;
1607
1608 -- no need to mark dirty as this info is not downloaded
1609 END IF;
1610
1611 END NOTIFY_RESPONSE;
1612
1613
1614
1615 --Subscription to event "oracle.apps.csm.download.timer"
1616 FUNCTION NOTIFICATION_TIMER_SUB(p_subscription_guid IN RAW, p_event IN OUT NOCOPY WF_EVENT_T)
1617 RETURN VARCHAR2
1618 IS
1619 l_nid NUMBER;
1620 l_try NUMBER;
1621 l_wftimer wf_event_t;
1622 CURSOR c_response(b_nid NUMBER) IS
1623 SELECT 1
1624 FROM WF_NOTIFICATIONS
1625 WHERE NOTIFICATION_ID=b_nid
1626 AND STATUS='CLOSED';
1627
1628 CURSOR c_get_resp_id(b_user_name VARCHAR2) IS
1629 SELECT APP_ID,RESPONSIBILITY_ID
1630 FROM ASG_USER
1631 WHERE USER_NAME=b_user_name;
1632
1633 l_timeout NUMBER;
1634 l_resp_id NUMBER;
1635 l_app_id NUMBER;
1636
1637 l_check NUMBER:=0;
1638 l_role VARCHAR2(120);
1639 l_err_msg VARCHAR2(2000);
1640 l_sql_code NUMBER;
1641 l_count NUMBER;
1642
1643 BEGIN
1644
1645 l_nid := to_number(p_event.GetValueForParameter('NOTIFICATION_ID'));
1646 l_try := to_number(p_event.GetValueForParameter('TRIES'));
1647 l_role := p_event.GetValueForParameter('SENT_TO');
1648
1649 CSM_UTIL_PKG.LOG('TIMER MODULE ENTERED on '|| to_char(sysdate,'DD-MON-YY HH:MI:SS AM') ||' with nid:'||l_nid||' and try:'||l_try,
1650 'CSM_NOTIFICATION_EVENT_PKG.NOTIFICATION_TIMER_SUB',FND_LOG.LEVEL_PROCEDURE);
1651
1652 OPEN c_response(l_nid);
1653 FETCH c_response INTO l_check;
1654 CLOSE c_response;
1655
1656 IF l_check = 1 THEN
1657 CSM_UTIL_PKG.LOG('Response received in try#'||l_try ||' for nid: '||l_nid,
1658 'CSM_NOTIFICATION_EVENT_PKG.NOTIFICATION_TIMER_SUB',FND_LOG.LEVEL_PROCEDURE);
1659 RETURN 'SUCCESS';
1660 END IF;
1661
1662
1663 IF l_try <4 THEN
1664 --timer logic - only 3 tries
1665
1666 OPEN c_get_resp_id(substr(l_role,5,length(l_role)-9));
1667 FETCH c_get_resp_id INTO l_app_id,l_resp_id;
1668 CLOSE c_get_resp_id;
1669
1670 if l_try < 3 then
1671 l_count := l_try + 1;
1672 else
1673 l_count := l_try;
1674 end if;
1675
1676 l_timeout := to_number(fnd_profile.value_specific('CSM_ALERT_TIMEOUT_'||l_count,
1677 asg_base.get_user_id(substr(l_role,5,length(l_role)-9)),l_resp_id,l_app_id));
1678
1679 --try sending Email again
1680 wf_notification.forward(l_nid,l_role);
1681
1682 UPDATE CSM_AUTO_SYNC_NFN
1683 SET REMINDERS_SENT = REMINDERS_SENT + 1
1684 WHERE NOTIFICATION_ID=l_nid;
1685
1686 l_try := l_try + 1;
1687
1688 wf_event_t.initialize(l_wftimer);
1689 l_wftimer.AddParameterToList('NOTIFICATION_ID',to_char(l_nid));
1690 l_wftimer.AddParameterToList('TRIES',l_try);
1691 l_wftimer.AddParameterToList('SENT_TO',l_role);
1692 wf_event.raise(p_event_name=>'oracle.apps.csm.download.timer',
1693 p_event_key=>to_char(l_nid),p_parameters=>l_wftimer.getParameterList,
1694 p_event_data=>null,p_send_date=>(sysdate+(l_timeout*60*0.000011574)));
1695 ELSE
1696
1697 CSM_UTIL_PKG.LOG('Email Id '||l_nid||' is not answered by '||substr(l_role,5,length(l_role)-9),
1698 'CSM_NOTIFICATION_EVENT_PKG.NOTIFICATION_TIMER_SUB',FND_LOG.LEVEL_PROCEDURE);
1699
1700 wf_event_t.initialize(l_wftimer);
1701 l_wftimer.AddParameterToList('NOTIFICATION_ID',to_char(l_nid));
1702 l_wftimer.AddParameterToList('RECIPIENT',substr(l_role,5,length(l_role)-9));
1703 wf_event.raise(p_event_name=>'oracle.apps.csm.download.noResponse',
1704 p_event_key=>to_char(l_nid),p_parameters=>l_wftimer.getParameterList,
1705 p_event_data=>null,p_send_date=>null);
1706 END IF;
1707
1708 RETURN 'SUCCESS';
1709 EXCEPTION
1710 WHEN Others THEN
1711 l_sql_code:= SQLCODE;
1712 l_err_msg:= substr(SQLERRM,1,2000);
1713 CSM_UTIL_PKG.LOG('exception while processing '||l_nid||' : '||l_sql_code||':'||l_err_msg,
1714 'CSM_NOTIFICATION_EVENT_PKG.NOTIFICATION_TIMER_SUB',FND_LOG.LEVEL_PROCEDURE);
1715
1716 RETURN 'ERROR';
1717 END NOTIFICATION_TIMER_SUB;
1718
1719 FUNCTION getGMTDeviation(p_date IN DATE) RETURN VARCHAR2
1720 IS
1721 l_server_tz NUMBER;
1722 l_dst_begin DATE;
1723 l_dst_end DATE;
1724 l_deviation NUMBER;
1725 l_increment NUMBER;
1726 l_float NUMBER;
1727 l_dev VARCHAR2(10);
1728 BEGIN
1729 select to_number(profile_option_value) INTO l_server_tz from fnd_profile_option_values where profile_option_id in
1730 (select profile_option_id from fnd_profile_options where profile_option_name in ('SERVER_TIMEZONE_ID'))
1731 and level_id=10001;
1732
1733 select next_day(add_months('1-JAN'||to_char(sysdate,'YYYY'),begin_dst_month-1) + ((begin_dst_week_of_month-1)*7)-1,
1734 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,
1735 next_day(add_months('1-JAN'||to_char(sysdate,'YYYY'),end_dst_month-1) + ((end_dst_week_of_month-1)*7)-1,
1736 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,
1737 GMT_DEVIATION_HOURS,DST_INCREMENT
1738 into l_dst_begin,l_dst_end,l_deviation,l_increment
1739 from jtm_hz_timezones_b where timezone_id=l_server_tz;
1740
1741 IF(p_date between l_dst_begin and l_dst_end) THEN
1742 l_deviation := l_deviation + l_increment;
1743 END IF;
1744
1745 IF l_deviation < 0 THEN
1746 l_dev := to_char(l_deviation);
1747 ELSE
1748 l_dev := '+'||to_char(l_deviation);
1749 END IF;
1750
1751 IF(instr(l_dev,'.')=0) THEN
1752 RETURN '[GMT'||l_dev||']';
1753 ELSE
1754 l_float:=to_number(substr(l_dev,instr(l_dev,'.'))) * 60 ;
1755 RETURN '[GMT'||substr(l_dev,1,instr(l_dev,'.')-1)||':'||l_float||']';
1756 END IF;
1757
1758 EXCEPTION
1759 WHEN Others THEN
1760 RETURN '';
1761 END getGMTDeviation;
1762
1763 PROCEDURE email_deferred_admin(p_tracking_id IN NUMBER)
1764 IS
1765
1766 CURSOR c_get_deferred_data(b_id NUMBER)
1767 IS
1768 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,
1769 wfrl.display_name, nfn.object_name, pi.primary_key_column, nfn.object_id, nfn.error_msg,nfn.creation_date
1770 from csm_deferred_nfn_info nfn , asg_pub_item pi, asg_user usr, wf_roles wfrl
1771 where tracking_id=b_id
1772 and wfrl.name=nfn.client_id
1773 and nfn.object_name=pi.item_id
1774 and usr.user_name=nfn.client_id;
1775
1776 l_tran_id NUMBER;
1777 l_seq NUMBER;
1778 l_client_id VARCHAR2(100);
1779 l_dev_type VARCHAR2(100);
1780 l_disp_name VARCHAR2(500);
1781 l_pub_name VARCHAR2(100);
1782 l_pk_name VARCHAR2(100);
1783 l_pk_value VARCHAR2(100);
1784 l_err_msg VARCHAR2(2000);
1785 l_tran_date date;
1786
1787 CURSOR c_get_resp_id(b_user_name VARCHAR2) IS
1788 SELECT APP_ID,RESPONSIBILITY_ID
1789 FROM ASG_USER
1790 WHERE USER_NAME=b_user_name;
1791
1792 l_user_string VARCHAR2(4000);
1793 l_user_list asg_download.pk_list;
1794 l_resp_id NUMBER;
1795 l_app_id NUMBER;
1796 l_notification_id NUMBER;
1797
1798 l_sql_err_msg VARCHAR2(2000);
1799 l_sql_code NUMBER;
1800 l_wf_param wf_event_t;
1801 BEGIN
1802
1803 OPEN c_get_deferred_data(p_tracking_id);
1804 FETCH c_get_deferred_data INTO l_tran_id,l_seq, l_client_id,l_dev_type,l_disp_name,
1805 l_pub_name,l_pk_name,l_pk_value,l_err_msg,l_tran_date;
1806 CLOSE c_get_deferred_data;
1807
1808 OPEN c_get_resp_id(l_client_id);
1809 FETCH c_get_resp_id INTO l_app_id,l_resp_id;
1810 CLOSE c_get_resp_id;
1811
1812 SELECT fnd_profile.value_specific('CSM_NOTIFY_DEFERRED',NULL,l_resp_id,l_app_id)
1813 INTO l_user_string FROM DUAL;
1814
1815 IF CSM_UTIL_PKG.IS_HTML5_USER(l_client_id) THEN --for htm users send report
1816 IF l_user_string IS NULL THEN
1817 l_user_string:=l_client_id;
1818 ELSIF INSTR(l_user_string,l_client_id)<1 THEN
1819 l_user_string:=l_user_string||','||l_client_id;
1820 END IF;
1821 END IF;
1822
1823 IF l_user_string IS NULL THEN
1824 RAISE NO_DATA_FOUND;
1825 END IF;
1826
1827 l_user_list := asg_download.get_listfrom_string(upper(l_user_string));
1828
1829 wf_event_t.initialize(l_wf_param);
1830 l_wf_param.AddParameterToList('TEMPLATE','DEFERRED_ERROR_REPORT');
1831
1832 l_wf_param.AddParameterToList( 'USER_NAME', l_client_id);
1833 l_wf_param.AddParameterToList( 'TRAN_ID', to_char(l_tran_id));
1834 l_wf_param.AddParameterToList( 'SEQUENCE', to_char(l_seq));
1835 l_wf_param.AddParameterToList( 'DEVICE_TYPE', l_dev_type);
1836 l_wf_param.AddParameterToList( 'EMP_NAME', l_disp_name);
1837 l_wf_param.AddParameterToList( 'PUB_ITEM', l_pub_name);
1838 l_wf_param.AddParameterToList( 'PK_COLUMN', l_pk_name);
1839 l_wf_param.AddParameterToList( 'PK_VALUE', l_pk_value);
1840 l_wf_param.AddParameterToList( 'ERR_MSG', NVL(l_err_msg,'NULL'));
1841 l_wf_param.AddParameterToList( 'TRAN_DATE', to_char(l_tran_date,'DD-MON-RRRR HH24:MI:SS')||getGMTDeviation(l_tran_date));
1842
1843
1844 FOR I IN 1..l_user_list.COUNT
1845 LOOP
1846 BEGIN
1847 CSM_UTIL_PKG.LOG('Sending deferred report email to '||l_user_list(I),
1848 'CSM_NOTIFICATION_EVENT_PKG.email_deferred_admin',FND_LOG.LEVEL_PROCEDURE);
1849
1850 l_notification_id := invoke_WF_NotifyProcess(l_user_list(I),l_wf_param);
1851
1852 IF CSM_UTIL_PKG.IS_HTML5_USER(l_user_list(I)) THEN
1853 DOWNLOAD_NOTIFICATION(l_notification_id,l_sql_err_msg); --download notification to client
1854 END IF;
1855
1856 EXCEPTION
1857 WHEN OTHERS THEN
1858 l_sql_code:= SQLCODE;
1859 l_sql_err_msg:= substr(SQLERRM,1,2000);
1860 CSM_UTIL_PKG.LOG('exception while sending notification to '||l_user_list(I)||' : '||l_sql_code||':'||l_sql_err_msg,
1861 'CSM_NOTIFICATION_EVENT_PKG.email_deferred_admin',FND_LOG.LEVEL_EXCEPTION);
1862 END;
1863 END LOOP;
1864
1865 EXCEPTION
1866 WHEN NO_DATA_FOUND THEN
1867 CSM_UTIL_PKG.LOG('No email is sent to the administrator since the profile CSM_NOTIFY_DEFERRED is NULL',
1868 'CSM_NOTIFICATION_EVENT_PKG.email_deferred_admin',FND_LOG.LEVEL_PROCEDURE);
1869 WHEN Others THEN
1870 l_sql_code:= SQLCODE;
1871 l_sql_err_msg:= substr(SQLERRM,1,2000);
1872 CSM_UTIL_PKG.LOG('exception while emailing admin : '||l_sql_code||':'||l_sql_err_msg,
1873 'CSM_NOTIFICATION_EVENT_PKG.email_deferred_admin',FND_LOG.LEVEL_EXCEPTION);
1874
1875 END email_deferred_admin;
1876
1877
1878 FUNCTION get_source_object_code_str(p_parent_pubitem IN VARCHAR2,p_child_pubitem IN VARCHAR2)
1879 return VARCHAR2
1880 IS
1881 l_code VARCHAR2(100);
1882 BEGIN
1883 /* make an entry here only if more than one parent exists and if the column
1884 name specifying parent's id is generic */
1885
1886 IF p_child_pubitem ='CSM_TASKS' THEN
1887 select decode(p_parent_pubitem,'CSM_INCIDENTS_ALL','SR','X')
1888 INTO l_code from dual;
1889
1890 RETURN 'AND SOURCE_OBJECT_TYPE_CODE='''||l_code||'''';
1891 ELSIF p_child_pubitem = 'CSF_M_NOTES' THEN
1892 select decode(p_parent_pubitem,'CSM_INCIDENTS_ALL','SR','CSM_TASKS','TASK',
1893 'CSM_DEBRIEF_HEADERS', 'SD','X') INTO l_code from dual;
1894
1895 RETURN 'AND SOURCE_OBJECT_CODE='''||l_code||'''';
1896 ELSIF p_child_pubitem = 'CSF_M_LOBS' THEN
1897 select decode(p_parent_pubitem,'CSM_INCIDENTS_ALL','CS_INCIDENTS','CSM_TASKS','JTF_TASKS_B',
1898 'CSM_DEBRIEF_HEADERS', 'CSF_DEBRIEF_HEADERS','X') INTO l_code from dual;
1899
1900 RETURN 'AND ENTITY_NAME='''||l_code||'''';
1901 END IF;
1902
1903 RETURN '';
1904 END get_source_object_code_str;
1905
1906 FUNCTION get_source_object_column(p_parent_pubitem IN VARCHAR2,p_child_pubitem IN VARCHAR2)
1907 return VARCHAR2
1908 IS
1909 BEGIN
1910
1911 --if only one parent , directly return column name
1912 --if more than one parent and if not specified here make an entry in get_source_object_code_str
1913
1914 IF p_child_pubitem = 'CSM_TASK_ASSIGNMENTS' THEN
1915 RETURN 'TASK_ID';
1916 ELSIF p_child_pubitem = 'CSM_TASK_ASSIGNMENTS_AUDIT' THEN
1917 RETURN 'TASK_ASSIGNMENT_ID';
1918 ELSIF p_child_pubitem = 'CSM_DEBRIEF_HEADERS' THEN
1919 RETURN 'TASK_ASSIGNMENT_ID';
1920 ELSIF p_child_pubitem = 'CSF_M_LOBS' THEN
1921 RETURN 'PK1_VALUE';
1922 ELSIF p_child_pubitem in ('CSF_M_DEBRIEF_LABOR' ,'CSF_M_DEBRIEF_PARTS','CSF_M_DEBRIEF_EXPENSES')
1923 and p_parent_pubitem = 'CSM_DEBRIEF_HEADERS' THEN
1924 RETURN 'DEBRIEF_HEADER_ID';
1925 ELSIF p_child_pubitem in ('CSF_M_DEBRIEF_LABOR' ,'CSF_M_DEBRIEF_PARTS','CSF_M_DEBRIEF_EXPENSES')
1926 and p_parent_pubitem = 'CSM_TASK_ASSIGNMENTS' THEN
1927 RETURN 'TASK_ASSIGNMENT_ID';
1928 ELSIF p_child_pubitem = 'CSM_REQ_HEADERS' and p_parent_pubitem = 'CSM_TASK_ASSIGNMENTS' THEN
1929 RETURN 'TASK_ASSIGNMENT_ID';
1930 ELSIF p_child_pubitem = 'CSM_REQ_HEADERS' and p_parent_pubitem = 'CSM_TASKS' THEN
1931 RETURN 'TASK_ID';
1932 ELSIF p_child_pubitem = 'CSM_REQ_LINES' THEN
1933 RETURN 'REQUIREMENT_HEADER_ID';
1934 ELSIF p_child_pubitem = 'CSM_QUERY_VARIABLE_VALUES' THEN
1935 RETURN 'INSTANCE_ID';
1936 END IF;
1937
1938 RETURN 'SOURCE_OBJECT_ID';
1939
1940 END get_source_object_column;
1941
1942 --12.1.2
1943 PROCEDURE notify_deferred(p_user_name IN VARCHAR2,
1944 p_tranid IN NUMBER,
1945 p_pubitem IN VARCHAR2,
1946 p_sequence IN NUMBER,
1947 p_dml_type IN VARCHAR2,
1948 p_pk IN VARCHAR2,
1949 p_error_msg IN VARCHAR2)
1950 IS
1951 CURSOR c_parent(b_child varchar2) IS
1952 SELECT lookup_name
1953 FROM CSM_ERROR_NFN_LOOKUPS
1954 WHERE LOOKUP_TYPE='RELATIONSHIP'
1955 AND LOOKUP_CODE='PARENT_OF'
1956 AND LOOKUP_VALUE=b_child;
1957
1958 CURSOR c_get_resp_id(b_user_name VARCHAR2) IS
1959 SELECT APP_ID,RESPONSIBILITY_ID
1960 FROM ASG_USER
1961 WHERE USER_NAME=b_user_name;
1962
1963 l_wftimer wf_event_t;
1964 l_timeout NUMBER;
1965 l_resp_id NUMBER;
1966 l_app_id NUMBER;
1967
1968 l_parent_pi VARCHAR2(100);
1969 l_subject VARCHAR2(200);
1970 l_body VARCHAR2(4000);
1971 l_mode VARCHAR2(10);
1972 l_sql VARCHAR2(4000);
1973 l_pk_col VARCHAR2(200);
1974 l_pk_value NUMBER;
1975 l_pk_clause VARCHAR2(500);
1976 l_notification_id NUMBER :=-1;
1977 l_tracking_id NUMBER :=-1;
1978 l_parent_tracking_id NUMBER;
1979 isRoot BOOLEAN:=TRUE;
1980 l_err_msg VARCHAR2(2000);
1981 l_sql_code NUMBER;
1982 l_autosync_enabled VARCHAR2(100);
1983 BEGIN
1984
1985 BEGIN
1986 SELECT tracking_id INTO l_tracking_id
1987 FROM CSM_DEFERRED_NFN_INFO
1988 WHERE CLIENT_ID=p_user_name
1989 AND DEFERRED_TRAN_ID=p_tranid
1990 AND DML=p_dml_type
1991 AND SEQUENCE=p_sequence;
1992 EXCEPTION
1993 WHEN NO_DATA_FOUND THEN
1994 NULL;
1995 END;
1996
1997 IF l_tracking_id<>-1 THEN
1998 l_sql:=p_user_name||' ,'||p_tranid||' ,'||p_sequence;
1999 CSM_UTIL_PKG.LOG('This deferred record ('||l_sql||') is already being tracked by Id -'||l_tracking_id,
2000 'CSM_NOTIFICATION_EVENT_PKG.notify_deferred',FND_LOG.LEVEL_PROCEDURE);
2001 RETURN;
2002 END IF;
2003
2004
2005 SELECT primary_key_column INTO l_pk_col
2006 FROM ASG_PUB_ITEM
2007 WHERE item_id=p_pubitem;
2008
2009 IF instr(l_pk_col,',')<> 0 THEN
2010 CSM_UTIL_PKG.LOG('Mulitple Pks in '||p_pubitem||' is not supported in MFS Updatable PIs',
2011 'CSM_NOTIFICATION_EVENT_PKG.notify_deferred',FND_LOG.LEVEL_PROCEDURE);
2012 RETURN;
2013 END IF;
2014
2015
2016 l_pk_clause :='TRANID$$='||p_tranid ||' AND SEQNO$$='||p_sequence||' AND CLID$$CS='''||p_user_name||'''';
2017
2018 OPEN c_parent(p_pubitem);
2019 LOOP
2020 FETCH c_parent INTO l_parent_pi;
2021 EXIT WHEN c_parent%NOTFOUND;
2022 /* IF l_parent_pi IS NOT NULL THEN*/
2023 BEGIN
2024 l_sql:= 'SELECT TRACKING_ID FROM CSM_DEFERRED_NFN_INFO WHERE OBJECT_NAME='''||l_parent_pi ||''''
2025 ||' AND OBJECT_ID=(SELECT '||get_source_object_column(l_parent_pi,p_pubitem)|| ' FROM '
2026 ||p_pubitem||'_INQ WHERE '||l_pk_clause||' '||get_source_object_code_str(l_parent_pi,p_pubitem) ||') '
2027 ||' AND DEFERRED_TRAN_ID ='||p_tranid ||' AND CLIENT_ID='''||p_user_name||'''';
2028
2029 EXECUTE IMMEDIATE l_sql INTO l_parent_tracking_id;
2030 isROOT:=FALSE;
2031
2032 EXCEPTION
2033 WHEN NO_DATA_FOUND THEN
2034 NULL;
2035 END;
2036
2037 EXIT WHEN isROOT=FALSE; --parent found, so exit
2038 END LOOP;
2039
2040 CLOSE c_parent;
2041
2042 select CSM_DEFERRED_NFN_INFO_S.nextval into l_tracking_id from dual;
2043
2044
2045 IF isRoot AND NOT CSM_UTIL_PKG.IS_HTML5_USER(p_user_name) THEN --notify/email user
2046 --Check if auto sync is enabled
2047 BEGIN
2048 select STATUS INTO l_autosync_enabled from wf_events
2049 where name='oracle.apps.csm.download.startsync';
2050 EXCEPTION
2051 WHEN NO_DATA_FOUND THEN
2052 l_autosync_enabled:='DISABLED';
2053 END;
2054
2055 IF l_autosync_enabled = 'ENABLED' THEN
2056 BEGIN -- this email part shdn't disrupt deferred tracking
2057 IF p_dml_type='I' THEN
2058 l_mode := 'INSERT';
2059 ELSIF p_dml_type='U' THEN
2060 l_mode := 'UPDATE';
2061 ELSE
2062 l_mode := 'DELETE';
2063 END IF;
2064
2065 l_subject := 'MFS_ALERT:NOTIFICATION_ID=&'||'#NID:MODE=NEW:OBJECT_NAME=DEFERRED_TRANSACTION:TRACKING_ID='||l_tracking_id;
2066 l_body := 'MFS_ALERT_DETAILS:NOTIFICATION_ID=&'||'#NID:DEFERRED_PI_NAME='||p_pubitem||':'||l_pk_col||'='||p_pk||':DML_TYPE='
2067 ||l_mode||':ERROR_MSG='||replace(p_error_msg,':','\:')||':UPLOAD_TRANID='||p_tranid
2068 ||':REPLY_TO='||get_mailer_emailId ||':MFS_ALERT_DETAILS';
2069
2070 l_notification_id:=send_email(p_user_name,l_subject,l_body);
2071
2072 IF(l_notification_id <> -1) THEN
2073 -- insert into auto sync table
2074 INSERT INTO csm_auto_sync_nfn(USER_ID,NOTIFICATION_ID,OBJECT_NAME,OBJECT_ID,DML,REMINDERS_SENT,CREATION_DATE,CREATED_BY
2075 ,LAST_UPDATE_DATE,LAST_UPDATED_BY)
2076 VALUES(asg_base.get_user_id(p_user_name),l_notification_id,'DEFERRED_TRANSACTION',l_tracking_id,'NEW',0,sysdate,1,sysdate,1);
2077
2078 CSM_ACC_PKG.Insert_Acc
2079 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_AUTO_SYNC_NFN')
2080 ,P_ACC_TABLE_NAME => 'CSM_AUTO_SYNC_NFN_ACC'
2081 ,P_SEQ_NAME => 'CSM_AUTO_SYNC_NFN_ACC_S'
2082 ,P_PK1_NAME => 'NOTIFICATION_ID'
2083 ,P_PK1_NUM_VALUE => l_notification_id
2084 ,P_USER_ID => asg_base.get_user_id(p_user_name)
2085 );
2086
2087 CSM_UTIL_PKG.LOG('Invoke timer for '||l_notification_id,
2088 'CSM_NOTIFICATION_EVENT_PKG.notify_deferred',FND_LOG.LEVEL_PROCEDURE);
2089
2090
2091 OPEN c_get_resp_id(p_user_name);
2092 FETCH c_get_resp_id INTO l_app_id,l_resp_id;
2093 CLOSE c_get_resp_id;
2094
2095 l_timeout := to_number(fnd_profile.value_specific('CSM_ALERT_TIMEOUT_1',asg_base.get_user_id(p_user_name),l_resp_id,l_app_id));
2096
2097 IF l_timeout > 0 THEN
2098 --timer logic - Fixed 3 tries + 1 original email
2099 wf_event_t.initialize(l_wftimer);
2100 l_wftimer.AddParameterToList('NOTIFICATION_ID',to_char(l_notification_id));
2101 l_wftimer.AddParameterToList('SENT_TO','CSM_'||p_user_name||'_ROLE');
2102 l_wftimer.AddParameterToList('TRIES','1');
2103
2104 wf_event.raise(p_event_name=>'oracle.apps.csm.download.timer',
2105 p_event_key=>to_char(l_notification_id),p_parameters=>l_wftimer.getParameterList,
2106 p_event_data=>null,p_send_date=>(sysdate+(l_timeout*60*0.000011574)));
2107 END IF;
2108
2109 END IF;
2110 EXCEPTION
2111 WHEN Others THEN
2112 l_sql_code:= SQLCODE;
2113 l_err_msg:= substr(SQLERRM,1,2000);
2114 CSM_UTIL_PKG.LOG('exception while sending/tracking deferred Auto Sync email in NFN table: '||l_sql_code||':'||l_err_msg,
2115 'CSM_NOTIFICATION_EVENT_PKG.notify_deferred',FND_LOG.LEVEL_PROCEDURE);
2116 END;
2117 END IF; --auto sync enabled ?
2118 END IF; --is ROOT
2119
2120
2121 --insert into tracking table
2122
2123 INSERT INTO CSM_DEFERRED_NFN_INFO(TRACKING_ID,CLIENT_ID,NOTIFICATION_ID,OBJECT_NAME , OBJECT_ID,
2124 DEFERRED_TRAN_ID , SEQUENCE, DML , PARENT_ID , ERROR_MSG ,CREATION_DATE )
2125 VALUES(l_tracking_id,p_user_name,l_notification_id,p_pubitem,p_pk,p_tranid,p_sequence,
2126 p_dml_type,l_parent_tracking_id,p_error_msg,sysdate);
2127
2128 CSM_ACC_PKG.Insert_Acc
2129 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_DEFERRED_TRANSACTIONS')
2130 ,P_ACC_TABLE_NAME => 'CSM_DEFERRED_TRANSACTIONS_ACC'
2131 ,P_SEQ_NAME => 'CSM_DEFERRED_TXNS_ACC_S'
2132 ,P_PK1_NAME => 'TRACKING_ID'
2133 ,P_PK1_NUM_VALUE => l_tracking_id
2134 ,P_USER_ID => asg_base.get_user_id(p_user_name)
2135 );
2136
2137 --Email even if l_notification_id is -1
2138 IF isROOT THEN
2139 email_deferred_admin(l_tracking_id); --Email Admin & only html5 Techs about the root error.
2140 END IF;
2141
2142 CSM_UTIL_PKG.LOG('Inserted Tracking Id - '||l_tracking_id,'CSM_NOTIFICATION_EVENT_PKG.notify_deferred',FND_LOG.LEVEL_PROCEDURE);
2143
2144 EXCEPTION
2145 WHEN Others THEN
2146 l_sql_code:= SQLCODE;
2147 l_err_msg:= substr(SQLERRM,1,2000);
2148 CSM_UTIL_PKG.LOG('exception while processing '||NVL(l_notification_id,l_tracking_id)||' : '||l_sql_code||':'||l_err_msg,
2149 'CSM_NOTIFICATION_EVENT_PKG.notify_deferred',FND_LOG.LEVEL_PROCEDURE);
2150 CSM_UTIL_PKG.LOG('Dynamic SQL query is: '||l_sql,
2151 'CSM_NOTIFICATION_EVENT_PKG.notify_deferred',FND_LOG.LEVEL_PROCEDURE);
2152 END NOTIFY_DEFERRED;
2153
2154 PROCEDURE PURGE_USER(p_user_id IN NUMBER)
2155 IS
2156 BEGIN
2157
2158 delete from csm_auto_sync_nfn_acc where user_id=p_user_id;
2159
2160 delete from csm_deferred_transactions_acc where user_id=p_user_id;
2161
2162 delete from csm_deferred_nfn_info where client_id=csm_util_pkg.get_user_name(p_user_id);
2163
2164 for rec in (select nfn.notification_id from csm_auto_sync_nfn nfn, wf_notifications wfn
2165 where nfn.user_id=p_user_id and nfn.notification_id = wfn.notification_id
2166 and wfn.status='OPEN')
2167 loop
2168 wf_notification.respond(rec.notification_id);
2169 end loop;
2170
2171 DELETE FROM csm_client_nfn_log cl
2172 WHERE EXISTS (SELECT 1 FROM csm_auto_sync_nfn b WHERE b.NOTIFICATION_ID=cl.NOTIFICATION_ID and user_id=p_user_id);
2173
2174 delete from csm_auto_sync_nfn where user_id=p_user_id;
2175
2176 delete from CSM_SYNC_ERROR_NFN_INFO where client_id=csm_util_pkg.get_user_name(p_user_id);
2177
2178 END PURGE_USER;
2179
2180 --Subscription to event "oracle.apps.asg.sync.failure"
2181 FUNCTION EMAIL_SYNC_ERROR_ADMIN_SUB(p_subscription_guid IN RAW, p_event IN OUT NOCOPY WF_EVENT_T)
2182 RETURN VARCHAR2
2183 IS
2184
2185 l_session_id NUMBER;
2186 l_tran_id NUMBER;
2187 l_client_id VARCHAR2(100);
2188 l_dev_type VARCHAR2(20);
2189 l_dev_name VARCHAR2(240);
2190 l_err_msg VARCHAR2(2000);
2191 l_sync_date DATE;
2192
2193 CURSOR c_get_resp_id(b_user_name VARCHAR2) IS
2194 SELECT APP_ID,RESPONSIBILITY_ID
2195 FROM ASG_USER
2196 WHERE USER_NAME=b_user_name;
2197
2198 l_user_string VARCHAR2(4000);
2199 l_user_list asg_download.pk_list;
2200 l_resp_id NUMBER;
2201 l_app_id NUMBER;
2202 l_notification_id NUMBER;
2203
2204 l_sql VARCHAR2(1000);
2205 l_sql_err_msg VARCHAR2(2000);
2206 l_sql_code NUMBER;
2207 l_wf_param wf_event_t;
2208 BEGIN
2209
2210 l_session_id := to_number(p_event.GetValueForParameter('SESSION_ID'));
2211 l_tran_id := to_number(p_event.GetValueForParameter('TRAN_ID'));
2212 l_client_id := p_event.GetValueForParameter('CLIENT_ID');
2213 l_err_msg := p_event.GetValueForParameter('ERROR_MSG');
2214 l_sync_date :=to_date(p_event.GetValueForParameter('SYNC_DATE'),'DD-MM-RRRR HH24:MI:SS');
2215 l_dev_type := p_event.GetValueForParameter('DEVICE_TYPE');
2216
2217 BEGIN
2218 l_sql:= 'select upper(device_name) from (select a.NAME as DEVICE_NAME '
2219 ||' 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 '
2220 ||' 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';
2221
2222 EXECUTE IMMEDIATE l_sql INTO l_dev_name USING l_client_id;
2223 EXCEPTION
2224 WHEN Others THEN
2225 l_dev_name := 'NULL';
2226 END;
2227
2228 BEGIN
2229 OPEN c_get_resp_id(l_client_id);
2230 FETCH c_get_resp_id INTO l_app_id,l_resp_id;
2231 CLOSE c_get_resp_id;
2232
2233 SELECT fnd_profile.value_specific('CSM_NFN_SYNC_ERROR',NULL,l_resp_id,l_app_id)
2234 INTO l_user_string FROM DUAL;
2235
2236 IF l_user_string IS NULL THEN
2237 CSM_UTIL_PKG.LOG('No email is sent to the administrator since the profile CSM_NFN_SYNC_ERROR is NULL for technician.'
2238 ||' Sending email only to Technician - '|| l_client_id,'CSM_NOTIFICATION_EVENT_PKG.EMAIL_SYNC_ERROR_ADMIN_SUB',FND_LOG.LEVEL_PROCEDURE);
2239 l_user_string:= l_client_id;
2240 ELSIF(instr(l_user_string,l_client_id) = 0) THEN
2241 l_user_string:=l_user_string||','||l_client_id;
2242 END IF;
2243 EXCEPTION
2244 WHEN Others THEN
2245 CSM_UTIL_PKG.LOG('No email is sent to the administrator since the profile CSM_NFN_SYNC_ERROR is NULL.'
2246 ||' Sending email only to Technician '||l_client_id,'CSM_NOTIFICATION_EVENT_PKG.EMAIL_SYNC_ERROR_ADMIN_SUB',FND_LOG.LEVEL_PROCEDURE);
2247
2248 l_user_string :=l_client_id;
2249 END;
2250
2251 l_user_list := asg_download.get_listfrom_string(upper(l_user_string));
2252
2253 wf_event_t.initialize(l_wf_param);
2254 l_wf_param.AddParameterToList('TEMPLATE','SYNC_ERROR_REPORT');
2255
2256 l_wf_param.AddParameterToList('SESSION_ID', l_session_id);
2257 l_wf_param.AddParameterToList('TRAN_ID', l_tran_id);
2258 l_wf_param.AddParameterToList('USER_NAME', l_client_id);
2259 l_wf_param.AddParameterToList('DEVICE_TYPE', l_dev_type);
2260 l_wf_param.AddParameterToList('DEVICE_NAME', l_dev_name);
2261 l_wf_param.AddParameterToList('ERROR_MSG', NVL(l_err_msg,'NULL'));
2262 l_wf_param.AddParameterToList('SYNC_DATE', to_char(l_sync_date,'DD-MON-RRRR HH24:MI:SS')||getGMTDeviation(l_sync_date));
2263
2264 FOR I IN 1..l_user_list.COUNT
2265 LOOP
2266 BEGIN
2267 CSM_UTIL_PKG.LOG('Sending sync error report email to '||l_user_list(I),
2268 'CSM_NOTIFICATION_EVENT_PKG.EMAIL_SYNC_ERROR_ADMIN_SUB',FND_LOG.LEVEL_PROCEDURE);
2269
2270 l_notification_id := invoke_WF_NotifyProcess(l_user_list(I),l_wf_param);
2271
2272 IF l_notification_id <> -1 THEN
2273 INSERT INTO CSM_SYNC_ERROR_NFN_INFO(NOTIFICATION_ID,RECIPIENT_NAME, SYNC_SESSION_ID ,CLIENT_ID)
2274 VALUES(l_notification_id,l_user_list(I),l_session_id,l_client_id);
2275
2276 DOWNLOAD_NOTIFICATION(l_notification_id,l_sql_err_msg); --download notification to client
2277 ELSE
2278 CSM_UTIL_PKG.LOG('Invoke Wf process returns -1. No notification sent to '||l_user_list(I),
2279 'CSM_NOTIFICATION_EVENT_PKG.EMAIL_SYNC_ERROR_ADMIN_SUB',FND_LOG.LEVEL_EXCEPTION);
2280 END IF;
2281 EXCEPTION
2282 WHEN OTHERS THEN
2283 l_sql_code:= SQLCODE;
2284 l_sql_err_msg:= substr(SQLERRM,1,2000);
2285 CSM_UTIL_PKG.LOG('exception while sending notification to '||l_user_list(I)||' : '||l_sql_code||':'||l_sql_err_msg,
2286 'CSM_NOTIFICATION_EVENT_PKG.EMAIL_SYNC_ERROR_ADMIN_SUB',FND_LOG.LEVEL_EXCEPTION);
2287 END;
2288 END LOOP;
2289
2290 RETURN 'SUCCESS';
2291 EXCEPTION
2292 WHEN Others THEN
2293 l_sql_code:= SQLCODE;
2294 l_sql_err_msg:= substr(SQLERRM,1,2000);
2295 CSM_UTIL_PKG.LOG('exception while emailing admin : '||l_sql_code||':'||l_sql_err_msg,
2296 'CSM_NOTIFICATION_EVENT_PKG.EMAIL_SYNC_ERROR_ADMIN_SUB',FND_LOG.LEVEL_EXCEPTION);
2297 CSM_UTIL_PKG.LOG('Dynamic SQL query composed is :'||l_sql,
2298 'CSM_NOTIFICATION_EVENT_PKG.EMAIL_SYNC_ERROR_ADMIN_SUB',FND_LOG.LEVEL_EXCEPTION);
2299 RETURN 'ERROR';
2300 END EMAIL_SYNC_ERROR_ADMIN_SUB;
2301
2302 PROCEDURE EMAIL_SYNC_ERRORS_CONC(p_status OUT NOCOPY VARCHAR2, p_message OUT NOCOPY VARCHAR2)
2303 IS
2304 PRAGMA AUTONOMOUS_TRANSACTION;
2305 l_session_id NUMBER;
2306 l_client_id VARCHAR2(100);
2307 l_dev_type VARCHAR2(100);
2308 l_dev_name VARCHAR2(240);
2309 l_err_msg VARCHAR2(4000);
2310 l_sync_date DATE;
2311
2312 CURSOR c_get_resp_id(b_user_name VARCHAR2) IS
2313 SELECT APP_ID,RESPONSIBILITY_ID
2314 FROM ASG_USER
2315 WHERE USER_NAME=b_user_name;
2316
2317 l_user_string VARCHAR2(4000);
2318 l_user_list asg_download.pk_list;
2319 l_resp_id NUMBER;
2320 l_app_id NUMBER;
2321 l_notification_id NUMBER;
2322 l_curr_client_id VARCHAR2(100);
2323 l_last_run_date DATE;
2324
2325
2326 l_sql VARCHAR2(1000);
2327 l_sql_err_msg VARCHAR2(2000);
2328 l_sql_code NUMBER;
2329
2330 type t_curs is ref cursor;
2331 cur t_curs;
2332
2333 l_wf_param wf_event_t;
2334 BEGIN
2335
2336 SELECT NVL(LAST_RUN_DATE,TO_DATE(1,'J')) INTO l_last_run_date
2337 FROM jtm_con_request_data
2338 WHERE product_code = 'CSM'
2339 AND package_name = 'CSM_NOTIFICATION_EVENT_PKG'
2340 AND procedure_name = 'EMAIL_SYNC_ERRORS_CONC';
2341
2342
2343 OPEN cur FOR 'SELECT SESSION_ID,client_id,DECODE(DEVICE_PLATFORM,''WCE'',''WINCE'',''LAPTOP'') as DEVICE_TYPE,
2344 dbms_lob.substr(message,1990,1)||''...'' as ERROR_MSG, START_TIME AS SYNC_DATE
2345 FROM '||asg_base.G_OLITE_SCHEMA||'.c$sync_history HIST , ASG_USER au
2346 WHERE RESULT<>''SUCCESS''
2347 AND trim(CLIENT_ID) IS NOT NULL
2348 AND CLIENT_ID = AU.USER_NAME
2349 AND START_TIME > AU.CREATION_DATE
2350 AND START_TIME > :1
2351 AND NOT EXISTS(SELECT 1 FROM CSM_SYNC_ERROR_NFN_INFO
2352 WHERE SYNC_SESSION_ID=HIST.SESSION_ID) ORDER BY CLIENT_ID,SESSION_ID' USING l_last_run_date;
2353
2354 LOOP
2355 FETCH cur INTO l_session_id ,l_client_id,l_dev_type,l_err_msg,l_sync_date;
2356 EXIT WHEN cur%NOTFOUND;
2357
2358
2359 IF(l_curr_client_id IS NULL OR l_curr_client_id<>l_client_id) THEN
2360
2361 CSM_UTIL_PKG.LOG('Processing for user '||l_client_id,
2362 'CSM_NOTIFICATION_EVENT_PKG.EMAIL_SYNC_ERRORS_CONC',FND_LOG.LEVEL_PROCEDURE);
2363
2364 l_curr_client_id := l_client_id;
2365 BEGIN
2366 l_sql:= 'select upper(device_name) from (select a.NAME as DEVICE_NAME '
2367 ||' 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 '
2368 ||' 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';
2369
2370 EXECUTE IMMEDIATE l_sql INTO l_dev_name USING l_client_id;
2371 EXCEPTION
2372 WHEN OTHERS THEN
2373 l_dev_name := 'NULL';
2374 END;
2375
2376 BEGIN
2377 OPEN c_get_resp_id(l_client_id);
2378 FETCH c_get_resp_id INTO l_app_id,l_resp_id;
2379 CLOSE c_get_resp_id;
2380
2381 SELECT fnd_profile.value_specific('CSM_NFN_SYNC_ERROR',NULL,l_resp_id,l_app_id)
2382 INTO l_user_string FROM DUAL;
2383
2384 IF l_user_string IS NULL THEN
2385 CSM_UTIL_PKG.LOG('No email is sent to the administrator since the profile CSM_NFN_SYNC_ERROR is NULL for technician.'
2386 ||' Sending email only to Technician - '|| l_client_id,'CSM_NOTIFICATION_EVENT_PKG.EMAIL_SYNC_ERRORS_CONC',FND_LOG.LEVEL_PROCEDURE);
2387 l_user_string:= l_client_id;
2388 ELSIF(instr(l_user_string,l_client_id) = 0) THEN
2389 l_user_string:=l_user_string||','||l_client_id;
2390 END IF;
2391 EXCEPTION
2392 WHEN Others THEN
2393 CSM_UTIL_PKG.LOG('No email is sent to the administrator since the profile CSM_NFN_SYNC_ERROR is NULL for technician.'
2394 ||' Sending email only to Technician - '|| l_client_id,'CSM_NOTIFICATION_EVENT_PKG.EMAIL_SYNC_ERRORS_CONC',FND_LOG.LEVEL_PROCEDURE);
2395 l_user_string:= l_client_id;
2396 END;
2397
2398 IF(l_user_list.COUNT>0) THEN
2399 l_user_list.DELETE;
2400 END IF;
2401
2402 l_user_list := asg_download.get_listfrom_string(upper(l_user_string));
2403
2404 END IF;
2405
2406 wf_event_t.initialize(l_wf_param);
2407 l_wf_param.AddParameterToList('TEMPLATE','SYNC_ERROR_REPORT');
2408
2409 l_wf_param.AddParameterToList('SESSION_ID', to_char(l_session_id));
2410 l_wf_param.AddParameterToList('USER_NAME', l_client_id);
2411 l_wf_param.AddParameterToList('DEVICE_TYPE', l_dev_type);
2412 l_wf_param.AddParameterToList('DEVICE_NAME', l_dev_name);
2413 l_wf_param.AddParameterToList('ERROR_MSG', NVL(l_err_msg,'NULL'));
2414 l_wf_param.AddParameterToList('SYNC_DATE', to_char(l_sync_date,'DD-MON-RRRR HH24:MI:SS')||getGMTDeviation(l_sync_date));
2415
2416 FOR I IN 1..l_user_list.COUNT
2417 LOOP
2418 BEGIN
2419 CSM_UTIL_PKG.LOG('Sending sync error report email on session '||l_session_id||' to '||l_user_list(I),
2420 'CSM_NOTIFICATION_EVENT_PKG.EMAIL_SYNC_ERRORS_CONC',FND_LOG.LEVEL_PROCEDURE);
2421
2422 l_notification_id := invoke_WF_NotifyProcess(l_user_list(I),l_wf_param);
2423
2424 IF l_notification_id <> -1 THEN
2425
2426 INSERT INTO CSM_SYNC_ERROR_NFN_INFO(NOTIFICATION_ID,RECIPIENT_NAME, SYNC_SESSION_ID ,CLIENT_ID)
2427 VALUES(l_notification_id,l_user_list(I),l_session_id,l_client_id);
2428
2429 DOWNLOAD_NOTIFICATION(l_notification_id,l_sql_err_msg); --download notification to client
2430 ELSE
2431 CSM_UTIL_PKG.LOG('Invoke Wf process returns -1. No notification sent to '||l_user_list(I),
2432 'CSM_NOTIFICATION_EVENT_PKG.EMAIL_SYNC_ERROR_ADMIN_SUB',FND_LOG.LEVEL_EXCEPTION);
2433 END IF;
2434
2435 EXCEPTION
2436 WHEN OTHERS THEN
2437 l_sql_code:= SQLCODE;
2438 l_sql_err_msg:= substr(SQLERRM,1,2000);
2439 CSM_UTIL_PKG.LOG('exception while sending notification on session '||l_session_id||' to '||l_user_list(I)||' : '||l_sql_code||':'||l_sql_err_msg,
2440 'CSM_NOTIFICATION_EVENT_PKG.EMAIL_SYNC_ERRORS_CONC',FND_LOG.LEVEL_EXCEPTION);
2441 END;
2442 END LOOP;
2443
2444 COMMIT; -- commit after every session is notified.
2445 END LOOP;
2446
2447 UPDATE jtm_con_request_data
2448 SET LAST_RUN_DATE=sysdate
2449 WHERE product_code = 'CSM'
2450 AND package_name = 'CSM_NOTIFICATION_EVENT_PKG'
2451 AND procedure_name = 'EMAIL_SYNC_ERRORS_CONC';
2452
2453 p_status := 'SUCCESS';
2454 p_message := 'CSM_NOTIFICATION_EVENT_PKG.EMAIL_SYNC_ERRORS_CONC Executed successfully';
2455
2456 COMMIT;
2457
2458 EXCEPTION
2459 WHEN Others THEN
2460 l_sql_code:= SQLCODE;
2461 l_sql_err_msg:= substr(SQLERRM,1,2000);
2462 CSM_UTIL_PKG.LOG('exception while emailing admin : '||l_sql_code||':'||l_sql_err_msg,
2463 'CSM_NOTIFICATION_EVENT_PKG.EMAIL_SYNC_ERRORS_CONC',FND_LOG.LEVEL_EXCEPTION);
2464 p_status := 'ERROR';
2465 p_message := 'Error in CSM_NOTIFICATION_EVENT_PKG.EMAIL_SYNC_ERRORS_CONC: ' ||l_sql_err_msg;
2466 ROLLBACK;
2467 END EMAIL_SYNC_ERRORS_CONC;
2468
2469 END CSM_NOTIFICATION_EVENT_PKG;