[Home] [Help]
MATERIALIZED VIEW: APPS.OPI_WMS_009_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
, f.operation_plan_id OPERATION_PLAN_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)
, f.operation_plan_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)
, f.operation_plan_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_OP_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.operation_plan_id = f.operation_plan_id
AND e.operation_plan_status = f.status
AND e.completion_date = f.plan_execution_end_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.plan_execution_end_date
AND c.report_date = e.completion_date
AND e.operation_plan_indicator = 2
AND f.status = 3
GROUP BY
f.organization_id
, f.operation_plan_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)