Image
oracle

Oracle Fusion AR Integration with Third Party

Swati Kane
May 4, 2020
Different approaches –
  1. PaaS as integration platform –

    Oracle PaaS service is one of the most flexible platform to integrate data / transactions from any third party system to Oracle . We integrated AR invoices from third party system to Oracle using oracle PaaS service for one of our Clients.

    Challenges - Here the challenge was that the transactional data was available only in .CSV format and placing the file on the PaaS environment was next to impossible .

    Solution - The solution suggested / accepted and implemented was –

    Any Invoice file coming from third party will be pushed to Oracle fusion.

    Below is the sequence of steps that will be followed for doing the same.

    • Third party system will call UCM upload webservice and will place invoice file/s ( csv ) on UCM. Seeded oracle webservice will be called for the same .
    • PaaS will call UCM download webservice and download the file on PaaS.
    • DBMS JOB scheduler on PaaS will pick eligible records and insert it into staging table.
    • After eligible records are inserted in PaaS staging table, validation program will be called to validate all fields.
    • All validated records will be inserted in AR interface . The webservice xyz.oraclecloud.com/fscmService/RecInvoiceService?WSDL will be used for the same.
    • The ESS job to import invoices will be called from PaaS to create AR invoices.
    • The custom report will be run from PaaS and successful and error out transaction will be updated on PaaS.
    • PaaS will call UCM upload webservice and place the Invoice detail output file on UCM.
    • Email notification will be sent to respective teams as per the requirement.
      syntax_image
  2. Boomi as Integration platform –

    Using Boomi as integration platform is one of the most efficient and popular way to integrate data with two different systems. At one of our clients , client wanted to push AR invoices from almost 9 different platforms.

    Challenge – All platforms has data present in different format in terms of data fields , file format , frequency of data availability and many others .

    Designing the different processes in Boomi to fetch the data from different platforms was possible. However getting that data uniformly in oracle with proper grouping was a challenge.

    Solution – Following is the design proposed and implemented -

    • Fetch Invoice File from AWS S3 Bucket / using platform APIs and upload data to Staging DB. Staging DB can be any cloud DB.
      syntax_image
    • Using oracle fusion webservice move data from Staging DB to Oracle AR interface.
    • Call oracle webservice to submit the ESS job to import AR invoices.
      syntax_image
    • Once invoices are created /processed , staging DB is updated with the invoice number / error status.

Technicality behind the AR import –

  • To insert data into AR interface tables ,oracle fusion provides the webservice.
  • Ra_interface_lines_all stores transaction header and line information.
  • AutoInvoice uses Transaction Flexfields to uniquely identify each transaction that you import into Receivables.
  • INTERFACE_LINE_CONTEXT is a required column in AutoInvoice. Combination of interface_line_attribute1-15 ( based on DFF setup ) must be unique.
  • There are attributes available in the interface tables to populate transaction line DFF and header level DFF values.
  • Inserting data into ra_interface_distributions is optional. If you do not use AutoAccounting, you must enter accounting distributions for your transactions. INTERFACE_LINE_CONTEXT and combination of interface_line_attribute1 -15 will determine the distribution belongs to which interface line.
  • Import Invoices –Typically in the integration this ESS job is called using webservice to create invoices . We need to pass the required parameters and job will create the invoices .Autoinvoice program groups the invoice lines into a single invoice based on Business unit + Legal entity + Source + Transaction type + Currency + Transaction Date by default. We can add Grouping rules in case additional grouping logic is to be enforced based on which lines will be grouped in one invoice.
  • BI reporting and integration – Every integration platform will need data from oracle for validation or updating. The challenge here is again how to get it ? This is typically achieved by designing BI reports with the help of oracle BI Analytics and calling them from integration platform.

References –

About the Author

Swati Kane, Solution Architect – ERP

Swati Kane is a Solution Architect at Jade Global and has been with the organization for the past 10 years. She has 15+ years of experience as techno functional consultant in EBS & Fusion Implementation, Upgrade, Enhancement & Support . She has been instrumental in providing the custom solution to meet the client business need and has vast experience in integrating third party systems with Oracle ERP ( EBS & Oracle fusion).

Add New Comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.