select
f.organization_id organization_id,
f.department_id department_id,
f.asset_group_id asset_group_id,
f.instance_id instance_id,
f.activity_id activity_id,
f.work_order_type work_order_type,
f.user_defined_Status_id user_Defined_Status_id,
trunc(coll.last_update_date) day,
grouping_id(f.department_id, f.asset_group_id, f.instance_id, f.activity_id, f.work_order_type, f.user_defined_Status_id) grp_id,
case
when (trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range1_high or b.range1_high is null then 1
when (trunc(coll.last_update_date) - f.scheduled_completion_date) >= b.range1_high and ((trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range2_high or b.range2_high is null) then 2
when (trunc(coll.last_update_date) - f.scheduled_completion_date) >= b.range2_high and ((trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range3_high or b.range3_high is null) then 3
when (trunc(coll.last_update_date) - f.scheduled_completion_date) >= b.range3_high and ((trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range4_high or b.range4_high is null) then 4
when (trunc(coll.last_update_date) - f.scheduled_completion_date) >= b.range4_high and ((trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range5_high or b.range5_high is null) then 5
when (trunc(coll.last_update_date) - f.scheduled_completion_date) >= b.range5_high and ((trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range6_high or b.range6_high is null) then 6
when (trunc(coll.last_update_date) - f.scheduled_completion_date) >= b.range6_high and ((trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range7_high or b.range7_high is null) then 7
when (trunc(coll.last_update_date) - f.scheduled_completion_date) >= b.range7_high and ((trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range8_high or b.range8_high is null) then 8
when (trunc(coll.last_update_date) - f.scheduled_completion_date) >= b.range8_high and ((trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range9_high or b.range9_high is null) then 9
when (trunc(coll.last_update_date) - f.scheduled_completion_date) >= b.range9_high and ((trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range10_high or b.range10_high is null) then 10
else 0
end bucket_num,
count(*) num_pastdue
from
ISC.ISC_MAINT_WORK_ORDERS_F f,
ISC.ISC_MAINT_WORK_ORDERS_F coll,
BIS.BIS_BUCKET_CUSTOMIZATIONS b,
BIS.BIS_BUCKET bb
WHERE
trunc(coll.last_update_date) > f.scheduled_completion_date and f.completion_date is null and coll.Organization_id = -99 and coll.Work_Order_id = -99 and coll.Entity_Type = -1
and bb.short_name = 'BIV_MAINT_PAST_DUE_AGING'
and bb.bucket_id = b.bucket_id
and f.include_wo = 1 /* Not include: Pending Close, Failed Close, Cancelled */
group by
f.organization_id
,rollup(f.department_id)
,rollup(f.asset_group_id, f.instance_id)
,rollup(f.activity_id)
,rollup(f.work_order_type)
,rollup(f.user_Defined_status_id)
,trunc(coll.last_update_date)
,case
when (trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range1_high or b.range1_high is null then 1
when (trunc(coll.last_update_date) - f.scheduled_completion_date) >= b.range1_high and ((trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range2_high or b.range2_high is null) then 2
when (trunc(coll.last_update_date) - f.scheduled_completion_date) >= b.range2_high and ((trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range3_high or b.range3_high is null) then 3
when (trunc(coll.last_update_date) - f.scheduled_completion_date) >= b.range3_high and ((trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range4_high or b.range4_high is null) then 4
when (trunc(coll.last_update_date) - f.scheduled_completion_date) >= b.range4_high and ((trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range5_high or b.range5_high is null) then 5
when (trunc(coll.last_update_date) - f.scheduled_completion_date) >= b.range5_high and ((trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range6_high or b.range6_high is null) then 6
when (trunc(coll.last_update_date) - f.scheduled_completion_date) >= b.range6_high and ((trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range7_high or b.range7_high is null) then 7
when (trunc(coll.last_update_date) - f.scheduled_completion_date) >= b.range7_high and ((trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range8_high or b.range8_high is null) then 8
when (trunc(coll.last_update_date) - f.scheduled_completion_date) >= b.range8_high and ((trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range9_high or b.range9_high is null) then 9
when (trunc(coll.last_update_date) - f.scheduled_completion_date) >= b.range9_high and ((trunc(coll.last_update_date) - f.scheduled_completion_date) < b.range10_high or b.range10_high is null) then 10
else 0
end
[Home] [Help]