INV_SUGGESTIONS_TEMP

Details

Schema: FUSION
Object owner: INV
Object type: VIEW

Columns

Name
ROW_ID REC_TYPE TYPE_CODE MOVE_ORDER_LINE_ID TRANSACTION_TEMP_ID ORGANIZATION_ID INVENTORY_ITEM_ID QUANTITY REVISION LOT_NUMBER LOT_EXPIRATION_DATE LOT_ATTRIBUTE_CATEGORY SERIAL_NUMBER SERIAL_ATTRIBUTE_CATEGORY FROM_ORGANIZATION_ID FROM_SUBINVENTORY_CODE FROM_LOCATOR_ID FROM_COST_GROUP_ID PICK_STRATEGY_ID PICK_RULE_ID TO_ORGANIZATION_ID TO_SUBINVENTORY_CODE TO_LOCATOR_ID TO_COST_GROUP_ID PUTAWAY_STRATEGY_ID PUTAWAY_RULE_ID LPN_ID SECONDARY_QUANTITY SECONDARY_UOM

Name

ROW_ID

REC_TYPE

TYPE_CODE

MOVE_ORDER_LINE_ID

TRANSACTION_TEMP_ID

ORGANIZATION_ID

INVENTORY_ITEM_ID

QUANTITY

REVISION

LOT_NUMBER

LOT_EXPIRATION_DATE

LOT_ATTRIBUTE_CATEGORY

SERIAL_NUMBER

SERIAL_ATTRIBUTE_CATEGORY

FROM_ORGANIZATION_ID

FROM_SUBINVENTORY_CODE

FROM_LOCATOR_ID

FROM_COST_GROUP_ID

PICK_STRATEGY_ID

PICK_RULE_ID

TO_ORGANIZATION_ID

TO_SUBINVENTORY_CODE

TO_LOCATOR_ID

TO_COST_GROUP_ID

PUTAWAY_STRATEGY_ID

PUTAWAY_RULE_ID

LPN_ID

SECONDARY_QUANTITY

SECONDARY_UOM

Query

SQL_Statement
SELECT wtt.rowid row_id, 'WTT' AS 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 INV_TRANSACTIONS_GT wtt, INV_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' AS 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 , NULL 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 , NULL 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 INV_MATERIAL_TXNS_TEMP mtt where mtt.move_order_line_id is not null and ( ( mtt.transaction_temp_id not in ( select mtlt.transaction_temp_id from inv_transaction_lots_temp mtlt )) and (mtt.transaction_temp_id not in ( select msnt.transaction_temp_id from inv_serial_numbers_temp msnt )) ) union SELECT mtt.rowid row_id, 'MTT' AS 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 , NULL 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 , NULL 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 INV_MATERIAL_TXNS_TEMP mtt , inv_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' AS 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 , NULL 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 , NULL 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 INV_MATERIAL_TXNS_TEMP mtt , inv_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' AS 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 , NULL 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 , NULL 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 INV_MATERIAL_TXNS_TEMP mtt ,inv_transaction_lots_temp mtlt ,inv_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

SQL_Statement

SELECT wtt.rowid row_id, 'WTT' AS 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 INV_TRANSACTIONS_GT wtt, INV_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' AS 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 , NULL 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 , NULL 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 INV_MATERIAL_TXNS_TEMP mtt where mtt.move_order_line_id is not null and ( ( mtt.transaction_temp_id not in ( select mtlt.transaction_temp_id from inv_transaction_lots_temp mtlt )) and (mtt.transaction_temp_id not in ( select msnt.transaction_temp_id from inv_serial_numbers_temp msnt )) ) union SELECT mtt.rowid row_id, 'MTT' AS 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 , NULL 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 , NULL 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 INV_MATERIAL_TXNS_TEMP mtt , inv_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' AS 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 , NULL 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 , NULL 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 INV_MATERIAL_TXNS_TEMP mtt , inv_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' AS 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 , NULL 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 , NULL 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 INV_MATERIAL_TXNS_TEMP mtt ,inv_transaction_lots_temp mtlt ,inv_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