[Home] [Help]
PACKAGE BODY: APPS.CSM_SERVICE_HISTORY_EVENT_PKG
Source
1 PACKAGE BODY CSM_SERVICE_HISTORY_EVENT_PKG AS
2 /* $Header: csmsrhb.pls 120.6 2008/02/08 12:29:18 trajasek ship $ */
3
4 /*** Globals ***/
5 g_debug_level NUMBER;
6 g_object_name CONSTANT VARCHAR2(30) := 'CSM_SERVICE_HISTORY_EVENT_PKG' ;
7 g_table_name CONSTANT VARCHAR2(30) := 'CSM_SERVICE_HISTORY';
8 g_publication_item_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
9 CSM_ACC_PKG.t_publication_item_list('CSM_SERVICE_HISTORY');
10 g_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_SERVICE_HISTORY_ACC';
11 g_pk1_name CONSTANT VARCHAR2(30) := 'INCIDENT_ID';
12 g_pk2_name CONSTANT VARCHAR2(30) := 'HISTORY_INCIDENT_ID';
13 g_pk3_name CONSTANT VARCHAR2(30) := 'INSTANCE_ID';
14 g_seq_name CONSTANT VARCHAR2(30) := 'CSM_SERVICE_HISTORY_ACC_S' ;
15
16 g_incidents_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_INCIDENTS_ALL_ACC';
17 g_incidents_table_name CONSTANT VARCHAR2(30) := 'CS_INCIDENTS_ALL';
18 g_incidents_seq_name CONSTANT VARCHAR2(30) := 'CSM_INCIDENTS_ALL_ACC_S' ;
19 g_incidents_pk1_name CONSTANT VARCHAR2(30) := 'INCIDENT_ID';
20 g_incidents_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
21 CSM_ACC_PKG.t_publication_item_list('CSM_INCIDENTS_ALL');
22
23 PROCEDURE DELETE_HISTORY_SR_RECORD( p_incident_id NUMBER
24 , p_history_id NUMBER
25 , p_user_id NUMBER )
26 IS
27 l_sqlerrno VARCHAR2(20);
28 l_sqlerrmsg VARCHAR2(4000);
29 l_error_msg VARCHAR2(4000);
30 l_return_status VARCHAR2(2000);
31
32 CURSOR c_closed_assignments( b_incident_id NUMBER ) IS
33 SELECT TASK_ASSIGNMENT_ID
34 FROM JTF_TASKS_B tk
35 , JTF_TASK_ASSIGNMENTS ta
36 , JTF_TASK_TYPES_B tt
37 , JTF_TASK_STATUSES_B tkst
38 , JTF_TASK_STATUSES_B tast
39 WHERE tk.SOURCE_OBJECT_ID = b_incident_id
40 AND tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
41 AND tk.TASK_ID = ta.TASK_ID
42 AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
43 AND tk.TASK_STATUS_ID = tkst.TASK_STATUS_ID
44 AND (tkst.CLOSED_FLAG = 'Y'
45 OR tkst.COMPLETED_FLAG = 'Y')
46 AND nvl(tkst.CANCELLED_FLAG,'N') <> 'Y'
47 AND nvl(tkst.REJECTED_FLAG,'N') <> 'Y'
48 AND tk.TASK_TYPE_ID = tt.TASK_TYPE_ID
49 AND tt.RULE = 'DISPATCH'
50 AND ta.ASSIGNMENT_STATUS_ID = tast.TASK_STATUS_ID
51 AND (tast.CLOSED_FLAG = 'Y'
52 OR tast.COMPLETED_FLAG = 'Y')
53 AND nvl(tast.CANCELLED_FLAG,'N') <> 'Y'
54 AND nvl(tast.REJECTED_FLAG,'N') <> 'Y' ;
55
56 BEGIN
57
58 CSM_UTIL_PKG.LOG('Entering CSM_SERVICE_HISTORY_EVENT_PKG.DELETE_HISTORY_SR_RECORD',
59 'CSM_SERVICE_HISTORY_EVENT_PKG.DELETE_HISTORY_SR_RECOR',FND_LOG.LEVEL_PROCEDURE);
60
61 --Calculate ta + debrief
62 FOR r_closed_assignement IN c_closed_assignments( b_incident_id => p_history_id ) LOOP
63
64 csm_task_assignment_event_pkg.task_assignment_hist_del_init
65 (p_task_assignment_id=>r_closed_assignement.task_assignment_id,
66 p_parent_incident_id=>p_incident_id,
67 p_user_id=>p_user_id,
68 p_error_msg=>l_error_msg,
69 x_return_status=>l_return_status);
70
71 END LOOP;
72 CSM_UTIL_PKG.LOG('Leaving CSM_SERVICE_HISTORY_EVENT_PKG.DELETE_HISTORY_SR_RECORD',
73 'CSM_SERVICE_HISTORY_EVENT_PKG.DELETE_HISTORY_SR_RECOR',FND_LOG.LEVEL_EXCEPTION);
74
75
76 EXCEPTION WHEN OTHERS THEN
77 l_sqlerrno := to_char(SQLCODE);
78 l_sqlerrmsg := substr(SQLERRM, 1,2000);
79 l_error_msg := l_error_msg ||'- Exception in DELETE_HISTORY_SR_RECORD for incident_id:' || p_incident_id
80 || ' and user_id: ' || p_user_id || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
81 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SERVICE_HISTORY_EVENT_PKG.DELETE_HISTORY_SR_RECORD',FND_LOG.LEVEL_EXCEPTION);
82 RAISE;
83 END DELETE_HISTORY_SR_RECORD;
84
85 PROCEDURE CREATE_HISTORY_SR_RECORD( p_incident_id IN NUMBER
86 , p_history_id IN NUMBER
87 , p_user_id IN NUMBER
88 , p_closed_date IN DATE )
89 IS
90 l_dummy BOOLEAN;
91 l_sqlerrno VARCHAR2(20);
92 l_sqlerrmsg VARCHAR2(4000);
93 l_error_msg VARCHAR2(4000);
94 l_return_status VARCHAR2(2000);
95
96 CURSOR c_closed_assignments( b_hist_incident_id NUMBER, b_incident_id IN number,
97 b_user_id IN number) IS
98 SELECT TASK_ASSIGNMENT_ID
99 FROM JTF_TASKS_B tk
100 , JTF_TASK_ASSIGNMENTS ta
101 , JTF_TASK_TYPES_B tt
102 , JTF_TASK_STATUSES_B tkst
103 , JTF_TASK_STATUSES_B tast
104 WHERE tk.SOURCE_OBJECT_ID = b_hist_incident_id
105 AND tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
106 AND tk.TASK_ID = ta.TASK_ID
107 AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
108 AND tk.TASK_STATUS_ID = tkst.TASK_STATUS_ID
109 AND (tkst.CLOSED_FLAG = 'Y'
110 OR tkst.COMPLETED_FLAG = 'Y')
111 AND nvl(tkst.CANCELLED_FLAG,'N') <> 'Y'
112 AND nvl(tkst.REJECTED_FLAG,'N') <> 'Y'
113 AND tk.TASK_TYPE_ID = tt.TASK_TYPE_ID
114 AND tt.RULE = 'DISPATCH'
115 AND ta.ASSIGNMENT_STATUS_ID = tast.TASK_STATUS_ID
116 AND (tast.CLOSED_FLAG = 'Y'
117 OR tast.COMPLETED_FLAG = 'Y')
118 AND nvl(tast.CANCELLED_FLAG,'N') <> 'Y'
119 AND nvl(tast.REJECTED_FLAG,'N') <> 'Y'
120 AND NOT EXISTS
121 (SELECT 1
122 FROM csm_service_history_acc acc
123 WHERE acc.user_id = b_user_id
124 AND acc.incident_id = b_incident_id
125 AND acc.history_incident_id = b_hist_incident_id
126 );
127
128 l_itemtype varchar2(30);
129 l_itemkey varchar2(1000);
130 l_seq_val number;
131 BEGIN
132
133 CSM_UTIL_PKG.LOG('Entering CSM_SERVICE_HISTORY_EVENT_PKG.CREATE_HISTORY_SR_RECORD',
134 'CSM_SERVICE_HISTORY_EVENT_PKG.CREATE_HISTORY_SR_RECOR',FND_LOG.LEVEL_PROCEDURE);
135
136 --use the CSMTYPE3 itemtype
137 l_itemtype := 'CSMTYPE3';
138
139 --Call for each task assignment
140 FOR r_closed_assignement IN c_closed_assignments( b_hist_incident_id => p_history_id, b_incident_id => p_incident_id,
141 b_user_id => p_user_id) LOOP
142
143 csm_task_assignment_event_pkg.task_assignment_hist_init
144 (p_task_assignment_id=>r_closed_assignement.task_assignment_id,
145 p_parent_incident_id=>p_incident_id,
146 p_user_id=>p_user_id,
147 p_error_msg=>l_error_msg,
148 x_return_status=>l_return_status);
149
150 END LOOP;
151 CSM_UTIL_PKG.LOG('Leaving CSM_SERVICE_HISTORY_EVENT_PKG.CREATE_HISTORY_SR_RECORD',
152 'CSM_SERVICE_HISTORY_EVENT_PKG.CREATE_HISTORY_SR_RECOR',FND_LOG.LEVEL_PROCEDURE);
153
154 EXCEPTION WHEN OTHERS THEN
155 l_sqlerrno := to_char(SQLCODE);
156 l_sqlerrmsg := substr(SQLERRM, 1,2000);
157 l_error_msg := l_error_msg ||'- Exception in CREATE_HISTORY_SR_RECORD for incident_id:' || p_incident_id
158 || ' and user_id: ' || p_user_id || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
159 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SERVICE_HISTORY_EVENT_PKG.CREATE_HISTORY_SR_RECORD',FND_LOG.LEVEL_EXCEPTION);
160 RAISE;
161 END CREATE_HISTORY_SR_RECORD;
162
163 /*Procedure calculates the x number of history service request for the given sr */
164 PROCEDURE CALCULATE_HISTORY( l_incident_id in number,
165 l_user_id in number)
166 IS
167 CURSOR c_sr_type( b_incident_id NUMBER )
168 IS
169 SELECT CUSTOMER_PRODUCT_ID
170 , INSTALL_SITE_ID
171 , CUSTOMER_ID
172 , INCIDENT_LOCATION_ID
173 FROM CS_INCIDENTS_ALL_B
174 WHERE INCIDENT_ID = b_incident_id;
175
176 r_sr_type c_sr_type%ROWTYPE;
177
178 CURSOR c_task_time( b_incident_id NUMBER
179 , b_user_id NUMBER )
180 IS
181 SELECT MAX(tk.SCHEDULED_END_DATE ) AS "TASK_TIME"
182 FROM JTF_TASKS_B tk
183 , JTF_TASK_ASSIGNMENTS ta
184 , JTF_RS_RESOURCE_EXTNS rs
185 WHERE tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
186 AND tk.SOURCE_OBJECT_ID = b_incident_id
187 AND tk.TASK_ID = ta.TASK_ID
188 AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
189 AND ta.RESOURCE_ID = rs.resource_id
190 AND rs.user_id = b_user_id ;
191
192 r_task_time c_task_time%ROWTYPE;
193
194 CURSOR c_get_cp_history( b_max_date DATE,
195 b_customer_product_id NUMBER )
196 IS
197 SELECT DISTINCT inc.INCIDENT_ID
198 , inc.CLOSE_DATE
199 FROM CS_INCIDENTS_ALL_B inc
200 , JTF_TASKS_B tk
201 , JTF_TASK_ASSIGNMENTS ta
202 , CS_INCIDENT_STATUSES_B ists
203 , JTF_TASK_TYPES_B tt
204 , JTF_TASK_STATUSES_B tkst
205 , JTF_TASK_STATUSES_B tast
206 WHERE inc.CLOSE_DATE <= b_max_date
207 AND inc.INCIDENT_ID = tk.SOURCE_OBJECT_ID
208 AND inc.INCIDENT_STATUS_ID = ists.INCIDENT_STATUS_ID
209 --AND inc.install_site_id IS NOT NULL
210 AND ists.CLOSE_FLAG = 'Y'
211 AND tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
212 AND tk.TASK_ID = ta.TASK_ID
213 AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
214 AND tk.TASK_STATUS_ID = tkst.TASK_STATUS_ID
215 AND (tkst.CLOSED_FLAG = 'Y' OR tkst.COMPLETED_FLAG = 'Y')
216 AND NVL(tkst.CANCELLED_FLAG,'N') <> 'Y'
217 AND NVL(tkst.REJECTED_FLAG,'N') <> 'Y'
218 AND tk.TASK_TYPE_ID = tt.TASK_TYPE_ID
219 AND tt.RULE = 'DISPATCH'
220 AND ta.ASSIGNMENT_STATUS_ID = tast.TASK_STATUS_ID
221 AND (tast.CLOSED_FLAG = 'Y' OR tast.COMPLETED_FLAG = 'Y')
222 AND NVL(tast.CANCELLED_FLAG,'N') <> 'Y'
223 AND NVL(tast.REJECTED_FLAG,'N') <> 'Y'
224 AND inc.CUSTOMER_PRODUCT_ID = b_customer_product_id
225 ORDER BY inc.CLOSE_DATE DESC;
226
227 CURSOR c_get_non_cp_history( b_max_date DATE,
228 b_customer_id NUMBER,
229 b_INCIDENT_LOCATION_ID NUMBER )
230 IS
231 SELECT DISTINCT inc.INCIDENT_ID
232 , inc.CLOSE_DATE
233 FROM CS_INCIDENTS_ALL_B inc
234 , JTF_TASKS_B tk
235 , JTF_TASK_ASSIGNMENTS ta
236 , CS_INCIDENT_STATUSES_B ists
237 , JTF_TASK_TYPES_B tt
238 , JTF_TASK_STATUSES_B tkst
239 , JTF_TASK_STATUSES_B tast
240 WHERE inc.CLOSE_DATE <= b_max_date
241 AND inc.INCIDENT_ID = tk.SOURCE_OBJECT_ID
242 AND inc.INCIDENT_STATUS_ID = ists.INCIDENT_STATUS_ID
243 --AND inc.install_site_id IS NOT NULL
244 AND ists.CLOSE_FLAG = 'Y'
245 AND tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
246 AND tk.TASK_ID = ta.TASK_ID
247 AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
248 AND tk.TASK_STATUS_ID = tkst.TASK_STATUS_ID
249 AND (tkst.CLOSED_FLAG = 'Y' OR tkst.COMPLETED_FLAG = 'Y')
250 AND NVL(tkst.CANCELLED_FLAG,'N') <> 'Y'
251 AND NVL(tkst.REJECTED_FLAG,'N') <> 'Y'
252 AND tk.TASK_TYPE_ID = tt.TASK_TYPE_ID
253 AND tt.RULE = 'DISPATCH'
254 AND ta.ASSIGNMENT_STATUS_ID = tast.TASK_STATUS_ID
255 AND (tast.CLOSED_FLAG = 'Y' OR tast.COMPLETED_FLAG = 'Y')
256 AND NVL(tkst.CANCELLED_FLAG,'N') <> 'Y'
257 AND NVL(tkst.REJECTED_FLAG,'N') <> 'Y'
258 AND inc.CUSTOMER_ID = b_customer_id
259 AND inc.INCIDENT_LOCATION_ID = b_INCIDENT_LOCATION_ID
260 ORDER BY inc.CLOSE_DATE DESC;
261
262 l_history_count NUMBER;
263
264 CURSOR c_history( b_incident_id NUMBER, b_user_id NUMBER )
265 IS
266 SELECT HISTORY_INCIDENT_ID
267 FROM CSM_SERVICE_HISTORY_ACC
268 WHERE INCIDENT_ID = b_incident_id
269 AND USER_ID = b_user_id;
270
271 TYPE history_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
272
273 l_history_table history_table_type;
274 l_cntr NUMBER;
275 l_not_exists BOOLEAN;
276 l_sqlerrno VARCHAR2(20);
277 l_sqlerrmsg VARCHAR2(4000);
278 l_error_msg VARCHAR2(4000);
279 l_return_status VARCHAR2(2000);
280
281 BEGIN
282 CSM_UTIL_PKG.LOG('Entering CSM_SERVICE_HISTORY_EVENT_PKG.CALCULATE_HISTORY',
283 'CSM_SERVICE_HISTORY_EVENT_PKG.CALCULATE_HISTORY',FND_LOG.LEVEL_PROCEDURE);
284
285 /*Get history count from profile*/
286 l_history_count := csm_profile_pkg.get_history_count(l_user_id);
287
288 /*TODO: FETCH ALL EXISTING HISTORY RECORDS FOR THIS INCIDENT IN A PLSQL TABLE*/
289 /* WHEN CALCULATING NEEDED RECORDS SHOULD BE COMPARED TO THIS TABLE*/
290 /* IF IT MATCHES RECORD DOES NOT NEED TO BE INSERTED AND THE PLSQL RECORD SHOULD BE DELETED*/
291 /* AT THE END PUSH A DELETE FOR THE REMAINING RECORDS AS THEY ARE NO LONGER NEEDED*/
292 OPEN c_history( l_incident_id, l_user_id );
293 FETCH c_history BULK COLLECT INTO l_history_table;
294 CLOSE c_history;
295
296 OPEN c_sr_type( b_incident_id => l_incident_id );
297 FETCH c_sr_type INTO r_sr_type;
298 IF c_sr_type%FOUND THEN
299 OPEN c_task_time( b_incident_id => l_incident_id, b_user_id => l_user_id );
300 FETCH c_task_time INTO r_task_time;
301 CLOSE c_task_time;
302 /*Check for sr type ( on CP or not )*/
303 IF r_sr_type.CUSTOMER_PRODUCT_ID IS NOT NULL THEN
304 /*Only history for CP product*/
305 CSM_UTIL_PKG.LOG(' Service request is based on a customer product',
306 'CSM_SERVICE_HISTORY_EVENT_PKG.CALCULATE_HISTORY',FND_LOG.LEVEL_PROCEDURE);
307
308 -- If csm_history_count profile value > 0
309 IF l_history_count > 0 THEN
310
311 FOR r_get_cp_history IN c_get_cp_history( b_max_date => nvl( r_task_time.TASK_TIME, SYSDATE ),
312 b_customer_product_id => r_sr_type.CUSTOMER_PRODUCT_ID )
313 LOOP
314 IF l_history_table.COUNT > 0 THEN
315 l_not_exists := TRUE;
316 /*Check if record exists*/
317 FOR i IN l_history_table.FIRST .. l_history_table.LAST LOOP
318 IF l_history_table.EXISTS(i) THEN
319 IF l_history_table(i) = r_get_cp_history.incident_id THEN
320 /*Record does exist do not insert but remove reference from list*/
321 CSM_UTIL_PKG.LOG('Already replicated, deleting from PLSQL table',
322 'CSM_SERVICE_HISTORY_EVENT_PKG.CALCULATE_HISTORY',FND_LOG.LEVEL_PROCEDURE);
323 l_history_table.DELETE(i);
324 l_not_exists := FALSE;
325 END IF;
326 END IF;
327 END LOOP; -- end of for looping over l_history table
328 IF l_not_exists THEN
329 /*Record does not yet exists so insert*/
330 CSM_UTIL_PKG.LOG('Record not replicated yet; push it to client(s)',
331 'CSM_SERVICE_HISTORY_EVENT_PKG.CALCULATE_HISTORY',FND_LOG.LEVEL_PROCEDURE);
332 CREATE_HISTORY_SR_RECORD( p_incident_id => l_incident_id
333 , p_history_id => r_get_cp_history.incident_id
334 , p_user_id => l_user_id
335 , p_closed_date => r_get_cp_history.close_date );
336 END IF;
337 ELSE -- else when l_history_table count is 0
338 /*Record does not yet exists so insert*/
339 CREATE_HISTORY_SR_RECORD( p_incident_id => l_incident_id
340 , p_history_id => r_get_cp_history.incident_id
341 , p_user_id => l_user_id
342 , p_closed_date => r_get_cp_history.close_date );
343 END IF;
344
345 l_history_count := l_history_count - 1;
346 EXIT WHEN l_history_count = 0;
347 END LOOP;
348 END IF ; -- l_history_count > 0
349 /*Push delete to history records that are no longer history record*/
350 IF l_history_table.COUNT > 0 THEN
351 FOR i IN l_history_table.FIRST .. l_history_table.LAST LOOP
352 IF l_history_table.EXISTS(i) THEN
353 DELETE_HISTORY_SR_RECORD( l_incident_id, l_history_table(i), l_user_id );
354 END IF;
355 END LOOP;
356 END IF;
357 ELSE
358 /*SR history for cust/install site*/
359 CSM_UTIL_PKG.LOG(' Service request is not based on a customer product',
360 'CSM_SERVICE_HISTORY_EVENT_PKG.CALCULATE_HISTORY',FND_LOG.LEVEL_PROCEDURE);
361
362 -- If csm_history_count profile value > 0
363 IF l_history_count > 0 THEN
364
365 FOR r_get_non_cp_history IN c_get_non_cp_history( b_max_date => nvl( r_task_time.TASK_TIME, SYSDATE ),
366 b_customer_id => r_sr_type.CUSTOMER_ID,
367 b_INCIDENT_LOCATION_ID => r_sr_type.INCIDENT_LOCATION_ID )
368
369 LOOP
370
371 IF l_history_table.COUNT > 0 THEN
372 l_not_exists := TRUE;
373
374 FOR i IN l_history_table.FIRST .. l_history_table.LAST LOOP
375 IF l_history_table.EXISTS(i) THEN
376 IF l_history_table(i) = r_get_non_cp_history.incident_id THEN
377 /*Record does exist do not insert but remove reference from list*/
378 CSM_UTIL_PKG.LOG('Already replicated, deleting from PLSQL table',
379 'CSM_SERVICE_HISTORY_EVENT_PKG.CALCULATE_HISTORY',FND_LOG.LEVEL_PROCEDURE);
380 l_history_table.DELETE(i);
381 l_not_exists := FALSE;
382 END IF;
383 END IF;
384 END LOOP;
385 IF l_not_exists THEN
386 /*Record does not yet exists so insert*/
387 CSM_UTIL_PKG.LOG('Record not replicated yet; push it to client(s)',
388 'CSM_SERVICE_HISTORY_EVENT_PKG.CALCULATE_HISTORY',FND_LOG.LEVEL_PROCEDURE);
389
390 CREATE_HISTORY_SR_RECORD( p_incident_id => l_incident_id
391 , p_history_id => r_get_non_cp_history.incident_id
392 , p_user_id => l_user_id
393 , p_closed_date => r_get_non_cp_history.close_date );
394 END IF;
395 ELSE
396 CREATE_HISTORY_SR_RECORD( p_incident_id => l_incident_id
397 , p_history_id => r_get_non_cp_history.incident_id
398 , p_user_id => l_user_id
399 , p_closed_date => r_get_non_cp_history.close_date );
400 END IF;
401 l_history_count := l_history_count - 1;
402 EXIT WHEN l_history_count = 0;
403 END LOOP;
404 END IF ; -- l_history_count > 0
405 /*Push delete to history records that are no longer history record*/
406 IF l_history_table.COUNT > 0 THEN
407 FOR i IN l_history_table.FIRST .. l_history_table.LAST LOOP
408 IF l_history_table.EXISTS(i) THEN
409 DELETE_HISTORY_SR_RECORD( l_incident_id, l_history_table(i), l_user_id );
410 END IF;
411 END LOOP;
412 END IF;
413 END IF;
414 ELSE
415
416 null ;
417 END IF;
418 CLOSE c_sr_type;
419 CSM_UTIL_PKG.LOG('Leaving CSM_SERVICE_HISTORY_EVENT_PKG.CALCULATE_HISTORY',
420 'CSM_SERVICE_HISTORY_EVENT_PKG.CALCULATE_HISTORY',FND_LOG.LEVEL_PROCEDURE);
421
422
423 EXCEPTION
424 WHEN OTHERS THEN
425 l_sqlerrno := to_char(SQLCODE);
426 l_sqlerrmsg := substr(SQLERRM, 1,2000);
427 l_error_msg := ' Exception in CALCULATE_HISTORY for incident_id: ' || l_incident_id ||
428 ' and for user_id: ' || l_user_id || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
429 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SERVICE_HISTORY_EVENT_PKG.CALCULATE_HISTORY',FND_LOG.LEVEL_EXCEPTION);
430 RAISE;
431 END CALCULATE_HISTORY;
432
433 PROCEDURE CONCURRENT_HISTORY(p_status OUT NOCOPY VARCHAR2, p_message OUT NOCOPY VARCHAR2)
434 IS
435 PRAGMA AUTONOMOUS_TRANSACTION;
436
437 CURSOR c_acc_incidents
438 IS
439 SELECT acc.INCIDENT_ID
440 , acc.USER_ID
441 FROM CSM_INCIDENTS_ALL_ACC acc
442 , CS_INCIDENTS_ALL_B inc
443 , CS_INCIDENT_STATUSES_b cis
444 , JTF_TASKS_B tsk
445 , JTF_TASK_ASSIGNMENTS ta
446 , JTF_RS_RESOURCE_EXTNS rs
447 WHERE tsk.SOURCE_OBJECT_ID = inc.INCIDENT_ID
448 AND tsk.SOURCE_OBJECT_TYPE_CODE = 'SR'
449 AND tsk.TASK_ID = ta.TASK_ID
450 AND ta.RESOURCE_ID = rs.RESOURCE_ID
451 AND rs.USER_ID = acc.USER_ID
452 AND inc.INCIDENT_ID = acc.INCIDENT_ID
453 AND inc.incident_status_id = cis.incident_status_id
454 AND NVL(cis.close_flag,'N') <> 'Y'
455 AND tsk.SCHEDULED_END_DATE >= TRUNC(SYSDATE)
456 AND NVL(inc.CLOSE_DATE, SYSDATE ) >= SYSDATE;
457
458 -- this purges SR history that was not purged by the TA purge
459 -- ideally this shouldn't happen and this is more of a data fix
460 CURSOR l_purge_SR_history
461 IS
462 SELECT /*+INDEX (hacc CSM_SERVICE_HISTORY_ACC_U1)*/ user_id,
463 incident_id,
464 history_incident_id
465 FROM csm_service_history_acc hacc
466 WHERE NOT EXISTS
467 (SELECT 'X'
468 FROM csm_incidents_all_acc acc
469 WHERE acc.user_id = hacc.user_id
470 AND acc.incident_id = hacc.incident_id);
471
472 CURSOR l_upd_last_run_date_csr
473 IS
474 SELECT 1
475 FROM jtm_con_request_data
476 WHERE product_code = 'CSM'
477 AND package_name = 'CSM_SERVICE_HISTORY_EVENT_PKG'
478 AND procedure_name = 'CONCURRENT_HISTORY'
479 FOR UPDATE OF last_run_date NOWAIT ;
480
481 l_last_run_date date ;
482 l_dummy number ;
483 l_sqlerrno VARCHAR2(20);
484 l_sqlerrmsg VARCHAR2(4000);
485 l_error_msg VARCHAR2(4000);
486 l_return_status VARCHAR2(2000);
487
488 BEGIN
489 CSM_UTIL_PKG.LOG('Entering CSM_SERVICE_HISTORY_EVENT_PKG.CONCURRENT_HISTORY',
490 'CSM_SERVICE_HISTORY_EVENT_PKG.CONCURRENT_HISTORY',FND_LOG.LEVEL_PROCEDURE);
491
492 /* Assign flow_type to 'HISTORY' */
493 -- csm_util_pkg.g_flow_type := 'HISTORY' ;
494 l_last_run_date := SYSDATE;
495
496 -- get new history
497 FOR r_acc_incident IN c_acc_incidents LOOP
498 CALCULATE_HISTORY( l_incident_id => r_acc_incident.INCIDENT_ID
499 , l_user_id => r_acc_incident.USER_ID );
500 END LOOP;
501
502 -- purge history that is not purged by TA purge
503 FOR r_purge_SR_history IN l_purge_SR_history LOOP
504 DELETE_HISTORY_SR_RECORD(p_incident_id=>r_purge_SR_history.incident_id,
505 p_history_id=>r_purge_SR_history.history_incident_id,
506 p_user_id=>r_purge_SR_history.user_id);
507 END LOOP;
508
509 -- update last_run_date
510 OPEN l_upd_last_run_date_csr;
511 FETCH l_upd_last_run_date_csr INTO l_dummy;
512 IF l_upd_last_run_date_csr%FOUND THEN
513 UPDATE jtm_con_request_data
514 SET last_run_date = l_last_run_date
515 WHERE CURRENT OF l_upd_last_run_date_csr;
516 END IF;
517 CLOSE l_upd_last_run_date_csr;
518
519 COMMIT;
520
521 CSM_UTIL_PKG.LOG('Leaving CSM_SERVICE_HISTORY_EVENT_PKG.CONCURRENT_HISTORY',
522 'CSM_SERVICE_HISTORY_EVENT_PKG.CONCURRENT_HISTORY',FND_LOG.LEVEL_PROCEDURE);
523 p_status := 'SUCCESS';
524 p_message := 'CSM_SERVICE_HISTORY_EVENT_PKG.CONCURRENT_HISTORY Executed successfully';
525
526 EXCEPTION
527 WHEN OTHERS THEN
528 l_sqlerrno := to_char(SQLCODE);
529 l_sqlerrmsg := substr(SQLERRM, 1,2000);
530 l_error_msg := ' Exception in CONCURRENT_HISTORY : ' || l_sqlerrno || ':' || l_sqlerrmsg;
531 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SERVICE_HISTORY_EVENT_PKG.CONCURRENT_HISTORY',FND_LOG.LEVEL_EXCEPTION);
532 p_status := 'ERROR';
533 p_message := 'Error in CSM_SERVICE_HISTORY_EVENT_PKG.CONCURRENT_HISTORY: ' || l_error_msg;
534 ROLLBACK;
535 END CONCURRENT_HISTORY;
536
537 PROCEDURE SERVICE_HISTORY_ACC_I(p_parent_incident_id IN NUMBER,
538 p_incident_id IN NUMBER,
539 p_user_id IN NUMBER)
540 IS
541 l_sqlerrno VARCHAR2(20);
542 l_sqlerrmsg VARCHAR2(4000);
543 l_error_msg VARCHAR2(4000);
544 l_return_status VARCHAR2(2000);
545
546 BEGIN
547 CSM_UTIL_PKG.LOG('Entering SERVICE_HISTORY_ACC_I for incident_id: ' || p_incident_id
548 || ' and parent_incident_id:' || p_parent_incident_id,
549 'CSM_SERVICE_HISTORY_EVENT_PKG.SERVICE_HISTORY_ACC_I',FND_LOG.LEVEL_PROCEDURE);
550
551 CSM_ACC_PKG.Insert_Acc
552 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
553 ,P_ACC_TABLE_NAME => g_acc_table_name
554 ,P_SEQ_NAME => g_seq_name
555 ,P_PK1_NAME => g_pk1_name
556 ,P_PK1_NUM_VALUE => p_parent_incident_id
557 ,P_PK2_NAME => g_pk2_name
558 ,P_PK2_NUM_VALUE => p_incident_id
559 ,p_USER_ID => p_user_id
560 );
561
562 CSM_UTIL_PKG.LOG('Leaving SERVICE_HISTORY_ACC_I for incident_id: ' || p_incident_id
563 || ' and parent_incident_id:' || p_parent_incident_id,
564 'CSM_SERVICE_HISTORY_EVENT_PKG.SERVICE_HISTORY_ACC_I',FND_LOG.LEVEL_PROCEDURE);
565 EXCEPTION
566 WHEN OTHERS THEN
567 l_sqlerrno := to_char(SQLCODE);
568 l_sqlerrmsg := substr(SQLERRM, 1,2000);
569 l_error_msg := ' Exception in SERVICE_HISTORY_ACC_I for incident_id: ' || p_incident_id
570 || ' and parent_incident_id:' || p_parent_incident_id || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
571 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SERVICE_HISTORY_EVENT_PKG.SERVICE_HISTORY_ACC_I',FND_LOG.LEVEL_EXCEPTION);
572 RAISE;
573 END SERVICE_HISTORY_ACC_I;
574
575 PROCEDURE DELETE_HISTORY(p_task_assignment_id IN NUMBER,
576 p_incident_id IN NUMBER,
577 p_user_id IN NUMBER)
578 IS
579 l_sqlerrno VARCHAR2(20);
580 l_sqlerrmsg VARCHAR2(4000);
581 l_error_msg VARCHAR2(4000);
582 l_return_status VARCHAR2(2000);
583
584 CURSOR c_history ( b_incident_id NUMBER, b_task_assignment_id NUMBER, b_user_id NUMBER ) IS
585 SELECT HISTORY_INCIDENT_ID
586 FROM CSM_SERVICE_HISTORY_ACC
587 WHERE INCIDENT_ID = b_incident_id
588 AND USER_ID = b_user_id
589 AND NOT EXISTS (SELECT 'X'
590 FROM CSM_TASK_ASSIGNMENTS_ACC ACC,
591 JTF_TASK_ASSIGNMENTS ASG,
592 JTF_TASKS_B TASK
593 WHERE ACC.TASK_ASSIGNMENT_ID = ASG.TASK_ASSIGNMENT_ID
594 AND ASG.TASK_ID = TASK.TASK_ID
595 AND TASK.SOURCE_OBJECT_TYPE_CODE = 'SR'
596 AND TASK.SOURCE_OBJECT_ID = b_incident_id
597 AND ACC.USER_ID = b_user_id
598 AND ACC.TASK_ASSIGNMENT_ID <> b_task_assignment_id);
599
600 BEGIN
601 CSM_UTIL_PKG.LOG('Entering DELETE_HISTORY for incident_id: ' || p_incident_id
602 || ' and task_assignment_id:' || p_task_assignment_id,
603 'CSM_SERVICE_HISTORY_EVENT_PKG.DELETE_HISTORY',FND_LOG.LEVEL_PROCEDURE);
604
605 FOR r_history IN c_history( p_incident_id, p_task_assignment_id, p_user_id ) LOOP
606 DELETE_HISTORY_SR_RECORD( p_incident_id, r_history.HISTORY_INCIDENT_ID, p_user_id );
607 END LOOP;
608
609 CSM_UTIL_PKG.LOG('Leaving DELETE_HISTORY for incident_id: ' || p_incident_id
610 || ' and task_assignment_id:' || p_task_assignment_id,
611 'CSM_SERVICE_HISTORY_EVENT_PKG.DELETE_HISTORY',FND_LOG.LEVEL_PROCEDURE);
612 EXCEPTION
613 WHEN OTHERS THEN
614 l_sqlerrno := to_char(SQLCODE);
615 l_sqlerrmsg := substr(SQLERRM, 1,2000);
616 l_error_msg := ' Exception in DELETE_HISTORY for incident_id: ' || p_incident_id
617 || ' and task_assignment_id:' || p_task_assignment_id || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
618 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SERVICE_HISTORY_EVENT_PKG.DELETE_HISTORY',FND_LOG.LEVEL_EXCEPTION);
619 RAISE;
620 END DELETE_HISTORY;
621
622 PROCEDURE SERVICE_HISTORY_ACC_D(p_parent_incident_id IN NUMBER,
623 p_incident_id IN NUMBER,
624 p_user_id IN NUMBER)
625 IS
626 l_sqlerrno VARCHAR2(20);
627 l_sqlerrmsg VARCHAR2(4000);
628 l_error_msg VARCHAR2(4000);
629 l_return_status VARCHAR2(2000);
630
631 BEGIN
632 CSM_UTIL_PKG.LOG('Entering SERVICE_HISTORY_ACC_D for incident_id: ' || p_incident_id
633 || ' and parent_incident_id:' || p_parent_incident_id,
634 'CSM_SERVICE_HISTORY_EVENT_PKG.SERVICE_HISTORY_ACC_D',FND_LOG.LEVEL_PROCEDURE);
635
636 CSM_ACC_PKG.Delete_Acc
637 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
638 ,P_ACC_TABLE_NAME => g_acc_table_name
639 ,P_PK1_NAME => g_pk1_name
640 ,P_PK1_NUM_VALUE => p_parent_incident_id
641 ,P_PK2_NAME => g_pk2_name
642 ,P_PK2_NUM_VALUE => p_incident_id
643 ,p_USER_ID => p_user_id
644 );
645
646 CSM_UTIL_PKG.LOG('Leaving SERVICE_HISTORY_ACC_D for incident_id: ' || p_incident_id
647 || ' and parent_incident_id:' || p_parent_incident_id,
648 'CSM_SERVICE_HISTORY_EVENT_PKG.SERVICE_HISTORY_ACC_D',FND_LOG.LEVEL_PROCEDURE);
649 EXCEPTION
650 WHEN OTHERS THEN
651 l_sqlerrno := to_char(SQLCODE);
652 l_sqlerrmsg := substr(SQLERRM, 1,2000);
653 l_error_msg := ' Exception in SERVICE_HISTORY_ACC_D for incident_id: ' || p_incident_id
654 || ' and parent_incident_id:' || p_parent_incident_id || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
655 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SERVICE_HISTORY_EVENT_PKG.SERVICE_HISTORY_ACC_D',FND_LOG.LEVEL_EXCEPTION);
656 RAISE;
657 END SERVICE_HISTORY_ACC_D;
658
659 PROCEDURE PROCESS_OWNER_HISTORY( p_return_status OUT NOCOPY VARCHAR2,p_error_message OUT NOCOPY VARCHAR2
660 )
661 IS
662
663 TYPE l_instance_id_tbl_type IS TABLE OF csm_item_instances_acc.instance_id%TYPE INDEX BY BINARY_INTEGER;
664 TYPE l_user_id_tbl_type IS TABLE OF csm_parties_acc.user_id%TYPE INDEX BY BINARY_INTEGER;
665 TYPE l_party_id_tbl_type IS TABLE OF csm_parties_acc.party_id%TYPE INDEX BY BINARY_INTEGER;
666 TYPE l_incident_id_tbl_type IS TABLE OF csm_incidents_all_acc.incident_id%TYPE INDEX BY BINARY_INTEGER;
667
668 l_instance_id_tbl l_instance_id_tbl_type;
669 l_incident_id_tbl l_incident_id_tbl_type;
670 l_user_id_tbl l_user_id_tbl_type;
671 l_parent_incident_id NUMBER;
672 l_sr_profile_value VARCHAR2(1) := NULL;
673
674 l_sqlerrno VARCHAR2(20);
675 l_sqlerrmsg VARCHAR2(2000);
676 l_error_msg VARCHAR2(3000);
677 l_return_status VARCHAR2(3000);
678 l_error_message VARCHAR2(3000);
679
680 /*
681 This cursor fetches SR history for the parties downloaded
682 */
683 CURSOR l_sr_hist_ins_csr
684 IS
685 SELECT ciab.incident_id
686 , ciia.user_id
687 , ciia.instance_id
688 FROM cs_incidents_all_b ciab
689 , cs_incident_statuses_b cisb
690 , csm_item_instances_acc ciia
691 , jtf_tasks_b jtb
692 WHERE ciab.customer_product_id = ciia.instance_id
693 AND ciab.incident_status_id = cisb.incident_status_id
694 AND cisb.close_flag = 'Y'
695 AND ciab.incident_id = jtb.source_object_id
696 AND jtb.source_object_type_code = 'SR'
697 and jtb.scheduled_start_date > (sysdate - NVL(fnd_profile.value_specific('CSF_M_HISTORY'),100))
698 AND ciia.user_id IN ( SELECT cpa.user_id
699 FROM csm_party_assignment cpa
700 WHERE cpa.deleted_flag ='N'
701 )
702 AND NOT EXISTS ( SELECT 1
703 FROM csm_service_history_acc csha
704 WHERE csha.user_id = ciia.user_id
705 AND csha.history_incident_id = ciab.incident_id
706 );
707
708
709 /*
710 This cursor fetches SR history for the parties downloaded
711 */
712 CURSOR l_sr_hist_del_csr
713 IS
714 SELECT csha.history_incident_id
715 , csha.user_id
716 , csha.instance_id
717 FROM csm_service_history_acc csha
718 WHERE csha.incident_id=1
719 AND NOT EXISTS ( SELECT 1
720 FROM csm_item_instances_acc ciia
721 WHERE ciia.user_id = csha.user_id
722 AND ciia.instance_id = csha.instance_id
723 );
724
725 BEGIN
726
727 CSM_UTIL_PKG.LOG('Entering CSM_SERVICE_HISTORY_EVENT_PKG.PROCESS_OWNER_HISTORY',
728 'CSM_SERVICE_HISTORY_EVENT_PKG.PROCESS_OWNER_HISTORY',FND_LOG.LEVEL_PROCEDURE);
729
730 l_parent_incident_id := 1;
731
732 l_sr_profile_value := fnd_profile.value_specific('CSM_SR_HIST_DWLD_PARTY');
733
734 IF l_sr_profile_value = 'Y' THEN
735
736
737 OPEN l_sr_hist_ins_csr;
738
739 LOOP
740
741 IF l_instance_id_tbl.COUNT > 0 THEN
742
743 l_instance_id_tbl.DELETE;
744
745 END IF;
746
747 IF l_incident_id_tbl.COUNT > 0 THEN
748
749 l_incident_id_tbl.DELETE;
750
751 END IF;
752
753 IF l_user_id_tbl.COUNT > 0 THEN
754
755 l_user_id_tbl.DELETE;
756
757 END IF;
758
759 FETCH l_sr_hist_ins_csr BULK COLLECT INTO l_incident_id_tbl,l_user_id_tbl,l_instance_id_tbl LIMIT 100;
760 EXIT WHEN l_incident_id_tbl.COUNT = 0;
761
762 IF l_incident_id_tbl.COUNT > 0 THEN
763
764 FOR i IN l_incident_id_tbl.FIRST..l_incident_id_tbl.LAST LOOP
765
766 --call the CSM_ACC_PKG to insert into csm_service_history_acc table
767
768 CSM_ACC_PKG.Insert_Acc
769 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
770 ,P_ACC_TABLE_NAME => g_acc_table_name
771 ,P_SEQ_NAME => g_seq_name
772 ,P_PK1_NAME => g_pk1_name
773 ,P_PK1_NUM_VALUE => l_parent_incident_id
774 ,P_PK2_NAME => g_pk2_name
775 ,P_PK2_NUM_VALUE => l_incident_id_tbl(i)
776 ,P_PK3_NAME => g_pk3_name
777 ,P_PK3_NUM_VALUE => l_instance_id_tbl(i)
778 ,p_USER_ID => l_user_id_tbl(i)
779 );
780
781 END LOOP;
782
783 FOR i IN l_incident_id_tbl.FIRST..l_incident_id_tbl.LAST LOOP
784
785 --call the CSM_ACC_PKG to insert into CSM_INCIDENTS_ALL_ACC table
786
787 CSM_ACC_PKG.Insert_Acc
788 ( P_PUBLICATION_ITEM_NAMES => g_incidents_pubi_name
789 ,P_ACC_TABLE_NAME => g_incidents_acc_table_name
790 ,P_SEQ_NAME => g_incidents_seq_name
791 ,P_PK1_NAME => g_incidents_pk1_name
792 ,P_PK1_NUM_VALUE => l_incident_id_tbl(i)
793 ,P_USER_ID => l_user_id_tbl(i)
794 );
795
796 END LOOP;
797
798 END IF;
799
800 -- commit after every 100 records
801
802 COMMIT;
803
804 END LOOP;
805
806 CLOSE l_sr_hist_ins_csr;
807
808 OPEN l_sr_hist_del_csr;
809
810 LOOP
811
812 IF l_instance_id_tbl.COUNT > 0 THEN
813
814 l_instance_id_tbl.DELETE;
815
816 END IF;
817
818 IF l_incident_id_tbl.COUNT > 0 THEN
819
820 l_incident_id_tbl.DELETE;
821
822 END IF;
823
824 IF l_user_id_tbl.COUNT > 0 THEN
825
826 l_user_id_tbl.DELETE;
827
828 END IF;
829
830 FETCH l_sr_hist_del_csr BULK COLLECT INTO l_incident_id_tbl,l_user_id_tbl,l_instance_id_tbl LIMIT 100;
831 EXIT WHEN l_incident_id_tbl.COUNT = 0;
832
833 IF l_incident_id_tbl.COUNT > 0 THEN
834
835 FOR i IN l_incident_id_tbl.FIRST..l_incident_id_tbl.LAST LOOP
836
837 --call the CSM_ACC_PKG to delete from csm_service_history_acc table
838
839 CSM_ACC_PKG.Delete_Acc
840 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
841 ,P_ACC_TABLE_NAME => g_acc_table_name
842 ,P_PK1_NAME => g_pk1_name
843 ,P_PK1_NUM_VALUE => l_parent_incident_id
844 ,P_PK2_NAME => g_pk2_name
845 ,P_PK2_NUM_VALUE => l_incident_id_tbl(i)
846 ,P_PK3_NAME => g_pk3_name
847 ,P_PK3_NUM_VALUE => l_instance_id_tbl(i)
848 ,p_USER_ID => l_user_id_tbl(i)
849 );
850
851 END LOOP;
852
853 FOR i IN l_incident_id_tbl.FIRST..l_incident_id_tbl.LAST LOOP
854
855 --call the CSM_ACC_PKG to Delete from CSM_INCIDENTS_ALL_ACC table
856
857 CSM_ACC_PKG.Delete_Acc
858 ( P_PUBLICATION_ITEM_NAMES => g_incidents_pubi_name
859 ,P_ACC_TABLE_NAME => g_incidents_acc_table_name
860 ,P_PK1_NAME => g_incidents_pk1_name
861 ,P_PK1_NUM_VALUE => l_incident_id_tbl(i)
862 ,P_USER_ID => l_user_id_tbl(i)
863 );
864
865 END LOOP;
866
867 END IF;
868
869 -- commit after every 100 records
870
871 COMMIT;
872
873 END LOOP;
874
875 CLOSE l_sr_hist_del_csr;
876
877 ELSE
878
879 CSM_UTIL_PKG.LOG('The Profile Option CSM: Allow Service Request History Download for Parties
880 is set to NO',FND_LOG.LEVEL_PROCEDURE);
881
882 END IF;
883
884 p_return_status := 'SUCCESS';
885 p_error_message := 'SR HISTORY Records are successfully processed';
886
887 CSM_UTIL_PKG.LOG('Leaving CSM_SERVICE_HISTORY_EVENT_PKG.PROCESS_OWNER_HISTORY',
888 'CSM_SERVICE_HISTORY_EVENT_PKG.PROCESS_OWNER_HISTORY',FND_LOG.LEVEL_EXCEPTION);
889
890
891 EXCEPTION WHEN OTHERS THEN
892 l_sqlerrno := to_char(SQLCODE);
893 l_sqlerrmsg := substr(SQLERRM, 1,2000);
894 p_return_status := 'ERROR';
895 p_error_message := l_sqlerrmsg;
896 l_error_msg :='Exception in PROCESS_OWNER_HISTORY ' || l_sqlerrno || ':' || l_sqlerrmsg;
897 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SERVICE_HISTORY_EVENT_PKG.PROCESS_OWNER_HISTORY',FND_LOG.LEVEL_EXCEPTION);
898 RAISE;
899 END PROCESS_OWNER_HISTORY;
900
901 END;