[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
trf.object_id incident_id
, tsk.actual_start_date escalated_date_from
, nvl(tsk.actual_end_date,p_collect_to_date+1) escalated_date_to
from
jtf_tasks_b tsk
, jtf_task_references_b trf
where
trf.object_type_code = 'SR'
and trf.reference_code = 'ESC'
and tsk.task_type_id = 22
and tsk.task_id = trf.task_id
and trf.object_id in ( select /*+ cardinality(stg,10) NO_UNNEST */ incident_id from biv_dbi_collection_stg stg)
and NOT EXISTS
(SELECT null
FROM jtf_task_references_b trf2
where trf2.reference_code = 'ESC'
and trf2.object_type_code = 'SR'
and trf2.object_id = trf.object_id
and trf2.task_id < trf.task_id)
order by 1, 2, 3;
select
audit_date
, incident_type_id
, inventory_item_id
, inv_organization_id
, incident_severity_id
, incident_status_id
, owner_group_id
, status_flag
, sr_creation_channel
, customer_id
, incident_date
, unowned_date
, resolved_flag
, incident_resolved_date
, resolved_event_flag
, unresolved_event_flag
, backlog_rowid
, resolution_code
, incident_urgency_id
, incident_owner_id
from
biv_dbi_collection_stg
where
incident_id = b_incident_id
and audit_date <= b_esc_date
order by
audit_date desc
, incident_audit_id desc;
insert into biv_dbi_collection_stg
( incident_id
, audit_date
, incident_audit_id
, incident_type_id
, inventory_item_id
, inv_organization_id
, incident_severity_id
, incident_status_id
, owner_group_id
, status_flag
, sr_creation_channel
, customer_id
, incident_date
, unowned_date
, resolved_flag
, incident_resolved_date
, resolved_event_flag
, unresolved_event_flag
, resolution_code
, last_for_day_flag
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, incident_urgency_id
, incident_owner_id
, ever_escalated
)
values
( e.incident_id
, trunc(e.escalated_date_from)
, 2
, l_stg_rec.incident_type_id
, l_stg_rec.inventory_item_id
, l_stg_rec.inv_organization_id
, l_stg_rec.incident_severity_id
, l_stg_rec.incident_status_id
, l_stg_rec.owner_group_id
, l_stg_rec.status_flag
, l_stg_rec.sr_creation_channel
, l_stg_rec.customer_id
, l_stg_rec.incident_date
, l_stg_rec.unowned_date
, l_stg_rec.resolved_flag
, l_stg_rec.incident_resolved_date
, l_stg_rec.resolved_event_flag
, l_stg_rec.unresolved_event_flag
, l_stg_rec.resolution_code
, 'Y'
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, l_stg_rec.incident_urgency_id
, l_stg_rec.incident_owner_id
, 'N'
);
insert into biv_dbi_collection_stg
( incident_id
, audit_date
, incident_audit_id
, incident_type_id
, inventory_item_id
, inv_organization_id
, incident_severity_id
, incident_status_id
, owner_group_id
, status_flag
, sr_creation_channel
, customer_id
, incident_date
, unowned_date
, resolved_flag
, incident_resolved_date
, resolved_event_flag
, unresolved_event_flag
, resolution_code
, last_for_day_flag
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, incident_urgency_id
, incident_owner_id
, ever_escalated
)
values
( e.incident_id
, trunc(e.escalated_date_to)
, 2
, l_stg_rec.incident_type_id
, l_stg_rec.inventory_item_id
, l_stg_rec.inv_organization_id
, l_stg_rec.incident_severity_id
, l_stg_rec.incident_status_id
, l_stg_rec.owner_group_id
, l_stg_rec.status_flag
, l_stg_rec.sr_creation_channel
, l_stg_rec.customer_id
, l_stg_rec.incident_date
, l_stg_rec.unowned_date
, null -- RAVI to Verify
, null -- RAVI tp Verify
, 'N'
, 'N'
, l_stg_rec.resolution_code
, 'Y'
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, l_stg_rec.incident_urgency_id
, l_stg_rec.incident_owner_id
, 'N'
);
/* update all of the rows in the staging table where
there audit date is within the escalated date range
but don't update the row for the existing backlog
as we have created a new row for this.
*/
update biv_dbi_collection_stg
set escalated_date = case when ( trunc(audit_date) < trunc(e.escalated_date_to) ) then e.escalated_date_from
else escalated_date
end
, ever_escalated = 'Y'
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
where
incident_id = e.incident_id
and trunc(audit_date) >= trunc(e.escalated_date_from)
and backlog_rowid is null;
bis_collection_utilities.log('insert rows for previous backlog into staging table',1);
insert rows for previous backlog into staging table
insert current audit activity rows into staging table
apply values from the incidents table to staging table
*/
bis_collection_utilities.log('insert current audit activity rows into staging table',1);
insert into biv_dbi_collection_stg
(incident_id
, audit_date
, incident_audit_id
, incident_type_id
, inventory_item_id
, inv_organization_id
, incident_severity_id
, incident_status_id
, owner_group_id
, status_flag
, old_status_flag
, unowned_date
, resolved_flag
, incident_resolved_date
, resolved_event_flag
, unresolved_event_flag
, escalated_date
, backlog_rowid
, first_opened_flag
, reopened_flag
, reopened_date
, closed_flag
, closed_date
, last_for_day_flag
, party_merge_flag
, old_customer_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, incident_urgency_id
, incident_owner_id
, ever_escalated
, incident_date
, customer_id
, sr_creation_channel
, resolution_code
)
select
f.incident_id
, f.backlog_date_from
, 1
, f.incident_type_id
, f.inventory_item_id
, f.inv_organization_id
, f.incident_severity_id
, f.incident_status_id
, f.owner_group_id
, 'O'
, 'O'
, f.unowned_date
, f.resolved_flag
, f.incident_resolved_date
, 'N'
, 'N'
, f.escalated_date
, f.rowid
, null
, null
, null
, null
, null
, 'Y'
, null
, null
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, f.incident_urgency_id
, f.incident_owner_id
, f.escalated_flag
,i.incident_date
, nvl(i.customer_id,-1) /* functionally should not be possible */
, nvl(i.sr_creation_channel,'-1') /* functionally should not be possible */
, nvl(i.resolution_code,'-1') /* valid, resolution code not specified*/
from
biv_dbi_backlog_sum_f f, cs_incidents_all_b i
where
backlog_date_to = l_max_date
and i.incident_id = f.incident_id
union all
select
a.incident_id
, a.creation_date audit_date
, a.incident_audit_id
, nvl(a.incident_type_id,-1) incident_type_id /* workaround bad data */
, nvl2( a.inventory_item_id+a.inv_organization_id
, a.inventory_item_id
, l_missing_inventory_item_id ) inventory_item_id
, nvl2( a.inventory_item_id+a.inv_organization_id
, a.inv_organization_id
, l_missing_organization_id )inv_organization_id
, nvl(a.incident_severity_id,-1) incident_severity_id /* workaround bad data */
, nvl(a.incident_status_id,-1) incident_status_id /* workaround bad data */
, decode(a.group_type, 'RS_GROUP', nvl(a.group_id,l_missing_owner_group_id)
, l_missing_owner_group_id) owner_group_id
, a.status_flag
, a.old_status_flag
, decode( a.incident_owner_id
, null
, nvl(a.owner_assigned_time,nvl(a.incident_date,a.creation_date))
-- based on bug 2993526, if the incident is created
-- with no owner, the initial audit row will have
-- NULL in owner_assigned_time - intended behavior
-- so we need to take incident_date from audit row
-- if for any reason (bad data) this is null, then we take
-- creation_date from row.
, null ) unowned_date
, case when (a.incident_resolved_date is not null and
a.incident_resolved_date <= a.creation_date) then 'Y'
-- when a.status_flag = 'C' then 'Y'
else 'N'
end resolved_flag
, case when (a.incident_resolved_date is not null and
a.incident_resolved_date <= a.creation_date) then a.incident_resolved_date
-- when a.status_flag = 'C' then nvl(a.close_date,a.creation_date)
else null
end incident_resolved_date
, case
when nvl(a.old_incident_resolved_date, a.incident_resolved_date+1) <> a.incident_resolved_date
and a.incident_resolved_date is not null then
'Y'
else
'N'
end resolved_event_flag
, case
when a.old_incident_resolved_date <> NVL(a.incident_resolved_date,a.old_incident_resolved_date+1)
and a.incident_resolved_date is null then
'Y'
else
'N'
end unresolved_event_flag
, null
, null
, case
when a.change_incident_type_flag = 'Y' and a.old_incident_type_id is null then
'Y'
else
'N'
end first_opened_flag
, case
when a.change_status_flag = 'Y' and a.old_status_flag = 'C' and a.status_flag = 'O' then
'Y'
else
'N'
end reopened_flag
, case
when a.change_status_flag = 'Y' and a.old_status_flag = 'C' and a.status_flag = 'O' then
a.creation_date
else
null
end reopened_date
, case
when a.change_status_flag = 'Y' and a.status_flag = 'C' then
'Y'
else
'N'
end closed_flag
, case
when a.change_status_flag = 'Y' and a.status_flag = 'C' then
nvl(a.close_date,a.creation_date)
else
null
end closed_date
, decode( a.incident_audit_id
, last_value(a.incident_audit_id)
over ( partition by a.incident_id, trunc(a.creation_date)
-- modified order by based on conclusions found in bug 3524935
order by decode(a.old_status_flag,null,1,2)
, a.creation_date
, a.incident_audit_id
rows between unbounded preceding and unbounded following )
, 'Y'
, 'N' ) last_for_day_flag
, case
when a.old_customer_id is not null and
a.customer_id is not null and
a.old_customer_id <> a.customer_id then
'Y'
else
'N'
end party_merge_flag
, a.old_customer_id
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, nvl(a.incident_urgency_id,-1) incident_urgency_id /* workaround bad data */
, decode(a.resource_type, 'RS_EMPLOYEE', nvl(a.incident_owner_id,-1) , -1) incident_owner_id
, 'N'
,i.incident_date
, nvl(i.customer_id,-1) /* functionally should not be possible */
, nvl(i.sr_creation_channel,'-1') /* functionally should not be possible */
, nvl(i.resolution_code,'-1') /* valid, resolution code not specified*/
from
cs_incidents_audit_b a , cs_incidents_all_b i
where
a.creation_date between p_collect_from_date and p_collect_to_date
and i.incident_id = a.incident_id
and nvl(a.updated_entity_code, 'SR_HEADER') IN ('SR_HEADER','SR_ESCALATION')
and ('Y' in ( a.change_status_flag
, a.change_incident_status_flag
, a.change_incident_type_flag
, a.change_incident_severity_flag
, a.change_inventory_item_flag
, a.change_inv_organization_flag
, a.change_incident_owner_flag
, a.change_incident_urgency_flag
, a.change_group_flag
, case
when a.old_customer_id is not null and
a.customer_id is not null and
a.old_customer_id <> a.customer_id then
'Y'
else
'N'
end
--Start bug#4932634
) or NVL(a.old_incident_resolved_date,trunc(sysdate)) <> a.incident_resolved_date );
bis_collection_utilities.log('Inserted ' || l_rowcount || ' rows',2);
bis_collection_utilities.log('Inserted ' || l_temp_rowcount || ' rows',2);
update biv_dbi_collection_stg
set status_flag = lower(status_flag)
where rowid in ( select rowid
from
( select
incident_id || '^' ||
incident_type_id || '^' ||
inventory_item_id || '^' ||
inv_organization_id || '^' ||
incident_severity_id || '^' ||
incident_status_id || '^' ||
owner_group_id || '^' ||
unowned_date || '^' ||
resolved_flag || '^' ||
incident_resolved_date || '^'||
resolved_event_flag || '^'||
unresolved_event_flag || '^'||
escalated_date || '^'||
incident_urgency_id || '^'||
incident_owner_id || '^'||
ever_escalated as conc_key
, lag(incident_id || '^' ||
incident_type_id || '^' ||
inventory_item_id || '^' ||
inv_organization_id || '^' ||
incident_severity_id || '^' ||
incident_status_id || '^' ||
owner_group_id || '^' ||
unowned_date || '^' ||
resolved_flag || '^' ||
incident_resolved_date || '^'||
resolved_event_flag || '^'||
unresolved_event_flag || '^'||
escalated_date || '^'||
incident_urgency_id|| '^'||
incident_owner_id || '^'||
ever_escalated
,1,'^')
over (order by
incident_id
, audit_date
, incident_audit_id) prev_conc_key
from
biv_dbi_collection_stg s
where
last_for_day_flag = 'Y'
)
where conc_key = prev_conc_key
);
bis_collection_utilities.log('Updated ' || l_temp_rowcount || ' rows',2);
update biv_dbi_collection_log
set staging_table_flag = 'Y'
, staging_table_count = l_rowcount
, staging_table_time = dbms_utility.get_time - l_timer
, staging_error_message = null
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
where rowid = p_log_rowid;
select distinct
old_customer_id from_party
, customer_id to_party
bulk collect into l_from_party_tab
, l_to_party_tab
from
biv_dbi_collection_stg stg
where
party_merge_flag = 'Y';
update biv_dbi_activity_sum_f
set
primary_flag = 'N'
, customer_id = l_to_party_tab(i)
, last_updated_by = g_user_id
, last_update_date = sysdate
where
customer_id = l_from_party_tab(i);
bis_collection_utilities.log('Party Merge updated ' || l_rowcount || ' rows',1);
select /*+ no_merge cardinality (stg,10) */
trunc(audit_date) activity_date
, incident_type_id
, inventory_item_id
, inv_organization_id
, incident_severity_id
, customer_id
, owner_group_id
, sr_creation_channel
, sum(decode(first_opened_flag,'Y',1,0)) first_opened_count
, sum(decode(reopened_flag,'Y',1,0)) reopened_count
, sum(decode(closed_flag,'Y',1,0)) closed_count
, sysdate update_date
, g_user_id user_id
, g_login_id login_id
, incident_urgency_id
, incident_owner_id
, ever_escalated escalated_flag
from
biv_dbi_collection_stg stg
where
'Y' in ( first_opened_flag, reopened_flag, closed_flag )
and audit_date <= trunc(p_collect_from_date)+(86399/86400)
group by
trunc(audit_date)
, incident_type_id
, inventory_item_id
, inv_organization_id
, incident_severity_id
, customer_id
, owner_group_id
, sr_creation_channel
, incident_urgency_id
, incident_owner_id
, ever_escalated
) m
on ( a.activity_date = m.activity_date and
a.incident_type_id = m.incident_type_id and
a.inventory_item_id = m.inventory_item_id and
a.inv_organization_id = m.inv_organization_id and
a.incident_severity_id = m.incident_severity_id and
a.customer_id = m.customer_id and
a.owner_group_id = m.owner_group_id and
a.sr_creation_channel = m.sr_creation_channel and
a.primary_flag = 'Y'and
a.incident_urgency_id = m.incident_urgency_id and
a.incident_owner_id = m.incident_owner_id and
a.escalated_flag = m.escalated_flag
)
when matched then
update
set a.first_opened_count = a.first_opened_count + m.first_opened_count
, a.reopened_count = a.reopened_count + m.reopened_count
, a.closed_count = a.closed_count + m.closed_count
, a.last_update_date = m.update_date
, a.last_updated_by = m.user_id
, a.last_update_login = m.login_id
when not matched then
insert
( activity_date
, incident_type_id
, inventory_item_id
, inv_organization_id
, incident_severity_id
, customer_id
, owner_group_id
, sr_creation_channel
, primary_flag
, first_opened_count
, reopened_count
, closed_count
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, incident_urgency_id
, incident_owner_id
, escalated_flag
)
values
( m.activity_date
, m.incident_type_id
, m.inventory_item_id
, m.inv_organization_id
, m.incident_severity_id
, m.customer_id
, m.owner_group_id
, m.sr_creation_channel
, 'Y'
, m.first_opened_count
, m.reopened_count
, m.closed_count
, m.update_date
, m.user_id
, m.update_date
, m.user_id
, m.login_id
, m.incident_urgency_id
, m.incident_owner_id
, m.escalated_flag
);
bis_collection_utilities.log('Insert activity from ' ||
fnd_date.date_to_displaydt(trunc(p_collect_from_date-(1/86400))+1) ||
' to ' ||
fnd_date.date_to_displaydt(p_collect_to_date)
,1);
insert
into biv_dbi_activity_sum_f a
( activity_date
, incident_type_id
, inventory_item_id
, inv_organization_id
, incident_severity_id
, customer_id
, owner_group_id
, sr_creation_channel
, primary_flag
, first_opened_count
, reopened_count
, closed_count
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, incident_urgency_id
, incident_owner_id
, escalated_flag
)
select
trunc(audit_date) activity_date
, incident_type_id
, inventory_item_id
, inv_organization_id
, incident_severity_id
, customer_id
, owner_group_id
, sr_creation_channel
, 'Y'
, sum(decode(first_opened_flag,'Y',1,0)) first_opened_count
, sum(decode(reopened_flag,'Y',1,0)) reopened_count
, sum(decode(closed_flag,'Y',1,0)) closed_count
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, incident_urgency_id
, incident_owner_id
, ever_escalated
from
biv_dbi_collection_stg stg
where
'Y' in ( first_opened_flag, reopened_flag, closed_flag )
and audit_date >= trunc(p_collect_from_date-(1/86400))+1
group by
trunc(audit_date)
, incident_type_id
, inventory_item_id
, inv_organization_id
, incident_severity_id
, customer_id
, owner_group_id
, sr_creation_channel
, incident_urgency_id
, incident_owner_id
, ever_escalated;
bis_collection_utilities.log('Inserted ' || l_temp_rowcount || ' rows',2);
update biv_dbi_collection_log
set activity_flag = 'Y'
, activity_count = l_rowcount
, activity_time = dbms_utility.get_time - l_timer
, activity_error_message = null
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
where rowid = p_log_rowid;
update biv_dbi_closed_sum_f
set
customer_id = l_to_party_tab(i)
, last_updated_by = g_user_id
, last_update_date = sysdate
where
customer_id = l_from_party_tab(i);
bis_collection_utilities.log('Party Merge updated ' || l_rowcount || ' rows',1);
select /*+ cardinality (stg,10) */
incident_id
, max(closed_date)
keep (dense_rank last order by audit_date, incident_audit_id) closed_date
, max(reopened_date)
keep (dense_rank last order by audit_date, incident_audit_id) reopened_date
, max(incident_type_id)
keep (dense_rank last order by audit_date, incident_audit_id) incident_type_id
, max(inventory_item_id)
keep (dense_rank last order by audit_date, incident_audit_id) inventory_item_id
, max(inv_organization_id)
keep (dense_rank last order by audit_date, incident_audit_id) inv_organization_id
, max(incident_severity_id)
keep (dense_rank last order by audit_date, incident_audit_id) incident_severity_id
, max(customer_id)
keep (dense_rank last order by audit_date, incident_audit_id) customer_id
, max(owner_group_id)
keep (dense_rank last order by audit_date, incident_audit_id) owner_group_id
, max(sr_creation_channel)
keep (dense_rank last order by audit_date, incident_audit_id) sr_creation_channel
, max(resolution_code)
keep (dense_rank last order by audit_date, incident_audit_id) resolution_code
, max(closed_date - incident_date)
keep (dense_rank last order by audit_date, incident_audit_id) time_to_close
, sysdate update_date
, g_user_id user_id
, g_login_id login_id
, max(incident_urgency_id)
keep (dense_rank last order by audit_date, incident_audit_id) incident_urgency_id
, max(incident_owner_id)
keep (dense_rank last order by audit_date, incident_audit_id) incident_owner_id
, max(ever_escalated)
keep (dense_rank last order by audit_date, incident_audit_id) escalated_flag
from
biv_dbi_collection_stg stg
where
('Y' in (closed_flag) and (old_status_flag = 'O' or old_status_flag is null)
/* workaround for bad data where old_status_flag can be null.*/
or 'Y' in (reopened_flag))
/* to update those SR's that have been reopened so that they are not displayed in the report. */
group by incident_id
) m
on ( c.incident_id = m.incident_id )
when matched then
update
set report_date = decode(m.reopened_date,null,trunc(m.closed_date),c.report_date)
, reopened_date = m.reopened_date
, incident_type_id = decode(m.reopened_date,null,m.incident_type_id,c.incident_type_id)
, inventory_item_id = decode(m.reopened_date,null,m.inventory_item_id,c.inventory_item_id)
, inv_organization_id = decode(m.reopened_date,null,m.inv_organization_id,c.inv_organization_id)
, incident_severity_id = decode(m.reopened_date,null,m.incident_severity_id,c.incident_severity_id)
, customer_id = decode(m.reopened_date,null,m.customer_id,c.customer_id)
, owner_group_id = decode(m.reopened_date,null,m.owner_group_id,c.owner_group_id)
, sr_creation_channel = decode(m.reopened_date,null,m.sr_creation_channel,c.sr_creation_channel)
, resolution_code = decode(m.reopened_date,null,m.resolution_code,c.resolution_code)
, time_to_close = decode(m.reopened_date,null,m.time_to_close,c.time_to_close)
, last_update_date = m.update_date
, last_updated_by = m.user_id
, last_update_login = m.login_id
, incident_urgency_id = decode(m.reopened_date,null,m.incident_urgency_id,c.incident_urgency_id)
, incident_owner_id = decode(m.reopened_date,null,m.incident_owner_id,c.incident_owner_id)
, escalated_flag = decode(m.reopened_date,null,m.escalated_flag,c.escalated_flag)
when not matched then
insert
( report_date
, incident_id
, incident_type_id
, inventory_item_id
, inv_organization_id
, incident_severity_id
, customer_id
, owner_group_id
, sr_creation_channel
, resolution_code
, time_to_close
, reopened_date
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, incident_urgency_id
, incident_owner_id
, escalated_flag
)
values
( trunc(nvl(m.closed_date,m.reopened_date))
, m.incident_id
, m.incident_type_id
, m.inventory_item_id
, m.inv_organization_id
, m.incident_severity_id
, m.customer_id
, m.owner_group_id
, m.sr_creation_channel
, m.resolution_code
, m.time_to_close
, m.reopened_date
, m.update_date
, m.user_id
, m.update_date
, m.user_id
, m.login_id
, m.incident_urgency_id
, m.incident_owner_id
, m.escalated_flag
);
update biv_dbi_collection_log
set closed_flag = 'Y'
, closed_count = l_rowcount
, closed_time = dbms_utility.get_time - l_timer
, closed_error_message = null
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
where rowid = p_log_rowid;
update biv_dbi_backlog_sum_f
set
customer_id = l_to_party_tab(i)
, last_updated_by = g_user_id
, last_update_date = sysdate
where
customer_id = l_from_party_tab(i);
bis_collection_utilities.log('Party merge updated ' || l_rowcount || ' rows',1);
select
backlog_rowid
, backlog_date_to
bulk collect into l_backlog_rowid_tab
, l_backlog_date_to_tab
from
( select
backlog_rowid
, audit_date
, lead(trunc(audit_date)-1,1,l_max_date)
over(partition by incident_id
order by audit_date, incident_audit_id) backlog_date_to
from
biv_dbi_collection_stg stg
where
status_flag in ('O', 'C')
and last_for_day_flag = 'Y'
)
where
backlog_rowid is not null;
/* update all existing backlog rows with there new end dates
*/
forall i in 1..l_backlog_rowid_tab.count
update /*+ rowid(f) */ biv_dbi_backlog_sum_f f
set backlog_date_to = l_backlog_date_to_tab(i)
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
where
rowid = l_backlog_rowid_tab(i)
and backlog_date_to <> l_backlog_date_to_tab(i);
bis_collection_utilities.log('Updated ' || l_temp_rowcount || ' rows',2);
bis_collection_utilities.log('Inserting new backlog rows',1);
/* insert new backlog rows
*/
insert
first
when status_flag = 'O' and
backlog_rowid is null then
into biv_dbi_backlog_sum_f
( backlog_date_from
, backlog_date_to
, incident_id
, incident_type_id
, inventory_item_id
, inv_organization_id
, incident_severity_id
, incident_status_id
, customer_id
, owner_group_id
, sr_creation_channel
, incident_date
, escalated_date
, unowned_date
, resolved_flag
, incident_resolved_date
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, escalated_flag
, incident_urgency_id
, incident_owner_id
)
values
( backlog_date_from
, backlog_date_to
, incident_id
, incident_type_id
, inventory_item_id
, inv_organization_id
, incident_severity_id
, incident_status_id
, customer_id
, owner_group_id
, sr_creation_channel
, incident_date
, escalated_date
, unowned_date
, resolved_flag
, incident_resolved_date
, last_update_date
, last_updated_by
, last_update_date
, last_updated_by
, last_update_login
, escalated_flag
, incident_urgency_id
, incident_owner_id
)
select
status_flag
, backlog_rowid
, trunc(audit_date) backlog_date_from
, lead(trunc(audit_date)-1,1,l_max_date)
over(partition by incident_id
order by audit_date, incident_audit_id) backlog_date_to
, incident_id
, incident_type_id
, inventory_item_id
, inv_organization_id
, incident_severity_id
, incident_status_id
, customer_id
, owner_group_id
, sr_creation_channel
, incident_date
, escalated_date
, unowned_date
, resolved_flag
, incident_resolved_date
, sysdate last_update_date
, g_user_id last_updated_by
, g_login_id last_update_login
-- , case when (escalated_date <= audit_date) then 'Y' else 'N' end escalated_flag
, ever_escalated escalated_flag
, incident_urgency_id
, incident_owner_id
from
biv_dbi_collection_stg stg
where
status_flag in ('O','C')
and last_for_day_flag = 'Y';
bis_collection_utilities.log('Inserted ' || l_temp_rowcount || ' rows',2);
update biv_dbi_collection_log
set backlog_flag = 'Y'
, backlog_count = l_rowcount
, backlog_time = dbms_utility.get_time - l_timer
, backlog_error_message = null
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
where rowid = p_log_rowid;
update biv_dbi_resolution_sum_f
set
customer_id = l_to_party_tab(i)
, last_updated_by = g_user_id
, last_update_date = sysdate
where
customer_id = l_from_party_tab(i);
bis_collection_utilities.log('Party Merge updated ' || l_rowcount || ' rows',1);
select
CASE WHEN incident_resolved_date is null or incident_resolved_date < incident_date
THEN last_update_date
ELSE incident_resolved_date
END report_date,
incident_id,
incident_type_id,
nvl2( inventory_item_id+inv_organization_id
, inventory_item_id
, l_missing_inventory_item_id ) inventory_item_id,
nvl2( inventory_item_id+inv_organization_id
, inv_organization_id
, l_missing_organization_id )inv_organization_id,
incident_severity_id,
nvl(customer_id,-1) customer_id,
decode(group_type, 'RS_GROUP', nvl(owner_group_id,l_missing_owner_group_id)
, l_missing_owner_group_id) owner_group_id,
nvl(sr_creation_channel,'-1') sr_creation_channel,
nvl(resolution_code,'-1') resolution_code,
CASE WHEN (incident_resolved_date IS NOT NULL)
THEN
case when incident_resolved_date < incident_date then
last_update_date
else
incident_resolved_date
end
ELSE
NULL
END - incident_date time_to_resolution,
sysdate last_update_date,
g_user_id last_updated_by,
g_login_id last_update_login,
incident_urgency_id,
decode(resource_type, 'RS_EMPLOYEE', nvl(incident_owner_id,-2)
, -2) incident_owner_id,
escalated_flag,
respond_on_date,
respond_by_date,
resolve_by_date,
incident_date,
contract_number
FROM
(
select
i.incident_id,
CASE
WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN
CASE WHEN(a.a_incident_resolved_date < i.incident_date) then i.incident_date
-- WHEN(a.a_incident_resolved_date > i.close_date ) then i.close_date
ELSE a.a_incident_resolved_date
END
/* From 8.0 SR's that are resolved only are taken into the resolution fact.
WHEN (i.status_flag = 'C') THEN
case
when i.close_date is null or i.close_date < i.incident_date then
i.last_update_date
else
i.close_date
end */
ELSE NULL
END incident_resolved_date,
CASE
WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_incident_type_id
ELSE incident_type_id
END incident_type_id,
CASE
WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_inventory_item_id
ELSE inventory_item_id
END inventory_item_id,
CASE
WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_inv_organization_id
ELSE inv_organization_id
END inv_organization_id,
CASE
WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_incident_severity_id
ELSE incident_severity_id
END incident_severity_id,
i.customer_id,
/* From 8.0 SR's that are resolved only are taken into the resolution fact.
CASE
WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_group_type
WHEN (i.status_flag = 'C') THEN group_type
ELSE NULL
END*/ group_type,
i.sr_creation_channel,
CASE
WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_resolution_code
ELSE resolution_code
END resolution_code,
i.last_update_date,
i.owner_group_id,
i.incident_date,
CASE
WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_incident_urgency_id
-- WHEN (i.status_flag = 'C') THEN incident_urgency_id
ELSE NULL
END incident_urgency_id,
i.resource_type,
CASE
WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_incident_owner_id
-- WHEN (i.status_flag = 'C') THEN incident_owner_id
ELSE NULL
END incident_owner_id,
CASE
WHEN(i.incident_resolved_date = a.a_incident_resolved_date) THEN a_escalated_flag
/* WHEN (i.status_flag = 'C') THEN
CASE WHEN e.escalated_date_from <= i.close_date THEN 'Y' ELSE 'N' END */
ELSE 'N'
END escalated_flag,
i.inc_responded_by_date respond_on_date,
i.obligation_date respond_by_date,
i.expected_resolution_date resolve_by_date,
i.contract_number contract_number
from
(
select /*+ cardinality(stg, 10) */
incident_id a_incident_id
, max(incident_resolved_date)
keep (dense_rank last order by audit_date, incident_audit_id) a_incident_resolved_date
, max(incident_type_id)
keep (dense_rank last order by audit_date, incident_audit_id) a_incident_type_id
, max(inventory_item_id)
keep (dense_rank last order by audit_date, incident_audit_id) a_inventory_item_id
, max(inv_organization_id)
keep (dense_rank last order by audit_date, incident_audit_id) a_inv_organization_id
, max(incident_severity_id)
keep (dense_rank last order by audit_date, incident_audit_id) a_incident_severity_id
, max(owner_group_id)
keep (dense_rank last order by audit_date, incident_audit_id) a_owner_group_id
, max(sr_creation_channel)
keep (dense_rank last order by audit_date, incident_audit_id) a_sr_creation_channel
, max(resolution_code)
keep (dense_rank last order by audit_date, incident_audit_id) a_resolution_code
, max(incident_urgency_id)
keep (dense_rank last order by audit_date, incident_audit_id) a_incident_urgency_id
, max(incident_owner_id)
keep (dense_rank last order by audit_date, incident_audit_id) a_incident_owner_id
, max(ever_escalated)
keep (dense_rank last order by audit_date, incident_audit_id) a_escalated_flag
from
biv_dbi_collection_stg stg
where ('Y' in (resolved_flag) and (old_status_flag = 'O' or old_status_flag is null)
/* workaround for bad data where old_status_flag can be null.*/
or 'Y' in (reopened_flag))
/* to update those SR's that have been reopened so that they are not displayed in the report. */
group by incident_id
) a, cs_incidents_all_b i,
(
select trf.object_id, tsk.task_id, trunc(tsk.actual_start_date) escalated_date_from
, trunc(nvl(tsk.actual_end_date,to_date('01-12-4712','DD-MM-YYYY'))) escalated_date_to
, tsk.actual_start_date escalated_date
, CASE WHEN trunc(tsk.actual_start_date) = trunc(nvl(tsk.actual_end_date,to_date('01-12-4712','DD-MM-YYYY')))
THEN
'Y'
ELSE
'N'
END de_escalated_same_day
from
jtf_tasks_b tsk, jtf_task_references_b trf
where trf.object_type_code = 'SR'
and trf.reference_code = 'ESC'
and tsk.task_type_id = 22
and trf.task_id = tsk.task_id
and NOT EXISTS
(SELECT null
FROM jtf_task_references_b trf2
where trf2.reference_code = 'ESC'
and trf2.object_type_code = 'SR'
and trf2.object_id = trf.object_id
and trf2.task_id < trf.task_id)
) e
where a.a_incident_id = i.incident_id
and e.object_id(+) = i.incident_id
)
) m
on ( c.incident_id = m.incident_id )
when matched then
update
set
report_date = m.report_date
, incident_type_id = m.incident_type_id
, inventory_item_id = m.inventory_item_id
, inv_organization_id = m.inv_organization_id
, incident_severity_id = m.incident_severity_id
, customer_id = m.customer_id
, owner_group_id = m.owner_group_id
, sr_creation_channel = sr_creation_channel
, resolution_code = resolution_code
, time_to_resolution = m.time_to_resolution
, last_update_date = m.last_update_date
, last_updated_by = m.last_updated_by
, last_update_login = m.last_update_login
, incident_urgency_id = m.incident_urgency_id
, incident_owner_id = m.incident_owner_id
, escalated_flag = m.escalated_flag
, respond_on_date = m.respond_on_date
, respond_by_date = m.respond_by_date
, resolve_by_date= m.resolve_by_date
, incident_date = m.incident_date
, contract_number = m.contract_number
when not matched then
insert
(
report_date
, incident_id
, incident_type_id
, inventory_item_id
, inv_organization_id
, incident_severity_id
, customer_id
, owner_group_id
, sr_creation_channel
, resolution_code
, time_to_resolution
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, incident_urgency_id
, incident_owner_id
, escalated_flag
, respond_on_date
, respond_by_date
, resolve_by_date
, incident_date
, contract_number
)
values
(
m.report_date
, m.incident_id
, m.incident_type_id
, m.inventory_item_id
, m.inv_organization_id
, m.incident_severity_id
, m.customer_id
, m.owner_group_id
, m.sr_creation_channel
, m.resolution_code
, m.time_to_resolution
, sysdate
, m.last_updated_by
, m.last_update_date
, m.last_updated_by
, m.last_update_login
, m.incident_urgency_id
, m.incident_owner_id
, m.escalated_flag
, m.respond_on_date
, m.respond_by_date
, m.resolve_by_date
, m.incident_date
, m.contract_number
);
update biv_dbi_collection_log
set resolution_flag = 'Y'
, resolution_count = l_rowcount
, resolution_time = dbms_utility.get_time - l_timer
, resolution_error_message = null
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
where rowid = p_log_rowid;
update biv_dbi_collection_log
set success_flag = 'Y'
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
where rowid = l_log_rowid;
update biv_dbi_collection_log
set last_collection_flag = 'N'
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
where rowid = l_log_rowid;
insert into biv_dbi_collection_log
( last_collection_flag
, process_type
, collect_from_date
, collect_to_date
, success_flag
, staging_table_flag
, activity_flag
, closed_flag
, backlog_flag
, resolution_flag
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
values
( 'Y'
, g_process_type
, l_collect_from_date
, l_collect_to_date
, 'N'
, 'N'
, 'N'
, 'N'
, 'N'
, 'N'
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
)
returning rowid into l_log_rowid;
update biv_dbi_collection_log
set success_flag = 'Y'
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
where rowid = l_log_rowid;