Query to fetch Absence Details Day Wise

 

SELECT papf.person_number,

       ppnf.full_name        emp_name,

       pd.name               dept_name,

       hl.town_or_city city,

       ppn_lm.full_name      lmname,

       abstype.base_name,

       abtfl.name            leave_name,

       apape.start_date      absence_start_date,

       apape.end_date        absence_end_date,

       apape.scheduled_units duration

FROM   per_all_people_f papf,

       per_all_assignments_m paam,

       hr_locations hl,

       per_assignment_supervisors_f pasf_lm,

       per_departments pd,

       per_person_names_f ppn_lm,

       per_person_names_f ppnf,

       hr_organization_units hou,

       anc_per_abs_entries absentry,

       anc_absence_types_f abstype,

       anc_absence_types_f_tl abtfl,

       anc_per_abs_plan_entries apape

WHERE  1 = 1

       AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND

                                  papf.effective_end_date

       -- Assignment

       AND papf.person_id = paam.person_id

       AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND

                                  paam.effective_end_date

       AND paam.assignment_status_type IN ( 'ACTIVE' ) -- Active Assignment

       AND paam.assignment_type IN ( 'E' ) -- Type Employee

       AND paam.primary_flag = 'Y' -- primary assignment

       -- location

       AND paam.location_id = hl.location_id

       AND TRUNC(SYSDATE) BETWEEN hl.effective_start_date AND

                                  hl.effective_end_date

       --LM

       AND paam.assignment_id = pasf_lm.assignment_id(+)

       AND pasf_lm.manager_type(+) = ( 'LINE_MANAGER' )

       AND TRUNC(SYSDATE) BETWEEN pasf_lm.effective_start_date(+) AND

                                  pasf_lm.effective_end_date(+)

       --LM Name 

       AND pasf_lm.manager_id = ppn_lm.person_id(+)

       AND ppn_lm.name_type(+) = 'GLOBAL'

       AND SYSDATE BETWEEN ppn_lm.effective_start_date(+) AND

                           ppn_lm.effective_end_date(+)

       AND papf.person_id = ppnf.person_id(+)

       AND ppnf.name_type(+) = 'GLOBAL'

       AND SYSDATE BETWEEN ppnf.effective_start_date(+) AND

                           ppnf.effective_end_date(+)

       ---------------------------------------- --dept ----------------------------------------

       AND paam.organization_id = pd.organization_id(+)

       AND TRUNC(SYSDATE) BETWEEN pd.effective_start_date AND

                                  pd.effective_end_date

       AND pd.status = 'A'

       --------------------------- --bu ----------------------------------------

       AND paam.business_unit_id = hou.organization_id(+)

       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(apape.start_date) BETWEEN abstype.effective_start_date AND

                                           abstype.effective_end_date

       AND TRUNC(apape.start_date) BETWEEN

           absentry.start_date AND absentry.end_date

       AND abstype.absence_type_id = abtfl.absence_type_id

       AND abtfl.LANGUAGE = 'US'

       AND TRUNC(apape.start_date) BETWEEN

           abtfl.effective_start_date AND abtfl.effective_end_date

       AND apape.assignment_id = paam.assignment_id

       AND apape.per_absence_entry_id = absentry.per_absence_entry_id

       AND absentry.person_id = papf.person_id

       AND papf.person_number = '30061803'