select hapf.position_code
,hpb.profile_code
,hpt.description
,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'
