Are you tired of spending countless hours on manual accounting tasks? Do you want to maximize your efficiency and streamline your workflow? Look no further than Oracle Integration’s Accounting Hub Cloud integration automation capabilities. This blog post will explore using these tools to automate processes and save valuable time in your day-to-day operations. Get ready to revolutionize how you manage financial information with this powerful technology!

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 numerous reporting requirements in an automated and controlled fashion, 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 sub-ledgers to enhance reporting.

Why do we need to Automate the Process of importing Transactions?

Customers with high volumes of transactions from diverse industrial 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 must also integrate new industry-specific systems or recently acquired companies into their existing environment and automate seamlessly importing accounting transactions into Accounting Hub.

Usecase

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

Solution

Oracle Integration capabilities are used to automate the process of Subledger posting of Loan Transactions into Accounting Hub Cloud. Out-of-the-box ERP Cloud adapter capabilities help to integrate seamlessly with UCM Services and ESS jobs to simplify the import process further. 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 external files that don’t match the template.

Pre-Requisite Configuration

Roles Required for Integration User

1. Add the below roles to the Integration User in ERP Cloud.
2. Create a new job role under the role category “Common – Job Roles.”
3. Under role hierarchy, add the following duties:

• ORA_XLA_ACCOUNTING_HUB_INTEGRATION_DUTY
• ORA_XLA_ACCOUNTING_HUB_INTEGRATION_DUTY_OBI
• FIN_FUSIONACCOUNTINGHUB_IMPORT_RWD

1. Save this job role.
2. Assign this job role to a user.
3. Run the following jobs:

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

Set up Accounting Hub Cloud

Setting up Accounting Hub Cloud involves three primary steps:

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

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

High-Level Design of the Integration Flow

Flow1: In this Integration Flow, we will retrieve 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, submitting the job in an asynchronous (fire and forget) fashion is always a good idea. To track the status of the job, we will use something like a parking lot table to store our job requests.

Flow2: This Integration Flow is a DB Poller that will be implemented as a Scheduled Integration. We will fetch the running job list and iteratively check the job status. If the job status is “SUCCEED” or “ERROR,” we will invoke the download ESS job details operation and fetch the log file to send a notification with an attachment proactively. The operation’s response is a .zip file containing source transaction files and the log file. Remember that the email notification attachment limit is 2MB, so we will send just the .log file in the notification by stripping off the original transaction files.

Design

I’ll highlight a few essential parts of the integration as appropriate to design the complete use case.

Flow1:

Create  a Schedule Based Integration

Configure the 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 the FTP Download operation

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

Map SubmitESSJobRequest Activity: The parameters below are 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 the parameters of the Import Accounting Transactions process and depends on whether the Flexible Configuration of the Accounting Flow feature is enabled. If the feature is not enabled, you can provide values for only one parameter. See the table below:

Parameter List

Create a table in ATP with the 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 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, which also gives the jobstatus alone.

• Invoke ERPIntegrationService -> downloadESSJobExecutionDetails. We need to use the SOAP adapter instead of the ERP Cloud adapter. The reason is that the response will be a zip file comprising transaction files. Leveraging a SOAP adapter allows us to get a response as an Attachment.

Pass RequestId as the 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 the 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 you set up. In my case, it is “99Personal LoanMortgages”

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

Download Template will download a zip file consisting of a Header, Lines, and Metadata files.

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

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

Run Integration Flow 1

• Push the XlaTransactionUploadTemplate99.zip file into configured SFTP location and trigger the Scheduled 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 we fetch in Flow 2 consists of the log files and source transaction files.

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

Run Integration Flow 2- DB Poller

• The Job poller Integration Flow fetches the ERP Status, and based on the Status, it receives all the jobs and sends a Notification with a .log attachment alone
• Search for the Journals from General Accounting > Journals > Tasks panel > Subledger Accounting > Review Subledger Journals. Notice that Journals are in the ‘Draft’ state because we passed ‘D’ as one of the parameters from OIC when invoking submitESSJob

  

• Verify the Journal Lines

Conclusion

Oracle Integration provides a powerful platform for building integrations between FAH and other applications. With its low-code, visual development environment, organizations can quickly and easily build integrations that streamline their financial processes and gain greater visibility into their financial data.

References

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