Sridhar Doki

 

Oracle Cloud ERP Release 12 has provided multiple ways to integrate and exchange data from/to external systems.  More details about each of the available options can be found in the Oracle Enterprise Repository: http://www.oracle.com/webfolder/technetwork/docs/HTML/oer-redirect.html

  1. File Based Data Integrator - FBDI Approach
  2. SOAP & REST Web Services
  3. ADFdi – Similar to Web ADI
  4. BI Publisher

Centroid has implemented many Cloud ERP integrations relevant to a particular business use case for many clients.

With this post, we will share a few recent examples.  We recently implemented an integration of GL journal data from an external source to Oracle Cloud ERP using our in-house built tool we call “Centroid Integrator.”  The implementation of this integration took less than one week to complete the full cycle i.e. design, build, test and deploy and one week for stabilization.

We would like to share the approach for performing the integration step by step.

Business Requirement

Our customer has multiple business systems that they maintain for a variety of business activities each of which generates large volumes of financial transactions on a daily basis.  Our customer required all of these externally generated transactions to be automatically integrated into Oracle Cloud ERP to post to the General Ledger providing a single consolidated view.

And for this purpose, our customer wanted an interface designed to integrate the daily GL journal transactions from the external systems to Oracle Cloud ERP via data files.  And these transactions should be processed every day scheduled at specific times.  Below is the delimited data format provided by the customer:

Field Name

Journal Source

Journal Category

Currency Code

Segment1

Segment2

Segment3

Segment4

Segment5

Segment6

Segment7

Debit Amount

Credit Amount

Converted Debit Amount

Converted Credit Amount

Reference1

Reference2

Reference3

Reference4

Reference5

Transaction Date

Journal Entry Creation Date

Clearing Company

Ledger Name

Additional Information

Journal Line Description

In addition to the above, our customer also wanted to have the following functionality included in the design of the integration.

  1. Data validation and error processing logic
  2. Notifications for all errors with transaction details
  3. Notifications should be sent to specific users and distribution list of users
  4. Archival of processed data files

Our Approach

The first and foremost step for any integration in Oracle Cloud ERP is to determine whether Oracle has provided any pre-built options that can be leveraged in support of your requirements.  We follow a simple process to help make an approach decision.

 

After our initial analysis, we found that Oracle Cloud ERP provides both FBDI and SOAP web service for the purpose of uploading GL Journal data.

As the inbound data is sent as a delimited file and data can also vary, we evaluated both options to determine the best fit.

  1. FBDI: https://docs.oracle.com/cloud/farel12/financialscs_gs/OEFBF/Journal_Import_301648204_fbdi_18.htm#301648204
  2. SOAP web service for journal integration: https://docs.oracle.com/cloud/farel12/financialscs_gs/OESWF/Journal_JournalImportService_svc_21.htm#oracle.apps.financials.generalLedger.journals.desktopEntry.journalImportService.JournalImportService   

And based on the type of interface and customer requirements, we chose the FBDI template approach as it supports bulk data loads.

As per the flowchart above, the format and contents of the source data files provided by the customer do not match directly with the FBDI template required by Oracle Cloud ERP.  For this purpose, we have to transform the data file to the corresponding FBDI template format.  During the design and development of this interface, we faced several challenges and some of them are highlighted below.

  1. The GL Journals data file sent by external systems is in a custom format i.e. it does not match the FBDI template format. So, we need to create separate logic to transform the content from the source data file into the FBDI template. With Centroid Integrator, this can be done very quickly replacing extensive manual data formatting.
  2. Before processing the GL Journal data file to Oracle Cloud ERP, we need to validate the data for accuracy and ensure it conforms with Oracle Cloud ERP setups.  This is a major requirement as we need a staging location where validations can be performed and data transformed.  Centroid Integrator provides features that not only make this possible, but make it quick and easy.
  3. Once the GL Journal data is sent to Oracle Cloud ERP after submission of the standard Oracle FBDI Interface program, we need to wait until the main Import Journals program is complete. The reason for this is, when the FBDI interface is submitted, the process will return the request id of the file loader program instead of the actual import program.  And we need to send the outcome of the processing details of the Import Journals program whether success or error as final output for the email notification to the user distribution list.  Linking of all of these sequential steps and determining the outcome of each transaction within a source file can be tricky.  We have made this simple by building this capability into Centroid Integrator for you.  Just a few clicks to make it happen within minutes.
  4. Lastly, we need to find the import status and any error codes for each journal transaction processed, generate the error report, and automatically send to the notification distribution list. This is a very useful set of features that we have built and included with Centroid Integrator all using familiar technologies, SQL and BI Publisher Reports.

Below is our solution approach:

  1. Receive the delimited data file at a particular location from the source systems. For this purpose, customer connects to the Centroid Integrator site and transmits the files using FTP. For each file processed a unique reference key is created. And this key will be shared with subsequent steps to process this file creating an audit trail for each and every data file.
  2. Load the data into Oracle table for validation and processing purpose.  Centroid Integrator provides an option with SQL Loader where it can load any delimiter or fixed width data to staging tables. And at the time of loading, the records are marked with initialized flag.
  3. Perform initial validations:
    1. Data accuracy
    2. Validation of segments
    3. Credit and Debit amounts

With Centroid Integrator, we can create our own PLSQL custom program units to validate the data per your specific requirements and mark the records with a status of Success or Failure.

  1. If there are validation failures, then the whole file will not be processed and the data will be updated as error with error message.  Centroid Integrator provides an option to put your SQL queries directly into the GUI and allow you to create the data file as required.
  2. If all the basic validations are successful, then the logic will transform the data and generate a delimited data file with all columns that are required for the FBDI.  With Centroid Integrator, you can configure your file name and file delimiter format.
  3. The data file is then compressed for space savings.  Centroid Integrator has an option to compress set of files based on the previous steps. This is just a configuration step.
  4. A SOAP web service is used to load the GL Journal data using the compressed payload. This ERP Integration Service web service is utilized and performs the following functions: https://docs.oracle.com/cloud/farel11/financialscs_gs/OESWF/ERP_Integration_Service_ErpIntegrationService_svc_8.htm#loadAndImportData
    1. Uploads the data into Oracle Cloud UCM under a particular folder
    2. Unzips and loads the data file into the corresponding interface table
    3. And then finally if everything goes well at this point, it will initiate the Journals Import program
    4. This web service returns the process id of the Load Interface file program and not the Journals Import program

This step is the heart and soul of the Centroid Integrator. This step will use the WebService required to interface the compressed file to Cloud ERP and in turn receives the response. This is all automated using Centroid Integrator simplifying the work required by the user.

  1. To get the Journals Import program process id, we created a new BI Publisher report to pass the parameter of the Load Interface File program process id and it will return the Journal import program process id and this will get executed by using the BI Publisher web service program.

Note: Oracle Cloud ERP has BI publisher webservices available to execute the reports and to download the report data. For this requirement, there is just an option to check in the Centroid Integrator for the above steps. Once enabled, Centroid Integrator will take care of the process for you.

  1. The process will wait for the Journals Import program to complete by invoking wait for job completion web service. This is one more option provided by Centroid Integrator where you can wait for completion of the program.
  2. A new BI Publisher report is created to get the error records from the GL_INTERFACE_ERRORS table for each batch.
  3. Once the Journal Import program is completed, the logic will run the check to find if any error records exists in the interface table by using BI Publisher web services.
  4. If any error records exist at this point in the process, they are merged with error records that occurred during initial validation and finally an excel sheet output will be created. If there are no errors, then the process proceeds automatically to the next step.
  5. Download the log files of Load Interface File and Journals Import and error files (if any) and automatically send to the distribution list based on the source of the file. This is a configurable option in Centroid Integrator.
  6. Finally, archive the source data file for audit purposes. This is another configurable option provided in Centroid Integrator.
  7. A scheduled job is set to run every day to process the incoming files automatically.  Centroid Integrator has a REST API exposed and this can be executed from the shell script.  So, for GL Journals integration, the process name will be called in the REST API to interface GL Journals periodically.
  8. When the final email is sent to the notification distribution list, Centroid Integrator has an option to send the Data File along with the other details.

About Centroid Integrator

Centroid Integrator is a cloud-based software-as-a-service integration platform developed by Centroid specifically designed for Oracle Cloud ERP applications and is a proven tool for converting key pillar and historical data and integrating key transactional data with Oracle cloud applications like ERP, SCM, Financials, PPM, HCM and CRM.

Like the example above, these integrations can be completed and production ready in less than a week using Centroid Integrator. It uses a combination of familiar technologies such as, SQL Loader, SQL, PLSQL, host scripts, Java based Web services SOAP/REST, and emailing features to accommodate the integrations.  Business users just need to upload the file to a designated location and the rest of the functionality will be taken care by the integrator. A very simple and easy to use tool.

Implementation Experience

  1. This integration required the following:
    • To store and archive the inbound data file on the file system
    • To load data into Oracle – Need sql loader facility or need to create custom code
    • Data in Oracle – Staging table in Oracle
    • Validation in Oracle – Need PLSQL features to validate the data file
    • Retrieve data from Oracle and create data file – Need SQL Plus features
    • Logic for compression – OS utilities
    • Feature to call web services with different end points of Oracle Cloud ERP – Oracle Cloud ERP has multiple end points for each stream. For example, for calling a web service to load data files to UCM and processing, need to have fin end point, whereas to call BI Reports, need bi end point, etc.
    • Emailing the status to distribution list – Emailing feature
    • Finally need to get it scheduled – cronjob feature

All the above features are default features and capabilities of the Centroid Integrator and are reusable components for all of your integration needs.

  1. As there are multiple steps involved in this integration, there are multiple potential failure points in the process as well.  There are error checks and validation points at each step in the process and the orchestration is made possible by Centroid Integrator.
  2. The setup data exists in Oracle Cloud ERP, and for validation we need to bring this information from Oracle Cloud ERP to the database. This is a common requirement that we have provided for with inbuilt capabilities of Centroid Integrator.
  3. FBDI expects a comma delimiter, which can sometimes exist in customer data leading to undesired results.  To accommodate this, we automatically enclose all character fields in double quotes to properly distinguish delimiters from data.
  4. Sometimes, the segment fields were null from the source causing integration failures.  We captured these issues in our custom validations and sent the report for further action.
  5. Submitting the standard jobs require parameters like Source, Legal Entity and Ledger information, which we added configurable capabilities into Centroid Integrator for this purpose.
  6. Thorough unit testing was critical for this integration as it contains multiple steps and integration with web services.  For some of the steps, we need to include sleep seconds and also need to iterate multiple times to find the status of the process id of submitted jobs.

Customer Experience

  • The integration for GL Journals worked as specified and met the business requirements.
  • Error reporting feature is the backbone of this integration. This gives the 360* view of the integration without logging into the system.
  • A dashboard is provided to display the number of files processed by week for each integration.
  • Showcases the flexibility of Oracle Cloud ERP.

 

Still have questions? Contact Centroid to learn more about Oracle Cloud ERP Integrations and how Centroid can help.