VIEW: APPS.WSM_WLT_SERIAL_GENEALOGY_V

Source

View Text - Preformatted

SELECT WSMT.TRANSACTION_DATE, ML.MEANING TRANSACTION_TYPE, DECODE(WSMT.transaction_type_id,6,DECODE(WST.current_wip_entity_id,WSRJ.wip_entity_id,WSSJ.WIP_ENTITY_NAME,NULL) ,WSSJ.WIP_ENTITY_NAME) PREV_WIP_ENTITY_NAME, DECODE(WSMT.transaction_type_id,6,DECODE(WST.current_wip_entity_id,WSRJ.wip_entity_id,WSSJ.AVAILABLE_QUANTITY,NULL) ,WSSJ.AVAILABLE_QUANTITY) PREV_START_QUANTITY, DECODE(WSMT.transaction_type_id,6,DECODE(WST.current_wip_entity_id,WSRJ.wip_entity_id,WSSJ.WIP_ENTITY_ID,NULL) ,WSSJ.WIP_ENTITY_ID) PREV_WIP_ENTITY_ID, DECODE(WSMT.transaction_type_id,6,DECODE(WST.current_wip_entity_id,WSRJ.wip_entity_id,BOR1.ALTERNATE_ROUTING_DESIGNATOR,NULL) ,BOR1.ALTERNATE_ROUTING_DESIGNATOR) PREV_ALT_ROUTING_DESIGNATOR, DECODE(WSMT.transaction_type_id,6,DECODE(WST.current_wip_entity_id,WSRJ.wip_entity_id,WSSJ.PRIMARY_ITEM_ID,NULL) ,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, WST.GEN_OBJECT_ID OBJECT_ID, 2 OBJECT_TYPE, WSMT.CREATED_BY, WSMT.TRANSACTION_ID FROM WSM_SPLIT_MERGE_TRANSACTIONS WSMT, WSM_SERIAL_TRANSACTIONS WST, 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 WSMT.TRANSACTION_ID = WST.TRANSACTION_ID 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 WSRJ.wip_entity_id = nvl(WST.changed_wip_entity_id,WST.current_wip_entity_id) AND WSSJ.wip_entity_id = nvl(WST.current_wip_entity_id,WST.changed_wip_entity_id) AND WSSJ.ROUTING_SEQ_ID = BOR1.ROUTING_SEQUENCE_ID (+)

View Text - HTML Formatted

SELECT WSMT.TRANSACTION_DATE
, ML.MEANING TRANSACTION_TYPE
, DECODE(WSMT.TRANSACTION_TYPE_ID
, 6
, DECODE(WST.CURRENT_WIP_ENTITY_ID
, WSRJ.WIP_ENTITY_ID
, WSSJ.WIP_ENTITY_NAME
, NULL)
, WSSJ.WIP_ENTITY_NAME) PREV_WIP_ENTITY_NAME
, DECODE(WSMT.TRANSACTION_TYPE_ID
, 6
, DECODE(WST.CURRENT_WIP_ENTITY_ID
, WSRJ.WIP_ENTITY_ID
, WSSJ.AVAILABLE_QUANTITY
, NULL)
, WSSJ.AVAILABLE_QUANTITY) PREV_START_QUANTITY
, DECODE(WSMT.TRANSACTION_TYPE_ID
, 6
, DECODE(WST.CURRENT_WIP_ENTITY_ID
, WSRJ.WIP_ENTITY_ID
, WSSJ.WIP_ENTITY_ID
, NULL)
, WSSJ.WIP_ENTITY_ID) PREV_WIP_ENTITY_ID
, DECODE(WSMT.TRANSACTION_TYPE_ID
, 6
, DECODE(WST.CURRENT_WIP_ENTITY_ID
, WSRJ.WIP_ENTITY_ID
, BOR1.ALTERNATE_ROUTING_DESIGNATOR
, NULL)
, BOR1.ALTERNATE_ROUTING_DESIGNATOR) PREV_ALT_ROUTING_DESIGNATOR
, DECODE(WSMT.TRANSACTION_TYPE_ID
, 6
, DECODE(WST.CURRENT_WIP_ENTITY_ID
, WSRJ.WIP_ENTITY_ID
, WSSJ.PRIMARY_ITEM_ID
, NULL)
, 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
, WST.GEN_OBJECT_ID OBJECT_ID
, 2 OBJECT_TYPE
, WSMT.CREATED_BY
, WSMT.TRANSACTION_ID
FROM WSM_SPLIT_MERGE_TRANSACTIONS WSMT
, WSM_SERIAL_TRANSACTIONS WST
, 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 WSMT.TRANSACTION_ID = WST.TRANSACTION_ID
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 WSRJ.WIP_ENTITY_ID = NVL(WST.CHANGED_WIP_ENTITY_ID
, WST.CURRENT_WIP_ENTITY_ID)
AND WSSJ.WIP_ENTITY_ID = NVL(WST.CURRENT_WIP_ENTITY_ID
, WST.CHANGED_WIP_ENTITY_ID)
AND WSSJ.ROUTING_SEQ_ID = BOR1.ROUTING_SEQUENCE_ID (+)