Loading GL Balances from EBS to EPM Cloud using the EPM Integration Agent adapter

In October 2019 (19.10 update) Oracle released the long awaited EPM Integration Agent, a tool which allows seamless integration of data from on-premises data sources to EPM Cloud via Data Management. To help us even further, Oracle also included an adapter to extract GL balances from an Oracle EBS source system! This blog details how to setup and execute the EBS GL adapter to load monthly Actual balances to EPM Planning.

This blog does not cover the installation of the EPM Integration Agent. For information on getting the agent up and running, I’ll pass you over to this comprehensive blog from John Goodwin: https://john-goodwin.blogspot.com/2019/10/epm-cloud-integration-agent-part-2-get.html

Once you’ve got the agent running, we will need to setup the extract in Data Management. The first step for this is to navigate to Target Application and add the Data Source using the Oracle template for EBS GL balances data export.

From the Source System dropdown, select EBS GL Balance option. This will automatically name the application “EBS GL Balance”. Add a prefix if there is a requirement for multiple instances of the extract query. For this test blog I’ve added prefix “Test_” so the Target Application will be created with name “Test_EBS GL Balance”

Upon creating the Target Application, the system will automatically generate the SQL query required to extract GL data from the EBS GL balances table. This is a standard template which extracts a number of fields from Oracle EBS. These fields are then mapped to Planning dimension using standard import format and data load mappings in Data Management.

Dimension Details

The fields that are extracted using the EBS query are listed on the dimension details of the target application details.

The extracted fields (Oracle spelling 😉) are as follows:

• Acoount Type – Type of account indicated with a single letter, e.g. R for Revenue accounts
• Balance by Acct Type – Calculated balance depending on account type, in local currency
• Balance Type – the Balance type (Actual, Budget, or Encumbrance)
• Beg Balance CR – Net credit beginning balance in local currency
• Beg Balance DR – Net debit beginning balance in local currency
• Budget Version ID – Not currently used
• Code Combination ID – Identifier for combination of custom segments
• Currecy Code – Local currency code
• Enabled – indicates whether the code combination is enabled or disabled
• Encumbrance Type ID – defines the type for Encumbrance balances
• Func Eq Balance by Acct Type – Calculated balance depending on account type, in base currency
• Func Eq Beg Bal DR – Net debit beginning balance in base currency
• Func Eq Beg Bal DR_1 – Net credit beginning balance in base currency
• Func Eq Period Net CR – Period net credit balance in base currency
• Func Eq Period Net DR – Period net debit balance in base currency
• Func Eq Periodic Balance – Period balance in base currency
• Func Eq YTD Balance – Period Year-to-date in base currency
• Ledger ID – Code ID for the EBS Ledger
• Ledger Name – Name of the EBS Ledger
• Period Name – EBS Period Name e.g. MAR-20
• Period Net CR – Period net credit balance
• Period Net DR – Period net debit balance
• Period Type – Type of accounting period
• Period Year – EBS Year e.g. 2020
• Periodic Balance – Period balance in local currency, calculated in query as: (Period Net DR - Period Net CR)
• Perion Number – Period number e.g. 9 (for 9^th month of financial year)
• Summary Account – Indicates whether summary account combination
• Translated – Indicates the translation status of the balance
• YTD Balance – Year-to-date in local currency, calculated in query as: (Beg Balance DR - Beg Balance DR) + (Period Net DR - Period Net CR)
• Segment1, … , Segment30 – Custom Oracle EBS segments, for example CoA codes

For further detail of the EBS GL_BALANCES table fields, see Oracle documentation here: https://docs.oracle.com/cd/E83857_01/saas/financials/18b/oedmf/GL_BALANCES-tbl.html

Application Filters

The application filters define the connection details for the source environment that will be used by the EPM integration agent when running a query.

The filters are as follows:

• Delimiter – Comma by default (remember this for the import format stage)
• Credential Store – Specifies that credentials are stored in Cloud (does not require editing)
• JDBC URL – Enter URL for on-prem source in format jdbc:oracle:thin:@<host>:<port>:<sid>
• Username – Enter Username for on-prem tables
• Password – Enter Password for on-prem tables
• Fetch Size – Number of rows fetched with each database query, default 1000

Note: when using the EPM integration agent against a custom on-premise database source (not using the EBS GL Balances adapter), there are two additional variables:

• Data Extract Query – Name of the SQL query which will be executed. The query for the EBS adapter is automatically generated so this field is unavailable.
• JDBC Driver – Database type, can be set as Oracle or SQL. For the EBS adapter, this is automatically set as Oracle.

Import Format

The import format however will not be automatically generated by the adapter, so must be setup manually.

Enter the following:

• Name – Name the import format
• Description – Add a summary description of the usage
• Source – Select the EBS GL Balance Target Application created earlier
• Target – This will be Planning if loading to an EPM Cloud Planning application
• File Type – Delimited Numeric Data for the EBS GL balances load (remember this for the data load rule)
• File Delimiter – Must be equal to the delimiter set in the target application filter earlier
• Drill URL – [Blog for setting up EBS drill-through to follow 😊]

In the import format mappings section, map the EBS segments to the Planning dimensions using the source column dropdowns. Depending on the currency format of the application, the monthly periodic balance amount field will be either:

• Func Eq Periodic Balance – Period balance in base (reporting) currency
• Periodic Balance – Period balance in local currency

Location

Create a Location and link the EBS import format created earlier. Double check that the Source (Test_EBS GL Balance) and Target (Planning) have linked successfully from the import format.

Data Load Rules

Once the Location is created, add a new Data Load Rule referencing the import format created earlier. In this case, we are using the Actual Category, to map the EBS GL Balances to the “Actual” Scenario in Planning.  Default (global) period mappings will be used.

The data load rules use two run-time source filters which must be set before executing the load:

• Ledger – Name of the EBS Ledger to export
• Period – Period Name in EBS format, e.g. JUL-19

Each load rule references a single Ledger, so multiple load rules may be required to pull data from all EBS Ledgers.

Note: These filters must be the exact name of the Ledger/Period; unfortunately using wild cards will not work ☹ (this is because Oracle’s SQL query uses an ‘Equals’ test, rather than ‘Like’)

Add the mappings for each dimension as required in the Data Load Mappings area.

Execute the data load rule, making sure to select the same period that is referenced in the Period source filter. View the source and target data in the Data Load Workbench to confirm correct data intersection and mappings have been applied.

In practice, we’ve found that executing a data load for a single Ledger and Period isn’t a particularly efficient process. For example, it’s not possible to run the query for multiple periods at once, making updating historic periods difficult.

To get around this issue, it is possible to retrieve the full ‘EBS GL Balance’ SQL query from the process log file, create a custom source and tweak the SQL to include different filters. [Blog to follow detailing how to do this 😊]

Thanks for reading!