Thursday, November 17, 2011

CVR Overview (Cross Validation Rules )

/* CVR Overview (Cross Validation Rules )
Provides view of header level cross validation rule definitions to 
obtain an overview of rules and messages across multiple charts of accounts 
(Tested on Vision 11.5.10.2  June 2007 )*/
select   fst.id_flex_structure_name"CoA"
,    r.flex_validation_rule_name"Rule Name"
,    r.enabled_flag"Enb?"
,    r.ERROR_SEGMENT_COLUMN_NAME"Error Seg"
,    length(tl.error_message_text)"Error Length"
,    tl.ERROR_MESSAGE_TEXT"Message"
,    tl.CREATION_DATE
--select count(*), fst.id_flex_structure_name
FROM   fnd_flex_validation_rules r,
       fnd_flex_vdation_rules_tl tl,
    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 = 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.application_id = 101
--AND    substr(fst.id_flex_structure_name,1,2) in ('BE','LU','ES')  -- LIMITS RESULTS TO SPECIFIC CHARTS OF ACCOUNTS
--AND    length(tl.error_message_text) > 150   --- THIS IS USED FOR CHECK FOR MESSAGES OVER 150 CHARACTERS THAT CAN CAUSE SQL ERRORS IN I-Expenses 
ORDER BY 1,2

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect