Oracle cross validation rules query
In this post , We will be discuss about Oracle cross validation rules query. We all know the We creates Cross validations rules in Oracle Chart of Account or in KFF. With the help on these rules we restrict the business users to enter the Specific GL accounts combinations in the Oracle Transactions. We can easily verify these rules from the Oracle application but here in the post , I will share the SQL query to extract the Cross Validations rules in the Oracle. Please find below the complete details of the Oracle cross validation rules query.
Oracle Cross validation rules works on the basis of Two Principals and these are Include and Exclude.
Two Important Tables of Oracle cross validation rules query
1.fnd_flex_include_rule_lines
2.fnd_flex_exclude_rule_lines
Sample of Oracle cross validation rules query
SELECT FST.ID_FLEX_STRUCTURE_NAME " Accounting Strucuture"
, R.FLEX_VALIDATION_RULE_NAME "Cross Validation Rule Name"
, L.INCLUDE_EXCLUDE_INDICATOR "Indicator Type"
, L.CONCATENATED_SEGMENTS_LOW "Accounts FROM"
, L.CONCATENATED_SEGMENTS_HIGH "Acccounts TO"
FROM FND_FLEX_VALIDATION_RULES R,
FND_FLEX_VDATION_RULES_TL TL,
FND_FLEX_VALIDATION_RULE_LINES L,
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
ORDER BY FST.ID_FLEX_STRUCTURE_NAME,R.FLEX_VALIDATION_RULE_NAME,L.CONCATENATED_SEGMENTS_LOW
, R.FLEX_VALIDATION_RULE_NAME "Cross Validation Rule Name"
, L.INCLUDE_EXCLUDE_INDICATOR "Indicator Type"
, L.CONCATENATED_SEGMENTS_LOW "Accounts FROM"
, L.CONCATENATED_SEGMENTS_HIGH "Acccounts TO"
FROM FND_FLEX_VALIDATION_RULES R,
FND_FLEX_VDATION_RULES_TL TL,
FND_FLEX_VALIDATION_RULE_LINES L,
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
ORDER BY FST.ID_FLEX_STRUCTURE_NAME,R.FLEX_VALIDATION_RULE_NAME,L.CONCATENATED_SEGMENTS_LOW
3 comments:
Brilliant blog I visit this blog it's incredibly awesome. Curiously, in this blog content formed doubtlessly and sensible. The substance of information is helpful.
Oracle Fusion HCM Online Training
thank you, it's helpfull.
What direction would you go in to have the cross validation rule functionality in a 3rd party application before allowing it to feed Oracle? In almost every scenario I've seen, 3rd party applications feed temporary/staging tables, validations are performed and the system either leaves them in those temp tables with various status code errors or loads them, after validations have been successful. I'm thinking if there was an initial validation in the external tools, it would reduce the number of errors, thereby reducing the amount of manual labor needed to fix them. If different modules, like AP and PO, have access to cross validation rules before allowing journals to be generated, it would seem that having the ability to use cross validation in a 3rd party application would help... Examples: expense reporting application, a time and attendance application, and internal application from vendors that have access to send vouchers via EDI.
Post a Comment