Loading Actuals from Fusion ERP Cloud to PBCS

July 05, 2019

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 and selecting Source System Type ‘Oracle Financials Cloud’ from the dropdown.

Enter the Drill Through URL as R12 or R13 depending on the version of Fusion ERP.

Configure the source connection with URL in the following format:

https://server.oraclecloud.com

Where the server is your ERP cloud server name.

Note: the format of this URL was previously "https://server/publicFinancialCommonErpIntegration/ErpIntegrationService?WSDL" until 2020. If this format is used, the initialization will error with message "Exception: java.lang.Exception: The rest service request has failed: 415 Unsupported Media Type"

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, ‘Initialize’ to import the source system information.

After the initialize 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.

Configure Period Mappings

Period mappings between ERP and PBCS are setup automatically when the source system is initialised in Data Management. The initialise must be run again when new years are added to ERP or PBCS.

Adjustment period mappings however are not automatically set up. They must be manually added in the ‘Source Mapping’ tab, with mapping type ‘Adjustment.

The adjustment periods in ERP are in format ADJ1-YYYY. Enter the name of the Adjustment period and map it to a target PBCS period using the period keys are a reference.

Create Import Format

Next, we need to setup the import format for the data load from ERP. This is where we map the dimensions between Source and Target systems. This will be the basis for creating the Data Load Mappings later on.

If there are dimensions which exist in the Target but not in the Source, leave these blank and we can address them in the Data Load Mappings area.

Location

Now create a Location, as normal, referencing the above import format. This will hold all the data load rules and mappings for our integration from ERP to PBCS.

Data Load Rules & Source Filters

When setting up the data load rules, it is recommended to have one data load rule for each ERP Ledger that will require a data pull.Within each data load rule, specify data filters for each dimension in the Source cube (ERP). As well as specifying filters for any custom CoA segments (e.g. Project, Account etc.), filters must be added for the core ERP dimensions, listed below:

Scenario – specify that we want the Actual data
Ledger – name of the Ledger that this rule is pulling data from e.g. LedgerA
Balance Amount – usually take Period Activity for data comparisons in PBCS (other options: Beginning/Ending Balance, Debit/Credit Amount)
Amount Type – we need Base (other options: PTD or YTD)
Currency – in this case GBP only
Currency Type – take the Total value, which includes all input and converted values (other options: Base or Converted)

Tip: Use the Select... button and select Yes for setting ‘Use Qualified Member Name’ to avoid error from duplicate member names or aliases in the Source system.

When running a data load rule, there is also the option to include Adjustment periods on load (Yes/No) or to load Adjustment period data only. This can be setup on creation of the data load rule or changed at run time. This uses adjustment period mappings setup in the Period Mapping section earlier.

Data Load Mappings

Any data load mapping type can be used for this integration, including SQL mappings, and are setup in the same Data Load Mappings area as when loading data from a file.

For dimensions which are present in the Target (PBCS) but not the Source (ERP), map all data to one member in PBCS (e.g. map all data to version “Working”)

Executing the Data Load

Once all of the setup steps are complete, the data load from ERP to PBCS can be run just like any other file data load in Data Management, by executing the Data Load Rule.

It’s also possible to use EPM Automate command RunDataRule to kick off the data loads using a script. This can then be automated overnight if required. See RunDataRule command for more information.

Common Integration Errors

1. Validation Error

If a validation error is received when importing the data from ERP, this is usually caused by a missing data load mapping.

Checks/Resolution:

· Go to Workbench and check ‘Validation Errors’ tab to see which dimensions caused the error

· Review/amend data load mappings to make sure all data is mapped

· Re-run data load rule

2. Yellow Warning

When importing data, a yellow warning message will be shown if the period does not contain any non-zero data

Checks/Resolution:

· Make sure correct period was selected when executing the rule

· Review source filters to confirm correct data intersection is being picked up

· Review data intersection in ERP via Smart View to confirm whether data exists there

3. Error 3303

When exporting to target, error code 3303 indicates that the member is missing from the target PBCS application.

Checks/Resolution:

· Add member to target PBCS application

· Ensure database refresh is performed successfully

4. RunTimeError

Although quite generic, a “RunTimeError” is usually caused by the ERP Essbase cube being locked at the time of running the data load. Some potential causes of the ERP Essbase cube being locked are:

      ·         ERP cube restructure in progress
      ·        Scheduled Job running on the ERP system
      ·        ERP Environment is currently unavailable

To proceed, ensure the ERP cube is unlocked and no jobs are currently running on the ERP system before submitting the data load again.