MATERIALIZED VIEW: APPS.OPI_WMS_002_MV

Source

Select /* 12.0: bug#4526784 */	grouping_id(f.receiving_org_id,
                    nvl(item.inv_category_id,-1),
                    (f.inventory_item_id||'-'||f.receiving_org_id)) AGG_LEVEL,
        grouping_id(f.receiving_org_id,
                    nvl(item.inv_category_id,-1),
                    (f.inventory_item_id||'-'||f.receiving_org_id),
                    f.primary_uom_code,
                    c.ent_qtr_id,
                    c.ent_period_id,
                    c.week_id,
                    c.report_date_julian) GRP_ID,
	 decode(grouping_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) time_id,
         decode(grouping_id(c.ent_qtr_id,c.ent_period_id,c.week_id,c.report_date_julian),
                0, 1,
                1, 16,
                3, 32,
                7, 64) PERIOD_TYPE_ID,
	 f.receiving_org_id organization_id,
         nvl(item.inv_category_id, -1) inv_category_id,
         f.inventory_item_id || '-' || f.receiving_org_id item_org_id,
         f.primary_uom_code uom_code,
         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.primary_quantity) quantity,
         count(f.primary_quantity) c_quantity,
         count(case when f.transaction_type in ('RECEIVE', 'MATCH') then f.primary_quantity end) transactions_cnt,
         count(*)
 FROM POA.POA_DBI_RTX_F f,
      FII.FII_TIME_DAY c,
      ENI.ENI_OLTP_ITEM_STAR item
WHERE f.receiving_org_id = item.organization_id
  AND f.inventory_item_id = item.inventory_item_id
  AND f.wms_grp_txn_date = c.report_date
  AND f.transaction_type in ('RECEIVE','MATCH','CORRECT')
  AND nvl(f.parent_transaction_type, 'RECEIVE') in ('RECEIVE','MATCH', 'UNORDERED')
  AND f.wms_enabled_flag = 'Y'
  AND f.routing_header_id in (1, 2)
GROUP BY f.receiving_org_id,
         rollup((nvl(item.inv_category_id,-1),
                f.inventory_item_id || '-' || f.receiving_org_id,
                f.primary_uom_code)),
         c.ent_qtr_id,rollup(c.ent_period_id,c.week_id,c.report_date_julian)