Query to fetch Absence Details of Employee

Query to fetch Absence Details of Employee

WITH cal
     AS (SELECT/*+parallel(4) */ ( TRUNC(TRUNC(:p_Start_Date)) + LEVEL - 1 ) AS
                                 DAY
         FROM   dual
         CONNECT BY LEVEL <= ( TRUNC(TRUNC(:p_End_Date)) -
                               TRUNC(TRUNC(:p_Start_Date)) +
                               1 ))
SELECT papf.person_number,
       abstype.base_name,
       absentry.start_date,
       cal.day,
       abtfl.name leave_name
FROM   anc_per_abs_entries absentry,
       anc_absence_types_f abstype,
       anc_absence_types_f_tl abtfl,
       cal,
       per_all_people_f papf
WHERE  1 = 1
       AND abstype.absence_type_id = absentry.absence_type_id
       AND absentry.absence_status_cd NOT IN ( 'ORA_WITHDRAWN' )
       AND absentry.approval_status_cd = 'APPROVED'
       AND TRUNC(SYSDATE) BETWEEN abstype.effective_start_date AND
                                  abstype.effective_end_date
       AND TO_DATE(TO_CHAR(cal.day, 'dd-MM-yyyy'), 'dd-MM-yyyy') BETWEEN
           absentry.start_date AND absentry.end_date
       AND absentry.start_date = ( CASE
                                     WHEN
           absentry.start_date <> absentry.end_date
           AND absentry.duration = 1 THEN
                                     TO_DATE(TO_CHAR(cal.day, 'dd-MM-yyyy'),
                                     'dd-MM-yyyy')
                                     ELSE absentry.start_date
                                   END )
       AND abstype.absence_type_id = abtfl.absence_type_id
       AND abtfl.LANGUAGE = 'US'
       AND TRUNC(SYSDATE) BETWEEN abtfl.effective_start_date AND
                                  abtfl.effective_end_date
       AND absentry.person_id = papf.person_id
       AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND
                                  papf.effective_end_date