MATERIALIZED VIEW: APPS.OPI_SCRAP_SUM_MV

Source

Select /* 12.0: bug#4526784 */ fact.organization_id,
           fact.source,
           fact.inventory_item_id,
           fact.uom_code,
           fact.inventory_item_id||'-'||fact.organization_id item_org_id,
           nvl (star.inv_category_id, -1) inv_category_id,
           grouping_id (fact.organization_id,
                        fact.source,
                        fact.inventory_item_id,
                        fact.uom_code,
                        nvl (star.inv_category_id, -1),
                        cal.ent_qtr_id,
                        cal.ent_period_id,
                        cal.week_id,
                        cal.report_date_julian) grouping_id,
           grouping_id (nvl (star.inv_category_id, -1),
                        fact.inventory_item_id) item_cat_flag,
           decode (grouping_id (cal.ent_qtr_id, cal.ent_period_id,
                                cal.week_id, cal.report_date_julian),
                   14, cal.report_date_julian,
                   13, cal.week_id,
                   11, cal.ent_period_id,
                   7, cal.ent_qtr_id) time_id,
           decode (grouping_id (cal.ent_qtr_id, cal.ent_period_id,
                                cal.week_id, cal.report_date_julian),
                   14, 1,
                   13, 16,
                   11, 32,
                   7, 64) period_type_id,
           cal.ent_qtr_id,
           cal.ent_period_id,
           cal.week_id,
           cal.report_date_julian,
           sum (fact.production_qty) production_qty,
           count (fact.production_qty) production_qty_cnt,
           sum (fact.production_val_b) production_val_b,
           count (fact.production_val_b) production_val_b_cnt,
           sum (fact.production_val_g) production_val_g,
           count (fact.production_val_g) production_val_g_cnt,
           sum (fact.scrap_qty) scrap_qty,
           count (fact.scrap_qty) scrap_qty_cnt,
           sum (fact.scrap_val_b) scrap_val_b,
           count (fact.scrap_val_b) scrap_val_b_cnt,
           sum (fact.scrap_val_g) scrap_val_g,
           count (fact.scrap_val_g) scrap_val_g_cnt,
           sum (fact.production_val_sg) production_val_sg,
           count (fact.production_val_sg) production_val_sg_cnt,
           sum (scrap_val_sg) scrap_val_sg,
           count (scrap_val_sg) scrap_val_sg_cnt,
           count (*) lines_cnt
      FROM  ENI.ENI_OLTP_ITEM_STAR star,
            FII.FII_TIME_DAY cal,
            opi_prod_scr_mv fact
      WHERE star.organization_id = fact.organization_id
        AND star.inventory_item_id = fact.inventory_item_id
        AND cal.report_date = fact.transaction_date
      GROUP BY
            fact.organization_id,
            fact.source,
            rollup (nvl (star.inv_category_id, -1),
                    (fact.inventory_item_id, fact.uom_code)),
            grouping sets (cal.ent_qtr_id, cal.ent_period_id,
                           cal.week_id, cal.report_date_julian)