MATERIALIZED VIEW: APPS.OPI_DBI_RES_UTL_MV

Source

Select /* 12.0: bug#4526784 */ grouping_id (rtl.organization_id,
                        res.resource_group,
                        res.department_id,
                        rtl.resource_id,
                        ent_qtr_id,
                        ent_period_id,
                        week_id,
                        report_date_julian,
                        res.ID,
                        res.resource_group_fk,
                        res.department_fk ) grp_id,
           decode (grouping_id (ent_qtr_id, ent_period_id,
                                week_id, report_date_julian),
                   14, report_date_julian,
                   13,week_id,
                   11, ent_period_id,
                    7, ent_qtr_id) time_id,
           decode (grouping_id (ent_qtr_id, ent_period_id,
                                week_id, report_date_julian),
                   14, 1,
                   13, 16,
                   11, 32,
                    7, 64) period_type_id,
          grouping_id (res.resource_group, res.department_id,
                       rtl.resource_id ) resource_level_flag,
          rtl.organization_id,
          rtl.resource_id,
          res.ID resource_org_id,
          res.resource_group,
          res.resource_group_fk resource_group_id,
          res.department_id,
          res.department_fk resource_department_id,
          cal.ent_qtr_id,
          cal.ent_period_id,
          cal.week_id,
          cal.report_date_julian,
          sum (rtl.avail_val_b) avail_val_b,
          sum (rtl.avail_val_g) avail_val_g,
          sum (rtl.avail_qty_g) avail_qty,
          sum (rtl.actual_val_b) actual_val_b,
          sum (rtl.actual_val_g) actual_val_g,
          sum (rtl.actual_qty_g) actual_qty,
          count (rtl.avail_val_b) avail_val_b_cnt,
          count (rtl.avail_val_g) avail_val_g_cnt,
          count (rtl.avail_qty_g) avail_qty_cnt,
          count (rtl.actual_val_b) actual_val_b_cnt,
          count (rtl.actual_val_g) actual_val_g_cnt,
          count (rtl.actual_qty_g) actual_qty_cnt,
          sum (rtl.avail_val_sg) avail_val_sg,
          count (rtl.avail_val_sg) avail_val_sg_cnt,
          sum (rtl.actual_val_sg) actual_val_sg,
          count (rtl.actual_val_sg) actual_val_sg_cnt,
          count(*) cnt
      FROM  OPI.OPI_DBI_RES_AVAIL_F rtl,
            FII.FII_TIME_DAY cal,
            eni_res_5_mv res
      WHERE rtl.transaction_date = cal.report_date
        AND rtl.resource_id     = res.resource_id
        AND rtl.organization_id = res.organization_id
        AND ( (source = 1 and rtl.department_id = res.department_id )
             OR source = 2 OR source = 3)
      GROUP BY
            rtl.organization_id,
            grouping sets (    (res.resource_group, res.resource_group_fk ),
                               (res.department_id,  res.department_fk  ),
                               (res.resource_group, res.resource_group_fk,
                                res.department_id, res.department_fk,
                                rtl.resource_id, res.ID ),
                               () ),
            grouping sets (ent_qtr_id, ent_period_id,
                           week_id, report_date_julian)