MATERIALIZED VIEW: APPS.OPI_WMS_004_MV

Source

Select /* 12.0: bug#4526784 */
        f.organization_id organization_id,
        decode (f.subinventory_code,
                NULL, NULL,
                -1, -1,
                (f.subinventory_code || '-' || f.organization_id))
          subinventory_code,
        (i.inventory_item_id || '-' || i.organization_id) item_org_id,
        f.primary_UOM uom_code,
        nvl (i.inv_category_id, -1) inv_category_id,
        grouping_id
            (decode (f.subinventory_code,
                     NULL, NULL,
                     -1, -1,
                     (f.subinventory_code || '-' || f.organization_id)),
                     nvl (i.inv_category_id, -1),
                     (i.inventory_item_id || '-'|| i.organization_id))
           aggregation_level_flag,
        grouping_id (f.organization_id,
                     decode (f.subinventory_code,
                             NULL, NULL,
                             -1, -1,
                             (f.subinventory_code || '-' ||
                              f.organization_id)),
                     (i.inventory_item_id || '-' || i.organization_id),
                     f.primary_uom,
                     nvl(i.inv_category_id,-1),
                     c.ent_year_id,
                     c.ent_qtr_id,
                     c.ent_period_id,
                     c.week_id,
                     c.report_date_julian)
          grp_id,
        decode (grouping_id (c.ent_year_id, c.ent_qtr_id,
                             c.ent_period_id, c.week_id,
                             c.report_date_julian),
                0, c.report_date_julian,
                1, c.week_id,
                3, c.ent_period_id,
                7, c.ent_qtr_id,
                15, c.ent_year_id)
          time_id,
        decode (grouping_id (c.ent_year_id, c.ent_qtr_id,
                             c.ent_period_id, c.week_id,
                             c.report_date_julian),
                0, 1,
                1, 16,
                3, 32,
                7, 64,
                15, 128)
          period_type_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_qty) stored_qty,
        count (f.onhand_qty) cnt_stored_qty,
        -- Note: Conv. rates cannot be -ve. The ETL will error out in
        -- those cases.
        sum (decode (conv.weight_conv_rate,
                     NULL, NULL,
                     f.onhand_qty * conv.weight_conv_rate *
                     i.unit_weight))
          weight_stored,
        count (decode (conv.weight_conv_rate,
                       NULL, NULL,
                       f.onhand_qty * conv.weight_conv_rate *
                       i.unit_weight))
          cnt_weight_stored,
        sum (decode (conv.volume_conv_rate,
                     NULL, NULL,
                     f.onhand_qty * conv.volume_conv_rate *
                     i.unit_volume))
          utilized_volume,
        count (decode (conv.volume_conv_rate,
                       NULL, NULL,
                       f.onhand_qty * conv.volume_conv_rate *
                       i.unit_volume))
          cnt_utilized_volume,
        count(*)  cnt_total
      FROM  OPI.OPI_DBI_INV_VALUE_F f,
            FII.FII_TIME_DAY c,
            ENI.ENI_OLTP_ITEM_STAR i,
            OPI.OPI_DBI_WMS_STOR_ITEM_CONV_F conv
      WHERE f.organization_id = i.organization_id
        AND f.inventory_item_id = i.inventory_item_id
        AND f.transaction_date = c.report_date
        AND f.organization_id = conv.organization_id
        AND f.inventory_item_id = conv.inventory_item_id
      GROUP BY
        f.organization_id,
        rollup (
                (
                 decode
                    (f.subinventory_code,
                     NULL, NULL,
                     -1, -1,
                     (f.subinventory_code || '-' || f.organization_id)),
                 nvl (i.inv_category_id, -1)
                ),
                (
                 (i.inventory_item_id || '-' || i.organization_id),
                 f.primary_uom
                )
               ),
        c.ent_year_id,
        rollup (c.ent_qtr_id, c.ent_period_id,
                c.week_id, c.report_date_julian)