Reports (BIP) – Query to extract basic position details

SELECT
to_char(hapf.Effective_Start_Date,'YYYY/MM/DD') "Effective_Start_Date",
to_char(hapf.Effective_End_Date,'YYYY/MM/DD') "Effective_End_Date",
(SELECT DISTINCT BU_NAME from FUN_ALL_BUSINESS_UNITS_V fun where fun.BU_ID= BUSINESS_UNIT_ID) "BU",
(select DISTINCT hapft.NAME
from HR_ALL_POSITIONS_F_TL hapft
where hapft.position_id=hapf.position_id
AND trunc(sysdate) between trunc(hapft.effective_start_date) and trunc(hapft.effective_end_date)
AND hapft.language = USERENV('LANG')
) "Pos_Name",
hapf.POSITION_CODE "PosCode",
hapf.ACTIVE_STATUS "Active_Inactive",
(select DISTINCT horg.NAME from HR_ALL_ORGANIZATION_UNITS_F_VL horg where horg.ORGANIZATION_ID=hapf.ORGANIZATION_ID
AND trunc(sysdate) between trunc(horg.effective_start_date) and trunc(horg.effective_end_date)) "Dept",
(select pjft.name
from per_jobs_f_tl pjft
WHERE pjft.job_id = hapf.job_id
AND trunc(SYSDATE) BETWEEN pjft.effective_start_date AND pjft.effective_end_date
AND language = USERENV('LANG')) "Job",
(select pj.job_code from per_jobs pj where pj.job_id=hapf.job_id
AND trunc(sysdate) between trunc(pj.effective_start_date) and trunc(pj.effective_end_date)) "JobCode",
(select hlaf.location_code from hr_locations_all_f_vl HLAF where HLAF.location_id=hapf.location_id
AND trunc(sysdate) between trunc(HLAF.effective_start_date) and trunc(HLAF.effective_end_date)
) "Location"
FROM HR_ALL_POSITIONS_F hapf
where TRUNC(SYSDATE) between trunc(hapf.effective_start_date) and trunc(hapf.effective_end_date)