Inventory On-hand quantity Interface

USING this interface we can UPDATE ON hand quantity OF Inventory Item.
Interface Tables:
MTL_TRANSACTIONS_INTERFACE
MTL_MTL_TRANSACTION_LOTS_INTERFACE (IF the item IS Lot controlled)
MTL_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 recordsWITH 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.

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 dedicated 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 Sample data FOR Mtl_Transactions_interface table.
 
 
 DECLARE
  l_source_code      VARCHAR2 (100) := 'MIGRATION';
  l_lock_flag        NUMBER         := 2;
  l_source_line_id   NUMBER         := 99;
  l_source_header_id NUMBER         := 99;
  l_process_flag     NUMBER         := 1;
  l_user_id          NUMBER         := 6871;
  --fnd_global.user_id;
  l_resp_id NUMBER := 53922;
  --fnd_global.resp_id;
  l_appl_id NUMBER := 7000;
  --fnd_global.resp_appl_id;
  l_org_id NUMBER := 3;
  --fnd_global.org_id;
  l_login_id NUMBER := 6871;
  --fnd_global.login_id;
  l_transaction_mode         NUMBER := 3;
  l_transaction_interface_id NUMBER;
BEGIN
  DBMS_OUTPUT.put_line ('Before inserting!');
  fnd_client_info.set_org_context ('1');
  --initializing org_id --fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id, l_login_id);
  fnd_global.apps_initialize (6871, 53922, 7000, 6871);
  SELECT mtl_material_transactions_s.NEXTVAL
  INTO l_transaction_interface_id
  FROM DUAL;
  INSERT
INTO mtl_transactions_interface
    (
      transaction_interface_id,
      source_code,
      source_line_id,
      source_header_id ,
      process_flag,
      validation_required,
      transaction_mode ,
      last_update_date,
      creation_date,
      created_by ,
      last_update_login,
      inventory_item_id,
      organization_id
      --
      ,
      transaction_quantity,
      transaction_uom,
      transaction_date ,
      subinventory_code,
      transaction_source_type_id ,
      transaction_action_id,
      transaction_type_id ,
      transaction_cost,
      distribution_account_id ,
      last_updated_by,
      lock_flag
    )
    VALUES
    (
      l_transaction_interface_id,
      l_source_code,
      l_source_line_id,
      l_source_header_id ,
      l_process_flag,
      1,
      l_transaction_mode ,
      SYSDATE,
      SYSDATE,
      l_user_id ,
      l_login_id,
      161073,
      l_org_id ,
      30,
      'NOS',
      SYSDATE,
      'FGS_OE',
      13,
      27,
      42,
      100,
      1001,
      l_user_id,
      l_lock_flag
    );
  COMMIT;
  DBMS_OUTPUT.put_line (Successfully Inserted data INTO Interface table!'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('ERROR WHILE INSERTING DATA INTO INTERFACE TABLE =' || SQLERRM); END;