Streamlining adjustment period data load in Oracle Cloud – ERP to EPM

Lydia Maksoud

Brovanture Consultant

Typically, Oracle Cloud EPM Planning applications will have a 12-period year which can present a challenge when loading adjustment period data from Oracle Cloud ERP. To get around this, Oracle have provided the ability to map Oracle Cloud ERP adjustment periods to Oracle Cloud EPM periods in data exchange. Adjustments are usually loaded to the first or last period, depending on if the adjustment is applied at the start or end of the year.

Adjustment period mappings are not automatically set up when initialising the source system, so the first step is to create these in the Source Mapping tab, with mapping type Adjustment:

Enter the name of the Adjustment period and map it to a target the Oracle Cloud EPM period using the period keys as a reference. Note: the format of the adjustment periods may differ too above.

To enable loading adjustment period data, use the Include Adjustment Periods setting on the data load options, with one of the below options:

• No – load period value only (no adjustment values)
• Yes – this setting will load both the period and the adjustment value, according to adjustment period mappings
• Yes (adjustment only) – only the adjustment period value will be loaded (no periodic data)

The rest of this blog will assume that the Yes setting has been selected, meaning that both periodic and adjustment data will be pulled through. For example, when we run a load for Oct-22, the load will pull in Oct-22 period data plus Adj-Oct-22 adjustment data.

As both sets of data are being loaded to a single period in Oracle Cloud EPM (Oct FY22) it would be useful to be able to distinguish between which lines are periodic (Oct-22) and which are adjustments (Adj-Oct-22). Luckily, we have found a way to do this with a simple SQL mapping!

This trick uses the GL period name, which is loaded into a hidden attribute in the data load. To find which attribute contains the GL period, go to the workbench in data management (note: at the point of writing this feature isn’t available in data exchange), and click View -> Columns -> Show All

 

Scroll over to the right to find the attribute which holds the GL period (See Attribute 11 below).

 

We can now use the attribute 11 to map data, depending on if the data is an adjustment or not.

For example, we can map a component dimension as “Base” for periodic data and “Adjustment” for adjustment data, using the below SQL mapping:

CASE

WHEN ATTR11 like ‘Adj%’ THEN ‘Adjustment’

ELSE ‘Base’

END

 

Hope this helps!

Until next time

Lydia

More post by Lydia can be found on her blogspot page HERE