View: WMS_SUGGESTIONS_TEMP_V

Product:	WMS - Warehouse Management
Description:	WMS SUGGESTIONS TEMP View
Implementation/DBA Data:	APPS.WMS_SUGGESTIONS_TEMP_V

View Text

SELECT WTT.ROWID ROW_ID
, 'WTT' REC_TYPE
, WTT.TYPE_CODE
, WTT.TRANSACTION_TEMP_ID MOVE_ORDER_LINE_ID
, WTT.TRANSACTION_TEMP_ID
, MOL.ORGANIZATION_ID
, MOL.INVENTORY_ITEM_ID
, WTT.TRANSACTION_QUANTITY QUANTITY
, WTT.REVISION
, WTT.LOT_NUMBER
, WTT.LOT_EXPIRATION_DATE
, '' LOT_ATTRIBUTE_CATEGORY
, WTT.SERIAL_NUMBER
, '' SERIAL_ATTRIBUTE_CATEGORY
, WTT.FROM_ORGANIZATION_ID
, WTT.FROM_SUBINVENTORY_CODE
, WTT.FROM_LOCATOR_ID
, WTT.FROM_COST_GROUP_ID
, 0 PICK_STRATEGY_ID
, DECODE(WTT.TYPE_CODE
, 2
, WTT.RULE_ID
, NULL) PICK_RULE_ID
, WTT.TO_ORGANIZATION_ID
, WTT.TO_SUBINVENTORY_CODE
, WTT.TO_LOCATOR_ID
, WTT.TO_COST_GROUP_ID
, 0 PUTAWAY_STRATEGY_ID
, DECODE(WTT.TYPE_CODE
, 1
, WTT.RULE_ID
, NULL) PUTAWAY_RULE_ID
, WTT.LPN_ID LPN_ID
, WTT.SECONDARY_QUANTITY SECONDARY_QUANTITY
, MOL.SECONDARY_UOM_CODE SECONDARY_UOM
FROM WMS_TRANSACTIONS_TEMP WTT
, MTL_TXN_REQUEST_LINES MOL
WHERE WTT.TRANSACTION_TEMP_ID = MOL.LINE_ID
AND WTT.LINE_TYPE_CODE = 2
AND WTT.TYPE_CODE IN (1
, 2) 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
, MTT.TRANSACTION_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
, MTT.SECONDARY_UOM_CODE SECONDARY_UOM
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MTT
WHERE MTT.MOVE_ORDER_LINE_ID IS NOT NULL
AND ( ( MTT.TRANSACTION_TEMP_ID NOT IN ( SELECT MTLT.TRANSACTION_TEMP_ID
FROM MTL_TRANSACTION_LOTS_TEMP MTLT ))
AND (MTT.TRANSACTION_TEMP_ID NOT IN ( SELECT MSNT.TRANSACTION_TEMP_ID
FROM MTL_SERIAL_NUMBERS_TEMP 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.TRANSACTION_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
, MTT.SECONDARY_UOM_CODE SECONDARY_UOM
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MTT
, MTL_TRANSACTION_LOTS_TEMP 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
, MTT.TRANSACTION_QUANTITY 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
, MTT.SECONDARY_TRANSACTION_QUANTITY SECONDARY_QUANTITY
, MTT.SECONDARY_UOM_CODE SECONDARY_UOM
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MTT
, MTL_SERIAL_NUMBERS_TEMP 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
, MTT.TRANSACTION_QUANTITY 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
, MTT.SECONDARY_TRANSACTION_QUANTITY SECONDARY_QUANTITY
, MTT.SECONDARY_UOM_CODE SECONDARY_UOM
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MTT
, MTL_TRANSACTION_LOTS_TEMP MTLT
, MTL_SERIAL_NUMBERS_TEMP 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