VIEW: APPS.WIP_WS_PTPKPI_AGG_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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