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 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.

 

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;