Query Job Position

SELECT DISTINCT EMPLOYEE_NUMBER, FULL_NAME,POSITION_ID, JOB_ID,
(SELECT PJ.NAME
FROM PER_JOBS PJ
WHERE PJ.JOB_ID = PAAF.JOB_ID) JOB_NAME,
(SELECT NVL(PKFV.SEGMENT2,'')
 FROM hr_all_positions_f HAPF,
 PER_POSITION_DEFINITIONS_KFV PKFV
 WHERE HAPF.POSITION_ID = PAAF.POSITION_ID
 AND :P_AS_ON_DATE BETWEEN  HAPF.EFFECTIVE_START_DATE AND HAPF.EFFECTIVE_END_DATE
 AND HAPF.POSITION_DEFINITION_ID = PKFV.POSITION_DEFINITION_ID
 AND ID_FLEX_NUM = 50311
 )POSITION_NAME
FROM PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF
--PER_JOBS P
where :P_AS_ON_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND :P_AS_ON_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAYROLL_ID = 82--PALACE
AND PAAF.PERSON_ID = NVL(:P_PERSON_ID, PAAF.PERSON_ID)
AND PAAF.POSITION_ID = NVL(:P_POSITION_ID, PAAF.POSITION_ID)
AND PAAF.JOB_ID = NVL(:P_JOB_ID, PAAF.JOB_ID)
ORDER BY 1