[Home] [Help]
MATERIALIZED VIEW: APPS.OPI_WMS_006_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
, NVL(i.inv_category_id,-1) INV_CATEGORY_ID
, (f.inventory_item_id||'-'||f.organization_id) ITEM_ORG_ID
, GROUPING_ID( f.organization_id
,DECODE (f.subinventory_code
,NULL,NULL
,-1,-1
,(f.subinventory_code||'-'||f.organization_id))
,NVL(i.inv_category_id,-1)
,(f.inventory_item_id||'-'||f.organization_id)) AGG_LEVEL
, GROUPING_ID( f.organization_id
,DECODE (f.subinventory_code
,NULL,NULL
,-1,-1
,(f.subinventory_code||'-'||f.organization_id))
,NVL(i.inv_category_id,-1)
,(f.inventory_item_id||'-'||f.organization_id)
, 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
, NULL) 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
, NULL) PERIOD_TYPE_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.picks) PICKS
, COUNT(f.picks) PICKS_CNT
, SUM(f.picks_with_exceptions) PICKS_WITH_EXCEPTIONS
, COUNT(f.picks_with_exceptions) PICKS_WITH_EXCEPTIONS_CNT
, SUM(f.pick_exceptions) PICK_EXCEPTIONS
, COUNT(f.pick_exceptions) PICK_EXCEPTIONS_CNT
, COUNT(*) TOTAL_CNT
FROM
OPI.OPI_DBI_WMS_TASKS_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.completion_date
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)
, (f.inventory_item_id||'-'||f.organization_id))
, c.ent_qtr_id
, ROLLUP(c.ent_period_id, c.week_id, c.report_date_julian)