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 Oracle Cloud EPM 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 Oracle EPM Cloud 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: HERE

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 Oracle EPM Cloud Planning dimensions 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

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-premises source in format jdbc:oracle:thin:@<host>:<port>:<sid>
Username – Enter Username for on-premises tables
Password – Enter Password for on-premises tables
Fetch Size – Number of rows fetched with each database query, default 1000

Note: when using the EPM Integration Agent against a custom on-premises 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 Oracle Cloud EPM 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 Oracle Cloud EPM 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 (Oracle Cloud EPM 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 Oracle Cloud EPM 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!

Read about Lydia’s six years as a consultant with Brovanture HERE