MATERIALIZED VIEW: APPS.ENI_DBI_INV_BASE_MV

Source

SELECT /* 12.0: bug#4526784 */
        f.organization_id     organization_id,
        i.inventory_item_id  inventory_item_id,
        i.inventory_item_id||'-'||f.organization_id item_org_id,
        i.inventory_item_id||'-'||edom.master_organization_id item_master_org_id,
        nvl(i.vbh_category_id,-1)     item_category_id,
        NVL(c.report_date_julian, NVL(c.week_id, NVL(c.ent_period_id, NVL(c.ent_qtr_id, c.ent_year_id)))) time_id,
        c.ent_year_id year_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(f.onhand_value_b * f.conversion_rate)      onhand_value_g,
        COUNT(f.onhand_value_b * f.conversion_rate)    count_ovg,
        SUM(f.onhand_value_b)      onhand_value_b,
        COUNT(f.onhand_value_b)    count_ovb,
        SUM(f.intransit_value_b * f.conversion_rate)   intransit_value_g,
        COUNT(f.intransit_value_b * f.conversion_rate) count_ivg,
        SUM(intransit_value_b)   intransit_value_b,
        COUNT(intransit_value_b) count_ivb,
        SUM(f.wip_value_b *  f.conversion_rate)         wip_value_g,
        COUNT(f.wip_value_b *  f.conversion_rate)       count_wvg,
        SUM(wip_value_b)         wip_value_b,
        COUNT(wip_value_b)       count_wvb,
        SUM(  (  f.onhand_value_b + f.intransit_value_b + f.wip_value_b)* f.conversion_rate) inv_total_value_g,
        COUNT(  (  f.onhand_value_b + f.intransit_value_b + f.wip_value_b )* f.conversion_rate) count_i_t_v_g,
        SUM(  (  f.onhand_value_b + f.intransit_value_b + f.wip_value_b)) inv_total_value_b,
        COUNT(  (  f.onhand_value_b      + f.intransit_value_b + f.wip_value_b)) count_i_t_v_b,
        -- begin secondary currency
        SUM(f.onhand_value_b*f.sec_conversion_rate) AS onhand_value_sg,
        COUNT(f.onhand_value_b*f.sec_conversion_rate) AS cnt_onhand_value_sg,
        SUM(f.intransit_value_b*f.sec_conversion_rate) AS intransit_value_sg,
        COUNT(f.intransit_value_b*f.sec_conversion_rate) AS cnt_intransit_value_sg,
        SUM(f.wip_value_b*f.sec_conversion_rate) AS wip_value_sg,
        COUNT(f.wip_value_b*f.sec_conversion_rate) AS cnt_wip_value_sg,
        SUM( ( f.onhand_value_b + f.intransit_value_b + f.wip_value_b ) *f.sec_conversion_rate) AS inv_total_value_sg,
        COUNT( ( f.onhand_value_b + f.intransit_value_b + f.wip_value_b ) *f.sec_conversion_rate) AS cnt_inv_total_value_sg,
        --f.sec_conversion_rate,
        -- end secondary currency
        count(*)  count_total  ,
        GROUPING_ID(f.organization_id,
                    i.inventory_item_id,
                    i.inventory_item_id||'-'||f.organization_id,
                    i.inventory_item_id||'-'||edom.master_organization_id,
                    nvl(i.vbh_category_id,-1),
                    --f.sec_conversion_rate,
                    c.ent_year_id,
                    c.ent_qtr_id,
                    c.ent_period_id,
                    c.week_id,
                    c.report_date_julian) GRP_ID
        FROM
            OPI.OPI_DBI_INV_VALUE_F f,
            FII.FII_TIME_DAY c,
            ENI.ENI_OLTP_ITEM_STAR i,
            ENI_DBI_ORG_MV edom
        WHERE   i.organization_id = f.organization_id
        AND edom.organization_id = i.organization_id
        AND  i.inventory_item_id = f.inventory_item_id
        AND   c.report_date = f.transaction_date
        GROUP BY
               f.organization_id,
               i.inventory_item_id,
               i.inventory_item_id||'-'||f.organization_id,
               i.inventory_item_id||'-'||edom.master_organization_id,
               nvl(i.vbh_category_id,-1),
               --f.sec_conversion_rate,
               grouping sets (c.ent_year_id, c.ent_qtr_id, c.ent_period_id, c.week_id, c.report_date_julian)