Description:
This blog explains the technical approach for creating an automated interface to accept user-supplied material transactions from External Systems and to import these transactions to Oracle as Material Transactions. Email will be sent to business if the onhand quantity is lesser than the given quantity
Step 1: Staging Table will be created in the External system, and the transaction data will be populated to SCRAP Table.
CREATE TABLE scrap
(
scrap_id NUMBER PRIMARY KEY,
transaction_date DATE,
item_number VARCHAR2(255 BYTE),
quantity NUMBER,
reason VARCHAR2(255 BYTE),
subinventory VARCHAR2(10 BYTE),
account_alias VARCHAR2(150 BYTE),
status VARCHAR2(1 BYTE),
error_message VARCHAR2(255 BYTE)
);
Step 2: Data will be extracted from SCRAP table, and the below validations will be performed before inserting to MTL_TRANSACTIONS_INTERFACE table
Step3: The below validations are done if the disposition_id exists from the table
MTL_GENERIC_DISPOSITIONS for the account alias column in staging table.
- To Validate if the item exists in MTL_SYSTEM_ITEMS_B
- To Validate subinventory code in MTL_SECONDARY_INVENTORIES
- To Validate if the transaction_quantity from the MTL_ONHAND_QUANTITIES
table is lesser than the scrap quantity from SCRAP. If so, email will be sent to the user with the details using SMTP Server.
- To Mail addresses will be maintained in LOOKUPS.
Step 4: FND_REQUEST.SUBMIT_REQUEST should be used to call the Process transaction interface Program from PL/SQL Procedure. This will call another Oracle seeded program Inventory transaction worker which will create material transactions in Inventory.
Summary:
The problem occurs with imported transactions only, eg, it does not happen with similar transactions entered manually through the application form.
Queries?
Do drop a note by writing us at contact@doyensys.com or use the comment section below to ask your questions.
