Sarah Ansell

Brovanture Consultant

Use a Clear Region to ensure that the target intersection for loading is empty before running a data integration via Data Exchange or Data Management. Not only can a Clear Region wipe data from Oracle Cloud EPM Planning, the functionality can be applied to Oracle Cloud ERP General Ledger too! This allows users to clear and reload data in the GL simply by launching the Data Load Rule from Oracle EPM Cloud Planning.

Use Cases

Daily refresh of Actuals from Oracle Cloud ERP GL
Saves scheduling a clear data Business Rule ahead of the daily data load!
Pushing / pulling data between Oracle Cloud EPM Planning & Oracle Cloud ERP GL to overwrite a ‘Latest Forecast’ scenario member to be used as a default for reporting tools.
Pushing / pulling data between Oracle Cloud EPM Planning & Oracle Cloud ERP GL in scenarios where only a subset of data requires clearing.
The standard Replace Export Mode (more on this below) would clear all account members (e.g., FTE data) regardless of the source data.

What you need to know

General

The Clear Region is attached to a Data Load Rule and can only be configured within Data Exchange – Data Integration.
The Clear Region is only applied when using the Export Mode: Replace
Planning to automate using EPM Automate or the Batch Scheduler?
This is not an issue! Once the Clear Region is configured in Data Exchange, provided the Export Mode Replace is set, the clear will still be applied when the rule is automated.

Pushing Data to Oracle GL

Export Mode Replace is not available when running via Data Management and therefore your Clear Region will not be applied. Instead, create a Batch, for which you can select the Export Mode, and then run the batch!
There is a limit in Oracle Cloud ERP GL on the number of members that can be cleared via a data load. The Member Functions such as ILvl0Descenants are not used. Instead, the Parent is defined and the load creates a flat list of lowest level members beneath the parent for clearing. This can cause the limit to be reached. Split the load into multiple load rules to avoid this – not so sleek but unavoidable at times!

How to configure a Clear Region

Create a Data Load Rule
Navigate to Data Exchange – Data Integration and click to edit the Integration
Select the Options tab
Within Options, select the Clear Region tab
Click the + to add a dimension for clearing or use the drop down arrow to change a dimension
When Oracle Cloud EPM Planning is the target, you can use Member Functions in the Filter Conditions, such as @ILvl0Descendants(Total Entity).
When Oracle Cloud ERP General Ledger is the target, you can select the Parent for which to clear all lowest level members beneath, but you cannot use Member Functions.
If preferred for a dimension, you can use Derive From Data to only clear any members that are featured in the dataset. Leave the Filter Condition empty for these dimensions.
Some of the Clear Region will be automatically defined from the dataset regardless of the definition selected here.
- For Oracle Cloud EPM Planning targets, the Period, Year, and Scenario are defined from the dataset
- For Oracle Cloud ERP GL targets, the Ledger, Scenario, and Accounting Period are defined by the dataset
Click Save and Close
Run the integration using Export Mode: Replace
- For Oracle Cloud ERP GL Targets Data Management will offer Replace as an export option – instead create a Batch and configure the Export Mode to Replace using the Batch Options.
- Optionally: Automate via Batch Scheduler or EPM Automate and the Clear Region will be applied.

View the Log to see more detail on the Clear Region applied:

What is different when using Export Mode Replace without a Clear Region?

Replace data uses the Point of View and contents of the dataset to clear data for the Year, Period, Scenario, Version, and Entity dimensions. However, it clears for all accounts, and all other custom dimension members.

What if your data has moved from one entity to a different entity and no longer featured in the subsequent dataset? Using Replace, without a set Clear Region, the original entity will remain un-cleared and duplication would occur.

Clear regions allow users to be more specific in defining the region that is cleared.

Supporting Information from Oracle Documentation

Can only be used with Oracle Cloud ERP Financials 20C or higher
For the Oracle Cloud ERP Financials, you cannot specify member functions. You can select parent member and system clear data for all lowest level children automatically.
For Oracle Cloud EPM Planning, the Period, Year, and Scenario dimensions are derived automatically based on the data and you do not have to specify any filters for these dimensions. If you specify member for these dimensions, they are ignored.
For Oracle General Ledger, the Ledger, Scenario, and Accounting Period dimensions are derived automatically based on the data and you do not have to specify any filters for these dimensions. If you specify member for these dimensions, they are ignored.

For more useful Oracle Cloud EPM and NetSuite ERP blogs posted by my colleagues, see the Brovanture website HERE

Until next time

Sarah