This Query will fetch the Employee Person id, Employee Number, Employee Name, Leave Start Date, Leave End Date, Leave Name, Employee Assignment Id.
Change the Query as Per your Need.
SELECT A.PERSON_ID PersonId,
B.ASSIGNMENT_ID AssignmentId,
A.EMPLOYEE_NUMBER StaffNumber,
A.FULL_NAME StaffName,
Y.NAME AbsenceName,
Y.ABSENCE_ATTENDANCE_TYPE_ID AbsenceId,
X.DATE_NOTIFICATION NotifyDate,
X.CREATION_DATE CreatedDate,
X.DATE_START LeaveStart,
X.DATE_END LeaveEnd,
X.ABSENCE_DAYS TotalDays
FROM PER_ABSENCE_ATTENDANCES X,
PER_ABSENCE_ATTENDANCE_TYPES Y,
PER_ALL_PEOPLE_F A,
PER_ALL_ASSIGNMENTS_F B
WHERE X.ABSENCE_ATTENDANCE_TYPE_ID = Y.ABSENCE_ATTENDANCE_TYPE_ID
AND X.BUSINESS_GROUP_ID = Y.BUSINESS_GROUP_ID
AND A.BUSINESS_GROUP_ID = Y.BUSINESS_GROUP_ID
AND A.PERSON_ID = X.PERSON_ID
AND A.PERSON_ID = B.PERSON_ID
AND B.BUSINESS_GROUP_ID = Y.BUSINESS_GROUP_ID
AND B.PERSON_ID = X.PERSON_ID
AND SYSDATE BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN B.EFFECTIVE_START_DATE AND B.EFFECTIVE_END_DATE
AND X.DATE_START BETWEEN '01-JAN-2016' AND '31-JAN-2017' /* Duration */
AND A.EMPLOYEE_NUMBER = 'XYZ' /* Employee Number */
AND Y.NAME = 'Unpaid Leave' /* Leave Name */
ORDER BY 6, 8;
