[Home] [Help]
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'
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
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
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
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'
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');
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
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
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
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
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);
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
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
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))
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: )
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:
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. */