Automating Accounting Hub Cloud Integration with Oracle Integration

Background (What is Accounting Hub Cloud?)

Oracle Fusion Cloud Accounting Hub is a robust accounting engine that integrates and aligns information from virtually any source system to consistently enforce accounting policies and meet multiple reporting requirements, giving you the agility, control, and insight needed to run your business.

As an integrated accounting platform, Accounting Hub standardizes the accounting from multiple third party transactional systems to consistently enforce accounting policies and meet multiple reporting requirements in an automated and controlled fashion, and with minimal disruption to existing financial processes. The core capability of Accounting Hub is the robust accounting engine, delivering flexible configuration that captures rich elements from your existing business systems

Accounting Hub improves the agility of finance organizations with a simple process for integrating source systems into one enterprise-wide accounting platform and recording financial transactions from the subledgers to enhance reporting.

Why we need to Automate the Process of importing Transactions?

Customers with high volumes of transactions from diverse industry applications such as internet services and marketplaces, billing systems for telecommunications, core banking platforms, logistics, or claims systems for insurance, Accounting Hub can receive transaction information from these external source systems and apply rules to create detailed accounting entries to meet virtually any accounting requirement. They need to integrate new industry-specific systems or recently acquired companies into their existing environment and automate the whole process of importing accounting transactions seamlessly into Accounting Hub.

Usecase

A Financial Services company Vision Corp (a fictitous company) has multiple transaction systems (for Loans, Insurances, Billing etc.,) acquired or home-grown over a period of several years. Each of these systems have limited or no accounting & reporting capabilities. Fiscal reporting involves manual consolidation of accounting which is spread across disparate transaction systems. Vision Corp would like to centralize the process and bring down the consolidation time & efforts with touchless automated accounting & reporting processes.

Solution

Oracle Inegration capabilities are used to automate the process Subledger posting of Loan Transactions into Accounting Hub Cloud. Out of the box ERP Cloud adapter capabilities helps to integrate seamlesslly with UCM Services and ESS jobs to further simplify the import process. The solution provides automated processing to bring the transaction data files from the registered source systems into the ERP Cloud. This includes data from other external sources, such as other custom applications, transaction systems, or legacy ERP. Optionally, data transformation processing may be required for data sourced from other external files which doesn't match the template. 

Pre-Requisite Configuration

Roles required for Integration User

Add the below roles to the Integration User in ERP Cloud

1. Create a new job role under role category "Common - Job Roles"

2. Under role hierarchy, add the following duties:

ORA_XLA_ACCOUNTING_HUB_INTEGRATION_DUTY

ORA_XLA_ACCOUNTING_HUB_INTEGRATION_DUTY_OBI

FIN_FUSIONACCOUNTINGHUB_IMPORT_RWD

3. Save this job role.

4. Assign this job role to a user.

5. Run the following jobs:

• Send Pending LDAP Requests
• Retrieve Latest LDAP Changes
• Import User and Role Application Security Data

Set up Accounting Hub Cloud

Setting up Accounting Hub Cloud involves three primary steps as indicated below:

1. Create a new subledger application by registering the transaction source system using a spreadsheet
2. Configuring Accounting Rules
3. Upload Transaction Data to create Accounting Entries

However, this blog covers the Step 3 above to automate the import of Transaction Entries using Oracle Integration.

High Level Design of the Integration Flow

Flow 1: In this Integration Flow we will retreive the file from an FTP server and subsequently upload the File to UCM and then Invoke the ESS Job. As, the Import Accounting Transactions ESS job would take a while to process the transactions file it is always a good idea to submit the job in an asynchronous (fire and forget) fashion. To track the status of the job we will use something like a parking lot table to store our job requests.

Flow 2: This Integration Flow is a DB Poller which will be implemented as a Scheduled Integration. We will fetch the list of running jobs and iteratively check the job status. If the job status is "SUCCEEDED" or "ERROR" we will invoke download ess job details operation and fetch the log file to proactively send a notification with attachment. The response of the operation is a .zip file which contains source transacitons files along with the log file. Remember that email notification attachment limit is 2MB so, we will make sure to send just the .log file in the notification by stripping off the original transactions files.

Design

I'll highlight a few important parts of the integration as appropriate to design the complete usecase.

Flow1:

• Create  a Schedule Based Integration flow

• Configure ERP Cloud Adapter option to "Send Files to ERP Cloud"
• Provide Security Group(FAFusionImportExport) and Doc Account(fin$/fusionAccountingHub$/import$)
• File Reference
• Map Upload File to UCM Activity -> Map the File Reference which is a response from FTP Download operation

• Use ERP Cloud Adapter to Invoke ERPIntegrationService->submitESSJob Operation

• Map SubmitESSJobRequest Activity: Map the below parameters as appropriate to your Import Job. 
• Jobpackagename: /oracle/apps/ess/financials/subledgerAccounting/shared
• job definition name: XLATXNIMPORT
• param list: <docid>,<filename>,#NULL,#NULL,Y,D,S,N,N,N

The last one – paramList – corresponds to parameters of the Import Accounting Transactions process and depends on whether the Flexible Configuration of the Accounting Flow feature is enabled or not. If the feature is not enabled you can provide values for only 1parameter. See the table below

Number	Meaning	Value
1	docid	Use docId returned from the Send Files to ERP Cloud
2	filename	Name of the data file
3	Always use "null"
4	Import Set	Import Set Optionally group multiple data files in a single import from interface
5	Import from Interface	Y or N
6	Create Accounting	D for Draft; F for Final; N for No
7	Report Style	D for Detail; N for No report; S for Summary
8	Transfer to General Ledger	Y or N
9	Post in General Ledger	Y or N
10	Update Subledger Balances	Y or N

                

• Create a table in ATP with below fields. Insert into Jobs_Status Table (This table acts like a parking lot for pushing job status records with request id)
• RequestID - Request Id from SubmitESS Job
• ERPStatus - RUNNING (Possible Status's are RUNNING/SUCCEEDED/ERROR/WAIT/READY)
• Processed - False (In the DB Poller integration

Flow2:

• Query for all jobs in Jobs_status table where processed=FALSE and ERPStatus=Running
• Invoke ERPIntegrationService -> getESSExecutionDetails passing requestId as input. The Response result element will contain a json as below

            {"JOBS":{"JOBNAME":"Import Accounting Transactions","JOBPATH":"/oracle/apps/ess/financials/subledgerAccounting/shared","REQUESTID":"5569846","STATUS":"SUCCEEDED"}}

        Alternatively, you can invoke getESSJobStatus as well which gives the jobstatus alone.

• Invoke ERPIntegrationService -> downloadESSJobExecutionDetails. We need to use SOAP adapter instead of ERP Cloud adapter. The reason being the response will be a zip file which consists of source transaction files as well. Leveraging a SOAP adapter gives us the ability to get response as Attachment

                 Pass RequestId as input parameter for the operation

• Unzip all files and fetch just the log files by iterating through the files. The log file pattern will be <request_id>.log
• Send an email notification with the attachment of log file

Testing the END to END Flow

• Download the Transaction zip file from Setup & Maintenance > Setup: Fusion Accounting Hub > Accounting Transformation > Manage Subledger Application
• In the scope column select the Subledger Application that you have setup. I my case it is "99Personal LoanMortgages"

• Events that are configured for my Subledger Application are Loan Orginated,Interest Accrual,Loan Payment etc.,

• Download Template will download a zip file which consists of Header file, Lines file and Metadata file

• Modified the XlaTrxH.csv to capture Loan Orginiation of 3 Customers with respective Transaction numbers and other details as per the Template

• Added the Respective Loan Transactions for the 3 Customers with a Matching Transaction numbers as provided in the Header file

Run Integration Flow1

• Push the XlaTransactionUploadTemplate99.zip file into configure SFTP location and trigger the Scheudled Integration Flow (Flow1).
• Following gets triggered based on the param list passed

• Once the Job is Successful, Select Import Accounting Transactions and notice the log file. The Job output zip file that we fetch in Flow 2 consists of the log files along with source transaction files.

• Observe a record with ERP in the Job_Status (Parking lot table)

Run Integration Flow2 - Db Poller

• The Job poller Integraiton Flow fetches the ERP Status and based on the Status it retreives all the jobs and sends a Notification with .log attachment alone

• Search for the Journals from the General Accounting > Journals > Tasks panel > Subledger Accounting > Review Subledger Journals. Notice that Journals are in 'Draft' state because we passed 'D' as one of the parameters from OIC when invoking submitESSJob.

• Verify the Journal Lines

Conclusion: Why You Should Integrate Oracle Accounting Hub Cloud with Your Business Systems?

We can consolidate corporate accounting in a single system which is flexible & powerful to optimize the fiscal reporting process and serve as single source of truth across corporate integrating with disparate transaction systems through a touchless end-to-end process. Oracle Integration with its features and Out of the Box ERP Cloud adapter capabilities helps us achieve the required automation in place.

References

• Accounting Hub Best Practices
• Accounting Hub Cloud Docs
• Oracle Integration Docs
• Accounting Hub Integration duty role