The below sample query can be used to extract the employee absence history in Oracle EBS.
Query: Absence history for an person id, if person id is not given on the parameter all the employees absence history will be extracted
SELECT EMPLOYEE_NUMBER,
PAPF.FULL_NAME EMPLOYEE_FULL_NAME,
PAA.PERSON_ID,
PAAT.NAME ABSENCE_TYPE,
TO_CHAR (PAA.DATE_START, ‘DD-MON-RRRR’) ABSENCE_START,
TO_CHAR (PAA.DATE_END, ‘DD-MON-RRRR’) ABSENCE_END,
ABSENCE_DAYS,
TO_CHAR (PAA.CREATION_DATE, ‘DD-MON-RRRR’) ABSENCE_CREATED
FROM PER_ABSENCE_ATTENDANCES PAA,
PER_ABSENCE_ATTENDANCE_TYPES PAAT,
PER_ALL_PEOPLE_F PAPF
WHERE 1 = 1
AND PAA.ABSENCE_ATTENDANCE_TYPE_ID = PAAT.ABSENCE_ATTENDANCE_TYPE_ID
AND PAA.DATE_START BETWEEN PAAT.DATE_EFFECTIVE
AND NVL (PAAT.DATE_END, ’31-DEC-4712′)
AND PAA.PERSON_ID = PAPF.PERSON_ID
AND PAA.DATE_START BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND PAPF.PERSON_ID = NVL(:P_PERSON_ID,PAPF.PERSON_ID)
ORDER BY EMPLOYEE_NUMBER, PAA.CREATION_DATE, PAA.DATE_START
;
