View: WIP_WS_PTPKPI_AGG_V

Product:	WIP - Work in Process
Description:	MES Production to Plan KPI aggregated View
Implementation/DBA Data:	APPS.WIP_WS_PTPKPI_AGG_V

View Text

SELECT NVL(PLAN1.ORGANIZATION_ID
, ACTUAL.ORGANIZATION_ID) AS ORGANIZATION_ID
, NVL(PLAN1.DEPARTMENT_ID
, ACTUAL.DEPARTMENT_ID) AS DEPARTMENT_ID
, NVL(PLAN1.WIP_ENTITY_ID
, ACTUAL.WIP_ENTITY_ID) AS WIP_ENTITY_ID
, NVL(PLAN1.OPERATION_SEQ_NUM
, ACTUAL.OPERATION_SEQ_NUM) AS OPERATION_SEQ_NUM
, NVL(PLAN1.SHIFT_ID
, ACTUAL.SHIFT_ID) AS SHIFT_ID
, NVL(PLAN1.PLANNED_QTY
, 0) AS PLANNED_QTY
, (CASE WHEN ACTUAL.SHIFT_ID = PLAN1.SHIFT_ID THEN (CASE WHEN NVL(ACTUAL.MOVED_QTY
, 0) > NVL(PLAN1.PLANNED_QTY
, 0) THEN NVL(PLAN1.PLANNED_QTY
, 0) ELSE NVL(ACTUAL.MOVED_QTY
, 0) END) ELSE 0 END ) AS MOVED_AGAINST_PLANNED_QTY
, (CASE WHEN PLAN1.SHIFT_ID IS NULL THEN NVL(ACTUAL.MOVED_QTY
, 0) WHEN (ACTUAL.SHIFT_ID = PLAN1.SHIFT_ID
AND NVL(ACTUAL.MOVED_QTY
, 0) > NVL(PLAN1.PLANNED_QTY
, 0)) THEN (NVL(ACTUAL.MOVED_QTY
, 0) - NVL(PLAN1.PLANNED_QTY
, 0)) ELSE 0 END ) AS MOVED_AGAINST_UNPLANNED_QTY
, NVL(ACTUAL.SCRAPPED_QTY
, 0) AS SCRAPPED_QTY
, NVL(ACTUAL.REJECTED_QTY
, 0) AS REJECTED_QTY
, NVL(PLAN1.PRIMARY_UOM_CODE
, ACTUAL.PRIMARY_UOM_CODE) AS PRIMARY_UOM_CODE
, NVL(PLAN1.OP_LEAD_TIME
, ACTUAL.OP_LEAD_TIME) AS OP_LEAD_TIME
, NVL(PLAN1.SHIFT_START_TIME
, ACTUAL.SHIFT_START_TIME) AS SHIFT_START_TIME
FROM WIP_WS_PTPKPI_PLAN PLAN1 FULL OUTER JOIN WIP_WS_PTPKPI_ACTUAL ACTUAL ON PLAN1.ORGANIZATION_ID = ACTUAL.ORGANIZATION_ID
AND PLAN1.DEPARTMENT_ID = ACTUAL.DEPARTMENT_ID
AND PLAN1.WIP_ENTITY_ID = ACTUAL.WIP_ENTITY_ID
AND PLAN1.OPERATION_SEQ_NUM = ACTUAL.OPERATION_SEQ_NUM
AND PLAN1.SHIFT_ID = ACTUAL.SHIFT_ID

Columns