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;
