MATERIALIZED VIEW: APPS.OPI_PTP_SUM_STG_MV

Source

Select /* 12.0: bug#4526784 */ f.baseline_id,
           f.plan_name,
           f.owning_org_id,
           f.organization_id organization_id,
           f.inventory_item_id inventory_item_id,
           nvl(i.inv_category_id, -1) inv_category_id,
           f.inventory_item_id||'-'||f.organization_id item_org_id,
           f.uom_code uom_code,
           grouping_id (f.baseline_id,
                        f.plan_name,
                        f.owning_org_id,
                        f.organization_id,
                        f.inventory_item_id,
                        nvl(i.inv_category_id,-1),
                        f.uom_code,
                        c.ent_qtr_id,
                        c.ent_period_id,
                        c.week_id,
                        c.report_date_julian) grouping_id,
           grouping_id (nvl (i.inv_category_id,-1),
                        f.inventory_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,
           sum (planned_quantity) planned_quantity,
           count (planned_quantity) planned_quantity_cnt,
           sum (planned_quantity * cst.unit_cost) planned_standard_value_b,
           count (planned_quantity * cst.unit_cost)
                planned_standard_value_b_cnt,
           sum (planned_quantity * cst.unit_cost * cnv.conversion_rate)
                planned_standard_value_g,
           count (planned_quantity * cst.unit_cost * cnv.conversion_rate)
                planned_standard_value_g_cnt,
           sum (planned_quantity * cst.unit_cost * cnv.sec_conversion_rate)
                planned_standard_value_sg,
           count (planned_quantity * cst.unit_cost * cnv.sec_conversion_rate)
                planned_standard_value_sg_cnt,
           count(*) cnt
      from  OPI.OPI_DBI_PTP_PLAN_STG f,
            ENI.ENI_OLTP_ITEM_STAR i,
            FII.FII_TIME_DAY c,
            OPI.OPI_DBI_PTP_COST cst,
            OPI.OPI_DBI_PTP_CONV cnv
      WHERE i.organization_id = f.organization_id
        AND i.inventory_item_id = f.inventory_item_id
        AND c.report_date = f.transaction_date
        AND f.organization_id = cst.organization_id
        AND f.inventory_item_id = cst.inventory_item_id
        AND f.transaction_date >= cst.from_date
        AND f.transaction_date < nvl(cst.to_date, f.transaction_date + 1)
        AND f.organization_id = cnv.organization_id
        AND f.transaction_date >= cnv.from_date
        AND f.transaction_date < nvl(cnv.to_date, f.transaction_date + 1)
      GROUP BY
            f.baseline_id,
            f.plan_name,
            f.owning_org_id,
            f.organization_id,
            rollup (nvl (i.inv_category_id,-1),
                        (f.inventory_item_id, f.uom_code)),
            grouping sets (c.ent_qtr_id, c.ent_period_id,
                           c.week_id, c.report_date_julian)