The intent of this document is provide the reader with a fundamental understanding of Oracle’s Cross Validation Rules, enriched with lessons learned from actual implementations. Matt Makowsky is an Oracle Financial Applications Consultant with 17 years experience and a Senior Solutions Architect with Perficient. Feel free to ask him any questions in the comments section below the blog.
Cross Validation Rules, when implemented with Dynamic Insertion are a key control that, when implemented properly, can ensure the integrity of financial reports by preventing journal line postings against what would otherwise be considered invalid code combinations.
A code combination is a “string” of combined accounting segments that effectively tag a gl transaction across many dimensions of your business, for example: Legal Entity, Account, Cost Center, Product Line, Location. When values from these segments are combined in a journal entry line, a “code combination” is created, and that financial transaction can be reported independently within each segment.
Dynamic Insertion, when enabled, allows users to create code combinations “on the fly”, which is effective for efficiency, but by itself can create invalid data if a user creates a financial transaction with two or more segment values that don’t make sense. For example, a product line of “leather chairs” may not exist in the cost center of “IT Group”. One or the other may be correct, but obviously one or the other is incorrect, and therefore financial reporting will not reflect reality. The IT Group may be being charged for expenses that belong to manufacturing, or the product line of leather chairs may be charged with costs for IT salaries.
Cross Validation Rules can be used to control how dynamic insertion behaves when subjected to manual user inputs from throughout the enterprise.
Cross Validation Rules are written in ranges, across segments, specifically defining ranges of values across segments that cannot post together. With a well defined chart of accounts, and meaningful ranges within segments, cross validation rules can be very easy to write and maintain. If you are having difficulty writing effective rules, or they are too confusing, this could be a sign that your chart of accounts is not optimally defined, and should be revisited.
Best Practices:
1 – Keep the rules high level and simple. If they are too detailed, your chart of accounts may not be optimally defined. For example, it should be relatively easy to write a simple “P&L/Cost Center” rule, which would state that all P&L accounts must be assigned a cost center other than “00000”. If your accounts are created properly, then all your P&L accounts should be ranged between 400000 and 999999 (for example) without any gaps for other types of accounts.
2 – Focus on areas that are “heavy” with manual journal entries and prone to user error. If certain sets of accounts are posted automatically by subledger systems and should not have manual journals, these types of accounts can be ignored, and addressed later if you find people begin posting manually to these accounts.
3 – When writing a rule, consider only two segments at a time. More than two will make the rule confusing. It’s easier to make multiple rules across two segments rather than trying to create rules across 3 segments. These are “AND” conditions. So if attempting to use 3 segments, all 3 must be true at the same time in order for the rule to be effective.
4 – Group Rules logically. Oracle provides for many different rules, each which can have many lines associated to the rule. For example, you may have a “Product Line/Legal Entity” rule whereby certain legal entities cannot be combined with certain product lines. Do not try to combine this rule with a “Cost Center/Product Line” rule which would indicate a product line may be invalid for a give cost center. Group these separately, so the rules are easy to maintain and the error messaging is clear to the end user as to which segment should be corrected. Combining the concepts will make this unclear and confusing.
5 – Every new rule must have a “universal include” row, and then followed by “excludes”. Avoid using “Include” rules in the details. Work on the principle of management by exception. Make a universal include rule, and then enter excludes as exceptions. A universal include will have a range of 0000’s to zzzz’s for each segment. Exclude rows would then exclude low to high ranges across 2 segments. There is no need to fill in 000’s to zzz’ across each segment when writing your exclude rows. Not doing so will make it easier to read the rules.
6 – Make error messaging clear and concise so that users understand the nature of the errors.
7 – Run the cross validations rule violation report after keying in your cross validation rules (in environments that have established code combinations). This report will tell you all existing code combinations that would be invalid based on your new rules. You may determine that the rule itself is invalid and needs correction.
8 – Monitor and adjust rules as needed. Reclassify invalid transactions, then disable bad code combinations. Write new rules to prevent similar transactions from occurring in the future.
Note: Once a code combination is created, a cross validation rule will no longer prevent it from being used in the future. The code combination itself must be disabled after the amounts are reclassified.
Lastly, the definition of and maintenance of cross validation rules should be limited to very few users. Always have a back up person in case someone is on vacation, especially at month end.
Hi Matt,
In our business we are having discussions on the “From” value ranges, for both the include and exclude rows. If the segments are char format…is the best practice, or requirement, to use a single “0” or complete the segment value with all “000000”, if the segment is 6 characters in length? Also, can this be found in Oracle’s metalink?
thanks in advance
Quick answser is the include should be all 0’s to all Z’s (as in the screenshot attached within the blog).
The excludes only need to be filled in on the relevant segments (that saves a lot of time), and yes, I recommend you use all 0’s where applicable. See the “exclude” row in the screenshot attached within the blog.
If you search Data Loader on Google, you will find my cross validation rule data loader I wrote 18 years ago. It’s still good. You can download it for free, of course, along with the data loader tool (if you don’t already have it). It will save you tons of time. If you can’t find it, let me know and I’ll send you the link.
Regards,
Matt
We use production environment for years and now we want to set GL cross validation rule.
But after setting the cross validation rule, there is no error message if user enter excluded COA combination.
Could you tell the reason and many thanks
One reason may be is that the code combination already exists. Cross Validatoin rules only prevent the creation of new combinations. If it already exists, it will not prevent them from being used.
I recommend you run the report called “Cross-Validation Rule Violation Report” with disable turned OFF .. then you can determine which accounts should be disabled.
If this is not the case, then there could be something wrong with your exclude…
Normally, you should only exclude 2 segments at a time within a rule.. anymore than that could cause confusion and unexepcted results.
Good luck. Please come back and let me know if either of these issues exists.
Thank you
I would complete the 0’s and the Z’s in both ends of the range. (low to high — 00000 – ZZZZZ)
Hi Matt, are you able to help me understand what order the the script runs through the “exclude” lines? Does it first go from segment 1 low to segment 1 high, then segment 2 low to segment 2 high and so on, or does it look at all the segments together on the “from” first, then all the segments on the “to” section. I think how the script reads the rule would be very helpful for me to understand how to set the rule up in the first place, as for a string with up to 7 segments, the concept of multiple permutations and combinations of the rules is clouding my understanding of this. Would much appreciate your insight.
Yours truly, a non-management information systems trained accountant who is now responsible for validation rule set up.
I wouldn’t try to think of it as what order it reads the rules. Each rule is independent of the others. If you have two segments where ranges of those segments need not be allowed, you simply put in your exclude rule.. From 001-060, for first segment and 8850-9900 for second segment. This simply means that any value from range 001 and 060 is incompatible with values 8850 through 9900 in the other segment. You could have multiple permeatations of this same rule.. 001-060 with 9950-9989… this would exclude the next range, but would allow values between 9901 and 9949, if you were to combine these two simple examples.
Hi Matt, Can we download CRV from an environment and upload it on another environment
Oracle has some tools to move FND type objects like value sets and FSG Reports. I don’t think that one exists for CVR rules, but I do believe there are third party tools available to do this.