MATERIALIZED VIEW: APPS.ISC_FS_005_MV

Source

select /* 12.0: bug#4526784 */
  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
, grouping_id( e.vbh_category_id
             , nvl(e.master_id,e.id)
             , b.customer_id ) grp_id
, b.act_bac_district_id parent_district_id
, b.act_bac_assignee_type record_type
, b.act_bac_assignee_id district_id
, to_char(b.act_bac_assignee_id) || '.' || b.act_bac_district_id district_id_c
, b.task_type_id
, b.customer_id
, nvl(e.master_id,e.id) product_id
, e.vbh_category_id
, sum(decode(m.id,1,1,-1)) backlog_count
, sum(decode(b.backlog_status_code,1,decode(m.id,1,1,-1),0)) in_planning_count
, sum(decode(b.backlog_status_code,2,decode(m.id,1,1,-1),0)) assigned_count
, sum(decode(b.backlog_status_code,3,decode(m.id,1,1,-1),0)) working_count
, sum(decode(b.backlog_status_code,4,decode(m.id,1,1,-1),0)) completed_count
, sum(decode(b.backlog_status_code,5,decode(m.id,1,1,-1),0)) other_count
/* the following columns prefixed with mv_ exist solely to enable
   MV to be fast refreshable */
, c.report_date_julian                mv_day_id
, c.week_id                           mv_week_id
, c.ent_period_id                     mv_period_id
, c.ent_qtr_id                        mv_ent_qtr_id
, c.ent_year_id                       mv_ent_year_id
, grouping_id( c.ent_year_id
             , c.ent_qtr_id
             , c.ent_period_id
             , c.week_id
             , c.report_date_julian
             , b.act_bac_district_id
             , b.act_bac_assignee_type
             , b.act_bac_assignee_id
             , to_char(b.act_bac_assignee_id) || '.' || b.act_bac_district_id
             , b.task_type_id
             , b.customer_id
             , nvl(e.master_id,e.id)
             , e.vbh_category_id
             )                        mv_grp_id
, count(*) mv_count
, count(decode(m.id,1,1,-1)) mv_backlog_count
, count(decode(b.backlog_status_code,1,decode(m.id,1,1,-1),0)) mv_in_planning_count
, count(decode(b.backlog_status_code,2,decode(m.id,1,1,-1),0)) mv_assigned_count
, count(decode(b.backlog_status_code,3,decode(m.id,1,1,-1),0)) mv_working_count
, count(decode(b.backlog_status_code,4,decode(m.id,1,1,-1),0)) mv_completed_count
, count(decode(b.backlog_status_code,5,decode(m.id,1,1,-1),0)) mv_other_count
/* end of fast refreshable needed columns */
from
  ISC.ISC_FS_TASK_BACKLOG_F b
, FII.FII_TIME_DAY c
, ENI.ENI_OLTP_ITEM_STAR e
, OKI.OKI_DBI_MULTIPLEXER_B m
where
    m.id < 3
and decode(m.id,1,b.backlog_date_from,b.backlog_date_to+1) = c.report_date
and (m.id = 1 or b.backlog_date_to < to_date('4712/12/31','YYYY/MM/DD'))
and b.inventory_item_id = e.inventory_item_id
and b.inv_organization_id = e.organization_id
and b.task_type_rule = 'DISPATCH'
and b.source_object_type_code = 'SR'
and b.deleted_flag = 'N'
group by
  c.ent_year_id
, rollup(c.ent_qtr_id, c.ent_period_id, c.week_id, c.report_date_julian)
, b.act_bac_district_id
, b.act_bac_assignee_type
, b.act_bac_assignee_id
, to_char(b.act_bac_assignee_id) || '.' || b.act_bac_district_id
, b.task_type_id
, rollup( e.vbh_category_id
        , nvl(e.master_id,e.id)
        , b.customer_id
        )