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.
  • December 22, 2014 | 20 views