If you’re looking to set up a data load of ‘actuals’ from Oracle Fusion Enterprise Resource Planning (ERP) Cloud to Oracle Planning and Budgeting Cloud Service (PBCS) and have heard this is an “out of the box” integration, then good news! Not only is the out of the box connector included within PBCS Data Management but the integration is simple to setup and to schedule using Oracle EPM Automate.
This blog will detail how to setup the ERP to PBCS connector in PBCS 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 Oracle Essbase database which sits underneath the Oracle Fusion ERP SQL tables and holds the period Actuals values. This ERP Oracle Essbase database is created using job ‘Create General Ledger Balances Cube’ when configuring the Oracle ERP system. This database must be created before the integration can be set up correctly and can then be accessed directly via Oracle Smart View for Microsoft Office 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 Oracle Fusion ERP Cloud.
Configure the source connection with URL in the following format:
Where the server is your ERP cloud server name.
This will require an Oracle ERP Cloud user with ‘General Accountant’ user role and data access to all ERP ledgers that 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 databases, due to multiple Chart of Accounts (CoA) 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 PBCS Data Management. The initialise must be run again when new years are added to Oracle ERP or Oracle 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 as a reference.
Create Import Format
Next, setup the import format for the data load from ERP. This is where dimensions are mapped 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 they can be addressed them in the Data Load Mappings area.
Now create a Location, as normal, referencing the above import format. This will hold all the data load rules and mappings for the 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 database (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 you 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 – you 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 tick ‘Use Distinct Member Names’ 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 Oracle 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
- Validation Error
If a validation error is received when importing the data from ERP, this is usually caused by a missing data load mapping.
- 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
- Yellow Warning
When importing data, a yellow warning message will be shown if the period does not contain any non-zero data
- 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 Oracle Smart View to confirm whether data exists there
- Error 3303
When exporting to target, error code 3303 indicates that the member is missing from the target PBCS application.
- Add member to target PBCS application
- Ensure database refresh is performed successfully
I hope this helps you to get your integration done successfully first time.
Until next time