[Home] [Help]
PACKAGE BODY: APPS.CSL_CSP_REQ_LINES_ACC_PKG
Source
1 PACKAGE BODY CSL_CSP_REQ_LINES_ACC_PKG AS
2 /* $Header: cslrlacb.pls 120.0 2005/05/24 17:19:34 appldev noship $ */
3
4 /*** Globals ***/
5 -- CSP_REQUIREMENT_HEADERS
6 g_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_CSP_REQ_LINES_ACC';
7 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
8 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSP_REQUIREMENT_LINES');
9 g_pk1_name CONSTANT VARCHAR2(30) := 'REQUIREMENT_LINE_ID';
10
11 -- OE_ORDER_HEADERS_ALL
12 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'JTM_OE_ORDER_HEADERS_ALL_ACC';
13 g_publication_item_name1 CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
14 JTM_HOOK_UTIL_PKG.t_publication_item_list('OE_ORDER_HEADERS_ALL');
15 g_pk1_name1 CONSTANT VARCHAR2(30) := 'HEADER_ID';
16
17 -- OE_ORDER_LINES_ALL
18 g_acc_table_name2 CONSTANT VARCHAR2(30) := 'JTM_OE_ORDER_LINES_ALL_ACC';
19 g_publication_item_name2 CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
20 JTM_HOOK_UTIL_PKG.t_publication_item_list('OE_ORDER_LINES_ALL');
21 g_pk1_name2 CONSTANT VARCHAR2(30) := 'LINE_ID';
22
23 g_table_name CONSTANT VARCHAR2(30) := 'CSP_REQUIREMENT_LINES';
24 g_debug_level NUMBER; -- debug level
25
26 /*** Function that checks if requirement record(s) should be replicated. Returns TRUE if it should ***/
27 FUNCTION Replicate_Record
28 ( p_req_line_id NUMBER
29 )
30 RETURN BOOLEAN
31 IS
32 CURSOR c_req_resource (b_req_line_id NUMBER) IS
33 SELECT RH.resource_id
34 FROM CSP_REQUIREMENT_HEADERS RH
35 , CSP_REQUIREMENT_LINES RL
36 WHERE RH.REQUIREMENT_HEADER_ID = RL.REQUIREMENT_HEADER_ID
37 AND RL.REQUIREMENT_LINE_ID = b_req_line_id;
38
39 /* CURSOR c_req_task_ass( b_req_line_id NUMBER ) IS
40 SELECT jta.resource_id
41 FROM jtf.jtf_task_assignments jta
42 , csp_requirement_headers crh
43 , csp_requirement_lines crl
44 WHERE crh.task_assignment_id = jta.task_assignment_id
45 AND jta.assignee_role = 'ASSIGNEE'
46 AND crh.requirement_header_id = crl.requirement_header_id
47 AND crl.requirement_line_id = b_req_line_id;*/
48
49 l_resource_id NUMBER;
50 BEGIN
51 /*** get debug level ***/
52 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
53
54 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
55 jtm_message_log_pkg.Log_Msg
56 ( p_req_line_id
57 , g_table_name
58 , 'Entering Replicate_Record'
59 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
60 END IF;
61
62 OPEN c_req_resource( p_req_line_id );
63 FETCH c_req_resource INTO l_resource_id;
64 IF c_req_resource%NOTFOUND THEN
65 -- OPEN c_req_task_ass( p_req_line_id );
66 -- FETCH c_req_task_ass INTO l_resource_id;
67 -- IF c_req_task_ass%NOTFOUND THEN
68 l_resource_id := -1;
69 -- END IF;
70 -- CLOSE c_req_task_ass;
71 END IF;
72 CLOSE c_req_resource;
73
74 IF l_resource_id < 0 THEN
75 /*** could not find requirement record -> exit ***/
76 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
77 jtm_message_log_pkg.Log_Msg
78 ( p_req_line_id
79 , g_table_name
80 , 'Requirement not created for specific resource; not replicating record'
81 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
82 END IF;
83 RETURN FALSE;
84 END IF;
85
86 /*** is resource a mobile user? ***/
87 IF NOT JTM_HOOK_UTIL_PKG.isMobileFSresource( l_resource_id ) THEN
88 /*** No -> exit ***/
89 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
90 jtm_message_log_pkg.Log_Msg
91 ( p_req_line_id
92 , g_table_name
93 , 'Replicate_Record returned FALSE' || fnd_global.local_chr(10) ||
94 'Resource_id ' || l_resource_id || ' is not a mobile user.'
95 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
96 END IF;
97
98 RETURN FALSE;
99 END IF;
100
101
102 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
103 jtm_message_log_pkg.Log_Msg
104 ( p_req_line_id
105 , g_table_name
106 , 'Replicate_Record returned TRUE'
107 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
108 END IF;
109
110 /** Record matched criteria -> return true ***/
111 RETURN TRUE;
112 END Replicate_Record;
113
114 /*** Private procedure that replicates given requierment related data for resource ***/
115 PROCEDURE Insert_ACC_Record
116 ( p_req_line_id IN NUMBER
117 )
118 IS
119 CURSOR c_req_info (b_req_line_id NUMBER) IS
120 SELECT RH.resource_id
121 , OH.HEADER_ID
122 , OL.LINE_ID
123 , RH.DESTINATION_ORGANIZATION_ID
124 , RL.INVENTORY_ITEM_ID
125 FROM CSP_REQUIREMENT_HEADERS RH
126 , CSP_REQUIREMENT_LINES RL
127 , OE_ORDER_LINES_ALL OL
128 , OE_ORDER_HEADERS_ALL OH
129 WHERE RH.REQUIREMENT_HEADER_ID = RL.REQUIREMENT_HEADER_ID
130 AND RL.REQUIREMENT_LINE_ID = b_req_line_id
131 AND RL.ORDER_LINE_ID = OL.LINE_ID (+)
132 AND OL.HEADER_ID = OH.HEADER_ID (+);
133
134 r_req_info c_req_info%ROWTYPE;
135
136 /* CURSOR c_req_task_ass( b_req_line_id NUMBER ) IS
137 SELECT jta.resource_id
138 , oh.header_id
139 , ol.line_id
140 FROM jtf.jtf_task_assignments jta
141 , csp_requirement_headers crh
142 , csp_requirement_lines crl
143 , oe_order_headers_all oh
144 , oe_order_lines_all ol
145 WHERE crh.task_assignment_id = jta.task_assignment_id
146 AND jta.assignee_role = 'ASSIGNEE'
147 AND crh.requirement_header_id = crl.requirement_header_id
148 AND crl.requirement_line_id = b_req_line_id
149 AND crl.order_line_id = ol.line_id(+)
150 AND ol.header_id = oh.header_id(+);*/
151
152
153 -- r_req_task_ass c_req_task_ass%ROWTYPE;
154 l_resource_id NUMBER;
155 l_header_id NUMBER;
156 l_line_id NUMBER;
157 BEGIN
158 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
159 jtm_message_log_pkg.Log_Msg
160 ( p_req_line_id
161 , g_table_name
162 , 'Entering Insert_ACC_Record'
163 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
164 END IF;
165
166 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
167 jtm_message_log_pkg.Log_Msg
168 ( p_req_line_id
169 , g_table_name
170 , 'Inserting ACC record'
171 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
172 END IF;
173
174 OPEN c_req_info( p_req_line_id );
175 FETCH c_req_info INTO r_req_info;
176 IF c_req_info%NOTFOUND THEN
177 -- OPEN c_req_task_ass( p_req_line_id );
178 -- FETCH c_req_task_ass INTO r_req_task_ass;
179 -- IF c_req_task_ass%NOTFOUND THEN
180 l_resource_id := -1;
181 -- ELSE
182 -- l_resource_id := r_req_task_ass.resource_id;
183 -- l_header_id := r_req_task_ass.header_id;
184 -- l_line_id := r_req_task_ass.line_id;
185 -- END IF;
186 -- CLOSE c_req_task_ass;
187 ELSE
188 l_resource_id := r_req_info.resource_id;
189 l_header_id := r_req_info.header_id;
190 l_line_id := r_req_info.line_id;
191 END IF;
192
193 CLOSE c_req_info;
194
195 IF l_resource_id < 0 THEN
196 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
197 jtm_message_log_pkg.Log_Msg
198 ( p_req_line_id
199 , g_table_name
200 , 'Requirement not created for specific resource; not replicating record'
201 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
202 END IF;
203 ELSE
204 /*** check if requirement has an item and organization ***/
205 IF r_req_info.inventory_item_id IS NOT NULL
206 AND r_req_info.destination_organization_id IS NOT NULL THEN
207 /*** yes -> replicate item ***/
208 csl_mtl_system_items_acc_pkg.pre_insert_child(
209 r_req_info.inventory_item_id
210 ,r_req_info.destination_organization_id
211 ,l_resource_id);
212 END IF;
213
214 JTM_HOOK_UTIL_PKG.Insert_Acc
215 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
216 , P_ACC_TABLE_NAME => g_acc_table_name
217 , P_PK1_NAME => g_pk1_name
218 , P_PK1_NUM_VALUE => p_req_line_id
219 , P_RESOURCE_ID => l_resource_id
220 );
221
222 IF (l_header_id IS NOT NULL) THEN
223 -- OE_ORDER_HEADERS_ALL
224 JTM_HOOK_UTIL_PKG.Insert_Acc
225 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
226 , P_ACC_TABLE_NAME => g_acc_table_name1
227 , P_PK1_NAME => g_pk1_name1
228 , P_PK1_NUM_VALUE => l_header_id
229 , P_RESOURCE_ID => l_resource_id
230 );
231 -- OE_ORDER_LINES_ALL
232 JTM_HOOK_UTIL_PKG.Insert_Acc
233 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
234 , P_ACC_TABLE_NAME => g_acc_table_name2
235 , P_PK1_NAME => g_pk1_name2
236 , P_PK1_NUM_VALUE => l_line_id
237 , P_RESOURCE_ID => l_resource_id
238 );
239 END IF;
240 END IF;
241
242 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
243 jtm_message_log_pkg.Log_Msg
244 ( p_req_line_id
245 , g_table_name
246 , 'Leaving Insert_ACC_Record'
247 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
248 END IF;
249 END Insert_ACC_Record;
250
251 /*** Private procedure that re-sends given requirement to mobile ***/
252 PROCEDURE Update_ACC_Record
253 ( p_req_line_id IN NUMBER
254 )
255 IS
256 CURSOR c_req_info (b_req_line_id NUMBER) IS
257 SELECT RH.resource_id
258 , OH.HEADER_ID
259 , OL.LINE_ID
260 , RH.DESTINATION_ORGANIZATION_ID
261 , RL.INVENTORY_ITEM_ID
262 FROM CSP_REQUIREMENT_HEADERS RH
263 , CSP_REQUIREMENT_LINES RL
264 , OE_ORDER_LINES_ALL OL
265 , OE_ORDER_HEADERS_ALL OH
266 WHERE RH.REQUIREMENT_HEADER_ID = RL.REQUIREMENT_HEADER_ID
267 AND RL.REQUIREMENT_LINE_ID = b_req_line_id
268 AND RL.ORDER_LINE_ID = OL.LINE_ID (+)
269 AND OL.HEADER_ID = OH.HEADER_ID (+);
270
271 r_req_info c_req_info%ROWTYPE;
272
273 /* CURSOR c_req_task_ass( b_req_line_id NUMBER ) IS
274 SELECT jta.resource_id
275 , oh.header_id
276 , ol.line_id
277 FROM jtf.jtf_task_assignments jta
278 , csp_requirement_headers crh
279 , csp_requirement_lines crl
280 , oe_order_headers_all oh
281 , oe_order_lines_all ol
282 WHERE crh.task_assignment_id = jta.task_assignment_id
283 AND jta.assignee_role = 'ASSIGNEE'
284 AND crh.requirement_header_id = crl.requirement_header_id
285 AND crl.requirement_line_id = b_req_line_id
286 AND crl.order_line_id = ol.line_id(+)
287 AND ol.header_id = oh.header_id(+);*/
288
289
290 -- r_req_task_ass c_req_task_ass%ROWTYPE;
291 l_resource_id NUMBER;
292 l_header_id NUMBER;
293 l_line_id NUMBER;
294 BEGIN
295 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
296 jtm_message_log_pkg.Log_Msg
297 ( p_req_line_id
298 , g_table_name
299 , 'Entering Update_ACC_Record'
300 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
301 END IF;
302
303 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
304 jtm_message_log_pkg.Log_Msg
305 ( p_req_line_id
306 , g_table_name
307 , 'Updating ACC record(s)'
308 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
309 END IF;
310
311 OPEN c_req_info( p_req_line_id );
312 FETCH c_req_info INTO r_req_info;
313 IF c_req_info%NOTFOUND THEN
314 -- OPEN c_req_task_ass( p_req_line_id );
315 -- FETCH c_req_task_ass INTO r_req_task_ass;
316 -- IF c_req_task_ass%NOTFOUND THEN
317 l_resource_id := -1;
318 -- ELSE
319 -- l_resource_id := r_req_task_ass.resource_id;
320 -- l_header_id := r_req_task_ass.header_id;
321 -- l_line_id := r_req_task_ass.line_id;
322 -- END IF;
323 -- CLOSE c_req_task_ass;
324 ELSE
325 l_resource_id := r_req_info.resource_id;
326 l_header_id := r_req_info.header_id;
327 l_line_id := r_req_info.line_id;
328 END IF;
329 CLOSE c_req_info;
330
331 IF l_resource_id < 0 THEN
332 /*** could not find requirement record -> exit ***/
333 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
334 jtm_message_log_pkg.Log_Msg
335 ( p_req_line_id
336 , g_table_name
337 , 'Update ACC Record error: Could not find resource for requirement line '|| p_req_line_id
338 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
339 END IF;
340 ELSE
341 /*** check if requirement has an item and organization ***/
342 IF r_req_info.inventory_item_id IS NOT NULL
343 AND r_req_info.destination_organization_id IS NOT NULL THEN
344 /*** yes -> replicate item ***/
345 csl_mtl_system_items_acc_pkg.pre_insert_child(
346 r_req_info.inventory_item_id
347 ,r_req_info.destination_organization_id
348 ,l_resource_id);
349 END IF;
350
351 JTM_HOOK_UTIL_PKG.Update_Acc
352 ( g_publication_item_name
353 ,g_acc_table_name
354 ,l_resource_id
355 ,p_req_line_id
356 );
357
358 IF (l_header_id IS NOT NULL) THEN
359 -- OE_ORDER_HEADERS_ALL
360 JTM_HOOK_UTIL_PKG.Insert_Acc
361 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
362 , P_ACC_TABLE_NAME => g_acc_table_name1
363 , P_PK1_NAME => g_pk1_name1
364 , P_PK1_NUM_VALUE => l_header_id
365 , P_RESOURCE_ID => l_resource_id
366 );
367 -- OE_ORDER_LINES_ALL
368 JTM_HOOK_UTIL_PKG.Insert_Acc
369 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
370 , P_ACC_TABLE_NAME => g_acc_table_name2
371 , P_PK1_NAME => g_pk1_name2
372 , P_PK1_NUM_VALUE => l_line_id
373 , P_RESOURCE_ID => l_resource_id
374 );
375 END IF;
376 END IF;
377
378 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
379 jtm_message_log_pkg.Log_Msg
380 ( p_req_line_id
381 , g_table_name
382 , 'Leaving Update_ACC_Record'
383 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
384 END IF;
385 END Update_ACC_Record;
386
387 /*** Private procedure that deletes requirement for resource from acc table ***/
388 PROCEDURE Delete_ACC_Record
389 ( p_req_line_id IN NUMBER
390 ,p_resource_id IN NUMBER
391 )
392 IS
393 CURSOR c_req_line_id (b_req_line_id NUMBER) IS
394 SELECT RH.resource_id
395 , OH.HEADER_ID
396 , OL.LINE_ID
397 FROM CSP_REQUIREMENT_HEADERS RH
398 , CSP_REQUIREMENT_LINES RL
399 , OE_ORDER_LINES_ALL OL
400 , OE_ORDER_HEADERS_ALL OH
401 WHERE RH.REQUIREMENT_HEADER_ID = RL.REQUIREMENT_HEADER_ID
402 AND RL.REQUIREMENT_LINE_ID = b_req_line_id
403 AND RL.ORDER_LINE_ID = OL.LINE_ID (+)
404 AND OL.HEADER_ID = OH.HEADER_ID (+);
405
406 r_req_line_id c_req_line_id%ROWTYPE;
407
408 l_header_id NUMBER;
409 l_line_id NUMBER;
410
411 BEGIN
412 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
413 jtm_message_log_pkg.Log_Msg
414 ( p_req_line_id
415 , g_table_name
416 , 'Entering Delete_ACC_Record'
417 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
418 END IF;
419
420 OPEN c_req_line_id( p_req_line_id );
421 FETCH c_req_line_id INTO r_req_line_id;
422 IF c_req_line_id%NOTFOUND THEN
423 /*** could not find requirement record -> exit ***/
424 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
425 jtm_message_log_pkg.Log_Msg
426 ( p_req_line_id
430 END IF;
427 , g_table_name
428 , 'Could not find record associated with CSP_REQUIREMENT_LINES.REQUIREMENT_LINE_ID ' || p_req_line_id
429 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
431 ELSE
432 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
433 jtm_message_log_pkg.Log_Msg
434 ( p_req_line_id
435 , g_table_name
436 , 'Deleting ACC record for resource_id = ' || p_resource_id
437 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
438 END IF;
439
440 -- No delete of the requirement is possible
441 JTM_HOOK_UTIL_PKG.Delete_Acc
442 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
443 , P_ACC_TABLE_NAME => g_acc_table_name
444 , P_PK1_NAME => g_pk1_name
445 , P_PK1_NUM_VALUE => p_req_line_id
446 , P_RESOURCE_ID => p_resource_id
447 );
448
449 l_header_id := r_req_line_id.HEADER_ID;
450 l_line_id := r_req_line_id.LINE_ID;
451 IF (l_header_id IS NOT NULL) THEN
452
453 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
454 jtm_message_log_pkg.Log_Msg
455 ( p_resource_id
456 , g_table_name
457 , 'Delete Order Header acc record for user: ' || p_resource_id
458 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
459 END IF;
460 JTM_HOOK_UTIL_PKG.Delete_Acc
461 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
462 , P_ACC_TABLE_NAME => g_acc_table_name1
463 , P_PK1_NAME => g_pk1_name1
464 , P_PK1_NUM_VALUE => l_header_id
465 , P_RESOURCE_ID => p_resource_id
466 );
467
468 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
469 jtm_message_log_pkg.Log_Msg
470 ( p_resource_id
471 , g_table_name
472 , 'Delete Order Line acc record for user: ' || p_resource_id
473 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
474 END IF;
475 JTM_HOOK_UTIL_PKG.Delete_Acc
476 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
477 , P_ACC_TABLE_NAME => g_acc_table_name2
478 , P_PK1_NAME => g_pk1_name2
479 , P_PK1_NUM_VALUE => l_line_id
480 , P_RESOURCE_ID => p_resource_id
481 );
482 END IF;
483 END IF;
484 CLOSE c_req_line_id;
485
486 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
487 jtm_message_log_pkg.Log_Msg
488 ( p_req_line_id
489 , g_table_name
490 , 'Leaving Delete_ACC_Record'
491 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
492 END IF;
493 END Delete_ACC_Record;
494
495 /*** Called before requirement Insert ***/
496 PROCEDURE PRE_INSERT_REQ_LINE
497 ( x_return_status OUT NOCOPY varchar2
498 )
499 IS
500 BEGIN
501 x_return_status := FND_API.G_RET_STS_SUCCESS;
502 END PRE_INSERT_REQ_LINE;
503
504 /*** Called after requirement Insert ***/
505 PROCEDURE POST_INSERT_REQ_LINE
506 ( x_return_status OUT NOCOPY varchar2
507 )
508 IS
509 l_req_line_id NUMBER;
510 l_enabled_flag VARCHAR2(30);
511 BEGIN
512 l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP( P_APP_SHORT_NAME => 'CSL');
513 IF l_enabled_flag <> 'Y' THEN
514 x_return_status := FND_API.G_RET_STS_SUCCESS;
515 RETURN;
516 END IF;
517 /*** get debug level ***/
518 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
519 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
520 jtm_message_log_pkg.Log_Msg
521 ( l_req_line_id
522 , g_table_name
523 , 'Entering POST_INSERT hook'
524 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
525 END IF;
526
527 /*** get location record details from public API ***/
528 l_req_line_id := CSP_REQUIREMENT_LINES_PKG.user_hook_rec.REQUIREMENT_LINE_ID;
529
530 /*** Insert record if applicable ***/
531 IF Replicate_Record(l_req_line_id) THEN
532 Insert_ACC_Record(l_req_line_id);
533 END IF;
534
535 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
536 jtm_message_log_pkg.Log_Msg
537 ( l_req_line_id
538 , g_table_name
539 , 'Leaving POST_INSERT hook'
540 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
541 END IF;
542
543 x_return_status := FND_API.G_RET_STS_SUCCESS;
544 RETURN;
545
546 EXCEPTION WHEN OTHERS THEN
547 /*** hook failed -> log error ***/
548 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
549 jtm_message_log_pkg.Log_Msg
550 ( l_req_line_id
551 , g_table_name
552 , 'Caught exception in POST_INSERT hook:' || fnd_global.local_chr(10) || sqlerrm
553 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
554 END IF;
555 fnd_msg_pub.Add_Exc_Msg('CSL_CSP_REQ_LINES_ACC_PKG','POST_INSERT_REQ_LINE',sqlerrm);
556 -- x_return_status := FND_API.G_RET_STS_ERROR;
557 x_return_status := FND_API.G_RET_STS_SUCCESS;
558 END POST_INSERT_REQ_LINE;
559
560 /* Called before requirement Update */
561 PROCEDURE PRE_UPDATE_REQ_LINE
562 ( x_return_status OUT NOCOPY varchar2
563 )
564 IS
565 BEGIN
566 x_return_status := FND_API.G_RET_STS_SUCCESS;
567
568 EXCEPTION WHEN OTHERS THEN
569 /*** hook failed -> log error ***/
573 END PRE_UPDATE_REQ_LINE;
570 fnd_msg_pub.Add_Exc_Msg('CSL_CSP_REQ_LINES_ACC_PKG','PRE_UPDATE_REQ_LINE',sqlerrm);
571 -- x_return_status := FND_API.G_RET_STS_ERROR;
572 x_return_status := FND_API.G_RET_STS_SUCCESS;
574
575 /* Called after requirement Update */
576 PROCEDURE POST_UPDATE_REQ_LINE
577 ( x_return_status OUT NOCOPY varchar2
578 )
579 IS
580 l_req_line_id NUMBER;
581 l_enabled_flag VARCHAR2(30);
582 BEGIN
583 l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP( P_APP_SHORT_NAME => 'CSL');
584 IF l_enabled_flag <> 'Y' THEN
585 x_return_status := FND_API.G_RET_STS_SUCCESS;
586 RETURN;
587 END IF;
588 /*** get debug level ***/
589 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
590
591 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
592 jtm_message_log_pkg.Log_Msg
593 ( l_req_line_id
594 , g_table_name
595 , 'Entering POST_UPDATE hook'
596 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
597 END IF;
598
599 /*** get location record details from public API ***/
600 l_req_line_id := CSP_REQUIREMENT_LINES_PKG.user_hook_rec.REQUIREMENT_LINE_ID;
601
602 IF Replicate_Record( l_req_line_id ) THEN
603 Update_ACC_Record(l_req_line_id);
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_req_line_id
609 , g_table_name
610 , 'Leaving POST_UPDATE 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 EXCEPTION WHEN OTHERS THEN
616 /*** hook failed -> log error ***/
617 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
618 jtm_message_log_pkg.Log_Msg
619 ( l_req_line_id
620 , g_table_name
621 , 'Caught exception in POST_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
622 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
623 END IF;
624 fnd_msg_pub.Add_Exc_Msg('CSL_CSP_REQ_LINES_ACC_PKG','POST_UPDATE_REQ_LINE',sqlerrm);
625 -- x_return_status := FND_API.G_RET_STS_ERROR;
626 x_return_status := FND_API.G_RET_STS_SUCCESS;
627 END POST_UPDATE_REQ_LINE;
628
629 /* Called before req header Delete */
630 PROCEDURE PRE_DELETE_REQ_LINE
631 ( x_return_status OUT NOCOPY varchar2
632 )
633 IS
634 BEGIN
635 x_return_status := FND_API.G_RET_STS_SUCCESS;
636 END PRE_DELETE_REQ_LINE;
637
638 /* Called after req header Delete */
639 PROCEDURE POST_DELETE_REQ_LINE
640 ( x_return_status OUT NOCOPY varchar2
641 )
642 IS
643 BEGIN
644
645 x_return_status := FND_API.G_RET_STS_SUCCESS;
646
647 EXCEPTION WHEN OTHERS THEN
648 /*** hook failed -> log error ***/
649 fnd_msg_pub.Add_Exc_Msg('CSL_CSP_REQ_LINES_ACC_PKG','POST_DELETE_REQ_LINE',sqlerrm);
650 -- x_return_status := FND_API.G_RET_STS_ERROR;
651 x_return_status := FND_API.G_RET_STS_SUCCESS;
652 END POST_DELETE_REQ_LINE;
653
654
655
656 /* Remove all ACC resords of a mobile user */
657 PROCEDURE Delete_All_ACC_Records
658 ( p_resource_id in NUMBER
659 , x_return_status OUT NOCOPY varchar2
660 )
661 IS
662 CURSOR c_req_resource (b_resource_id NUMBER) IS
663 SELECT RL.REQUIREMENT_LINE_ID
664 FROM CSP_REQUIREMENT_HEADERS RH
665 , CSP_REQUIREMENT_LINES RL
666 WHERE RH.REQUIREMENT_HEADER_ID = RL.REQUIREMENT_HEADER_ID
667 AND RH.RESOURCE_ID = b_resource_id;
668
669 /* CURSOR c_req_task_ass( b_resource_id NUMBER ) IS
670 SELECT crl.requirement_line_id
671 FROM jtf.jtf_task_assignments jta
672 , csp_requirement_headers crh
673 , csp_requirement_lines crl
674 WHERE crh.task_assignment_id = jta.task_assignment_id
675 AND jta.assignee_role = 'ASSIGNEE'
676 AND crh.requirement_header_id = crl.requirement_header_id
677 AND jta.resource_id = b_resource_id;*/
678
679 BEGIN
680
681 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
682 jtm_message_log_pkg.Log_Msg
683 ( p_resource_id
684 , g_table_name
685 , 'Entering Delete_All_ACC_Records procedure for user: ' || p_resource_id
686 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
687 END IF;
688
689 /*First all resource based lines */
690 FOR r_req_resource IN c_req_resource( p_resource_id ) LOOP
691 Delete_Acc_Record( r_req_resource.requirement_line_id, p_resource_id );
692 END LOOP;
693
694 /* Second all task assignment based lines */
695 -- FOR r_req_task_ass IN c_req_task_ass( p_resource_id ) LOOP
696 -- Delete_Acc_Record( r_req_task_ass.requirement_line_id, p_resource_id );
697 -- END LOOP;
698
699 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
700 jtm_message_log_pkg.Log_Msg
701 ( p_resource_id
702 , g_table_name
703 , 'Leaving Delete_All_ACC_Records procedure for user: ' || p_resource_id
704 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
705 END IF;
706
707
708 x_return_status := FND_API.G_RET_STS_SUCCESS;
709 EXCEPTION WHEN OTHERS THEN
710 /*** hook failed -> log error ***/
711 fnd_msg_pub.Add_Exc_Msg('CSL_CSP_REQ_LINES_ACC_PKG','Delete_All_ACC_Records',sqlerrm);
712 -- x_return_status := FND_API.G_RET_STS_ERROR;
713 x_return_status := FND_API.G_RET_STS_SUCCESS;
714 END Delete_All_ACC_Records;
715
716 /* Full synch for a mobile user */
717 PROCEDURE Insert_All_ACC_Records
718 ( p_resource_id in NUMBER
719 , x_return_status OUT NOCOPY varchar2
720 )
721 IS
722 CURSOR c_req_resource (b_resource_id NUMBER) IS
723 SELECT RL.REQUIREMENT_LINE_ID
724 FROM CSP_REQUIREMENT_HEADERS RH
725 , CSP_REQUIREMENT_LINES RL
726 WHERE RH.REQUIREMENT_HEADER_ID = RL.REQUIREMENT_HEADER_ID
727 AND RH.RESOURCE_ID = b_resource_id;
728
729 /* CURSOR c_req_task_ass( b_resource_id NUMBER ) IS
730 SELECT crl.requirement_line_id
731 FROM jtf.jtf_task_assignments jta
732 , csp_requirement_headers crh
733 , csp_requirement_lines crl
734 WHERE crh.task_assignment_id = jta.task_assignment_id
735 AND jta.assignee_role = 'ASSIGNEE'
736 AND crh.requirement_header_id = crl.requirement_header_id
737 AND jta.resource_id = b_resource_id;*/
738
739 BEGIN
740
741 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
742 jtm_message_log_pkg.Log_Msg
743 ( p_resource_id
744 , g_table_name
745 , 'Entering Insert_All_ACC_Records procedure for user: ' || p_resource_id
746 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
747 END IF;
748
749 /*First all resource based lines*/
750 FOR r_req_resource IN c_req_resource( p_resource_id ) LOOP
751 IF Replicate_Record ( r_req_resource.requirement_line_id ) THEN
752 Insert_Acc_Record( r_req_resource.requirement_line_id );
753 END IF;
754 END LOOP;
755
756 /*Second all task assignment based lines*/
757 -- FOR r_req_task_ass IN c_req_task_ass( p_resource_id ) LOOP
758 -- IF Replicate_Record ( r_req_task_ass.requirement_line_id ) THEN
759 -- Insert_Acc_Record( r_req_task_ass.requirement_line_id );
760 -- END IF;
761 -- END LOOP;
762
763 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
764 jtm_message_log_pkg.Log_Msg
765 ( p_resource_id
766 , g_table_name
767 , 'Leaving Insert_All_ACC_Records procedure for user: ' || p_resource_id
768 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
769 END IF;
770
771 x_return_status := FND_API.G_RET_STS_SUCCESS;
772 EXCEPTION WHEN OTHERS THEN
773 /*** hook failed -> log error ***/
774 fnd_msg_pub.Add_Exc_Msg('CSL_CSP_REQ_LINES_ACC_PKG','Insert_All_ACC_Records',sqlerrm);
775 -- x_return_status := FND_API.G_RET_STS_ERROR;
776 x_return_status := FND_API.G_RET_STS_SUCCESS;
777 END Insert_All_ACC_Records;
778
779 END CSL_CSP_REQ_LINES_ACC_PKG;