MATERIALIZED VIEW: APPS.OPI_PGM_CBN_MV

Source

Select /* 12.0: bug#4526784 */ fact.inv_org_id organization_id,
           fact.customer_id,
           fact.item_id top_model_item_id,
           fact.top_model_org_id,
           fact.item_category_id,
           time.report_date transaction_date,
           fact.uom uom_code,
           sum (decode (fact.return_flag,
                        1, -1 * fact.fulfilled_amt_g,
                        fact.fulfilled_amt_g)) fulfilled_val_g,
           sum (decode (fact.return_flag,
                        1, -1 * fact.fulfilled_amt_f,
                        fact.fulfilled_amt_f)) fulfilled_val_b,
           sum (decode (fact.return_flag,
                        1, -1 * fact.fulfilled_qty,
                        fact.fulfilled_qty)) fulfilled_qty,
           null cogs_val_g,
           null cogs_val_b,
           0 as pmarker,
           count (decode (fact.return_flag,
                          1, -1 * fact.fulfilled_amt_g,
                          fact.fulfilled_amt_g)) fulfilled_val_g_cnt,
           count (decode (fact.return_flag,
                          1, -1 * fact.fulfilled_amt_f,
                          fact.fulfilled_amt_f)) fulfilled_val_b_cnt,
           count (decode (fact.return_flag,
                        1, -1 * fact.fulfilled_qty,
                        fact.fulfilled_qty)) fulfilled_qty_cnt,
           null cogs_val_g_cnt,
           null cogs_val_b_cnt,
           sum (decode (fact.return_flag,
                        1, -1 * fact.fulfilled_amt_g1,
                        fact.fulfilled_amt_g1))  fulfilled_val_sg,
           count (decode (fact.return_flag,
                          1, -1 * fact.fulfilled_amt_g1,
                          fact.fulfilled_amt_g1))  fulfilled_val_sg_cnt,
           null cogs_val_sg,
           null cogs_val_sg_cnt,
           count(*) c_total
      FROM  isc_dbi_cfm_005_mv fact,
            FII.FII_TIME_DAY time
      WHERE fact.customer_flag = 0
        AND fact.item_cat_flag = 0
        AND time.report_date_julian = fact.time_id
        AND fact.period_type_id = 1
      GROUP BY
            fact.inv_org_id,
            time.report_date,
            fact.customer_id,
            fact.item_id,
            fact.item_category_id,
            fact.uom,
            fact.top_model_org_id
    UNION ALL
    SELECT fact.organization_id,
           fact.customer_id,
           fact.top_model_item_id || '-' || fact.top_model_org_id
                top_model_item_id,
           fact.top_model_org_id,
           nvl (item.vbh_category_id, -1) item_category_id,
           trunc (fact.cogs_date) transaction_date,
           fact.top_model_item_uom uom_code,
           null fulfilled_val_g,
           null fulfilled_val_b,
           null fulfilled_qty,
           sum (fact.cogs_val_g) cogs_val_g,
           sum (fact.cogs_val_b) cogs_val_b,
           1 as pmarker,
           null fulfilled_val_g_cnt,
           null fulfilled_val_b_cnt,
           null fulfilled_qty_cnt,
           count (fact.cogs_val_g) cogs_val_g_cnt,
           count (fact.cogs_val_b) cogs_val_b_cnt,
           null fulfilled_val_sg,
           null fulfilled_val_sg_cnt,
           sum (fact.cogs_val_sg) cogs_val_sg,
           count (fact.cogs_val_sg) cogs_val_sg_cnt,
           count(*) c_total
      FROM  OPI.OPI_DBI_COGS_F fact,
            ENI.ENI_OLTP_ITEM_STAR item
      WHERE fact.top_model_item_id = item.inventory_item_id
        AND fact.top_model_org_id = item.organization_id
      GROUP BY
            fact.organization_id,
            trunc(fact.cogs_date),
            fact.top_model_item_uom,
            fact.top_model_item_id || '-' || fact.top_model_org_id,
            nvl(item.vbh_category_id, -1),
            fact.customer_id,
            fact.top_model_org_id