As explained in my previous blog on loading ‘actuals’ from Oracle Fusion ERP Cloud top Oracle Planning and Budgeting Cloud Service (PBCS), the GL actuals data is stored in an ASO Oracle Essbase database, referred to as the GL Balances cube within Oracle Fusion ERP Cloud. What I didn’t cover in the previous blog is that, as well as holding the GL actuals values, this Oracle Fusion ERP Essbase cube can be configured to also hold budget and/or forecast data, which can be sourced from PBCS.
The main advantage of doing this, is that the budget and forecast data would be available alongside real-time actuals for reporting using Oracle Financial Reporting Centre (FRC) or Oracle Transactional Business Intelligence (OTBI).
Before this integration can be setup using Oracle Data Management:
- The Oracle Fusion ERP Essbase cube must have been created using job ‘Create General Ledger Balances Cube’ when configuring the ERP system
- Budget/forecast scenarios must be added to the CoA via ‘Manage Chart of Accounts Value Sets’ task
- Scenarios must be deployed to the Oracle Fusion ERP Essbase cube using job ‘Create Scenario Dimension Members’ – these should be visible in the ERP scenario dimension
When pushing budgets to the same cube as the GL actual balances, you can use the same source system and period mappings as with the actuals load:
Setting up with Oracle Fusion ERP Cloud source system is explained here.
Create Import Format
Next, you need to setup the import format. This is where you map the dimensions between source and target systems and will be the basis for creating the data load mappings. Remember here, the source is the PBCS application and the target is the Fusion ERP system.
Make sure to add a mapping for the ‘Ledger’ dimension here, even if it is not relevant. If there are other dimensions which exist in the target but not in the source system, leave these blank and you can address them in the Data Load Mappings area.
Now you can create a location to store all the data load rules and mappings for this integration, referencing the import format just created.
If the Oracle Fusion ERP Essbase cube contains multiple currencies, make sure to specify a Currency in the ‘Functional Currency’ field. In this case, all data in PBCS was GBP.
Data Load Rule & Source Filters
As with the Actuals load, when setting up the data load rules, it is recommended to have one data load rule for each ERP Ledger, including the Consolidation Ledger – this is held as a separate member in ERP and does not automatically equal the total of the Primary Ledgers.
Within each data load rule, specify the ‘source plan type’ (PBCS) and complete the data filters for each dimension in the chosen plan type. Keep in mind that this integration will use the source data filters listed here for the scenario and version, rather than using the category mappings (as it does for a data file load).
As the source is PBCS here, we can reference functions e.g. @Lvl0Descendants and Substitution Variables, referenced with ampersand (&) e.g. &CurrBud
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 (ERP) but not the Source (PBCS), map all data to one member in PBCS (e.g. map all data to “Default” Intercompany)
Remember, in this example the scenario is mapped from the version (set in import format) and the integration will use these mappings instead of the category mappings.
Executing the Data Push
Once the above setup steps are complete, the budget push from PBCS to ERP can be run just like any other file data load in Oracle 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.
Note! It is important that the ERP cube remains unlocked during the data load. Please bear this in mind when scheduling data pushes using EPM Automate.
Clearing Data in Fusion ERP
When pushing budget or forecast data from planning (PBCS) to a defined accounting scenario in ERP, the data will only be overwritten for combinations which have non-zero data in the push. This means that if data has been cleared to #missing in the PBCS source data, then existing values in ERP will not be overwritten.
Unfortunately, in Oracle Cloud ERP this isn’t as simple as running a business rule to clear out a specified slice of data, unlike in the agile world of Oracle EPM Cloud.
The quickest way we have found to clear out an entire Accounting Scenario in ERP is to essentially delete and re-add the scenario in ERP. As you can imagine, this is a manual process that cannot be scheduled, making it difficult for this integration to be fully automated.
Note! If an error is encountered during this integration, then no data is loaded at all and the target intersection will remain empty.
- Missing or Disabled Members
If an ERP member (segment value) is missing or has been disabled, then the data push will fail.
- Check mappings to ensure correct member name is referenced
- Re-enable or add member into CoA segment and deploy to cube
- Parent level data
Upper level members are tagged as being a ‘parent’ value in ERP, whether they have any children or not. This will cause an error if data is stuck in a childless parent value in PBCS and has been picked up by the data load source filters because it is technically a level zero member.
- Check mappings to ensure correct member name is referenced
- Confirm if there are any ‘childless parents’ in PBCS. If so, remove member or data before re-running the data load.
Although quite generic, a ‘RunTimeError’ is usually caused by the Oracle Fusion ERP Essbase cube being locked at the time of running the data load.
Some potential causes of the Oracle Fusion 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 Oracle Fusion ERP cube is unlocked and no jobs are currently running on the ERP system before and submitting the data load again.
Until next time
Find our more about EPM and ERP software solutions from Brovanture HERE
Lydia also blogs on her own blogspot page HERE