[Home] [Help]
PACKAGE BODY: APPS.BIV_DBI_COLLECTION_INC
Source
1 package body biv_dbi_collection_inc as
2 /* $Header: bivsrvcincb.pls 120.5 2006/01/17 03:09:06 ngmishra 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) := 'INCREMENTAL_LOAD';
8
9 function apply_escalations
10 ( p_collect_from_date in date
11 , p_collect_to_date in date
12 , x_rowcount out nocopy number
13 , x_error_message out nocopy varchar2
14 ) return number is
15
16 /* this cursor finds all of the escalation that relate to
17 incident present in the staging table.
18 */
19 cursor c_esc is
20 select
21 trf.object_id incident_id
22 , tsk.actual_start_date escalated_date_from
23 , nvl(tsk.actual_end_date,p_collect_to_date+1) escalated_date_to
24 from
25 jtf_tasks_b tsk
26 , jtf_task_references_b trf
27 where
28 trf.object_type_code = 'SR'
29 and trf.reference_code = 'ESC'
30 and tsk.task_type_id = 22
31 and tsk.task_id = trf.task_id
32 and trf.object_id in ( select /*+ cardinality(stg,10) NO_UNNEST */ incident_id from biv_dbi_collection_stg stg)
33 and NOT EXISTS
34 (SELECT null
35 FROM jtf_task_references_b trf2
36 where trf2.reference_code = 'ESC'
37 and trf2.object_type_code = 'SR'
38 and trf2.object_id = trf.object_id
39 and trf2.task_id < trf.task_id)
40 order by 1, 2, 3;
41
42 /* this cursor attempts to find the last row in the staging
43 table prior or equal to the escalation (from or to) date
44 */
45 cursor c_stg( b_incident_id number, b_esc_date date ) is
46 select
47 audit_date
48 , incident_type_id
49 , inventory_item_id
50 , inv_organization_id
51 , incident_severity_id
52 , incident_status_id
53 , owner_group_id
54 , status_flag
55 , sr_creation_channel
56 , customer_id
57 , incident_date
58 , unowned_date
59 , resolved_flag
60 , incident_resolved_date
61 , resolved_event_flag
62 , unresolved_event_flag
63 , backlog_rowid
64 , resolution_code
65 , incident_urgency_id
66 , incident_owner_id
67 from
68 biv_dbi_collection_stg
69 where
70 incident_id = b_incident_id
71 and audit_date <= b_esc_date
72 order by
73 audit_date desc
74 , incident_audit_id desc;
75
76 l_stg_rec c_stg%rowtype;
77 l_rowcount number := 0;
78
79 begin
80
81 for e in c_esc loop
82 /* if the escalation started on or after the current collect from
83 date we need to check that we have a row in the staging
84 table for the day that it starts
85 */
86 if e.escalated_date_from >= p_collect_from_date then
87 open c_stg( e.incident_id, e.escalated_date_from );
88 fetch c_stg into l_stg_rec;
89 if c_stg%found and
90 (trunc(l_stg_rec.audit_date) < trunc(e.escalated_date_from) or
91 l_stg_rec.backlog_rowid is not null) then
92 insert into biv_dbi_collection_stg
93 ( incident_id
94 , audit_date
95 , incident_audit_id
96 , incident_type_id
97 , inventory_item_id
98 , inv_organization_id
99 , incident_severity_id
100 , incident_status_id
101 , owner_group_id
102 , status_flag
103 , sr_creation_channel
104 , customer_id
105 , incident_date
106 , unowned_date
107 , resolved_flag
108 , incident_resolved_date
109 , resolved_event_flag
110 , unresolved_event_flag
111 , resolution_code
112 , last_for_day_flag
113 , creation_date
114 , created_by
115 , last_update_date
116 , last_updated_by
117 , last_update_login
118 , incident_urgency_id
119 , incident_owner_id
120 , ever_escalated
121 )
122 values
123 ( e.incident_id
124 , trunc(e.escalated_date_from)
125 , 2
126 , l_stg_rec.incident_type_id
127 , l_stg_rec.inventory_item_id
128 , l_stg_rec.inv_organization_id
129 , l_stg_rec.incident_severity_id
130 , l_stg_rec.incident_status_id
131 , l_stg_rec.owner_group_id
132 , l_stg_rec.status_flag
133 , l_stg_rec.sr_creation_channel
134 , l_stg_rec.customer_id
135 , l_stg_rec.incident_date
136 , l_stg_rec.unowned_date
137 , l_stg_rec.resolved_flag
138 , l_stg_rec.incident_resolved_date
139 , l_stg_rec.resolved_event_flag
140 , l_stg_rec.unresolved_event_flag
141 , l_stg_rec.resolution_code
142 , 'Y'
143 , sysdate
144 , g_user_id
145 , sysdate
146 , g_user_id
147 , g_login_id
148 , l_stg_rec.incident_urgency_id
149 , l_stg_rec.incident_owner_id
150 , 'N'
151 );
152 l_rowcount := l_rowcount +1;
153 end if;
154 close c_stg;
155 end if;
156
157 /* if the escalation ended between the current collect from
158 date and the collect to date we need to check that we have a row
159 in the staging table for the day that it ends
160 */
161 if e.escalated_date_to between p_collect_from_date
162 and p_collect_to_date then
163 open c_stg( e.incident_id, e.escalated_date_to );
164 fetch c_stg into l_stg_rec;
165 if c_stg%found and
166 (trunc(l_stg_rec.audit_date) < trunc(e.escalated_date_to) or
167 l_stg_rec.backlog_rowid is not null) then
168 insert into biv_dbi_collection_stg
169 ( incident_id
170 , audit_date
171 , incident_audit_id
172 , incident_type_id
173 , inventory_item_id
174 , inv_organization_id
175 , incident_severity_id
176 , incident_status_id
177 , owner_group_id
178 , status_flag
179 , sr_creation_channel
180 , customer_id
181 , incident_date
182 , unowned_date
183 , resolved_flag
184 , incident_resolved_date
185 , resolved_event_flag
186 , unresolved_event_flag
187 , resolution_code
188 , last_for_day_flag
189 , creation_date
190 , created_by
191 , last_update_date
192 , last_updated_by
193 , last_update_login
194 , incident_urgency_id
195 , incident_owner_id
196 , ever_escalated
197 )
198 values
199 ( e.incident_id
200 , trunc(e.escalated_date_to)
201 , 2
202 , l_stg_rec.incident_type_id
203 , l_stg_rec.inventory_item_id
204 , l_stg_rec.inv_organization_id
205 , l_stg_rec.incident_severity_id
206 , l_stg_rec.incident_status_id
207 , l_stg_rec.owner_group_id
208 , l_stg_rec.status_flag
209 , l_stg_rec.sr_creation_channel
210 , l_stg_rec.customer_id
211 , l_stg_rec.incident_date
212 , l_stg_rec.unowned_date
213 , null -- RAVI to Verify
214 , null -- RAVI tp Verify
215 , 'N'
216 , 'N'
217 , l_stg_rec.resolution_code
218 , 'Y'
219 , sysdate
220 , g_user_id
221 , sysdate
222 , g_user_id
223 , g_login_id
224 , l_stg_rec.incident_urgency_id
225 , l_stg_rec.incident_owner_id
226 , 'N'
227 );
228 end if;
229 l_rowcount := l_rowcount +1;
230 close c_stg;
231 end if;
232
233 /* update all of the rows in the staging table where
234 there audit date is within the escalated date range
235 but don't update the row for the existing backlog
236 as we have created a new row for this.
237 */
238 update biv_dbi_collection_stg
239 set escalated_date = case when ( trunc(audit_date) < trunc(e.escalated_date_to) ) then e.escalated_date_from
240 else escalated_date
241 end
242 , ever_escalated = 'Y'
243 , last_update_date = sysdate
244 , last_updated_by = g_user_id
245 , last_update_login = g_login_id
246 where
247 incident_id = e.incident_id
248 and trunc(audit_date) >= trunc(e.escalated_date_from)
249 and backlog_rowid is null;
250 end loop;
251
252 BEGIN
253
254 fnd_stats.gather_table_stats(ownname => 'BIV',
255 tabname => 'BIV_DBI_ESCALATIONS_STG', PERCENT => 10);
256
257 END;
258
259 x_rowcount := l_rowcount;
260 return 0;
261
262 exception
263 when others then
264 x_error_message := sqlerrm;
265 return -1;
266
267 end apply_escalations;
268
269 function process_incremental
270 ( p_log_rowid in rowid
271 , p_collect_from_date in date
272 , p_collect_to_date in date
273 , p_staging_flag in varchar2
274 , p_activity_flag in varchar2
275 , p_closed_flag in varchar2
276 , p_backlog_flag in varchar2
277 , p_resolution_flag in varchar2
278 , x_rowcount out nocopy number
279 , x_error_message out nocopy varchar2
280 )
281 return number as
282
283 l_exception exception;
284 l_error_message varchar2(4000);
285 l_biv_schema varchar2(100);
286
287 l_phase number;
288
289 l_timer number;
290 l_total_rowcount number := 0;
291 l_rowcount number;
292 l_temp_rowcount number;
293
294 type t_rowid_tab is table of rowid;
295 type t_date_tab is table of date;
296 l_backlog_rowid_tab t_rowid_tab;
297 l_backlog_date_to_tab t_date_tab;
298 l_backlog_collected_to_tab t_date_tab;
299
300 type t_number_tab is table of number;
301
302 l_from_party_tab t_number_tab;
303 l_to_party_tab t_number_tab;
304
305 l_missing_owner_group_id number := biv_dbi_collection_util.get_missing_owner_group_id;
306 l_missing_inventory_item_id number := biv_dbi_collection_util.get_missing_inventory_item_id;
307 l_missing_organization_id number := biv_dbi_collection_util.get_missing_organization_id;
308
309 l_max_date date := to_date('4712/12/31','yyyy/mm/dd');
310
311 begin
312
313 if p_staging_flag = 'N' then
314
315 l_phase := 1;
316 l_timer := dbms_utility.get_time;
317 l_rowcount := 0;
318
319 if biv_dbi_collection_util.get_schema_name(l_biv_schema, l_error_message) <> 0 then
320 raise l_exception;
321 end if;
322
323 bis_collection_utilities.log('Truncating table staging table');
324
325 if biv_dbi_collection_util.truncate_table
326 (l_biv_schema, 'BIV_DBI_COLLECTION_STG', l_error_message) <> 0 then
327 raise l_exception;
328 end if;
329
330 bis_collection_utilities.log('Populating staging table');
331
332 /* this is a temporary workaround to bad audit data cause by:
333 - bug 3050727 - fixed
334 */
335
336 if biv_dbi_collection_util.correct_bad_audit(l_error_message) <> 0 then
337 raise l_exception;
338 end if;
339
340 bis_collection_utilities.log('insert rows for previous backlog into staging table',1);
341 /*
342 insert rows for previous backlog into staging table
343 insert current audit activity rows into staging table
344 apply values from the incidents table to staging table
345 */
346
347 bis_collection_utilities.log('insert current audit activity rows into staging table',1);
348
349 bis_collection_utilities.log('apply values from the incidents table to staging table',1);
350
351
352 insert into biv_dbi_collection_stg
353 (incident_id
354 , audit_date
355 , incident_audit_id
356 , incident_type_id
357 , inventory_item_id
358 , inv_organization_id
359 , incident_severity_id
360 , incident_status_id
361 , owner_group_id
362 , status_flag
363 , old_status_flag
364 , unowned_date
365 , resolved_flag
366 , incident_resolved_date
367 , resolved_event_flag
368 , unresolved_event_flag
369 , escalated_date
370 , backlog_rowid
371 , first_opened_flag
372 , reopened_flag
373 , reopened_date
374 , closed_flag
375 , closed_date
376 , last_for_day_flag
377 , party_merge_flag
378 , old_customer_id
379 , creation_date
380 , created_by
381 , last_update_date
382 , last_updated_by
383 , last_update_login
384 , incident_urgency_id
385 , incident_owner_id
386 , ever_escalated
387 , incident_date
388 , customer_id
389 , sr_creation_channel
390 , resolution_code
391 )
392 select
393 f.incident_id
394 , f.backlog_date_from
395 , 1
396 , f.incident_type_id
397 , f.inventory_item_id
398 , f.inv_organization_id
399 , f.incident_severity_id
400 , f.incident_status_id
401 , f.owner_group_id
402 , 'O'
403 , 'O'
404 , f.unowned_date
405 , f.resolved_flag
406 , f.incident_resolved_date
407 , 'N'
408 , 'N'
409 , f.escalated_date
410 , f.rowid
411 , null
412 , null
413 , null
414 , null
415 , null
416 , 'Y'
417 , null
418 , null
419 , sysdate
420 , g_user_id
421 , sysdate
422 , g_user_id
423 , g_login_id
424 , f.incident_urgency_id
425 , f.incident_owner_id
426 , f.escalated_flag
427 ,i.incident_date
428 , nvl(i.customer_id,-1) /* functionally should not be possible */
429 , nvl(i.sr_creation_channel,'-1') /* functionally should not be possible */
430 , nvl(i.resolution_code,'-1') /* valid, resolution code not specified*/
431 from
432 biv_dbi_backlog_sum_f f, cs_incidents_all_b i
433 where
434 backlog_date_to = l_max_date
435 and i.incident_id = f.incident_id
436
437 union all
438
439 select
440 a.incident_id
441 , a.creation_date audit_date
442 , a.incident_audit_id
443 , nvl(a.incident_type_id,-1) incident_type_id /* workaround bad data */
444 , nvl2( a.inventory_item_id+a.inv_organization_id
445 , a.inventory_item_id
446 , l_missing_inventory_item_id ) inventory_item_id
447 , nvl2( a.inventory_item_id+a.inv_organization_id
448 , a.inv_organization_id
449 , l_missing_organization_id )inv_organization_id
450 , nvl(a.incident_severity_id,-1) incident_severity_id /* workaround bad data */
451 , nvl(a.incident_status_id,-1) incident_status_id /* workaround bad data */
452 , decode(a.group_type, 'RS_GROUP', nvl(a.group_id,l_missing_owner_group_id)
453 , l_missing_owner_group_id) owner_group_id
454 , a.status_flag
455 , a.old_status_flag
456 , decode( a.incident_owner_id
457 , null
458 , nvl(a.owner_assigned_time,nvl(a.incident_date,a.creation_date))
459 -- based on bug 2993526, if the incident is created
460 -- with no owner, the initial audit row will have
461 -- NULL in owner_assigned_time - intended behavior
462 -- so we need to take incident_date from audit row
463 -- if for any reason (bad data) this is null, then we take
464 -- creation_date from row.
465 , null ) unowned_date
466 , case when (a.incident_resolved_date is not null and
467 a.incident_resolved_date <= a.creation_date) then 'Y'
468 -- when a.status_flag = 'C' then 'Y'
469 else 'N'
470 end resolved_flag
471 , case when (a.incident_resolved_date is not null and
472 a.incident_resolved_date <= a.creation_date) then a.incident_resolved_date
473 -- when a.status_flag = 'C' then nvl(a.close_date,a.creation_date)
474 else null
475 end incident_resolved_date
476 , case
477 when nvl(a.old_incident_resolved_date, a.incident_resolved_date+1) <> a.incident_resolved_date
478 and a.incident_resolved_date is not null then
479 'Y'
480 else
481 'N'
482 end resolved_event_flag
483 , case
484 when a.old_incident_resolved_date <> NVL(a.incident_resolved_date,a.old_incident_resolved_date+1)
485 and a.incident_resolved_date is null then
486 'Y'
487 else
488 'N'
489 end unresolved_event_flag
490 , null
491 , null
492 , case
493 when a.change_incident_type_flag = 'Y' and a.old_incident_type_id is null then
494 'Y'
495 else
496 'N'
497 end first_opened_flag
498 , case
499 when a.change_status_flag = 'Y' and a.old_status_flag = 'C' and a.status_flag = 'O' then
500 'Y'
501 else
502 'N'
503 end reopened_flag
504 , case
505 when a.change_status_flag = 'Y' and a.old_status_flag = 'C' and a.status_flag = 'O' then
506 a.creation_date
507 else
508 null
509 end reopened_date
510 , case
511 when a.change_status_flag = 'Y' and a.status_flag = 'C' then
512 'Y'
513 else
514 'N'
515 end closed_flag
516 , case
517 when a.change_status_flag = 'Y' and a.status_flag = 'C' then
518 nvl(a.close_date,a.creation_date)
519 else
520 null
521 end closed_date
522 , decode( a.incident_audit_id
523 , last_value(a.incident_audit_id)
524 over ( partition by a.incident_id, trunc(a.creation_date)
525 -- modified order by based on conclusions found in bug 3524935
526 order by decode(a.old_status_flag,null,1,2)
527 , a.creation_date
528 , a.incident_audit_id
529 rows between unbounded preceding and unbounded following )
530 , 'Y'
531 , 'N' ) last_for_day_flag
532 , case
533 when a.old_customer_id is not null and
534 a.customer_id is not null and
535 a.old_customer_id <> a.customer_id then
536 'Y'
537 else
538 'N'
539 end party_merge_flag
540 , a.old_customer_id
541 , sysdate
542 , g_user_id
543 , sysdate
544 , g_user_id
545 , g_login_id
546 , nvl(a.incident_urgency_id,-1) incident_urgency_id /* workaround bad data */
547 , decode(a.resource_type, 'RS_EMPLOYEE', nvl(a.incident_owner_id,-1) , -1) incident_owner_id
548 , 'N'
549 ,i.incident_date
550 , nvl(i.customer_id,-1) /* functionally should not be possible */
551 , nvl(i.sr_creation_channel,'-1') /* functionally should not be possible */
552 , nvl(i.resolution_code,'-1') /* valid, resolution code not specified*/
553 from
554 cs_incidents_audit_b a , cs_incidents_all_b i
555 where
556 a.creation_date between p_collect_from_date and p_collect_to_date
557 and i.incident_id = a.incident_id
558 and nvl(a.updated_entity_code, 'SR_HEADER') IN ('SR_HEADER','SR_ESCALATION')
559 and ('Y' in ( a.change_status_flag
560 , a.change_incident_status_flag
561 , a.change_incident_type_flag
562 , a.change_incident_severity_flag
563 , a.change_inventory_item_flag
564 , a.change_inv_organization_flag
565 , a.change_incident_owner_flag
566 , a.change_incident_urgency_flag
567 , a.change_group_flag
568 , case
569 when a.old_customer_id is not null and
570 a.customer_id is not null and
571 a.old_customer_id <> a.customer_id then
572 'Y'
573 else
574 'N'
575 end
576 --Start bug#4932634
577 ) or NVL(a.old_incident_resolved_date,trunc(sysdate)) <> a.incident_resolved_date );
578 --End bug#4932634
579
580 l_rowcount := sql%rowcount;
581
582 bis_collection_utilities.log('Inserted ' || l_rowcount || ' rows',2);
583
584 commit;
585
586
587 /*
588 apply_escalations
589 */
590 if apply_escalations( p_collect_from_date
591 , p_collect_to_date
592 , l_temp_rowcount
593 , l_error_message
594 ) <> 0 then
595 raise l_exception;
596 end if;
597
598 bis_collection_utilities.log('Inserted ' || l_temp_rowcount || ' rows',2);
599
600 l_rowcount := l_rowcount + l_temp_rowcount;
601
602 commit;
603
604 bis_collection_utilities.log('hide ''duplicate'' rows from backlog query',1);
605 /*
606 hide 'duplicate' rows from backlog query
607 */
608 update biv_dbi_collection_stg
609 set status_flag = lower(status_flag)
610 where rowid in ( select rowid
611 from
612 ( select
613 incident_id || '^' ||
614 incident_type_id || '^' ||
615 inventory_item_id || '^' ||
616 inv_organization_id || '^' ||
617 incident_severity_id || '^' ||
618 incident_status_id || '^' ||
619 owner_group_id || '^' ||
620 unowned_date || '^' ||
621 resolved_flag || '^' ||
622 incident_resolved_date || '^'||
623 resolved_event_flag || '^'||
624 unresolved_event_flag || '^'||
625 escalated_date || '^'||
626 incident_urgency_id || '^'||
627 incident_owner_id || '^'||
628 ever_escalated as conc_key
629 , lag(incident_id || '^' ||
630 incident_type_id || '^' ||
631 inventory_item_id || '^' ||
632 inv_organization_id || '^' ||
633 incident_severity_id || '^' ||
634 incident_status_id || '^' ||
635 owner_group_id || '^' ||
636 unowned_date || '^' ||
637 resolved_flag || '^' ||
638 incident_resolved_date || '^'||
639 resolved_event_flag || '^'||
640 unresolved_event_flag || '^'||
641 escalated_date || '^'||
642 incident_urgency_id|| '^'||
643 incident_owner_id || '^'||
644 ever_escalated
645 ,1,'^')
646 over (order by
647 incident_id
648 , audit_date
649 , incident_audit_id) prev_conc_key
650 from
651 biv_dbi_collection_stg s
652 where
653 last_for_day_flag = 'Y'
654 )
655 where conc_key = prev_conc_key
656 );
657
658 l_temp_rowcount := sql%rowcount;
659
660 bis_collection_utilities.log('Updated ' || l_temp_rowcount || ' rows',2);
661
662 commit;
663
664 bis_collection_utilities.log('Gathering Statistics for staging table');
665
666 if biv_dbi_collection_util.gather_statistics
667 (l_biv_schema, 'BIV_DBI_COLLECTION_STG', l_error_message) <> 0 then
668 raise l_exception;
669 end if;
670
671 update biv_dbi_collection_log
672 set staging_table_flag = 'Y'
673 , staging_table_count = l_rowcount
674 , staging_table_time = dbms_utility.get_time - l_timer
675 , staging_error_message = null
676 , last_update_date = sysdate
677 , last_updated_by = g_user_id
678 , last_update_login = g_login_id
679 where rowid = p_log_rowid;
680
681 commit;
682
683 l_total_rowcount := l_total_rowcount + l_rowcount;
684
685 bis_collection_utilities.log('Staging table complete');
686
687 else
688
689 bis_collection_utilities.log('Staging table already complete, skipping');
690
691 end if;
692
693 bis_collection_utilities.log('Checking for party merge');
694
695 select distinct
696 old_customer_id from_party
697 , customer_id to_party
698 bulk collect into l_from_party_tab
699 , l_to_party_tab
700 from
701 biv_dbi_collection_stg stg
702 where
703 party_merge_flag = 'Y';
704
705 bis_collection_utilities.log('found ' || l_from_party_tab.count || ' distinct party merges', 1);
706
707 if p_activity_flag = 'N' then
708
709 l_phase := 2;
710 l_timer := dbms_utility.get_time;
711 l_rowcount := 0;
712
713 bis_collection_utilities.log('Starting Activity Incremental Load');
714
715 if l_from_party_tab.count > 0 then
716
717 forall i in 1..l_from_party_tab.count
718 update biv_dbi_activity_sum_f
719 set
720 primary_flag = 'N'
721 , customer_id = l_to_party_tab(i)
722 , last_updated_by = g_user_id
723 , last_update_date = sysdate
724 where
725 customer_id = l_from_party_tab(i);
726
727 l_rowcount := sql%rowcount;
728
729 bis_collection_utilities.log('Party Merge updated ' || l_rowcount || ' rows',1);
730
731 end if;
732
733 if p_collect_from_date <> trunc(p_collect_from_date) then
734
735 bis_collection_utilities.log('Merge activity from ' ||
736 fnd_date.date_to_displaydt(p_collect_from_date) ||
737 ' to ' ||
738 fnd_date.date_to_displaydt(least(p_collect_to_date
739 ,trunc(p_collect_from_date)+(86399/86400)))
740 ,1);
741
742 merge
743 into biv_dbi_activity_sum_f a
744 using (
745 select /*+ no_merge cardinality (stg,10) */
746 trunc(audit_date) activity_date
747 , incident_type_id
748 , inventory_item_id
749 , inv_organization_id
750 , incident_severity_id
751 , customer_id
752 , owner_group_id
753 , sr_creation_channel
754 , sum(decode(first_opened_flag,'Y',1,0)) first_opened_count
755 , sum(decode(reopened_flag,'Y',1,0)) reopened_count
756 , sum(decode(closed_flag,'Y',1,0)) closed_count
757 , sysdate update_date
758 , g_user_id user_id
759 , g_login_id login_id
760 , incident_urgency_id
761 , incident_owner_id
762 , ever_escalated escalated_flag
763 from
764 biv_dbi_collection_stg stg
765 where
766 'Y' in ( first_opened_flag, reopened_flag, closed_flag )
767 and audit_date <= trunc(p_collect_from_date)+(86399/86400)
768 group by
769 trunc(audit_date)
770 , incident_type_id
771 , inventory_item_id
772 , inv_organization_id
773 , incident_severity_id
774 , customer_id
775 , owner_group_id
776 , sr_creation_channel
777 , incident_urgency_id
778 , incident_owner_id
779 , ever_escalated
780 ) m
781 on ( a.activity_date = m.activity_date and
782 a.incident_type_id = m.incident_type_id and
783 a.inventory_item_id = m.inventory_item_id and
784 a.inv_organization_id = m.inv_organization_id and
785 a.incident_severity_id = m.incident_severity_id and
786 a.customer_id = m.customer_id and
787 a.owner_group_id = m.owner_group_id and
788 a.sr_creation_channel = m.sr_creation_channel and
789 a.primary_flag = 'Y'and
790 a.incident_urgency_id = m.incident_urgency_id and
791 a.incident_owner_id = m.incident_owner_id and
792 a.escalated_flag = m.escalated_flag
793 )
794 when matched then
795 update
796 set a.first_opened_count = a.first_opened_count + m.first_opened_count
797 , a.reopened_count = a.reopened_count + m.reopened_count
798 , a.closed_count = a.closed_count + m.closed_count
799 , a.last_update_date = m.update_date
800 , a.last_updated_by = m.user_id
801 , a.last_update_login = m.login_id
802 when not matched then
803 insert
804 ( activity_date
805 , incident_type_id
806 , inventory_item_id
807 , inv_organization_id
808 , incident_severity_id
809 , customer_id
810 , owner_group_id
811 , sr_creation_channel
812 , primary_flag
813 , first_opened_count
814 , reopened_count
815 , closed_count
816 , creation_date
817 , created_by
818 , last_update_date
819 , last_updated_by
820 , last_update_login
821 , incident_urgency_id
822 , incident_owner_id
823 , escalated_flag
824 )
825 values
826 ( m.activity_date
827 , m.incident_type_id
828 , m.inventory_item_id
829 , m.inv_organization_id
830 , m.incident_severity_id
831 , m.customer_id
832 , m.owner_group_id
833 , m.sr_creation_channel
834 , 'Y'
835 , m.first_opened_count
836 , m.reopened_count
837 , m.closed_count
838 , m.update_date
839 , m.user_id
840 , m.update_date
841 , m.user_id
842 , m.login_id
843 , m.incident_urgency_id
844 , m.incident_owner_id
845 , m.escalated_flag
846 );
847
848 l_temp_rowcount := sql%rowcount;
849
850 l_rowcount := l_rowcount + l_temp_rowcount;
851
852 bis_collection_utilities.log('Merged ' || l_temp_rowcount || ' rows',2);
853
854 end if;
855
856 if trunc(p_collect_to_date) >= trunc(p_collect_from_date) and
857 trunc(p_collect_from_date-(1/86400))+1 <= p_collect_to_date then
858
859 bis_collection_utilities.log('Insert activity from ' ||
860 fnd_date.date_to_displaydt(trunc(p_collect_from_date-(1/86400))+1) ||
861 ' to ' ||
862 fnd_date.date_to_displaydt(p_collect_to_date)
863 ,1);
864 insert
865 into biv_dbi_activity_sum_f a
866 ( activity_date
867 , incident_type_id
868 , inventory_item_id
869 , inv_organization_id
870 , incident_severity_id
871 , customer_id
872 , owner_group_id
873 , sr_creation_channel
874 , primary_flag
875 , first_opened_count
876 , reopened_count
877 , closed_count
878 , creation_date
879 , created_by
880 , last_update_date
881 , last_updated_by
882 , last_update_login
883 , incident_urgency_id
884 , incident_owner_id
885 , escalated_flag
886 )
887 select
888 trunc(audit_date) activity_date
889 , incident_type_id
890 , inventory_item_id
891 , inv_organization_id
892 , incident_severity_id
893 , customer_id
894 , owner_group_id
895 , sr_creation_channel
896 , 'Y'
897 , sum(decode(first_opened_flag,'Y',1,0)) first_opened_count
898 , sum(decode(reopened_flag,'Y',1,0)) reopened_count
899 , sum(decode(closed_flag,'Y',1,0)) closed_count
900 , sysdate
901 , g_user_id
902 , sysdate
903 , g_user_id
904 , g_login_id
905 , incident_urgency_id
906 , incident_owner_id
907 , ever_escalated
908 from
909 biv_dbi_collection_stg stg
910 where
911 'Y' in ( first_opened_flag, reopened_flag, closed_flag )
912 and audit_date >= trunc(p_collect_from_date-(1/86400))+1
913 group by
914 trunc(audit_date)
915 , incident_type_id
916 , inventory_item_id
917 , inv_organization_id
918 , incident_severity_id
919 , customer_id
920 , owner_group_id
921 , sr_creation_channel
922 , incident_urgency_id
923 , incident_owner_id
924 , ever_escalated;
925
926 l_temp_rowcount := sql%rowcount;
927
928 l_rowcount := l_rowcount + l_temp_rowcount;
929
930 bis_collection_utilities.log('Inserted ' || l_temp_rowcount || ' rows',2);
931
932 end if;
933
934 update biv_dbi_collection_log
935 set activity_flag = 'Y'
936 , activity_count = l_rowcount
937 , activity_time = dbms_utility.get_time - l_timer
938 , activity_error_message = null
939 , last_update_date = sysdate
940 , last_updated_by = g_user_id
941 , last_update_login = g_login_id
942 where rowid = p_log_rowid;
943
944 commit;
945
946 l_total_rowcount := l_total_rowcount + l_rowcount;
947
948 bis_collection_utilities.log('Activity incremental load complete');
949
950 else
951
952 bis_collection_utilities.log('Activity incremental load already complete, skipping');
953
954 end if;
955
956 if p_closed_flag = 'N' then
957
958 l_phase := 3;
959 l_timer := dbms_utility.get_time;
960 l_rowcount := 0;
961
962 bis_collection_utilities.log('Starting Closed Incremental Load');
963
964 if l_from_party_tab.count > 0 then
965
966 forall i in 1..l_from_party_tab.count
967 update biv_dbi_closed_sum_f
968 set
969 customer_id = l_to_party_tab(i)
970 , last_updated_by = g_user_id
971 , last_update_date = sysdate
972 where
973 customer_id = l_from_party_tab(i);
974
975 l_rowcount := sql%rowcount;
976
977 bis_collection_utilities.log('Party Merge updated ' || l_rowcount || ' rows',1);
978
979 end if;
980
981 merge
982 into biv_dbi_closed_sum_f c
983 using (
984 select /*+ cardinality (stg,10) */
985 incident_id
986 , max(closed_date)
987 keep (dense_rank last order by audit_date, incident_audit_id) closed_date
988 , max(reopened_date)
989 keep (dense_rank last order by audit_date, incident_audit_id) reopened_date
990 , max(incident_type_id)
991 keep (dense_rank last order by audit_date, incident_audit_id) incident_type_id
992 , max(inventory_item_id)
993 keep (dense_rank last order by audit_date, incident_audit_id) inventory_item_id
994 , max(inv_organization_id)
995 keep (dense_rank last order by audit_date, incident_audit_id) inv_organization_id
996 , max(incident_severity_id)
997 keep (dense_rank last order by audit_date, incident_audit_id) incident_severity_id
998 , max(customer_id)
999 keep (dense_rank last order by audit_date, incident_audit_id) customer_id
1000 , max(owner_group_id)
1001 keep (dense_rank last order by audit_date, incident_audit_id) owner_group_id
1002 , max(sr_creation_channel)
1003 keep (dense_rank last order by audit_date, incident_audit_id) sr_creation_channel
1004 , max(resolution_code)
1005 keep (dense_rank last order by audit_date, incident_audit_id) resolution_code
1006 , max(closed_date - incident_date)
1007 keep (dense_rank last order by audit_date, incident_audit_id) time_to_close
1008 , sysdate update_date
1009 , g_user_id user_id
1010 , g_login_id login_id
1011 , max(incident_urgency_id)
1012 keep (dense_rank last order by audit_date, incident_audit_id) incident_urgency_id
1013 , max(incident_owner_id)
1014 keep (dense_rank last order by audit_date, incident_audit_id) incident_owner_id
1015 , max(ever_escalated)
1016 keep (dense_rank last order by audit_date, incident_audit_id) escalated_flag
1017 from
1018 biv_dbi_collection_stg stg
1019 where
1020 ('Y' in (closed_flag) and (old_status_flag = 'O' or old_status_flag is null)
1021 /* workaround for bad data where old_status_flag can be null.*/
1022 or 'Y' in (reopened_flag))
1023 /* to update those SR's that have been reopened so that they are not displayed in the report. */
1024 group by incident_id
1025 ) m
1026 on ( c.incident_id = m.incident_id )
1027 when matched then
1028 update
1029 set report_date = decode(m.reopened_date,null,trunc(m.closed_date),c.report_date)
1030 , reopened_date = m.reopened_date
1031 , incident_type_id = decode(m.reopened_date,null,m.incident_type_id,c.incident_type_id)
1032 , inventory_item_id = decode(m.reopened_date,null,m.inventory_item_id,c.inventory_item_id)
1033 , inv_organization_id = decode(m.reopened_date,null,m.inv_organization_id,c.inv_organization_id)
1034 , incident_severity_id = decode(m.reopened_date,null,m.incident_severity_id,c.incident_severity_id)
1035 , customer_id = decode(m.reopened_date,null,m.customer_id,c.customer_id)
1036 , owner_group_id = decode(m.reopened_date,null,m.owner_group_id,c.owner_group_id)
1037 , sr_creation_channel = decode(m.reopened_date,null,m.sr_creation_channel,c.sr_creation_channel)
1038 , resolution_code = decode(m.reopened_date,null,m.resolution_code,c.resolution_code)
1039 , time_to_close = decode(m.reopened_date,null,m.time_to_close,c.time_to_close)
1040 , last_update_date = m.update_date
1041 , last_updated_by = m.user_id
1042 , last_update_login = m.login_id
1043 , incident_urgency_id = decode(m.reopened_date,null,m.incident_urgency_id,c.incident_urgency_id)
1044 , incident_owner_id = decode(m.reopened_date,null,m.incident_owner_id,c.incident_owner_id)
1045 , escalated_flag = decode(m.reopened_date,null,m.escalated_flag,c.escalated_flag)
1046 when not matched then
1047 insert
1048 ( report_date
1049 , incident_id
1050 , incident_type_id
1051 , inventory_item_id
1052 , inv_organization_id
1053 , incident_severity_id
1054 , customer_id
1055 , owner_group_id
1056 , sr_creation_channel
1057 , resolution_code
1058 , time_to_close
1059 , reopened_date
1060 , creation_date
1061 , created_by
1062 , last_update_date
1063 , last_updated_by
1064 , last_update_login
1065 , incident_urgency_id
1066 , incident_owner_id
1067 , escalated_flag
1068 )
1069 values
1070 ( trunc(nvl(m.closed_date,m.reopened_date))
1071 , m.incident_id
1072 , m.incident_type_id
1073 , m.inventory_item_id
1074 , m.inv_organization_id
1075 , m.incident_severity_id
1076 , m.customer_id
1077 , m.owner_group_id
1078 , m.sr_creation_channel
1079 , m.resolution_code
1080 , m.time_to_close
1081 , m.reopened_date
1082 , m.update_date
1083 , m.user_id
1084 , m.update_date
1085 , m.user_id
1086 , m.login_id
1087 , m.incident_urgency_id
1088 , m.incident_owner_id
1089 , m.escalated_flag
1090 );
1091
1092 l_temp_rowcount := sql%rowcount;
1093
1094 l_rowcount := l_rowcount + l_temp_rowcount;
1095
1096 bis_collection_utilities.log('Merged ' || l_temp_rowcount || ' rows',1);
1097
1098 update biv_dbi_collection_log
1099 set closed_flag = 'Y'
1100 , closed_count = l_rowcount
1101 , closed_time = dbms_utility.get_time - l_timer
1102 , closed_error_message = null
1103 , last_update_date = sysdate
1104 , last_updated_by = g_user_id
1105 , last_update_login = g_login_id
1106 where rowid = p_log_rowid;
1107
1108 commit;
1109
1110 l_total_rowcount := l_total_rowcount + l_rowcount;
1111
1112 bis_collection_utilities.log('Closed incremental load complete');
1113
1114 else
1115
1116 bis_collection_utilities.log('Closed incremental load already complete, skipping');
1117
1118 end if;
1119
1120 if p_backlog_flag = 'N' then
1121
1122 l_phase := 4;
1123 l_timer := dbms_utility.get_time;
1124 l_rowcount := 0;
1125
1126 bis_collection_utilities.log('Starting Backlog Incremental Load');
1127
1128 if l_from_party_tab.count > 0 then
1129
1130 forall i in 1..l_from_party_tab.count
1131 update biv_dbi_backlog_sum_f
1132 set
1133 customer_id = l_to_party_tab(i)
1134 , last_updated_by = g_user_id
1135 , last_update_date = sysdate
1136 where
1137 customer_id = l_from_party_tab(i);
1138
1139 l_rowcount := sql%rowcount;
1140
1141 bis_collection_utilities.log('Party merge updated ' || l_rowcount || ' rows',1);
1142
1143 end if;
1144
1145 bis_collection_utilities.log('Updating existing backlog rows',1);
1146
1147 /* identify all existing backlog rows and determine there new end dates
1148 */
1149 select
1150 backlog_rowid
1151 , backlog_date_to
1152 bulk collect into l_backlog_rowid_tab
1153 , l_backlog_date_to_tab
1154 from
1155 ( select
1156 backlog_rowid
1157 , audit_date
1158 , lead(trunc(audit_date)-1,1,l_max_date)
1159 over(partition by incident_id
1160 order by audit_date, incident_audit_id) backlog_date_to
1161 from
1162 biv_dbi_collection_stg stg
1163 where
1164 status_flag in ('O', 'C')
1165 and last_for_day_flag = 'Y'
1166 )
1167 where
1168 backlog_rowid is not null;
1169
1170 /* update all existing backlog rows with there new end dates
1171 */
1172 forall i in 1..l_backlog_rowid_tab.count
1173 update /*+ rowid(f) */ biv_dbi_backlog_sum_f f
1174 set backlog_date_to = l_backlog_date_to_tab(i)
1175 , last_update_date = sysdate
1176 , last_updated_by = g_user_id
1177 , last_update_login = g_login_id
1178 where
1179 rowid = l_backlog_rowid_tab(i)
1180 and backlog_date_to <> l_backlog_date_to_tab(i);
1181
1182 l_temp_rowcount := sql%rowcount;
1183
1184 l_rowcount := l_rowcount + l_temp_rowcount;
1185
1186 bis_collection_utilities.log('Updated ' || l_temp_rowcount || ' rows',2);
1187
1188 bis_collection_utilities.log('Inserting new backlog rows',1);
1189
1190 /* insert new backlog rows
1191 */
1192 insert
1193 first
1194 when status_flag = 'O' and
1195 backlog_rowid is null then
1196 into biv_dbi_backlog_sum_f
1197 ( backlog_date_from
1198 , backlog_date_to
1199 , incident_id
1200 , incident_type_id
1201 , inventory_item_id
1202 , inv_organization_id
1203 , incident_severity_id
1204 , incident_status_id
1205 , customer_id
1206 , owner_group_id
1207 , sr_creation_channel
1208 , incident_date
1209 , escalated_date
1210 , unowned_date
1211 , resolved_flag
1212 , incident_resolved_date
1213 , creation_date
1214 , created_by
1215 , last_update_date
1216 , last_updated_by
1217 , last_update_login
1218 , escalated_flag
1219 , incident_urgency_id
1220 , incident_owner_id
1221 )
1222 values
1223 ( backlog_date_from
1224 , backlog_date_to
1225 , incident_id
1226 , incident_type_id
1227 , inventory_item_id
1228 , inv_organization_id
1229 , incident_severity_id
1230 , incident_status_id
1231 , customer_id
1232 , owner_group_id
1233 , sr_creation_channel
1234 , incident_date
1235 , escalated_date
1236 , unowned_date
1237 , resolved_flag
1238 , incident_resolved_date
1239 , last_update_date
1240 , last_updated_by
1241 , last_update_date
1242 , last_updated_by
1243 , last_update_login
1244 , escalated_flag
1245 , incident_urgency_id
1246 , incident_owner_id
1247 )
1248 select
1249 status_flag
1250 , backlog_rowid
1251 , trunc(audit_date) backlog_date_from
1252 , lead(trunc(audit_date)-1,1,l_max_date)
1253 over(partition by incident_id
1254 order by audit_date, incident_audit_id) backlog_date_to
1255 , incident_id
1256 , incident_type_id
1257 , inventory_item_id
1258 , inv_organization_id
1259 , incident_severity_id
1260 , incident_status_id
1261 , customer_id
1262 , owner_group_id
1263 , sr_creation_channel
1264 , incident_date
1265 , escalated_date
1266 , unowned_date
1267 , resolved_flag
1268 , incident_resolved_date
1269 , sysdate last_update_date
1270 , g_user_id last_updated_by
1271 , g_login_id last_update_login
1272 -- , case when (escalated_date <= audit_date) then 'Y' else 'N' end escalated_flag
1273 , ever_escalated escalated_flag
1274 , incident_urgency_id
1275 , incident_owner_id
1276 from
1277 biv_dbi_collection_stg stg
1278 where
1279 status_flag in ('O','C')
1280 and last_for_day_flag = 'Y';
1281
1282 l_temp_rowcount := sql%rowcount;
1283
1284 l_rowcount := l_rowcount + l_temp_rowcount;
1285
1286 bis_collection_utilities.log('Inserted ' || l_temp_rowcount || ' rows',2);
1287
1288 update biv_dbi_collection_log
1289 set backlog_flag = 'Y'
1290 , backlog_count = l_rowcount
1291 , backlog_time = dbms_utility.get_time - l_timer
1292 , backlog_error_message = null
1293 , last_update_date = sysdate
1294 , last_updated_by = g_user_id
1295 , last_update_login = g_login_id
1296 where rowid = p_log_rowid;
1297
1298 commit;
1299
1300 l_total_rowcount := l_total_rowcount + l_rowcount;
1301
1302 bis_collection_utilities.log('Backlog incremental load complete');
1303
1304 else
1305
1306 bis_collection_utilities.log('Backlog incremental load already complete, skipping');
1307 end if;
1308
1309 if p_resolution_flag = 'N' then
1310
1311 l_phase := 5;
1312 l_timer := dbms_utility.get_time;
1313 l_rowcount := 0;
1314
1315 bis_collection_utilities.log('Starting Resolution Incremental Load');
1316
1317 if l_from_party_tab.count > 0 then
1318
1319 forall i in 1..l_from_party_tab.count
1320 update biv_dbi_resolution_sum_f
1321 set
1322 customer_id = l_to_party_tab(i)
1323 , last_updated_by = g_user_id
1324 , last_update_date = sysdate
1325 where
1326 customer_id = l_from_party_tab(i);
1327
1328 l_rowcount := sql%rowcount;
1329
1330 bis_collection_utilities.log('Party Merge updated ' || l_rowcount || ' rows',1);
1331
1332 end if;
1333
1334 merge
1335 into biv_dbi_resolution_sum_f c
1336 using (
1337 select
1338 CASE WHEN incident_resolved_date is null or incident_resolved_date < incident_date
1339 THEN last_update_date
1340 ELSE incident_resolved_date
1341 END report_date,
1342 incident_id,
1343 incident_type_id,
1344 nvl2( inventory_item_id+inv_organization_id
1345 , inventory_item_id
1346 , l_missing_inventory_item_id ) inventory_item_id,
1347 nvl2( inventory_item_id+inv_organization_id
1348 , inv_organization_id
1349 , l_missing_organization_id )inv_organization_id,
1350 incident_severity_id,
1351 nvl(customer_id,-1) customer_id,
1352 decode(group_type, 'RS_GROUP', nvl(owner_group_id,l_missing_owner_group_id)
1353 , l_missing_owner_group_id) owner_group_id,
1354 nvl(sr_creation_channel,'-1') sr_creation_channel,
1355 nvl(resolution_code,'-1') resolution_code,
1356 CASE WHEN (incident_resolved_date IS NOT NULL)
1357 THEN
1358 case when incident_resolved_date < incident_date then
1359 last_update_date
1360 else
1361 incident_resolved_date
1362 end
1363 ELSE
1364 NULL
1365 END - incident_date time_to_resolution,
1366 sysdate last_update_date,
1367 g_user_id last_updated_by,
1368 g_login_id last_update_login,
1369 incident_urgency_id,
1370 decode(resource_type, 'RS_EMPLOYEE', nvl(incident_owner_id,-2)
1371 , -2) incident_owner_id,
1372 escalated_flag,
1373 respond_on_date,
1374 respond_by_date,
1375 resolve_by_date,
1376 incident_date,
1377 contract_number
1378 FROM
1379 (
1380 select
1381 i.incident_id,
1382 CASE
1383 WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN
1384 CASE WHEN(a.a_incident_resolved_date < i.incident_date) then i.incident_date
1385 -- WHEN(a.a_incident_resolved_date > i.close_date ) then i.close_date
1386 ELSE a.a_incident_resolved_date
1387 END
1388 /* From 8.0 SR's that are resolved only are taken into the resolution fact.
1389 WHEN (i.status_flag = 'C') THEN
1390 case
1391 when i.close_date is null or i.close_date < i.incident_date then
1392 i.last_update_date
1393 else
1394 i.close_date
1395 end */
1396 ELSE NULL
1397 END incident_resolved_date,
1398 CASE
1399 WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_incident_type_id
1400 ELSE incident_type_id
1401 END incident_type_id,
1402 CASE
1403 WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_inventory_item_id
1404 ELSE inventory_item_id
1405 END inventory_item_id,
1406 CASE
1407 WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_inv_organization_id
1408 ELSE inv_organization_id
1409 END inv_organization_id,
1410 CASE
1411 WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_incident_severity_id
1412 ELSE incident_severity_id
1413 END incident_severity_id,
1414 i.customer_id,
1415 /* From 8.0 SR's that are resolved only are taken into the resolution fact.
1416 CASE
1417 WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_group_type
1418 WHEN (i.status_flag = 'C') THEN group_type
1419 ELSE NULL
1420 END*/ group_type,
1421 i.sr_creation_channel,
1422 CASE
1423 WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_resolution_code
1424 ELSE resolution_code
1425 END resolution_code,
1426 i.last_update_date,
1427 i.owner_group_id,
1428 i.incident_date,
1429 CASE
1430 WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_incident_urgency_id
1431 -- WHEN (i.status_flag = 'C') THEN incident_urgency_id
1432 ELSE NULL
1433 END incident_urgency_id,
1434 i.resource_type,
1435 CASE
1436 WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_incident_owner_id
1437 -- WHEN (i.status_flag = 'C') THEN incident_owner_id
1438 ELSE NULL
1439 END incident_owner_id,
1440 CASE
1441 WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_escalated_flag
1442 /* WHEN (i.status_flag = 'C') THEN
1443 CASE WHEN e.escalated_date_from <= i.close_date THEN 'Y' ELSE 'N' END */
1444 ELSE 'N'
1445 END escalated_flag,
1446 i.inc_responded_by_date respond_on_date,
1447 i.obligation_date respond_by_date,
1448 i.expected_resolution_date resolve_by_date,
1449 i.contract_number contract_number
1450 from
1451 (
1452 select /*+ cardinality(stg, 10) */
1453 incident_id a_incident_id
1454 , max(incident_resolved_date)
1455 keep (dense_rank last order by audit_date, incident_audit_id) a_incident_resolved_date
1456 , max(incident_type_id)
1457 keep (dense_rank last order by audit_date, incident_audit_id) a_incident_type_id
1458 , max(inventory_item_id)
1459 keep (dense_rank last order by audit_date, incident_audit_id) a_inventory_item_id
1460 , max(inv_organization_id)
1461 keep (dense_rank last order by audit_date, incident_audit_id) a_inv_organization_id
1462 , max(incident_severity_id)
1463 keep (dense_rank last order by audit_date, incident_audit_id) a_incident_severity_id
1464 , max(owner_group_id)
1465 keep (dense_rank last order by audit_date, incident_audit_id) a_owner_group_id
1466 , max(sr_creation_channel)
1467 keep (dense_rank last order by audit_date, incident_audit_id) a_sr_creation_channel
1468 , max(resolution_code)
1469 keep (dense_rank last order by audit_date, incident_audit_id) a_resolution_code
1470 , max(incident_urgency_id)
1471 keep (dense_rank last order by audit_date, incident_audit_id) a_incident_urgency_id
1472 , max(incident_owner_id)
1473 keep (dense_rank last order by audit_date, incident_audit_id) a_incident_owner_id
1474 , max(ever_escalated)
1475 keep (dense_rank last order by audit_date, incident_audit_id) a_escalated_flag
1476 from
1477 biv_dbi_collection_stg stg
1478 where ('Y' in (resolved_flag) and (old_status_flag = 'O' or old_status_flag is null)
1479 /* workaround for bad data where old_status_flag can be null.*/
1480 or 'Y' in (reopened_flag))
1481 /* to update those SR's that have been reopened so that they are not displayed in the report. */
1482 group by incident_id
1483 ) a, cs_incidents_all_b i,
1484 (
1485 select trf.object_id, tsk.task_id, trunc(tsk.actual_start_date) escalated_date_from
1486 , trunc(nvl(tsk.actual_end_date,to_date('01-12-4712','DD-MM-YYYY'))) escalated_date_to
1487 , tsk.actual_start_date escalated_date
1488 , CASE WHEN trunc(tsk.actual_start_date) = trunc(nvl(tsk.actual_end_date,to_date('01-12-4712','DD-MM-YYYY')))
1489 THEN
1490 'Y'
1491 ELSE
1492 'N'
1493 END de_escalated_same_day
1494 from
1495 jtf_tasks_b tsk, jtf_task_references_b trf
1496 where trf.object_type_code = 'SR'
1497 and trf.reference_code = 'ESC'
1498 and tsk.task_type_id = 22
1499 and trf.task_id = tsk.task_id
1500 and NOT EXISTS
1501 (SELECT null
1502 FROM jtf_task_references_b trf2
1503 where trf2.reference_code = 'ESC'
1504 and trf2.object_type_code = 'SR'
1505 and trf2.object_id = trf.object_id
1506 and trf2.task_id < trf.task_id)
1507 ) e
1508 where a.a_incident_id = i.incident_id
1509 and e.object_id(+) = i.incident_id
1510 )
1511 ) m
1512 on ( c.incident_id = m.incident_id )
1513 when matched then
1514 update
1515 set
1516 report_date = m.report_date
1517 , incident_type_id = m.incident_type_id
1518 , inventory_item_id = m.inventory_item_id
1519 , inv_organization_id = m.inv_organization_id
1520 , incident_severity_id = m.incident_severity_id
1521 , customer_id = m.customer_id
1522 , owner_group_id = m.owner_group_id
1523 , sr_creation_channel = sr_creation_channel
1524 , resolution_code = resolution_code
1525 , time_to_resolution = m.time_to_resolution
1526 , last_update_date = m.last_update_date
1527 , last_updated_by = m.last_updated_by
1528 , last_update_login = m.last_update_login
1529 , incident_urgency_id = m.incident_urgency_id
1530 , incident_owner_id = m.incident_owner_id
1531 , escalated_flag = m.escalated_flag
1532 , respond_on_date = m.respond_on_date
1533 , respond_by_date = m.respond_by_date
1534 , resolve_by_date= m.resolve_by_date
1535 , incident_date = m.incident_date
1536 , contract_number = m.contract_number
1537 when not matched then
1538 insert
1539 (
1540 report_date
1541 , incident_id
1542 , incident_type_id
1543 , inventory_item_id
1544 , inv_organization_id
1545 , incident_severity_id
1546 , customer_id
1547 , owner_group_id
1548 , sr_creation_channel
1549 , resolution_code
1550 , time_to_resolution
1551 , creation_date
1552 , created_by
1553 , last_update_date
1554 , last_updated_by
1555 , last_update_login
1556 , incident_urgency_id
1557 , incident_owner_id
1558 , escalated_flag
1559 , respond_on_date
1560 , respond_by_date
1561 , resolve_by_date
1562 , incident_date
1563 , contract_number
1564
1565 )
1566 values
1567 (
1568 m.report_date
1569 , m.incident_id
1570 , m.incident_type_id
1571 , m.inventory_item_id
1572 , m.inv_organization_id
1573 , m.incident_severity_id
1574 , m.customer_id
1575 , m.owner_group_id
1576 , m.sr_creation_channel
1577 , m.resolution_code
1578 , m.time_to_resolution
1579 , sysdate
1580 , m.last_updated_by
1581 , m.last_update_date
1582 , m.last_updated_by
1583 , m.last_update_login
1584 , m.incident_urgency_id
1585 , m.incident_owner_id
1586 , m.escalated_flag
1587 , m.respond_on_date
1588 , m.respond_by_date
1589 , m.resolve_by_date
1590 , m.incident_date
1591 , m.contract_number
1592 );
1593
1594 l_temp_rowcount := sql%rowcount;
1595
1596 l_rowcount := l_rowcount + l_temp_rowcount;
1597
1598 bis_collection_utilities.log('Merged ' || l_temp_rowcount || ' rows',1);
1599
1600 update biv_dbi_collection_log
1601 set resolution_flag = 'Y'
1602 , resolution_count = l_rowcount
1603 , resolution_time = dbms_utility.get_time - l_timer
1604 , resolution_error_message = null
1605 , last_update_date = sysdate
1606 , last_updated_by = g_user_id
1607 , last_update_login = g_login_id
1608 where rowid = p_log_rowid;
1609
1610 commit;
1611
1612 l_total_rowcount := l_total_rowcount + l_rowcount;
1613
1614 bis_collection_utilities.log('Resolution incremental load complete');
1615
1616 else
1617
1618 bis_collection_utilities.log('Resolution incremental load already complete, skipping');
1619
1620 end if;
1621
1622
1623
1624 x_rowcount := l_total_rowcount;
1625
1626 return 0;
1627
1628 exception
1629 when others then
1630 rollback;
1631 if l_error_message is null then
1632 l_error_message := substr(sqlerrm,1,4000);
1633 end if;
1634 x_error_message := l_error_message;
1635 if l_phase = 1 then
1636 biv_dbi_collection_util.set_log_error( p_rowid => p_log_rowid
1637 , p_staging_error => l_error_message
1638 );
1639 elsif l_phase = 2 then
1640 biv_dbi_collection_util.set_log_error( p_rowid => p_log_rowid
1641 , p_activity_error => l_error_message
1642 );
1643 elsif l_phase = 3 then
1644 biv_dbi_collection_util.set_log_error( p_rowid => p_log_rowid
1645 , p_closed_error => l_error_message
1646 );
1647 elsif l_phase = 4 then
1648 biv_dbi_collection_util.set_log_error( p_rowid => p_log_rowid
1649 , p_backlog_error => l_error_message
1650 );
1651 end if;
1652
1653 commit;
1654
1655 return -1;
1656
1657 end process_incremental;
1658
1659 procedure incremental_load
1660 ( errbuf in out nocopy varchar2
1661 , retcode in out nocopy varchar2) as
1662
1663 l_exception exception;
1664 l_error_message varchar2(4000);
1665 l_biv_schema varchar2(100);
1666
1667 l_log_rowid rowid;
1668 l_process_type varchar2(30);
1669 l_collect_from_date date;
1670 l_collect_to_date date;
1671 l_success_flag varchar2(1);
1672 l_staging_flag varchar2(1);
1673 l_activity_flag varchar2(1);
1674 l_closed_flag varchar2(1);
1675 l_backlog_flag varchar2(1);
1676 l_resolution_flag varchar2(1);
1677
1678 l_target_date date := sysdate;
1679 l_process_success number;
1680 l_rowcount number;
1681
1682 begin
1683
1684 if not bis_collection_utilities.setup( 'BIV_DBI_COLLECTION' ) then
1685 raise g_bis_setup_exception;
1686 end if;
1687
1688 biv_dbi_collection_util.get_last_log
1689 ( l_log_rowid
1690 , l_process_type
1691 , l_collect_from_date
1692 , l_collect_to_date
1693 , l_success_flag
1694 , l_staging_flag
1695 , l_activity_flag
1696 , l_closed_flag
1697 , l_backlog_flag
1698 , l_resolution_flag
1699 );
1700
1701 if l_success_flag is null then
1702 l_error_message := 'Incremental Load can only be run after a completed initial or incremental load';
1703 raise l_exception;
1704 end if;
1705
1706 if l_success_flag = 'N' and
1707 l_process_type <> g_process_type then
1708 l_error_message := 'Incremental Load cannot run as there is an incomplete initial load in progress';
1709 raise l_exception;
1710 end if;
1711
1712 if l_success_flag = 'N' then
1713
1714 bis_collection_utilities.log('Resuming previous incomplete Incremental Load');
1715 bis_collection_utilities.log('From ' || fnd_date.date_to_displaydt(l_collect_from_date),1);
1716 bis_collection_utilities.log('To ' || fnd_date.date_to_displaydt(l_collect_to_date),1);
1717
1718 l_process_success := process_incremental( l_log_rowid
1719 , l_collect_from_date
1720 , l_collect_to_date
1721 , l_staging_flag
1722 , l_activity_flag
1723 , l_closed_flag
1724 , l_backlog_flag
1725 , l_resolution_flag
1726 , l_rowcount
1727 , l_error_message
1728 );
1729 if l_process_success <> 0 then
1730 raise l_exception;
1731 end if;
1732
1733 update biv_dbi_collection_log
1734 set success_flag = 'Y'
1735 , last_update_date = sysdate
1736 , last_updated_by = g_user_id
1737 , last_update_login = g_login_id
1738 where rowid = l_log_rowid;
1739
1740 commit;
1741
1742 bis_collection_utilities.wrapup( p_status => true
1743 , p_period_from => l_collect_from_date
1744 , p_period_to => l_collect_to_date
1745 , p_count => l_rowcount
1746 );
1747
1748 if not bis_collection_utilities.setup( 'BIV_DBI_COLLECTION' ) then
1749 raise g_bis_setup_exception;
1750 end if;
1751
1752 end if;
1753
1754 update biv_dbi_collection_log
1755 set last_collection_flag = 'N'
1756 , last_update_date = sysdate
1757 , last_updated_by = g_user_id
1758 , last_update_login = g_login_id
1759 where rowid = l_log_rowid;
1760
1761 l_collect_from_date := l_collect_to_date + (1/86400);
1762 l_collect_to_date := l_target_date;
1763
1764 insert into biv_dbi_collection_log
1765 ( last_collection_flag
1766 , process_type
1767 , collect_from_date
1768 , collect_to_date
1769 , success_flag
1770 , staging_table_flag
1771 , activity_flag
1772 , closed_flag
1773 , backlog_flag
1774 , resolution_flag
1775 , creation_date
1776 , created_by
1777 , last_update_date
1778 , last_updated_by
1779 , last_update_login
1780 )
1781 values
1782 ( 'Y'
1783 , g_process_type
1784 , l_collect_from_date
1785 , l_collect_to_date
1786 , 'N'
1787 , 'N'
1788 , 'N'
1789 , 'N'
1790 , 'N'
1791 , 'N'
1792 , sysdate
1793 , g_user_id
1794 , sysdate
1795 , g_user_id
1796 , g_login_id
1797 )
1798 returning rowid into l_log_rowid;
1799
1800 bis_collection_utilities.log('Starting new Initial Load');
1801 bis_collection_utilities.log('From ' || fnd_date.date_to_displaydt(l_collect_from_date),1);
1802 bis_collection_utilities.log('To ' || fnd_date.date_to_displaydt(l_collect_to_date),1);
1803
1804 commit;
1805
1806 l_process_success := process_incremental( l_log_rowid
1807 , l_collect_from_date
1808 , l_collect_to_date
1809 , 'N'
1810 , 'N'
1811 , 'N'
1812 , 'N'
1813 , 'N'
1814 , l_rowcount
1815 , l_error_message
1816 );
1817 if l_process_success <> 0 then
1818 raise l_exception;
1819 end if;
1820
1821 update biv_dbi_collection_log
1822 set success_flag = 'Y'
1823 , last_update_date = sysdate
1824 , last_updated_by = g_user_id
1825 , last_update_login = g_login_id
1826 where rowid = l_log_rowid;
1827
1828 commit;
1829
1830 bis_collection_utilities.wrapup( p_status => true
1831 , p_period_from => l_collect_from_date
1832 , p_period_to => l_collect_to_date
1833 , p_count => l_rowcount
1834 );
1835
1836 bis_collection_utilities.log('Incremental Load complete');
1837
1838 exception
1839 when g_bis_setup_exception then
1840 rollback;
1841 errbuf := 'Error in BIS_COLLECTION_UTILITIES.Setup';
1842 retcode := '2';
1843
1844 when others then
1845 rollback;
1846 if l_error_message is null then
1847 l_error_message := substr(sqlerrm,1,4000);
1848 end if;
1849 bis_collection_utilities.wrapup( p_status => false
1850 , p_message => l_error_message
1851 , p_period_from => l_collect_from_date
1852 , p_period_to => l_collect_to_date
1853 );
1854 errbuf := l_error_message;
1855 retcode := '2';
1856
1857 end incremental_load;
1858
1859 procedure incremental_log
1860 ( errbuf in out nocopy varchar2
1861 , retcode in out nocopy varchar2 ) as
1862
1863 begin
1864
1865 -- this is a noop procedure.
1866 -- we need to register a concurrent program to run for incremental
1867 -- refresh for biv_dbi_collection_log. this is because the
1868 -- "Data Last Updated" calculation for reports checks the
1869 -- completion date for the object. without this, the completion
1870 -- date is always the initial load date.
1871
1872 null;
1873
1874 end incremental_log;
1875
1876 end biv_dbi_collection_inc;