[Home] [Help]
PACKAGE BODY: APPS.CSL_NOTIFICATIONS_PKG
Source
1 PACKAGE BODY CSL_NOTIFICATIONS_PKG AS
2 /* $Header: cslvnotb.pls 115.30 2002/11/08 14:00:23 asiegers ship $ */
3 error EXCEPTION;
4
5 /*** Globals ***/
6 g_object_name CONSTANT VARCHAR2(30) := 'CSL_NOTIFICATIONS_PKG';
7 g_pub_name CONSTANT VARCHAR2(30) := 'WF_NOTIFICATIONS';
8 g_debug_level NUMBER; -- debug level
9
10 CURSOR c_notification( b_user_name VARCHAR2, b_tranid NUMBER) is
11 SELECT *
12 FROM CSL_WF_NOTIFICATIONS_inq
13 WHERE tranid$$ = b_tranid
14 AND clid$$cs = b_user_name;
15
16 CURSOR c_notification_attr( b_user_name VARCHAR2, b_tranid NUMBER, b_notification_id NUMBER) is
17 SELECT *
18 FROM CSL_WF_NOTIFICATION_ATTR_INQ
19 WHERE tranid$$ = b_tranid
20 AND clid$$cs = b_user_name
21 AND notification_id = b_notification_id;
22
23 /***
24 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
25 ***/
26 PROCEDURE APPLY_INSERT
27 (
28 p_user_name IN VARCHAR2,
29 p_record IN c_notification%ROWTYPE,
30 p_error_msg OUT NOCOPY VARCHAR2,
31 x_return_status IN OUT NOCOPY VARCHAR2
32 ) IS
33 CURSOR c_fnd_user
34 ( b_user_name fnd_user.user_name%TYPE
35 )
36 IS
37 SELECT fu.user_name
38 , fu.start_date
39 , fu.end_date
40 FROM fnd_user fu
41 WHERE fu.user_name = b_user_name;
42
43 r_fnd_user c_fnd_user%ROWTYPE;
44
45 CURSOR c_attr( b_notification_id NUMBER, b_name VARCHAR2 ) IS
46 SELECT *
47 FROM WF_NOTIFICATION_ATTRIBUTES
48 WHERE NOTIFICATION_ID = b_notification_id
49 AND NAME = b_name;
50
51 r_attr c_attr%ROWTYPE;
52
53 l_notification_id NUMBER;
54 l_msg_count NUMBER;
55 l_msg_data VARCHAR2(240);
56
57 l_receiver_found BOOLEAN;
58 l_valid_receiver BOOLEAN;
59
60 BEGIN
61 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
62 jtm_message_log_pkg.Log_Msg
63 ( v_object_id => p_record.notification_id
64 , v_object_name => g_object_name
65 , v_message => 'Entering ' || g_object_name || '.APPLY_INSERT'
66 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
67 END IF;
68
69 l_receiver_found := FALSE;
70 l_valid_receiver := FALSE;
71 -- Check if receiver exists
72 OPEN c_fnd_user( b_user_name => p_record.recipient_role );
73 FETCH c_fnd_user INTO r_fnd_user;
74 IF c_fnd_user%FOUND THEN
75 l_receiver_found := TRUE;
76 ELSE
77 l_receiver_found := FALSE;
78 END IF;
79 CLOSE c_fnd_user;
80
81 IF l_receiver_found THEN
82 -- Check if receiver is valid for the current date
83 IF TRUNC(r_fnd_user.start_date) > TRUNC(SYSDATE) THEN
84 -- The receiver is not yet valid
85 x_return_status := FND_API.G_RET_STS_ERROR;
86 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
87 ( p_message => 'CSL_MAIL_RECEIVER_NOT_YET'
88 , p_token_name1 => 'MAIL_RECEIVER'
89 , p_token_value1 => p_record.recipient_role
90 , p_token_name2 => 'START_DATE'
91 , p_token_value2 => FND_DATE.Date_To_CharDT(r_fnd_user.start_date)
92 );
93 ELSIF TRUNC(r_fnd_user.end_date) < TRUNC(SYSDATE)
94 THEN
95 -- The receiver is no longer valid
96 x_return_status := FND_API.G_RET_STS_ERROR;
97 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
98 ( p_message => 'CSL_MAIL_RECEIVER_OUTDATED'
99 , p_token_name1 => 'MAIL_RECEIVER'
100 , p_token_value1 => p_record.recipient_role
101 , p_token_name2 => 'END_DATE'
102 , p_token_value2 => FND_DATE.Date_To_CharDT(r_fnd_user.end_date)
103 );
104 ELSE
105 -- The receiver is valid
106 l_valid_receiver := TRUE;
107 END IF;
108 ELSE
109 -- The receiver is unknown
110 x_return_status := FND_API.G_RET_STS_ERROR;
111 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
112 ( p_message => 'CSL_MAIL_RECEIVER_UNKNOWN'
113 , p_token_name1 => 'MAIL_RECEIVER'
114 , p_token_value1 => p_record.recipient_role
115 );
116 END IF;
117
118 IF l_valid_receiver THEN
119 -- Create a notification
120 l_notification_id := WF_NOTIFICATION.Send
121 ( role => p_record.recipient_role
122 , msg_type => 'CS_MSGS' -- Service Message
123 , msg_name => 'FYI_MESSAGE' -- ...
124 );
125
126 -- The WF_NOTIFICATION.Send API has also created the following attributes:
127 -- COMMENT, MESSAGE_TEXT, OBJECT_FORM, OBJECT_ID, OBJECT_TYPE, PRIORITY,
128 -- RESULT, SENDER.
129
130 -- Set the 'PRIORITY' attribute for the priority of the notification
131 WF_NOTIFICATION.SetAttrText
132 ( l_notification_id
133 , 'PRIORITY'
134 , p_record.priority
135 );
136
137 -- Create a 'SUBJECT' attribute for the subject of the notification
138 WF_NOTIFICATION.AddAttr
139 ( nid => l_notification_id
140 , aname => 'SUBJECT'
141 );
142
143 -- Set the 'SUBJECT' attribute
144 WF_NOTIFICATION.SetAttrText
145 ( l_notification_id
146 , 'SUBJECT'
147 , p_record.subject
148 );
149
150 /*Get the attributes for this notification ( SENDER, MESSAGE_TEXT, READ_FLAG, DELETE_FLAG )*/
151 /*Need to do this now because the record gets another PK*/
152 FOR r_notification_attr IN c_notification_attr( p_record.clid$$cs
153 , p_record.tranid$$
154 , p_record.notification_id ) LOOP
155 /*** First check if attri exists otherwise create it ***/
156 OPEN c_attr( b_notification_id => l_notification_id
157 , b_name => r_notification_attr.name );
158 FETCH c_attr INTO r_attr;
159 IF c_attr%NOTFOUND THEN
160 -- Create the attribute
161 WF_NOTIFICATION.AddAttr
162 ( nid => l_notification_id
163 , aname => r_notification_attr.name );
164 END IF;
165 CLOSE c_attr;
166 WF_NOTIFICATION.SetAttrText
167 ( l_notification_id
168 , r_notification_attr.name
169 , r_notification_attr.text_value
170 );
171 END LOOP;
172
173 x_return_status := FND_API.G_RET_STS_SUCCESS;
174
175 END IF;
176
177 /*** Call Concurrent Program to push new Notification and Attributes to mobile ***/
178 CSL_CONC_NOTIFICATION_PKG.RUN_CONCURRENT_NOTIFICATIONS;
179
180 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
181 jtm_message_log_pkg.Log_Msg
182 ( v_object_id => p_record.notification_id
183 , v_object_name => g_object_name
184 , v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT'
185 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
186 END IF;
187
188 EXCEPTION WHEN OTHERS THEN
189 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
190 jtm_message_log_pkg.Log_Msg
191 ( v_object_id => p_record.notification_id
192 , v_object_name => g_object_name
193 , v_message => 'Exception occurred in APPLY_INSERT:' || fnd_global.local_chr(10) || sqlerrm
194 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
195 END IF;
196
197 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
198 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
199 (
200 p_api_error => TRUE
201 );
202
203 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
204 jtm_message_log_pkg.Log_Msg
205 ( v_object_id => p_record.notification_id
206 , v_object_name => g_object_name
207 , v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT'
208 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
209 END IF;
210
211 x_return_status := FND_API.G_RET_STS_ERROR;
212 END APPLY_INSERT;
213
214 /***
215 This procedure is called by APPLY_CLIENT_CHANGES when an updated record is to be processed.
216 ***/
217 PROCEDURE APPLY_UPDATE
218 (
219 p_record IN c_notification%ROWTYPE,
220 p_error_msg OUT NOCOPY VARCHAR2,
221 x_return_status IN OUT NOCOPY VARCHAR2
222 ) IS
223 BEGIN
224 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
225 jtm_message_log_pkg.Log_Msg
226 ( v_object_id => p_record.notification_id
227 , v_object_name => g_object_name
228 , v_message => 'Entering ' || g_object_name || '.APPLY_UPDATE'
229 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
230 END IF;
231
232 -- No update possible ( is done in attributes ) so return success
233 x_return_status := FND_API.G_RET_STS_SUCCESS;
234
235 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
236 /*** exception occurred in API -> return errmsg ***/
237 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
238 (
239 p_api_error => TRUE
240 );
241 END IF;
242
243 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
244 jtm_message_log_pkg.Log_Msg
245 ( v_object_id => p_record.notification_id
246 , v_object_name => g_object_name
247 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
248 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
249 END IF;
250
251 EXCEPTION WHEN OTHERS THEN
252 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
253 jtm_message_log_pkg.Log_Msg
254 ( v_object_id => p_record.notification_id
255 , v_object_name => g_object_name
256 , v_message => 'Exception occurred in APPLY_UPDATE:' || fnd_global.local_chr(10) || sqlerrm
257 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
258 END IF;
259
260 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
261 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
262 (
263 p_api_error => TRUE
264 );
265
266 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
267 jtm_message_log_pkg.Log_Msg
268 ( v_object_id => p_record.notification_id -- put PK column here
269 , v_object_name => g_object_name
270 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
271 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
272 END IF;
273
274 x_return_status := FND_API.G_RET_STS_ERROR;
275 END APPLY_UPDATE;
276
277 /***
278 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
279 ***/
280 PROCEDURE APPLY_RECORD
281 (
282 p_user_name IN VARCHAR2,
283 p_record IN c_notification%ROWTYPE,
284 p_error_msg OUT NOCOPY VARCHAR2,
285 x_return_status IN OUT NOCOPY VARCHAR2
286 ) IS
287 BEGIN
288 /*** initialize return status and message list ***/
289 x_return_status := FND_API.G_RET_STS_SUCCESS;
290 FND_MSG_PUB.INITIALIZE;
291
292 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
293 jtm_message_log_pkg.Log_Msg
294 ( v_object_id => p_record.notification_id
295 , v_object_name => g_object_name
296 , v_message => 'Entering ' || g_object_name || '.APPLY_RECORD'
297 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
298 END IF;
299
300 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
301 jtm_message_log_pkg.Log_Msg
302 ( v_object_id => p_record.notification_id
303 , v_object_name => g_object_name
304 , v_message => 'Processing NOTIFICATION_ID = ' || p_record.notification_id || fnd_global.local_chr(10) ||
305 'DMLTYPE = ' || p_record.dmltype$$
306 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
307 END IF;
308
309 IF p_record.dmltype$$='I' THEN
310 -- Process insert
311 APPLY_INSERT
312 (
313 p_user_name,
314 p_record,
315 p_error_msg,
316 x_return_status
317 );
318 ELSIF p_record.dmltype$$='U' THEN
319 -- Process update
320 APPLY_UPDATE
321 (
322 p_record,
323 p_error_msg,
324 x_return_status
325 );
326 ELSIF p_record.dmltype$$='D' THEN
327 -- Process delete; not supported for this entity
328 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
329 jtm_message_log_pkg.Log_Msg
330 ( v_object_id => p_record.notification_id
331 , v_object_name => g_object_name
332 , v_message => 'Delete is not supported for this entity'
333 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
334 END IF;
335
336 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
337 (
338 p_message => 'CSL_DML_OPERATION'
339 , p_token_name1 => 'DML'
340 , p_token_value1 => p_record.dmltype$$
341 );
342
343 x_return_status := FND_API.G_RET_STS_ERROR;
344 ELSE
345 -- invalid dml type
346 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
347 jtm_message_log_pkg.Log_Msg
348 ( v_object_id => p_record.notification_id
349 , v_object_name => g_object_name
350 , v_message => 'Invalid DML type: ' || p_record.dmltype$$
351 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
352 END IF;
353
354 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
355 (
356 p_message => 'CSL_DML_OPERATION'
357 , p_token_name1 => 'DML'
358 , p_token_value1 => p_record.dmltype$$
359 );
360
361 x_return_status := FND_API.G_RET_STS_ERROR;
362 END IF;
363
364 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
365 jtm_message_log_pkg.Log_Msg
366 ( v_object_id => p_record.notification_id
367 , v_object_name => g_object_name
368 , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
369 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
370 END IF;
371 EXCEPTION WHEN OTHERS THEN
372 /*** defer record when any process exception occurs ***/
373 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
374 jtm_message_log_pkg.Log_Msg
375 ( v_object_id => p_record.notification_id
376 , v_object_name => g_object_name
377 , v_message => 'Exception occurred in APPLY_RECORD:' || fnd_global.local_chr(10) || sqlerrm
378 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
379 END IF;
380
381 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
382 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
383 (
384 p_api_error => TRUE
385 );
386
387 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
388 jtm_message_log_pkg.Log_Msg
389 ( v_object_id => p_record.notification_id
390 , v_object_name => g_object_name
391 , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
392 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
393 END IF;
394
395 x_return_status := FND_API.G_RET_STS_ERROR;
396 END APPLY_RECORD;
397
398 /***
399 This procedure is called by CSL_SERVICEL_WRAPPER_PKG when publication item WF_NOTIFICATIONS
400 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
401 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
402 public APIs.
403 ***/
404 PROCEDURE APPLY_CLIENT_CHANGES
405 (
406 p_user_name IN VARCHAR2,
407 p_tranid IN NUMBER,
408 p_debug_level IN NUMBER,
409 x_return_status IN OUT NOCOPY VARCHAR2
410 ) IS
411
412 l_process_status VARCHAR2(1);
413 l_error_msg VARCHAR2(4000);
414 BEGIN
415 g_debug_level := p_debug_level;
416 x_return_status := FND_API.G_RET_STS_SUCCESS;
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 => null
421 , v_object_name => g_object_name
422 , v_message => 'Entering ' || g_object_name || '.Apply_Client_Changes'
423 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
424 END IF;
425
426 /*** loop through WF_NOTIFICATION records in inqueue ***/
427 FOR r_notification IN c_notification( p_user_name, p_tranid) LOOP
428
429 SAVEPOINT save_rec;
430
431 /*** apply record ***/
432 APPLY_RECORD
433 (
434 p_user_name
435 , r_notification
436 , l_error_msg
437 , l_process_status
438 );
439
440 /*** was record processed successfully? ***/
441 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
442 /*** Yes -> reject record because of changed pk ***/
443 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
444 jtm_message_log_pkg.Log_Msg
445 ( v_object_id => r_notification.notification_id
446 , v_object_name => g_object_name
447 , v_message => 'Record successfully processed, rejecting record'
448 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
449 END IF;
450
451 CSL_SERVICEL_WRAPPER_PKG.REJECT_RECORD
452 (
453 p_user_name,
454 p_tranid,
455 r_notification.seqno$$,
456 r_notification.notification_id,
457 g_object_name,
458 g_pub_name,
459 l_error_msg,
460 l_process_status
461 );
462
463 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
464 /*** Reject successfull than reject matching attributes ***/
465 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
466 jtm_message_log_pkg.Log_Msg
467 ( v_object_id => r_notification.notification_id
468 , v_object_name => g_object_name
469 , v_message => 'Record rejected, now rejecting attributes'
470 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
471 END IF;
472
473 FOR r_notification_attr IN c_notification_attr( p_user_name
474 , p_tranid
475 , r_notification.notification_id ) LOOP
476 CSL_SERVICEL_WRAPPER_PKG.REJECT_RECORD
477 ( p_user_name,
478 p_tranid,
479 r_notification_attr.seqno$$,
480 r_notification_attr.notification_id,
481 g_object_name,
482 'WF_NOTIFICATION_ATTR',
483 l_error_msg,
484 l_process_status
485 );
486 END LOOP;
487 END IF;
488
489 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
490 jtm_message_log_pkg.Log_Msg
491 ( v_object_id => r_notification.notification_id
492 , v_object_name => g_object_name
493 , v_message => 'Record successfully processed, deleting from inqueue'
494 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
495 END IF;
496
497 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
498 (
499 p_user_name,
500 p_tranid,
501 r_notification.seqno$$,
502 r_notification.notification_id,
503 g_object_name,
504 g_pub_name,
505 l_error_msg,
506 l_process_status
507 );
508 /*** was delete successful? ***/
509 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
510 /*** no -> rollback ***/
511 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
512 jtm_message_log_pkg.Log_Msg
513 ( v_object_id => r_notification.notification_id
514 , v_object_name => g_object_name
515 , v_message => 'Deleting from inqueue failed, rolling back to savepoint'
516 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
517 END IF;
518 ROLLBACK TO save_rec;
519 ELSE
520 /*** Yes -> Delete Attributes ***/
521 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
522 jtm_message_log_pkg.Log_Msg
523 ( v_object_id => r_notification.notification_id
524 , v_object_name => g_object_name
525 , v_message => 'Deleting from inqueue succeeded, deleting attributes'
526 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
527 END IF;
528
529 FOR r_notification_attr IN c_notification_attr( p_user_name
530 , p_tranid
531 , r_notification.notification_id ) LOOP
532
533 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
534 (
535 p_user_name,
536 p_tranid,
537 r_notification_attr.seqno$$,
538 r_notification_attr.notification_id,
539 g_object_name,
540 'WF_NOTIFICATION_ATTR',
541 l_error_msg,
542 l_process_status
543 );
544
545 END LOOP;
546 END IF;
547
548 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
549 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
550 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
551 jtm_message_log_pkg.Log_Msg
552 ( v_object_id => r_notification.notification_id
553 , v_object_name => g_object_name
554 , v_message => 'Record not processed successfully, deferring and rejecting record'
555 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
556 END IF;
557
558 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
559 (
560 p_user_name
561 , p_tranid
562 , r_notification.seqno$$
563 , r_notification.notification_id
564 , g_object_name
565 , g_pub_name
566 , l_error_msg
567 , l_process_status
568 );
569
570 /*** Was defer successful? ***/
571 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
572 /*** no -> rollback ***/
573 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
574 jtm_message_log_pkg.Log_Msg
575 ( v_object_id => r_notification.notification_id
576 , v_object_name => g_object_name
577 , v_message => 'Defer record failed, rolling back to savepoint'
578 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
579 END IF;
580 ROLLBACK TO save_rec;
581 END IF;
582 ELSE
583 /*** Yes -> also defer attributes ***/
584 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
585 jtm_message_log_pkg.Log_Msg
586 ( v_object_id => r_notification.notification_id
587 , v_object_name => g_object_name
588 , v_message => 'Defer record succeeded, deferring and rejecting Attribute record(s)'
589 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
590 END IF;
591 FOR r_notification_attr IN c_notification_attr( p_user_name
592 , p_tranid
593 , r_notification.notification_id ) LOOP
594
595 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
596 (
597 p_user_name,
598 p_tranid,
599 r_notification_attr.seqno$$,
600 r_notification_attr.notification_id,
601 g_object_name,
602 'WF_NOTIFICATION_ATTR',
603 l_error_msg,
604 l_process_status
605 );
606 END LOOP;
607 END IF;
608 END IF;
609 END LOOP;
610
611 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
612 jtm_message_log_pkg.Log_Msg
613 ( v_object_id => null
614 , v_object_name => g_object_name
615 , v_message => 'Leaving ' || g_object_name || '.Apply_Client_Changes'
616 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
617 END IF;
618
619 EXCEPTION WHEN OTHERS THEN
620 /*** catch and log exceptions ***/
621 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
622 jtm_message_log_pkg.Log_Msg
623 ( v_object_id => null
624 , v_object_name => g_object_name
625 , v_message => 'Exception occurred in APPLY_CLIENT_CHANGES:' || fnd_global.local_chr(10) || sqlerrm
626 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
627 END IF;
628 x_return_status := FND_API.G_RET_STS_ERROR;
629 END APPLY_CLIENT_CHANGES;
630
631 END CSL_NOTIFICATIONS_PKG;