[Home] [Help]
MATERIALIZED VIEW: APPS.ISC_DR_BKLG_01_MV
Source
select
/*+ use_hash(ftd fact eoi bb bbc) */
/* 12.0: bug#4526784 */
fact.repair_organization_id repair_organization_id
, fact.item_org_id item_org_id
, nvl(eoi.vbh_category_id,-1) product_category_id
, repair_type_id repair_type_id
, customer_id customer_id
, decode( grouping_id( ftd.ent_year_id
, ftd.ent_qtr_id
, ftd.ent_period_id
, ftd.week_id
, ftd.report_date_julian
)
, 0, ftd.report_date_julian
, 1, ftd.week_id
, 3, ftd.ent_period_id
, 7, ftd.ent_qtr_id
, 15, ftd.ent_year_id
) time_id
, decode( grouping_id( ftd.ent_year_id
, ftd.ent_qtr_id
, ftd.ent_period_id
, ftd.week_id
, ftd.report_date_julian
)
, 0, 1
, 1, 16
, 3, 32
, 7, 64
, 15, 128
) period_type_id
, ftd.ent_year_id year_id
, ftd.ent_qtr_id qtr_id
, ftd.ent_period_id month_id
, ftd.week_id week_id
, ftd.report_date_julian day_id
, count( decode( fact.dbi_ro_creation_date
, ftd.report_date, 1
, null
)
) open_count
, count( decode( fact.dbi_date_closed
, ftd.report_date, 1
, null
)
) close_count
, count( case
when (fact.dbi_date_closed is null or fact.dbi_date_closed > promise_date) and
dbi_promise_date + 1 = report_date then
1
else null
end
) past_due_open_count
, count( case
when fact.dbi_date_closed > promise_date and
fact.dbi_date_closed = report_date then
1
else null
end
) late_complete_count
, count( case
when promise_date is not null and
fact.dbi_date_closed = report_date then
1
else null
end
) complete_with_promise_date_cnt
, sum( case
when fact.dbi_date_closed > promise_date and
fact.dbi_date_closed = report_date then
fact.dbi_date_closed - promise_date
else 0
end
) days_late
, count( case
when fact.dbi_date_closed > promise_date and
fact.dbi_date_closed = report_date then
fact.dbi_date_closed - promise_date
else 0
end
) count_days_late_mv
, count( case
when fact.dbi_date_closed > promise_date and
fact.dbi_date_closed = report_date and
fact.dbi_date_closed - promise_date >= nvl(bbc.range1_low,0) and
(fact.dbi_date_closed - promise_date) < nvl(bbc.range1_high,fact.dbi_date_closed - promise_date + 1) then
1
else null
end
) days_late_age_b1
, count( case
when fact.dbi_date_closed > promise_date and
fact.dbi_date_closed = report_date and
fact.dbi_date_closed - promise_date >= bbc.range2_low and
(fact.dbi_date_closed - promise_date) < nvl(bbc.range2_high,fact.dbi_date_closed - promise_date + 1) then
1
else null
end
) days_late_age_b2
, count( case
when fact.dbi_date_closed > promise_date and
fact.dbi_date_closed = report_date and
fact.dbi_date_closed - promise_date >= bbc.range3_low and
(fact.dbi_date_closed - promise_date) < nvl(bbc.range3_high,fact.dbi_date_closed - promise_date + 1) then
1
else null
end
) days_late_age_b3
, count( case
when fact.dbi_date_closed > promise_date and
fact.dbi_date_closed = report_date and
fact.dbi_date_closed - promise_date >= bbc.range4_low and
(fact.dbi_date_closed - promise_date) < nvl(bbc.range4_high,fact.dbi_date_closed - promise_date + 1) then
1
else null
end
) days_late_age_b4
, count( case
when fact.dbi_date_closed > promise_date and
fact.dbi_date_closed = report_date and
fact.dbi_date_closed - promise_date >= bbc.range5_low and
(fact.dbi_date_closed - promise_date) < nvl(bbc.range5_high,fact.dbi_date_closed - promise_date + 1) then
1
else null
end
) days_late_age_b5
, count( case
when fact.dbi_date_closed > promise_date and
fact.dbi_date_closed = report_date and
fact.dbi_date_closed - promise_date >= bbc.range6_low and
(fact.dbi_date_closed - promise_date) < nvl(bbc.range6_high,fact.dbi_date_closed - promise_date + 1) then
1
else null
end
) days_late_age_b6
, count( case
when fact.dbi_date_closed > promise_date and
fact.dbi_date_closed = report_date and
fact.dbi_date_closed - promise_date >= bbc.range7_low and
(fact.dbi_date_closed - promise_date) < nvl(bbc.range7_high,fact.dbi_date_closed - promise_date + 1) then
1
else null
end
) days_late_age_b7
, count( case
when fact.dbi_date_closed > promise_date and
fact.dbi_date_closed = report_date and
fact.dbi_date_closed - promise_date >= bbc.range8_low and
(fact.dbi_date_closed - promise_date) < nvl(bbc.range8_high,fact.dbi_date_closed - promise_date + 1) then
1
else null
end
) days_late_age_b8
, count( case
when fact.dbi_date_closed > promise_date and
fact.dbi_date_closed = report_date and
fact.dbi_date_closed - promise_date >= bbc.range9_low and
(fact.dbi_date_closed - promise_date) < nvl(bbc.range9_high,fact.dbi_date_closed - promise_date + 1) then
1
else null
end
) days_late_age_b9
, count( case
when fact.dbi_date_closed > promise_date and
fact.dbi_date_closed = report_date and
fact.dbi_date_closed - promise_date >= bbc.range10_low and
(fact.dbi_date_closed - promise_date) < nvl(bbc.range10_high,fact.dbi_date_closed - promise_date + 1) then
1
else null
end
) days_late_age_b10
, 31 - grouping_id( fact.item_org_id
, customer_id
, nvl(eoi.vbh_category_id,-1)
, fact.repair_organization_id
, repair_type_id
) aggregation_flag
, grouping_id( fact.item_org_id
, customer_id
, nvl(eoi.vbh_category_id,-1)
, fact.repair_organization_id
, repair_type_id
, ftd.ent_year_id
, ftd.ent_qtr_id
, ftd.ent_period_id
, ftd.week_id
, ftd.report_date_julian
) grp_id_mv
, count(*) count_mv
from
FII.FII_TIME_DAY ftd
, ISC.ISC_DR_REPAIR_ORDERS_F fact
, ENI.ENI_OLTP_ITEM_STAR eoi
, BIS.BIS_BUCKET bb
, BIS.BIS_BUCKET_CUSTOMIZATIONS bbc
where
ftd.report_date in ( fact.dbi_date_closed
, fact.dbi_ro_creation_date
, fact.dbi_promise_date+1
)
and fact.item_org_id = eoi.id
and bb.short_name = 'ISC_DEPOT_BKLG_CMP_AGING'
and bb.bucket_id = bbc.bucket_id
group by
fact.repair_organization_id
, fact.repair_type_id
, ftd.ent_year_id
, rollup( customer_id )
, rollup( nvl(eoi.vbh_category_id,-1)
, fact.item_org_id
)
, rollup( ftd.ent_qtr_id
, ftd.ent_period_id
, ftd.week_id
, ftd.report_date_julian
)