[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert /*+ append parallel(f) */
into isc_maint_req_wo_f f
( request_type
, maint_request_id
, association_id
, request_number
, organization_id
, department_id
, asset_group_id
, instance_id /* replaced asset_number with instance_id */
, request_start_date
, request_severity_id
, work_order_id
, completion_date
, response_days
, completion_days
, work_order_count
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
--
-- select SR/WO associations
--
select /*+ parallel(x) */
'2' request_type
, maint_request_id
, association_id
, request_number
, nvl(organization_id,-1)
, nvl(department_id,-1)
, nvl(asset_group_id,-1)
, nvl(instance_id,-1)
, request_start_date
, request_severity_id
, work_order_id
, completion_date
, response_days
, completion_days
, work_order_count
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
from
( select /*+ parallel(x) */
maint_request_id
, association_id
, request_number
, organization_id
, department_id
, asset_group_id
, instance_id
, request_start_date
, request_severity_id
, work_order_id
-- if this is the last completion date for all WOs for the SR and
-- the completion date is not null we attribute the completion date
-- to this SR/WO association
, case
when completion_rank = 1 and
completion_datetime <> l_missing_completion_date then
trunc(completion_datetime)
else
null
end completion_date
-- if this is the last completion date for all WOs for the SR and
-- the completion date is not null we attribute the min response days
-- to this SR/WO association
, case
when completion_rank = 1 and
completion_datetime <> l_missing_completion_date then
min_response_days
else
null
end response_days
-- if this is the last completion date for all WOs for the SR and
-- the completion date is not null we attribute the completion days
-- to this SR/WO association
, case
when completion_rank = 1 and
completion_datetime <> l_missing_completion_date then
completion_days
else
null
end completion_days
-- if this is the last completion date for all WOs for the SR and
-- the completion date is not null we attribute the number of work orders
-- to this SR/WO association
, case
when completion_rank = 1 and
completion_datetime <> l_missing_completion_date then
work_order_count
else
null
end work_order_count
from
( select /*+ parallel(i) parallel(a) parallel(w) */
i.incident_id maint_request_id
, a.wo_service_entity_assoc_id association_id
, i.incident_number request_number
, a.maintenance_organization_id organization_id
, i.owning_department_id department_id
, i.inventory_item_id asset_group_id
, i.customer_product_id instance_id
, i.incident_date request_start_date
, i.incident_severity_id request_severity_id
, w.work_order_id
, w.completion_datetime
-- calculate the response days for each SR/WO association
-- this should never be less than 0 days
, greatest(w.wo_creation_datetime - i.incident_date, 0) response_days
-- calculate the completion days for each SR/WO association
-- this should never be less than 0 days
, greatest(w.completion_datetime - i.incident_date, 0) completion_days
-- rank the SR/WO associations for the same SR based on WO completion date,
-- the WO with the latest completion date is ranked first. A null
-- completion date will always outrank a not null completion date
, row_number()
over(partition by i.incident_id
order by nvl(w.completion_datetime,l_missing_completion_date) desc
, a.wo_service_entity_assoc_id) completion_rank
-- determine the min response days for all SR/WO associations for
-- the same SR
, min(greatest(w.wo_creation_datetime - i.incident_date, 0))
over(partition by i.incident_id) min_response_days
, count(*) over(partition by i.incident_id) work_order_count
from
cs_incidents_all_b i
, eam_wo_service_association a
, isc_maint_work_orders_f w
where
i.incident_id = a.service_request_id
and a.wip_entity_id = w.work_order_id
and a.maintenance_organization_id = w.organization_id
and nvl(a.enable_flag,'Y') = 'Y'
-- exclude all cancelled work orders
and w.status_type <> 7
) x
) x
where nvl(completion_date,g_global_start_date) >= g_global_start_date
union all
--
-- select WR/WO associations
--
select /*+ parallel(r) parallel(w) */
'1' request_type
, r.work_request_id maint_request_id
, r.work_request_id association_id
, r.work_request_number request_number
, nvl(r.organization_id,-1) organization_id
, nvl(r.work_request_owning_dept,-1) department_id
, nvl(r.asset_group,-1) asset_group_id
, nvl(r.maintenance_object_id,-1) instance_id
, r.creation_date request_start_date
, nvl(r.work_request_priority_id,-1) request_severity_id
, w.work_order_id
, trunc(w.completion_datetime) completion_date
, case
when w.completion_datetime is not null then
greatest(w.wo_creation_datetime - r.creation_date, 0)
else
null
end response_days
, case
when w.completion_datetime is not null then
greatest(w.completion_datetime - r.creation_date, 0)
else
null
end completion_days
, case
when w.completion_datetime is not null then
1
else
null
end work_order_count
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
from
wip_eam_work_requests r
, isc_maint_work_orders_f w
where
r.wip_entity_id = w.work_order_id
and r.organization_id = w.organization_id
-- only include WR with WO completion_date >= global start date
and nvl(w.completion_date,g_global_start_date) >= g_global_start_date
-- exclude all cancelled work orders
and w.status_type <> 7;
bis_collection_utilities.log( l_rowcount || ' rows inserted into base summary', 1 );
insert into
isc_maint_req_wo_stg
( maint_request_id
, phase_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
select distinct
service_request_id
, 1
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
from eam_wo_service_association a
where a.last_update_date >= l_collect_from_date;
bis_collection_utilities.log( l_rowcount || ' rows inserted staging table from association', 1 );
( select distinct
service_request_id maint_request_id
, 2 phase_id
from eam_wo_service_association a
, isc_maint_work_orders_f w
where
a.wip_entity_id = w.work_order_id
and a.maintenance_organization_id = w.organization_id
and w.last_update_date >= l_collect_from_date
) n
on ( s.maint_request_id = n.maint_request_id )
when matched then
update
set phase_id = s.phase_id + n.phase_id
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_id = g_program_id
, program_login_id = g_program_login_id
, program_application_id = g_program_application_id
, request_id = g_request_id
when not matched then
insert
( maint_request_id
, phase_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
values
( n.maint_request_id
, n.phase_id
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
);
( select distinct
incident_id maint_request_id
, 3 phase_id
from cs_incidents_audit_b a
, cs_incident_types_b t
where
a.creation_date >= l_collect_from_date
and a.incident_type_id = t.incident_type_id
and t.maintenance_flag = 'Y'
and ( ( a.change_incident_type_flag = 'Y' and a.old_incident_type_id is null ) or
a.change_inventory_item_flag = 'Y' or
a.change_inv_organization_flag = 'Y' or
( ( a.item_serial_number is null and a.old_item_serial_number is not null) or
( a.old_item_serial_number is null and a.item_serial_number is not null ) or
( a.old_item_serial_number <> a.item_serial_number ) ) or
( ( a.owning_department_id is null and a.old_owning_department_id is not null) or
( a.old_owning_department_id is null and a.owning_department_id is not null ) or
( a.old_owning_department_id <> a.owning_department_id ) )
)
) n
on ( s.maint_request_id = n.maint_request_id )
when matched then
update
set phase_id = s.phase_id + n.phase_id
, last_update_date = sysdate
, last_updated_by = g_user_id
, last_update_login = g_login_id
, program_id = g_program_id
, program_login_id = g_program_login_id
, program_application_id = g_program_application_id
, request_id = g_request_id
when not matched then
insert
( maint_request_id
, phase_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
values
( n.maint_request_id
, n.phase_id
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
);
select
'2' request_type
, maint_request_id
, association_id
, request_number
, nvl(organization_id,-1) organization_id
, nvl(department_id,-1) department_id
, nvl(asset_group_id,-1) asset_group_id
, nvl(instance_id,-1) instance_id /* replaced asset_number with instance_id */
, request_start_date
, request_severity_id
, work_order_id
-- if this is the last completion date for all WOs for the SR and
-- the completion date is not null we attribute the completion date
-- to this SR/WO association
, case
when completion_rank = 1 and
completion_datetime <> l_missing_completion_date and
completion_datetime <> l_disabled_completion_date then
trunc(completion_datetime)
else
null
end completion_date
-- if this is the last completion date for all WOs for the SR and
-- the completion date is not null we attribute the min response days
-- to this SR/WO association
, case
when completion_rank = 1 and
completion_datetime <> l_missing_completion_date and
completion_datetime <> l_disabled_completion_date then
min_response_days
else
null
end response_days
-- if this is the last completion date for all WOs for the SR and
-- the completion date is not null we attribute the completion days
-- to this SR/WO association
, case
when completion_rank = 1 and
completion_datetime <> l_missing_completion_date and
completion_datetime <> l_disabled_completion_date then
completion_days
else
null
end completion_days
-- if this is the last completion date for all WOs for the SR and
-- the completion date is not null we attribute the number of work orders
-- to this SR/WO association
, case
when completion_rank = 1 and
completion_datetime <> l_missing_completion_date and
completion_datetime <> l_disabled_completion_date then
work_order_count
else
null
end work_order_count
from
( select
i.incident_id maint_request_id
, a.wo_service_entity_assoc_id association_id
, i.incident_number request_number
, a.maintenance_organization_id organization_id
, i.owning_department_id department_id
, i.inventory_item_id asset_group_id
, i.customer_product_id instance_id
, i.incident_date request_start_date
, i.incident_severity_id request_severity_id
, case
when nvl(a.enable_flag,'Y') = 'Y' then
w.work_order_id
else
null
end work_order_id
, case
when nvl(a.enable_flag,'Y') = 'Y' and
w.status_type <> 7 then
w.completion_datetime
else
null
end completion_datetime
-- calculate the response days for each SR/WO association
-- this should never be less than 0 days
, case
when nvl(a.enable_flag,'Y') = 'Y' and
w.status_type <> 7 then
greatest(w.wo_creation_datetime - i.incident_date, 0)
else
null
end response_days
-- calculate the completion days for each SR/WO association
-- this should never be less than 0 days
, case
when nvl(a.enable_flag,'Y') = 'Y' and
w.status_type <> 7 then
greatest(w.completion_datetime - i.incident_date, 0)
else
null
end completion_days
-- rank the SR/WO associations for the same SR based on WO completion date,
-- the WO with the latest completion date is ranked first. A null
-- completion date will always outrank a not null completion date
, row_number()
over(partition by i.incident_id
order by case
when nvl(a.enable_flag,'Y') = 'Y' and
w.status_type <> 7 then
nvl(w.completion_datetime,l_missing_completion_date)
else
l_disabled_completion_date
end desc
, a.wo_service_entity_assoc_id) completion_rank
, min(greatest(case
when nvl(a.enable_flag,'Y') = 'Y' and
w.status_type <> 7 then
w.wo_creation_datetime - i.incident_date
else
999999999999999
end, 0))
over(partition by i.incident_id) min_response_days
, sum( case
when nvl(a.enable_flag,'Y') = 'Y' and
w.status_type <> 7 then
1
else
0
end ) over(partition by i.incident_id) work_order_count
from
cs_incidents_all_b i
, eam_wo_service_association a
, isc_maint_work_orders_f w
, isc_maint_req_wo_stg c
where
i.incident_id = a.service_request_id
and a.wip_entity_id = w.work_order_id
and a.maintenance_organization_id = w.organization_id
and a.service_request_id = c.maint_request_id
)
union all
--
-- select WR/WO associations
--
select
'1' request_type
, r.work_request_id maint_request_id
, r.work_request_id association_id
, r.work_request_number request_number
, nvl(r.organization_id,-1) organization_id
, nvl(r.work_request_owning_dept,-1) department_id
, nvl(r.asset_group,-1) asset_group_id
, nvl(r.maintenance_object_id,-1) instance_id
, r.creation_date request_start_date
, nvl(r.work_request_priority_id,-1) request_severity_id
, w.work_order_id
, case
when w.completion_datetime is not null and
w.status_type <> 7 then
trunc(w.completion_datetime)
else
null
end completion_date
, case
when w.completion_datetime is not null and
w.status_type <> 7 then
greatest(w.wo_creation_datetime - r.creation_date, 0)
else
null
end response_days
, case
when w.completion_datetime is not null and
w.status_type <> 7 then
greatest(w.completion_datetime - r.creation_date, 0)
else
null
end completion_days
, case
when w.completion_datetime is not null and
w.status_type <> 7 then
1
else
null
end work_order_count
from
wip_eam_work_requests r
, isc_maint_work_orders_f w
where
r.wip_entity_id = w.work_order_id(+)
and r.organization_id = w.organization_id(+)
and ( r.last_update_date >= l_collect_from_date or
w.last_update_date >= l_collect_from_date )
) s
on
( f.request_type = s.request_type and
f.maint_request_id = s.maint_request_id and
f.association_id = s.association_id )
when matched then
update
set f.organization_id = s.organization_id
, f.department_id = s.department_id
, f.asset_group_id = s.asset_group_id
, f.instance_id = s.instance_id
, f.request_severity_id = s.request_severity_id
, f.work_order_id = s.work_order_id
, f.completion_date = s.completion_date
, f.response_days = s.response_days
, f.completion_days = s.completion_days
, f.work_order_count = s.work_order_count
, f.last_update_date = sysdate
, f.last_updated_by = g_user_id
, f.last_update_login = g_login_id
, f.program_id = g_program_id
, f.program_login_id = g_program_login_id
, f.program_application_id = g_program_application_id
, f.request_id = g_request_id
when not matched then
insert
( request_type
, maint_request_id
, association_id
, request_number
, organization_id
, department_id
, asset_group_id
, instance_id
, request_start_date
, request_severity_id
, work_order_id
, completion_date
, response_days
, completion_days
, work_order_count
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
values
( s.request_type
, s.maint_request_id
, s.association_id
, s.request_number
, s.organization_id
, s.department_id
, s.asset_group_id
, s.instance_id
, s.request_start_date
, s.request_severity_id
, s.work_order_id
, s.completion_date
, s.response_days
, s.completion_days
, s.work_order_count
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
);