A common question Brovanture’s clients have when moving from Hyperion Financial Management (HFM) to Oracle Cloud EPM Consolidation and Close is, how do we add validations into the close process? They want to prevent users from promoting data when certain conditions haven’t been met, such as Balance Sheet balancing, depreciation in the Profit and Loss = depreciation in the Balance Sheet etc.
The answer is simple, in ‘Data forms’.
Creating validations in Oracle Cloud EPM Consolidation and Close is a significantly easier for the Administrator to manage compared to HFM. In HFM the administrator would need to create new metadata and rules. In Oracle Cloud EPM Consolidation and Close it’s easy, simply create the criteria to be validated in a data form and then add a validation rule using the rule builder to apply the required conditions.
Let me take you through a couple of examples.
Example 1 – Results of a cell determines if the validation passes or fails.
The scenario below shows a headcount data form for two entities, a validation is required to flag if the headcount data is missing.
We can see in the example below that Entity 1 has headcount data populated but Entity 2 has no headcount data and therefore fails the validation and highlights the Check cell red.
Step 1 – Create a calculation row which contains the data to validate.
Step 2 – Create the validation by selecting the row you wish to hold the validation, in this case row 3 Check row, and from the properties panel select Validation Rules
Step 3 – Ensure the rule location is set to row 3 as seen below and click the to create a new rule.
Step 4 – This will open the Data Validation Rule Builder screen as show below. Enter the desired Rule Name and description and tick the enable validation rule, next use the to add a new condition and populate with the required criteria as below.
Condition = If, Source Type = Current Cell Value, Operator = <=, Target Type = Value, Target Value = 0, Process = blank, Actions = none selected
Condition = Then, Source Type = Process Cell, Actions = Click the format icon
This opens the Data Validation Rule Builder formatting window where you can choose the Cell Background Colour when the validation fails.
Next, choose the Approval action, either None (soft validation) Update promotional path or Do Not Promote (Block Promotional path)
Once you are happy with the rule click Validate and then OK and save the data form.
Example 2 – A Validation based on the difference between two data points
Step 1 – Create a data form with the two data points in separate rows or columns and then add a Formula row/column to calculate the variance between the two data points, this will be used for the validation.
Step 2 – Select the formula row where the variance of the two data points is held and open the Data Validation Rule Builder and build out the conditions as per below, this validation rule checks the variance calculation is withing a tolerance of -10 and +10, here we have 2 process cell lines to format the cell Green if ok and red if it fails, and the same as the 1st example here we have added an additional condition to prevent the data from being promoted if the validation fails.
When setting the cell format for row 4, you can select the Approvals to “Do Not Promote”
Until Next time
Please note that while we try to keep our blogs UpToDate, Oracle publish updates monthly and so there may be changes, we always advise to check the latest admin guide published by Oracle.