Oracle Fusion AR Integration with Third Party
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.
o 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 .
https://xyz.oraclecloud.com/fscmService/ErpIntegrationService?WSDL
o PaaS will call UCM download webservice and download the file on PaaS.
https://xyz.oraclecloud.com/idcws/GenericSoapPort?WSDL
o DBMS JOB scheduler on PaaS will pick eligible records and insert it into staging table.
o After eligible records are inserted in PaaS staging table, validation program will be called to validate all fields.
o All validated records will be inserted in AR interface . The webservice xyz.oraclecloud.com/fscmService/RecInvoiceService?WSDL will be used for the same.
o The ESS job to import invoices will be called from PaaS to create AR invoices.
o The custom report will be run from PaaS and successful and error out transaction will be updated on PaaS.
o PaaS will call UCM upload webservice and place the Invoice detail output file on UCM.
o Email notification will be sent to respective teams as per the requirement.
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 -
o Fetch Invoice File from AWS S3 Bucket / using platform APIs and upload data to Staging DB. Staging DB can be any cloud DB.
o Using oracle fusion webservice move data from Staging DB to Oracle AR interface.
o Call oracle webservice to submit the ESS job to import AR invoices.
o 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 –
· Oracle Receivables Reference Guide
· https://docs.oracle.com/cloud/farel9/financialscs_gs/OEDMF
Original blog source: https://www.jadeglobal.com/blog/oracle-fusion-ar-integration-third-party
