BIP – Query to get position profile details

select hapf.position_code
,hpb.profile_code
,hpt.description profile_desc
,hpt.summary
,hpeiv.DESCRIPTION
,hpeiv.RESPONSIBILITIES
,hpeiv.QUALIFICATIONS
from HRT_PROFILE_ITEMS hpi
,HRT_PROFILES_B hpb
,HRT_PROFILES_TL hpt
,HRT_PROFILE_RELATIONS hpr
,HRT_PROFILE_EXTRA_INFO_VL hpeiv
,HR_ALL_POSITIONS_F hapf
where hpi.profile_id = hpb.profile_id
and hpb.profile_usage_code = 'M'
and hpi.profile_id = hpr.profile_id
and hpi.profile_id = hpt.profile_id
and hpi.profile_id = hpeiv.profile_id
and hapf.position_id = hpr.object_id
and trunc(sysdate) between hapf.effective_start_date and hapf.effective_end_Date
and hapf.position_code= '1099'
and hpt.language = 'US'

Query to extract only the profile data:

select hpb.profile_code
,hpt.description
,hpt.summary
,hpeiv.DESCRIPTION desc1
,hpeiv.RESPONSIBILITIES
,hpeiv.QUALIFICATIONS
,hikm.source_system_id
,hikm.source_system_owner
from HRT_PROFILES_B hpb
,HRT_PROFILES_TL hpt
,HRT_PROFILE_EXTRA_INFO_VL hpeiv
,HRC_INTEGRATION_KEY_MAP hikm
where hpeiv.profile_id = hpb.profile_id
and hpb.profile_usage_code = 'M'
and hpt.language = 'US'
and hpb.profile_code like '%TEST%'
and hikm.surrogate_id = hpeiv.PROFILE_EXTRA_INFO_ID
order by hpb.creation_date desc