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,
grouping_id(f.department_id, f.activity_id, f.asset_group_id, f.instance_id, f.work_order_type) grp_id,
decode(grouping_id(
c.ent_qtr_id
, c.ent_period_id
, c.week_id
, c.report_date_julian ), 0, c.report_date_julian
, 1, c.week_id
, 3, c.ent_period_id
, 7, c.ent_qtr_id
) time_id,
decode(grouping_id(
c.ent_qtr_id
, c.ent_period_id
, c.week_id
, c.report_date_julian ), 0, 1
, 1, 16
, 3, 32
, 7, 64 ) period_type_id,
case when f.days_late >= b.range1_low and (f.days_late < b.range1_high or b.range1_high is null) then 1
when f.days_late >= b.range2_low and (f.days_late < b.range2_high or b.range2_high is null) then 2
when f.days_late >= b.range3_low and (f.days_late < b.range3_high or b.range3_high is null) then 3
when f.days_late >= b.range4_low and (f.days_late < b.range4_high or b.range4_high is null) then 4
when f.days_late >= b.range5_low and (f.days_late < b.range5_high or b.range5_high is null) then 5
when f.days_late >= b.range6_low and (f.days_late < b.range6_high or b.range6_high is null) then 6
when f.days_late >= b.range7_low and (f.days_late < b.range7_high or b.range7_high is null) then 7
when f.days_late >= b.range8_low and (f.days_late < b.range8_high or b.range8_high is null) then 8
when f.days_late >= b.range9_low and (f.days_late < b.range9_high or b.range9_high is null) then 9
else 10
end bucket_num,
count(*) num_count,
/* the following columns prefixed with mv_ exist solely to enable MV to be fast refreshable */
grouping_id(f.organization_id, f.department_id, f.activity_id, f.asset_group_id, f.instance_id, f.work_order_type, c.ent_qtr_id, c.ent_period_id, c.week_id, c.report_date_julian,
case when f.days_late >= b.range1_low and (f.days_late < b.range1_high or b.range1_high is null) then 1
when f.days_late >= b.range2_low and (f.days_late < b.range2_high or b.range2_high is null) then 2
when f.days_late >= b.range3_low and (f.days_late < b.range3_high or b.range3_high is null) then 3
when f.days_late >= b.range4_low and (f.days_late < b.range4_high or b.range4_high is null) then 4
when f.days_late >= b.range5_low and (f.days_late < b.range5_high or b.range5_high is null) then 5
when f.days_late >= b.range6_low and (f.days_late < b.range6_high or b.range6_high is null) then 6
when f.days_late >= b.range7_low and (f.days_late < b.range7_high or b.range7_high is null) then 7
when f.days_late >= b.range8_low and (f.days_late < b.range8_high or b.range8_high is null) then 8
when f.days_late >= b.range9_low and (f.days_late < b.range9_high or b.range9_high is null) then 9
else 10
end
) mv_grouping_id,
c.ent_qtr_id mv_qtr_id,
c.ent_period_id mv_period_id,
c.week_id mv_week_id,
c.report_date_julian mv_day_id
from
ISC.ISC_MAINT_WORK_ORDERS_F f,
BIS.BIS_BUCKET_CUSTOMIZATIONS b,
BIS.BIS_BUCKET bb,
FII.FII_TIME_DAY c
where
f.include_wo = 1 /* Do not include: Pending Close, Failed Close, Cancelled */
and f.completion_date > f.scheduled_completion_date
and bb.short_name = 'BIV_MAINT_LATECMPL_AGING'
and bb.bucket_id = b.bucket_id
and c.report_date = f.completion_date
group by
f.organization_id,
case when f.days_late >= b.range1_low and (f.days_late < b.range1_high or b.range1_high is null) then 1
when f.days_late >= b.range2_low and (f.days_late < b.range2_high or b.range2_high is null) then 2
when f.days_late >= b.range3_low and (f.days_late < b.range3_high or b.range3_high is null) then 3
when f.days_late >= b.range4_low and (f.days_late < b.range4_high or b.range4_high is null) then 4
when f.days_late >= b.range5_low and (f.days_late < b.range5_high or b.range5_high is null) then 5
when f.days_late >= b.range6_low and (f.days_late < b.range6_high or b.range6_high is null) then 6
when f.days_late >= b.range7_low and (f.days_late < b.range7_high or b.range7_high is null) then 7
when f.days_late >= b.range8_low and (f.days_late < b.range8_high or b.range8_high is null) then 8
when f.days_late >= b.range9_low and (f.days_late < b.range9_high or b.range9_high is null) then 9
else 10
end,
rollup(department_id), rollup(activity_id), rollup(asset_group_id, instance_id),
rollup(work_order_type),
c.ent_qtr_id, rollup(c.ent_period_id, c.week_id, c.report_date_julian)
[Home] [Help]