[Home] [Help]
2: /* $Header: cslinacb.pls 120.0 2005/05/30 07:41:03 appldev noship $ */
3:
4: /*** Globals ***/
5: g_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_CS_INCIDENTS_ALL_ACC';
6: g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
7: JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_CS_INCIDENTS_ALL_VL');
8: g_table_name CONSTANT VARCHAR2(30) := 'CS_INCIDENTS_ALL';
9: g_pk1_name CONSTANT VARCHAR2(30) := 'INCIDENT_ID';
10:
3:
4: /*** Globals ***/
5: g_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_CS_INCIDENTS_ALL_ACC';
6: g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
7: JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_CS_INCIDENTS_ALL_VL');
8: g_table_name CONSTANT VARCHAR2(30) := 'CS_INCIDENTS_ALL';
9: g_pk1_name CONSTANT VARCHAR2(30) := 'INCIDENT_ID';
10:
11: g_debug_level NUMBER; -- debug level
36: r_incident c_incident%ROWTYPE;
37:
38: BEGIN
39: /*** get debug level ***/
40: g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
41:
42: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
43: jtm_message_log_pkg.Log_Msg
44: ( p_incident_id
38: BEGIN
39: /*** get debug level ***/
40: g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
41:
42: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
43: jtm_message_log_pkg.Log_Msg
44: ( p_incident_id
45: , g_table_name
46: , 'Entering Replicate_Record'
43: jtm_message_log_pkg.Log_Msg
44: ( p_incident_id
45: , g_table_name
46: , 'Entering Replicate_Record'
47: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
48: END IF;
49:
50: OPEN c_incident( p_incident_id );
51: FETCH c_incident INTO r_incident;
50: OPEN c_incident( p_incident_id );
51: FETCH c_incident INTO r_incident;
52: IF c_incident%NOTFOUND THEN
53: /*** could not find incident record -> exit ***/
54: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
55: jtm_message_log_pkg.Log_Msg
56: ( p_incident_id
57: , g_table_name
58: , 'Replicate_Record error: Could not find incident_id ' || p_incident_id
55: jtm_message_log_pkg.Log_Msg
56: ( p_incident_id
57: , g_table_name
58: , 'Replicate_Record error: Could not find incident_id ' || p_incident_id
59: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
60: END IF;
61:
62: CLOSE c_incident;
63: RETURN FALSE;
63: RETURN FALSE;
64: END IF;
65: CLOSE c_incident;
66:
67: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
68: jtm_message_log_pkg.Log_Msg
69: ( p_incident_id
70: , g_table_name
71: , 'Replicate_Record returned TRUE'
68: jtm_message_log_pkg.Log_Msg
69: ( p_incident_id
70: , g_table_name
71: , 'Replicate_Record returned TRUE'
72: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
73: END IF;
74:
75: /** Record matched criteria -> return true ***/
76: RETURN TRUE;
98: l_cursorid INTEGER;
99: l_execute_status INTEGER;
100:
101: BEGIN
102: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
103: jtm_message_log_pkg.Log_Msg
104: ( p_incident_id
105: , g_table_name
106: , 'Entering Insert_ACC_Record'
103: jtm_message_log_pkg.Log_Msg
104: ( p_incident_id
105: , g_table_name
106: , 'Entering Insert_ACC_Record'
107: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
108: END IF;
109:
110: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
111: jtm_message_log_pkg.Log_Msg
106: , 'Entering Insert_ACC_Record'
107: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
108: END IF;
109:
110: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
111: jtm_message_log_pkg.Log_Msg
112: ( p_incident_id
113: , g_table_name
114: , 'Inserting ACC record for resource_id = ' || p_resource_id
111: jtm_message_log_pkg.Log_Msg
112: ( p_incident_id
113: , g_table_name
114: , 'Inserting ACC record for resource_id = ' || p_resource_id
115: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
116: END IF;
117:
118: /*** Insert incident ACC record ***/
119: JTM_HOOK_UTIL_PKG.Insert_Acc
115: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
116: END IF;
117:
118: /*** Insert incident ACC record ***/
119: JTM_HOOK_UTIL_PKG.Insert_Acc
120: ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
121: ,P_ACC_TABLE_NAME => g_acc_table_name
122: ,P_RESOURCE_ID => p_resource_id
123: ,P_PK1_NAME => g_pk1_name
132: ***************************************************************/
133: OPEN c_incident( p_incident_id );
134: FETCH c_incident INTO r_incident;
135: IF c_incident%FOUND THEN
136: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
137: jtm_message_log_pkg.Log_Msg
138: ( p_incident_id
139: , g_table_name
140: , 'Inserting non-critical dependant records'
137: jtm_message_log_pkg.Log_Msg
138: ( p_incident_id
139: , g_table_name
140: , 'Inserting non-critical dependant records'
141: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
142: END IF;
143:
144: -- PARTY
145: IF r_incident.customer_id IS NOT NULL THEN
211: end;
212: DBMS_SQL.close_cursor (l_cursorid);
213:
214: /*Done, all packages are called*/
215: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
216: jtm_message_log_pkg.Log_Msg
217: ( p_incident_id
218: , g_table_name
219: , 'Leaving Insert_ACC_Record'
216: jtm_message_log_pkg.Log_Msg
217: ( p_incident_id
218: , g_table_name
219: , 'Leaving Insert_ACC_Record'
220: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
221: END IF;
222:
223: END Insert_ACC_Record;
224:
231: ,p_acc_id IN NUMBER
232: )
233: IS
234: BEGIN
235: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
236: jtm_message_log_pkg.Log_Msg
237: ( p_incident_id
238: , g_table_name
239: , 'Entering Update_ACC_Record'
236: jtm_message_log_pkg.Log_Msg
237: ( p_incident_id
238: , g_table_name
239: , 'Entering Update_ACC_Record'
240: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
241: END IF;
242:
243: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
244: jtm_message_log_pkg.Log_Msg
239: , 'Entering Update_ACC_Record'
240: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
241: END IF;
242:
243: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
244: jtm_message_log_pkg.Log_Msg
245: ( p_incident_id
246: , g_table_name
247: , 'Updating ACC record for resource_id = ' || p_resource_id || fnd_global.local_chr(10) ||
245: ( p_incident_id
246: , g_table_name
247: , 'Updating ACC record for resource_id = ' || p_resource_id || fnd_global.local_chr(10) ||
248: 'access_id = ' || p_acc_id
249: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
250: END IF;
251:
252: JTM_HOOK_UTIL_PKG.Update_Acc
253: ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
248: 'access_id = ' || p_acc_id
249: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
250: END IF;
251:
252: JTM_HOOK_UTIL_PKG.Update_Acc
253: ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
254: ,P_ACC_TABLE_NAME => g_acc_table_name
255: ,P_RESOURCE_ID => p_resource_id
256: ,P_ACCESS_ID => p_acc_id
255: ,P_RESOURCE_ID => p_resource_id
256: ,P_ACCESS_ID => p_acc_id
257: );
258:
259: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
260: jtm_message_log_pkg.Log_Msg
261: ( p_incident_id
262: , g_table_name
263: , 'Leaving Update_ACC_Record'
260: jtm_message_log_pkg.Log_Msg
261: ( p_incident_id
262: , g_table_name
263: , 'Leaving Update_ACC_Record'
264: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
265: END IF;
266: END Update_ACC_Record;
267:
268:
286: l_stmt VARCHAR2(4000);
287: l_cursorid INTEGER;
288: l_execute_status INTEGER;
289: BEGIN
290: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
291: jtm_message_log_pkg.Log_Msg
292: ( p_incident_id
293: , g_table_name
294: , 'Entering Delete_ACC_Record'
291: jtm_message_log_pkg.Log_Msg
292: ( p_incident_id
293: , g_table_name
294: , 'Entering Delete_ACC_Record'
295: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
296: END IF;
297:
298: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
299: jtm_message_log_pkg.Log_Msg
294: , 'Entering Delete_ACC_Record'
295: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
296: END IF;
297:
298: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
299: jtm_message_log_pkg.Log_Msg
300: ( p_incident_id
301: , g_table_name
302: , 'Deleting ACC record for resource_id = ' || p_resource_id
299: jtm_message_log_pkg.Log_Msg
300: ( p_incident_id
301: , g_table_name
302: , 'Deleting ACC record for resource_id = ' || p_resource_id
303: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
304: END IF;
305:
306: /*** Delete incident ACC record ***/
307: JTM_HOOK_UTIL_PKG.Delete_Acc
303: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
304: END IF;
305:
306: /*** Delete incident ACC record ***/
307: JTM_HOOK_UTIL_PKG.Delete_Acc
308: ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
309: ,P_ACC_TABLE_NAME => g_acc_table_name
310: ,P_PK1_NAME => g_pk1_name
311: ,P_PK1_NUM_VALUE => p_incident_id
312: ,P_RESOURCE_ID => p_resource_id
313: );
314:
315: /*Delete also the dependant records*/
316: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
317: jtm_message_log_pkg.Log_Msg
318: ( p_incident_id
319: , g_table_name
320: , 'Deleting child records'
317: jtm_message_log_pkg.Log_Msg
318: ( p_incident_id
319: , g_table_name
320: , 'Deleting child records'
321: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
322: END IF;
323:
324: OPEN c_incident( p_incident_id );
325: FETCH c_incident INTO r_incident;
397: end;
398: DBMS_SQL.close_cursor (l_cursorid);
399:
400:
401: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
402: jtm_message_log_pkg.Log_Msg
403: ( p_incident_id
404: , g_table_name
405: , 'Leaving Delete_ACC_Record'
402: jtm_message_log_pkg.Log_Msg
403: ( p_incident_id
404: , g_table_name
405: , 'Leaving Delete_ACC_Record'
406: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
407: END IF;
408:
409: END Delete_ACC_Record;
410:
423: l_acc_id NUMBER;
424: l_success BOOLEAN;
425: BEGIN
426: /*** get debug level ***/
427: g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
428:
429: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
430: jtm_message_log_pkg.Log_Msg
431: ( p_incident_id
425: BEGIN
426: /*** get debug level ***/
427: g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
428:
429: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
430: jtm_message_log_pkg.Log_Msg
431: ( p_incident_id
432: , g_table_name
433: , 'Entering Pre_Insert_Child procedure'
430: jtm_message_log_pkg.Log_Msg
431: ( p_incident_id
432: , g_table_name
433: , 'Entering Pre_Insert_Child procedure'
434: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
435: END IF;
436:
437: l_success := FALSE;
438: /*Does the record match the criteria*/
453: IF p_flow_type = G_FLOW_NORMAL THEN
454: IF FND_PROFILE.VALUE( 'JTM_SYNCHRONOUS_HISTORY') = 'Y' THEN
455: IF CSL_SERVICE_HISTORY_PKG.GET_HISTORY_COUNT( p_resource_id ) > 0 THEN
456: /*Yes create history*/
457: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
458: jtm_message_log_pkg.Log_Msg
459: ( p_incident_id
460: , g_table_name
461: , 'History should be gathered synchronously'
458: jtm_message_log_pkg.Log_Msg
459: ( p_incident_id
460: , g_table_name
461: , 'History should be gathered synchronously'
462: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
463: END IF;
464: CSL_SERVICE_HISTORY_PKG.CALCULATE_HISTORY( p_incident_id => p_incident_id
465: , p_resource_id => p_resource_id );
466: END IF;--history count
467: END IF;--synchronous history
468: END IF;--p_flow_type
469: END IF;--Replicate record
470:
471: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
472: jtm_message_log_pkg.Log_Msg
473: ( p_incident_id
474: , g_table_name
475: , 'Leaving Pre_Insert_Child procedure'
472: jtm_message_log_pkg.Log_Msg
473: ( p_incident_id
474: , g_table_name
475: , 'Leaving Pre_Insert_Child procedure'
476: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
477: END IF;
478:
479: RETURN l_success;
480: END Pre_Insert_Child;
489: )
490: IS
491: BEGIN
492: /*** get debug level ***/
493: g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
494:
495: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
496: jtm_message_log_pkg.Log_Msg
497: ( p_incident_id
491: BEGIN
492: /*** get debug level ***/
493: g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
494:
495: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
496: jtm_message_log_pkg.Log_Msg
497: ( p_incident_id
498: , g_table_name
499: , 'Entering Post_Delete_Child'
496: jtm_message_log_pkg.Log_Msg
497: ( p_incident_id
498: , g_table_name
499: , 'Entering Post_Delete_Child'
500: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
501: END IF;
502:
503: IF p_flow_type <> G_FLOW_HISTORY THEN
504: /*Delete also the history for this SR*/
501: END IF;
502:
503: IF p_flow_type <> G_FLOW_HISTORY THEN
504: /*Delete also the history for this SR*/
505: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
506: jtm_message_log_pkg.Log_Msg
507: ( p_incident_id
508: , g_table_name
509: , 'Delete all history records on incident id '||p_incident_id
506: jtm_message_log_pkg.Log_Msg
507: ( p_incident_id
508: , g_table_name
509: , 'Delete all history records on incident id '||p_incident_id
510: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
511: END IF;
512:
513: CSL_SERVICE_HISTORY_PKG.DELETE_HISTORY( p_incident_id => p_incident_id
514: , p_resource_id => p_resource_id );
519: , p_resource_id
520: , p_flow_type );
521:
522:
523: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
524: jtm_message_log_pkg.Log_Msg
525: ( p_incident_id
526: , g_table_name
527: , 'Leaving Post_Delete_Child'
524: jtm_message_log_pkg.Log_Msg
525: ( p_incident_id
526: , g_table_name
527: , 'Leaving Post_Delete_Child'
528: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
529: END IF;
530: END Post_Delete_Child;
531:
532: /* Called before incident Insert */
559: x_return_status := FND_API.G_RET_STS_SUCCESS;
560: RETURN;
561: END IF;
562: /*** get debug level ***/
563: g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
564: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
565: jtm_message_log_pkg.Log_Msg
566: ( l_incident_id
567: , g_table_name
560: RETURN;
561: END IF;
562: /*** get debug level ***/
563: g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
564: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
565: jtm_message_log_pkg.Log_Msg
566: ( l_incident_id
567: , g_table_name
568: , 'Entering POST_INSERT_INCIDENT hook'
565: jtm_message_log_pkg.Log_Msg
566: ( l_incident_id
567: , g_table_name
568: , 'Entering POST_INSERT_INCIDENT hook'
569: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
570: END IF;
571:
572: /*** get incident record details from public API ***/
573: l_incident_id := cs_servicerequest_pvt.user_hooks_rec.request_id;
573: l_incident_id := cs_servicerequest_pvt.user_hooks_rec.request_id;
574: OPEN c_incident( l_incident_id );
575: FETCH c_incident INTO r_incident;
576: IF c_incident%FOUND THEN
577: IF JTM_HOOK_UTIL_PKG.isMobileFSresource(r_incident.RESOURCE_ID) THEN
578: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
579: jtm_message_log_pkg.Log_Msg
580: ( l_incident_id
581: , g_table_name
574: OPEN c_incident( l_incident_id );
575: FETCH c_incident INTO r_incident;
576: IF c_incident%FOUND THEN
577: IF JTM_HOOK_UTIL_PKG.isMobileFSresource(r_incident.RESOURCE_ID) THEN
578: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
579: jtm_message_log_pkg.Log_Msg
580: ( l_incident_id
581: , g_table_name
582: , 'SR is logged by mobile resource '||r_incident.RESOURCE_ID||' hence inserting record in acc table'
579: jtm_message_log_pkg.Log_Msg
580: ( l_incident_id
581: , g_table_name
582: , 'SR is logged by mobile resource '||r_incident.RESOURCE_ID||' hence inserting record in acc table'
583: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
584: END IF;
585: l_dummy := Pre_Insert_Child( l_incident_id, r_incident.RESOURCE_ID, G_FLOW_MOBILE_SR );
586: ELSE
587: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
583: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
584: END IF;
585: l_dummy := Pre_Insert_Child( l_incident_id, r_incident.RESOURCE_ID, G_FLOW_MOBILE_SR );
586: ELSE
587: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
588: jtm_message_log_pkg.Log_Msg
589: ( l_incident_id
590: , g_table_name
591: , r_incident.RESOURCE_ID||' is not a OMFS/Laptop resource'
588: jtm_message_log_pkg.Log_Msg
589: ( l_incident_id
590: , g_table_name
591: , r_incident.RESOURCE_ID||' is not a OMFS/Laptop resource'
592: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
593: END IF;
594: END IF;--isMobileFSresource
595: ELSE
596: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
592: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
593: END IF;
594: END IF;--isMobileFSresource
595: ELSE
596: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
597: jtm_message_log_pkg.Log_Msg
598: ( l_incident_id
599: , g_table_name
600: , 'SR is not logged by a mobile resource'
597: jtm_message_log_pkg.Log_Msg
598: ( l_incident_id
599: , g_table_name
600: , 'SR is not logged by a mobile resource'
601: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
602: END IF;
603: END IF;--c_incident%FOUND
604: CLOSE c_incident;
605:
602: END IF;
603: END IF;--c_incident%FOUND
604: CLOSE c_incident;
605:
606: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
607: jtm_message_log_pkg.Log_Msg
608: ( l_incident_id
609: , g_table_name
610: , 'Leaving POST_INSERT_INCIDENT hook'
607: jtm_message_log_pkg.Log_Msg
608: ( l_incident_id
609: , g_table_name
610: , 'Leaving POST_INSERT_INCIDENT 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
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_incident_id
620: , g_table_name
621: , 'Caught exception in POST_INSERT_INCIDENT hook:' || fnd_global.local_chr(10) || sqlerrm
618: jtm_message_log_pkg.Log_Msg
619: ( l_incident_id
620: , g_table_name
621: , 'Caught exception in POST_INSERT_INCIDENT 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_CS_INCIDENTS_ALL_ACC_PKG','POST_INSERT_INCIDENT',sqlerrm);
625: -- x_return_status := FND_API.G_RET_STS_ERROR;
626: x_return_status := FND_API.G_RET_STS_SUCCESS;
656: /*** get incident record details from public API ***/
657: l_incident_id := cs_servicerequest_pvt.user_hooks_rec.request_id;
658:
659: /*** get debug level ***/
660: g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
661:
662: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
663: jtm_message_log_pkg.Log_Msg
664: ( l_incident_id
658:
659: /*** get debug level ***/
660: g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
661:
662: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
663: jtm_message_log_pkg.Log_Msg
664: ( l_incident_id
665: , g_table_name
666: , 'Entering PRE_UPDATE hook'
663: jtm_message_log_pkg.Log_Msg
664: ( l_incident_id
665: , g_table_name
666: , 'Entering PRE_UPDATE hook'
667: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
668: END IF;
669:
670: /*** Check if task before update matched criteria ***/
671: g_replicate_pre_update := Replicate_Record( l_incident_id );
676: CLOSE c_incident;
677:
678: x_return_status := FND_API.G_RET_STS_SUCCESS;
679:
680: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
681: jtm_message_log_pkg.Log_Msg
682: ( l_incident_id
683: , g_table_name
684: , 'Leaving PRE_UPDATE hook'
681: jtm_message_log_pkg.Log_Msg
682: ( l_incident_id
683: , g_table_name
684: , 'Leaving PRE_UPDATE hook'
685: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
686: END IF;
687:
688: EXCEPTION WHEN OTHERS THEN
689: /*** hook failed -> log error ***/
686: END IF;
687:
688: EXCEPTION WHEN OTHERS THEN
689: /*** hook failed -> log error ***/
690: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
691: jtm_message_log_pkg.Log_Msg
692: ( l_incident_id
693: , g_table_name
694: , 'Caught exception in PRE_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
691: jtm_message_log_pkg.Log_Msg
692: ( l_incident_id
693: , g_table_name
694: , 'Caught exception in PRE_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
695: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
696: END IF;
697: fnd_msg_pub.Add_Exc_Msg('CSL_CS_INCIDENTS_ALL_ACC_PKG','PRE_UPDATE_TASK',sqlerrm);
698: -- x_return_status := FND_API.G_RET_STS_ERROR;
699: x_return_status := FND_API.G_RET_STS_SUCCESS;
750: /*** get task record details from public API ***/
751: l_incident_id := cs_servicerequest_pvt.user_hooks_rec.request_id;
752:
753: /*** get debug level ***/
754: g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
755:
756: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
757: jtm_message_log_pkg.Log_Msg
758: ( l_incident_id
752:
753: /*** get debug level ***/
754: g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
755:
756: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
757: jtm_message_log_pkg.Log_Msg
758: ( l_incident_id
759: , g_table_name
760: , 'Entering POST_UPDATE hook'
757: jtm_message_log_pkg.Log_Msg
758: ( l_incident_id
759: , g_table_name
760: , 'Entering POST_UPDATE hook'
761: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
762: END IF;
763:
764: /*** Check if task after update matches criteria ***/
765: l_replicate := Replicate_Record( l_incident_id );
768: IF l_replicate THEN
769: /*** yes -> was record already replicated? ***/
770: IF g_replicate_pre_update THEN
771: /*** yes -> re-send updated task record to all resources ***/
772: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
773: jtm_message_log_pkg.Log_Msg
774: ( l_incident_id
775: , g_table_name
776: , 'Incident was replicateable before and after update. Re-sending incident record to mobile users.'
773: jtm_message_log_pkg.Log_Msg
774: ( l_incident_id
775: , g_table_name
776: , 'Incident was replicateable before and after update. Re-sending incident record to mobile users.'
777: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
778: END IF;
779: /*** get list of resources to whom the record was replicated ***/
780: JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
781: ( P_ACC_TABLE_NAME => g_acc_table_name
776: , 'Incident was replicateable before and after update. Re-sending incident record to mobile users.'
777: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
778: END IF;
779: /*** get list of resources to whom the record was replicated ***/
780: JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
781: ( P_ACC_TABLE_NAME => g_acc_table_name
782: ,P_PK1_NAME => g_pk1_name
783: ,P_PK1_NUM_VALUE => l_incident_id
784: ,L_TAB_RESOURCE_ID => l_tab_resource_id
807: CLOSE c_incident;
808:
809: /*check if customer has changed ( not possible in the form , but maybe the API ? )*/
810: IF r_incident.customer_id <> g_cached_rec.customer_id THEN
811: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
812: jtm_message_log_pkg.Log_Msg
813: ( l_incident_id
814: , g_table_name
815: , 'Customer of the incident has changed.'
812: jtm_message_log_pkg.Log_Msg
813: ( l_incident_id
814: , g_table_name
815: , 'Customer of the incident has changed.'
816: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
817: END IF;
818: CSL_HZ_PARTIES_ACC_PKG.CHANGE_PARTY( g_cached_rec.customer_id
819: , r_incident.customer_id
820: , l_tab_resource_id(i));
822: END IF;--customer check
823:
824: /*check if the installed at address is changed*/
825: IF NVL( r_incident.incident_location_id, FND_API.G_MISS_NUM ) <> NVL( g_cached_rec.incident_location_id, FND_API.G_MISS_NUM ) THEN
826: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
827: jtm_message_log_pkg.Log_Msg
828: ( l_incident_id
829: , g_table_name
830: , 'Installed at address of the incident has changed.'
827: jtm_message_log_pkg.Log_Msg
828: ( l_incident_id
829: , g_table_name
830: , 'Installed at address of the incident has changed.'
831: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
832: END IF;
833: -- 11510 Change 3430663. Use incident_location_id
834: CSL_HZ_PARTY_SITES_ACC_PKG.CHANGE_PARTY_SITE( g_cached_rec.incident_location_id
835: , r_incident.incident_location_id
837: END IF;--install site check
838:
839: /*check if the customer product is changed*/
840: IF NVL( r_incident.customer_product_id, FND_API.G_MISS_NUM ) <> NVL( g_cached_rec.customer_product_id, FND_API.G_MISS_NUM ) THEN
841: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
842: jtm_message_log_pkg.Log_Msg
843: ( l_incident_id
844: , g_table_name
845: , 'Customer product of the incident has changed.'
842: jtm_message_log_pkg.Log_Msg
843: ( l_incident_id
844: , g_table_name
845: , 'Customer product of the incident has changed.'
846: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
847: END IF;
848: /* First delete old customer product */
849: IF g_cached_rec.customer_product_id IS NOT NULL THEN
850: -- ER 3168446 - View ib at a location. Pass the Install Site Id
866: END IF;--customer product check
867:
868: /*check if the inventory item is changed*/
869: IF NVL( r_incident.inventory_item_id, FND_API.G_MISS_NUM ) <> NVL( g_cached_rec.inventory_item_id, FND_API.G_MISS_NUM ) THEN
870: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
871: jtm_message_log_pkg.Log_Msg
872: ( l_incident_id
873: , g_table_name
874: , 'Inventory item of the incident has changed.'
871: jtm_message_log_pkg.Log_Msg
872: ( l_incident_id
873: , g_table_name
874: , 'Inventory item of the incident has changed.'
875: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
876: END IF;
877: /* First delete old inventory item */
878: IF g_cached_rec.inventory_item_id IS NOT NULL THEN
879: CSL_MTL_SYSTEM_ITEMS_ACC_PKG.POST_DELETE_Child( g_cached_rec.inventory_item_id
889: END IF;--inventory item check
890:
891: IF NVL(r_incident.contract_service_id, FND_API.G_MISS_NUM) <> NVL( g_cached_rec.contract_service_id, FND_API.G_MISS_NUM) THEN
892: /*Delete contract record, use dynamic SQL because Contracts might not be implemented/used*/
893: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
894: jtm_message_log_pkg.Log_Msg
895: ( l_incident_id
896: , g_table_name
897: , 'Contract line of the incident has changed.'
894: jtm_message_log_pkg.Log_Msg
895: ( l_incident_id
896: , g_table_name
897: , 'Contract line of the incident has changed.'
898: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
899: END IF;
900:
901: l_cursorid := DBMS_SQL.open_cursor;
902: l_stmt := 'Begin CSL_CONTRACT_HANDLING_PKG.POST_UPDATE_SR_CONTRACT_ACC( :1,:2,:3,:4,:5 );'||
921: ELSE
922: /***
923: record was not replicated before update so we don't need it
924: ***/
925: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
926: jtm_message_log_pkg.Log_Msg
927: ( l_incident_id
928: , g_table_name
929: , 'Incident was not replicated before update.'
926: jtm_message_log_pkg.Log_Msg
927: ( l_incident_id
928: , g_table_name
929: , 'Incident was not replicated before update.'
930: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
931: END IF;
932: END IF;
933: ELSE
934: /*** record should not be replicated anymore -> was it replicated before? ***/
933: ELSE
934: /*** record should not be replicated anymore -> was it replicated before? ***/
935: IF g_replicate_pre_update THEN
936: /*** yes -> delete record related data for all resources ***/
937: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
938: jtm_message_log_pkg.Log_Msg
939: ( l_incident_id
940: , g_table_name
941: , 'Incident was replicated before update, but should no longer be replicated.'
938: jtm_message_log_pkg.Log_Msg
939: ( l_incident_id
940: , g_table_name
941: , 'Incident was replicated before update, but should no longer be replicated.'
942: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
943: END IF;
944: END IF;
945: END IF;
946:
945: END IF;
946:
947: x_return_status := FND_API.G_RET_STS_SUCCESS;
948:
949: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
950: jtm_message_log_pkg.Log_Msg
951: ( l_incident_id
952: , g_table_name
953: , 'Leaving POST_UPDATE hook'
950: jtm_message_log_pkg.Log_Msg
951: ( l_incident_id
952: , g_table_name
953: , 'Leaving POST_UPDATE hook'
954: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
955: END IF;
956:
957: EXCEPTION WHEN OTHERS THEN
958: /*** hook failed -> log error ***/
955: END IF;
956:
957: EXCEPTION WHEN OTHERS THEN
958: /*** hook failed -> log error ***/
959: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
960: jtm_message_log_pkg.Log_Msg
961: ( l_incident_id
962: , g_table_name
963: , 'Caught exception in POST_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
960: jtm_message_log_pkg.Log_Msg
961: ( l_incident_id
962: , g_table_name
963: , 'Caught exception in POST_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
964: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
965: END IF;
966: fnd_msg_pub.Add_Exc_Msg('CSL_CS_INCIDENTS_ALL_ACC_PKG','POST_UPDATE_TASK',sqlerrm);
967: -- x_return_status := FND_API.G_RET_STS_ERROR;
968: x_return_status := FND_API.G_RET_STS_SUCCESS;
1000: AND au.RESOURCE_ID = b_resource_id;
1001: r_incident c_incident%ROWTYPE;
1002: l_dummy BOOLEAN;
1003: BEGIN
1004: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1005: jtm_message_log_pkg.Log_Msg
1006: ( p_resource_id
1007: , g_table_name
1008: , 'Entering POST_INSERT hook'
1005: jtm_message_log_pkg.Log_Msg
1006: ( p_resource_id
1007: , g_table_name
1008: , 'Entering POST_INSERT hook'
1009: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1010: END IF;
1011:
1012: /*** insert all SRs created by resource ***/
1013: FOR r_incident IN c_incident( p_resource_id ) LOOP
1013: FOR r_incident IN c_incident( p_resource_id ) LOOP
1014: l_dummy := Pre_Insert_Child( r_incident.incident_id, p_resource_id, G_FLOW_MOBILE_SR );
1015: END LOOP;
1016:
1017: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1018: jtm_message_log_pkg.Log_Msg
1019: ( p_resource_id
1020: , g_table_name
1021: , 'Leaving POST_INSERT hook'
1018: jtm_message_log_pkg.Log_Msg
1019: ( p_resource_id
1020: , g_table_name
1021: , 'Leaving POST_INSERT hook'
1022: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1023: END IF;
1024:
1025: x_return_status := FND_API.G_RET_STS_SUCCESS;
1026:
1025: x_return_status := FND_API.G_RET_STS_SUCCESS;
1026:
1027: EXCEPTION
1028: WHEN OTHERS THEN
1029: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1030: jtm_message_log_pkg.Log_Msg
1031: ( p_resource_id
1032: , g_table_name
1033: , 'Caught exception in INSERT_ALL_ACC_RECORDS:' || fnd_global.local_chr(10) || sqlerrm
1030: jtm_message_log_pkg.Log_Msg
1031: ( p_resource_id
1032: , g_table_name
1033: , 'Caught exception in INSERT_ALL_ACC_RECORDS:' || fnd_global.local_chr(10) || sqlerrm
1034: , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
1035: END IF;
1036: x_return_status := FND_API.G_RET_STS_ERROR;
1037: END INSERT_ALL_ACC_RECORDS;
1038: