Inventory On-hand quantity Interface

Inventory On-hand quantity Interface

This interface lets you import the on hand inventory into Oracle.

Interface tables:

• MTL_TRANSACTIONS_INTERFACE
• MTL_MTL_TRANSACTION_LOTS_INTERFACE (If the item is Lot Controlled)
• MTLL_SERIAL_NUMBERS_INTERFACE (If the item is Serial Controlled)

Concurrent Program:

• Launch the Transaction Manager through Interface Manager or explicitly call the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS () to launch a dedicated transaction worker to process them.
• The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, and PROCESS_FLAG.
• Only records with TRANSACTION_MODE of 3, LOCK_FLAG of '2', and PROCESS_FLAG of '1' will be picked up by the Transaction Manager and assigned to a Transaction Worker. If a record fails to process completely, then PROCESS_FLAG will be set to '3' and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.

Base Tables:

• MTL_ON_HAND_QUANTITIES
• MTL_LOT_NUMBERS
• MTL_SERIAL_NUMBERS

Validations:

• Validate organization_id
• Check if item is assigned to organization
• Validate disposition_id
• Check if the item for the org is lot controlled before inserting into the Lots interface table.
• Check if the item for the org is serial controlled before inserting into Serial interface table.
• Check if inventory already exists for that item in that org and for a lot.
• Validate organization_id, organization_code.
• Validate inventory item id.
• Transaction period must be open.

Important Columns:

Some important columns that need to be populated in the interface tables:

MTL_TRANSACTIONS_INTERFACE:

• TRANSACTION_SOURCE_NAME (ANY USER DEFINED VALUE),
• TRANSACTION_HEADER_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL)
• TRANSACTION_INTERFACE_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL – If item is lot or serial controlled, use this field to link to mtl_transactions_interface otherwise leave it as NULL),
• TRANSACTION_DATE,
• TRANSACTION_TYPE_ID,
• PROCESS_FLAG (1 = Yet to be processed, 2 = Processed, 3= Error)
• TRANSACTION_MODE (2 = Concurrent – to launch a ded transaction worker to explicitly process a set of transactions, 3 = Background – will be picked up by transaction manager polling process and assigned to transaction worker. These will not be picked up until the transaction manager is running)
• SOURCE_CODE,
• SOURCE_HEADER_ID,
• SOURCE_LINE_ID (Details about the source like Order Entry etc for tracking purposes)
• TRANSACTION_SOURCE_ID

Source Type Foreign Key Reference

• Account GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
• Account Alias MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID
• Job or schedule WIP_ENTITIES.WIP_ENTITY_ID
• Sales Order MTL_SALES_ORDERS.SALES_ORDER_ID
• ITEM_SEGMENT1 TO 20,
• TRANSACTION_QTY,
• TRANSACTION_UOM,
• SUBINVENTORY_CODE,
• ORGANIZATION_ID,
• LOC_SEGMENT1 TO 20

MTL_TRANSACTION_LOTS_INTERFACE:

• TRANSACTION_INTERFACE_ID,
• LOT_NUMBER,
• LOT_EXPIRATION_DATE,
• TRANSACTION_QUANTITY,
• SERIAL_TRANSACTION_TEMP_ID (This is required for items under both lot and serial control to identify child records in mtl_serial_numbers_interface)

MTL_SERIAL_NUMBERS_INTERFACE:

• TRANSACTION_INTERFACE_ID,
• FM_SERIAL_NUMBER,
• TO_SERIAL_NUMBER,
• VENDOR_SERIAL_NUMBER