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:

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

BIP – Query to extract Rating Model data in HDL Format

You can use below query to extract Rating Model data from fusion environment for updates.

SELECT 'METADATA|RatingModel|RatingModelCode|DateFrom|DateTo|RatingName|RatingDescription|SourceSystemId|SourceSystemOwner' DATA_LINE, 1 ORDER_NUM
  FROM DUAL
UNION
select
'MERGE'
||'|'||
'RatingModel'
||'|'||
hrmb.rating_model_code
||'|'||
TO_CHAR(hrmb.date_from,'YYYY/MM/DD')
||'|'||
TO_CHAR(hrmb.date_to,'YYYY/MM/DD')
||'|'||
rtl.rating_name
||'|'||
rtl.rating_description
||'|'||
hikm.source_system_id
||'|'||
hikm.source_system_owner  DATA_LINE, 2 ORDER_NUM
FROM hrt_rating_models_b hrmb,
     hrt_rating_models_tl rtl,
     hrc_integration_key_map hikm
WHERE hrmb.rating_model_id = rtl.rating_model_id
  AND hikm.surrogate_id = hrmb.rating_model_id
  AND rtl.language = 'US'
ORDER BY ORDER_NUM

Save the data as RatingModel.dat before uploading to Fusion. You can make use of this query to extract data for reference or use it as a method to extract data from one instance and upload it to a different instance.

Check below post for sample RatingModel HDL file:

https://fusionhcmconsulting.com/2022/03/hdl-sample-file-to-upload-rating-model/