MATERIALIZED VIEW: APPS.OPI_MFG_VAR_SUM_MV

Source

SELECT /* 12.0: bug#4526784 */ f.organization_id organization_id,
           f.Assembly_Item_id Assembly_Item_id,
           f.Assembly_Item_id||'-'||f.organization_id Item_Org_id,
           f.UOM_Code UOM_Code,
           nvl(i.inv_category_id,-1) inv_category_id,
           grouping_id (f.organization_id,
                        f.Assembly_Item_id,
                        f.UOM_Code,
                        nvl(i.inv_category_id,-1),
                        ent_qtr_id,
                        c.ent_period_id,
                        c.week_id,
                        c.report_date_julian,
                        f.source) grouping_id,
           grouping_id (nvl (i.inv_category_id,-1),
                        f.Assembly_item_id) item_cat_flag,
           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 (c.ent_qtr_id, c.ent_period_id,
                                c.week_id, c.report_date_julian),
                   14, 1,
                   13, 16,
                   11, 32,
                   7, 64) period_type_id,
           c.ent_qtr_id qtr_id,
           c.ent_period_id month_id,
           c.week_id week_id,
           c.report_date_julian day_id,
           f.Source source,
           sum (f.Standard_Value_B) Std_value_b,
           count (f.Standard_Value_B) Std_value_b_cnt,
           sum (f.Standard_Value_B * f.Conversion_rate) Std_value_g,
           count (f.Standard_Value_B * f.Conversion_rate) Std_value_g_cnt,
           sum (f.Actual_Value_B) Act_value_b,
           count (f.Actual_Value_B) Act_value_b_cnt,
           sum (f.Actual_Value_B * f.Conversion_rate) Act_value_g,
           count (f.Actual_Value_B * f.Conversion_rate) Act_value_g_cnt,
           sum (f.Actual_Qty_Completed) Actual_Qty_Completed,
           count (f.Actual_Qty_Completed) Act_Qty_Completed_cnt,
           sum (f.Standard_Value_B * f.sec_conversion_rate) std_value_sg,
           count (f.Standard_Value_B * f.sec_conversion_rate) std_value_sg_cnt,
           sum (f.Actual_Value_B * f.sec_conversion_rate) act_value_sg,
           count (f.Actual_Value_B * f.sec_conversion_rate) act_value_sg_cnt,
           count (*) cnt
      FROM  OPI.OPI_DBI_MFG_CST_VAR_F f,
      	    OPI.OPI_DBI_JOBS_F jobs,
            ENI.ENI_OLTP_ITEM_STAR i,
            FII.FII_TIME_DAY c
      WHERE
            jobs.job_status_code = 12
        AND jobs.job_id = f.job_id
        AND jobs.organization_id = f.organization_id
        AND jobs.assembly_item_id = f.assembly_item_id
        AND jobs.job_type = f.job_type
        AND jobs.source = f.source
        AND i.organization_id = f.organization_id
        AND i.inventory_item_id = f.Assembly_Item_id
        AND c.report_date = f.Closed_date
      GROUP BY
            f.organization_id,
            f.source,
            rollup (nvl(i.inv_category_id,-1),
                    (f.Assembly_Item_id, f.UOM_Code)),
            grouping sets (c.ent_qtr_id, c.ent_period_id,
                           c.week_id, c.report_date_julian)