SELECT

PAPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER,
PAPF.FULL_NAME EMPLOYEE_NAME,
PAPF.EMAIL_ADDRESS EMAIL_ADDRESS,
(
SELECT TO_CHAR(PPS.DATE_START, 'DD-MON-YYYY')
FROM PER_PERIODS_OF_SERVICE PPS
WHERE DATE_START = (
SELECT
MAX(DATE_START)
FROM
PER_PERIODS_OF_SERVICE PPOE
WHERE
PPOE.PERSON_ID = PPS.PERSON_ID
)
AND PPS.PERSON_ID = PAPF.PERSON_ID
) HIRE_DATE,
TO_CHAR(PAPF.DATE_OF_BIRTH, 'DD-MON-YYYY') DATE_OF_BIRTH,
(
SELECT
meaning
FROM
hr_lookups
WHERE
lookup_type = 'SEX'
AND upper(lookup_code) = upper([Link])
)GENDER,
(
SELECT [Link]
FROM HR_ALL_ORGANIZATION_UNITS HAOU
WHERE HAOU.ORGANIZATION_ID =
PAAF.ORGANIZATION_ID
--AND TRIM(UPPER([Link])) =
TRIM(UPPER('DEPT'))
) DEPARTMENT,

[Link] JOB,
[Link] POSITION,
(
SELECT PAPF1.EMPLOYEE_NUMBER
FROM PER_ALL_PEOPLE_F PAPF1
WHERE PAPF1.PERSON_ID = PAAF.SUPERVISOR_ID
AND SYSDATE BETWEEN
PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE
) SUPERVISOR_NUMBER,
(
SELECT PAPF1.FULL_NAME
FROM PER_ALL_PEOPLE_F PAPF1
WHERE PAPF1.PERSON_ID = PAAF.SUPERVISOR_ID
AND SYSDATE BETWEEN
PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE
) SUPERVISOR_NAME,
[Link] GRADE,
CEIL(MONTHS_BETWEEN((:P_EFFECTIVE_DATE), PAPF.START_DATE) /
12) YEARS_OF_EXPERIENCE,
(
SELECT TRUNC(MONTHS_BETWEEN(:P_EFFECTIVE_DATE,
PPS.DATE_START) / 12)
FROM PER_PERIODS_OF_SERVICE PPS
 WHERE DATE_START = (
SELECT
MAX(DATE_START)
FROM
PER_PERIODS_OF_SERVICE PPOE
WHERE
PPOE.PERSON_ID = PPS.PERSON_ID
)
AND PPS.PERSON_ID = PAPF.PERSON_ID
) EXP_YEAR,
(
SELECT TRUNC(MOD(MONTHS_BETWEEN(:P_EFFECTIVE_DATE,
PPS.DATE_START), 12))
FROM PER_PERIODS_OF_SERVICE PPS
WHERE DATE_START = (
SELECT MAX(DATE_START)
FROM PER_PERIODS_OF_SERVICE PPOE
WHERE PPOE.PERSON_ID = PPS.PERSON_ID
)
AND PPS.PERSON_ID = PAPF.PERSON_ID
) MONT_EXP
FROM
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_ALL_POSITIONS PP,
PER_JOBS PJ,
PER_GRADES PG,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.JOB_ID = PJ.JOB_ID(+)
AND PAAF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND PAAF.POSITION_ID = PP.POSITION_ID(+)
AND PAAF.GRADE_ID = PG.GRADE_ID(+)
AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
AND PAAF.ASSIGNMENT_TYPE = 'E'
and (
SELECT PPS.DATE_START
FROM PER_PERIODS_OF_SERVICE PPS
WHERE DATE_START = (
SELECT
MAX(DATE_START)
FROM
PER_PERIODS_OF_SERVICE PPOE
WHERE
PPOE.PERSON_ID = PPS.PERSON_ID
)
AND PPS.PERSON_ID = PAPF.PERSON_ID
)>'01-JAN-2024 00:00:00'
AND PAPF.BUSINESS_GROUP_ID =
NVL(:P_BG_ID,PAPF.BUSINESS_GROUP_ID)
AND (:P_EFFECTIVE_DATE) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND (:P_EFFECTIVE_DATE) BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
ORDER BY EMPLOYEE_NUMBER;