MATERIALIZED VIEW: APPS.OPI_INV_VAL_SUM_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,
         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,
         f.source source,
         grouping_id (f.organization_id,
                      decode (f.subinventory_code,
                              NULL, NULL,
                              -1, -1,
                              (f.subinventory_code||'-'||f.organization_id)),
                      f.source,
                      f.primary_uom,
                      (i.inventory_item_id||'-'||i.organization_id),
                      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),
                 30, c.report_date_julian,
                 29, c.week_id,
                 27, c.ent_period_id,
                 23, 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),
                 30, 1,
                 29, 16,
                 27, 32,
                 23, 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,
         f.primary_UOM uom_code,
         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.onhand_qty) onhand_qty,
         COUNT(f.onhand_qty) count_o_qty,
         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.intransit_qty) intransit_qty,
         COUNT(f.intransit_qty) count_i_qty,
         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,
         SUM (f.onhand_value_b * f.sec_conversion_rate) onhand_value_sg,
         COUNT (f.onhand_value_b * f.sec_conversion_rate) count_ovsg,
         SUM (f.intransit_value_b * f.sec_conversion_rate) intransit_value_sg,
         COUNT (f.intransit_value_b * f.sec_conversion_rate) count_ivsg,
         SUM (f.wip_value_b * f.sec_conversion_rate) wip_value_sg,
         COUNT (f.wip_value_b * f.sec_conversion_rate) count_wvsg,
         SUM ((f.onhand_value_b + f.intransit_value_b + f.wip_value_b) * f.sec_conversion_rate) inv_total_value_sg,
         COUNT ((f.onhand_value_b + f.intransit_value_b + f.wip_value_b) * f.sec_conversion_rate) count_I_T_V_SG,
         count(*)  count_total
     FROM  OPI.OPI_DBI_INV_VALUE_F f,
           FII.FII_TIME_DAY c,
           ENI.ENI_OLTP_ITEM_STAR i
     WHERE i.organization_id = f.organization_id
       AND i.inventory_item_id = f.inventory_item_id
       AND c.report_date = f.transaction_date
     GROUP BY f.organization_id,
              f.source,
              grouping sets ( (),
                              (decode (f.subinventory_code,
                                       NULL, NULL,
                                       -1, -1,
                                       (f.subinventory_code||'-'||f.organization_id)),
                                       nvl(i.inv_category_id,-1)),
                              (decode(f.subinventory_code,
                                      NULL, NULL,
                                      -1, -1,
                                      (f.subinventory_code||'-'||f.organization_id))),
                              (nvl(i.inv_category_id,-1)),
                              (decode(f.subinventory_code,
                                      NULL, NULL,
                                      -1, -1,
                                      (f.subinventory_code||'-'||f.organization_id)),
                               nvl(i.inv_category_id,-1),
                               f.primary_uom,
                               i.inventory_item_id||'-'||i.organization_id)),
             grouping sets (c.ent_year_id, c.ent_qtr_id,
                            c.ent_period_id, c.week_id,
                            c.report_date_julian)