BIP – Query to extract Rating Model Levels in HDL Format

This is second post in a series to extract Rating Model data from Fusion environment in HDL format. The first post has a query to extract only the rating model data and second post describes the BIP query to extract rating model levels data.

Query to extract Rating Model data:

BIP – Query to extract Rating Model data in HDL Format

Query to extract Rating Model levels data:

SELECT 'METADATA|RatingLevel|RatingModelCode|RatingLevelCode|CareerStrDev|RatingDescription|DateFrom|FromPoints|MaximumRatingDistribution|MinimumRatingDistribution|NumericRating|ReviewPoints|ReviewRatingDescr|RatingShortDescr|StarRating|DateTo|ToPoints|SourceSystemId|SourceSystemOwner' DATA_LINE, 1 ORDER_NUM
  FROM DUAL
UNION
select
'MERGE'
||'|'||
'RatingLevel'
||'|'||
hrmb.rating_model_code
||'|'||
hrlv.rating_level_code
||'|'||
hrlv.career_str_dev
||'|'||
hrlv.rating_description
||'|'||
TO_CHAR(hrlv.date_from,'YYYY/MM/DD')
||'|'||
hrlv.from_points
||'|'||
hrlv.max_rating_distribution
||'|'||
hrlv.min_rating_distribution
||'|'||
hrlv.numeric_rating
||'|'||
hrlv.review_points
||'|'||
hrlv.review_rating_descr
||'|'||
hrlv.rating_short_descr
||'|'||
hrlv.star_rating
||'|'||
TO_CHAR(hrlv.date_to,'YYYY/MM/DD')
||'|'||
hrlv.to_points
||'|'||
hikm.source_system_id
||'|'||
hikm.source_system_owner DATA_LINE, 2 ORDER_NUM
FROM hrt_rating_models_b hrmb
   , hrt_rating_levels_vl hrlv
   , hrc_integration_key_map hikm
WHERE hrmb.rating_model_id = hrlv.rating_model_id
  AND hikm.surrogate_id = hrlv.rating_level_id