MATERIALIZED VIEW: APPS.OPI_PGM_SUM_MV

Source

Select /* 12.0: bug#4526784 */ fact.organization_id organization_id,
           fact.customer_id customer_id,
           fact.top_model_item_id item_org_id,
           fact.top_model_org_id,
           fact.item_category_id,
           grouping_id (fact.organization_id,
                        fact.customer_id,
                        fact.top_model_item_id,
                        fact.item_category_id,
                        fact.uom_code,
                        fact.top_model_org_id,
                        ent_qtr_id,
                        ent_period_id,
                        week_id,
                        report_date_julian) grouping_id,
           grouping_id (fact.customer_id,
                        fact.item_category_id,
                        fact.top_model_item_id) customer_item_cat_flag,
           decode (grouping_id (ent_qtr_id, ent_period_id,
                                week_id, report_date_julian),
                   0, report_date_julian,
                   1, week_id,
                   3, ent_period_id,
                   7, ent_qtr_id) time_id,
           decode (grouping_id (ent_qtr_id, ent_period_id,
                                week_id, report_date_julian),
                   0, 1,
                   1, 16,
                   3, 32,
                   7, 64) period_type_id,
           report_date_julian day_id,
           week_id week_id,
           ent_period_id ent_period_id,
           ent_qtr_id ent_qtr_id,
           sum (fulfilled_val_g) fulfilled_val_g,
           count (fulfilled_val_g) fulfilled_val_g_cnt,
           sum (fulfilled_val_b) fulfilled_val_b,
           count (fulfilled_val_b) fulfilled_val_b_cnt,
           sum (cogs_val_g) cogs_val_g,
           count(cogs_val_g) cogs_val_g_cnt,
           sum(cogs_val_b) cogs_val_b,
           count(cogs_val_b) cogs_val_b_cnt,
           sum(fulfilled_qty) fulfilled_qty,
           count(fulfilled_qty) fulfilled_qty_cnt,
           fact.uom_code ,
           sum (fulfilled_val_sg) fulfilled_val_sg,
           count (fulfilled_val_sg) fulfilled_val_sg_cnt,
           sum (cogs_val_sg) cogs_val_sg,
           count (cogs_val_sg) cogs_val_sg_cnt,
           count(*) cnt
      FROM  opi_pgm_grp_mv fact,
            FII.FII_TIME_DAY cal
      WHERE fact.transaction_date = cal.report_date
      GROUP BY
            fact.organization_id,
            fact.top_model_org_id,
            fact.uom_code,
            rollup (fact.customer_id),
            rollup (fact.item_category_id, fact.top_model_item_id),
            ent_qtr_id,
            rollup (ent_period_id, week_id, report_date_julian)