Oracle Cloud EPM Business Rules Custom Error Messages

Richard Jeffries

Brovanture Consultant

Oracle Cloud EPM Business Rules Custom Error Messages

Quite often we get requirements in Oracle Cloud EPM whereby certain combinations of Smart List choices or numeric data shouldn’t be allowed. This is a level of detail that valid intersections can’t really help solve. For instance, in a workforce application, a user may submit National Insurance (NI) data for an employee whose salary isn’t high enough to qualify them for it. A valid intersection could be used to block the NI input for that employee everywhere, but it cannot do that based on the values being stored in the cube.

One solution would be to just clear the data if its incorrect e.g.

ELSE()

“National Insurance” = #Missing;

ENDIF

However, this can lead to users not understanding where the data went, it isn’t clear that they have made a mistake with this approach. What if the national insurance data was correct but was simply entered on the wrong row? The user would either miss this entirely or would have to go back through row by row to find where it went missing.

This is where @Return and @Currmbr come in. Using the @Return function will stop the business rule in its tracks as soon as the ELSE command has been fulfilled. This will then allow you to bring back the current member as a string to tell you where the business rule went wrong, allowing the user to fix this immediately e.g.

ELSE()

@RETURN(@CONCATENATE(@NAME("National Insurance Incorrect for "), @NAME(@CURRMBR("Entity"))), Error);

ENDIF

The error message that pops up if run from Oracle Smart View for Office will look something like this:

All you need to do is build out the IF statement for your specific requirements and the rest should give you the extra detail you need.

This can also be very useful during development work when your hitting issues with IF statements not seeming to include all the members you expect. Sometimes seeing the first member in the hierarchy that is causing issues can help a lot in identifying a pattern so you can solve the problem at hand.

Until next time.

Richard