[Home] [Help]
PACKAGE BODY: APPS.CSL_SERVICE_HISTORY_PKG
Source
1 PACKAGE BODY CSL_SERVICE_HISTORY_PKG AS
2 /* $Header: cslsrhib.pls 115.7 2004/05/10 06:42:37 utekumal ship $ */
3
4 /*** Globals ***/
5 g_debug_level NUMBER;
6 g_table_name CONSTANT VARCHAR2(30) := 'CSL_SERVICE_HISTORY_PKG';
7 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
8 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_SERVICE_HISTORY');
9 g_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_SERVICE_HISTORY';
10 g_pk1_name CONSTANT VARCHAR2(30) := 'INCIDENT_ID';
11 g_pk2_name CONSTANT VARCHAR2(30) := 'HISTORY_INCIDENT_ID';
12
13
14 PROCEDURE INSERT_MAPPING_RECORD( p_incident_id NUMBER
15 , p_history_id NUMBER
16 , p_resource_id NUMBER )
17 IS
18 BEGIN
19 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
20 jtm_message_log_pkg.Log_Msg
21 ( null
22 , g_table_name
23 , 'Entering INSERT_MAPPING_RECORD for incident '||p_incident_id
24 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
25 , 'csl_service_history_pkg');
26 END IF;
27
28 JTM_HOOK_UTIL_PKG.Insert_Acc
29 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
30 ,P_ACC_TABLE_NAME => g_acc_table_name
31 ,P_RESOURCE_ID => p_resource_id
32 ,P_PK1_NAME => g_pk1_name
33 ,P_PK1_NUM_VALUE => p_incident_id
34 ,P_PK2_NAME => g_pk2_name
35 ,P_PK2_NUM_VALUE => p_history_id
36 );
37
38 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
39 jtm_message_log_pkg.Log_Msg
40 ( null
41 , g_table_name
42 , 'Leaving INSERT_MAPPING_RECORD for incident '||p_incident_id
43 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
44 , 'csl_service_history_pkg');
45 END IF;
46 EXCEPTION WHEN OTHERS THEN
47 jtm_message_log_pkg.Log_Msg( null
48 , g_table_name
49 , 'Exception in CSL_SERVICE_HISTORY_PKG.INSERT_MAPPING_RECORD for incident '||p_incident_id||
50 ' and resource '||p_resource_id || ':' || fnd_global.local_chr(10) || sqlerrm
51 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
52 , 'csl_service_history_pkg');
53 RAISE;
54 END INSERT_MAPPING_RECORD;
55
56 PROCEDURE DELETE_MAPPING_RECORD( p_incident_id NUMBER
57 , p_history_id NUMBER
58 , p_resource_id NUMBER )
59 IS
60 BEGIN
61 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
62 jtm_message_log_pkg.Log_Msg
63 ( null
64 , g_table_name
65 , 'Entering DELETE_MAPPING_RECORD for incident '||p_incident_id
66 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
67 , 'csl_service_history_pkg');
68 END IF;
69
70 JTM_HOOK_UTIL_PKG.Delete_Acc
71 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
72 ,P_ACC_TABLE_NAME => g_acc_table_name
73 ,P_RESOURCE_ID => p_resource_id
74 ,P_PK1_NAME => g_pk1_name
75 ,P_PK1_NUM_VALUE => p_incident_id
76 ,P_PK2_NAME => g_pk2_name
77 ,P_PK2_NUM_VALUE => p_history_id
78 );
79
80 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
81 jtm_message_log_pkg.Log_Msg
82 ( null
83 , g_table_name
84 , 'Leaving DELETE_MAPPING_RECORD for incident '||p_incident_id
85 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
86 , 'csl_service_history_pkg');
87 END IF;
88 EXCEPTION WHEN OTHERS THEN
89 jtm_message_log_pkg.Log_Msg( null
90 , g_table_name
91 , 'Exception in CSL_SERVICE_HISTORY_PKG.DELETE_MAPPING_RECORD for incident '||p_incident_id||
92 ' and resource '||p_resource_id||':'|| fnd_global.local_chr(10) || sqlerrm
93 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
94 , 'csl_service_history_pkg');
95 RAISE;
96 END DELETE_MAPPING_RECORD;
97
98 /*** Function to retreive the amount of history records***/
99 FUNCTION GET_HISTORY_COUNT( p_resource_id IN NUMBER )
100 RETURN NUMBER
101 IS
102 l_max_count NUMBER;
103 l_profile_value NUMBER;
104 CURSOR c_responsibilities( b_resource_id NUMBER ) IS
105 SELECT fur.USER_ID
106 , fur.RESPONSIBILITY_ID
107 , fur.RESPONSIBILITY_APPLICATION_ID
108 FROM FND_USER_RESP_GROUPS fur
109 , ASG_USER au
110 WHERE au.RESOURCE_ID = b_resource_id
111 AND au.USER_ID = fur.USER_ID
112 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(fur.start_date,sysdate))
113 AND TRUNC(NVL(fur.end_date,sysdate));
114
115 BEGIN
116 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
117 jtm_message_log_pkg.Log_Msg
118 ( null
119 , g_table_name
120 , 'Entering GET_HISTORY_COUNT for resource '||p_resource_id
121 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
122 , 'csl_service_history_pkg');
123 END IF;
124 /*If multiple values can be gotton from the profile the maximum value will be returned
125 Leading is the profile order of user ->resp -> appl -> site so it basicly only counts
126 for responsibility for the highest value
127 */
128 l_max_count := 0;
129 FOR r_resp IN c_responsibilities( p_resource_id ) LOOP
130 l_profile_value := TO_NUMBER(
131 FND_PROFILE.VALUE_SPECIFIC(NAME => 'JTM_HISTORY_COUNT',
132 USER_ID => r_resp.USER_ID ,
133 RESPONSIBILITY_ID => r_resp.RESPONSIBILITY_ID ,
134 APPLICATION_ID => r_resp.RESPONSIBILITY_APPLICATION_ID ));
135 IF l_profile_value > l_max_count THEN
136 l_max_count := l_profile_value;
137 END IF;
138 END LOOP;
139
140 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
141 jtm_message_log_pkg.Log_Msg
142 ( null
143 , g_table_name
144 , 'Leaving GET_HISTORY_COUNT for for resource '||p_resource_id||
145 ' with value '||l_max_count
146 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
147 , 'csl_service_history_pkg');
148 END IF;
149 RETURN l_max_count;
150 EXCEPTION WHEN OTHERS THEN
151 jtm_message_log_pkg.Log_Msg( null
152 , g_table_name
153 , 'Exception in CSL_SERVICE_HISTORY_PKG.GET_HISTORY_COUNT for resource '||p_resource_id||':'
154 || fnd_global.local_chr(10) || sqlerrm
155 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
156 , 'csl_service_history_pkg');
157 RETURN 0;
158 END GET_HISTORY_COUNT;
159
160 PROCEDURE DELETE_HISTORY_SR_RECORD( p_incident_id NUMBER
161 , p_history_id NUMBER
162 , p_resource_id NUMBER )
163 IS
164 CURSOR c_closed_tasks( b_incident_id NUMBER )IS
165 SELECT tk.TASK_ID
166 FROM JTF_TASKS_B tk
167 , JTF_TASK_STATUSES_B ts
168 WHERE tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
169 AND tk.SOURCE_OBJECT_ID = b_incident_id
170 AND tk.TASK_STATUS_ID = ts.TASK_STATUS_ID
171 AND (ts.CLOSED_FLAG = 'Y'
172 OR ts.COMPLETED_FLAG = 'Y' )
173 AND NVL(ts.CANCELLED_FLAG,'N') <> 'Y'
174 AND NVL(ts.REJECTED_FLAG, 'N') <> 'Y';
175
176 CURSOR c_closed_assignments( b_task_id NUMBER ) IS
177 SELECT TASK_ASSIGNMENT_ID
178 , RESOURCE_ID
179 FROM JTF_TASK_ASSIGNMENTS ta
180 , JTF_TASK_STATUSES_B ts
181 WHERE ta.TASK_ID = b_task_id
182 AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
183 AND ts.TASK_STATUS_ID = ta.ASSIGNMENT_STATUS_ID
184 AND (ts.CLOSED_FLAG = 'Y'
185 OR ts.COMPLETED_FLAG = 'Y' )
186 AND NVL(ts.CANCELLED_FLAG,'N') <> 'Y'
187 AND NVL(ts.REJECTED_FLAG, 'N') <> 'Y';
188 BEGIN
189 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
190 jtm_message_log_pkg.Log_Msg
191 ( null
192 , g_table_name
193 , 'Entering DELETE_HISTORY_SR_RECORD for incident '||p_incident_id
194 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
195 , 'csl_service_history_pkg');
196 END IF;
197
198 CSL_CS_INCIDENTS_ALL_ACC_PKG.Post_Delete_Child( p_incident_id => p_history_id
199 , p_resource_id => p_resource_id
200 , p_flow_type => CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY);
201
202 --Calculate tasks
203 FOR r_closed_task IN c_closed_tasks( b_incident_id => p_history_id ) LOOP
204 CSL_JTF_TASKS_ACC_PKG.Post_Delete_Child( p_task_id => r_closed_task.task_id
205 , p_resource_id => p_resource_id
206 , p_flow_type => CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY);
207
208 --Calculate ta + debrief
209 FOR r_closed_assignement IN c_closed_assignments( b_task_id => r_closed_task.task_id ) LOOP
210 CSL_JTF_TASK_ASS_ACC_PKG.Post_Delete_Child(
211 p_task_assignment_id => r_closed_assignement.task_assignment_id,
212 p_resource_id => p_resource_id,
213 p_flow_type => CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY);
214
215 --Delete resource of ta
216 CSL_JTF_RESOURCE_EXTNS_ACC_PKG.Delete_Resource_Extns(
217 p_resource_extn_id => r_closed_assignement.resource_id,
218 p_resource_id => p_resource_id );
219 END LOOP;
220 END LOOP;
221 DELETE_MAPPING_RECORD( p_incident_id, p_history_id, p_resource_id );
222 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
223 jtm_message_log_pkg.Log_Msg
224 ( null
225 , g_table_name
226 , 'Leaving DELETE_HISTORY_SR_RECORD for incident '||p_incident_id
227 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
228 , 'csl_service_history_pkg');
229 END IF;
230 EXCEPTION WHEN OTHERS THEN
231 jtm_message_log_pkg.Log_Msg
232 ( null
233 , g_table_name
234 , 'Exception in CSL_SERVICE_HISTORY_PKG.DELETE_HISTORY_SR_RECORD for incident '||p_incident_id||
235 ' and resource '||p_resource_id||':'|| fnd_global.local_chr(10) || sqlerrm
236 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
237 , 'csl_service_history_pkg');
238 RAISE;
239 END DELETE_HISTORY_SR_RECORD;
240
241 PROCEDURE CREATE_HISTORY_SR_RECORD( p_incident_id IN NUMBER
242 , p_history_id IN NUMBER
243 , p_resource_id IN NUMBER
244 , p_closed_date IN DATE )
245 IS
246 l_dummy BOOLEAN;
247
248 CURSOR c_closed_tasks( b_incident_id NUMBER )IS
249 SELECT tk.TASK_ID
250 FROM JTF_TASKS_B tk
251 , JTF_TASK_STATUSES_B ts
252 WHERE tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
253 AND tk.SOURCE_OBJECT_ID = b_incident_id
254 AND tk.TASK_STATUS_ID = ts.TASK_STATUS_ID
255 AND (ts.CLOSED_FLAG = 'Y'
256 OR ts.COMPLETED_FLAG = 'Y' )
257 AND NVL(ts.CANCELLED_FLAG,'N') <> 'Y'
258 AND NVL(ts.REJECTED_FLAG,'N') <> 'Y';
259
260 CURSOR c_closed_assignments( b_task_id NUMBER ) IS
261 SELECT TASK_ASSIGNMENT_ID
262 , RESOURCE_ID
263 FROM JTF_TASK_ASSIGNMENTS ta
264 , JTF_TASK_STATUSES_B ts
265 WHERE ta.TASK_ID = b_task_id
266 AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
267 AND ts.TASK_STATUS_ID = ta.ASSIGNMENT_STATUS_ID
268 AND (ts.CLOSED_FLAG = 'Y'
269 OR ts.COMPLETED_FLAG = 'Y' )
270 AND NVL(ts.CANCELLED_FLAG,'N') <> 'Y'
271 AND NVL(ts.REJECTED_FLAG,'N') <> 'Y';
272
273 BEGIN
274 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
275 jtm_message_log_pkg.Log_Msg
276 ( null
277 , g_table_name
278 , 'Entering CREATE_HISTORY_SR_RECORD for incident '||p_incident_id
279 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
280 , 'csl_service_history_pkg');
281 END IF;
282
283 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
284 jtm_message_log_pkg.Log_Msg
285 ( null
286 , g_table_name
287 , 'Inserting history record '||p_history_id||' for incident '||
288 p_incident_id||' and resource '||p_resource_id
289 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
290 , 'csl_service_history_pkg');
291 END IF;
292
293 INSERT_MAPPING_RECORD( p_incident_id, p_history_id, p_resource_id );
294 --Insert SR
295 l_dummy := CSL_CS_INCIDENTS_ALL_ACC_PKG.Pre_Insert_Child(
296 p_incident_id => p_history_id
297 , p_resource_id => p_resource_id
298 , p_flow_type => CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY);
299 --Calculate tasks
300 FOR r_closed_task IN c_closed_tasks( b_incident_id => p_history_id ) LOOP
301 l_dummy := CSL_JTF_TASKS_ACC_PKG.Pre_Insert_Child( p_task_id => r_closed_task.task_id
302 , p_resource_id => p_resource_id
303 , p_flow_type => CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY);
304
305 --Calculate ta + debrief
306 FOR r_closed_assignement IN c_closed_assignments( b_task_id => r_closed_task.task_id ) LOOP
307 l_dummy := CSL_JTF_TASK_ASS_ACC_PKG.Pre_Insert_Child(
308 p_task_assignment_id => r_closed_assignement.task_assignment_id,
309 p_resource_id => p_resource_id,
310 p_flow_type => CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY);
311
312 --Insert resource of ta
313 CSL_JTF_RESOURCE_EXTNS_ACC_PKG.Insert_Resource_Extns(
314 p_resource_extn_id => r_closed_assignement.resource_id,
315 p_resource_id => p_resource_id );
316
317 END LOOP;
318 END LOOP;
319
320 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
321 jtm_message_log_pkg.Log_Msg
322 ( null
323 , g_table_name
324 , 'Leaving CREATE_HISTORY_SR_RECORD for incident '||p_incident_id
325 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
326 , 'csl_service_history_pkg');
327 END IF;
328 EXCEPTION WHEN OTHERS THEN
329 jtm_message_log_pkg.Log_Msg
330 ( null
331 , g_table_name
332 , 'Exception in CSL_SERVICE_HISTORY_PKG.CREATE_HISTORY_SR_RECORD for incident '||p_incident_id||
333 ' and resource '||p_resource_id||':'|| fnd_global.local_chr(10) || sqlerrm
334 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
335 , 'csl_service_history_pkg');
336 RAISE;
337 END CREATE_HISTORY_SR_RECORD;
338
339 PROCEDURE DELETE_HISTORY( p_incident_id IN NUMBER
340 , p_resource_id IN NUMBER )
341 IS
342 CURSOR c_history ( b_incident_id NUMBER, b_resource_id NUMBER ) IS
343 SELECT HISTORY_INCIDENT_ID
344 FROM CSL_SERVICE_HISTORY
345 WHERE INCIDENT_ID = b_incident_id
346 AND RESOURCE_ID = b_resource_id;
347
348
349 BEGIN
350 /*** get debug level ***/
351 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
352
353 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
354 jtm_message_log_pkg.Log_Msg
355 ( null
356 , g_table_name
357 , 'Entering DELETE_HISTORY for incident '||p_incident_id
358 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
359 , 'csl_service_history_pkg');
360 END IF;
361
362 FOR r_history IN c_history( p_incident_id, p_resource_id ) LOOP
363 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
364 jtm_message_log_pkg.Log_Msg
365 ( null
366 , g_table_name
367 , 'Calling delete history record for incident id '||p_incident_id||
368 ' and history id '||r_history.HISTORY_INCIDENT_ID||' and resource id '||p_resource_id
369 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
370 , 'csl_service_history_pkg');
371 END IF;
372 DELETE_HISTORY_SR_RECORD( p_incident_id, r_history.HISTORY_INCIDENT_ID, p_resource_id );
373 END LOOP;
374
375 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
376 jtm_message_log_pkg.Log_Msg
377 ( null
378 , g_table_name
379 , 'Leaving DELETE_HISTORY for incident '||p_incident_id
380 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
381 , 'csl_service_history_pkg');
382 END IF;
383 EXCEPTION WHEN OTHERS THEN
384 jtm_message_log_pkg.Log_Msg
385 ( null
386 , g_table_name
387 , 'Exception in CSL_SERVICE_HISTORY_PKG.DELETE_HISTORY for incident '||p_incident_id||
388 ' and resource '||p_resource_id||':'|| fnd_global.local_chr(10) || sqlerrm
389 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
390 , 'csl_service_history_pkg');
391 END DELETE_HISTORY;
392
393 /*Procedure calculates the x number of history service request for the given sr */
394 PROCEDURE CALCULATE_HISTORY( p_incident_id IN NUMBER
395 , p_resource_id IN NUMBER )
396 IS
397 CURSOR c_sr_type( b_incident_id NUMBER ) IS
398 -- 11.5.10 Service uptake - 3430663. Get incident_location_id
399 SELECT CUSTOMER_PRODUCT_ID
400 , INCIDENT_LOCATION_ID
401 , CUSTOMER_ID
402 FROM CS_INCIDENTS_ALL_B
403 WHERE INCIDENT_ID = b_incident_id;
404 r_sr_type c_sr_type%ROWTYPE;
405
406 CURSOR c_task_time( b_incident_id NUMBER
407 , b_resource_id NUMBER ) IS
408 SELECT MAX(tk.SCHEDULED_END_DATE ) AS "TASK_TIME"
409 FROM JTF_TASKS_B tk
410 , JTF_TASK_ASSIGNMENTS ta
411 WHERE tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
412 AND tk.SOURCE_OBJECT_ID = b_incident_id
413 AND tk.TASK_ID = ta.TASK_ID
414 AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
415 AND ta.RESOURCE_ID = b_resource_id;
416 r_task_time c_task_time%ROWTYPE;
417
418 CURSOR c_get_cp_history( b_max_date DATE,
419 b_customer_product_id NUMBER ) IS
420 SELECT DISTINCT inc.INCIDENT_ID
421 , inc.CLOSE_DATE
422 FROM CS_INCIDENTS_ALL_B inc
423 , JTF_TASKS_B tk
424 , JTF_TASK_ASSIGNMENTS ta
425 , CS_INCIDENT_STATUSES_B ists
426 , JTF_TASK_TYPES_B tt
427 , JTF_TASK_STATUSES_B tkst
428 , JTF_TASK_STATUSES_B tast
429 WHERE inc.CLOSE_DATE <= b_max_date
430 AND inc.INCIDENT_ID = tk.SOURCE_OBJECT_ID
431 AND inc.INCIDENT_STATUS_ID = ists.INCIDENT_STATUS_ID
432 AND ists.CLOSE_FLAG = 'Y'
433 AND tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
434 AND tk.TASK_ID = ta.TASK_ID
435 AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
436 AND tk.TASK_STATUS_ID = tkst.TASK_STATUS_ID
437 AND (tkst.CLOSED_FLAG = 'Y'
438 OR tkst.COMPLETED_FLAG = 'Y')
439 AND nvl(tkst.CANCELLED_FLAG,'N') <> 'Y'
440 AND nvl(tkst.REJECTED_FLAG,'N') <> 'Y'
441 AND tk.TASK_TYPE_ID = tt.TASK_TYPE_ID
442 AND tt.RULE = 'DISPATCH'
443 AND ta.ASSIGNMENT_STATUS_ID = tast.TASK_STATUS_ID
444 AND (tast.CLOSED_FLAG = 'Y'
445 OR tast.COMPLETED_FLAG = 'Y')
446 AND nvl(tast.CANCELLED_FLAG,'N') <> 'Y'
447 AND nvl(tast.REJECTED_FLAG,'N') <> 'Y'
448 AND inc.CUSTOMER_PRODUCT_ID = b_customer_product_id
449 ORDER BY inc.CLOSE_DATE DESC;
450
451 -- 11510 Changes 3430663. Using incident_location_id instead of install_site_id
452 CURSOR c_get_non_cp_history( b_max_date DATE,
453 b_customer_id NUMBER,
454 b_incident_location_id NUMBER ) IS
455 SELECT DISTINCT inc.INCIDENT_ID
456 , inc.CLOSE_DATE
457 FROM CS_INCIDENTS_ALL_B inc
458 , JTF_TASKS_B tk
459 , JTF_TASK_ASSIGNMENTS ta
460 , CS_INCIDENT_STATUSES_B ists
461 , JTF_TASK_TYPES_B tt
462 , JTF_TASK_STATUSES_B tkst
463 , JTF_TASK_STATUSES_B tast
464 WHERE inc.CLOSE_DATE <= b_max_date
465 AND inc.INCIDENT_ID = tk.SOURCE_OBJECT_ID
466 AND inc.INCIDENT_STATUS_ID = ists.INCIDENT_STATUS_ID
467 AND ists.CLOSE_FLAG = 'Y'
468 AND tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
469 AND tk.TASK_ID = ta.TASK_ID
470 AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
471 AND tk.TASK_STATUS_ID = tkst.TASK_STATUS_ID
472 AND (tkst.CLOSED_FLAG = 'Y'
473 OR tkst.COMPLETED_FLAG = 'Y')
474 AND tk.TASK_TYPE_ID = tt.TASK_TYPE_ID
475 AND tt.RULE = 'DISPATCH'
476 AND ta.ASSIGNMENT_STATUS_ID = tast.TASK_STATUS_ID
477 AND (tast.CLOSED_FLAG = 'Y'
478 OR tast.COMPLETED_FLAG = 'Y')
479 AND inc.CUSTOMER_ID = b_customer_id
480 AND inc.INCIDENT_LOCATION_ID = b_incident_location_id
481 ORDER BY inc.CLOSE_DATE DESC;
482
483 l_history_count NUMBER;
484
485 CURSOR c_history( b_incident_id NUMBER, b_resource_id NUMBER ) IS
486 SELECT HISTORY_INCIDENT_ID
487 FROM CSL_SERVICE_HISTORY
488 WHERE INCIDENT_ID = b_incident_id
489 AND RESOURCE_ID = b_resource_id;
490
491 TYPE history_table_type IS TABLE OF NUMBER
492 INDEX BY BINARY_INTEGER;
493
494 l_history_table history_table_type;
495 l_cntr NUMBER;
496
497 l_not_exists BOOLEAN;
498
499 BEGIN
500 /*** get debug level ***/
501 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
502
503 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
504 jtm_message_log_pkg.Log_Msg
505 ( null
506 , g_table_name
507 , 'Entering CALCULATE_HISTORY for incident '||p_incident_id
508 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
509 , 'csl_service_history_pkg');
510 END IF;
511
512 /*Get history count from profile*/
513 l_history_count := GET_HISTORY_COUNT( p_resource_id);
514
515 /*TODO: FETCH ALL EXISTING HISTORY RECORDS FOR THIS INCIDENT IN A PLSQL TABLE*/
516 /* WHEN CALCULATING NEEDED RECORDS SHOULD BE COMPARED TO THIS TABLE*/
517 /* IF IT MATCHES RECORD DOES NOT NEED TO BE INSERTED AND THE PLSQL RECORD SHOULD BE DELETED*/
518 /* AT THE END PUSH A DELETE FOR THE REMAINING RECORDS AS THEY ARE NO LONGER NEEDED*/
519 OPEN c_history( p_incident_id, p_resource_id );
520 FETCH c_history BULK COLLECT INTO l_history_table;
521 CLOSE c_history;
522
523 OPEN c_sr_type( b_incident_id => p_incident_id );
524 FETCH c_sr_type INTO r_sr_type;
525 IF c_sr_type%FOUND THEN
526 OPEN c_task_time( b_incident_id => p_incident_id, b_resource_id => p_resource_id );
527 FETCH c_task_time INTO r_task_time;
528 CLOSE c_task_time;
529 /*Check for sr type ( on CP or not )*/
530 IF r_sr_type.CUSTOMER_PRODUCT_ID IS NOT NULL THEN
531 /*Only history for CP product*/
532 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
533 jtm_message_log_pkg.Log_Msg
534 ( null
535 , g_table_name
536 , 'Service request is based on a customer product'
537 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
538 , 'csl_service_history_pkg');
539 END IF;
540
541 FOR r_get_cp_history IN c_get_cp_history( b_max_date => nvl( r_task_time.TASK_TIME, SYSDATE ),
542 b_customer_product_id => r_sr_type.CUSTOMER_PRODUCT_ID )
543 LOOP
544 IF l_history_table.COUNT > 0 THEN
545 l_not_exists := TRUE;
546 /*Check if record exists*/
547 FOR i IN l_history_table.FIRST .. l_history_table.LAST LOOP
548 IF l_history_table.EXISTS(i) THEN
549 IF l_history_table(i) = r_get_cp_history.incident_id THEN
550 /*Record does exist do not insert but remove reference from list*/
551 l_history_table.DELETE(i);
552 l_not_exists := FALSE;
553 END IF;
554 END IF;
555 END LOOP;
556 IF l_not_exists THEN
557 /*Record does not yet exists so insert*/
558 CREATE_HISTORY_SR_RECORD( p_incident_id => p_incident_id
559 , p_history_id => r_get_cp_history.incident_id
560 , p_resource_id => p_resource_id
561 , p_closed_date => r_get_cp_history.close_date );
562 END IF;
563 ELSE
564 /*Record does not yet exists so insert*/
565 CREATE_HISTORY_SR_RECORD( p_incident_id => p_incident_id
566 , p_history_id => r_get_cp_history.incident_id
567 , p_resource_id => p_resource_id
568 , p_closed_date => r_get_cp_history.close_date );
569 END IF;
570
571 l_history_count := l_history_count - 1;
572 EXIT WHEN l_history_count = 0;
573 END LOOP;
574
575 /*Push delete to history records that are no longer history record*/
576 IF l_history_table.COUNT > 0 THEN
577 FOR i IN l_history_table.FIRST .. l_history_table.LAST LOOP
578 IF l_history_table.EXISTS(i) THEN
579 DELETE_HISTORY_SR_RECORD( p_incident_id, l_history_table(i), p_resource_id );
580 END IF;
581 END LOOP;
582 END IF;
583 ELSE
584 /*SR history for cust/install site*/
585 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
586 jtm_message_log_pkg.Log_Msg
587 ( null
588 , g_table_name
589 , 'Service request is not based on a customer product, retrieving history for' ||
590 fnd_global.local_chr(10) || 'task time = ' || to_char(r_task_time.TASK_TIME, 'DD-MON-YYYY HH24:MI') ||
591 fnd_global.local_chr(10) || 'customer_id = ' || r_sr_type.CUSTOMER_ID ||
592 fnd_global.local_chr(10) || 'incident_location_id = ' || r_sr_type.incident_location_id
593 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
594 , 'csl_service_history_pkg');
595 END IF;
596
597 FOR r_get_non_cp_history IN c_get_non_cp_history( b_max_date => nvl( r_task_time.TASK_TIME, SYSDATE ),
598 b_customer_id => r_sr_type.CUSTOMER_ID,
599 b_incident_location_id => r_sr_type.incident_location_id )
600 LOOP
601
602 IF l_history_table.COUNT > 0 THEN
603 l_not_exists := TRUE;
604 /*Check if record exists*/
605 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
606 jtm_message_log_pkg.Log_Msg
607 ( null
608 , g_table_name
609 , 'Checking if incident_id ' || r_get_non_cp_history.incident_id ||' needs to be replicated'
610 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
611 , 'csl_service_history_pkg');
612 END IF;
613
614 FOR i IN l_history_table.FIRST .. l_history_table.LAST LOOP
615 IF l_history_table.EXISTS(i) THEN
616 IF l_history_table(i) = r_get_non_cp_history.incident_id THEN
617 /*Record does exist do not insert but remove reference from list*/
618 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
619 jtm_message_log_pkg.Log_Msg
620 ( null
621 , g_table_name
622 , 'Already replicated, deleting from PL/SQL table'
623 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
624 , 'csl_service_history_pkg');
625 END IF;
626 l_history_table.DELETE(i);
627 l_not_exists := FALSE;
628 END IF;
629 END IF;
630 END LOOP;
631 IF l_not_exists THEN
632 /*Record does not yet exists so insert*/
633 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
634 jtm_message_log_pkg.Log_Msg
635 ( null
636 , g_table_name
637 , 'Record not replicated yet; push it to client(s)'
638 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
639 , 'csl_service_history_pkg');
640 END IF;
641 CREATE_HISTORY_SR_RECORD( p_incident_id => p_incident_id
642 , p_history_id => r_get_non_cp_history.incident_id
643 , p_resource_id => p_resource_id
644 , p_closed_date => r_get_non_cp_history.close_date );
645 END IF;
646 ELSE
647 CREATE_HISTORY_SR_RECORD( p_incident_id => p_incident_id
648 , p_history_id => r_get_non_cp_history.incident_id
649 , p_resource_id => p_resource_id
650 , p_closed_date => r_get_non_cp_history.close_date );
651 END IF;
652 l_history_count := l_history_count - 1;
653 EXIT WHEN l_history_count = 0;
654 END LOOP;
655
656 /*Push delete to history records that are no longer history record*/
657 IF l_history_table.COUNT > 0 THEN
658 FOR i IN l_history_table.FIRST .. l_history_table.LAST LOOP
659 IF l_history_table.EXISTS(i) THEN
660 DELETE_HISTORY_SR_RECORD( p_incident_id, l_history_table(i), p_resource_id );
661 END IF;
662 END LOOP;
663 END IF;
664 END IF;
665 ELSE
666 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
667 jtm_message_log_pkg.Log_Msg
668 ( null
669 , g_table_name
670 , 'Could not find data for incident id '||p_incident_id
671 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
672 , 'csl_service_history_pkg');
673 END IF;
674 END IF;
675 CLOSE c_sr_type;
676
677 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
678 jtm_message_log_pkg.Log_Msg
679 ( null
680 , g_table_name
681 , 'Leaving CALCULATE_HISTORY for incident '||p_incident_id
682 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
683 , 'csl_service_history_pkg');
684 END IF;
685
686 EXCEPTION WHEN OTHERS THEN
687 jtm_message_log_pkg.Log_Msg
688 ( null
689 , g_table_name
690 , 'Exception in CSL_SERVICE_HISTORY_PKG.CALCULATE_HISTORY for incident '||p_incident_id||
691 ' and resource '||p_resource_id||':'|| fnd_global.local_chr(10) || sqlerrm
692 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
693 , 'csl_service_history_pkg');
694 END CALCULATE_HISTORY;
695
696 PROCEDURE CONCURRENT_HISTORY
697 IS
698 PRAGMA AUTONOMOUS_TRANSACTION;
699 CURSOR c_acc_incidents IS
700 SELECT acc.INCIDENT_ID
701 , acc.RESOURCE_ID
702 FROM CSL_CS_INCIDENTS_ALL_ACC acc
703 , CS_INCIDENTS_ALL_B inc
704 , JTF_TASKS_B tsk
705 , JTF_TASK_ASSIGNMENTS ta
706 WHERE tsk.SOURCE_OBJECT_ID = inc.INCIDENT_ID
707 AND tsk.SOURCE_OBJECT_TYPE_CODE = 'SR'
708 AND tsk.TASK_ID = ta.TASK_ID
709 AND ta.RESOURCE_ID = acc.RESOURCE_ID
710 AND inc.INCIDENT_ID = acc.INCIDENT_ID
711 AND tsk.SCHEDULED_END_DATE >= TRUNC(SYSDATE)
712 AND NVL(inc.CLOSE_DATE, SYSDATE ) >= SYSDATE;
713
714 BEGIN
715 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
716 jtm_message_log_pkg.Log_Msg
717 ( null
718 , g_table_name
719 , 'Entering CONCURRENT_HISTORY'
720 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
721 , 'csl_service_history_pkg');
722 END IF;
723
724 FOR r_acc_incident IN c_acc_incidents LOOP
725 CALCULATE_HISTORY( p_incident_id => r_acc_incident.INCIDENT_ID
726 , p_resource_id => r_acc_incident.RESOURCE_ID );
727 END LOOP;
728
729 UPDATE JTM_CON_REQUEST_DATA
730 SET LAST_RUN_DATE = SYSDATE
731 WHERE PRODUCT_CODE = 'CSL'
732 AND PACKAGE_NAME = 'CSL_SERVICE_HISTORY_PKG'
733 AND PROCEDURE_NAME = 'CONCURRENT_HISTORY';
734
735
736 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
737 jtm_message_log_pkg.Log_Msg
738 ( null
739 , g_table_name
740 , 'Leaving CONCURRENT_HISTORY'
741 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
742 , 'csl_service_history_pkg');
743 END IF;
744 COMMIT;
745 EXCEPTION WHEN OTHERS THEN
746 ROLLBACK;
747 jtm_message_log_pkg.Log_Msg
748 ( null
749 , g_table_name
750 , 'Exception in CSL_SERVICE_HISTORY_PKG.CONCURRENT_HISTORY'||':'
751 || fnd_global.local_chr(10) || sqlerrm
752 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
753 , 'csl_service_history_pkg');
754 END CONCURRENT_HISTORY;
755
756 END CSL_SERVICE_HISTORY_PKG;