[Home] [Help]
PACKAGE BODY: APPS.BIV_DBI_COLLECTION_INIT
Source
1 package body biv_dbi_collection_init as
2 /* $Header: bivsrvcintb.pls 120.6 2005/11/10 06:25:04 kamsharm noship $ */
3
4 g_bis_setup_exception exception;
5 g_user_id number := fnd_global.user_id;
6 g_login_id number := fnd_global.login_id;
7 g_process_type varchar2(30) := 'INITIAL_LOAD';
8
9 function internal_wrapup
10 ( p_rowid in rowid
11 , x_error_message out nocopy varchar2 )
12 return number as
13
14 cursor c_wrapup is
15 select
16 success_flag
17 , activity_flag
18 , closed_flag
19 , backlog_flag
20 , resolution_flag
21 from
22 biv_dbi_collection_log
23 where
24 rowid = p_rowid
25 for update of success_flag;
26
27 l_success_flag varchar2(1);
28 l_activity_flag varchar2(1);
29 l_closed_flag varchar2(1);
30 l_backlog_flag varchar2(1);
31 l_resolution_flag varchar2(1);
32
33 begin
34
35 open c_wrapup;
36 fetch c_wrapup into l_success_flag
37 , l_activity_flag
38 , l_closed_flag
39 , l_backlog_flag
40 , l_resolution_flag;
41
42 if l_success_flag = 'Y' then
43 x_error_message := 'Internal wrapup called for completed initial load';
44 return -1;
45 end if;
46
47 if l_activity_flag = 'Y' and
48 l_closed_flag = 'Y' and
49 l_backlog_flag = 'Y' and
50 l_resolution_flag = 'Y' then
51
52 update biv_dbi_collection_log
53 set success_flag = 'Y'
54 , last_update_date = sysdate
55 , last_updated_by = g_user_id
56 , last_update_login = g_login_id
57 where current of c_wrapup;
58
59 bis_collection_utilities.put_line('Initial Load complete');
60
61 end if;
62
63 close c_wrapup;
64
65 return 0;
66
67 exception
68 when others then
69 x_error_message := sqlerrm;
70 return -1;
71
72 end internal_wrapup;
73
74 procedure setup
75 ( errbuf in out nocopy varchar2
76 , retcode in out nocopy varchar2
77 , p_load_to in varchar2 default fnd_date.date_to_canonical(sysdate)
78 , p_force in varchar2 default 'N'
79 ) as
80
81 l_exception exception;
82 l_error_message varchar2(4000);
83
84 l_log_rowid rowid;
85 l_process_type varchar2(30);
86 l_collect_from_date date;
87 l_collect_to_date date;
88 l_success_flag varchar2(1);
89 l_staging_flag varchar2(1);
90 l_activity_flag varchar2(1);
91 l_closed_flag varchar2(1);
92 l_backlog_flag varchar2(1);
93 l_resolution_flag varchar2(1);
94
95 l_biv_schema varchar2(100);
96 l_rowcount number;
97
98
99 begin
100 if not bis_collection_utilities.setup( 'BIV_DBI_COLLECT_INIT_SETUP' ) then
101 raise g_bis_setup_exception;
102 end if;
103
104 /* this is a temporary workaround to bad audit data cause by:
105 - bug 3050727 - fixed
106 */
107
108 if biv_dbi_collection_util.correct_bad_audit(l_error_message) <> 0 then
109 raise l_exception;
110 end if;
111
112 biv_dbi_collection_util.get_last_log( l_log_rowid
113 , l_process_type
114 , l_collect_from_date
115 , l_collect_to_date
116 , l_success_flag
117 , l_staging_flag
118 , l_activity_flag
119 , l_closed_flag
120 , l_backlog_flag
121 , l_resolution_flag
122 );
123
124 if nvl(l_success_flag,'Y') = 'N' then
125
126 if p_force = 'Y' then
127 l_success_flag := 'Y';
128 bis_collection_utilities.log('Last collection did not complete successfully, forcing new initial load');
129 end if;
130
131 end if;
132
133 if nvl(l_success_flag,'Y') = 'N' and
134 nvl(l_process_type,g_process_type) <> g_process_type then
135 l_error_message := 'Initial Load cannot run as there is an incomplete incremental load in progress';
136 raise l_exception;
137 end if;
138
139 if nvl(l_success_flag,'Y') = 'Y' then
140
141 if l_log_rowid is not null then
142 update biv_dbi_collection_log
143 set last_collection_flag = 'N'
144 , last_update_date = sysdate
145 , last_updated_by = g_user_id
146 , last_update_login = g_login_id
147 where rowid = l_log_rowid;
148 end if;
149
150 l_collect_from_date := to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),'mm/dd/yyyy');
151 if l_collect_from_date is null then
152 l_error_message := 'BIS_GLOBAL_START_DATE is not set';
153 raise l_exception;
154 end if;
155
156 if p_load_to is null then
157 l_error_message := 'p_load_to is a required parameter';
158 raise l_exception;
159 end if;
160 l_collect_to_date := fnd_date.canonical_to_date(p_load_to);
161 if l_collect_to_date > sysdate then
162 l_error_message := 'p_load_to must less than or equal to ' ||
163 fnd_date.date_to_displaydt(sysdate);
164 raise l_exception;
165 end if;
166
167 -- @@@@@@@@ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
168 -- the next line overrides the global start date retrieved from the
169 -- BIS_GLOBAL_START_DATE so that we can test different scenareos
170 -- l_collect_from_date := sysdate;
171 --
172 -- the next line override sysdate so we can test initial + incremental
173 -- load
174 -- l_collect_to_date := to_date('31-12-2002 23:59:59','dd-mm-yyyy hh24:mi:ss');
175 -- @@@@@@@@ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
176
177 if biv_dbi_collection_util.get_schema_name
178 (l_biv_schema, l_error_message) <> 0 then
179 raise l_exception;
180 end if;
181
182
183 -- Populating escalation staging table for initial loads
184 -- This staging table will be used in initial load programs of BIV summary tables
185 -- For marking escalated_flag and escalation date
186 IF( g_process_type = 'INITIAL_LOAD')
187 THEN
188
189 bis_collection_utilities.log('Starting Escalations Staging table population ');
190
191 bis_collection_utilities.log('Truncating table '||l_biv_schema||'.'||'BIV_DBI_ESCALATIONS_STG');
192
193 if biv_dbi_collection_util.truncate_table
194 (l_biv_schema, 'BIV_DBI_ESCALATIONS_STG', l_error_message) <> 0 then
195 raise l_exception;
196 end if;
197
198 bis_collection_utilities.log('Inserting rows into BIV_DBI_ESCALATIONS_STG');
199
200 insert into biv_dbi_escalations_stg
201 (
202 incident_id,
203 escalated_date_from,
204 escalated_date_to,
205 escalated_date,
206 de_escalated_same_day
207 )
208 select /*+ use_hash(tsk,trf) parallel(tsk) parallel(trf) */
209 trf.object_id incident_id
210 , trunc(tsk.actual_start_date) escalated_date_from
211 , trunc(nvl(tsk.actual_end_date,to_date('01-12-4712','DD-MM-YYYY'))) escalated_date_to
212 , tsk.actual_start_date escalated_date
213 , CASE WHEN trunc(tsk.actual_start_date) = trunc(nvl(tsk.actual_end_date,to_date('01-12-4712','DD-MM-YYYY')))
214 THEN
215 'Y'
216 ELSE
217 'N'
218 END de_escalated_same_day
219 from
220 jtf_tasks_b tsk
221 , jtf_task_references_b trf
222 where
223 trf.object_type_code = 'SR'
224 and trf.reference_code = 'ESC'
225 and tsk.task_type_id = 22
226 and tsk.task_id = trf.task_id
227 and tsk.task_id in (select task_id from (select min(task_id)task_id, object_id
228 from jtf_task_references_b
229 where reference_code = 'ESC'
230 and object_type_code = 'SR'
231 group by object_id));
232
233 l_rowcount := sql%rowcount;
234
235 bis_collection_utilities.log('Inserted ' || l_rowcount || ' rows');
236 commit;
237
238 BEGIN
239
240 fnd_stats.gather_table_stats(ownname => 'BIV'
241 ,tabname => 'BIV_DBI_ESCALATIONS_STG', percent => 10);
242 END;
243
244
245 END IF;
246
247 insert into biv_dbi_collection_log
248 ( last_collection_flag
249 , process_type
250 , collect_from_date
251 , collect_to_date
252 , success_flag
253 , staging_table_flag
254 , activity_flag
255 , closed_flag
256 , backlog_flag
257 , resolution_flag
258 , creation_date
259 , created_by
260 , last_update_date
261 , last_updated_by
262 , last_update_login
263 )
264 values
265 ( 'Y'
266 , g_process_type
267 , l_collect_from_date
268 , l_collect_to_date
269 , 'N'
270 , 'Y'
271 , 'N'
272 , 'N'
273 , 'N'
274 , 'N'
275 , sysdate
276 , g_user_id
277 , sysdate
278 , g_user_id
279 , g_login_id
280 );
281
282 bis_collection_utilities.log('Starting new Initial Load');
283 bis_collection_utilities.log('From ' || fnd_date.date_to_displaydt(l_collect_from_date),1);
284 bis_collection_utilities.log('To ' || fnd_date.date_to_displaydt(l_collect_to_date),1);
285
286 else
287
288 bis_collection_utilities.log('Resuming previous incomplete Initial Load');
289 bis_collection_utilities.log('From ' || fnd_date.date_to_displaydt(l_collect_from_date),1);
290 bis_collection_utilities.log('To ' || fnd_date.date_to_displaydt(l_collect_to_date),1);
291
292 end if;
293
294 commit;
295
296 bis_collection_utilities.wrapup( p_status => true
297 , p_period_from => l_collect_from_date
298 , p_period_to => l_collect_to_date
299 );
300
301 exception
302 when g_bis_setup_exception then
303 rollback;
304 errbuf := 'Error in BIS_COLLECTION_UTILITIES.Setup';
305 retcode := '2';
306
307 when others then
308 rollback;
309 if l_error_message is null then
310 l_error_message := substr(sqlerrm,1,4000);
311 end if;
312 bis_collection_utilities.log('Error:');
313 bis_collection_utilities.log(l_error_message,1);
314 bis_collection_utilities.wrapup( p_status => false
315 , p_message => l_error_message
316 , p_period_from => l_collect_from_date
317 , p_period_to => l_collect_to_date
318 );
319 errbuf := l_error_message;
320 retcode := '2';
321
322 end setup;
323
324 /* The procedure load_activity inserts data into the activity fact.*/
325
326 procedure load_activity
327 ( errbuf in out nocopy varchar2
328 , retcode in out nocopy varchar2) as
329
330 l_exception exception;
331 l_error_message varchar2(4000);
332
333 l_log_rowid rowid;
334 l_process_type varchar2(30);
335 l_collect_from_date date;
336 l_collect_to_date date;
337 l_success_flag varchar2(1);
338 l_staging_flag varchar2(1);
339 l_activity_flag varchar2(1);
340 l_closed_flag varchar2(1);
341 l_backlog_flag varchar2(1);
342 l_resolution_flag varchar2(1);
343
344 l_biv_schema varchar2(100);
345
346 l_timer number;
347 l_rowcount number;
348
349 l_missing_owner_group_id number := biv_dbi_collection_util.get_missing_owner_group_id;
350 l_missing_inventory_item_id number := biv_dbi_collection_util.get_missing_inventory_item_id;
351 l_missing_organization_id number := biv_dbi_collection_util.get_missing_organization_id;
352
353 begin
354
355 if not bis_collection_utilities.setup( 'BIV_DBI_COLLECT_INIT_ACTIVITY' ) then
356 raise g_bis_setup_exception;
357 end if;
358
359 biv_dbi_collection_util.get_last_log( l_log_rowid
360 , l_process_type
361 , l_collect_from_date
362 , l_collect_to_date
363 , l_success_flag
364 , l_staging_flag
365 , l_activity_flag
366 , l_closed_flag
367 , l_backlog_flag
368 , l_resolution_flag
369 );
370
371 if l_process_type <> g_process_type then
372 l_error_message := 'Activity process called for wrong process type';
373 raise l_exception;
374 end if;
375
376 if nvl(l_success_flag,'X') <> 'N' then
377 l_error_message := 'Activity process called for completed initial load';
378 raise l_exception;
379 end if;
380
381 if l_activity_flag = 'N' then
382
383 l_timer := dbms_utility.get_time;
384
385 if biv_dbi_collection_util.get_schema_name
386 (l_biv_schema, l_error_message) <> 0 then
387 raise l_exception;
388 end if;
389
390 bis_collection_utilities.log('Starting Activity Initial Load');
391 bis_collection_utilities.log('From ' || fnd_date.date_to_displaydt(l_collect_from_date),1);
392 bis_collection_utilities.log('To ' || fnd_date.date_to_displaydt(l_collect_to_date),1);
393
394 bis_collection_utilities.log('Truncating table '||l_biv_schema||'.'||'BIV_DBI_ACTIVITY_SUM_F');
395
396 if biv_dbi_collection_util.truncate_table
397 (l_biv_schema, 'BIV_DBI_ACTIVITY_SUM_F', l_error_message) <> 0 then
398 raise l_exception;
399 end if;
400
401 bis_collection_utilities.log('Inserting rows into BIV_DBI_ACTIVITY_SUM_F');
402
403 insert /*+ APPEND parallel(biv_dbi_activity_sum_f) */
404 into biv_dbi_activity_sum_f
405 ( activity_date
406 , incident_type_id
407 , inventory_item_id
408 , inv_organization_id
409 , incident_severity_id
410 , customer_id
411 , owner_group_id
412 , sr_creation_channel
413 , primary_flag
414 , first_opened_count
415 , reopened_count
416 , closed_count
417 , creation_date
418 , created_by
419 , last_update_date
420 , last_updated_by
421 , last_update_login
422 , incident_urgency_id
423 , incident_owner_id
424 , escalated_flag
425 )
426 select /*+ ordered full(a) use_hash(i) parallel(a) parallel(i) */
427 trunc(a.creation_date) report_date
428 , nvl(a.incident_type_id,-1) incident_type_id /* workaround bad data */
429 , nvl2( a.inventory_item_id+a.inv_organization_id
430 , a.inventory_item_id
431 , l_missing_inventory_item_id ) inventory_item_id
432 , nvl2( a.inventory_item_id+a.inv_organization_id
433 , a.inv_organization_id
434 , l_missing_organization_id ) inv_organization_id
435 , nvl(a.incident_severity_id,-1) incident_severity_id /* workaround bad data */
436 , nvl(i.customer_id,-1) customer_id /* workaround bad data */
437 , decode(a.group_type, 'RS_GROUP', nvl(a.group_id,l_missing_owner_group_id)
438 , l_missing_owner_group_id) owner_group_id
439 , nvl(i.sr_creation_channel,'-1') sr_creation_channel /* workaround bad data */
440 , 'Y'
441 , sum(case when a.change_incident_type_flag = 'Y'
442 and a.old_incident_type_id is null then 1 else 0 end) first_opened_count
443 , sum(case when a.change_status_flag = 'Y'
444 and a.status_flag = 'O'
445 and a.old_status_flag = 'C' then 1 else 0 end) reopened_count
446 , sum(case when a.change_status_flag = 'Y'
447 and a.status_flag = 'C' then 1 else 0 end) closed_count
448 , sysdate
449 , g_user_id
450 , sysdate
451 , g_user_id
452 , g_login_id
453 , nvl(a.incident_urgency_id, -1) incident_urgency_id
454 , decode(a.resource_type, 'RS_EMPLOYEE', nvl(a.incident_owner_id, -2)
455 , -2) incident_owner_id
456 , case when e.escalated_date <= a.creation_date then 'Y' else 'N' end escalated_flag
457 from
458 cs_incidents_audit_b a
459 , cs_incidents_all_b i
460 , biv_dbi_escalations_stg e
461 where
462 a.incident_id = i.incident_id
463 and a.creation_date >= l_collect_from_date
464 and a.creation_date+0 <= l_collect_to_date /* change here as workaround to db bug killing parallelism */
465 and 'Y' in ( a.change_status_flag
466 )
467 and nvl(a.updated_entity_code, 'SR_HEADER') IN ('SR_HEADER','SR_ESCALATION')
468 and e.incident_id(+) = i.incident_id
469 group by
470 trunc(a.creation_date)
471 , nvl(a.incident_type_id,-1) /* workaround bad data */
472 , nvl2( a.inventory_item_id+a.inv_organization_id
473 , a.inventory_item_id
474 , l_missing_inventory_item_id )
475 , nvl2( a.inventory_item_id+a.inv_organization_id
476 , a.inv_organization_id
477 , l_missing_organization_id )
478 , nvl(a.incident_severity_id,-1) /* workaround bad data */
479 , nvl(i.customer_id,-1) /* workaround bad data */
480 , decode(a.group_type, 'RS_GROUP', nvl(a.group_id,l_missing_owner_group_id)
481 , l_missing_owner_group_id)
482 , nvl(i.sr_creation_channel,'-1') /* workaround bad data */
483 , nvl(a.incident_urgency_id, -1)
484 , decode(a.resource_type, 'RS_EMPLOYEE', nvl(a.incident_owner_id, -2)
485 , -2)
486 , case when e.escalated_date <= a.creation_date then 'Y' else 'N' end;
487
488 l_rowcount := sql%rowcount;
489
490 bis_collection_utilities.log('Inserted ' || l_rowcount || ' rows');
491
492 update biv_dbi_collection_log
493 set activity_flag = 'Y'
494 , activity_count = l_rowcount
495 , activity_time = dbms_utility.get_time - l_timer
496 , activity_error_message = null
497 , last_update_date = sysdate
498 , last_updated_by = g_user_id
499 , last_update_login = g_login_id
500 where rowid = l_log_rowid;
501
502 bis_collection_utilities.log('Activity initial load complete');
503
504 else
505
506 bis_collection_utilities.log('Activity initial load already complete, skipping');
507
508 end if;
509
510 if internal_wrapup(l_log_rowid, l_error_message) <> 0 then
511 raise l_exception;
512 end if;
513
514 commit;
515
516 bis_collection_utilities.wrapup( p_status => true
517 , p_period_from => l_collect_from_date
518 , p_period_to => l_collect_to_date
519 , p_count => l_rowcount
520 );
521
522 exception
523 when g_bis_setup_exception then
524 rollback;
525 errbuf := 'Error in BIS_COLLECTION_UTILITIES.Setup';
526 retcode := '2';
527 biv_dbi_collection_util.set_log_error
528 ( p_rowid => l_log_rowid
529 , p_activity_error => errbuf
530 );
531 commit;
532
533 when others then
534 rollback;
535 if l_error_message is null then
536 l_error_message := substr(sqlerrm,1,4000);
537 end if;
538 biv_dbi_collection_util.set_log_error
539 ( p_rowid => l_log_rowid
540 , p_activity_error => l_error_message
541 );
542 commit;
543 bis_collection_utilities.wrapup( p_status => false
544 , p_message => l_error_message
545 , p_period_from => l_collect_from_date
546 , p_period_to => l_collect_to_date
547 );
548 errbuf := l_error_message;
549 retcode := '2';
550
551 end load_activity;
552
553 /* The procedure load_closed inserts data into the closure fact.*/
554
555 procedure load_closed
556 ( errbuf in out nocopy varchar2
557 , retcode in out nocopy varchar2) as
558
559 l_exception exception;
560 l_error_message varchar2(4000);
561
562 l_log_rowid rowid;
563 l_process_type varchar2(30);
564 l_collect_from_date date;
565 l_collect_to_date date;
566 l_success_flag varchar2(1);
567 l_staging_flag varchar2(1);
568 l_activity_flag varchar2(1);
569 l_closed_flag varchar2(1);
570 l_backlog_flag varchar2(1);
571 l_resolution_flag varchar2(1);
572
573 l_biv_schema varchar2(100);
574
575 l_timer number;
576 l_rowcount number;
577
578 l_missing_owner_group_id number := biv_dbi_collection_util.get_missing_owner_group_id;
579 l_missing_inventory_item_id number := biv_dbi_collection_util.get_missing_inventory_item_id;
580 l_missing_organization_id number := biv_dbi_collection_util.get_missing_organization_id;
581
582 begin
583
584 if not bis_collection_utilities.setup( 'BIV_DBI_COLLECT_INIT_CLOSED' ) then
585 raise g_bis_setup_exception;
586 end if;
587
588 biv_dbi_collection_util.get_last_log( l_log_rowid
589 , l_process_type
590 , l_collect_from_date
591 , l_collect_to_date
592 , l_success_flag
593 , l_staging_flag
594 , l_activity_flag
595 , l_closed_flag
596 , l_backlog_flag
597 , l_resolution_flag
598 );
599
600 if l_process_type <> g_process_type then
601 l_error_message := 'Closed process called for wrong process type';
602 raise l_exception;
603 end if;
604
605 if nvl(l_success_flag,'X') <> 'N' then
606 l_error_message := 'Closed process called for completed initial load';
607 raise l_exception;
608 end if;
609
610 if l_closed_flag = 'N' then
611
612 l_timer := dbms_utility.get_time;
613
614 if biv_dbi_collection_util.get_schema_name
615 (l_biv_schema, l_error_message) <> 0 then
616 raise l_exception;
617 end if;
618
619 bis_collection_utilities.log('Starting Closed Initial Load');
620 bis_collection_utilities.log('From ' || fnd_date.date_to_displaydt(l_collect_from_date),1);
621 bis_collection_utilities.log('To ' || fnd_date.date_to_displaydt(l_collect_to_date),1);
622
623 bis_collection_utilities.log('Truncating table '||l_biv_schema||'.'||'BIV_DBI_CLOSED_SUM_F');
624
625 if biv_dbi_collection_util.truncate_table
626 (l_biv_schema, 'BIV_DBI_CLOSED_SUM_F', l_error_message) <> 0 then
627 raise l_exception;
628 end if;
629
630 bis_collection_utilities.log('Inserting rows into BIV_DBI_CLOSED_SUM_F');
631
632 insert /*+ APPEND parallel(csf)*/
633 into biv_dbi_closed_sum_f csf
634 (
635 report_date
636 , incident_id
637 , incident_type_id
638 , inventory_item_id
639 , inv_organization_id
640 , incident_severity_id
641 , customer_id
642 , owner_group_id
643 , sr_creation_channel
644 , resolution_code
645 , reopened_date
646 , time_to_close
647 , creation_date
648 , created_by
649 , last_update_date
650 , last_updated_by
651 , last_update_login
652 , incident_urgency_id
653 , incident_owner_id
654 , escalated_flag
655 )
656 select /*+ parallel(r) parallel(e) use_hash(e) */
657 trunc(report_date),
658 r.incident_id,
659 nvl(incident_type_id,-1) incident_type_id, /* workaround bad data */
660 nvl2( inventory_item_id+inv_organization_id
661 , inventory_item_id
662 , l_missing_inventory_item_id ) inventory_item_id,
663 nvl2( inventory_item_id+inv_organization_id
664 , inv_organization_id
665 , l_missing_organization_id )inv_organization_id,
666 nvl(incident_severity_id,-1) incident_severity_id, /* workaround bad data */
667 nvl(customer_id,-1) customer_id,
668 owner_group_id,
669 nvl(sr_creation_channel,'-1') sr_creation_channel, /* workaround bad data */
670 nvl(resolution_code,'-1') resolution_code,
671 null,
672 time_to_close,
673 sysdate,
674 g_user_id,
675 sysdate,
676 g_user_id,
677 g_login_id,
678 nvl(incident_urgency_id, '-1') incident_urgency_id,
679 nvl(incident_owner_id,'-2') incident_owner_id,
680 case when e.escalated_date <= case
681 when close_date is null or close_date < incident_date then last_update_date
682 else close_date
683 end
684 then 'Y'
685 else 'N'
686 end escalated_flag
687 from
688 (
689 select /*+ use_hash(I) */
690 case
691 when i.close_date is null or i.close_date < i.incident_date then
692 i.last_update_date
693 else
694 i.close_date
695 end report_date,
696 i.incident_id,
697 a_incident_type_id incident_type_id,
698 a_inventory_item_id inventory_item_id,
699 a_inv_organization_id inv_organization_id,
700 a_incident_severity_id incident_severity_id,
701 i.customer_id,
702 decode(a_group_type, 'RS_GROUP', nvl(a_group_id,l_missing_owner_group_id), l_missing_owner_group_id) owner_group_id,
703 i.sr_creation_channel,
704 a_resolution_code resolution_code,
705 CASE
706 WHEN i.close_date is null or i.close_date < i.incident_date THEN i.last_update_date
707 ELSE a_close_date
708 END - i.incident_date time_to_close,
709 a_incident_urgency_id incident_urgency_id,
710 decode(a_resource_type, 'RS_EMPLOYEE', nvl(a_incident_owner_id,-2), -2) incident_owner_id,
711 i.last_update_date,
712 i.close_date,
713 i.incident_date
714 from
715 (select /*+ parallel(ciab) */
716 ciab.incident_id a_incident_id,
717 max(ciab.close_date) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_close_date,
718 max(ciab.incident_type_id) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_incident_type_id,
719 max(ciab.inventory_item_id) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_inventory_item_id,
720 max(ciab.inv_organization_id) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_inv_organization_id,
721 max(ciab.incident_severity_id) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_incident_severity_id,
722 max(ciab.group_type) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_group_type,
723 max(ciab.resource_type) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_resource_type,
724 max(ciab.resolution_code) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_resolution_code,
725 max(ciab.group_id) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_group_id,
726 max(ciab.incident_urgency_id) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_incident_urgency_id,
727 max(ciab.incident_owner_id) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_incident_owner_id
728 from cs_incidents_audit_b ciab
729 where 1=1
730 and ciab.status_flag = 'C' -- to pick up only those records that are closed.
731 -- After the SR is closed changes made to the SR will not get picked up in the fact until its reopened and re-closed.
732 and (ciab.old_status_flag ='O' or ciab.old_status_flag is null) /* using status_flag instead of old_closed_date as a workaround for bad data */
733 group by ciab.incident_id
734 ) a,
735 cs_incidents_all_b i
736 where i.incident_id = a.a_incident_id
737 and i.status_flag = 'C'
738 ) r
739 , biv_dbi_escalations_stg e
740 where
741 e.incident_id (+) = r.incident_id
742 and case
743 when r.close_date is null or r.close_date < r.incident_date then
744 r.last_update_date
745 else
746 r.close_date
747 end >= l_collect_from_date
748 and case
749 when r.close_date is null or r.close_date < r.incident_date then
750 r.last_update_date+0
751 else
752 r.close_date+0
753 end <= l_collect_to_date;
754
755 l_rowcount := sql%rowcount;
756
757 bis_collection_utilities.log('Inserted ' || l_rowcount || ' rows');
758
759 update biv_dbi_collection_log
760 set closed_flag = 'Y'
761 , closed_count = l_rowcount
762 , closed_time = dbms_utility.get_time - l_timer
763 , closed_error_message = null
764 , last_update_date = sysdate
765 , last_updated_by = g_user_id
766 , last_update_login = g_login_id
767 where rowid = l_log_rowid;
768
769 bis_collection_utilities.log('Closed initial load complete');
770
771 else
772
773 bis_collection_utilities.log('Closed initial load already complete, skipping');
774
775 end if;
776
777 if internal_wrapup(l_log_rowid, l_error_message) <> 0 then
778 raise l_exception;
779 end if;
780
781 commit;
782
783 bis_collection_utilities.wrapup( p_status => true
784 , p_period_from => l_collect_from_date
785 , p_period_to => l_collect_to_date
786 , p_count => l_rowcount
787 );
788
789 exception
790 when g_bis_setup_exception then
791 rollback;
792 errbuf := 'Error in BIS_COLLECTION_UTILITIES.Setup';
793 retcode := '2';
794 biv_dbi_collection_util.set_log_error
795 ( p_rowid => l_log_rowid
796 , p_closed_error => errbuf
797 );
798 commit;
799
800 when others then
801 rollback;
802 if l_error_message is null then
803 l_error_message := substr(sqlerrm,1,4000);
804 end if;
805 biv_dbi_collection_util.set_log_error
806 ( p_rowid => l_log_rowid
807 , p_closed_error => l_error_message
808 );
809 commit;
810 bis_collection_utilities.wrapup( p_status => false
811 , p_message => l_error_message
812 , p_period_from => l_collect_from_date
813 , p_period_to => l_collect_to_date
814 );
815 errbuf := l_error_message;
816 retcode := '2';
817
818 end load_closed;
819
820 /* The procedure load_backlog inserts data into the backlog fact.*/
821
822 procedure load_backlog
823 ( errbuf in out nocopy varchar2
824 , retcode in out nocopy varchar2) as
825
826 l_exception exception;
827 l_error_message varchar2(4000);
828
829 l_log_rowid rowid;
830 l_process_type varchar2(30);
831 l_collect_from_date date;
832 l_collect_to_date date;
833 l_success_flag varchar2(1);
834 l_staging_flag varchar2(1);
835 l_activity_flag varchar2(1);
836 l_closed_flag varchar2(1);
837 l_backlog_flag varchar2(1);
838 l_resolution_flag varchar2(1);
839
840 l_biv_schema varchar2(100);
841
842 l_timer number;
843 l_rowcount number;
844
845 l_missing_owner_group_id number := biv_dbi_collection_util.get_missing_owner_group_id;
846 l_missing_inventory_item_id number := biv_dbi_collection_util.get_missing_inventory_item_id;
847 l_missing_organization_id number := biv_dbi_collection_util.get_missing_organization_id;
848
849 l_max_date date := to_date('4712/12/31','yyyy/mm/dd');
850
851 begin
852
853 if not bis_collection_utilities.setup( 'BIV_DBI_COLLECT_INIT_BACKLOG' ) then
854 raise g_bis_setup_exception;
855 end if;
856
857 biv_dbi_collection_util.get_last_log( l_log_rowid
858 , l_process_type
859 , l_collect_from_date
860 , l_collect_to_date
861 , l_success_flag
862 , l_staging_flag
863 , l_activity_flag
864 , l_closed_flag
865 , l_backlog_flag
866 , l_resolution_flag
867 );
868
869 if l_process_type <> g_process_type then
870 l_error_message := 'Backlog process called for wrong process type';
871 raise l_exception;
872 end if;
873
874 if nvl(l_success_flag,'X') <> 'N' then
875 l_error_message := 'Backlog process called for completed initial load';
876 raise l_exception;
877 end if;
878
879 if l_backlog_flag = 'N' then
880
881 l_timer := dbms_utility.get_time;
882
883 if biv_dbi_collection_util.get_schema_name
884 (l_biv_schema, l_error_message) <> 0 then
885 raise l_exception;
886 end if;
887
888 bis_collection_utilities.log('Starting Backlog Initial Load');
889 bis_collection_utilities.log('From ' || fnd_date.date_to_displaydt(l_collect_from_date),1);
890 bis_collection_utilities.log('To ' || fnd_date.date_to_displaydt(l_collect_to_date),1);
891
892 bis_collection_utilities.log('Truncating table '||l_biv_schema||'.'||'BIV_DBI_BACKLOG_SUM_F');
893
894 if biv_dbi_collection_util.truncate_table
895 (l_biv_schema, 'BIV_DBI_BACKLOG_SUM_F', l_error_message) <> 0 then
896 raise l_exception;
897 end if;
898
899 bis_collection_utilities.log('Inserting rows into BIV_DBI_BACKLOG_SUM_F');
900
901 insert /*+ APPEND parallel(biv_dbi_backlog_sum_f) */
902 first
903 when status_flag = 'O' then
904 into biv_dbi_backlog_sum_f
905 ( incident_id
906 , backlog_date_from
907 , backlog_date_to
908 , incident_type_id
909 , inventory_item_id
910 , inv_organization_id
911 , incident_severity_id
912 , incident_status_id
913 , customer_id
914 , owner_group_id
915 , sr_creation_channel
916 , incident_date
917 , escalated_date
918 , unowned_date
919 , creation_date
920 , created_by
921 , last_update_date
922 , last_updated_by
923 , last_update_login
924 , resolved_flag
925 , incident_resolved_date
926 , escalated_flag
927 , incident_urgency_id
928 , incident_owner_id
929 )
930 values
931 ( incident_id
932 , date_from
933 , date_to
934 , incident_type_id
935 , inventory_item_id
936 , inv_organization_id
937 , incident_severity_id
938 , incident_status_id
939 , customer_id
940 , owner_group_id
941 , sr_creation_channel
942 , incident_date
943 , escalated_date
944 , unowned_date
945 , sysdate
946 , g_user_id
947 , sysdate
948 , g_user_id
949 , g_login_id
950 , resolved_flag
951 , incident_resolved_date
952 , escalated_flag
953 , incident_urgency_id
954 , incident_owner_id
955 )
956 select /*+ parallel(b) */
957 status_flag
958 , incident_id
959 , greatest(audit_date,trunc(l_collect_from_date)) date_from
960 , lead(audit_date-1,1,l_max_date) over (partition by incident_id order by audit_date) date_to
961 , incident_type_id
962 , inventory_item_id
963 , inv_organization_id
964 , incident_severity_id
965 , incident_status_id
966 , customer_id
967 , owner_group_id
968 , sr_creation_channel
969 , incident_date
970 , escalated_date
971 , unowned_date
972 , resolved_flag
973 , incident_resolved_date
974 , escalated_flag
975 , incident_urgency_id
976 , incident_owner_id
977 from
978 (
979 select /*+ parallel(a) parallel(i) parallel(e) USE_HASH(A,I,E) */
980 a.incident_id
981 , a.audit_date
982 , nvl(a.status_flag,'O') status_flag /* workaround bad data */
983 , nvl(a.incident_type_id,-1) incident_type_id /* workaround bad data */
984 , nvl2( a.inventory_item_id+a.inv_organization_id
985 , a.inventory_item_id
986 , l_missing_inventory_item_id ) inventory_item_id
987 , nvl2( a.inventory_item_id+a.inv_organization_id
988 , a.inv_organization_id
989 , l_missing_organization_id )inv_organization_id
990 , nvl(a.incident_status_id,-1) incident_status_id /* workaround bad data */
991 , nvl(a.incident_severity_id,-1) incident_severity_id /* workaround bad data */
992 , nvl(i.customer_id,-1) customer_id /* workaround bad data */
993 , a.owner_group_id
994 , nvl(i.sr_creation_channel,'-1') sr_creation_channel /* workaround bad data */
995 , a.unowned_date
996 , i.incident_date
997 , case when a.audit_date >= e.escalated_date_from and
998 a.audit_date < e.escalated_date_to then
999 e.escalated_date
1000 else
1001 null
1002 end escalated_date
1003 , case when
1004 (a.incident_resolved_date is not null
1005 and trunc(a.incident_resolved_date) <= a.audit_date)
1006 then
1007 'Y'
1008 else
1009 'N'
1010 end resolved_flag
1011 , a.incident_resolved_date
1012 , case when e.escalated_date_from <= a.audit_date then 'Y' else 'N' end escalated_flag
1013 , nvl(a.incident_urgency_id, -1) incident_urgency_id
1014 , nvl(a.incident_owner_id, -2) incident_owner_id
1015 from
1016 (
1017 /* this query extracts just the last row for each
1018 incident in any day
1019 */
1020 select /*+ parallel(a) */
1021 incident_id
1022 , audit_date_for_day audit_date
1023 , status_flag
1024 , incident_status_id
1025 , incident_type_id
1026 , incident_severity_id
1027 , owner_group_id
1028 , inventory_item_id
1029 , inv_organization_id
1030 , unowned_date
1031 , incident_resolved_date
1032 , incident_urgency_id
1033 , incident_owner_id
1034 from
1035 (
1036 /*
1037 this query identifies all audit rows the audit table
1038 that have changes that may be of interest, and identifies
1039 the last row for each incident on any day
1040 */
1041 select /*+ parallel(a) full(a) */
1042 a.incident_id
1043 , a.incident_date
1044 , a.status_flag
1045 , a.incident_status_id
1046 , a.incident_type_id
1047 , a.incident_severity_id
1048 , decode(a.group_type, 'RS_GROUP', nvl(a.group_id,l_missing_owner_group_id)
1049 , l_missing_owner_group_id) owner_group_id
1050 , a.inventory_item_id
1051 , a.inv_organization_id
1052 , decode( a.incident_owner_id
1053 , null
1054 , nvl(a.owner_assigned_time,nvl(a.incident_date,a.creation_date))
1055 -- based on bug 2993526, if the incident is created
1056 -- with no owner, the initial audit row will have
1057 -- NULL in owner_assigned_time - intended behavior
1058 -- so we need to take incident_date from audit row
1059 -- if for any reason (bad data) this is null, then we take
1060 -- creation_date from row.
1061 , null ) unowned_date
1062 , incident_resolved_date incident_resolved_date
1063 , decode( a.incident_audit_id
1064 , last_value(a.incident_audit_id)
1065 over ( partition by a.incident_id, trunc(a.creation_date)
1066 -- modified order by based on conclusions found in bug 3524935
1067 order by decode(a.old_status_flag,null,1,2)
1068 , a.creation_date
1069 , a.incident_audit_id
1070 rows between unbounded preceding and unbounded following )
1071 , 'Y'
1072 , 'N' ) last_row_for_day
1073 , trunc(a.creation_date) audit_date_for_day
1074 , a.incident_urgency_id
1075 , decode(a.resource_type, 'RS_EMPLOYEE', nvl(a.incident_owner_id, -2)
1076 , -2) incident_owner_id
1077 from
1078 cs_incidents_audit_b a
1079 where
1080 a.creation_date >= l_collect_from_date
1081 and nvl(a.updated_entity_code, 'SR_HEADER') IN ('SR_HEADER','SR_ESCALATION')
1082 and ( 'Y' in ( a.change_incident_status_flag
1083 , a.change_incident_type_flag
1084 , a.change_incident_severity_flag
1085 , a.change_inventory_item_flag
1086 , a.change_inv_organization_flag
1087 , a.change_status_flag
1088 , a.change_incident_owner_flag
1089 , a.change_group_flag
1090 , a.change_incident_urgency_flag
1091 ) OR a.old_incident_resolved_date <> a.incident_resolved_date )
1092 )
1093 where
1094 last_row_for_day = 'Y'
1095 ----------------------------
1096 union all
1097 ----------------------------
1098 /*
1099 this query extracts the state of incidents that
1100 form the opening backlog based on the their first
1101 change since the global start date or their current
1102 value if no changes since global start date
1103 */
1104 select
1105 incident_id
1106 , audit_date
1107 , status_flag
1108 , incident_status_id
1109 , incident_type_id
1110 , incident_severity_id
1111 , owner_group_id
1112 , inventory_item_id
1113 , inv_organization_id
1114 , unowned_date
1115 , incident_resolved_date
1116 , incident_urgency_id
1117 , incident_owner_id
1118 from
1119 (
1120 select
1121 incident_id
1122 , decode( row_number()
1123 over( partition by incident_id
1124 -- modified order by based on conclusions found in bug 3524935
1125 order by source
1126 , creation_date
1127 , incident_audit_id )
1128 , 1, 'Y', 'N') first_for_incident
1129 , trunc(l_collect_from_date) -1 audit_date
1130 , status_flag
1131 , incident_status_id
1132 , incident_type_id
1133 , incident_severity_id
1134 , nvl(owner_group_id, l_missing_owner_group_id) owner_group_id
1135 , nvl2( inventory_item_id+inv_organization_id
1136 , inventory_item_id
1137 , l_missing_inventory_item_id ) inventory_item_id
1138 , nvl2( inventory_item_id+inv_organization_id
1139 , inv_organization_id
1140 , l_missing_organization_id )inv_organization_id
1141 , unowned_date
1142 , incident_resolved_date
1143 , incident_urgency_id
1144 , incident_owner_id
1145 from
1146 (
1147 select /*+ parallel(a) full(a) */
1148 a.incident_id
1149 , 1 source
1150 , a.creation_date
1151 , a.incident_audit_id
1152 , decode( a.change_status_flag
1153 , 'Y'
1154 , a.old_status_flag
1155 , a.status_flag ) status_flag
1156 , decode( a.change_incident_status_flag
1157 , 'Y'
1158 , a.old_incident_status_id
1159 , a.incident_status_id ) incident_status_id
1160 , decode( a.change_incident_type_flag
1161 , 'Y'
1162 , a.old_incident_type_id
1163 , a.incident_type_id ) incident_type_id
1164 , decode( a.change_incident_severity_flag
1165 , 'Y'
1166 , a.old_incident_severity_id
1167 , a.incident_severity_id ) incident_severity_id
1168 , decode( a.change_group_flag
1169 , 'Y'
1170 , decode(a.old_group_type,'RS_GROUP',a.old_group_id,null)
1171 , decode(a.group_type,'RS_GROUP',a.group_id,null) ) owner_group_id
1172 , decode( a.change_inventory_item_flag
1173 , 'Y'
1174 , a.old_inventory_item_id
1175 , a.inventory_item_id ) inventory_item_id
1176 , decode( a.change_inv_organization_flag
1177 , 'Y'
1178 , a.old_inv_organization_id
1179 , a.inv_organization_id ) inv_organization_id
1180 , decode( a.change_incident_owner_flag
1181 , 'Y'
1182 -- based on bug 2993526, if the incident is created
1183 -- with no owner, the initial audit row will have
1184 -- NULL in owner_assigned_time - intended behavior
1185 -- so we need to take incident_date from audit row
1186 -- if for any reason (bad data) this is null, then we take
1187 -- creation_date from row.
1188 , decode( a.old_incident_owner_id
1189 , null
1190 , nvl(a.old_owner_assigned_time,nvl(a.incident_date,a.creation_date))
1191 , null )
1192 , decode( a.incident_owner_id
1193 , null
1194 , nvl(a.owner_assigned_time,nvl(a.incident_date,a.creation_date))
1195 , null ) ) unowned_date
1196 , case when a.old_incident_resolved_date <> a.incident_resolved_date
1197 then a.old_incident_resolved_date
1198 else a.incident_resolved_date end incident_resolved_date
1199 , decode( a.change_incident_urgency_flag
1200 , 'Y'
1201 , a.old_incident_urgency_id
1202 , a.incident_urgency_id ) incident_urgency_id
1203 , decode( a.change_incident_owner_flag
1204 , 'Y'
1205 , decode(a.old_resource_type, 'RS_EMPLOYEE', nvl(a.old_incident_owner_id, -2) , -2)
1206 , decode(a.resource_type, 'RS_EMPLOYEE', nvl(a.incident_owner_id, -2) , -2) ) incident_owner_id
1207 from
1208 cs_incidents_audit_b a
1209 where
1210 a.creation_date >= l_collect_from_date
1211 and nvl(a.updated_entity_code, 'SR_HEADER') IN ('SR_HEADER','SR_ESCALATION')
1212 and a.incident_date < l_collect_from_date
1213 and ( 'Y' in ( a.change_incident_status_flag
1214 , a.change_incident_type_flag
1215 , a.change_incident_severity_flag
1216 , a.change_inventory_item_flag
1217 , a.change_inv_organization_flag
1218 , a.change_status_flag
1219 , a.change_incident_owner_flag
1220 , a.change_incident_urgency_flag
1221 , a.change_group_flag
1222 ) or a.old_incident_resolved_date <> a.incident_resolved_date )
1223 union all
1224 select /*+ parallel(i) full(i) */
1225 i.incident_id
1226 , 2 source
1227 , l_collect_from_date -1
1228 , 1
1229 , i.status_flag
1230 , i.incident_status_id
1231 , i.incident_type_id
1232 , i.incident_severity_id
1233 , decode(i.group_type,'RS_GROUP',i.owner_group_id,null) owner_group_id
1234 , i.inventory_item_id
1235 , i.inv_organization_id
1236 , decode( i.incident_owner_id
1237 , null
1238 , nvl(i.owner_assigned_time,i.incident_date)
1239 -- based on bug 2993526, if the incident is created
1240 -- with no owner, the initial audit row will have
1241 -- NULL in owner_assigned_time - intended behavior
1242 -- so we need to take incident_date.
1243 , null ) unowned_date
1244 , incident_resolved_date incident_resolved_date
1245 , i.incident_urgency_id
1246 , decode(i.resource_type, 'RS_EMPLOYEE', nvl(i.incident_owner_id, -2)
1247 , -2) incident_owner_id
1248 from
1249 cs_incidents_all_b i
1250 where
1251 i.incident_date < l_collect_from_date
1252 -- modified - this is not logical, unless it's 'O' it will be ingored anyway!
1253 and i.status_flag = 'O'
1254 )
1255 )
1256 where
1257 first_for_incident = 'Y'
1258 and status_flag = 'O'
1259 ) a
1260 , cs_incidents_all_b i
1261 , ( select /*+ parallel(stg) */ * from biv_dbi_escalations_stg stg
1262 where de_escalated_same_day = 'N'
1263 ) e
1264 where
1265 a.incident_id = i.incident_id
1266 and e.incident_id(+) = a.incident_id
1267 and a.audit_date <= l_collect_to_date
1268 ) b;
1269
1270 l_rowcount := sql%rowcount;
1271
1272 bis_collection_utilities.log('Inserted ' || l_rowcount || ' rows');
1273
1274 update biv_dbi_collection_log
1275 set backlog_flag = 'Y'
1276 , backlog_count = l_rowcount
1277 , backlog_time = dbms_utility.get_time - l_timer
1278 , backlog_error_message = null
1279 , last_update_date = sysdate
1280 , last_updated_by = g_user_id
1281 , last_update_login = g_login_id
1282 where rowid = l_log_rowid;
1283
1284 bis_collection_utilities.log('Backlog initial load complete');
1285
1286 else
1287
1288 bis_collection_utilities.log('Backlog initial load already complete, skipping');
1289
1290 end if;
1291
1292 if internal_wrapup(l_log_rowid, l_error_message) <> 0 then
1293 raise l_exception;
1294 end if;
1295
1296 commit;
1297
1298 bis_collection_utilities.wrapup( p_status => true
1299 , p_period_from => l_collect_from_date
1300 , p_period_to => l_collect_to_date
1301 , p_count => l_rowcount
1302 );
1303
1304 exception
1305 when g_bis_setup_exception then
1306 rollback;
1307 errbuf := 'Error in BIS_COLLECTION_UTILITIES.Setup';
1308 retcode := '2';
1309 biv_dbi_collection_util.set_log_error
1310 ( p_rowid => l_log_rowid
1311 , p_backlog_error => errbuf
1312 );
1313 commit;
1314
1315 when others then
1316 rollback;
1317 if l_error_message is null then
1318 l_error_message := substr(sqlerrm,1,4000);
1319 end if;
1320 biv_dbi_collection_util.set_log_error
1321 ( p_rowid => l_log_rowid
1322 , p_backlog_error => l_error_message
1323 );
1324 commit;
1325 bis_collection_utilities.wrapup( p_status => false
1326 , p_message => l_error_message
1327 , p_period_from => l_collect_from_date
1328 , p_period_to => l_collect_to_date
1329 );
1330 errbuf := l_error_message;
1331 retcode := '2';
1332
1333 end load_backlog;
1334
1335 /* The procedure load_resolution inserts data into the resolution fact.*/
1336
1337 procedure load_resolved
1338 ( errbuf in out nocopy varchar2
1339 , retcode in out nocopy varchar2) as
1340
1341 l_exception exception;
1342 l_error_message varchar2(4000);
1343
1344 l_log_rowid rowid;
1345 l_process_type varchar2(30);
1346 l_collect_from_date date;
1347 l_collect_to_date date;
1348 l_success_flag varchar2(1);
1349 l_staging_flag varchar2(1);
1350 l_activity_flag varchar2(1);
1351 l_closed_flag varchar2(1);
1352 l_backlog_flag varchar2(1);
1353 l_resolution_flag varchar2(1);
1354
1355 l_biv_schema varchar2(100);
1356
1357 l_timer number;
1358 l_rowcount number;
1359
1360 l_missing_owner_group_id number := biv_dbi_collection_util.get_missing_owner_group_id;
1361 l_missing_inventory_item_id number := biv_dbi_collection_util.get_missing_inventory_item_id;
1362 l_missing_organization_id number := biv_dbi_collection_util.get_missing_organization_id;
1363
1364 begin
1365
1366 if not bis_collection_utilities.setup( 'BIV_DBI_COLLECT_INIT_RESOLUTION' ) then
1367 raise g_bis_setup_exception;
1368 end if;
1369
1370 biv_dbi_collection_util.get_last_log( l_log_rowid
1371 , l_process_type
1372 , l_collect_from_date
1373 , l_collect_to_date
1374 , l_success_flag
1375 , l_staging_flag
1376 , l_activity_flag
1377 , l_closed_flag
1378 , l_backlog_flag
1379 , l_resolution_flag
1380 );
1381
1382 if l_process_type <> g_process_type then
1383 l_error_message := 'Resolution process called for wrong process type';
1384 raise l_exception;
1385 end if;
1386
1387 if nvl(l_success_flag,'X') <> 'N' then
1388 l_error_message := 'Resolution process called for completed initial load';
1389 raise l_exception;
1390 end if;
1391
1392 if l_resolution_flag = 'N' then
1393
1394 l_timer := dbms_utility.get_time;
1395
1396 if biv_dbi_collection_util.get_schema_name
1397 (l_biv_schema, l_error_message) <> 0 then
1398 raise l_exception;
1399 end if;
1400
1401 bis_collection_utilities.log('Starting Resolution Initial Load');
1402 bis_collection_utilities.log('From ' || fnd_date.date_to_displaydt(l_collect_from_date),1);
1403 bis_collection_utilities.log('To ' || fnd_date.date_to_displaydt(l_collect_to_date),1);
1404
1405 bis_collection_utilities.log('Truncating table '||l_biv_schema||'.'||'BIV_DBI_RESOLUTION_SUM_F');
1406
1407 if biv_dbi_collection_util.truncate_table
1408 (l_biv_schema, 'BIV_DBI_RESOLUTION_SUM_F', l_error_message) <> 0 then
1409 raise l_exception;
1410 end if;
1411
1412 bis_collection_utilities.log('Inserting rows into BIV_DBI_RESOLUTION_SUM_F');
1413
1414 insert /*+ APPEND parallel(rsf)*/
1415 into biv_dbi_resolution_sum_f rsf
1416 (
1417 report_date
1418 , incident_id
1419 , incident_type_id
1420 , inventory_item_id
1421 , inv_organization_id
1422 , incident_severity_id
1423 , customer_id
1424 , owner_group_id
1425 , sr_creation_channel
1426 , resolution_code
1427 , time_to_resolution
1428 , escalated_flag
1429 , incident_urgency_id
1430 , incident_owner_id
1431 , creation_date
1432 , created_by
1433 , last_update_date
1434 , last_updated_by
1435 , last_update_login
1436 , respond_on_date
1437 , respond_by_date
1438 , resolve_by_date
1439 , incident_date
1440 , contract_number
1441 )
1442 select /*+ parallel(r) parallel(e) use_hash(e) */
1443 CASE WHEN incident_resolved_date < incident_date
1444 THEN last_update_date
1445 ELSE incident_resolved_date
1446 END report_date,
1447 r.incident_id,
1448 incident_type_id,
1449 nvl2( inventory_item_id+inv_organization_id
1450 , inventory_item_id
1451 , l_missing_inventory_item_id ) inventory_item_id,
1452 nvl2( inventory_item_id+inv_organization_id
1453 , inv_organization_id
1454 , l_missing_organization_id )inv_organization_id,
1455 incident_severity_id,
1456 nvl(customer_id,-1) customer_id,
1457 owner_group_id,
1458 nvl(sr_creation_channel,'-1') sr_creation_channel,
1459 nvl(resolution_code,'-1') resolution_code,
1460 CASE WHEN (incident_resolved_date IS NOT NULL)
1461 THEN
1462 case
1463 when incident_resolved_date < incident_date then
1464 last_update_date
1465 else
1466 incident_resolved_date
1467 end
1468 ELSE
1469 NULL
1470 END - incident_date time_to_resolution,
1471 case when e.escalated_date <= case
1472 when incident_resolved_date < r.incident_date then last_update_date
1473 else incident_resolved_date
1474 end
1475 then 'Y'
1476 else 'N'
1477 end escalated_flag,
1478 nvl(incident_urgency_id, '-1') incident_urgency_id,
1479 nvl(incident_owner_id,'-2') incident_owner_id,
1480 sysdate,
1481 g_user_id,
1482 sysdate,
1483 g_user_id,
1484 g_login_id,
1485 respond_on_date,
1486 respond_by_date,
1487 resolve_by_date,
1488 incident_date,
1489 contract_number
1490 from
1491 (
1492 select /*+ use_hash(I) */
1493 i.incident_id,
1494 CASE
1495 WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN
1496 CASE WHEN(a.a_incident_resolved_date < i.incident_date) then i.incident_date
1497 -- WHEN(a.a_incident_resolved_date > i.close_date ) then i.close_date
1498 ELSE a.a_incident_resolved_date
1499 END
1500 /* From 8.0 SR's that are resolved only are taken into the resolution fact.
1501 i.e. all SR's that are closed are not pulled into the resolution fact until they have been resolved.
1502 WHEN (i.status_flag = 'C') THEN
1503 case
1504 when i.close_date is null or i.close_date < i.incident_date then
1505 i.last_update_date
1506 else
1507 i.close_date
1508 end
1509 ELSE NULL */
1510 END incident_resolved_date,
1511 CASE
1512 WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_incident_type_id
1513 ELSE incident_type_id
1514 END incident_type_id,
1515 CASE
1516 WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_inventory_item_id
1517 ELSE inventory_item_id
1518 END inventory_item_id,
1519 CASE
1520 WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_inv_organization_id
1521 ELSE inv_organization_id
1522 END inv_organization_id,
1523 CASE
1524 WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_incident_severity_id
1525 ELSE incident_severity_id
1526 END incident_severity_id,
1527 i.customer_id,
1528 CASE
1529 WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN
1530 decode(a_group_type, 'RS_GROUP', nvl(a_group_id,l_missing_owner_group_id), l_missing_owner_group_id)
1531 ELSE decode(group_type, 'RS_GROUP', nvl(owner_group_id,l_missing_owner_group_id), l_missing_owner_group_id)
1532 END owner_group_id,
1533 i.sr_creation_channel,
1534 CASE
1535 WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_resolution_code
1536 ELSE resolution_code
1537 END resolution_code,
1538 i.last_update_date,
1539 CASE
1540 WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_incident_urgency_id
1541 ELSE incident_urgency_id
1542 END incident_urgency_id,
1543 CASE
1544 WHEN(i.incident_resolved_date = a.a_incident_resolved_date)
1545 THEN decode(a_resource_type, 'RS_EMPLOYEE', nvl(a_incident_owner_id,-2), -2)
1546 ELSE decode(resource_type, 'RS_EMPLOYEE', nvl(incident_owner_id,-2), -2)
1547 END incident_owner_id,
1548 i.inc_responded_by_date respond_on_date,
1549 i.obligation_date respond_by_date,
1550 i.expected_resolution_date resolve_by_date,
1551 i.incident_date,
1552 i.contract_number contract_number
1553 from
1554 (select /*+ parallel(CS_INCIDENTS_AUDIT_B) */
1555 ciab.incident_id a_incident_id,
1556 max(ciab.incident_resolved_date) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_incident_resolved_date,
1557 max(ciab.incident_type_id) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_incident_type_id,
1558 max(ciab.inventory_item_id) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_inventory_item_id,
1559 max(ciab.inv_organization_id) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_inv_organization_id,
1560 max(ciab.incident_severity_id) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_incident_severity_id,
1561 max(ciab.group_type) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_group_type,
1562 max(ciab.resource_type) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_resource_type,
1563 max(ciab.resolution_code) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_resolution_code,
1564 max(ciab.group_id) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_group_id,
1565 max(ciab.incident_urgency_id) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_incident_urgency_id,
1566 max(ciab.incident_owner_id) keep (dense_rank last order by ciab.creation_date, ciab.incident_audit_id) a_incident_owner_id
1567 from cs_incidents_audit_b ciab
1568 where 1=1
1569 and ciab.INCIDENT_RESOLVED_DATE is not null
1570 and (ciab.old_status_flag = 'O' or ciab.old_status_flag is null)
1571 /* and INCIDENT_RESOLVED_DATE <> nvl(OLD_INCIDENT_RESOLVED_DATE,INCIDENT_RESOLVED_DATE+1)
1572 removed the where clause in 8.0 so that the latest attibutes are picked into the Resolution fact until the SR is closed.
1573 After the SR is closed changes made to the SR will not get picked up in the fact until its reopened and re-resolved.*/
1574 group by ciab.incident_id
1575 ) a,
1576 cs_incidents_all_b i
1577 where i.incident_id = a.a_incident_id (+)
1578 ) r
1579 , biv_dbi_escalations_stg e
1580 where
1581 e.incident_id (+) = r.incident_id
1582 and ( incident_resolved_date IS NOT NULL
1583 AND
1584 (
1585 case
1586 when incident_resolved_date < incident_date then
1587 last_update_date
1588 else
1589 incident_resolved_date
1590 end >= l_collect_from_date
1591 and case
1592 when incident_resolved_date < incident_date then
1593 last_update_date+0
1594 else
1595 incident_resolved_date+0
1596 end <= l_collect_to_date
1597 )
1598 );
1599 l_rowcount := sql%rowcount;
1600
1601 bis_collection_utilities.log('Inserted ' || l_rowcount || ' rows');
1602
1603 update biv_dbi_collection_log
1604 set resolution_flag = 'Y'
1605 , resolution_count = l_rowcount
1606 , resolution_time = dbms_utility.get_time - l_timer
1607 , resolution_error_message = null
1608 , last_update_date = sysdate
1609 , last_updated_by = g_user_id
1610 , last_update_login = g_login_id
1611 where rowid = l_log_rowid;
1612
1613 bis_collection_utilities.log('Resolution initial load complete');
1614
1615 else
1616
1617 bis_collection_utilities.log('Resolution initial load already complete, skipping');
1618
1619 end if;
1620
1621 if internal_wrapup(l_log_rowid, l_error_message) <> 0 then
1622 raise l_exception;
1623 end if;
1624
1625 commit;
1626
1627 bis_collection_utilities.wrapup( p_status => true
1628 , p_period_from => l_collect_from_date
1629 , p_period_to => l_collect_to_date
1630 , p_count => l_rowcount
1631 );
1632
1633 exception
1634 when g_bis_setup_exception then
1635 rollback;
1636 errbuf := 'Error in BIS_COLLECTION_UTILITIES.Setup';
1637 retcode := '2';
1638 biv_dbi_collection_util.set_log_error
1639 ( p_rowid => l_log_rowid
1640 , p_resolution_error => errbuf
1641 );
1642 commit;
1643
1644 when others then
1645 rollback;
1646 if l_error_message is null then
1647 l_error_message := substr(sqlerrm,1,4000);
1648 end if;
1649 biv_dbi_collection_util.set_log_error
1650 ( p_rowid => l_log_rowid
1651 , p_resolution_error => l_error_message
1652 );
1653 commit;
1654 bis_collection_utilities.wrapup( p_status => false
1655 , p_message => l_error_message
1656 , p_period_from => l_collect_from_date
1657 , p_period_to => l_collect_to_date
1658 );
1659 errbuf := l_error_message;
1660 retcode := '2';
1661
1662 end load_resolved;
1663
1664 procedure wrapup
1665 ( errbuf in out nocopy varchar2
1666 , retcode in out nocopy varchar2) as
1667
1668 begin
1669
1670 -- this is now a noop
1671 return;
1672
1673 end wrapup;
1674
1675
1676 end biv_dbi_collection_init;