Monday 22 December 2014

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.

6 comments:

  1. Thanks for sharing very good information about OCI Punchout.
    OCI Punchout

    ReplyDelete
  2. Thanks for sharing article about What is OCI Punchout
    What is OCI Punchout

    ReplyDelete
  3. Thank you for sharing such a nice and interesting blog with us. I have seen that all will say the same thing repeatedly. But in your blog, I had a chance to get some useful and unique information.

    Oracle Fusion HCM Online Training
    Oracle Fusion HCM Training

    ReplyDelete
  4. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.

    Big Data and Hadoop Online Training
    Big Data Hadoop Training
    Hyderabad

    ReplyDelete
  5. This blog contain every possible information about online shoping apps. do check your here Free Reaction Time Test

    ReplyDelete