HRMS: Absence Management (Leave) Queries

To get approved leaves:

         SELECT pat.NAME absence_type, paa.date_start leave_start_date,
                paa.date_end leave_end_date, paa.absence_days, paa.abs_information_category,
                paa.abs_information2, paa.abs_information3,paa.abs_information4
           FROM per_absence_attendances paa,
                per_absence_attendance_types pat,
                per_all_people_f papf
          WHERE paa.absence_attendance_type_id =
                                                pat.absence_attendance_type_id
            AND papf.person_id = paa.person_id
            AND TRUNC (paa.date_start) BETWEEN TRUNC
                                                    (papf.effective_start_date)
                                           AND TRUNC (papf.effective_end_date)
            AND TRUNC (p_date) BETWEEN TRUNC (paa.date_start)
                                   AND TRUNC (NVL (paa.date_end,
                                                   paa.date_start
                                                  )
                                             )
            AND papf.person_id = :p_person_id;

To get pending leaves:

      SELECT hat.transaction_id, hats.transaction_step_id

        FROM hr_api_transactions hat, hr_api_transaction_steps hats

       WHERE hat.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'

         AND hat.transaction_group = 'ABSENCE_MGMT'

         AND hat.transaction_identifier = 'ABSENCES'

         AND hat.transaction_ref_id IS NOT NULL

         AND hat.status = 'Y'

         AND hat.transaction_id = hats.transaction_id

         AND hat.CREATOR_PERSON_ID = :p_person_id; 

To delete pending leaves:

As far as i know there is no API to delete pending leaves.

If you are aware of any API used to delete pending leaves, please let me know.

Solution1:

This is my own way of deleting the leaves

CREATE OR REPLACE PROCEDURE xx_delete_pending_leaves

AS

   CURSOR c1

   IS

      SELECT hat.transaction_id, hats.transaction_step_id

        FROM hr_api_transactions hat, hr_api_transaction_steps hats

       WHERE hat.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'

         AND hat.transaction_group = 'ABSENCE_MGMT'

         AND hat.transaction_identifier = 'ABSENCES'

         AND hat.transaction_ref_id IS NOT NULL

         AND hat.status = 'Y'

         AND hat.transaction_id = hats.transaction_id;

--For a particular employee/pass the person_id values as a cursor parameter

         --AND hat.CREATOR_PERSON_ID = 11923; 

   --AND hats.creator_person_id = p_person_id;  -- Add date conditon later and person ID if required

   l_transaction_id        NUMBER;

   l_transaction_step_id   NUMBER;

   l_error                 VARCHAR2 (300);

BEGIN

   OPEN c1;

   LOOP

      FETCH c1

       INTO l_transaction_id, l_transaction_step_id;

      EXIT WHEN c1%NOTFOUND;

      IF l_transaction_step_id IS NOT NULL

      THEN

         DELETE FROM hr_api_transaction_values

               WHERE transaction_step_id = l_transaction_step_id;

         DBMS_OUTPUT.put_line

                     (   'Deleted Transaction Value of transaction step id: '

                      || l_transaction_step_id

                     );

      END IF;

      IF l_transaction_id IS NOT NULL

      THEN

         DELETE FROM hr_api_transaction_steps

               WHERE transaction_id = l_transaction_id;

         DBMS_OUTPUT.put_line

                           (   'Deleted Transaction step of transaction id: '

                            || l_transaction_id

                           );

         DELETE FROM hr_api_transactions

               WHERE transaction_id = l_transaction_id;

         DBMS_OUTPUT.put_line ('Deleted Transaction ID: ' || l_transaction_id);

      END IF;

   END LOOP;

   CLOSE c1;

   COMMIT;

EXCEPTION

   WHEN OTHERS

   THEN

      l_error := SUBSTR (SQLERRM, 1, 200);

      DBMS_OUTPUT.put_line (   'Other Error in xx_delete_pending_leaves- '

                            || l_error

                           );

--p_out := 'Other Error in xx_delete_pending_leaves- '||l_error; -- Removed OUT parameter

-- Write FND_LOG for concurrent program log

END xx_delete_pending_leaves;

Solution 2:

We can enable/disable the update/delete icon in Absence Management - HR self service responsibility. How ?

Solution: Get query from jsp page (OAF) (See Standard Query below) and change the decode value of cancel_icon, Confirm_icon and update_icon values by passing 'HrCancelEnabled/HrCancelDisabled' OR 'HrUpdateEnabled/HrUpdateDisabled' values and replace with the custom query.

Tables Used in Standard Query are:

 

       per_absence_attendances,

       per_absence_attendance_types,

       per_abs_attendance_types_tl,

       hr_lookups,

       hr_api_transactions, 

       hr_api_transaction_steps

Standard Query:

SELECT  NVL(paa.date_start,paa.date_projected_start) start_date

        ,NVL(paa.date_end,paa.date_projected_end) end_date

       ,paattl.name absence_type

       ,paat.absence_attendance_type_id absence_attendance_type_id

       ,fcl.meaning  absence_category

       ,paat.absence_category  absence_category_code

       ,paa.absence_hours

       ,(SELECT meaning from hr_lookups where 'A' = lookup_code(+) and 'LEAVE_STATUS' = lookup_type(+))

       approval_status

       , 'A' approval_status_code

       ,decode(paa.date_start,null,

                                   (SELECT meaning from fnd_lookup_values

                                    where lookup_type ='ABSENCE_STATUS' and lookup_code ='PLANNED'

 and language = userenv('LANG')),

                                   (SELECT meaning from fnd_lookup_values

                                    where lookup_type ='ABSENCE_STATUS' and lookup_code ='CONFIRMED'

and language = userenv('LANG')))

        absence_status

       , decode(paa.date_start,null,'PLANNED','CONFIRMED') absence_status_code

       ,(nvl((SELECT 'Y'

     from fnd_attached_documents

     where entity_name='PER_ABSENCE_ATTENDANCES'

     and pk1_value = to_char(paa.absence_attendance_id) and rownum = 1),'N')) supporting_documents

       ,decode (

        paa.date_start,null,

        decode(paa.date_projected_start, null, 'HrCancelDisabled', 'HrCancelEnabled'),

       

        decode(

               sign(trunc(paa.date_start)-trunc(sysdate)),1,'HrCancelEnabled',

                                                                              decode(paa.date_end,null,'HrCancelEnabled','HrCancelDisabled')

              )

       )

        cancel_icon

       ,decode(paa.date_end,NULL,

          decode(paa.date_start,Null, 'HrConfirmEnabled','HrConfirmDisabled'), 'HrConfirmDisabled' )

         confirm_icon       

       ,decode ( paa.date_start,null,

          'HrUpdateEnabled',

           decode(sign(nvl(paa.date_end,sysdate+1)-sysdate),1,'HrUpdateEnabled','HrUpdateDisabled'))

         update_icon                

       ,NULL

         details_icon

       ,paa.absence_attendance_id

       ,null transaction_id

      ,to_char(paa.absence_attendance_id) supportingDocKey

      ,paa.absence_days

FROM per_absence_attendances paa

     ,per_absence_attendance_types paat

     ,per_abs_attendance_types_tl paattl

     ,hr_lookups fcl

WHERE paa.person_id = :1

      and paa.business_group_id+0 = :2

      and paa.absence_attendance_type_id = paat.absence_attendance_type_id

      and paat.absence_attendance_type_id = paattl.absence_attendance_type_id

      and paattl.language = userenv('LANG')

      and fcl.lookup_type(+) = 'ABSENCE_CATEGORY'

      and paat.absence_category = fcl.lookup_code(+)

      and ((hr_api.return_legislation_code(paat.business_group_id) = 'GB'

         and nvl(paat.absence_category,'#')  not in

('M','GB_PAT_ADO','GB_PAT_BIRTH','GB_ADO'))

         or

        (hr_api.return_legislation_code(paat.business_group_id) <> 'GB'

         and nvl(paat.absence_category,'#') not in

('GB_PAT_ADO','GB_PAT_BIRTH','GB_ADO')))

      and not exists

      ( select 'e'

        from hr_api_transactions t

         WHERE

        t.selected_person_id = paa.person_id

        and t.CREATOR_PERSON_ID  = nvl(:3,t.CREATOR_PERSON_ID)

        and t.transaction_ref_table='PER_ABSENCE_ATTENDANCES'

         and t.transaction_ref_id = paa.absence_attendance_id

         and not(hr_absutil_ss.getabsencetype(t.transaction_id,    NULL) IS NULL

         and t.status = 'W')

         and t.status not in ('D','E')

      )

union all

select hr_absutil_ss.getStartDate(hat.transaction_id,null) start_date

       ,hr_absutil_ss.getEndDate(hat.transaction_id,null) end_date

       ,hr_absutil_ss.getAbsenceType(hat.transaction_id,null) absence_type

       , to_number(hats.Information5) absence_attendance_type_id

      ,hr_absutil_ss.getAbsenceCategory(hat.transaction_id,null)  absence_category

      , hats.Information6 absence_category_code

       ,hr_absutil_ss.getAbsenceHoursDuration(hat.transaction_id,null)  absence_hours

       ,hr_absutil_ss.getApprovalStatus(hat.transaction_id,null) approval_status

       ,hr_absutil_ss.getApprovalStatusCode(hat.transaction_id,null) approval_status_code

       ,hr_absutil_ss.getAbsenceStatus(hat.transaction_id,null) absence_status

       , hats.Information9 absence_status_code

       ,hr_absutil_ss.hasSupportingDocuments(hat.transaction_id,hat.TRANSACTION_REF_ID) supporting_documents

       ,hr_absutil_ss.isCancelAllowed(hat.transaction_id,null,hat.status) cancel_icon

       ,hr_absutil_ss.isConfirmAllowed(hat.transaction_id,null) confirm_icon

     ,hr_absutil_ss.isUpdateAllowed(hat.transaction_id,null,hat.status) update_icon

       ,null  details_icon

      ,hat.TRANSACTION_REF_ID absence_attendance_id

       ,hat.transaction_id transaction_id        

       ,to_char(hat.TRANSACTION_REF_ID||'_'||hat.transaction_id) supportingDocKey

      ,hr_absutil_ss.getAbsenceDaysDuration(hat.transaction_id,null)  absence_days

              

from hr_api_transactions hat

     ,hr_api_transaction_steps hats

where hat.TRANSACTION_REF_TABLE='PER_ABSENCE_ATTENDANCES'

and hat.TRANSACTION_GROUP='ABSENCE_MGMT'

and hat.TRANSACTION_IDENTIFIER='ABSENCES'

and hat.TRANSACTION_REF_ID is not null

and hat.SELECTED_PERSON_ID =:4

and hat.CREATOR_PERSON_ID  = nvl(:5,hat.CREATOR_PERSON_ID)

and hat.transaction_id=hats.transaction_id(+)

and hat.status not in ('D','E')

and not (hr_absutil_ss.getabsencetype(hat.transaction_id,null) is null and hat.status='W')

To be contd...

Thanks,

Rajesh