[Home] [Help]
MATERIALIZED VIEW: APPS.OPI_WMS_008_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
, 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) 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
, 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.plan_elapsed_time) ELAPSED_TIME
, count(f.plan_elapsed_time) ELAPSED_TIME_CNT
, sum(f.executions) EXECUTIONS
, count(f.executions) EXECUTIONS_CNT
, sum(f.executions_with_exceptions) EXEC_WITH_EXCEPTIONS
, count(f.executions_with_exceptions) EXEC_WITH_EXCEPTIONS_CNT
, sum(f.exceptions) EXCEPTIONS
, count(f.exceptions) EXCEPTIONS_CNT
, count(*) TOTAL_CNT
FROM
OPI.OPI_DBI_WMS_OP_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.plan_execution_end_date
AND f.status = 3
GROUP BY
f.organization_id, f.operation_plan_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)