[Home] [Help]
PACKAGE BODY: APPS.CSL_JTF_NOTES_ACC_PKG
Source
1 PACKAGE BODY CSL_JTF_NOTES_ACC_PKG AS
2 /* $Header: cslntacb.pls 120.0 2005/05/24 17:15:41 appldev noship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_JTF_NOTES_ACC';
6 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
7 JTM_HOOK_UTIL_PKG.t_publication_item_list('JTF_NOTES_VL');
8 g_table_name CONSTANT VARCHAR2(30) := 'JTF_NOTES_B';
9 g_pk_name CONSTANT VARCHAR2(30) := 'JTF_NOTE_ID';
10
11 g_debug_level NUMBER; -- debug level
12
13 /*** Function that checks if note should be replicated. Returns TRUE if it should ***/
14 FUNCTION Replicate_Record
15 ( p_jtf_note_id NUMBER
16 )
17 RETURN BOOLEAN
18 IS
19 CURSOR c_jtf_note (b_jtf_note_id NUMBER) IS
20 SELECT *
21 FROM JTF_NOTES_B
22 WHERE jtf_note_id = b_jtf_note_id;
23 r_jtf_note c_jtf_note%ROWTYPE;
24
25 BEGIN
26 /*** get debug level ***/
27 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
28
29 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
30 jtm_message_log_pkg.Log_Msg
31 ( p_jtf_note_id
32 , g_table_name
33 , 'Entering Replicate_Record'
34 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
35 END IF;
36
37 OPEN c_jtf_note( p_jtf_note_id );
38 FETCH c_jtf_note INTO r_jtf_note;
39 IF c_jtf_note%NOTFOUND THEN
40 /*** could not find jtf_note record -> exit ***/
41 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
42 jtm_message_log_pkg.Log_Msg
43 ( p_jtf_note_id
44 , g_table_name
45 , 'Replicate_Record error: Could not find jtf_note_id ' || p_jtf_note_id
46 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
47 END IF;
48
49 CLOSE c_jtf_note;
50 RETURN FALSE;
51 END IF;
52 CLOSE c_jtf_note;
53
54 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
55 jtm_message_log_pkg.Log_Msg
56 ( p_jtf_note_id
57 , g_table_name
58 , 'Replicate_Record returned TRUE'
59 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
60 END IF;
61
62 /** Record matched criteria -> return true ***/
63 RETURN TRUE;
64 END Replicate_Record;
65
66
67 /*** Public Function that returns the entered by id ***/
68 FUNCTION Get_User_Id( p_jtf_note_id NUMBER)
69 RETURN NUMBER
70 IS
71 CURSOR c_jtf_note (b_jtf_note_id NUMBER) IS
72 SELECT entered_by
73 FROM JTF_NOTES_B
74 WHERE jtf_note_id = b_jtf_note_id;
75 r_jtf_note c_jtf_note%ROWTYPE;
76
77 BEGIN
78 /*** get debug level ***/
79 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
80
81 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
82 jtm_message_log_pkg.Log_Msg
83 ( p_jtf_note_id
84 , g_table_name
85 , 'Entering Get_User_Id'
86 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
87 END IF;
88
89 OPEN c_jtf_note( p_jtf_note_id );
90 FETCH c_jtf_note INTO r_jtf_note;
91 IF c_jtf_note%NOTFOUND THEN
92 /*** could not find jtf_note record -> exit ***/
93 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
94 jtm_message_log_pkg.Log_Msg
95 ( p_jtf_note_id
96 , g_table_name
97 , 'Get_User_Id error: Could not find jtf_note_id ' || p_jtf_note_id
98 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
99 END IF;
100
101 CLOSE c_jtf_note;
102 RETURN -1;
103 END IF;
104 CLOSE c_jtf_note;
105
106 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
107 jtm_message_log_pkg.Log_Msg
108 ( p_jtf_note_id
109 , g_table_name
110 , 'Get_User_Id returned TRUE'
111 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
112 END IF;
113
114 /** return the entered by id ***/
115 return r_jtf_note.entered_by;
116 END Get_User_Id;
117
118
119 /*** Public Function that returns the resource extn id ***/
120 FUNCTION Get_Resource_Extn_Id( p_user_id NUMBER)
121 RETURN NUMBER
122 IS
123 CURSOR c_jtf_rs_resource_extns (b_user_id NUMBER) IS
124 SELECT resource_id
125 FROM JTF_RS_RESOURCE_EXTNS
126 WHERE user_id = b_user_id;
127 r_jtf_rs_resource_extns c_jtf_rs_resource_extns%ROWTYPE;
128
129 BEGIN
130 /*** get debug level ***/
131 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
132
133 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
134 jtm_message_log_pkg.Log_Msg
135 ( p_user_id
136 , g_table_name
137 , 'Entering Get_Resource_Extn_Id'
138 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
139 END IF;
140
141 OPEN c_jtf_rs_resource_extns( p_user_id );
142 FETCH c_jtf_rs_resource_extns INTO r_jtf_rs_resource_extns;
143 IF c_jtf_rs_resource_extns%NOTFOUND THEN
144 /*** could not find jtf_rs_resource_extns record -> exit ***/
145 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
146 jtm_message_log_pkg.Log_Msg
147 ( p_user_id
148 , g_table_name
149 , 'Get_Resource_Extn_Id error: Could not find p_user_id ' || p_user_id
150 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
151 END IF;
152
153 CLOSE c_jtf_rs_resource_extns;
154 RETURN -1;
155 END IF;
156 CLOSE c_jtf_rs_resource_extns;
157
158 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
159 jtm_message_log_pkg.Log_Msg
160 ( p_user_id
161 , g_table_name
162 , 'Get_Resource_Extn_Id returned TRUE'
163 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
164 END IF;
165
166 /** return the entered by id ***/
167 return r_jtf_rs_resource_extns.resource_id;
168 END Get_Resource_Extn_Id;
169
170 /*** Private procedure that replicates given jtf_note related data for resource ***/
171 PROCEDURE Insert_ACC_Record
172 ( p_jtf_note_id IN NUMBER
173 ,p_resource_id IN NUMBER
174 )
175 IS
176 BEGIN
177 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
178 jtm_message_log_pkg.Log_Msg
179 ( p_jtf_note_id
180 , g_table_name
181 , 'Entering Insert_ACC_Record'
182 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
183 END IF;
184
185 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
186 jtm_message_log_pkg.Log_Msg
187 ( p_jtf_note_id
188 , g_table_name
189 , 'Inserting ACC record for resource_id = ' || p_resource_id
190 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
191 END IF;
192
193 /*** Insert jtf_note ACC record ***/
194 JTM_HOOK_UTIL_PKG.Insert_Acc
195 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
196 ,P_ACC_TABLE_NAME => g_acc_table_name
197 ,P_PK1_NAME => g_pk_name
198 ,P_PK1_NUM_VALUE => p_jtf_note_id
199 ,P_RESOURCE_ID => p_resource_id
200 );
201
202 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
203 jtm_message_log_pkg.Log_Msg
204 ( p_jtf_note_id
205 , g_table_name
206 , 'Leaving Insert_ACC_Record'
207 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
208 END IF;
209 END Insert_ACC_Record;
210
211 /*** Private procedure that re-sends given jtf_note to mobile ***/
212 PROCEDURE Update_ACC_Record
213 ( p_jtf_note_id IN NUMBER
214 ,p_resource_id IN NUMBER
215 ,p_acc_id IN NUMBER
216 )
217 IS
218 BEGIN
219 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
220 jtm_message_log_pkg.Log_Msg
221 ( p_jtf_note_id
222 , g_table_name
223 , 'Entering Update_ACC_Record'
224 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
225 END IF;
226
227 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
228 jtm_message_log_pkg.Log_Msg
229 ( p_jtf_note_id
230 , g_table_name
231 , 'Updating ACC record for resource_id = ' || p_resource_id || fnd_global.local_chr(10) || 'access_id = ' || p_acc_id
232 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
233 END IF;
234
235 /*** Update jtf_note ACC record ***/
236 JTM_HOOK_UTIL_PKG.Update_Acc
237 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
238 ,P_ACC_TABLE_NAME => g_acc_table_name
239 ,P_RESOURCE_ID => p_resource_id
240 ,P_ACCESS_ID => p_acc_id
241 );
242
243 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
244 jtm_message_log_pkg.Log_Msg
245 ( p_jtf_note_id
246 , g_table_name
247 , 'Leaving Update_ACC_Record'
248 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
249 END IF;
250 END Update_ACC_Record;
251
252 /*** Private procedure that deletes jtf_note for resource from acc table ***/
253 PROCEDURE Delete_ACC_Record
254 ( p_jtf_note_id IN NUMBER
255 ,p_resource_id IN NUMBER
256 )
257 IS
258 BEGIN
259 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
260 jtm_message_log_pkg.Log_Msg
261 ( p_jtf_note_id
262 , g_table_name
263 , 'Entering Delete_ACC_Record'
264 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
265 END IF;
266
267 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
268 jtm_message_log_pkg.Log_Msg
269 ( p_jtf_note_id
270 , g_table_name
271 , 'Deleting ACC record for resource_id = ' || p_resource_id
272 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
273 END IF;
274
275 /*** Delete jtf_note ACC record ***/
276 JTM_HOOK_UTIL_PKG.Delete_Acc
277 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
278 ,P_ACC_TABLE_NAME => g_acc_table_name
279 ,P_PK1_NAME => g_pk_name
280 ,P_PK1_NUM_VALUE => p_jtf_note_id
281 ,P_RESOURCE_ID => p_resource_id
282 );
283
284 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
285 jtm_message_log_pkg.Log_Msg
286 ( p_jtf_note_id
287 , g_table_name
288 , 'Leaving Delete_ACC_Record'
289 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
290 END IF;
291 END Delete_ACC_Record;
292
293 /***
294 Public function that gets called when a jtf_note needs to be inserted into ACC table.
295 Returns TRUE when record already was or has been inserted into ACC table.
296 ***/
297 FUNCTION Pre_Insert_Child
298 ( p_jtf_note_id IN NUMBER
299 ,p_resource_id IN NUMBER
300 )
301 RETURN BOOLEAN
302 IS
303 l_acc_id NUMBER;
304 l_user_id NUMBER;
305 l_resource_extn_id NUMBER;
306 l_success BOOLEAN;
307 BEGIN
308 /*** get debug level ***/
309 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
310
311 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
312 jtm_message_log_pkg.Log_Msg
313 ( p_jtf_note_id
314 , g_table_name
315 , 'Entering Pre_Insert_Child procedure'
316 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
317 END IF;
318
319
320 /*** no -> does record match criteria? ***/
321 IF Replicate_Record( p_jtf_note_id ) THEN
322 /*** yes -> insert jtf_note acc record ***/
323 Insert_ACC_Record
324 ( p_jtf_note_id
325 ,p_resource_id
326 );
327
328 /*** Get the user id ***/
329 l_user_id := Get_User_Id( p_jtf_note_id );
330
331 /*** Insert the user ***/
332 CSL_FND_USER_ACC_PKG.Insert_User
333 ( l_user_id
334 ,p_resource_id
335 );
336
337 /*** Get the resource id ***/
338 l_resource_extn_id := Get_Resource_Extn_Id( l_user_id );
339
340 /*** Insert the resource ext ***/
341 /*** Only if resource id is not -1 ***/
342 IF l_resource_extn_id > -1 THEN
343 CSL_JTF_RESOURCE_EXTNS_ACC_PKG.Insert_Resource_Extns
344 ( l_resource_extn_id
345 ,p_resource_id
346 );
347 END IF;
348
349 l_success := TRUE;
350 END IF;
351
352 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
353 jtm_message_log_pkg.Log_Msg
354 ( p_jtf_note_id
355 , g_table_name
356 , 'Leaving Pre_Insert_Child procedure'
357 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
358 END IF;
359
360 RETURN l_success;
361 END Pre_Insert_Child;
362
363 /***
364 Public function that gets called when jtf_notes needs to be inserted into ACC table.
365 Returns TRUE when records already were or have been inserted into ACC table.
366 ***/
367 FUNCTION Pre_Insert_Children
368 ( p_source_obj_id IN NUMBER
369 ,p_source_obj_code IN VARCHAR2
370 ,p_resource_id IN NUMBER
371 )
372 RETURN BOOLEAN
373 IS
374 CURSOR c_jtf_note (b_source_obj_id NUMBER,
375 b_source_obj_code VARCHAR2) IS
376 SELECT *
377 FROM JTF_NOTES_B
378 WHERE source_object_id = b_source_obj_id
379 AND source_object_code = b_source_obj_code;
380 r_jtf_note c_jtf_note%ROWTYPE;
381
382 l_dummy BOOLEAN;
383 BEGIN
384 /*** get debug level ***/
385 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
386
387 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
388 jtm_message_log_pkg.Log_Msg
389 ( p_source_obj_id
390 , g_table_name
391 , 'Entering Pre_Insert_Children procedure'
392 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
393 END IF;
394
395 FOR r_jtf_note IN c_jtf_note( p_source_obj_id, p_source_obj_code ) LOOP
396
397 /*** Insert record if applicable ***/
398 l_dummy := Pre_Insert_Child
399 ( r_jtf_note.jtf_note_id
400 ,p_resource_id
401 );
402
403 IF l_dummy = FALSE THEN
404 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
405 jtm_message_log_pkg.Log_Msg
406 ( p_source_obj_id
407 , g_table_name
408 , 'Pre_Insert_Children: note was not insertable.'
412 END LOOP;
409 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
410 END IF;
411 END IF;
413
414 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
415 jtm_message_log_pkg.Log_Msg
416 ( p_source_obj_id
417 , g_table_name
418 , 'Leaving Pre_Insert_Children procedure'
419 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
420 END IF;
421
422 RETURN TRUE;
423 END Pre_Insert_Children;
424
425 /***
426 Public procedure that gets called when a jtf_note needs to be deleted from the ACC table.
427 ***/
428 PROCEDURE Post_Delete_Child
429 ( p_jtf_note_id IN NUMBER
430 ,p_resource_id IN NUMBER
431 )
432 IS
433 l_acc_id NUMBER;
434 l_user_id NUMBER;
435 l_resource_extn_id NUMBER;
436 l_success BOOLEAN;
437 BEGIN
438 /*** get debug level ***/
439 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
440
441 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
442 jtm_message_log_pkg.Log_Msg
443 ( p_jtf_note_id
444 , g_table_name
445 , 'Entering Post_Delete_Child'
446 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
447 END IF;
448
449 /*** no -> delete jtf_note record from ACC ***/
450 Delete_ACC_Record
451 ( p_jtf_note_id
452 ,p_resource_id);
453
454 /*** Get the user id ***/
455 l_user_id := Get_User_Id( p_jtf_note_id );
456
457 /*** Delete the user ***/
458 CSL_FND_USER_ACC_PKG.Delete_User
459 ( l_user_id
460 ,p_resource_id
461 );
462
463 /*** Get resource id ***/
464 l_resource_extn_id := Get_Resource_Extn_Id( l_user_id );
465
466 /*** Delete the resource ext ***/
467 /*** Only if resource id is not -1 ***/
468 IF l_resource_extn_id > -1 THEN
469 CSL_JTF_RESOURCE_EXTNS_ACC_PKG.Delete_Resource_Extns
470 ( l_resource_extn_id
471 ,p_resource_id
472 );
473 END IF;
474
475 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
476 jtm_message_log_pkg.Log_Msg
477 ( p_jtf_note_id
478 , g_table_name
479 , 'Leaving Post_Delete_Child'
480 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
481 END IF;
482 END Post_Delete_Child;
483
484 /***
485 Public procedure that gets called when jtf_notes need to be deleted from the ACC table.
486 ***/
487 PROCEDURE Post_Delete_Children
488 ( p_source_obj_id IN NUMBER
489 ,p_source_obj_code IN VARCHAR2
490 ,p_resource_id IN NUMBER
491 )
492 IS
493 CURSOR c_jtf_note (b_source_obj_id NUMBER,
494 b_source_obj_code VARCHAR2) IS
495 SELECT *
496 FROM JTF_NOTES_B
497 WHERE source_object_id = b_source_obj_id
498 AND source_object_code = b_source_obj_code;
499 r_jtf_note c_jtf_note%ROWTYPE;
500
501 BEGIN
502 /*** get debug level ***/
503 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
504
505 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
506 jtm_message_log_pkg.Log_Msg
507 ( p_source_obj_id
508 , g_table_name
509 , 'Entering Post_Delete_Children procedure'
510 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
511 END IF;
512
513 FOR r_jtf_note IN c_jtf_note( p_source_obj_id, p_source_obj_code ) LOOP
514
515 /*** Insert record if applicable ***/
516 Post_Delete_Child
517 ( r_jtf_note.jtf_note_id
518 ,p_resource_id
519 );
520 END LOOP;
521
522 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
523 jtm_message_log_pkg.Log_Msg
524 ( p_source_obj_id
525 , g_table_name
526 , 'Leaving Post_Delete_Children procedure'
527 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
528 END IF;
529
530 END Post_Delete_Children;
531
532 /* Called before jtf_note Insert */
533 PROCEDURE PRE_INSERT_NOTES
534 ( x_return_status OUT NOCOPY varchar2
535 )
536 IS
537 BEGIN
538 x_return_status := FND_API.G_RET_STS_SUCCESS;
539 END PRE_INSERT_NOTES;
540
541 /* Called after jtf_note Insert */
542 PROCEDURE POST_INSERT_NOTES ( p_api_version IN NUMBER
543 , p_init_msg_list IN VARCHAR2
544 , p_commit IN VARCHAR2
545 , p_validation_level IN NUMBER
546 , x_msg_count OUT NOCOPY NUMBER
547 , x_msg_data OUT NOCOPY VARCHAR2
548 , x_return_status OUT NOCOPY VARCHAR2
549 , p_jtf_note_id IN NUMBER )
550 IS
551 l_resource_id NUMBER; /* Get from API */
552 l_replicate BOOLEAN;
553
554 CURSOR c_object( b_note_id NUMBER ) IS
555 SELECT SOURCE_OBJECT_ID
556 , SOURCE_OBJECT_CODE
557 FROM JTF_NOTES_B
558 WHERE JTF_NOTE_ID = b_note_id;
559 r_object c_object%ROWTYPE;
560
561 CURSOR c_sr( b_id NUMBER ) IS
562 SELECT resource_id
563 FROM csl_cs_incidents_all_acc
564 WHERE incident_id = b_id;
568 FROM csl_jtf_tasks_acc
565
566 CURSOR c_task( b_id NUMBER ) IS
567 SELECT resource_id
569 WHERE task_id = b_id;
570
571 CURSOR c_party( b_id NUMBER ) IS
572 SELECT resource_id
573 FROM csl_hz_parties_acc
574 WHERE party_id = b_id;
575
576 CURSOR c_cp( b_id NUMBER ) IS
577 SELECT resource_id
578 FROM csl_csi_item_instances_acc
579 WHERE instance_id = b_id;
580
581 -- ER 3168529
582 CURSOR c_contracts ( b_id NUMBER ) IS
583 SELECT resource_id
584 FROM CSL_SR_CONTRACT_HEADERS_ACC a,
585 CSL_SR_CONTRACT_HEADERS b
586 WHERE a.incident_id = b.incident_id
587 AND b.contract_service_id = b_id;
588
589 -- ER 3746779
590 CURSOR c_debrief( b_id NUMBER ) IS
591 SELECT resource_id
592 FROM JTM_CSF_DEBRIEF_HEADERS_ACC
593 WHERE debrief_header_id = b_id;
594
595
596 BEGIN
597 /*** get debug level ***/
598 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
599
600 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
601 jtm_message_log_pkg.Log_Msg
602 ( p_jtf_note_id
603 , g_table_name
604 , 'Entering POST_INSERT hook'
605 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
606 END IF;
607
608 /*Get the object type and id of this note*/
609 OPEN c_object( p_jtf_note_id );
610 FETCH c_object INTO r_object;
611 IF c_object%NOTFOUND THEN
612 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
613 jtm_message_log_pkg.Log_Msg
614 ( p_jtf_note_id
615 , g_table_name
616 , 'Objects for note '||p_jtf_note_id||' not found'
617 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
618 END IF;
619 ELSE
620 l_replicate := FALSE;
621 /*Check if whe have the "parent" object of this note*/
622 IF r_object.SOURCE_OBJECT_CODE = 'SR' THEN
623 /*SR note, so check incident acc table*/
624 FOR r_sr IN c_sr( r_object.SOURCE_OBJECT_ID ) LOOP
625 l_replicate := Pre_Insert_Child( p_jtf_note_id, r_sr.resource_id );
626 END LOOP;
627 ELSIF r_object.SOURCE_OBJECT_CODE = 'TASK' THEN
628 /*TASK note, so check tasks acc table*/
629 FOR r_task IN c_task( r_object.SOURCE_OBJECT_ID ) LOOP
630 l_replicate := Pre_Insert_Child( p_jtf_note_id, r_task.resource_id );
631 END LOOP;
632 ELSIF r_object.SOURCE_OBJECT_CODE = 'PARTY' THEN
633 /*PARTY note, so check hz_party acc table*/
634 FOR r_party IN c_party( r_object.SOURCE_OBJECT_ID ) LOOP
635 l_replicate := Pre_Insert_Child( p_jtf_note_id, r_party.resource_id );
636 END LOOP;
637 ELSIF r_object.SOURCE_OBJECT_CODE = 'CP' THEN
638 /*CP note, so check customer product ( item instance ) acc table*/
639 FOR r_cp IN c_cp( r_object.SOURCE_OBJECT_ID ) LOOP
640 l_replicate := Pre_Insert_Child( p_jtf_note_id, r_cp.resource_id );
641 END LOOP;
642 -- ER 3168529 Contract Notes
643 ELSIF r_object.SOURCE_OBJECT_CODE = 'OKS_COV_NOTE' THEN
644 /* Contract note, so check contract service id */
645 FOR r_contracts IN c_contracts( r_object.SOURCE_OBJECT_ID ) LOOP
646 l_replicate := Pre_Insert_Child( p_jtf_note_id, r_contracts.resource_id );
647 END LOOP;
648 -- ER 3746779 Debrief Notes
649 ELSIF r_object.SOURCE_OBJECT_CODE = 'SD' THEN
650 /* Debrief note, so check debrief header id */
651 FOR r_debrief IN c_debrief( r_object.SOURCE_OBJECT_ID ) LOOP
652 l_replicate := Pre_Insert_Child( p_jtf_note_id, r_debrief.resource_id );
653 END LOOP;
654 ELSE
655 /*Note is of not supported type*/
656 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
657 jtm_message_log_pkg.Log_Msg
658 ( p_jtf_note_id
659 , g_table_name
660 , 'Source_Object_Code '||r_object.SOURCE_OBJECT_CODE||' is not supported'
661 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
662 END IF; --g_debug_level
663 END IF; -- CODE = SR
664
665 /*** Insert record if applicable ***/
666 IF l_replicate = FALSE THEN
667 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
668 jtm_message_log_pkg.Log_Msg
669 ( p_jtf_note_id
670 , g_table_name
671 , 'Note '||p_jtf_note_id||' did not match the criteria to be replicated'||fnd_global.local_chr(10)||
672 'Object id = '||r_object.SOURCE_OBJECT_ID||fnd_global.local_chr(10)||
673 'Object_code = '||r_object.SOURCE_OBJECT_CODE
674 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
675 END IF; -- g_debug_level
676 END IF; -- l_replicate = FALSE
677 END IF; --c_object%NOTFOUND
678 CLOSE c_object;
679
680 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
681 jtm_message_log_pkg.Log_Msg
682 ( p_jtf_note_id
683 , g_table_name
684 , 'Leaving POST_INSERT hook'
685 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
686 END IF;
687
688 x_return_status := FND_API.G_RET_STS_SUCCESS;
689 EXCEPTION WHEN OTHERS THEN
690 /*** hook failed -> log error ***/
691 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
692 jtm_message_log_pkg.Log_Msg
693 ( p_jtf_note_id
694 , g_table_name
698 fnd_msg_pub.Add_Exc_Msg('CSL_JTF_NOTES_ACC_PKG','POST_INSERT_JTF_NOTES',sqlerrm);
695 , 'Caught exception in POST_INSERT hook:' || fnd_global.local_chr(10) || sqlerrm
696 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
697 END IF;
699 -- x_return_status := FND_API.G_RET_STS_ERROR;
700 x_return_status := FND_API.G_RET_STS_SUCCESS;
701 END POST_INSERT_NOTES;
702
703 /* Called before jtf_note Update */
704 PROCEDURE PRE_UPDATE_NOTES
705 ( x_return_status OUT NOCOPY varchar2
706 )
707 IS
708 BEGIN
709 x_return_status := FND_API.G_RET_STS_SUCCESS;
710 END PRE_UPDATE_NOTES;
711
712 /* Called after jtf_note Update */
713 PROCEDURE POST_UPDATE_NOTES ( p_api_version IN NUMBER
714 , p_init_msg_list IN VARCHAR2
715 , p_commit IN VARCHAR2
716 , p_validation_level IN NUMBER
717 , x_msg_count OUT NOCOPY NUMBER
718 , x_msg_data OUT NOCOPY VARCHAR2
719 , x_return_status OUT NOCOPY VARCHAR2
720 , p_jtf_note_id IN NUMBER )
721 IS
722
723 l_jtf_note_id NUMBER;
724 l_user_id NUMBER;
725 l_resource_extn_id NUMBER;
726 l_replicate BOOLEAN;
727
728 l_tab_resource_id dbms_sql.Number_Table;
729 l_tab_access_id dbms_sql.Number_Table;
730 BEGIN
731 l_jtf_note_id := p_jtf_note_id;
732
733 /*** get debug level ***/
734 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
735
736 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
737 jtm_message_log_pkg.Log_Msg
738 ( l_jtf_note_id
739 , g_table_name
740 , 'Entering POST_UPDATE hook'
741 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
742 END IF;
743
744 /*** Check if jtf_note after update matches criteria ***/
745 l_replicate := Replicate_Record( l_jtf_note_id );
746
747 /*** replicate record after update? ***/
748 IF NOT l_replicate THEN
749 /*** yes -> re-send updated jtf_note record to all resources ***/
750 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
751 jtm_message_log_pkg.Log_Msg
752 ( l_jtf_note_id
753 , g_table_name
754 , 'Note was not replicateable after update.'
755 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
756 END IF;
757
758 ELSE
759 /*** yes -> re-send updated note record to all resources ***/
760 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
761 jtm_message_log_pkg.Log_Msg
762 ( l_jtf_note_id
763 , g_table_name
764 , 'Note being re-sent to mobile users.'
765 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
766 END IF;
767
768 /*** get list of resources to whom the record was replicated ***/
769 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
770 ( P_ACC_TABLE_NAME => g_acc_table_name
771 ,P_PK1_NAME => g_pk_name
772 ,P_PK1_NUM_VALUE => l_jtf_note_id
773 ,L_TAB_RESOURCE_ID => l_tab_resource_id
774 ,L_TAB_ACCESS_ID => l_tab_access_id
775 );
776
777 /*** re-send rec to all resources ***/
778 IF l_tab_resource_id.COUNT > 0 THEN
779
780 /*** Get the entered by id ***/
781 l_user_id := Get_User_Id( l_jtf_note_id );
782
783 /*** Get the resource id ***/
784 l_resource_extn_id := Get_Resource_Extn_Id( l_user_id );
785
786 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
787
788 /*** is resource a mobile user? ***/
789 IF NOT JTM_HOOK_UTIL_PKG.isMobileFSresource( l_tab_resource_id(i) ) THEN
790 /*** No -> exit ***/
791 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
792 jtm_message_log_pkg.Log_Msg
793 ( l_tab_resource_id(i)
794 , g_table_name
795 , 'POST_UPDATE_DEBRIEF_LINE' || fnd_global.local_chr(10) ||
796 'Resource_id ' || l_tab_resource_id(i) || ' is not a mobile user.'
797 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
798 END IF;
799 ELSE
800
801 Update_ACC_Record
802 ( l_jtf_note_id
803 ,l_tab_resource_id(i)
804 ,l_tab_access_id(i)
805 );
806
807 END IF;
808 END LOOP;
809 END IF;
810 END IF;
811
812 x_return_status := FND_API.G_RET_STS_SUCCESS;
813
814 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
815 jtm_message_log_pkg.Log_Msg
816 ( l_jtf_note_id
817 , g_table_name
818 , 'Leaving POST_UPDATE hook'
819 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
820 END IF;
821
822 EXCEPTION WHEN OTHERS THEN
823 /*** hook failed -> log error ***/
824 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
825 jtm_message_log_pkg.Log_Msg
826 ( l_jtf_note_id
827 , g_table_name
828 , 'Caught exception in POST_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
829 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
830 END IF;
831 fnd_msg_pub.Add_Exc_Msg('CSL_JTF_NOTES_ACC_PKG','POST_UPDATE_JTF_NOTES',sqlerrm);
832 -- x_return_status := FND_API.G_RET_STS_ERROR;
833 x_return_status := FND_API.G_RET_STS_SUCCESS;
834 END POST_UPDATE_NOTES;
835
836 /* Called before jtf_note Delete */
837 PROCEDURE PRE_DELETE_NOTES
838 ( x_return_status OUT NOCOPY varchar2
839 )
840 IS
841 BEGIN
842 x_return_status := FND_API.G_RET_STS_SUCCESS;
843 END PRE_DELETE_NOTES;
844
845 /* Called after jtf_note Delete */
846 PROCEDURE POST_DELETE_NOTES
847 ( x_return_status OUT NOCOPY varchar2
848 )
849 IS
850 BEGIN
851 x_return_status := FND_API.G_RET_STS_SUCCESS;
852 END POST_DELETE_NOTES;
853
854 END CSL_JTF_NOTES_ACC_PKG;