1 PACKAGE BODY CSL_WF_NOTIFICATION_AT_ACC_PKG AS
2 /* $Header: cslwaacb.pls 115.16 2002/11/08 13:59:56 asiegers ship $ */
3
4
5 /*** Globals for notification attributes ***/
6 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
7 JTM_HOOK_UTIL_PKG.t_publication_item_list('WF_NOTIFICATION_ATTR');
8 g_publication_item_name2 CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
9 JTM_HOOK_UTIL_PKG.t_publication_item_list('WF_NOTIFICATIONS');
10
11 g_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_WF_NOTIFICATION_AT_ACC';
12 g_acc_table_name2 CONSTANT VARCHAR2(30) := 'JTM_WF_NOTIFICATIONS_ACC';
13 g_table_name CONSTANT VARCHAR2(30) := 'WF_NOTIFICATION_ATTRIBUTES';
14 g_table_name2 CONSTANT VARCHAR2(30) := 'WF_NOTIFICATIONS';
15 g_pk1_name CONSTANT VARCHAR2(30) := 'NOTIFICATION_ID';
16 g_pk2_name CONSTANT VARCHAR2(30) := 'NAME';
17 g_debug_level NUMBER;
18
19 PROCEDURE INSERT_NOTIFICATION_ATTRIBUTE ( p_notification_id IN NUMBER, p_name IN VARCHAR2 )
20 IS
21
22 l_sender_user BOOLEAN;
23 l_recipient_user BOOLEAN;
24 l_sender_mobile_resource BOOLEAN;
25 l_recipient_mobile_resource BOOLEAN;
26
27 /*** Cursor for retrieving user id of the sender of the notification ***/
28 CURSOR c_get_sender
29 ( b_notification_id NUMBER
30 )
31 IS
32 SELECT user_id
33 FROM fnd_user usr
34 , wf_notifications wno
35 , wf_notification_attributes wna
36 WHERE usr.user_name = wna.text_value
37 AND wna.notification_id = wno.notification_id
38 AND wna.notification_id = b_notification_id
39 AND wno.MESSAGE_TYPE = 'CS_MSGS'
40 AND wno.MESSAGE_NAME = 'FYI_MESSAGE'
41 AND wno.STATUS = 'OPEN'
42 AND wna.name = 'SENDER';
43
44 r_get_sender c_get_sender%ROWTYPE;
45
46 /*** Cursor for retrieving user id of the recipient of the notification ***/
47 CURSOR c_get_recipient
48 ( b_notification_id NUMBER
49 )
50 IS
51 SELECT user_id
52 FROM fnd_user usr
53 , wf_notifications wfn
54 WHERE wfn.recipient_role = usr.user_name
55 AND wfn.MESSAGE_TYPE = 'CS_MSGS'
56 AND wfn.MESSAGE_NAME = 'FYI_MESSAGE'
57 AND wfn.STATUS = 'OPEN'
58 AND wfn.notification_id = b_notification_id;
59
60 r_get_recipient c_get_recipient%ROWTYPE;
61
62 /*** Cursor to retrieve the resource belonging to a user id ***/
63 CURSOR c_get_user_resource
64 ( b_user_id NUMBER
65 )
66 IS
67 SELECT resource_id
68 FROM jtf_rs_resource_extns
69 WHERE nvl(end_date_active, sysdate) >= sysdate
70 AND category = 'EMPLOYEE'
71 AND user_id = b_user_id;
72
73 r_get_sender_resource c_get_user_resource%ROWTYPE;
74 r_get_recipient_resource c_get_user_resource%ROWTYPE;
75
76 /*** Cursor to retrieve all notification attributes for a resource ***/
77 CURSOR c_recipient_attr (b_notification_id NUMBER, b_resource_id NUMBER)
78 IS
79 SELECT notification_id , name
80 FROM JTM_WF_NOTIFICATION_AT_ACC
81 WHERE notification_id = b_notification_id
82 AND resource_id = b_resource_id;
83
84 r_recipient_attr c_recipient_attr%ROWTYPE;
85
86 CURSOR c_notification_exists( b_notification_id NUMBER ) IS
87 SELECT access_id
88 , resource_id
89 FROM JTM_WF_NOTIFICATIONS_ACC
90 WHERE NOTIFICATION_ID = b_notification_id;
91
92 r_notification_exists c_notification_exists%ROWTYPE;
93
94 BEGIN
95
96 /*** Execute necessary cursors before the notification attribute is checked and init vars. ***/
97 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
98 l_sender_user := True;
99 l_recipient_user := True;
100
101 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
102 jtm_message_log_pkg.Log_Msg
103 ( p_notification_id
104 , g_table_name
105 , 'Entering Procedure INSERT_NOTIFICATION_ATTRIBUTE'
106 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
107 END IF;
108
109 /*** First check if sender and recipient are fnd users. ***/
110 /*** Then check if sender and recipient are mobile resources. ***/
111
112 /*** Get user_id of sender ***/
113 OPEN c_get_sender ( p_notification_id );
114 FETCH c_get_sender INTO r_get_sender;
115
116 IF c_get_sender%NOTFOUND THEN
117 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
118 jtm_message_log_pkg.Log_Msg
119 ( p_notification_id
120 , g_table_name
121 , 'Notification is not an open CS_MSGS/FYI_MESSAGE or sender user can not be found'
122 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
123 END IF;
124 l_sender_user := False;
125 END IF;
126 CLOSE c_get_sender;
127
128 /*** Get user_id of recipient ***/
129 OPEN c_get_recipient ( p_notification_id );
130 FETCH c_get_recipient INTO r_get_recipient;
131
132 IF c_get_recipient%NOTFOUND THEN
133 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
134 jtm_message_log_pkg.Log_Msg
135 ( p_notification_id
136 , g_table_name2
137 , 'Notification is not an open CS_MSGS/FYI_MESSAGE or recipient user can not be found'
138 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
139 END IF;
140 l_recipient_user := False;
141 END IF;
142 CLOSE c_get_recipient;
143
144 /*** If the sender is a user then check if it is a resource and a mobile resource ***/
145 IF l_sender_user THEN
146 OPEN c_get_user_resource (r_get_sender.user_id);
147 FETCH c_get_user_resource INTO r_get_sender_resource;
148
149 IF c_get_user_resource%NOTFOUND THEN
150 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
151 jtm_message_log_pkg.Log_Msg
152 ( p_notification_id
153 , g_table_name
154 , 'Sender of notification is not a resource.'
155 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
156 END IF;
157 /*** If sender is not a resource then it is also not a mobile resource ***/
158 l_sender_mobile_resource := False;
159 ELSE
160 /*** Check if sender is a mobile resource ***/
161 l_sender_mobile_resource := JTM_HOOK_UTIL_PKG.isMobileFSresource(r_get_sender_resource.resource_id);
162 END IF;
163 CLOSE c_get_user_resource;
164 ELSE
165 /*** If sender is not a user then it cannot be a resource and also not a mobile resource ***/
166 l_sender_mobile_resource := False;
167 END IF;
168
169 /*** If the recipient is a user then check if it is a mobile resource ***/
170 IF l_recipient_user THEN
171 OPEN c_get_user_resource (r_get_recipient.user_id);
172 FETCH c_get_user_resource INTO r_get_recipient_resource;
173
174 IF c_get_user_resource%NOTFOUND THEN
175 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
176 jtm_message_log_pkg.Log_Msg
177 ( p_notification_id
178 , g_table_name
179 , 'Recipient of notification is not a resource.'
180 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
181 END IF;
182 /*** If recipient is not a resource then it is also not a mobile resource ***/
183 l_recipient_mobile_resource := False;
184 ELSE
185 /*** Check if recipient is a mobile resource ***/
186 l_recipient_mobile_resource := JTM_HOOK_UTIL_PKG.isMobileFSresource(r_get_recipient_resource.resource_id);
187 END IF;
188 CLOSE c_get_user_resource;
189 ELSE
190 /*** If recipient is not a user then it cannot be a resource and also not a mobile resource ***/
191 l_recipient_mobile_resource := False;
192 END IF;
193
194 /* INSERT NOTIFICATION ATTRIBUTE */
195 /* There are 4 possible values for p_name: 'SENDER', 'DELETE_FLAG', 'READ_FLAG' or 'MESSAGE_TEXT'. */
196 /* First all specific code is executed depending of the value for p_name, then all common code. */
197 /* Specific code for Attribute Name = 'SENDER' */
198
199 IF (p_name = 'SENDER' AND l_sender_mobile_resource) THEN
200 OPEN c_notification_exists( p_notification_id );
201 FETCH c_notification_exists INTO r_notification_exists;
202 IF c_notification_exists%NOTFOUND THEN
203 /*** Notification id is not in ACC table yet: Insert ***/
204 CSL_WF_NOTIFICATIONS_ACC_PKG.INSERT_NOTIFICATION( p_notification_id );
205 END IF;
206 CLOSE c_notification_exists;
207 END IF;
208
209 /*** Specific code for Attribute Name = 'DELETE_FLAG' ***/
210 IF p_name = 'DELETE_FLAG' THEN
211 IF l_recipient_mobile_resource THEN
212 /*** Delete notification id for recipient from Notification ACC table. ***/
213 JTM_HOOK_UTIL_PKG.Delete_Acc
214 ( p_publication_item_names => g_publication_item_name2
215 , p_acc_table_name => g_acc_table_name2
216 , p_pk1_name => g_pk1_name
217 , p_pk1_num_value => p_notification_id
218 , p_resource_id => r_get_recipient_resource.resource_id
219 );
220
221 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
222 jtm_message_log_pkg.Log_Msg
223 ( p_notification_id
224 , g_table_name2
225 , 'Deleted recipient notification id from Notification Attribute ACC table.'
226 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
227 END IF;
228
229 /* Delete all attributes of notification id of recipient from Notification Attribute ACC table. */
230 /* Retrieve all attributes, loop through them and call JTM_HOOK_UTIL_PKG.Delete_Acc for all attributes.*/
231 OPEN c_recipient_attr(p_notification_id, r_get_recipient_resource.resource_id);
232 FETCH c_recipient_attr INTO r_recipient_attr;
233 IF c_recipient_attr%NOTFOUND THEN
234 /*** could not find any notification attribute records to be deleted ***/
235 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
236 jtm_message_log_pkg.Log_Msg
237 ( p_notification_id
238 , g_table_name
239 , 'Did not find any Notification Attribute records to be deleted for recipient.'
240 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
241 END IF;
242 CLOSE c_recipient_attr;
243 ELSE
244 /*** Loop over all available records and delete them from the Notification Attribute ACC table ***/
245 WHILE c_recipient_attr%FOUND LOOP
246 /*** Call delete function of JTM_HOOK_UTIL_PKG to delete records from the ACC table ***/
247 JTM_HOOK_UTIL_PKG.Delete_Acc
248 ( p_publication_item_names => g_publication_item_name
249 , p_acc_table_name => g_acc_table_name
250 , p_pk1_name => g_pk1_name
251 , p_pk1_num_value => p_notification_id
252 , p_pk2_name => g_pk2_name
253 , p_pk2_char_value => r_recipient_attr.name
254 , p_resource_id => r_get_recipient_resource.resource_id
255 );
256
257 FETCH c_recipient_attr INTO r_recipient_attr;
258 END LOOP;
259 CLOSE c_recipient_attr;
260
261 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
262 jtm_message_log_pkg.Log_Msg
263 ( p_notification_id
264 , g_table_name
265 , 'Deleted all records for recipient from Notification Attribute ACC table.'
266 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
267 END IF;
268 END IF;
269 ELSE
270 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
271 jtm_message_log_pkg.Log_Msg
272 ( p_notification_id
273 , g_table_name
274 , 'Recipient of notification is not a Mobile Resource: No deletion of notification from ACC table.'
275 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
276 END IF;
277 END IF;
278 END IF;
279
280
281 /* No specific code has to be executed for Notification Attribute Names: 'READ_FLAG' and 'MESSAGE_TEXT' */
282 /* Common code for Notification Attribute Name is 'SENDER' and 'MESSAGE_TEXT': */
283 /* Insert Notification Attribute id and name into ACC table for Notification Attributes. */
284 IF ((( p_name = 'SENDER')
285 OR ( p_name = 'MESSAGE_TEXT')
286 OR ( p_name = 'PRIORITY')
287 OR ( p_name = 'SUBJECT'))
288 AND l_sender_mobile_resource) THEN
289
290 /*** Do an insert into ACC table for sender of notification attribute ***/
291 JTM_HOOK_UTIL_PKG.Insert_Acc
292 ( p_publication_item_names => g_publication_item_name
293 , p_acc_table_name => g_acc_table_name
294 , p_pk1_name => g_pk1_name
295 , p_pk1_num_value => p_notification_id
296 , p_pk2_name => g_pk2_name
297 , p_pk2_char_value => p_name
298 , p_resource_id => r_get_sender_resource.resource_id
299 );
300
301 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
302 jtm_message_log_pkg.Log_Msg
303 ( p_notification_id
304 , g_table_name
305 , 'Inserted attributes for sender notification ' || p_notification_id|| ' + ' || p_name
306 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
307 END IF;
308 END IF;
309
310
311 /*** Common code for Notification Attribute Name is 'SENDER' and 'READ_FLAG': ***/
312 /*** Insert Notification Attribute id and name into ACC table for Notification Attributes. ***/
313 IF ( ((p_name = 'SENDER')
314 OR (p_name = 'READ_FLAG')
315 OR (p_name = 'MESSAGE_TEXT')
316 OR (p_name = 'SUBJECT')
317 OR (p_name = 'PRIORITY'))
318 AND l_recipient_mobile_resource) THEN
319
320 /*** Insert recipient notification id and name into Notification Attribute ACC table. ***/
321 JTM_HOOK_UTIL_PKG.Insert_Acc
322 ( p_publication_item_names => g_publication_item_name
323 , p_acc_table_name => g_acc_table_name
324 , p_pk1_name => g_pk1_name
325 , p_pk1_num_value => p_notification_id
326 , p_pk2_name => g_pk2_name
327 , p_pk2_char_value => p_name
328 , p_resource_id => r_get_recipient_resource.resource_id
329 );
330
331 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
332 jtm_message_log_pkg.Log_Msg
333 ( p_notification_id
334 , g_table_name
335 , 'Inserted recipient notification id and name into Notification Attribute ACC table.'
336 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
337 END IF;
338 END IF;
339
340 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
341 jtm_message_log_pkg.LOG_MSG
342 ( p_notification_id
343 , g_table_name
344 , 'Leaving Procedure INSERT_NOTIFICATION_ATTRIBUTE'
345 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
346 END IF;
347 END INSERT_NOTIFICATION_ATTRIBUTE;
348
349 /******/
350
351 PROCEDURE Insert_All_ACC_Records(
352 p_resource_id IN NUMBER,
353 x_return_status OUT NOCOPY VARCHAR2
354 )
355 IS
356
357 CURSOR c_notification_sender (b_resource_id NUMBER) IS
358 SELECT DISTINCT WNO.NOTIFICATION_ID
359 FROM WF_NOTIFICATIONS WNO,
360 WF_NOTIFICATION_ATTRIBUTES WNA,
361 FND_USER USR,
362 ASG_USER ADU
363 WHERE WNO.NOTIFICATION_ID = WNA.NOTIFICATION_ID
364 AND WNA.NAME = 'SENDER'
365 AND WNO.MESSAGE_TYPE = 'CS_MSGS'
366 AND WNO.MESSAGE_NAME = 'FYI_MESSAGE'
367 AND WNO.STATUS = 'OPEN'
368 AND WNA.TEXT_VALUE = USR.USER_NAME
369 AND USR.USER_ID = ADU.USER_ID
370 AND ADU.RESOURCE_ID = b_resource_id;
371 r_notification_sender c_notification_sender%ROWTYPE;
372
373 CURSOR c_notification_receive (b_resource_id NUMBER) IS
374 SELECT DISTINCT WNO.NOTIFICATION_ID
375 FROM WF_NOTIFICATIONS WNO,
376 FND_USER USR,
377 ASG_USER ADU
378 WHERE WNO.RECIPIENT_ROLE = USR.USER_NAME
379 AND USR.USER_ID = ADU.USER_ID
380 AND ADU.RESOURCE_ID = b_resource_id
381 AND WNO.STATUS = 'OPEN'
382 AND WNO.MESSAGE_TYPE = 'CS_MSGS'
383 AND WNO.MESSAGE_NAME = 'FYI_MESSAGE'
384 AND NOT EXISTS
385 ( SELECT NULL
386 FROM WF_NOTIFICATION_ATTRIBUTES WNA_DEL
387 WHERE WNA_DEL.NOTIFICATION_ID = WNO.NOTIFICATION_ID
388 AND WNA_DEL.NAME = 'DELETE_FLAG')
389 AND EXISTS
390 ( SELECT NULL
391 FROM WF_NOTIFICATION_ATTRIBUTES WNA_DEL
392 WHERE WNA_DEL.NOTIFICATION_ID = WNO.NOTIFICATION_ID
393 AND WNA_DEL.NAME = 'MESSAGE_TEXT')
394 AND EXISTS
395 ( SELECT NULL
396 FROM WF_NOTIFICATION_ATTRIBUTES WNA_DEL
397 WHERE WNA_DEL.NOTIFICATION_ID = WNO.NOTIFICATION_ID
398 AND WNA_DEL.NAME = 'SENDER'
399 AND WNA_DEL.TEXT_VALUE IN (
400 SELECT USER_NAME
401 FROM FND_USER
402 )
403 );
404 r_notification_receive c_notification_receive%ROWTYPE;
405
406 CURSOR c_get_attribute_name (p_notification_id NUMBER) IS
407 SELECT NAME
408 FROM WF_NOTIFICATION_ATTRIBUTES
409 WHERE NOTIFICATION_ID = p_notification_id;
410 r_get_attribute_name c_get_attribute_name%ROWTYPE;
411
412 l_return_value VARCHAR2(2000) := FND_API.G_RET_STS_SUCCESS;
413
414 BEGIN
415
416 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
417
418 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
419 jtm_message_log_pkg.Log_Msg
420 ( v_object_id => p_resource_id
421 , v_object_name => g_table_name
422 , v_message => 'Entering Insert_All_ACC_Records procedure for user: ' || p_resource_id
423 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
424 END IF;
425 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
426
427 jtm_message_log_pkg.Log_Msg
428 ( v_object_id => p_resource_id
429 , v_object_name => g_table_name
430 , v_message => 'Insert all Notification acc and Notification Attributes ACC records for user: '||
431 p_resource_id
432 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
433 END IF;
434
435 IF JTM_HOOK_UTIL_PKG.isMobileFSresource( p_resource_id ) THEN
436 /*** First insert the send records of a mobile user ***/
437 OPEN c_notification_sender ( p_resource_id );
438 FETCH c_notification_sender INTO r_notification_sender;
439 IF c_notification_sender%NOTFOUND THEN
440 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
441 jtm_message_log_pkg.Log_Msg
442 ( v_object_id => p_resource_id
443 , v_object_name => g_table_name
444 , v_message => 'Insert all Notification ACC: no send-records found for user: ' || p_resource_id
445 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
446 END IF;
447 ELSE
448 WHILE c_notification_sender%FOUND LOOP
449 CSL_WF_NOTIFICATIONS_ACC_PKG.Insert_Notification(r_notification_sender.notification_id);
450 OPEN c_get_attribute_name ( r_notification_sender.notification_id );
451 FETCH c_get_attribute_name INTO r_get_attribute_name;
452 IF c_get_attribute_name%NOTFOUND THEN
453 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
454 jtm_message_log_pkg.Log_Msg
455 ( v_object_id => p_resource_id
456 , v_object_name => g_table_name
457 , v_message => 'No Attributes records found for notification: ' ||
458 r_notification_sender.notification_id
459 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
460 END IF;
461 ELSE
462 WHILE c_get_attribute_name%FOUND LOOP
463 Insert_Notification_Attribute(r_notification_sender.notification_id,r_get_attribute_name.name);
464 FETCH c_get_attribute_name INTO r_get_attribute_name;
465 END LOOP;
466 CLOSE c_get_attribute_name;
467 END IF;
468 FETCH c_notification_sender INTO r_notification_sender;
469 END LOOP;
470 CLOSE c_notification_sender;
471 l_return_value := FND_API.G_RET_STS_SUCCESS;
472 END IF;
473 /*** Second insert all received records ***/
474 OPEN c_notification_receive ( p_resource_id );
475 FETCH c_notification_receive INTO r_notification_receive;
476 IF c_notification_receive%NOTFOUND THEN
477 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
478 jtm_message_log_pkg.Log_Msg
479 ( v_object_id => p_resource_id
480 , v_object_name => g_table_name
481 , v_message => 'No received records found for user : ' || p_resource_id
482 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
483 END IF;
484 ELSE
485 WHILE c_notification_receive%FOUND LOOP
486 CSL_WF_NOTIFICATIONS_ACC_PKG.Insert_Notification(r_notification_receive.notification_id);
487 OPEN c_get_attribute_name ( r_notification_receive.notification_id );
488 FETCH c_get_attribute_name INTO r_get_attribute_name;
489 IF c_get_attribute_name%NOTFOUND THEN
490 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
491 jtm_message_log_pkg.Log_Msg
492 ( v_object_id => p_resource_id
493 , v_object_name => g_table_name
494 , v_message => 'No received Notification Attributes records found for notification: '||
495 r_notification_receive.notification_id
496 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
497 END IF;
498 ELSE
499 WHILE c_get_attribute_name%FOUND LOOP
500 Insert_Notification_Attribute(r_notification_receive.notification_id,r_get_attribute_name.name);
501 FETCH c_get_attribute_name INTO r_get_attribute_name;
502 END LOOP;
503 CLOSE c_get_attribute_name;
504 END IF;
505 FETCH c_notification_receive INTO r_notification_receive;
506 END LOOP;
507 CLOSE c_notification_receive;
508 END IF;
509 l_return_value := FND_API.G_RET_STS_SUCCESS;
510 END IF;
511
512 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
513 jtm_message_log_pkg.Log_Msg
514 ( v_object_id => p_resource_id
515 , v_object_name => g_table_name
516 , v_message => 'Leaving Insert_All_ACC_Records procedure for user: ' || p_resource_id
517 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
518 END IF;
519
520 x_return_status := l_return_value;
521 EXCEPTION WHEN OTHERS THEN
522 /*** hook failed -> log error ***/
523 fnd_msg_pub.Add_Exc_Msg('CSL_CSP_REQ_HEADERS_ACC_PKG','Insert_All_ACC_Records',sqlerrm);
524 -- x_return_status := FND_API.G_RET_STS_ERROR;
525 x_return_status := FND_API.G_RET_STS_SUCCESS;
526
527 END Insert_All_ACC_Records;
528
529 PROCEDURE Delete_All_ACC_Records(
530 p_resource_id IN NUMBER,
531 x_return_status OUT NOCOPY VARCHAR2
532 )
533 IS
534
535 CURSOR c_notification_sender (b_resource_id NUMBER) IS
536 SELECT DISTINCT WNO.NOTIFICATION_ID,
537 USR.USER_ID
538 FROM WF_NOTIFICATIONS WNO,
539 WF_NOTIFICATION_ATTRIBUTES WNA,
540 FND_USER USR,
541 ASG_USER ADU
542 WHERE WNO.NOTIFICATION_ID = WNA.NOTIFICATION_ID
543 AND WNA.NAME = 'SENDER'
544 AND WNA.TEXT_VALUE = USR.USER_NAME
545 AND USR.USER_ID = ADU.USER_ID
546 AND ADU.RESOURCE_ID = b_resource_id;
547 r_notification_sender c_notification_sender%ROWTYPE;
548
549 CURSOR c_notification_receive (b_resource_id NUMBER) IS
550 SELECT DISTINCT WNO.NOTIFICATION_ID,
551 USR.USER_ID
552 FROM WF_NOTIFICATIONS WNO,
553 FND_USER USR,
554 ASG_USER ADU
555 WHERE WNO.RECIPIENT_ROLE = USR.USER_NAME
556 AND USR.USER_ID = ADU.USER_ID
557 AND ADU.RESOURCE_ID = b_resource_id
558 AND WNO.STATUS = 'OPEN'
559 AND NOT EXISTS
560 ( SELECT NULL
561 FROM WF_NOTIFICATION_ATTRIBUTES WNA_DEL
562 WHERE WNA_DEL.NOTIFICATION_ID = WNO.NOTIFICATION_ID
563 AND WNA_DEL.NAME = 'DELETE_FLAG');
564 r_notification_receive c_notification_receive%ROWTYPE;
565
566 CURSOR c_get_attribute_name (p_notification_id NUMBER) IS
567 SELECT NAME
568 FROM WF_NOTIFICATION_ATTRIBUTES
569 WHERE NOTIFICATION_ID = p_notification_id;
570 r_get_attribute_name c_get_attribute_name%ROWTYPE;
571
572 l_return_value VARCHAR2(2000) := FND_API.G_RET_STS_SUCCESS;
573
574 BEGIN
575 /*Get the debug level*/
576 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
577
578 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
579 jtm_message_log_pkg.Log_Msg
580 ( v_object_id => p_resource_id
581 , v_object_name => g_table_name
582 , v_message => 'Entering Delete_All_ACC_Records procedure for user: ' || p_resource_id
583 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
584 END IF;
585
586 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
587 jtm_message_log_pkg.Log_Msg
588 ( v_object_id => p_resource_id
589 , v_object_name => g_table_name
590 , v_message => 'Delete all Notification acc and Notification Attributes ACC records for user: '||
591 p_resource_id
592 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
593 END IF;
594
595 IF JTM_HOOK_UTIL_PKG.isMobileFSresource( p_resource_id ) THEN
596 /*** First delete the send records of a mobile user ***/
597 OPEN c_notification_sender ( p_resource_id );
598 FETCH c_notification_sender INTO r_notification_sender;
599 IF c_notification_sender%NOTFOUND THEN
600 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
601 jtm_message_log_pkg.Log_Msg
602 ( v_object_id => p_resource_id
603 , v_object_name => g_table_name
604 , v_message => 'No sent record found for user: ' || p_resource_id
605 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
606 END IF;
607 ELSE
608 WHILE c_notification_sender%FOUND LOOP
609 JTM_HOOK_UTIL_PKG.Delete_Acc
610 ( p_publication_item_names => g_publication_item_name
611 ,p_acc_table_name => g_acc_table_name2
612 ,p_pk1_name => g_pk1_name
613 ,p_pk1_num_value => r_notification_sender.notification_id
614 ,p_resource_id => p_resource_id
615 );
616 /*Call CSL_FND_USER_ACC_PKG to delete the sender fnd_user from the acc table*/
617 CSL_FND_USER_ACC_PKG.Delete_User(r_notification_sender.user_id , p_resource_id);
618
619 OPEN c_get_attribute_name ( r_notification_sender.notification_id );
620 FETCH c_get_attribute_name INTO r_get_attribute_name;
621 IF c_get_attribute_name%NOTFOUND THEN
622 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
623 jtm_message_log_pkg.Log_Msg
624 ( v_object_id => p_resource_id
625 , v_object_name => g_table_name
626 , v_message => 'No notification attributes found for notification: ' ||
627 r_notification_sender.notification_id
628 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
629 END IF;
630 ELSE
631 WHILE c_get_attribute_name%FOUND LOOP
632 JTM_HOOK_UTIL_PKG.Delete_Acc
633 ( p_publication_item_names => g_publication_item_name
634 , p_acc_table_name => g_acc_table_name
635 , p_pk1_name => g_pk1_name
636 , p_pk1_num_value => r_notification_sender.notification_id
637 , p_pk2_name => g_pk2_name
638 , p_pk2_char_value => r_get_attribute_name.name
639 , p_resource_id => p_resource_id
640 );
641 FETCH c_get_attribute_name INTO r_get_attribute_name;
642 END LOOP;
643 CLOSE c_get_attribute_name;
644 END IF;
645 FETCH c_notification_sender INTO r_notification_sender;
646 END LOOP;
647 CLOSE c_notification_sender;
648 l_return_value := FND_API.G_RET_STS_SUCCESS;
649 END IF;
650 /*** Second Delete all received records ***/
651 OPEN c_notification_receive ( p_resource_id );
652 FETCH c_notification_receive INTO r_notification_receive;
653 IF c_notification_receive%NOTFOUND THEN
654 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
655 jtm_message_log_pkg.Log_Msg
656 ( v_object_id => p_resource_id
657 , v_object_name => g_table_name
658 , v_message => 'No received records found for user: ' || p_resource_id
659 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
660 END IF;
661 ELSE
662 WHILE c_notification_receive%FOUND LOOP
663 JTM_HOOK_UTIL_PKG.Delete_Acc
664 ( p_publication_item_names => g_publication_item_name
665 , p_acc_table_name => g_acc_table_name2
666 , p_pk1_name => g_pk1_name
667 , p_pk1_num_value => r_notification_receive.notification_id
668 , p_resource_id => p_resource_id
669 );
670 /*Delete the receiving user*/
671 CSL_FND_USER_ACC_PKG.Delete_User(r_notification_receive.user_id , p_resource_id);
672
673 OPEN c_get_attribute_name ( r_notification_receive.notification_id );
674 FETCH c_get_attribute_name INTO r_get_attribute_name;
675 IF c_get_attribute_name%NOTFOUND THEN
676 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
677 jtm_message_log_pkg.Log_Msg
678 ( v_object_id => p_resource_id
679 , v_object_name => g_table_name
680 , v_message => 'No attributes found for notification ' ||
681 r_notification_receive.notification_id
682 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
683 END IF;
684 ELSE
685 WHILE c_get_attribute_name%FOUND LOOP
686 JTM_HOOK_UTIL_PKG.Delete_Acc
687 ( p_publication_item_names => g_publication_item_name
688 , p_acc_table_name => g_acc_table_name
689 , p_pk1_name => g_pk1_name
690 , p_pk1_num_value => r_notification_receive.notification_id
691 , p_pk2_name => g_pk2_name
692 , p_pk2_char_value => r_get_attribute_name.name
693 , p_resource_id => p_resource_id
694 );
695 FETCH c_get_attribute_name INTO r_get_attribute_name;
696 END LOOP;
697 CLOSE c_get_attribute_name;
698 END IF;
699 FETCH c_notification_receive INTO r_notification_receive;
700 END LOOP;
701 CLOSE c_notification_receive;
702 l_return_value := FND_API.G_RET_STS_SUCCESS;
703 END IF;
704 END IF;
705
706 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
707 jtm_message_log_pkg.Log_Msg
708 ( v_object_id => p_resource_id
709 , v_object_name => g_table_name
710 , v_message => 'Leaving Delete_All_ACC_Records procedure for user: ' || p_resource_id
711 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
712 END IF;
713
714 x_return_status := l_return_value;
715 EXCEPTION WHEN OTHERS THEN
716 /*** hook failed -> log error ***/
717 jtm_message_log_pkg.Log_Msg
718 ( v_object_id => p_resource_id
719 , v_object_name => g_table_name
720 , v_message => 'Error occurred in Delete_All_ACC_Records'||sqlerrm
721 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
722 fnd_msg_pub.Add_Exc_Msg('CSL_WF_NOTIFICATION_AT_ACC_PKG','Delete_All_ACC_Records',sqlerrm);
723 -- x_return_status := FND_API.G_RET_STS_ERROR;
724 x_return_status := FND_API.G_RET_STS_SUCCESS;
725
726 END Delete_All_ACC_Records;
727
728 END CSL_WF_NOTIFICATION_AT_ACC_PKG;
[Home] [Help]