Brovanture Oracle Cloud EPM

Loading Oracle Cloud ERP GL Balances to Oracle Cloud EPM Account Reconciliation

Lydia Maksoud
Brovanture Consultant

When loading GL balances from Oracle Cloud ERP to Oracle Cloud EPM Account Reconciliation, there are a few differences to the standard process used when loading to Oracle Cloud EPM Planning or Consolidation and Close. This blog will detail the full setup process required when loading GL balances to Oracle Cloud EPM Account Reconciliation for reconciliation compliance.

Before setting up the GL balances load from Oracle Cloud ERP, there are a couple of prerequisite tasks required which are part of the standard Oracle Cloud EPM Account Reconciliation setup. Firstly, ensure the correct Segments and Sub-Segments have been specified in the Configuration -> System Attributes area. This is a fundamental part of the Oracle Cloud EPM Account Reconciliation setup, which cannot be reversed without rebuilding the application. Then add all the necessary Oracle Cloud EPM Account Reconciliation Profiles and run the Create Reconciliations job for the required period.

To setup the GL balances data connection to Oracle Cloud ERP, the following 11 steps are required:

  1. Setup the Oracle Cloud ERP Source System

As this is an Oracle built connector, it’s as simple as providing a name for the source system and selecting Source System Type ‘Oracle ERP Cloud’ from the dropdown.

Brovanture Oracle Cloud EPM Account Reconciliation

Enter the Drill Through URL as R13 (this just refers to the version of Oracle Cloud ERP)

 

Configure the source connection with URL in the following format:

https://server.fa.ocs.oraclecloud.com, where the server is your Oracle Cloud ERP server name.

This will require an Oracle Cloud ERP user with General Accountant user role and data access to all the ERP Ledgers which require data exports. Enter the password for the user and test the connection to confirm.

Once successfully connected, run the ‘Initialize’ to import the source system information. After the initialisation is successfully run, navigate to the Target Application area to see the imported source system details. If there are multiple ERP cubes, due to multiple Chart of Accounts setups, they will all be listed here.

  1. Modify the ‘Account Reconciliation Manager’ Target Application

Navigate to Setup -> Target Applications in data management (or in Data Exchange, Actions -> Applications) and open the Account Reconciliation Manager application. Add any additional dimensionality which has been specified in the Profile Segments setup area as either a main segment or a sub-segment.

For example, we have the following profile segments activated in Account Reconciliation:

Brovanture Oracle Cloud EPM

Therefore, we’ve added all these segments into the Account Reconciliation Manager target application. This allows us to see the data at a detailed level when loaded to Account Reconciliation.

Brovanture Oracle Cloud EPM

Add the data table column name for each segment, this does not matter as long as they are unique. it is just used to store data in the tables behind the scenes. Also note the following need a specific dimension class:

  • Currency Bucket = Scenario (this will be the data management category)
  • Period = Period
  • Profile = Account
  1. New import format

Add a new import format, with the GL cube as the source and ‘Account Reconciliation Manager’ as the target. Then map each of the GL segments to the Account Reconciliation segments.

Brovanture Oracle Cloud EPM

  1. Setup Source Period mappings

Make sure to setup the period mappings for the Account Reconciliation Manager target application data loads. The ERP calendar period mappings should automatically be setup when initialising the ERP data source in step 1.

  1. New Location

Add a new location which will hold all data load rules and mappings for the GL data load. Add the relevant functional currency e.g. GBP

Brovanture Oracle Cloud EPM

  1. New Data Load Rule

Create a new data load rule in the new location, noting the following:

  • The category should be set to Functional
  • Select Default Period Mapping Type to use the standard period mappings
  • Select whether to include adjustment periods or not. See https://brovanture.com/streamlining-adjustment-period-data-load-in-oracle-cloud-erp-to-epm/ for more details.
  • Set the Source Options, specifying the filter conditions for each of the source GL segments. Typically for Account Reconciliation, the YTD amount is required for reconciliations.

Brovanture Oracle Cloud EPM

  1. Data Load Mappings

The Profile mapping is the most important mapping for loading data to Account Reconciliation, as this is how the data will be displayed against the correct reconciliation. This will depend on how the profiles have been setup in Account Reconciliation.

In my example setup, the Profile name uses format Company-Account so we use a SQL mapping to combine the segment values, as UD3 || ‘-‘ || UD4 (check the data table column name in step 2 to find the correct UD codes).

The Source Type mapping should be set to source system (without capitals). Note, if capitals are included, the data load will not show as failed, but it will not load to the reconciliations correctly.

For the remaining segments, map one to one, or add mappings where required.

  1. Import Data to Workbench

Unlike in Consolidation and Close or Planning, the data cannot be loaded to Account Reconciliation using data management or data exchange. However, before running the full data load process (step 9), we can import the data to the workbench to review and check that all mappings are working as expected.

  1. Setup Saved Data Load

Navigate to Configuration -> Data Loads (6th tab down) and add a new data load.

Input a Name and Description and select the relevant data load locations from the list at the bottom. The mode is dependent on the load method, with the following options:

  • Full Refresh – Clears out and reloads all balances for the selected period, and should be used if load definitions have been changed
  • Merge – Replace existing balances and add any new balances, but don’t clear other balances
  • Recalculate – Reapply mappings only, without reloading data
  • Snapshot – Replace or update previously loaded balances, only in locations specified (used in our example)

Once setup, we can reference this data load when running the load to a selected period in step 10.

Brovanture Oracle Cloud EPM

  1. Run Data Load

As explained in step 8, loading data does not follow the standard process. Rather than loading via data management or data exchange, you must do the following:

1. Go to the Application -> Periods area in Account Reconciliation

2. Then on the required period, select Actions -> Import data

3. Add a new data load execution, tick Use saved data load and select the data load setup in step 9. Note: once the load has been run once, you can select a previous load to rerun using the Rerun button

Brovanture Oracle Cloud EPM

4. View the load status in the Result This will display any errors or invalid mappings. Note: invalid mappings can appear if not all data lines are used in a reconciliation, however the rest of the data will have loaded.

Brovanture Oracle Cloud EPM

5. Once loaded successfully, view the loaded data in each of the reconciliations:

Brovanture Oracle Cloud EPM

  1. Drill-through to Source

Click the Ending Balance number link to open the Drill Through Details window.

Brovanture Oracle Cloud EPM

Use the Action menu on each line to Drill Through to Source and open the account balances viewer directly in the ERP source system.

Those are all the steps required to load GL balances from Oracle Cloud ERP GL to Oracle Cloud EPM Account Reconciliation. Hope it helps!

Until next time

Lydia

More posts by Lydia can be found on her blogspot page HERE