Oracle EBS SQL Script for List Of Cross-Validation Rules

 SQL Query to display the list of Cross-Validation Rules  :- 

Cross-Validation Rules (CVR) are used to restrict the combinations of segment values that users can create for Key Flexfields, most commonly the Accounting Flexfield (Chart of Accounts).

SELECT

        FST.ID_FLEX_STRUCTURE_NAME      STRUCUTURE_NAME ,
        R.FLEX_VALIDATION_RULE_NAME     CROSS_RULE_NAME ,
        TL.DESCRIPTION                                  ,
        TL.ERROR_MESSAGE_TEXT                           ,
        L.ENABLED_FLAG                                  ,
        DECODE(L.INCLUDE_EXCLUDE_INDICATOR,
                   'I'          ,'INCLUDE',
                   'E'          ,'EXCLUDE')  INDICATOR_TYPE ,
        L.CONCATENATED_SEGMENTS_LOW          ACCOUNTS_FROM  ,
        L.CONCATENATED_SEGMENTS_HIGH         ACCCOUNTS_TO   ,
        L.LAST_UPDATED_BY ,
        L.LAST_UPDATE_DATE
FROM
        APPS.FND_FLEX_VALIDATION_RULES               R,
        APPS.FND_FLEX_VDATION_RULES_TL              TL,
        APPS.FND_FLEX_VALIDATION_RULE_LINES          L,
        APPS.FND_ID_FLEX_STRUCTURES_VL             FST
WHERE
        R.APPLICATION_ID     = TL.APPLICATION_ID
AND     FST.ID_FLEX_NUM      = R.ID_FLEX_NUM
AND     R.ID_FLEX_CODE       = L.ID_FLEX_CODE
AND     R.ID_FLEX_NUM        = L.ID_FLEX_NUM
AND     R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME
AND     R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME
AND     R.APPLICATION_ID            = 101
AND     R.ID_FLEX_CODE              = TL.ID_FLEX_CODE
AND     R.ID_FLEX_NUM               = TL.ID_FLEX_NUM
AND     R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
AND     R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
AND     R.APPLICATION_ID            = L.APPLICATION_ID
GROUP BY
        FST.ID_FLEX_STRUCTURE_NAME   ,
        R.FLEX_VALIDATION_RULE_NAME  ,
        TL.DESCRIPTION               ,
        L.INCLUDE_EXCLUDE_INDICATOR  ,
        L.CONCATENATED_SEGMENTS_LOW  ,
        L.CONCATENATED_SEGMENTS_HIGH ,
        TL.ERROR_MESSAGE_TEXT,
        L.ENABLED_FLAG,
        L.LAST_UPDATED_BY,
        L.LAST_UPDATE_DATE
ORDER BY
        FST.ID_FLEX_STRUCTURE_NAME ,
        R.FLEX_VALIDATION_RULE_NAME,L.CONCATENATED_SEGMENTS_LOW

;

Setup Navigation (R12)

• Enable Cross-Validation: Navigate to General Ledger Super User > Setup > Financials > Flexfields > Key > Segments. Query your Chart of Accounts and ensure the Cross-Validate Multiple Segments checkbox is checked.
• Define Rules: Navigate to General Ledger Super User > Setup > Financials > Flexfields > Key > Rules.
• Required Fields:

1. Rule Name & Description: Unique identifier for the rule.
2. Error Message: The text displayed to the user when a violation occurs(e.g.,"Company 01 cannot use Department 999").
3. Error Segment: The specific segment where the cursor will land after an error, helping the user correct the mistake.

Your flexfield automatically displays this error message on the message line whenever a new combination of segment values violates your cross-validation rule. You should make your error messages as specific as possible so that your users can correct any errors easily.