MATERIALIZED VIEW: APPS.ISC_MAINT_004_MV

Source

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.activity_id, f.asset_group_id, f.instance_id) grp_id,
	decode(grouping_id( c.ent_year_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
                                            , 15, c.ent_year_id) time_id,	
	decode(grouping_id( c.ent_year_id
                    , c.ent_qtr_id
                    , c.ent_period_id
                    , c.week_id
                    , c.report_date_julian ), 0, 1
                                            , 1, 16
                                            , 3, 32
                                            , 7, 64
                                            , 15, 128) period_type_id,
	sum(decode(m.id, 1, decode(completion_date, null, 0, 1), 0)) num_completion,
        sum(decode(m.id, 1, case when nvl(completion_date, f.scheduled_completion_date) > f.scheduled_completion_date then 1 else 0 end)) num_past_due_cmpl,
	sum(decode(m.id, 1, case when days_late > 0 then 1 else 0 end)) num_late_completion,
	sum(decode(m.id, 1, case when days_late > 0 then days_late else 0 end)) days_late,
        sum(decode(m.id, 3, case when nvl(completion_date, f.scheduled_completion_date + 1)  > f.scheduled_completion_date then 1 else 0 end)) num_past_due,
	sum(decode(m.id, 2, decode(wo_creation_date, null, 0, 1), 0)) num_created,
	/* 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_year_id, c.ent_qtr_id, c.ent_period_id, c.week_id, c.report_date_julian) mv_grouping_id,
        c.ent_year_id           mv_year_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,
	count(decode(m.id, 1, case when nvl(completion_date, f.scheduled_completion_date) > f.scheduled_completion_date then 1 else 0 end)) mv_num_past_due_cmpl,
	count(decode(m.id, 1, decode(completion_date, null, 0, 1), 0)) mv_num_completion,
        count(decode(m.id, 2, decode(wo_creation_date, null, 0, 1), 0)) mv_num_created,
	count(decode(m.id, 1, case when days_late > 0 then 1 else 0 end)) mv_num_late_completion,
	count(decode(m.id, 1, case when days_late > 0 then days_late else 0 end)) mv_days_late,
        count(decode(m.id, 3, case when nvl(completion_date, f.scheduled_completion_date + 1)  > f.scheduled_completion_date then 1 else 0 end)) mv_num_past_due,
	count(*) mv_cnt
from
	ISC.ISC_MAINT_WORK_ORDERS_F f,
        OKI.OKI_DBI_MULTIPLEXER_B m,
        FII.FII_TIME_DAY c
where
        f.include_wo = 1 /* Do not include: Pending Close, Failed Close, Cancelled */ 
and     m.id < 4
and     c.report_date = decode(m.id, 1, f.dbi_completion_date, 2, f.wo_creation_date, 3, f.dbi_scheduled_completion_date+1)
group by
        f.organization_id, 
        department_id, rollup(activity_id), rollup(asset_group_id, instance_id), 
        work_order_type, 
        c.ent_year_id,
        rollup(c.ent_qtr_id, c.ent_period_id, c.week_id, c.report_date_julian)