[Home] [Help]
PACKAGE BODY: APPS.CSL_CSF_DEBRIEF_LINE_ACC_PKG
Source
1 PACKAGE BODY CSL_CSF_DEBRIEF_LINE_ACC_PKG AS
2 /* $Header: csldbacb.pls 120.0 2005/05/25 11:04:53 appldev noship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_CSF_DEBRIEF_LINES_ACC';
6 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
7 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSF_DEBRIEF_LINES');
8 g_table_name CONSTANT VARCHAR2(30) := 'CSF_DEBRIEF_LINES';
9 g_pk1_name CONSTANT VARCHAR2(30) := 'DEBRIEF_LINE_ID';
10 g_pre_replicate BOOLEAN;
11
12 g_debug_level NUMBER; -- debug level
13
14 /*** cache variables used by pre/post update ***/
15 CURSOR c_update_cache_rec( b_debrief_line_id NUMBER)
16 IS
17 SELECT inventory_item_id
18 , NVL( NVL(issuing_inventory_org_id, receiving_inventory_org_id)
19 , FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG')) AS organization_id
20 FROM csf_debrief_lines
21 WHERE debrief_line_id = b_debrief_line_id;
22 g_pre_update_rec c_update_cache_rec%ROWTYPE;
23
24 /*** Public Function that returns the debrief header id ***/
25 FUNCTION Get_Debrief_Header_Id
26 ( p_debrief_line_id NUMBER
27 )
28 RETURN NUMBER
29 IS
30 CURSOR c_debrief_line (b_debrief_line_id NUMBER) IS
31 SELECT debrief_header_id
32 FROM CSF_DEBRIEF_LINES
33 WHERE debrief_line_id = b_debrief_line_id;
34 r_debrief_line c_debrief_line%ROWTYPE;
35
36 BEGIN
37 /*** get debug level ***/
38 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
39
40 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
41 jtm_message_log_pkg.Log_Msg
42 ( p_debrief_line_id
43 , g_table_name
44 , 'Entering Get_Debrief_Header_Id'
45 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
46 END IF;
47
48 OPEN c_debrief_line( p_debrief_line_id );
49 FETCH c_debrief_line INTO r_debrief_line;
50 IF c_debrief_line%NOTFOUND THEN
51 /*** could not find debrief_line record -> exit ***/
52 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
53 jtm_message_log_pkg.Log_Msg
54 ( p_debrief_line_id
55 , g_table_name
56 , 'Get_Debrief_Header_Id error: Could not find debrief_line_id ' ||
57 p_debrief_line_id
58 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
59 END IF;
60
61 CLOSE c_debrief_line;
62 RETURN -1;
63 END IF;
64 CLOSE c_debrief_line;
65
66 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
67 jtm_message_log_pkg.Log_Msg
68 ( p_debrief_line_id
69 , g_table_name
70 , 'Get_Debrief_Header_Id returned TRUE'
71 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
72 END IF;
73
74 /** return the debrief header id ***/
75 return r_debrief_line.debrief_header_id;
76 END Get_Debrief_Header_Id;
77
78 /*** Function that checks if debrief line should be replicated. Returns TRUE if it should ***/
79 FUNCTION Replicate_Record
80 ( p_debrief_line_id NUMBER
81 )
82 RETURN BOOLEAN
83 IS
84 CURSOR c_debrief_line (b_debrief_line_id NUMBER) IS
85 SELECT *
86 FROM CSF_DEBRIEF_LINES
87 WHERE debrief_line_id = b_debrief_line_id;
88 r_debrief_line c_debrief_line%ROWTYPE;
89
90 BEGIN
91 /*** get debug level ***/
92 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
93
94 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
95 jtm_message_log_pkg.Log_Msg
96 ( p_debrief_line_id
97 , g_table_name
98 , 'Entering Replicate_Record'
99 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
100 END IF;
101
102 OPEN c_debrief_line( p_debrief_line_id );
103 FETCH c_debrief_line INTO r_debrief_line;
104 IF c_debrief_line%NOTFOUND THEN
105 /*** could not find debrief_line record -> exit ***/
106 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
107 jtm_message_log_pkg.Log_Msg
108 ( p_debrief_line_id
109 , g_table_name
110 , 'Replicate_Record error: Could not find debrief_line_id ' || p_debrief_line_id
111 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
112 END IF;
113
114 CLOSE c_debrief_line;
115 RETURN FALSE;
116 END IF;
117 CLOSE c_debrief_line;
118
119 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
120 jtm_message_log_pkg.Log_Msg
121 ( p_debrief_line_id
122 , g_table_name
123 , 'Replicate_Record returned TRUE'
124 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
125 END IF;
126
127 /** Record matched criteria -> return true ***/
128 RETURN TRUE;
129 END Replicate_Record;
130
131
132 /*** Private procedure that replicates given debrief_line related data for resource ***/
133 PROCEDURE Insert_ACC_Record
134 ( p_debrief_line_id IN NUMBER
135 ,p_resource_id IN NUMBER
136 )
137 IS
138 BEGIN
139 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
140 jtm_message_log_pkg.Log_Msg
141 ( p_debrief_line_id
142 , g_table_name
143 , 'Entering Insert_ACC_Record'
144 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
145 END IF;
146
147 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
148 jtm_message_log_pkg.Log_Msg
149 ( p_debrief_line_id
150 , g_table_name
151 , 'Inserting ACC record for resource_id = ' || p_resource_id
152 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
153 END IF;
154
155 /*** Insert debrief_line ACC record ***/
156 JTM_HOOK_UTIL_PKG.Insert_Acc
157 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
158 ,P_ACC_TABLE_NAME => g_acc_table_name
159 ,P_PK1_NAME => g_pk1_name
160 ,P_PK1_NUM_VALUE => p_debrief_line_id
161 ,P_RESOURCE_ID => p_resource_id
162 );
163
164 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
165 jtm_message_log_pkg.Log_Msg
166 ( p_debrief_line_id
167 , g_table_name
168 , 'Leaving Insert_ACC_Record'
169 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
170 END IF;
171 END Insert_ACC_Record;
172
173 /*** Private procedure that re-sends given debrief_line to mobile ***/
174 PROCEDURE Update_ACC_Record
175 ( p_debrief_line_id IN NUMBER
176 ,p_resource_id IN NUMBER
177 ,p_acc_id IN NUMBER
178 )
179 IS
180 BEGIN
181 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
182 jtm_message_log_pkg.Log_Msg
183 ( p_debrief_line_id
184 , g_table_name
185 , 'Entering Update_ACC_Record'
186 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
187 END IF;
188
189 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
190 jtm_message_log_pkg.Log_Msg
191 ( p_debrief_line_id
192 , g_table_name
193 , 'Updating ACC record for resource_id = ' || p_resource_id || fnd_global.local_chr(10) || 'access_id = ' || p_acc_id
194 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
195 END IF;
196
197 /*** Update debrief_line ACC record ***/
198 JTM_HOOK_UTIL_PKG.Update_Acc
199 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
200 ,P_ACC_TABLE_NAME => g_acc_table_name
201 ,P_RESOURCE_ID => p_resource_id
202 ,P_ACCESS_ID => p_acc_id
203 );
204
205 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
206 jtm_message_log_pkg.Log_Msg
207 ( p_debrief_line_id
208 , g_table_name
209 , 'Leaving Update_ACC_Record'
210 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
211 END IF;
212 END Update_ACC_Record;
213
214 /*** Private procedure that deletes debrief_line for resource from acc table ***/
215 PROCEDURE Delete_ACC_Record
216 ( p_debrief_line_id IN NUMBER
217 ,p_resource_id IN NUMBER
218 )
219 IS
220 BEGIN
221 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
222 jtm_message_log_pkg.Log_Msg
223 ( p_debrief_line_id
224 , g_table_name
225 , 'Entering Delete_ACC_Record'
226 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
227 END IF;
228
229 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
230 jtm_message_log_pkg.Log_Msg
231 ( p_debrief_line_id
232 , g_table_name
233 , 'Deleting ACC record for resource_id = ' || p_resource_id
234 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
235 END IF;
236
237 /*** Delete debrief_line ACC record ***/
238 JTM_HOOK_UTIL_PKG.Delete_Acc
239 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
240 ,P_ACC_TABLE_NAME => g_acc_table_name
241 ,P_PK1_NAME => g_pk1_name
242 ,P_PK1_NUM_VALUE => p_debrief_line_id
243 ,P_RESOURCE_ID => p_resource_id
244 );
245
246 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
247 jtm_message_log_pkg.Log_Msg
248 ( p_debrief_line_id
249 , g_table_name
250 , 'Leaving Delete_ACC_Record'
251 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
252 END IF;
253 END Delete_ACC_Record;
254
255 /***
256 Public function that gets called when a debrief_line needs to be inserted into ACC table.
257 Returns TRUE when record already was or has been inserted into ACC table.
258 ***/
259 FUNCTION Pre_Insert_Child
260 ( p_debrief_line_id IN NUMBER
261 ,p_resource_id IN NUMBER
262 )
263 RETURN BOOLEAN
264 IS
265 CURSOR c_debrief_line( b_debrief_line_id NUMBER)
266 IS
267 SELECT inventory_item_id
268 , NVL( NVL(issuing_inventory_org_id, receiving_inventory_org_id)
269 , FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG')) AS organization_id
270 FROM csf_debrief_lines
271 WHERE debrief_line_id = b_debrief_line_id;
272 r_debrief_line c_debrief_line%ROWTYPE;
273
274 l_debrief_header_id NUMBER;
275 l_acc_id NUMBER;
276 l_success BOOLEAN;
277 BEGIN
278 /*** get debug level ***/
279 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
280
281 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
282 jtm_message_log_pkg.Log_Msg
283 ( p_debrief_line_id
284 , g_table_name
285 , 'Entering Pre_Insert_Child procedure'
286 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
287 END IF;
288
289 /*** does record match criteria? ***/
290 IF Replicate_Record( p_debrief_line_id ) THEN
291 /*** Get the debrief header id ***/
292 l_debrief_header_id := Get_Debrief_Header_Id(p_debrief_line_id);
293
294 /*** Insert the debrief header ***/
295 CSL_CSF_DEBRIEF_HDR_ACC_PKG.Insert_Debrief_Header
296 ( l_debrief_header_id
297 ,p_resource_id
298 );
299
300 /*** insert the system item used by the debrief line ***/
301 OPEN c_debrief_line( p_debrief_line_id );
302 FETCH c_debrief_line INTO r_debrief_line;
303 IF c_debrief_line%FOUND THEN
304 IF r_debrief_line.inventory_item_id IS NOT NULL
305 AND r_debrief_line.organization_id IS NOT NULL THEN
306 CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Pre_Insert_Child (
307 p_inventory_item_id => r_debrief_line.inventory_item_id
308 ,p_organization_id => r_debrief_line.organization_id
309 ,p_resource_id => p_resource_id
310 );
311 END IF;
312 END IF;
313 CLOSE c_debrief_line;
314
315 /*** yes -> insert debrief_line acc record ***/
316 Insert_ACC_Record
317 ( p_debrief_line_id
318 ,p_resource_id
319 );
320
321 l_success := TRUE;
322 END IF;
323
324 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
325 jtm_message_log_pkg.Log_Msg
326 ( p_debrief_line_id
327 , g_table_name
328 , 'Leaving Pre_Insert_Child procedure'
329 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
330 END IF;
331
332 RETURN l_success;
333 END Pre_Insert_Child;
334
335 /***
336 Public procedure that gets called when debrief lines need to be inserted into ACC table.
337 ***/
338 PROCEDURE Pre_Insert_Children
339 ( p_task_assignment_id IN NUMBER
340 ,p_resource_id IN NUMBER
341 )
342 IS
343 CURSOR c_debrief_line (b_task_assignment_id NUMBER) IS
344 SELECT CDL.debrief_line_id
345 FROM CSF_DEBRIEF_HEADERS CDH, CSF_DEBRIEF_LINES CDL
346 WHERE CDH.task_assignment_id = b_task_assignment_id
347 AND CDH.debrief_header_id = CDL.debrief_header_id;
348 r_debrief_line c_debrief_line%ROWTYPE;
349
350 l_dummy BOOLEAN;
351 BEGIN
352 /*** get debug level ***/
353 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
354
355 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
356 jtm_message_log_pkg.Log_Msg
357 ( p_task_assignment_id
358 , g_table_name
359 , 'Entering Pre_Insert_Children procedure'
360 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
361 END IF;
362
363 FOR r_debrief_line IN c_debrief_line( p_task_assignment_id ) LOOP
364
365 /*** Insert record if applicable ***/
366 l_dummy := Pre_Insert_Child
367 ( r_debrief_line.debrief_line_id
368 ,p_resource_id
369 );
370
371 IF l_dummy = FALSE THEN
372 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
373 jtm_message_log_pkg.Log_Msg
374 ( p_task_assignment_id
375 , g_table_name
376 , 'Pre_Insert_Children: debrief line was not insertable.'
377 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
378 END IF;
379 END IF;
380 END LOOP;
381
382 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
383 jtm_message_log_pkg.Log_Msg
384 ( p_task_assignment_id
385 , g_table_name
386 , 'Leaving Pre_Insert_Children procedure'
387 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
388 END IF;
389
390 END Pre_Insert_Children;
391
392 /***
393 Public procedure that gets called when a debrief_line needs to be deleted from ACC table.
394 ***/
395 PROCEDURE Post_Delete_Child
396 ( p_debrief_line_id IN NUMBER
397 ,p_resource_id IN NUMBER
398 )
399 IS
400 CURSOR c_debrief_line( b_debrief_line_id NUMBER)
401 IS
402 SELECT inventory_item_id
403 , NVL( NVL(issuing_inventory_org_id, receiving_inventory_org_id)
404 , FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG')) AS organization_id
405 FROM csf_debrief_lines
406 WHERE debrief_line_id = b_debrief_line_id;
407 r_debrief_line c_debrief_line%ROWTYPE;
408
409 l_debrief_header_id NUMBER;
410 l_acc_id NUMBER;
411 l_success BOOLEAN;
412 BEGIN
413 /*** get debug level ***/
414 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
415
416 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
417 jtm_message_log_pkg.Log_Msg
418 ( p_debrief_line_id
419 , g_table_name
420 , 'Entering Post_Delete_Child'
421 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
422 END IF;
423
424 /*** no -> delete debrief_line record from ACC ***/
425 Delete_ACC_Record
426 ( p_debrief_line_id
427 ,p_resource_id);
428
429 /*** delete the system item used by the debrief line ***/
430 OPEN c_debrief_line( p_debrief_line_id );
431 FETCH c_debrief_line INTO r_debrief_line;
432 IF c_debrief_line%FOUND THEN
433 IF r_debrief_line.inventory_item_id IS NOT NULL
434 AND r_debrief_line.organization_id IS NOT NULL THEN
435 CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Post_Delete_Child (
436 p_inventory_item_id => r_debrief_line.inventory_item_id
437 ,p_organization_id => r_debrief_line.organization_id
438 ,p_resource_id => p_resource_id
439 );
440 END IF;
441 END IF;
442 CLOSE c_debrief_line;
443
444 /*** Get the debrief header id ***/
445 l_debrief_header_id := Get_Debrief_Header_Id(p_debrief_line_id);
446
447 /*** Delete the debrief header ***/
448 CSL_CSF_DEBRIEF_HDR_ACC_PKG.Delete_Debrief_Header
449 ( l_debrief_header_id
450 ,p_resource_id
451 );
452
453 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
454 jtm_message_log_pkg.Log_Msg
455 ( p_debrief_line_id
456 , g_table_name
457 , 'Leaving Post_Delete_Child'
458 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
459 END IF;
460 END Post_Delete_Child;
461
462 /***
463 Public procedure that gets called when debrief lines need to be deleted into ACC table.
464 ***/
465 PROCEDURE Post_Delete_Children
466 ( p_task_assignment_id IN NUMBER
467 ,p_resource_id IN NUMBER
468 )
469 IS
470 CURSOR c_debrief_line (b_task_assignment_id NUMBER) IS
471 SELECT CDL.debrief_line_id
472 FROM CSF_DEBRIEF_HEADERS CDH, CSF_DEBRIEF_LINES CDL
473 WHERE CDH.task_assignment_id = b_task_assignment_id
474 AND CDH.debrief_header_id = CDL.debrief_header_id;
475 r_debrief_line c_debrief_line%ROWTYPE;
476
477 BEGIN
478 /*** get debug level ***/
479 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
480
481 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
482 jtm_message_log_pkg.Log_Msg
483 ( p_task_assignment_id
484 , g_table_name
485 , 'Entering Post_Delete_Children procedure'
486 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
487 END IF;
488
489 FOR r_debrief_line IN c_debrief_line( p_task_assignment_id ) LOOP
490
491 /*** Delete record if applicable ***/
492 Post_Delete_Child
493 ( r_debrief_line.debrief_line_id
494 ,p_resource_id
495 );
496 END LOOP;
497
498 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
499 jtm_message_log_pkg.Log_Msg
500 ( p_task_assignment_id
501 , g_table_name
502 , 'Leaving Post_Delete_Children procedure'
503 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
504 END IF;
505
506 END Post_Delete_Children;
507
508 /***
509 Function that retrieves resource_id for a given debrief_line_id.
510 ***/
511 FUNCTION Get_Resource_Id( p_debrief_line_id NUMBER )
512 RETURN NUMBER
513 IS
514 CURSOR c_resource ( b_debrief_line_id NUMBER)
515 IS
516 SELECT resource_id
517 FROM jtf_task_assignments jta
518 , csf_debrief_headers dbh
519 , csf_debrief_lines dbl
520 WHERE jta.task_assignment_id = dbh.task_assignment_id
521 AND dbh.debrief_header_id = dbl.debrief_header_id
522 AND dbl.debrief_line_id = b_debrief_line_id;
523 r_resource c_resource%ROWTYPE;
524 BEGIN
525 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
526 jtm_message_log_pkg.Log_Msg
527 ( p_debrief_line_id
528 , g_table_name
529 , 'Entering Get_Resource_Id function'
530 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
531 END IF;
532
533 OPEN c_resource( p_debrief_line_id );
534 FETCH c_resource INTO r_resource;
535 CLOSE c_resource;
536
537 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
538 jtm_message_log_pkg.Log_Msg
539 ( p_debrief_line_id
540 , g_table_name
541 , 'Leaving Get_Resource_Id function'
542 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
543 END IF;
544 RETURN r_resource.resource_id;
545 END Get_Resource_Id;
546
547 /* Called before debrief_line Insert */
548 PROCEDURE PRE_INSERT_DEBRIEF_LINE
549 ( x_return_status OUT NOCOPY varchar2
550 )
551 IS
552 BEGIN
553 x_return_status := FND_API.G_RET_STS_SUCCESS;
554 END PRE_INSERT_DEBRIEF_LINE;
555
556 /* Called after debrief_line Insert */
557 PROCEDURE POST_INSERT_DEBRIEF_LINE
558 ( x_return_status OUT NOCOPY varchar2
559 )
560 IS
561 l_debrief_line_id NUMBER;
562 l_resource_id NUMBER;
563 l_dummy BOOLEAN;
564 l_enabled_flag VARCHAR2(30);
565 BEGIN
566 l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP( P_APP_SHORT_NAME => 'CSL' );
567 IF l_enabled_flag <> 'Y' THEN
568 x_return_status := FND_API.G_RET_STS_SUCCESS;
569 RETURN;
570 END IF;
571 /*** get debug level ***/
572 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
573
574 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
575 jtm_message_log_pkg.Log_Msg
576 ( l_debrief_line_id
577 , g_table_name
578 , 'Entering POST_INSERT hook'
579 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
580 END IF;
581
582 /*** get debrief record details from public API ***/
583 l_debrief_line_id := CSF_DEBRIEF_LINES_PKG.user_hooks_rec.debrief_line_id;
584 l_resource_id := Get_Resource_Id( l_debrief_line_id );
585
586 /*** is resource a mobile user? ***/
587 IF NOT JTM_HOOK_UTIL_PKG.isMobileFSresource( l_resource_id ) THEN
588 /*** No -> exit ***/
589 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
590 jtm_message_log_pkg.Log_Msg
591 ( l_debrief_line_id
592 , g_table_name
593 , 'POST_INSERT_DEBRIEF_LINE' || fnd_global.local_chr(10) ||
594 'Resource_id ' || l_resource_id || ' is not a mobile user.'
595 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
596 END IF;
597 ELSE
598
599 /*** Insert record if applicable ***/
600 l_dummy := Pre_Insert_Child
601 ( l_debrief_line_id
602 ,l_resource_id
603 );
604 END IF;
605
606 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
607 jtm_message_log_pkg.Log_Msg
608 ( l_debrief_line_id
609 , g_table_name
610 , 'Leaving POST_INSERT hook'
611 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
612 END IF;
613
614 x_return_status := FND_API.G_RET_STS_SUCCESS;
615 RETURN;
616
617 EXCEPTION WHEN OTHERS THEN
618 /*** hook failed -> log error ***/
619 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
620 jtm_message_log_pkg.Log_Msg
621 ( l_debrief_line_id
622 , g_table_name
623 , 'Caught exception in POST_INSERT hook:' || fnd_global.local_chr(10) || sqlerrm
624 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
625 END IF;
626 fnd_msg_pub.Add_Exc_Msg('CSL_CSF_DEBRIEF_LINE_ACC_PKG','POST_INSERT_DEBRIEF_LINE',sqlerrm);
627 -- x_return_status := FND_API.G_RET_STS_ERROR;
628 x_return_status := FND_API.G_RET_STS_SUCCESS;
629 END POST_INSERT_DEBRIEF_LINE;
630
631 /* Called before debrief_line Update */
632 PROCEDURE PRE_UPDATE_DEBRIEF_LINE
633 ( x_return_status OUT NOCOPY varchar2
634 )
635 IS
636 l_debrief_line_id NUMBER;
637 l_resource_id NUMBER;
638 l_enabled_flag VARCHAR2(30);
639 BEGIN
640 l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP( P_APP_SHORT_NAME => 'CSL' );
641 IF l_enabled_flag <> 'Y' THEN
642 x_return_status := FND_API.G_RET_STS_SUCCESS;
643 RETURN;
644 END IF;
645 /*** get debug level ***/
646 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
647
648 /*** default value of pre replication ***/
649 g_pre_replicate := FALSE;
650
651 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
652 jtm_message_log_pkg.Log_Msg
653 ( l_debrief_line_id
654 , g_table_name
655 , 'Entering PRE_UPDATE hook'
656 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
657 END IF;
658
659 /*** get debrief_line record details from public API ***/
660 l_debrief_line_id := CSF_DEBRIEF_LINES_PKG.user_hooks_rec.debrief_line_id;
661 l_resource_id := Get_Resource_Id( l_debrief_line_id );
662
663 /*** Check if debrief_line before update matches criteria ***/
664 IF JTM_HOOK_UTIL_PKG.isMobileFSresource( l_resource_id ) THEN
665 g_pre_replicate := Replicate_Record( l_debrief_line_id );
666 END IF;
667
668 IF g_pre_replicate THEN
669 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
670 jtm_message_log_pkg.Log_Msg
671 ( l_debrief_line_id
672 , g_table_name
673 , 'Debrief line was replicated before update.'
674 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
675 END IF;
676
677 /*** retrieve old system item data from debrief line ***/
678 OPEN c_update_cache_rec( l_debrief_line_id );
679 FETCH c_update_cache_rec INTO g_pre_update_rec;
680 CLOSE c_update_cache_rec;
681
682 ELSIF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
683 jtm_message_log_pkg.Log_Msg
684 ( l_debrief_line_id
685 , g_table_name
686 , 'Debrief line was not replicated before update'
687 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
688 END IF;
689
690 x_return_status := FND_API.G_RET_STS_SUCCESS;
691
692 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
693 jtm_message_log_pkg.Log_Msg
694 ( l_debrief_line_id
695 , g_table_name
696 , 'Leaving PRE_UPDATE hook'
697 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
698 END IF;
699
700 EXCEPTION WHEN OTHERS THEN
701 /*** hook failed -> log error ***/
702 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
703 jtm_message_log_pkg.Log_Msg
704 ( l_debrief_line_id
705 , g_table_name
706 , 'Caught exception in PRE_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
707 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
708 END IF;
709 fnd_msg_pub.Add_Exc_Msg('CSL_CSF_DEBRIEF_LINE_ACC_PKG','PRE_UPDATE_DEBRIEF_LINE',sqlerrm);
710 -- x_return_status := FND_API.G_RET_STS_ERROR;
711 x_return_status := FND_API.G_RET_STS_SUCCESS;
712 g_pre_replicate := FALSE;
713 END PRE_UPDATE_DEBRIEF_LINE;
714
715 /* Called after debrief_line Update */
716 PROCEDURE POST_UPDATE_DEBRIEF_LINE
717 ( x_return_status OUT NOCOPY varchar2
718 )
719 IS
720
721 l_debrief_line_id NUMBER;
722 l_resource_id NUMBER;
723 l_replicate BOOLEAN;
724
725 l_access_id NUMBER;
726 l_dummy BOOLEAN;
727 l_enabled_flag VARCHAR2(30);
728
729 l_post_update_rec c_update_cache_rec%ROWTYPE;
730 BEGIN
731 l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP( P_APP_SHORT_NAME => 'CSL' );
732 IF l_enabled_flag <> 'Y' THEN
733 x_return_status := FND_API.G_RET_STS_SUCCESS;
734 RETURN;
735 END IF;
736 /*** get debug level ***/
737 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
738
739 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
740 jtm_message_log_pkg.Log_Msg
741 ( l_debrief_line_id
742 , g_table_name
743 , 'Entering POST_UPDATE hook'
744 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
745 END IF;
746
747 /*** get debrief_line record details from public API ***/
748 l_debrief_line_id := CSF_DEBRIEF_LINES_PKG.user_hooks_rec.debrief_line_id;
749 l_resource_id := Get_Resource_Id( l_debrief_line_id );
750
751 /*** Check if debrief_line after update matches criteria ***/
752 l_replicate := FALSE;
753 IF JTM_HOOK_UTIL_PKG.isMobileFSresource( l_resource_id ) THEN
754 l_replicate := Replicate_Record( l_debrief_line_id );
755 END IF;
756
757 IF l_replicate THEN
758 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
759 jtm_message_log_pkg.Log_Msg
760 ( l_debrief_line_id
761 , g_table_name
762 , 'Debrief line should be replicated after update.'
763 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
764 END IF;
765 ELSIF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
766 jtm_message_log_pkg.Log_Msg
767 ( l_debrief_line_id
768 , g_table_name
769 , 'Debrief line should not be replicated after update'
770 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
771 END IF;
772
773 /*** Check results from pre update ***/
774 IF g_pre_replicate THEN
775
776 /*** replicate record after update? ***/
777 IF NOT l_replicate THEN
778
779 /*** No -> Delete the record ***/
780 Post_Delete_Child
781 ( l_debrief_line_id
782 , l_resource_id );
783
784 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
785 jtm_message_log_pkg.Log_Msg
786 ( l_debrief_line_id
787 , g_table_name
788 , 'Debrief line was deleted during post update.'
789 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
790 END IF;
791
792 ELSE
793
794 /*** yes -> re-send updated debrief record to resource ***/
795 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
796 jtm_message_log_pkg.Log_Msg
797 ( l_debrief_line_id
798 , g_table_name
799 , 'Debrief line being re-sent to mobile user.'
800 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
801 END IF;
802
803 l_access_id := JTM_HOOK_UTIL_PKG.Get_Acc_Id
804 ( p_acc_table_name => g_acc_table_name
805 ,p_resource_id => l_resource_id
806 ,P_PK1_NAME => g_pk1_name
807 ,P_PK1_NUM_VALUE => l_debrief_line_id
808 );
809
810 /* Update the debrief line */
811 Update_ACC_Record
812 ( l_debrief_line_id
813 ,l_resource_id
814 ,l_access_id
815 );
816
817 /* Check if system item changed */
818 OPEN c_update_cache_rec( l_debrief_line_id );
819 FETCH c_update_cache_rec INTO l_post_update_rec;
820 IF (g_pre_update_rec.inventory_item_id <> l_post_update_rec.inventory_item_id
821 OR g_pre_update_rec.organization_id <> l_post_update_rec.organization_id) THEN
822 -- yes -> remove old item and insert new item
823 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
824 jtm_message_log_pkg.Log_Msg
825 ( l_debrief_line_id
826 , g_table_name
827 , 'System item changed -> deleting old item and inserting new item.'
828 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
829 END IF;
830
831 CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Post_Delete_Child (
832 p_inventory_item_id => g_pre_update_rec.inventory_item_id
833 ,p_organization_id => g_pre_update_rec.organization_id
834 ,p_resource_id => l_resource_id
835 );
836
837 CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Pre_Insert_Child (
838 p_inventory_item_id => l_post_update_rec.inventory_item_id
839 ,p_organization_id => l_post_update_rec.organization_id
840 ,p_resource_id => l_resource_id
841 );
842 END IF; -- system item changed
843 CLOSE c_update_cache_rec;
844 END IF;
845
846 ELSIF l_replicate THEN
847 /*** record was not replicated before update -> replicate now ***/
848 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
849 jtm_message_log_pkg.Log_Msg
850 ( l_debrief_line_id
851 , g_table_name
852 , 'Debrief line was inserted during post update.'
853 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
854 END IF;
855
856 /*** Insert record if applicable ***/
857 l_dummy := Pre_Insert_Child
858 ( l_debrief_line_id
859 ,l_resource_id
860 );
861 END IF;
862
863 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
864 jtm_message_log_pkg.Log_Msg
865 ( l_debrief_line_id
866 , g_table_name
867 , 'Leaving POST_UPDATE hook'
868 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
869 END IF;
870
871 x_return_status := FND_API.G_RET_STS_SUCCESS;
872
873 EXCEPTION WHEN OTHERS THEN
874 /*** hook failed -> log error ***/
875 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
876 jtm_message_log_pkg.Log_Msg
877 ( l_debrief_line_id
878 , g_table_name
879 , 'Caught exception in POST_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
880 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
881 END IF;
882 fnd_msg_pub.Add_Exc_Msg('CSL_CSF_DEBRIEF_LINE_ACC_PKG','POST_UPDATE_DEBRIEF_LINE',sqlerrm);
883 -- x_return_status := FND_API.G_RET_STS_ERROR;
884 x_return_status := FND_API.G_RET_STS_SUCCESS;
885 END POST_UPDATE_DEBRIEF_LINE;
886
887 /* Called before debrief_line Delete */
888 PROCEDURE PRE_DELETE_DEBRIEF_LINE
889 ( x_return_status OUT NOCOPY varchar2
890 )
891 IS
892
893 l_debrief_line_id NUMBER;
894 l_resource_id NUMBER;
895
896 BEGIN
897
898 /*** get debug level ***/
899 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
900
901 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
902 jtm_message_log_pkg.Log_Msg
903 ( v_object_id => l_debrief_line_id
904 , v_object_name => g_table_name
905 , v_message => 'Entering PRE_DELETE hook'
906 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
907 END IF;
908
909 /*** Get debrief line record details from public API and then get the resource id ***/
910 l_debrief_line_id := CSF_DEBRIEF_LINES_PKG.user_hooks_rec.debrief_line_id;
911 l_resource_id := Get_Resource_Id( l_debrief_line_id );
912
913 /*** Delete debrief line from ACC table. This also deletes its Debrief Header ***/
914 Post_Delete_Child ( l_debrief_line_id
915 , l_resource_id );
916
917 x_return_status := FND_API.G_RET_STS_SUCCESS;
918
919 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
920 jtm_message_log_pkg.Log_Msg
921 ( v_object_id => l_debrief_line_id
922 , v_object_name => g_table_name
923 , v_message => 'Leaving PRE_DELETE hook'
924 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
925 END IF;
926
927 EXCEPTION WHEN OTHERS THEN
928
929 /*** hook failed -> log error ***/
930 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
931 jtm_message_log_pkg.Log_Msg
932 ( v_object_id => l_debrief_line_id
933 , v_object_name => g_table_name
934 , v_message => 'Caught exception in PRE_DELETE hook:' || fnd_global.local_chr(10) || sqlerrm
935 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
936 END IF;
937
938 fnd_msg_pub.Add_Exc_Msg('CSL_CSF_DEBRIEF_LINE_ACC_PKG','PRE_DELETE_DEBRIEF_LINE',sqlerrm);
939
940 x_return_status := FND_API.G_RET_STS_SUCCESS;
941 END PRE_DELETE_DEBRIEF_LINE;
942
943 /* Called after debrief_line Delete */
944 PROCEDURE POST_DELETE_DEBRIEF_LINE
945 ( x_return_status OUT NOCOPY varchar2
946 )
947 IS
948 BEGIN
949 x_return_status := FND_API.G_RET_STS_SUCCESS;
950 END POST_DELETE_DEBRIEF_LINE;
951
952 END CSL_CSF_DEBRIEF_LINE_ACC_PKG;