Thursday, November 17, 2011

DATALOAD (DLD) FORMAT SQL EXTRACT OF CVR CROSS VALIDATION RULES

/* DATALOAD (DLD) FORMAT SQL EXTRACT OF CVR CROSS VALIDATION RULES 
Extracts CVR's from one environment in a dataload format ready to load into the next environment using dataload classic.
Note : The segments low&high substings will need updating to match your specific chart of accounts definitions
(Tested on 11.5.9 Mar 2007 )*/
select fst.id_flex_structure_name "Books"
,    l.include_exclude_indicator"Inc?"
,    r.flex_validation_rule_name"Name"
, (case when l.include_exclude_indicator = 'I'then r.flex_validation_rule_name else NULL end )"Name"
, (case when l.include_exclude_indicator = 'I' then 'TAB' else NULL end )"Z"
,   (case when l.include_exclude_indicator = 'I' then   tl.description else NULL end )"Description"
,   (case when l.include_exclude_indicator = 'I' then      'TAB' else NULL end )"Z"
,   (case when l.include_exclude_indicator = 'I' then      'TAB' else NULL end )"Z"
,   (case when l.include_exclude_indicator = 'I' then   tl.ERROR_MESSAGE_TEXT else NULL end )"Message"
,   (case when l.include_exclude_indicator = 'I' then      'TAB' else NULL end )"Z"
,   (case when l.include_exclude_indicator = 'I' then   decode(r.ERROR_SEGMENT_COLUMN_NAME,'SEGMENT1','Entity','SEGMENT2','Office','SEGMENT3','Group','SEGMENT4','Account','SEGMENT5','Local','SEGMENT6','Partner','SEGMENT7','Project','SEGMENT8','Year','XXXXX') else NULL end )"Segment"
,   (case when l.include_exclude_indicator = 'I' then      'TAB' else NULL end )"Z"
,   (case when l.include_exclude_indicator = 'I' then      'TAB' else NULL end )"Z"
,   (case when l.include_exclude_indicator = 'I' then      'TAB' else NULL end )"Z"
,   (case when l.include_exclude_indicator = 'I' then      'TAB' else NULL end )"Z"
,    substr(l.concatenated_segments_low,0,2)"1l"
,    substr(l.concatenated_segments_high,0,2)"1H"
,    substr(l.concatenated_segments_low,4,2)"2l"
,    substr(l.concatenated_segments_high,4,2)"2H"
,    substr(l.concatenated_segments_low,7,4)"3l"
,    substr(l.concatenated_segments_high,7,4)"3H"
,    substr(l.concatenated_segments_low,12,5)"4l"
,    substr(l.concatenated_segments_high,12,5)"4H"
,    substr(l.concatenated_segments_low,18,6)"5l"
,    substr(l.concatenated_segments_high,18,6)"5H"
,    substr(l.concatenated_segments_low,25,4)"6l"
,    substr(l.concatenated_segments_high,25,4)"6H"
,    substr(l.concatenated_segments_low,30,5)"7l"
,    substr(l.concatenated_segments_high,30,5)"7H"
,    substr(l.concatenated_segments_low,36,4)"8l"
,    substr(l.concatenated_segments_high,36,4)"8H"
,    substr(l.concatenated_segments_low,41,4)"9l"
,    substr(l.concatenated_segments_high,41,4)"9H"
,    substr(l.concatenated_segments_low,46,4)"10l"
,    substr(l.concatenated_segments_high,46,4)"10H"
,    'ENT'
,    '*SL3'
,    '*DN'
,    'TAB'
,    '*SL1'   
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 = 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
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 = 'GL#'
--AND    substr(fst.id_flex_structure_name,1,2) in ('BE','LU')
--AND    r.ERROR_SEGMENT_COLUMN_NAME = 'SEGMENT5'
--AND    tl.ERROR_MESSAGE_TEXT like '%Local%'
--AND    substr(l.concatenated_segments_low,1,2)='ZZ'
ORDER BY 1,3,2 desc, 12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect