[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Insert /*+ append parallel(f) */ into
ISC_MAINT_ASSET_DOWN_F f
(
asset_status_id
,instance_id
,asset_group_id
,category_id
,asset_criticality_code
,organization_id
,department_id
,work_order_id
,operation_seq_number
,description
,enable_flag
,start_date
,end_date
,dbi_start_date
,effective_start_date
,effective_end_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_id
,program_login_id
,program_application_id
,request_id
)
select /*+ parallel(msn) parallel(fact) */
fact.asset_status_id asset_status_id
,fact.maintenance_object_id instance_id
,fact.asset_group_id asset_group_id
,nvl(cii.category_id,-1) category_id
,nvl(cii.asset_criticality_code,'-1') asset_criticality_code
,fact.organization_id oraganization_id
,nvl(eomd.owning_department_id,-1) department_id
,fact.wip_entity_id work_order_id
,fact.operation_seq_num operation_seq_number
,fact.description shutdown_description
,fact.enable_flag enable_flag
,fact.start_date start_date
,fact.end_date end_date
,greatest(fact.start_date,g_global_start_date)
dbi_start_date
,greatest(fact.effective_start_date,g_global_start_date)
effective_start_date
,effective_end_date effective_end_date
,sysdate creation_date
,g_user_id created_by
,sysdate last_update_date
,g_user_id last_updated_by
,g_login_id last_update_login
,g_program_id program_id
,g_program_login_id program_login_id
,g_program_application_id program_application_id
,g_request_id request_id
from
(
select
maintenance_object_id
,start_date
,end_date
,asset_status_id
,asset_group_id
,organization_id
,wip_entity_id
,operation_seq_num
,description
,enable_flag
,last_update_date
,case
when effective_start_date >= effective_end_date then
null
else
effective_start_date
end
effective_start_date
,case
when effective_start_date >= effective_end_date then
null
else
effective_end_date
end
effective_end_date
from
( select
maintenance_object_id
,start_date
,end_date
,asset_status_id
,asset_group_id
,organization_id
,wip_entity_id
,operation_seq_num
,description
,enable_flag
,last_update_date
,case
when start_date > lag(max_so_far,1,start_date-1)
over(partition by maintenance_object_id order by rn) then
start_date
else
lag(max_so_far,1) over(partition by maintenance_object_id order by rn)
end
effective_start_date
,max_so_far effective_end_date
from
( select /*+ parallel(EASH) */
maintenance_object_id
,start_date
,end_date
,asset_status_id
,asset_group_id
,organization_id
,wip_entity_id
,operation_seq_num
,description
,nvl(enable_flag,'Y') enable_flag
,last_update_date
,min(start_date) over(partition by maintenance_object_id order by start_date, end_date)
min_so_far
, max(end_date) over(partition by maintenance_object_id order by start_date, end_date)
max_so_far
, row_number() over(partition by maintenance_object_id order by start_date, end_date)
rn
from EAM_ASSET_STATUS_HISTORY EASH where (enable_flag = 'Y' or enable_flag is NULL ) and
start_date <> end_date
and maintenance_object_type = 3
)
)
)fact
,csi_item_instances cii /* table that contains the instance_id, category and criticality */
,EAM_ORG_MAINT_DEFAULTS eomd /* table containing the owning department */
where
fact.maintenance_object_id = cii.instance_id
and fact.maintenance_object_id = eomd.object_id(+) /* an asset need not be owned to any dept */
and fact.organization_id = eomd.organization_id(+)
and eomd.object_type(+) = 50
and fact.end_date >=g_global_start_date;
bis_collection_utilities.log( l_rowcount || ' rows inserted into base summary', 1 );
select
o.rowid old_rowid
, n.asset_status_id
, n.instance_id
, n.asset_group_id
, n.category_id
, n.asset_criticality_code
, n.organization_id
, n.department_id
, n.work_order_id
, n.operation_seq_number
, n.description
, n.start_date
, n.end_date
, n.dbi_start_date
, n.effective_start_date
, n.effective_end_date
, n.enable_flag
from
(
-- this inline view contains the current state of all enable_flag rows and
-- any rows that have become disabled since the last collection
-- by joining isc_maint_asset_down_f data to mtl_serial_numbers data
-- we also rename the maintenance_object_id to instance_id to remain consistent
-- with all the other regions
select
a.asset_status_id
, a.maintenance_object_id instance_id
, a.asset_group_id
, nvl(cii.category_id,-1) category_id
, nvl(cii.asset_criticality_code,'-1') asset_criticality_code
, a.organization_id
, nvl(eomd.owning_department_id,-1) department_id
, a.wip_entity_id work_order_id
, a.operation_seq_num operation_seq_number
, a.description
, a.start_date
, a.end_date
, a.dbi_start_date
, a.effective_start_date
, a.effective_end_date
, a.enable_flag enable_flag
from
(
-- this inline view contains the current state of all enable_flag rows and
-- any rows that have become disabled since the last collection
-- based only on isc_maint_asset_down_f
select
asset_status_id
, maintenance_object_id
, asset_group_id
, organization_id
, wip_entity_id
, operation_seq_num
, description
, start_date
, end_date
, dbi_start_date
, effective_start_date
, effective_end_date
, enable_flag
from
(
-- this inline view nulls out effective start and effective end dates
-- where they are not meaningful, that is the downtime row is fully
-- overlapped by a prior downtime
select
asset_status_id
, maintenance_object_id
, asset_group_id
, organization_id
, wip_entity_id
, operation_seq_num
, description
, start_date
, end_date
, dbi_start_date
, case
when effective_start_date >= effective_end_date then
null
else
effective_start_date
end effective_start_date
, case
when effective_start_date >= effective_end_date then
null
else
effective_end_date
end effective_end_date
, enable_flag
from
(
-- this inline view calculates the effective start and effective end date.
-- - for each downtime by comparing the dbi start date of the current row
-- with the max so far date of the previous row. if it is less then they
-- overlap, it it is great then there is a new effective start date.
-- - or each downtime the end date is the max so far date
select
asset_status_id
, maintenance_object_id
, asset_group_id
, organization_id
, wip_entity_id
, operation_seq_num
, description
, start_date
, end_date
, dbi_start_date
, case
when enable_flag = 'Y' then
case
when dbi_start_date > lag(max_so_far,1,dbi_start_date-1)
over(partition by maintenance_object_id order by rn) then
dbi_start_date
else
lag(max_so_far,1) over(partition by maintenance_object_id order by rn)
end
else
null
end effective_start_date
, case
when enable_flag = 'Y' then
max_so_far
else
null
end effective_end_date
, enable_flag
from
(
-- this inline view identifies all enable_flag rows and all rows that have
-- been updated since last collection (newly disabled rows)
-- it only considers rows with an end date >= gsd
-- it assumes that data can no longer be updated, the existing row will
-- be disabled and an new row will be inserted.
--
-- - it calculates dbi_start_date
-- - it determines the min start date-to date (min_so_far) for each maintenance_object_id (asset)
-- - it determines the max end date-to date (max_so_far) for each maintenance_object_id (asset)
-- - it determines the logical order of the downtime rows for each maintenance_object_id (asset)
--
-- disabled rows are ranked last
--
select
asset_status_id
, maintenance_object_id
, asset_group_id
, organization_id
, wip_entity_id
, operation_seq_num
, description
, start_date
, end_date
, greatest(start_date,g_global_start_date) dbi_start_date
, nvl(enable_flag,'Y') enable_flag
, min(decode(nvl(enable_flag,'Y'),'Y',greatest(start_date,g_global_start_date),null))
over(partition by maintenance_object_id
order by decode(nvl(enable_flag,'Y'),'Y',start_date,null), decode(nvl(enable_flag,'Y'),'Y',end_date,null)) min_so_far
, max(decode(nvl(enable_flag,'Y'),'Y',end_date,null))
over(partition by maintenance_object_id
order by decode(nvl(enable_flag,'Y'),'Y',start_date,null), decode(nvl(enable_flag,'Y'),'Y',end_date,null)) max_so_far
, row_number()
over(partition by maintenance_object_id
order by decode(nvl(enable_flag,'Y'),'Y',start_date,null), decode(nvl(enable_flag,'Y'),'Y',end_date,null)) rn
from
---
(
select * from eam_asset_status_history
where
(
( creation_date > l_collect_from_date and nvl(enable_flag,'Y') = 'Y' )
or
( (last_update_date > l_collect_from_date and creation_date < l_collect_from_date ) or nvl(enable_flag,'Y') = 'Y' )
)and end_date >= g_global_start_date and start_date <> end_date
and maintenance_object_type = 3
)
)
)
)
) a
, csi_item_instances cii /* extract the instance_id,category and criticality of the asset */
, eam_org_maint_defaults eomd /* extract the department of the asset */
where
a.maintenance_object_id = cii.instance_id
and a.maintenance_object_id = eomd.object_id(+) /* department is not mandatory */
and eomd.object_type(+)= 50 /* bug 4750689 */
and a.organization_id = eomd.organization_id(+)
) n
, isc_maint_asset_down_f o
where
n.asset_status_id = o.asset_status_id(+)
and ( o.asset_status_id is null or
n.category_id <> o.category_id or
n.asset_criticality_code <> o.asset_criticality_code or
n.organization_id <> o.organization_id or
n.department_id <> o.department_id or
nvl(n.work_order_id,-1) <> nvl(o.work_order_id,-1) or
nvl(n.operation_seq_number,-1) <> nvl(o.operation_seq_number,-1) or
nvl(n.description,'%%') <> nvl(o.description,'%%') or
n.start_date <> o.start_date or
n.end_date <> o.end_date or
n.dbi_start_date <> o.dbi_start_date or
nvl(n.effective_start_date,l_max_date) <> nvl(o.effective_start_date,l_max_date) or
nvl(n.effective_end_date,l_max_date) <> nvl(o.effective_end_date,l_max_date) or
nvl(n.enable_flag,'Y') <> nvl(o.enable_flag,'Y')
)
) data
on
(
data.old_rowid = nbmaf.rowid
)
when matched then
update set
-- 3 rows from updation of the mtl_serial_numbers table.
nbmaf.category_id = data.category_id
, nbmaf.asset_criticality_code = data.asset_criticality_code
, nbmaf.department_id = data.department_id
-- 5 rows from updation of the eam_asset_status_history table.
, nbmaf.work_order_id = data.work_order_id
, nbmaf.operation_seq_number = data.operation_seq_number
, nbmaf.description = data.description
, nbmaf.start_date = data.start_date
, nbmaf.end_date = data.end_date
, nbmaf.dbi_start_date = data.dbi_start_date
-- 2 rows for the updation of the asset effective start and end dates due
-- to addition of the n row which might impact the asset effective
--- downtime.
, nbmaf.effective_start_date = data.effective_start_date
, nbmaf.effective_end_date = data.effective_end_date
, nbmaf.enable_flag = data.enable_flag
--- the standard who cols that are to be updated.
, nbmaf.last_update_date = sysdate
, nbmaf.last_updated_by = g_user_id
, nbmaf.last_update_login = g_login_id
, nbmaf.program_id = g_program_id
, nbmaf.program_login_id = g_program_login_id
, nbmaf.program_application_id = g_program_application_id
, nbmaf.request_id = g_request_id
when not matched then
insert
(
asset_status_id
, instance_id
, asset_group_id
, category_id
, asset_criticality_code
, organization_id
, department_id
, work_order_id
, operation_seq_number
, description
, enable_flag
, start_date
, end_date
, dbi_start_date
, effective_start_date
, effective_end_date
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
values
(
data.asset_status_id
, data.instance_id
, data.asset_group_id
, data.category_id
, data.asset_criticality_code
, data.organization_id
, data.department_id
, data.work_order_id
, data.operation_seq_number
, data.description
, data.enable_flag
, data.start_date
, data.end_date
, data.dbi_start_date
, data.effective_start_date
, data.effective_end_date
, 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
);