FEATURES Spring 2018

FAH/SLA Integration With External Systems

By Raj Tirumala, ADT LLC

The multiple external systems feeding detailed journal entries to ADT Security’s general ledger (GL) resulted in a thick GL rather than a thin one. This complex data infrastructure prompted an immediate need for reporting, not just to track what GL entries were resulting from external systems but also to track the corrections and adjustments for stuck transactions.

As part of an 11i to R12 upgrade project, ADT hired an external implementing partner to conduct a discovery study on the current business process pain points. After multiple working sessions with the vendor and ADT’s IT department, the consensus was to implement Oracle Financial Accounting Hub (FAH) for the ADT controllership as well the IT leadership team to solve the current business process pain point.

Although ADT IT hired an external implementing partner for the R12 upgrade project, the ADT IT project management office (PMO) made the decision to implement FAH by the internal IT team. I was assigned as an R12 finance lead for the internal IT team and tasked to implement FAH based on my previous expertise with Oracle Subledger Accounting (SLA), which helped me to understand FAH/SLA architecture and custom tailor it to our ADT business needs.

Solution Overview and Highlights:

The solution objective was to meet the following business requirements:

  • Leverage FAH/SLA to generate summary-level journal entries to GL, which will make a thin GL as well as improve infrastructure performance.
  • Provide drill-down capability to review the external feed journal entries.
  • Provide a better audit trail with the FAH/SLA architecture. SLA doesn’t allow any adjustments or corrections from the subledger.
  • Provide a reporting solution to meet the reporting requirement.

Figure 1: An overview of the Oracle standard FAH, which will help in understanding the ADT custom FAH/SLA detailed solution presented in Figures 3 and 4.

Custom FAH/SLA Solution Highlights:

  • ADT has multiple external systems, and each vendor follows a different file format to transfer the data to GL. We took a step back and came up with the option of creating a custom GL interface table that is like a standard GL interface table. We requested all ADT external vendors to update their systems to drop data into the custom GL interface table instead of sending to the standard GL table, eliminating the need to have different formats for each vendor.
  • Created custom FAH routine header/lines table to populate information based on the custom GL interface. Hence, the Oracle FAH/SLA Create Accounting program can pick up the data and create an event in the Oracle SLA tables.
  • Each external system has a unique category and source. FAH/SLA is one application and one journal source. Hence, we have created external system journal categories as an event. We have more than 50 unique events.
  • One journal source and application is used for all 20 external systems. The business user had difficulty identifying the journal entries in GL. Hence, we came up with the option of leveraging the Oracle FAH/SLA Create Accounting program parameter journal batch name as a source system name so the user can identify the GL batches by external system.
  • After deployment of the FAH/SLA solution to external systems, it was observed that external systems occasionally fed invalid GL code combinations as well as out of balance journal entries where debit total amounts were not equal to credit total amounts. This resulted in the accounting program rejecting these batches with rejection reasons of “invalid combination” or “out of balance.”

SLA Architecture

Figure 2: Quick overview of the Oracle standard SLA architecture, which is key before we go into the details of the ADT custom solution.

  • SLA will not allow any corrections to the stuck journal batches, therefore, we created a custom wrapper program that identifies these scenarios, assigns these lines to a suspense account and stores original references. At the end of the program completion, the program sends an email to the accounting team to review the journal line and take necessary action with external vendors to avoid these kinds of scenarios.
  • External systems sent the entered amount as the same for both the debit and credit side. However, when we imported the journal entry, it was double the amount in the GL. We reached out to Oracle, who couldn’t provide any solution for this. We utilized the option in FAH/SLA journal entry line type setup to change the default from reading the entered amount to reading both the entered debit or credit columns from our custom FAH tables instead of looking at only the entered amount.
  • The Oracle FAH/SLA Create Accounting program encountered performance issues when a user ran the program at the ledger level. We approached Oracle on this but couldn’t get any resolution. After exploring multiple options, we identified one that ran the FAH/SLA Create Accounting program by process category, which has improved performance greatly.
    However, this approach requires the user to run the Create Accounting program by process category multiple times, resulting in end-user maintenance issues. Since this is not ideal, I came up with a solution by creating a separate request set by external system. The request set consists of the following stages with pre-populated parameters:
    File loading process by source.
    b. FAH routine program by source.
    c. Create Accounting by process category.
    The user must select the request set. By just clicking on “submit,” it automatically does the job for the user.

  • We have leveraged OBIEE as a reporting solution to generate the detail journal entries report for each external system’s GL journal entries.
  • We have a unique situation where one of the external system chart of accounts is not the same as Oracle chart of accounts. The external system has three segment charts of account where Oracle has six segment charts of account, which is a major disconnect for importing this data. Hence, we came up with an option of leveraging the mapping set functionality for this feed. Since we have a lot of mapping values that need to be loaded, we created a data loader professional script to perform a one-time load. Going forward, we used SLA export and import application accounting definitions, which saved a lot of time instead of redoing the data loader.

Functional Configurations:

Here are the functional configurations needed to stand up a custom FAH/SLA application:

  • Create/Register new custom SLA application.
  • Create new Journal Source.
  • Configure new Entities, Event classes and Identifier.
  • Configure each event as a Process category.
  • Configure Accounting Event class.
  • Configure Lookups.
  • Configure Journal Line Type and Journal Entry Description.
  • Mapping Sets.
  • Account Derivation Rules.
  • Supporting References.
  • Journal Line Definition.
  • Application Accounting Definitions.
  • Subledger Accounting Method.
  • Associate SLAM (Subledger
  • Accounting Method) to Ledger.

Here are the functional configurations needed to stand up a custom FAH/SLA application:

  • Create/Register new custom SLA application.
  • Create new Journal Source.
  • Configure new Entities, Event classes and Identifier.
  • Configure each event as a Process category.
  • Configure Accounting Event class.
  • Configure Lookups.
  • Configure Journal Line Type and Journal Entry Description.
  • Mapping Sets.
  • Account Derivation Rules.
  • Supporting References.
  • Journal Line Definition.
  • Application Accounting Definitions.
  • Subledger Accounting Method.
  • Associate SLAM (Subledger
  • Accounting Method) to Ledger.

Figure 3: Detailed overview of the ADT custom solution flow by leveraging FAH/SLA.

Lessons Learned and Tips:

  • Oracle has limited documentation and training. With most of the road blocks, we came up with a solution to meet the requirement. For example, standard SLA allows transactions by application. In the case of the external systems interface we can’t have by application, which is a huge nightmare for the users, we came up with the option of creating unique events as a journal category.
  • Understand the complete Oracle SLA build for other seeded applications before you build the custom FAH application.
  • Leverage the export and import standard functionality for deploying application accounting definitions.
  • Create Accounting always runs with summary parameter, instead of detail parameter, so that way it will avoid XML crashing.

Technical Overview

Figure 4: Underlying custom and standard SLA tables for this solution.

Conclusion:

The project realized tremendous savings for the accounting team from a time and resource perspective. For example, before FAH/SLA implementation, we had few resources to manage the external feed coordination and get the reporting details from the external vendors. Post FAH implementation, it is like one resource managing the whole process just by scheduling the concurrent program for each external feed and generating OBIEE reports to management in a timely manner. Now FAH/SLA feeds a summary level of journal entries, and, with that, we have achieved a thin GL as well as storage savings from the infrastructure perspective.

Raj Tirumala is an Oracle senior business analyst lead at ADT Security Systems. He holds a master’s in financial accounting and has more than 12 years of experience in multiple industries, specialized in project accounting, financials, FAH and cross-functional modules.