View: WSM_NEXT_OPERATIONS_V

Product:	WSM - Shop Floor Management
Description:	View for getting all valid next operations for a Job based on the network routing.
Implementation/DBA Data:	APPS.WSM_NEXT_OPERATIONS_V

View Text

SELECT /* NEXT OPERATION ACCORDING TO THE BON */ WO.WIP_ENTITY_ID
, WO.OPERATION_SEQ_NUM
, TO_NUMBER(NULL)
, /* ADDED TO_NUMBER SO THAT TO_OPERATION_SEQ_NUM IS OF DATATYPE NUMBER */ WO2.OPERATION_SEQ_NUM + NVL(WP.OP_SEQ_NUM_INCREMENT
, 10)
, /* CZH.I_9999 */ BOS.OPERATION_SEQ_NUM
, WO.OPERATION_SEQUENCE_ID
, BOS.OPERATION_SEQUENCE_ID
, BSO1.OPERATION_CODE
, BSO2.OPERATION_CODE
, BOS.OPERATION_DESCRIPTION
, BOS.STANDARD_OPERATION_ID
, BD.DEPARTMENT_CODE
, BOS.DEPARTMENT_ID
FROM BOM_STANDARD_OPERATIONS BSO1
, BOM_STANDARD_OPERATIONS BSO2
, BOM_DEPARTMENTS BD
, BOM_OPERATION_SEQUENCES BOS
, BOM_OPERATIONAL_ROUTINGS BOR
, BOM_OPERATION_NETWORKS BON
, WIP_OPERATIONS WO
, WIP_OPERATIONS WO2
, WIP_DISCRETE_JOBS WDJ
, WSM_PARAMETERS WP
WHERE WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND BOR.ASSEMBLY_ITEM_ID = DECODE(WDJ.JOB_TYPE
, 1
, WDJ.PRIMARY_ITEM_ID
, WDJ.ROUTING_REFERENCE_ID)
AND BOS.ROUTING_SEQUENCE_ID = NVL(WDJ.COMMON_ROUTING_SEQUENCE_ID
, WDJ.ROUTING_REFERENCE_ID)/*BBK1550565*/
AND BOS.ROUTING_SEQUENCE_ID = NVL(BOR.COMMON_ROUTING_SEQUENCE_ID
, BOR.ROUTING_SEQUENCE_ID)
AND NVL(WDJ.ROUTING_REVISION_DATE
, SYSDATE) >= NVL(BOS.EFFECTIVITY_DATE
, NVL(WDJ.ROUTING_REVISION_DATE
, SYSDATE))
AND NVL(WDJ.ROUTING_REVISION_DATE
, SYSDATE) <= NVL(BOS.DISABLE_DATE
, NVL(WDJ.ROUTING_REVISION_DATE
, SYSDATE)+1)
AND NVL(WDJ.ROUTING_REVISION_DATE
, SYSDATE) >= NVL(BON.EFFECTIVITY_DATE
, NVL(WDJ.ROUTING_REVISION_DATE
, SYSDATE)) /* NO USE */
AND NVL(WDJ.ROUTING_REVISION_DATE
, SYSDATE) <= NVL(BON.DISABLE_DATE
, NVL(WDJ.ROUTING_REVISION_DATE
, SYSDATE)+1) /* NO USE */
AND BSO1.STANDARD_OPERATION_ID(+) = WO.STANDARD_OPERATION_ID
AND BSO2.STANDARD_OPERATION_ID(+) = BOS.STANDARD_OPERATION_ID
AND BD.DEPARTMENT_ID = BOS.DEPARTMENT_ID /* BA: CZH.I_OED-2 */ /* CZH: REMOVE CALLING WSMPUTIL.REPLACEMENT_OP_SEQ_ID
AND NVL(WSMPUTIL.REPLACEMENT_OP_SEQ_ID(BON.FROM_OP_SEQ_ID
, WDJ.ROUTING_REVISION_DATE)
, -1) = WO.OPERATION_SEQUENCE_ID
AND NVL(WSMPUTIL.REPLACEMENT_OP_SEQ_ID(BON.TO_OP_SEQ_ID
, WDJ.ROUTING_REVISION_DATE)
, -1) = BOS.OPERATION_SEQUENCE_ID */
AND BON.FROM_OP_SEQ_ID IN ( SELECT B.OPERATION_SEQUENCE_ID
FROM BOM_OPERATION_SEQUENCES B
, BOM_OPERATION_SEQUENCES B2
WHERE B.OPERATION_SEQ_NUM = B2.OPERATION_SEQ_NUM
AND B.ROUTING_SEQUENCE_ID = B2.ROUTING_SEQUENCE_ID
AND B2.OPERATION_SEQUENCE_ID = WO.OPERATION_SEQUENCE_ID )
AND BON.TO_OP_SEQ_ID IN ( SELECT B.OPERATION_SEQUENCE_ID
FROM BOM_OPERATION_SEQUENCES B
, BOM_OPERATION_SEQUENCES B2
WHERE B.OPERATION_SEQ_NUM = B2.OPERATION_SEQ_NUM
AND B.ROUTING_SEQUENCE_ID = B2.ROUTING_SEQUENCE_ID
AND B2.OPERATION_SEQUENCE_ID = BOS.OPERATION_SEQUENCE_ID ) /* EA: CZH.I_OED-2 */
AND WO2.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WO2.OPERATION_SEQ_NUM = ( SELECT MAX(OPERATION_SEQ_NUM)
FROM WIP_OPERATIONS
WHERE WIP_ENTITY_ID = WO2.WIP_ENTITY_ID )
AND WP.ORGANIZATION_ID = WO.ORGANIZATION_ID UNION SELECT /* CURRENT OPERATION ITSELF */ WO.WIP_ENTITY_ID
, WO.OPERATION_SEQ_NUM
, TO_NUMBER(NULL)
, /* ADDED TO_NUMBER SO THAT TO_OPERATION_SEQ_NUM IS OF DATATYPE NUMBER */ WO.OPERATION_SEQ_NUM
, BOS.OPERATION_SEQ_NUM
, WO.OPERATION_SEQUENCE_ID
, BOS.OPERATION_SEQUENCE_ID
, BSO.OPERATION_CODE
, BSO.OPERATION_CODE
, NVL(BSO.OPERATION_DESCRIPTION
, BOS.OPERATION_DESCRIPTION)
, WO.STANDARD_OPERATION_ID
, BD.DEPARTMENT_CODE
, WO.DEPARTMENT_ID
FROM BOM_OPERATION_SEQUENCES BOS
, BOM_STANDARD_OPERATIONS BSO
, BOM_DEPARTMENTS BD
, WIP_OPERATIONS WO
, WIP_DISCRETE_JOBS WDJ
WHERE BOS.OPERATION_SEQUENCE_ID = WO.OPERATION_SEQUENCE_ID
AND BSO.STANDARD_OPERATION_ID(+) = BOS.STANDARD_OPERATION_ID
AND WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND BD.DEPARTMENT_ID = BOS.DEPARTMENT_ID

Columns