Objective:

Post doing the conversion in Fusion HCM, it is important to reconcile the match between the conversion records and loaded records. In order to match the records, you have to build a BIP report to get the details from the system.

Note: Source System Owner may differ with your conversion. Please align the correct source system owner in your query.

 

Query:

SELECT DISTINCT source_system_owner,
TO_CHAR(PG.EFFECTIVE_START_DATE,’YYYY/MM/DD’) EFFECTIVE_START_DATE
,TO_CHAR(PG.EFFECTIVE_END_DATE,’YYYY/MM/DD’)EFFECTIVE_END_DATE
,PG.GRADE_CODE
,PGF.NAME GRADE_NAME
,PG.ACTIVE_STATUS
,PG.GRADE_ID
,( SELECT pldtl.name
FROM PER_LEGISLATIVE_DATA_GROUPS pldg
,PER_LEGISLATIVE_DATA_GROUPS_tl pldtl
WHERE 1=1
AND pldg.LEGISLATIVE_DATA_GROUP_ID=pldtl.LEGISLATIVE_DATA_GROUP_ID
AND pldtl.LANGUAGE = USERENV(‘LANG’)
AND PRF.LEGISLATIVE_DATA_GROUP_ID=pldtl.LEGISLATIVE_DATA_GROUP_ID
) Legislative_data_group
,Pgsf.name grade_step_name
— ,prv.name grade_rate_name
–,pldtl.name Legislative
,prf.rate_type
,prf.CURRENCY_CODE
,prf.RATE_FREQUENCY
,prf.ANNUALIZATION_FACTOR
,prf.ACTIVE_STATUS status
,prv.minimum
,prv.maximum
,prv.mid_value
,prft.name Grade_rate_name
FROM PER_GRADES_F PG
,PER_GRADES_F_tl PGF
,PER_GRADE_STEPS_F PGS
,PER_GRADE_STEPS_F_TL PGSF
,PER_RATE_VALUES_F prv
,PER_RATES_F_TL PRFT
,PER_RATES_F prf
,hrc_integration_key_map hik
WHERE 1=1
AND PG.GRADE_ID=PGF.GRADE_ID
AND PG.GRADE_ID=PGS.GRADE_ID
AND PGS.GRADE_STEP_ID=PGSF.GRADE_STEP_ID(+)
AND PG.GRADE_ID=PRV.RATE_OBJECT_ID
AND prv.rate_id=prft.rate_id
AND prft.rate_id=prf.rate_id
AND TRUNC(SYSDATE) BETWEEN PGF.EFFECTIVE_START_DATE AND PGF.EFFECTIVE_END_DATE
–AND LANGUAGE = ‘US’
AND TRUNC(SYSDATE) BETWEEN PG.EFFECTIVE_START_DATE AND PG.EFFECTIVE_END_DATE
and hik.object_name =’Grade’
and hik.source_system_owner =’VISION
AND hik.surrogate_id=pg.grade_id

Recent Posts

Start typing and press Enter to search