View: WMS_SUGGESTIONS_TRACE_V

Product:	WMS - Warehouse Management
Description:	WMS_SUGGESTIONS_TRACE View
Implementation/DBA Data:	APPS.WMS_SUGGESTIONS_TRACE_V

View Text

SELECT MTT.ROWID ROW_ID
, 'MTT' REC_TYPE
, 0 TYPE_CODE
, MTT.MOVE_ORDER_LINE_ID MOVE_ORDER_LINE_ID
, MTT.TRANSACTION_TEMP_ID
, MTT.ORGANIZATION_ID
, MTT.INVENTORY_ITEM_ID
, MTT.PRIMARY_QUANTITY QUANTITY
, MTT.REVISION
, MTT.LOT_NUMBER
, MTT.LOT_EXPIRATION_DATE
, '' LOT_ATTRIBUTE_CATEGORY
, MTT.SERIAL_NUMBER
, '' SERIAL_ATTRIBUTE_CATEGORY
, MTT.ORGANIZATION_ID FROM_ORGANIZATION_ID
, MTT.SUBINVENTORY_CODE FROM_SUBINVENTORY_CODE
, MTT.LOCATOR_ID FROM_LOCATOR_ID
, MTT.COST_GROUP_ID FROM_COST_GROUP_ID
, MTT.PICK_STRATEGY_ID
, MTT.PICK_RULE_ID
, DECODE(MTT.TRANSACTION_ACTION_ID
, 27
, MTT.ORGANIZATION_ID
, 12
, MTT.ORGANIZATION_ID
, MTT.TRANSFER_ORGANIZATION) TO_ORGANIZATION_ID
, DECODE(MTT.TRANSACTION_ACTION_ID
, 27
, MTT.SUBINVENTORY_CODE
, 12
, MTT.SUBINVENTORY_CODE
, MTT.TRANSFER_SUBINVENTORY ) TO_SUBINVENTORY_CODE
, DECODE(MTT.TRANSACTION_ACTION_ID
, 27
, MTT.LOCATOR_ID
, 12
, MTT.LOCATOR_ID
, MTT.TRANSFER_TO_LOCATION) TO_LOCATOR_ID
, DECODE(MTT.TRANSACTION_ACTION_ID
, 27
, MTT.COST_GROUP_ID
, 12
, MTT.COST_GROUP_ID
, MTT.TRANSFER_COST_GROUP_ID) TO_COST_GROUP_ID
, MTT.PUT_AWAY_STRATEGY_ID PUTAWAY_STRATEGY_ID
, MTT.PUT_AWAY_RULE_ID PUTAWAY_RULE_ID
, MTT.ALLOCATED_LPN_ID LPN_ID
, MTT.SECONDARY_TRANSACTION_QUANTITY SECONDARY_QUANTITY
, '' GRADE_CODE
FROM WMS_MATERIAL_TXN_TRACE MTT
WHERE MTT.MOVE_ORDER_LINE_ID IS NOT NULL
AND ( MTT.TRANSACTION_TEMP_ID NOT IN ( SELECT MTLT.TRANSACTION_TEMP_ID
FROM WMS_TRANSACTION_LOTS_TRACE MTLT ) )
AND (MTT.TRANSACTION_TEMP_ID NOT IN (SELECT MSNT.TRANSACTION_TEMP_ID
FROM WMS_SERIAL_NUMBERS_TRACE MSNT ) ) UNION SELECT MTT.ROWID ROW_ID
, 'MTT' REC_TYPE
, 0 TYPE_CODE
, MTT.MOVE_ORDER_LINE_ID MOVE_ORDER_LINE_ID
, MTT.TRANSACTION_TEMP_ID
, MTT.ORGANIZATION_ID
, MTT.INVENTORY_ITEM_ID
, MTLT.PRIMARY_QUANTITY QUANTITY
, MTT.REVISION
, MTLT.LOT_NUMBER
, MTLT.LOT_EXPIRATION_DATE
, MTLT.LOT_ATTRIBUTE_CATEGORY
, MTT.SERIAL_NUMBER
, '' SERIAL_ATTRIBUTE_CATEGORY
, MTT.ORGANIZATION_ID FROM_ORGANIZATION_ID
, MTT.SUBINVENTORY_CODE FROM_SUBINVENTORY_CODE
, MTT.LOCATOR_ID FROM_LOCATOR_ID
, MTT.COST_GROUP_ID FROM_COST_GROUP_ID
, MTT.PICK_STRATEGY_ID
, MTT.PICK_RULE_ID
, DECODE(MTT.TRANSACTION_ACTION_ID
, 27
, MTT.ORGANIZATION_ID
, 12
, MTT.ORGANIZATION_ID
, MTT.TRANSFER_ORGANIZATION) TO_ORGANIZATION_ID
, DECODE(MTT.TRANSACTION_ACTION_ID
, 27
, MTT.SUBINVENTORY_CODE
, 12
, MTT.SUBINVENTORY_CODE
, MTT.TRANSFER_SUBINVENTORY ) TO_SUBINVENTORY_CODE
, DECODE(MTT.TRANSACTION_ACTION_ID
, 27
, MTT.LOCATOR_ID
, 12
, MTT.LOCATOR_ID
, MTT.TRANSFER_TO_LOCATION) TO_LOCATOR_ID
, DECODE(MTT.TRANSACTION_ACTION_ID
, 27
, MTT.COST_GROUP_ID
, 12
, MTT.COST_GROUP_ID
, MTT.TRANSFER_COST_GROUP_ID) TO_COST_GROUP_ID
, MTT.PUT_AWAY_STRATEGY_ID PUTAWAY_STRATEGY_ID
, MTT.PUT_AWAY_RULE_ID PUTAWAY_RULE_ID
, MTT.ALLOCATED_LPN_ID LPN_ID
, MTLT.SECONDARY_QUANTITY SECONDARY_QUANTITY
, MTLT.GRADE_CODE GRADE_CODE
FROM WMS_MATERIAL_TXN_TRACE MTT
, WMS_TRANSACTION_LOTS_TRACE MTLT
WHERE MTT.MOVE_ORDER_LINE_ID IS NOT NULL
AND MTT.TRANSACTION_TEMP_ID = MTLT.TRANSACTION_TEMP_ID
AND MTLT.SERIAL_TRANSACTION_TEMP_ID IS NULL UNION SELECT MTT.ROWID ROW_ID
, 'MTT' REC_TYPE
, 0 TYPE_CODE
, MTT.MOVE_ORDER_LINE_ID MOVE_ORDER_LINE_ID
, MTT.TRANSACTION_TEMP_ID
, MTT.ORGANIZATION_ID
, MTT.INVENTORY_ITEM_ID
, 1 QUANTITY
, MTT.REVISION
, MTT.LOT_NUMBER
, MTT.LOT_EXPIRATION_DATE
, '' LOT_ATTRIBUTE_CATEGORY
, MSNT.FM_SERIAL_NUMBER SERIAL_NUMBER
, MSNT.SERIAL_ATTRIBUTE_CATEGORY
, MTT.ORGANIZATION_ID FROM_ORGANIZATION_ID
, MTT.SUBINVENTORY_CODE FROM_SUBINVENTORY_CODE
, MTT.LOCATOR_ID FROM_LOCATOR_ID
, MTT.COST_GROUP_ID FROM_COST_GROUP_ID
, MTT.PICK_STRATEGY_ID
, MTT.PICK_RULE_ID
, DECODE(MTT.TRANSACTION_ACTION_ID
, 27
, MTT.ORGANIZATION_ID
, 12
, MTT.ORGANIZATION_ID
, MTT.TRANSFER_ORGANIZATION) TO_ORGANIZATION_ID
, DECODE(MTT.TRANSACTION_ACTION_ID
, 27
, MTT.SUBINVENTORY_CODE
, 12
, MTT.SUBINVENTORY_CODE
, MTT.TRANSFER_SUBINVENTORY ) TO_SUBINVENTORY_CODE
, DECODE(MTT.TRANSACTION_ACTION_ID
, 27
, MTT.LOCATOR_ID
, 12
, MTT.LOCATOR_ID
, MTT.TRANSFER_TO_LOCATION) TO_LOCATOR_ID
, DECODE(MTT.TRANSACTION_ACTION_ID
, 27
, MTT.COST_GROUP_ID
, 12
, MTT.COST_GROUP_ID
, MTT.TRANSFER_COST_GROUP_ID) TO_COST_GROUP_ID
, MTT.PUT_AWAY_STRATEGY_ID PUTAWAY_STRATEGY_ID
, MTT.PUT_AWAY_RULE_ID PUTAWAY_RULE_ID
, MTT.ALLOCATED_LPN_ID LPN_ID
, NULL SECONDARY_QUANTITY
, '' GRADE_CODE
FROM WMS_MATERIAL_TXN_TRACE MTT
, WMS_SERIAL_NUMBERS_TRACE MSNT
WHERE MTT.MOVE_ORDER_LINE_ID IS NOT NULL
AND MTT.TRANSACTION_TEMP_ID = MSNT.TRANSACTION_TEMP_ID UNION SELECT MTT.ROWID ROW_ID
, 'MTT' REC_TYPE
, 0 TYPE_CODE
, MTT.MOVE_ORDER_LINE_ID MOVE_ORDER_LINE_ID
, MTT.TRANSACTION_TEMP_ID
, MTT.ORGANIZATION_ID
, MTT.INVENTORY_ITEM_ID
, 1 QUANTITY
, MTT.REVISION
, MTLT.LOT_NUMBER
, MTLT.LOT_EXPIRATION_DATE
, MTLT.LOT_ATTRIBUTE_CATEGORY
, MSNT.FM_SERIAL_NUMBER SERIAL_NUMBER
, MSNT.SERIAL_ATTRIBUTE_CATEGORY
, MTT.ORGANIZATION_ID FROM_ORGANIZATION_ID
, MTT.SUBINVENTORY_CODE FROM_SUBINVENTORY_CODE
, MTT.LOCATOR_ID FROM_LOCATOR_ID
, MTT.COST_GROUP_ID FROM_COST_GROUP_ID
, MTT.PICK_STRATEGY_ID
, MTT.PICK_RULE_ID
, DECODE(MTT.TRANSACTION_ACTION_ID
, 27
, MTT.ORGANIZATION_ID
, 12
, MTT.ORGANIZATION_ID
, MTT.TRANSFER_ORGANIZATION) TO_ORGANIZATION_ID
, DECODE(MTT.TRANSACTION_ACTION_ID
, 27
, MTT.SUBINVENTORY_CODE
, 12
, MTT.SUBINVENTORY_CODE
, MTT.TRANSFER_SUBINVENTORY ) TO_SUBINVENTORY_CODE
, DECODE(MTT.TRANSACTION_ACTION_ID
, 27
, MTT.LOCATOR_ID
, 12
, MTT.LOCATOR_ID
, MTT.TRANSFER_TO_LOCATION) TO_LOCATOR_ID
, DECODE(MTT.TRANSACTION_ACTION_ID
, 27
, MTT.COST_GROUP_ID
, 12
, MTT.COST_GROUP_ID
, MTT.TRANSFER_COST_GROUP_ID) TO_COST_GROUP_ID
, MTT.PUT_AWAY_STRATEGY_ID PUTAWAY_STRATEGY_ID
, MTT.PUT_AWAY_RULE_ID PUTAWAY_RULE_ID
, MTT.ALLOCATED_LPN_ID LPN_ID
, NULL SECONDARY_QUANTITY
, '' GRADE_CODE
FROM WMS_MATERIAL_TXN_TRACE MTT
, WMS_TRANSACTION_LOTS_TRACE MTLT
, WMS_SERIAL_NUMBERS_TRACE MSNT
WHERE MTT.MOVE_ORDER_LINE_ID IS NOT NULL
AND MTT.TRANSACTION_TEMP_ID = MTLT.TRANSACTION_TEMP_ID
AND MTLT.SERIAL_TRANSACTION_TEMP_ID = MSNT.TRANSACTION_TEMP_ID

Columns