View: WSM_WIP_LOT_TXNS_V

Product:	WSM - Shop Floor Management
Description:
Implementation/DBA Data:	APPS.WSM_WIP_LOT_TXNS_V

View Text

SELECT WSMT.TRANSACTION_DATE
, ML.MEANING TRANSACTION_TYPE
, WSSJ.WIP_ENTITY_NAME PREV_WIP_ENTITY_NAME
, WSSJ.AVAILABLE_QUANTITY PREV_START_QUANTITY
, WSSJ.WIP_ENTITY_ID PREV_WIP_ENTITY_ID
, BOR1.ALTERNATE_ROUTING_DESIGNATOR PREV_ALT_ROUTING_DESIGNATOR
, WSSJ.PRIMARY_ITEM_ID PREV_PRIMARY_ITEM_ID
, WSRJ.WIP_ENTITY_NAME CHG_WIP_ENTITY_NAME
, WSRJ.WIP_ENTITY_ID CHG_WIP_ENTITY_ID
, WSRJ.START_QUANTITY CHG_START_QUANTITY
, WSRJ.ALTERNATE_ROUTING_DESIGNATOR CHG_ALT_ROUTING_DESIGNATOR
, WSRJ.PRIMARY_ITEM_ID CHG_PRIMARY_ITEM_ID
, WE.GEN_OBJECT_ID OBJECT_ID
, 5 OBJECT_TYPE
, WSMT.CREATED_BY
, WSMT.TRANSACTION_ID
FROM WSM_SPLIT_MERGE_TRANSACTIONS WSMT
, MFG_LOOKUPS ML
, WSM_SM_RESULTING_JOBS WSRJ
, WSM_SM_STARTING_JOBS WSSJ
, WIP_ENTITIES WE
, BOM_OPERATIONAL_ROUTINGS BOR1
WHERE WSMT.TRANSACTION_TYPE_ID=ML.LOOKUP_CODE
AND WSMT.STATUS=4
AND ML.LOOKUP_TYPE='WSM_WIP_LOT_TXN_TYPE'
AND WSMT.TRANSACTION_TYPE_ID IN (1
, 2
, 3
, 5
, 6
, 7)
AND WSSJ.TRANSACTION_ID=WSMT.TRANSACTION_ID
AND WSSJ.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
AND WSRJ.TRANSACTION_ID=WSMT.TRANSACTION_ID
AND WSSJ.ROUTING_SEQ_ID = BOR1.ROUTING_SEQUENCE_ID (+) UNION ALL SELECT WSMT.TRANSACTION_DATE
, ML.MEANING TRANSACTION_TYPE
, WSSJ.WIP_ENTITY_NAME PREV_WIP_ENTITY_NAME
, WSSJ.AVAILABLE_QUANTITY PREV_START_QUANTITY
, WSSJ.WIP_ENTITY_ID PREV_WIP_ENTITY_ID
, BOR1.ALTERNATE_ROUTING_DESIGNATOR PREV_ALT_ROUTING_DESIGNATOR
, WSSJ.PRIMARY_ITEM_ID PREV_PRIMARY_ITEM_ID
, WSRJ.WIP_ENTITY_NAME CHG_WIP_ENTITY_NAME
, WSRJ.WIP_ENTITY_ID CHG_WIP_ENTITY_ID
, WSRJ.START_QUANTITY CHG_START_QUANTITY
, WSRJ.ALTERNATE_ROUTING_DESIGNATOR CHG_ALT_ROUTING_DESIGNATOR
, WSRJ.PRIMARY_ITEM_ID CHG_PRIMARY_ITEM_ID
, WE.GEN_OBJECT_ID OBJECT_ID
, 5 OBJECT_TYPE
, WSMT.CREATED_BY
, WSMT.TRANSACTION_ID
FROM WSM_SPLIT_MERGE_TRANSACTIONS WSMT
, MFG_LOOKUPS ML
, WSM_SM_RESULTING_JOBS WSRJ
, WSM_SM_STARTING_JOBS WSSJ
, WIP_ENTITIES WE
, BOM_OPERATIONAL_ROUTINGS BOR1
WHERE WSMT.TRANSACTION_TYPE_ID=ML.LOOKUP_CODE
AND WSMT.STATUS=4
AND ML.LOOKUP_TYPE='WSM_WIP_LOT_TXN_TYPE'
AND WSMT.TRANSACTION_TYPE_ID IN (1
, 2)
AND WSSJ.TRANSACTION_ID=WSMT.TRANSACTION_ID
AND WSRJ.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
AND WSRJ.WIP_ENTITY_ID NOT IN ( SELECT WSSJ1.WIP_ENTITY_ID
FROM WSM_SM_STARTING_JOBS WSSJ1
WHERE WSSJ1.TRANSACTION_ID = WSSJ.TRANSACTION_ID )
AND WSRJ.TRANSACTION_ID=WSMT.TRANSACTION_ID
AND WSSJ.ROUTING_SEQ_ID = BOR1.ROUTING_SEQUENCE_ID (+)
AND WSMT.TRANSACTION_ID = ( SELECT MIN ( WSMT1.TRANSACTION_ID )
FROM WSM_SPLIT_MERGE_TRANSACTIONS WSMT1
, WSM_SM_RESULTING_JOBS WSRJ1
WHERE WSRJ1.TRANSACTION_ID = WSMT1.TRANSACTION_ID
AND WSRJ1.WIP_ENTITY_ID = WE.WIP_ENTITY_ID ) UNION ALL SELECT WSMT.TRANSACTION_DATE
, ML.MEANING TRANSACTION_TYPE
, NULL PREV_WIP_ENTITY_NAME
, TO_NUMBER( NULL) PREV_START_QUANTITY
, TO_NUMBER(NULL) PREV_WIP_ENTITY_ID
, NULL PREV_ALT_ROUTING_DESIGNATOR
, TO_NUMBER(NULL) PREV_PRIMARY_ITEM_ID
, WSRJ.WIP_ENTITY_NAME CHG_WIP_ENTITY_NAME
, WSRJ.WIP_ENTITY_ID CHG_WIP_ENTITY_ID
, WSRJ.START_QUANTITY CHG_START_QUANTITY
, WSRJ.ALTERNATE_ROUTING_DESIGNATOR CHG_ALT_ROUTING_DESIGNATOR
, WSRJ.PRIMARY_ITEM_ID CHG_PRIMARY_ITEM_ID
, WE.GEN_OBJECT_ID OBJECT_ID
, 5 OBJECT_TYPE
, WSMT.CREATED_BY
, WSMT.TRANSACTION_ID
FROM WSM_SPLIT_MERGE_TRANSACTIONS WSMT
, MFG_LOOKUPS ML
, WSM_SM_RESULTING_JOBS WSRJ
, WIP_ENTITIES WE
WHERE WSMT.TRANSACTION_TYPE_ID=ML.LOOKUP_CODE
AND WSMT.TRANSACTION_ID = WSRJ.TRANSACTION_ID
AND WSMT.STATUS=4
AND ML.LOOKUP_TYPE='WSM_WIP_LOT_TXN_TYPE'
AND WSMT.TRANSACTION_TYPE_ID = 4
AND WSRJ.WIP_ENTITY_ID=WE.WIP_ENTITY_ID

Columns