MTL Material Transactions Interface

Solution : 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 quanity is lesser than the given quantity

Step 1 : Staging Table will be created in External system and the                                                     transactions 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.

a.         To Validate if the item exists in MTL_SYSTEM_ITEMS_B

b.        To Validate subinventory code in MTL_SECONDARY_INVENTORIES

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

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