MATERIALIZED VIEW: APPS.OPI_SCRAP_REASON_001_MV

Source

SELECT     
           fact.organization_id,
           fact.source,
           fact.inventory_item_id,
           fact.uom_code,
	       fact.inventory_item_id||'-'||fact.organization_id item_org_id,
	       fact.scrap_reason_id,
           nvl (star.inv_category_id, -1) inv_category_id,
           grouping_id (fact.organization_id,
                        fact.source,
                        fact.inventory_item_id,
                        fact.uom_code,
                        fact.scrap_reason_id,
                        fact.inventory_item_id||'-'||fact.organization_id,
                        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( fact.organization_id
                      , NVL(star.inv_category_id,-1)
                      , (fact.inventory_item_id||'-'||fact.organization_id)
                      , fact.scrap_reason_id)   AGG_LEVEL,
           decode (grouping_id (cal.ent_qtr_id, cal.ent_period_id,
                                cal.week_id, cal.report_date_julian),
                   0, cal.report_date_julian,
                   1, cal.week_id,
                   3, 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),
                   0, 1,
                   1, 16,
                   3, 32,
                   7, 64) period_type_id,
           cal.ent_qtr_id,
           cal.ent_period_id,
           cal.week_id,
           cal.report_date_julian,
           sum (fact.scrap_quantity) scrap_qty,
	   count (fact.scrap_quantity) scrap_qty_cnt, 
	   sum (fact.scrap_value_b) scrap_val_b,  
	   count (fact.scrap_value_b) scrap_val_b_cnt, 
	   sum (fact.scrap_value_b * conversion_rate) scrap_val_g, 
	   count (fact.scrap_value_b *conversion_rate ) scrap_val_g_cnt, 
	   sum (scrap_value_b *sec_conversion_rate) scrap_val_sg,    
	   count (scrap_value_b *sec_conversion_rate) scrap_val_sg_cnt, 
       count(*) lines_cnt
      FROM  ENI.ENI_OLTP_ITEM_STAR star,
            FII.FII_TIME_DAY cal,
            OPI.OPI_DBI_WIP_SCRAP_F 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.scrap_reason_id,
	        fact.inventory_item_id,
            fact.source,
            fact.uom_code,
	        ROLLUP (NVL(star.inv_category_id,-1)
                  ,(fact.inventory_item_id||'-'||fact.organization_id)),
            cal.ent_qtr_id,
            ROLLUP(cal.ent_period_id, cal.week_id, cal.report_date_julian)