WIE_WO_SUPPLY

Details

Schema: FUSION
Object owner: WIE
Object type: VIEW

Columns

Name
ORGANIZATION_ID WORK_ORDER_ID WORK_ORDER_NUMBER WORK_ORDER_DESCRIPTION WORK_METHOD_ID WORK_METHOD_CODE WORK_ORDER_TYPE WORK_ORDER_SUB_TYPE WORK_ORDER_STATUS_ID USER_STATUS_CODE SYSTEM_STATUS_CODE INVENTORY_ITEM_ID ITEM_REVISION UOM_CODE PLANNED_START_DATE PLANNED_COMPLETION_DATE RELEASED_DATE ACTUAL_START_DATE ACTUAL_COMPLETION_DATE PLANNED_START_QUANTITY NETTABLE_SUPPLY_QTY_OVERRIDE COMPLETED_QUANTITY SCRAPPED_QUANTITY REJECTED_QUANTITY SUPPLY_QUANTITY PRIMARY_FLAG OPERATION_SEQ_NUMBER BATCH_QUANTITY BATCH_UOM_CODE

Name

ORGANIZATION_ID

WORK_ORDER_ID

WORK_ORDER_NUMBER

WORK_ORDER_DESCRIPTION

WORK_METHOD_ID

WORK_METHOD_CODE

WORK_ORDER_TYPE

WORK_ORDER_SUB_TYPE

WORK_ORDER_STATUS_ID

USER_STATUS_CODE

SYSTEM_STATUS_CODE

INVENTORY_ITEM_ID

ITEM_REVISION

UOM_CODE

PLANNED_START_DATE

PLANNED_COMPLETION_DATE

RELEASED_DATE

ACTUAL_START_DATE

ACTUAL_COMPLETION_DATE

PLANNED_START_QUANTITY

NETTABLE_SUPPLY_QTY_OVERRIDE

COMPLETED_QUANTITY

SCRAPPED_QUANTITY

REJECTED_QUANTITY

SUPPLY_QUANTITY

PRIMARY_FLAG

OPERATION_SEQ_NUMBER

BATCH_QUANTITY

BATCH_UOM_CODE

Query

SQL_Statement
SELECT WWO.ORGANIZATION_ID ORGANIZATION_ID, WWO.WORK_ORDER_ID WORK_ORDER_ID, WWO.WORK_ORDER_NUMBER WORK_ORDER_NUMBER, WWO.WORK_ORDER_DESCRIPTION WORK_ORDER_DESCRIPTION, WWO.WORK_METHOD_ID WORK_METHOD_ID, WWM.WORK_METHOD_CODE WORK_METHOD_CODE, WWO.WORK_ORDER_TYPE WORK_ORDER_TYPE, WWO.WORK_ORDER_SUB_TYPE WORK_ORDER_SUB_TYPE, WWO.WORK_ORDER_STATUS_ID WORK_ORDER_STATUS_ID, WWS.WO_STATUS_CODE USER_STATUS_CODE, WWS.WO_SYSTEM_STATUS_CODE SYSTEM_STATUS_CODE, WWO.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, WWO.ITEM_REVISION ITEM_REVISION, WWO.UOM_CODE UOM_CODE, WWO.PLANNED_START_DATE PLANNED_START_DATE, WWO.PLANNED_COMPLETION_DATE PLANNED_COMPLETION_DATE, WWO.RELEASED_DATE RELEASED_DATE, WWO.ACTUAL_START_DATE ACTUAL_START_DATE, WWO.ACTUAL_COMPLETION_DATE ACTUAL_COMPLETION_DATE, WWO.PLANNED_START_QUANTITY PLANNED_START_QUANTITY, WWO.NETTABLE_SUPPLY_QTY_OVERRIDE NETTABLE_SUPPLY_QTY_OVERRIDE, WWO.COMPLETED_QUANTITY COMPLETED_QUANTITY, WWO.SCRAPPED_QUANTITY SCRAPPED_QUANTITY, WWO.REJECTED_QUANTITY REJECTED_QUANTITY, DECODE(WWS.WO_SYSTEM_STATUS_CODE, 'CANCELED', 0, 'CLOSED', 0, WWO.PLANNED_START_QUANTITY - (NVL(WWO.COMPLETED_QUANTITY,0) + NVL(WWO.SCRAPPED_QUANTITY,0))) SUPPLY_QUANTITY, 'Y' PRIMARY_FLAG, (SELECT MAX(OPERATION_SEQ_NUMBER) FROM WIE_WO_OPERATIONS_B OP WHERE OP.WORK_ORDER_ID=WWO.WORK_ORDER_ID) OPERATION_SEQ_NUMBER, WWO.PLANNED_START_QUANTITY BATCH_QUANTITY, WWO.UOM_CODE BATCH_UOM_CODE FROM WIE_WORK_ORDERS_VL WWO, WIE_WO_STATUSES_B WWS, WIS_WORK_METHODS_B WWM WHERE WWS.WO_STATUS_ID = WWO.WORK_ORDER_STATUS_ID AND WWM.WORK_METHOD_ID = WWO.WORK_METHOD_ID AND WWM.WORK_METHOD_CODE <> 'PROCESS_MANUFACTURING' UNION ALL SELECT WWO.ORGANIZATION_ID ORGANIZATION_ID, WWO.WORK_ORDER_ID WORK_ORDER_ID, WWO.WORK_ORDER_NUMBER WORK_ORDER_NUMBER, WWO.WORK_ORDER_DESCRIPTION WORK_ORDER_DESCRIPTION, WWO.WORK_METHOD_ID WORK_METHOD_ID, WWM.WORK_METHOD_CODE WORK_METHOD_CODE, WWO.WORK_ORDER_TYPE WORK_ORDER_TYPE, WWO.WORK_ORDER_SUB_TYPE WORK_ORDER_SUB_TYPE, WWO.WORK_ORDER_STATUS_ID WORK_ORDER_STATUS_ID, WWS.WO_STATUS_CODE USER_STATUS_CODE, WWS.WO_SYSTEM_STATUS_CODE SYSTEM_STATUS_CODE, OUTPUTS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, NULL ITEM_REVISION, ITEM.PRIMARY_UOM_CODE UOM_CODE, WOOP.PLANNED_START_DATE PLANNED_START_DATE, WOOP.PLANNED_COMPLETION_DATE PLANNED_COMPLETION_DATE, WWO.RELEASED_DATE RELEASED_DATE, WOOP.ACTUAL_START_DATE ACTUAL_START_DATE, WOOP.ACTUAL_COMPLETION_DATE ACTUAL_COMPLETION_DATE, OUTPUTS.OUTPUT_QUANTITY PLANNED_START_QUANTITY, NULL NETTABLE_SUPPLY_QTY_OVERRIDE, OUTPUTS.COMPLETED_QUANTITY COMPLETED_QUANTITY, WOOP.SCRAPPED_QUANTITY SCRAPPED_QUANTITY, NULL REJECTED_QUANTITY, INV_CONVERT.INV_UM_CONVERT(OUTPUTS.INVENTORY_ITEM_ID,NULL,(OUTPUTS.OUTPUT_QUANTITY - (((NVL(WOOP.SCRAPPED_QUANTITY,0) + NVL(WOOP.CUMULATIVE_SCRAP_QUANTITY,0))/WWO.PLANNED_START_QUANTITY)*OUTPUTS.OUTPUT_QUANTITY + NVL(OUTPUTS.COMPLETED_QUANTITY,0))),OUTPUTS.UOM_CODE,ITEM.PRIMARY_UOM_CODE,NULL,NULL) SUPPLY_QUANTITY, OUTPUTS.PRIMARY_FLAG PRIMARY_FLAG, WOOP.OPERATION_SEQ_NUMBER OPERATION_SEQ_NUMBER, WWO.PLANNED_START_QUANTITY BATCH_QUANTITY, WWO.UOM_CODE BATCH_UOM_CODE FROM WIE_WO_OPERATION_OUTPUTS OUTPUTS, WIE_WO_OPERATIONS_B WOOP, EGP_SYSTEM_ITEMS_VL ITEM, WIE_WORK_ORDERS_VL WWO, WIS_WORK_METHODS_B WWM, WIE_WO_STATUSES_B WWS WHERE WOOP.WO_OPERATION_ID = OUTPUTS.WO_OPERATION_ID AND WWO.WORK_ORDER_ID = WOOP.WORK_ORDER_ID AND ITEM.INVENTORY_ITEM_ID = OUTPUTS.INVENTORY_ITEM_ID AND ITEM.ORGANIZATION_ID = OUTPUTS.ORGANIZATION_ID AND WWO.WORK_ORDER_ID = OUTPUTS.WORK_ORDER_ID AND WWM.WORK_METHOD_ID = WWO.WORK_METHOD_ID AND WWM.WORK_METHOD_CODE = 'PROCESS_MANUFACTURING' AND WWS.WO_STATUS_ID = WWO.WORK_ORDER_STATUS_ID

SQL_Statement

SELECT WWO.ORGANIZATION_ID ORGANIZATION_ID,

WWO.WORK_ORDER_ID WORK_ORDER_ID,

WWO.WORK_ORDER_NUMBER WORK_ORDER_NUMBER,

WWO.WORK_ORDER_DESCRIPTION WORK_ORDER_DESCRIPTION,

WWO.WORK_METHOD_ID WORK_METHOD_ID,

WWM.WORK_METHOD_CODE WORK_METHOD_CODE,

WWO.WORK_ORDER_TYPE WORK_ORDER_TYPE,

WWO.WORK_ORDER_SUB_TYPE WORK_ORDER_SUB_TYPE,

WWO.WORK_ORDER_STATUS_ID WORK_ORDER_STATUS_ID,

WWS.WO_STATUS_CODE USER_STATUS_CODE,

WWS.WO_SYSTEM_STATUS_CODE SYSTEM_STATUS_CODE,

WWO.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,

WWO.ITEM_REVISION ITEM_REVISION,

WWO.UOM_CODE UOM_CODE,

WWO.PLANNED_START_DATE PLANNED_START_DATE,

WWO.PLANNED_COMPLETION_DATE PLANNED_COMPLETION_DATE,

WWO.RELEASED_DATE RELEASED_DATE,

WWO.ACTUAL_START_DATE ACTUAL_START_DATE,

WWO.ACTUAL_COMPLETION_DATE ACTUAL_COMPLETION_DATE,

WWO.PLANNED_START_QUANTITY PLANNED_START_QUANTITY,

WWO.NETTABLE_SUPPLY_QTY_OVERRIDE NETTABLE_SUPPLY_QTY_OVERRIDE,

WWO.COMPLETED_QUANTITY COMPLETED_QUANTITY,

WWO.SCRAPPED_QUANTITY SCRAPPED_QUANTITY,

WWO.REJECTED_QUANTITY REJECTED_QUANTITY,

DECODE(WWS.WO_SYSTEM_STATUS_CODE, 'CANCELED', 0, 'CLOSED', 0, WWO.PLANNED_START_QUANTITY - (NVL(WWO.COMPLETED_QUANTITY,0) + NVL(WWO.SCRAPPED_QUANTITY,0))) SUPPLY_QUANTITY,

'Y' PRIMARY_FLAG,

(SELECT MAX(OPERATION_SEQ_NUMBER) FROM WIE_WO_OPERATIONS_B OP WHERE OP.WORK_ORDER_ID=WWO.WORK_ORDER_ID) OPERATION_SEQ_NUMBER,

WWO.PLANNED_START_QUANTITY BATCH_QUANTITY,

WWO.UOM_CODE BATCH_UOM_CODE

FROM WIE_WORK_ORDERS_VL WWO,

WIE_WO_STATUSES_B WWS,

WIS_WORK_METHODS_B WWM

WHERE WWS.WO_STATUS_ID = WWO.WORK_ORDER_STATUS_ID

AND WWM.WORK_METHOD_ID = WWO.WORK_METHOD_ID

AND WWM.WORK_METHOD_CODE <> 'PROCESS_MANUFACTURING'

UNION ALL

SELECT WWO.ORGANIZATION_ID ORGANIZATION_ID,

WWO.WORK_ORDER_ID WORK_ORDER_ID,

WWO.WORK_ORDER_NUMBER WORK_ORDER_NUMBER,

WWO.WORK_ORDER_DESCRIPTION WORK_ORDER_DESCRIPTION,

WWO.WORK_METHOD_ID WORK_METHOD_ID,

WWM.WORK_METHOD_CODE WORK_METHOD_CODE,

WWO.WORK_ORDER_TYPE WORK_ORDER_TYPE,

WWO.WORK_ORDER_SUB_TYPE WORK_ORDER_SUB_TYPE,

WWO.WORK_ORDER_STATUS_ID WORK_ORDER_STATUS_ID,

WWS.WO_STATUS_CODE USER_STATUS_CODE,

WWS.WO_SYSTEM_STATUS_CODE SYSTEM_STATUS_CODE,

OUTPUTS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,

NULL ITEM_REVISION,

ITEM.PRIMARY_UOM_CODE UOM_CODE,

WOOP.PLANNED_START_DATE PLANNED_START_DATE,

WOOP.PLANNED_COMPLETION_DATE PLANNED_COMPLETION_DATE,

WWO.RELEASED_DATE RELEASED_DATE,

WOOP.ACTUAL_START_DATE ACTUAL_START_DATE,

WOOP.ACTUAL_COMPLETION_DATE ACTUAL_COMPLETION_DATE,

OUTPUTS.OUTPUT_QUANTITY PLANNED_START_QUANTITY,

NULL NETTABLE_SUPPLY_QTY_OVERRIDE,

OUTPUTS.COMPLETED_QUANTITY COMPLETED_QUANTITY,

WOOP.SCRAPPED_QUANTITY SCRAPPED_QUANTITY,

NULL REJECTED_QUANTITY,

INV_CONVERT.INV_UM_CONVERT(OUTPUTS.INVENTORY_ITEM_ID,NULL,(OUTPUTS.OUTPUT_QUANTITY - (((NVL(WOOP.SCRAPPED_QUANTITY,0) + NVL(WOOP.CUMULATIVE_SCRAP_QUANTITY,0))/WWO.PLANNED_START_QUANTITY)*OUTPUTS.OUTPUT_QUANTITY + NVL(OUTPUTS.COMPLETED_QUANTITY,0))),OUTPUTS.UOM_CODE,ITEM.PRIMARY_UOM_CODE,NULL,NULL) SUPPLY_QUANTITY,

OUTPUTS.PRIMARY_FLAG PRIMARY_FLAG,

WOOP.OPERATION_SEQ_NUMBER OPERATION_SEQ_NUMBER,

WWO.PLANNED_START_QUANTITY BATCH_QUANTITY,

WWO.UOM_CODE BATCH_UOM_CODE

FROM WIE_WO_OPERATION_OUTPUTS OUTPUTS,

WIE_WO_OPERATIONS_B WOOP,

EGP_SYSTEM_ITEMS_VL ITEM,

WIE_WORK_ORDERS_VL WWO,

WIS_WORK_METHODS_B WWM,

WIE_WO_STATUSES_B WWS

WHERE WOOP.WO_OPERATION_ID = OUTPUTS.WO_OPERATION_ID

AND WWO.WORK_ORDER_ID = WOOP.WORK_ORDER_ID

AND ITEM.INVENTORY_ITEM_ID = OUTPUTS.INVENTORY_ITEM_ID

AND ITEM.ORGANIZATION_ID = OUTPUTS.ORGANIZATION_ID

AND WWO.WORK_ORDER_ID = OUTPUTS.WORK_ORDER_ID

AND WWM.WORK_METHOD_ID = WWO.WORK_METHOD_ID

AND WWM.WORK_METHOD_CODE = 'PROCESS_MANUFACTURING'

AND WWS.WO_STATUS_ID = WWO.WORK_ORDER_STATUS_ID