MATERIALIZED VIEW: APPS.ISC_DR_CURR_02_MV

Source

select
  /* 12.0: bug#4526784 */
  fact.item_org_id
, fact.product_category_id
, fact.repair_organization_id
, fact.repair_type_id
, fact.customer_id
, count(1) backlog_count
, count(decode(past_due_flag,'Y',1,null)) past_due_count
, count( case
           when promise_date is null then
             1
           else null
         end
       ) not_promised_count
, count( case
           when days_until_promised >= nvl(bbc2.range1_low,0) and
                days_until_promised < nvl(bbc2.range1_high,days_until_promised + 1) then
             1
           else null
         end
       ) days_until_promised_b1
, count( case
           when days_until_promised >= bbc2.range2_low and
                days_until_promised < nvl(bbc2.range2_high,days_until_promised + 1) then
             1
           else null
         end
       ) days_until_promised_b2
, count( case
           when days_until_promised >= bbc2.range3_low and
                days_until_promised < nvl(bbc2.range3_high,days_until_promised + 1) then
             1
           else null
         end
       ) days_until_promised_b3
, count( case
           when days_until_promised >= bbc2.range4_low and
                days_until_promised < nvl(bbc2.range4_high,days_until_promised + 1) then
             1
           else null
         end
       ) days_until_promised_b4
, count( case
           when days_until_promised >= bbc2.range5_low and
                days_until_promised < nvl(bbc2.range5_high,days_until_promised + 1) then
             1
           else null
         end
       ) days_until_promised_b5
, count( case
           when days_until_promised >= bbc2.range6_low and
                days_until_promised < nvl(bbc2.range6_high,days_until_promised + 1) then
             1
           else null
         end
       ) days_until_promised_b6
, count( case
           when days_until_promised >= bbc2.range7_low and
                days_until_promised < nvl(bbc2.range7_high,days_until_promised + 1) then
             1
           else null
         end
       ) days_until_promised_b7
, count( case
           when days_until_promised >= bbc2.range8_low and
                days_until_promised < nvl(bbc2.range8_high,days_until_promised + 1) then
             1
           else null
         end
       ) days_until_promised_b8
, count( case
           when days_until_promised >= bbc2.range9_low and
                days_until_promised < nvl(bbc2.range9_high,days_until_promised + 1) then
             1
           else null
         end
       ) days_until_promised_b9
, count( case
           when days_until_promised >= bbc2.range10_low and
                days_until_promised < nvl(bbc2.range10_high,days_until_promised + 1) then
             1
           else null
         end
       ) days_until_promised_b10
, count( case
           when past_due_flag = 'Y' and
                past_due_days >= nvl(bbc1.range1_low,0) and
                past_due_days < nvl(bbc1.range1_high,past_due_days +1) then
             1
           else null
         end
       ) past_due_age_b1
, count( case
           when past_due_flag = 'Y' and
                past_due_days >= bbc1.range2_low and
                past_due_days < nvl(bbc1.range2_high,past_due_days + 1) then
             1
           else null
         end
       ) past_due_age_b2
, count( case
           when past_due_flag = 'Y' and
                past_due_days >= bbc1.range3_low and
                past_due_days < nvl(bbc1.range3_high,past_due_days + 1) then
             1
           else null
         end
       ) past_due_age_b3
, count( case
           when past_due_flag = 'Y' and
                past_due_days >= bbc1.range4_low and
                past_due_days < nvl(bbc1.range4_high,past_due_days + 1) then
             1
           else null
         end
       ) past_due_age_b4
, count( case
           when past_due_flag = 'Y' and
                past_due_days >= bbc1.range5_low and
                past_due_days < nvl(bbc1.range5_high,past_due_days + 1) then
             1
           else null
         end
       ) past_due_age_b5
, count( case
           when past_due_flag = 'Y' and
                past_due_days >= bbc1.range6_low and
                past_due_days < nvl(bbc1.range6_high,past_due_days + 1) then
             1
           else null
         end
       ) past_due_age_b6
, count( case
           when past_due_flag = 'Y' and
                past_due_days >= bbc1.range7_low and
                past_due_days < nvl(bbc1.range7_high,past_due_days + 1) then
             1
           else null
         end
       ) past_due_age_b7
, count( case
           when past_due_flag = 'Y' and
                past_due_days >= bbc1.range8_low and
                past_due_days < nvl(bbc1.range8_high,past_due_days + 1) then
             1
           else null
         end
       ) past_due_age_b8
, count( case
           when past_due_flag = 'Y' and
                past_due_days >= bbc1.range9_low and
                past_due_days < nvl(bbc1.range9_high,past_due_days + 1) then
             1
           else null
         end
       ) past_due_age_b9
, count( case
           when past_due_flag = 'Y' and
                past_due_days >= bbc1.range10_low and
                past_due_days < nvl(bbc1.range10_high,past_due_days + 1) then
             1
           else null
         end
       ) past_due_age_b10
, 31 - grouping_id( fact.item_org_id
                  , customer_id
                  , product_category_id
                  , fact.repair_organization_id
                  , repair_type_id
                  ) aggregation_flag
from
  isc_dr_curr_01_mv fact
, BIS.BIS_BUCKET bb1
, BIS.BIS_BUCKET_CUSTOMIZATIONS bbc1
, BIS.BIS_BUCKET bb2
, BIS.BIS_BUCKET_CUSTOMIZATIONS bbc2
where
    bb1.short_name = 'ISC_DEPOT_BKLG_CMP_AGING'
and bb1.bucket_id = bbc1.bucket_id
and bb2.short_name = 'ISC_DEPOT_DAYS_UNTIL_PROM'
and bb2.bucket_id = bbc2.bucket_id
group by
  fact.repair_organization_id
, fact.repair_type_id
, rollup( customer_id )
, rollup( product_category_id
        , fact.item_org_id
        )