[Home] [Help]
MATERIALIZED VIEW: APPS.OPI_WMS_007_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
,e.reason_id REASON_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)
, e.reason_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)
,e.reason_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(e.exceptions) EXCEPTIONS
,COUNT(e.exceptions) EXCEPTIONS_CNT
,COUNT(*) TOTAL_CNT
FROM
OPI.OPI_DBI_WMS_TASKS_F f
, OPI.OPI_DBI_WMS_EX_F e
, FII.FII_TIME_DAY c
, ENI.ENI_OLTP_ITEM_STAR i
WHERE e.organization_id = f.organization_id
AND e.subinventory_code = f.subinventory_code
AND e.inventory_item_id = f.inventory_item_id
AND e.completion_date = f.completion_date
AND i.organization_id = f.organization_id
AND i.inventory_item_id = f.inventory_item_id
AND i.organization_id = e.organization_id
AND i.inventory_item_id = e.inventory_item_id
AND c.report_date = f.completion_date
AND c.report_date = e.completion_date
AND e.operation_plan_indicator = 1
GROUP BY
f.organization_id, e.reason_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)