/* CVR Overview (Cross Validation Rules )
Written by Daniel North,  ORAFINAPPS Limited 2007
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