MATERIALIZED VIEW: APPS.ISC_DR_MTTR_01_MV

Source

select
  /* 12.0: bug#4526784 */
  fact2.item_org_id item_org_id
, nvl(eoi.vbh_category_id,-1) product_category_id
, fact2.repair_organization_id repair_organization_id
, fact2.repair_type_id repair_type_id
, fact2.customer_id customer_id
, 31 - grouping_id( fact2.item_org_id
                   , fact2.customer_id
                   , nvl(eoi.vbh_category_id,-1)
                   , fact2.repair_organization_id
                   , fact2.repair_type_id
                   ) aggregation_flag
, ftd.ent_qtr_id qtr_id
, ftd.ent_period_id month_id
, ftd.week_id week_id
, ftd.report_date_julian day_id
, decode( grouping_id( ftd.ent_qtr_id
                     , ftd.ent_period_id
                     , ftd.week_id
                     , ftd.report_date_julian
                     )
        , 0, report_date_julian
        , 1, week_id
        , 3, ent_period_id
        , 7, ent_qtr_id
        ) time_id
, decode( grouping_id( ftd.ent_qtr_id
                     , ftd.ent_period_id
                     , ftd.week_id
                     , ftd.report_date_julian
                     )
         , 0, 1
         , 1, 16
         , 3, 32
         , 7, 64
         ) period_type_id
, sum( fact1.time_to_repair ) time_to_repair
, count( fact1.time_to_repair ) mv_time_to_repair_count
, grouping_id( fact2.item_org_id
             , fact2.customer_id
             , nvl(eoi.vbh_category_id,-1)
             , fact2.repair_organization_id
             , fact2.repair_type_id
             , ftd.ent_qtr_id
             , ftd.ent_period_id
             , ftd.week_id
             , ftd.report_date_julian
             ) mv_grp_id
, count(*) ro_count
, count( case
           when (fact1.time_to_repair >= nvl(bbc.range1_low,0) and
                fact1.time_to_repair < bbc.range1_high) then
             1
           else null
         end
       ) time_to_repair_b1
, count( case
           when (fact1.time_to_repair >= bbc.range2_low and
                fact1.time_to_repair < nvl(bbc.range2_high,fact1.time_to_repair + 1)) then
             1
           else null
         end
       ) time_to_repair_b2
, count( case
           when (fact1.time_to_repair >= bbc.range3_low and
                fact1.time_to_repair < nvl(bbc.range3_high,fact1.time_to_repair + 1)) then
             1
           else null
         end
       ) time_to_repair_b3
, count( case
           when (fact1.time_to_repair >= bbc.range4_low and
                fact1.time_to_repair < nvl(bbc.range4_high,fact1.time_to_repair + 1)) then
             1
           else null
         end
       ) time_to_repair_b4
, count( case
           when (fact1.time_to_repair >= bbc.range5_low and
                fact1.time_to_repair < nvl(bbc.range5_high,fact1.time_to_repair + 1)) then
             1
           else null
         end
       ) time_to_repair_b5
, count( case
           when (fact1.time_to_repair >= bbc.range6_low and
                fact1.time_to_repair < nvl(bbc.range6_high,fact1.time_to_repair + 1)) then
             1
           else null
         end
       ) time_to_repair_b6
, count( case
           when (fact1.time_to_repair >= bbc.range7_low and
                fact1.time_to_repair < nvl(bbc.range7_high,fact1.time_to_repair + 1)) then
             1
           else null
         end
       ) time_to_repair_b7
, count( case
           when (fact1.time_to_repair >= bbc.range8_low and
                fact1.time_to_repair < nvl(bbc.range8_high,fact1.time_to_repair + 1)) then
             1
           else null
         end
       ) time_to_repair_b8
, count( case
           when (fact1.time_to_repair >= bbc.range9_low and
                fact1.time_to_repair < nvl(bbc.range9_high,fact1.time_to_repair + 1)) then
             1
           else null
         end
       ) time_to_repair_b9
, count( case
           when (fact1.time_to_repair >= bbc.range10_low and
                fact1.time_to_repair < nvl(bbc.range10_high,fact1.time_to_repair + 1)) then
             1
           else null
         end
       ) time_to_repair_b10
from
  ISC.ISC_DR_MTTR_F fact1
, ISC.ISC_DR_REPAIR_ORDERS_F fact2
, FII.FII_TIME_DAY ftd
, ENI.ENI_OLTP_ITEM_STAR eoi
, BIS.BIS_BUCKET bb
, BIS.BIS_BUCKET_CUSTOMIZATIONS bbc
where
    fact1.repair_line_id = fact2.repair_line_id
and ftd.report_date = fact2.dbi_date_closed
and fact2.item_org_id = eoi.id
and bb.bucket_id = bbc.bucket_id
and bb.short_name = 'ISC_DEPOT_MTTR'
and fact1.time_to_repair >= 0
group by
  fact2.repair_organization_id
, fact2.repair_type_id,ftd.ent_qtr_id
, rollup( fact2.customer_id )
, rollup( nvl(eoi.vbh_category_id,-1)
        , fact2.item_org_id
        )
, rollup( ftd.ent_period_id
        , ftd.week_id
        , ftd.report_date_julian
        )