MATERIALIZED VIEW: APPS.BIV_BAC_SUM_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
, decode( grouping_id( nvl(pc.master_id,pc.id)
                     , f.customer_id
                     , f.owner_group_id
                     , f.incident_status_id ) , 14, 4 -- Status
                                              , 13, 3 -- Assignment Group
                                              , 11, 2 -- Customer
                                              , 7, 1 -- Prod
                                              , 0 ) grp_id
, f.incident_type_id
, f.incident_severity_id
, pc.vbh_category_id
, nvl(pc.master_id,pc.id) product_id
, f.customer_id
, f.owner_group_id
, f.incident_status_id
, f.resolved_flag
, sum(decode(m.id,1,1,0)-decode(m.id,2,1,0)) backlog_count
, sum(decode(escalated_date,null,0,decode(m.id,1,1,0)-decode(m.id,2,1,0))) escalated_count
, sum(decode(unowned_date,null,0,decode(m.id,1,1,0)-decode(m.id,2,1,0))) unowned_count
/* the following columns prefixed with mv_ exist solely to enable
   MV to be fast refreshable */
, grouping_id( c.ent_year_id
             , c.ent_qtr_id
             , c.ent_period_id
             , c.week_id
             , c.report_date_julian
             , f.incident_type_id
             , nvl(pc.master_id,pc.id)
             , pc.vbh_category_id
             , f.incident_severity_id
             , f.customer_id
             , f.owner_group_id
             , f.incident_status_id
             , f.resolved_flag
              ) mv_grp_id
, c.ent_year_id
, c.ent_qtr_id
, c.ent_period_id
, c.week_id
, c.report_date_julian
, count(*) mv_count
, count(decode(m.id,1,1,0)-decode(m.id,2,1,0)) mv_backlog_count
, count(decode(escalated_date,null,0,decode(m.id,1,1,0)-decode(m.id,2,1,0))) mv_escalated_count
, count(decode(unowned_date,null,0,decode(m.id,1,1,0)-decode(m.id,2,1,0))) mv_unowned_count
/* end of fast refreshable needed columns */
from
  BIV.BIV_DBI_BACKLOG_SUM_F f
, OKI.OKI_DBI_MULTIPLEXER_B m
, FII.FII_TIME_DAY c
, ENI.ENI_OLTP_ITEM_STAR pc
where m.id < 3
and greatest( trunc(f.incident_date)
            , decode(m.id,1,f.backlog_date_from,f.backlog_date_to+1) ) = c.report_date
and f.inventory_item_id = pc.inventory_item_id
and f.inv_organization_id = pc.organization_id
and (m.id = 1 or f.backlog_date_to < to_date('4712/12/31','YYYY/MM/DD'))
group by
  grouping sets ( (c.ent_year_id, c.ent_qtr_id, c.ent_period_id, c.week_id, c.report_date_julian)
                , (c.ent_year_id, c.ent_qtr_id, c.ent_period_id, c.week_id)
                , (c.ent_year_id, c.ent_qtr_id, c.ent_period_id)
                , (c.ent_year_id, c.ent_qtr_id)
                , (c.ent_year_id)
                )
, f.incident_type_id
, f.incident_severity_id
, pc.vbh_category_id
, f.resolved_flag
, grouping sets ( (nvl(pc.master_id,pc.id))
                , (f.customer_id)
                , (f.owner_group_id)
                , (f.incident_status_id)
                , (nvl(pc.master_id,pc.id),f.customer_id,f.owner_group_id,f.incident_status_id,f.resolved_flag)
                )