Inventory On-hand quantity Interface

Inventory On-hand quantity Interface, AskHareesh.blogspot.com

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


*/