Loading Oracle Cloud ERP GL Balances to ARCS

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

Before setting up the GL balances load from Oracle Cloud ERP to ARCS, there are a couple of prerequisite tasks required, which are part of the standard ARCS 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 ARCS setup, which cannot be reversed without rebuilding the application. Then add all the necessary ARCS 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 ERP Cloud 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. 

 

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

Configure the source connection with URL in the following format: https://server.fa.ocs.oraclecloud.com, where the server is your Cloud ERP server name.

This will require an ERP user with General Accountant user role and data access to all 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.

2. 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 ARCS:

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 ARCS.

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

3. 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 ARCS segments.

4. 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.

5. 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

6. New Data Load Rule

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

7. Data Load Mappings

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

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.

8. Import Data to Workbench

Unlike in FCC or Planning, the data cannot be loaded to ARCS 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.

9. 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.

10. 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 ARCS

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


4. View the load status in the Result column. 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.

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

11. Drill-through to Source

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

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 EPM Account Reconciliation (ARCS). Hope it helps!

Comments

Post a Comment

Popular posts from this blog

Loading multi-period row data files using Data Management

Image
The ability to load multi-period data with the Period and/or Year in the rows is not particularly new (since release 16.10), however it is something that most people seem to forget is possible in Data Management. This really is something worth remembering as it eliminates the need to either split the data files into one file per month or try to pivot the month into the columns. In order to set this up, let’s firstly look at how to add the Period and Year columns to the import format. Add the Period and Year columns using the Add -> Source Period Row dropdown, and input: The Source Column name – e.g. PERIOD The Field Number (file column) – e.g. 3 Any required expressions for that field  As these are Period/Year dimensions, the mappings are not held in the usual Data Load Mappings area. Instead they are configured in the Period Mappings , under the Source Mapping tab. Select the Source System as File to add a new mapping calendar for loading the data ...
Read more

Executing Smart View Retrievals using VBA

Image
When working with Hyperion systems, Smart View is a key tool used for reporting, as well as inputting data. It provides a valuable link between Hyperion and Excel, which allows users to retrieve data from the database straight into Excel with ease. Smart View functions are available through the Smart View toolbar and can be used with Essbase, HFM, Planning or Cloud connections. Occasionally, it is necessary to perform Smart View functions behind the scenes using VBA code, rather than manually using the Smart View toolbar. For example, a spreadsheet which will refresh all the Essbase retrieval sheets on opening, so that users can view the most up to date data, without having to remember to refresh each of the retrieval sheets themselves. This blog will be cover how to retrieve data using a Smart View refresh function in VBA. For more information about Smart View Excel VBA functions, go to the Oracle Hyperion Smart View Developers Guide .  System Requirements ...
Read more

Loading Actuals from Fusion ERP Cloud to PBCS

Image
If you’re looking to set up a data load of Actuals from ERP to PBCS and have heard this is an “Out of the box” integration, then good news! Not only is the OOTB connector included within PBCS Data Management but the integration is simple to setup and to schedule using EPM Automate. This blog will detail how to setup the ERP to PBCS connector in Data Management and some of the common errors found when running the data load. The first thing to know about this integration is that the data source is actually the ASO Essbase cube which sits underneath the Fusion ERP tables, holding the period Actuals values. This ERP Essbase cube is created using job ‘Create General Ledger Balances Cube’ when configuring the ERP system. This cube must be created before the integration can be set up correctly and can be accessed directly via Smart View for reconciliations. Set up the Source System As this is an Oracle built connector, it’s as simple as providing a name for the source system ...
Read more