Employees Total Leave Taken in a Particular Period ( Approved / Not Approved)

Employees Total Leave Taken including Approved and Not Approved Leave

Query to Know Employees total Leave Taken in a Particular Period (Both Approved and Not Approved)

SELECT PAPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER,

       PAPF.FULL_NAME EMPLOYEE_NAME,

       PAAF.ASSIGNMENT_ID ASSIGNMENT_ID,

       PAAF.PAYROLL_ID,

       PAAT.NAME "Leave Type",

       PAA.DATE_START ABSENCE_START_DATE,

       PAA.DATE_END ABSENCE_END_DATE,

       TO_CHAR ('Approved') Status

  FROM PER_ALL_PEOPLE_F PAPF,

       PER_ALL_ASSIGNMENTS_F PAAF,

       PER_ABSENCE_ATTENDANCES PAA,

       PER_ABSENCE_ATTENDANCE_TYPES PAAT

 WHERE     PAPF.PERSON_ID = PAAF.PERSON_ID

       AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'

       AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE

                               AND PAPF.EFFECTIVE_END_DATE

       AND TRUNC (SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE

                               AND PAAF.EFFECTIVE_END_DATE

       AND PAAF.PRIMARY_FLAG = 'Y'

       AND PAAF.ASSIGNMENT_TYPE = 'E'

       AND PAAF.ASSIGNMENT_STATUS_TYPE_ID IN (1, 2)

       AND PAPF.PERSON_ID = PAA.PERSON_ID

       AND PAA.ABSENCE_ATTENDANCE_TYPE_ID = PAAT.ABSENCE_ATTENDANCE_TYPE_ID

       AND PAAT.NAME = 'Unpaid / Unauthorized Leave'

       AND (   (    PAA.DATE_START <= TO_DATE ('21-JAN-2015')

                AND PAA.DATE_END >= TO_DATE ('19-FEB-2015'))

            OR (   PAA.DATE_START BETWEEN TO_DATE ('21-JAN-2015')

                                      AND TO_DATE ('19-FEB-2015')

                OR PAA.DATE_END BETWEEN TO_DATE ('21-JAN-2015')

                                    AND TO_DATE ('19-FEB-2015')))

UNION

SELECT PAPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER,

       PAPF.FULL_NAME EMPLOYEE_NAME,

       PAAF.ASSIGNMENT_ID,

       PAAF.PAYROLL_ID,

       apps.ameutil_pkg.get_trn_attr_value_f (trn.transaction_id,

                                                        'AttributeCategory')

          "Leave Type",

       TO_DATE (

          apps.ameutil_pkg.get_trn_attr_value_f (

             trn.transaction_id,

             'DateStart'),

          'RRRR-MM-DD')

          ABSENCE_START_DATE,

       TO_DATE (

          apps.ameutil_pkg.get_trn_attr_value_f (

             trn.transaction_id,

             'DateEnd'),

          'RRRR-MM-DD')

          ABSENCE_END_DATE,

       TO_CHAR ('Not Approved') Status

  FROM hr_api_transactions trn,

       PER_ALL_PEOPLE_F PAPF,

       PER_ALL_ASSIGNMENTS_F PAAF

 WHERE     transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'

       AND selected_person_id = PAPF.PERSON_ID

       AND trn.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID

       AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'

       AND PAAF.PRIMARY_FLAG = 'Y'

       AND PAAF.ASSIGNMENT_TYPE = 'E'

       AND PAAF.ASSIGNMENT_STATUS_TYPE_ID IN (1, 2)

       AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE

                               AND PAPF.EFFECTIVE_END_DATE

       AND TRUNC (SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE

                               AND PAAF.EFFECTIVE_END_DATE

       AND status = 'Y'

       AND transaction_identifier = 'ABSENCES'

       AND apps.ameutil_pkg.get_trn_attr_value_f (

              trn.transaction_id,

              'AttributeCategory') = 'Unpaid / Unauthorized Leave'

       AND (   (    TO_DATE (

                       apps.ameutil_pkg.get_trn_attr_value_f (

                          trn.transaction_id,

                          'DateStart'),

                       'RRRR-MM-DD') <= TO_DATE ('21-JAN-2015')

                AND TO_DATE (

                       apps.ameutil_pkg.get_trn_attr_value_f (

                          trn.transaction_id,

                          'DateEnd'),

                       'RRRR-MM-DD') >= TO_DATE ('19-FEB-2015'))

            OR (   TO_DATE (

                      apps.ameutil_pkg.get_trn_attr_value_f (

                         trn.transaction_id,

                         'DateStart'),

                      'RRRR-MM-DD') BETWEEN TO_DATE ('21-JAN-2015')

                                        AND TO_DATE ('19-FEB-2015')

                OR TO_DATE (

                      apps.ameutil_pkg.get_trn_attr_value_f (

                         trn.transaction_id,

                         'DateEnd'),

                      'RRRR-MM-DD') BETWEEN TO_DATE ('21-JAN-2015')

                                        AND TO_DATE ('19-FEB-2015')));