To delete pending leaves

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

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 1=1
 and 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 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 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;

Oracle HRMS Query to Get Latest Basic Salary Change Date

SELECT ppps.proposed_salary_n from per_pay_proposals ppps
where paaf.assignment_id = ppps.assignment_id
AND ppps.last_change_date is not null
and ppps.change_date = (SELECT  TO_CHAR(max(ppp.change_date),'DD-MON-RRRR') as change_effective_date
from per_pay_proposals ppp
where 1=1
and paaf.assignment_id = ppp.assignment_id

AND last_change_date is not null )

Oracle HRMS Query for Grade and Salary History

Oracle HRMS Query to Get Previous Grade

SELECT
pg_old.name
from apps.per_all_people_f papf,
apps.per_all_assignments_f paaf_old,
apps.per_grades pg_old,
apps.per_all_assignments_f paaf_new,
apps.per_grades pg_new
where 1=1
and papf.person_id = paaf_old.person_id
and papf.person_id = paaf_new.person_id
and paaf_old.person_id = paaf_new.person_id
and paaf_old.assignment_type = 'E'
and paaf_old.primary_flag = 'Y'
and paaf_new.assignment_type = 'E'
and paaf_new.primary_flag = 'Y'
and pg_old.grade_id = paaf_old.grade_id
and pg_new.grade_id = paaf_new.grade_id
and paaf_old.grade_id <> paaf_new.grade_id
and trunc(paaf_old.effective_end_date)+1 =trunc(paaf_new.effective_start_date)
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and  trunc(paaf_new.effective_start_date) between paaf_new.effective_start_date and paaf_new.effective_end_date
--and papf.person_id = 162
and papf.person_id = paaf.person_id
and paaf_new.effective_start_date =(select
((to_char(max(paaf_new.effective_start_date),'DD-MON-RRRR')))
from apps.per_all_people_f papf,
apps.per_all_assignments_f paaf_old,
apps.per_grades pg_old,
apps.per_all_assignments_f paaf_new,
apps.per_grades pg_new
where 1=1
and papf.person_id = paaf_old.person_id
and papf.person_id = paaf_new.person_id
and paaf_old.person_id = paaf_new.person_id
and paaf_old.assignment_type = 'E'
and paaf_old.primary_flag = 'Y'
and paaf_new.assignment_type = 'E'
and paaf_new.primary_flag = 'Y'
and pg_old.grade_id = paaf_old.grade_id
and pg_new.grade_id = paaf_new.grade_id
and paaf_old.grade_id <> paaf_new.grade_id
and trunc(paaf_old.effective_end_date)+1 =trunc(paaf_new.effective_start_date)
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and  trunc(paaf_new.effective_start_date) between paaf_new.effective_start_date and paaf_new.effective_end_date
--and papf.person_id = 162
and papf.person_id = paaf.person_id )

Terminate Employee Oracle EBS (Calling API)

Employee data could be terminated from back end using oracle HRMS API (HR_EX_EMPLOYEE_API.ACTUAL_TERMINATION_EMP).

Declare
CURSOR c_emp_cur
      IS
         SELECT ppos.period_of_service_id, ppos.object_version_number,
                papf.person_type_id, yte.termination_date end_date, yte.ID
           FROM per_all_people_f papf,
                per_periods_of_service ppos,
                ynppo_temp_employee yte
          WHERE papf.person_id = ppos.person_id
            AND SYSDATE BETWEEN papf.effective_start_date
                            AND papf.effective_end_date
            AND SYSDATE BETWEEN ppos.date_start
                            AND COALESCE (ppos.projected_termination_date,
                                          actual_termination_date,
                                          SYSDATE
                                         )
            AND yte.employee_code = papf.employee_number
            AND yte.flag = 'T'
            AND yte.status IS NULL;

      l_validate                       BOOLEAN        := FALSE;
      l_period_of_service_id           NUMBER;
      l_object_version_number          NUMBER;
      l_actual_notice_period_date      DATE;
      l_effective_date                 DATE;
      l_supervisor_warning             BOOLEAN;
      l_event_warning                  BOOLEAN;
      l_interview_warning              BOOLEAN;
      l_review_warning                 BOOLEAN;
      l_recruiter_warning              BOOLEAN;
      l_asg_future_changes_warning     BOOLEAN;
      l_f_asg_future_changes_warning   BOOLEAN;
      l_pay_proposal_warning           BOOLEAN;
      l_dod_warning                    BOOLEAN;
      l_final_process_date             DATE;
      l_org_now_no_manager_warning     BOOLEAN;
      l_entries_changed_warning        VARCHAR2 (255);
      l_f_entries_changed_warning      VARCHAR2 (255);
      l_alu_change_warning             VARCHAR2 (255);
      l_person_type_id                 NUMBER;
      l_last_std_process_date_out      DATE;
   BEGIN
      FOR c_emp_rec IN c_emp_cur
      LOOP
         l_period_of_service_id := c_emp_rec.period_of_service_id;
         l_object_version_number := c_emp_rec.object_version_number;
         l_actual_notice_period_date := c_emp_rec.end_date;
         l_effective_date := c_emp_rec.end_date;
         l_person_type_id := c_emp_rec.person_type_id;

         BEGIN
            hr_ex_employee_api.actual_termination_emp
               (p_validate                        => l_validate,
                p_effective_date                  => l_effective_date,
                p_period_of_service_id            => l_period_of_service_id,
                p_object_version_number           => l_object_version_number,
                p_actual_termination_date         => l_actual_notice_period_date,
                p_last_standard_process_date      => l_actual_notice_period_date,
                --p_person_type_id => l_person_type_id,
                --,p_leaving_reason => 'RESS'
                p_last_std_process_date_out       => l_last_std_process_date_out,
                p_supervisor_warning              => l_supervisor_warning,
                p_event_warning                   => l_event_warning,
                p_interview_warning               => l_interview_warning,
                p_review_warning                  => l_review_warning,
                p_recruiter_warning               => l_recruiter_warning,
                p_asg_future_changes_warning      => l_asg_future_changes_warning,
                p_entries_changed_warning         => l_entries_changed_warning,
                p_pay_proposal_warning            => l_pay_proposal_warning,
                p_dod_warning                     => l_dod_warning,
                p_alu_change_warning              => l_alu_change_warning
               );

            UPDATE ynppo_temp_employee
               SET status = 'Terminated'
             WHERE ID = c_emp_rec.ID;
       
         EXCEPTION
            WHEN OTHERS
            THEN
               UPDATE ynppo_temp_employee
                  SET status = 'Not Terminated'
                WHERE ID = c_emp_rec.ID;
                dbms_output.put_line(SQLERRM);
         END;
      END LOOP;
   END;



The YNPPO_TEMP_EMPLOYEE is staging table to store employee data which will be terminated using API.
Diposkan oleh Imam Tri Harsono di 22:40  
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Pinterest

Label: Human Resource API
Reaksi:
Update Employee Oracle EBS (Calling API)

Employee data could be inserted from back end using oracle HRMS API (HR_PERSON_API.UPDATE_PERSON).

Declare
CURSOR c_emp
      IS
         SELECT *
           FROM ynppo_temp_employee yte
          WHERE yte.status IS NULL AND yte.flag = 'U';

      ln_object_version_number      per_all_people_f.object_version_number%TYPE;
      lc_dt_ud_mode                 VARCHAR2 (100)                    := NULL;
      ln_assignment_id              per_all_assignments_f.assignment_id%TYPE;
      lc_employee_number            per_all_people_f.employee_number%TYPE;

      lb_correction                 BOOLEAN;
      lb_update                     BOOLEAN;
      lb_update_override            BOOLEAN;
      lb_update_change_insert       BOOLEAN;

      ld_effective_start_date       DATE;
      ld_effective_end_date         DATE;
      lc_full_name                  per_all_people_f.full_name%TYPE;
      ln_comment_id                 per_all_people_f.comment_id%TYPE;
      lb_name_combination_warning   BOOLEAN;
      lb_assign_payroll_warning     BOOLEAN;
      lb_orig_hire_warning          BOOLEAN;
      v_person_id                   NUMBER;
      v_object_version_number       NUMBER;
      v_effective_start_date        DATE;
      v_assignment_id               NUMBER;
   BEGIN
      FOR r_emp IN c_emp
      LOOP
         lc_employee_number := r_emp.employee_code;

         BEGIN
            SELECT papf.person_id, papf.object_version_number,
                   papf.effective_start_date, paaf.assignment_id
              INTO v_person_id, v_object_version_number,
                   v_effective_start_date, v_assignment_id
              FROM per_all_people_f papf, per_all_assignments_f paaf
             WHERE papf.employee_number = r_emp.employee_code
               AND TRUNC (SYSDATE) BETWEEN TRUNC (papf.effective_start_date)
                                       AND TRUNC (papf.effective_end_date)
               AND TRUNC (SYSDATE) BETWEEN TRUNC (paaf.effective_start_date)
                                       AND TRUNC (paaf.effective_end_date)
               AND papf.person_id = paaf.person_id;
         EXCEPTION
            WHEN OTHERS
            THEN
               v_person_id := 0;
               v_person_id := NULL;
         END;

         IF v_person_id = 0
         THEN
            NULL;
         ELSE
       
            -- Update Employee API
            -- ---------------------------------
            IF TRUNC (r_emp.effective_start_date) <>
                                               TRUNC (v_effective_start_date)
            THEN
               lc_dt_ud_mode := 'UPDATE';
            ELSE
               lc_dt_ud_mode := 'CORRECTION';
            END IF;

            BEGIN
               hr_person_api.update_person
                  ( -- Input Data Elements
                    -- ------------------------------
                   p_effective_date                => TRUNC (r_emp.effective_start_date),
                   p_datetrack_update_mode         => lc_dt_ud_mode,
                   p_person_id                     => v_person_id,
                   p_first_name                    => r_emp.first_name,
                   p_last_name                     => r_emp.last_name,
                   p_middle_names                  => r_emp.middle_names,
                   p_sex                           => r_emp.gender,
                   p_date_of_birth                 => r_emp.date_of_birth,
                   p_title                         => r_emp.title,
                   p_email_address                 => r_emp.email,
                   p_national_identifier           => lc_employee_number,
                   p_attribute1                    => r_emp.LEVELS,
                   p_attribute2                    => r_emp.level_desc,
                   p_attribute3                    => r_emp.position_code,
                   p_attribute4                    => r_emp.position_desc,
                   p_attribute5                    => r_emp.dept_code,
                   p_attribute6                    => r_emp.department,
                   p_attribute7                    => r_emp.sub_dept_code,
                   p_attribute8                    => r_emp.sub_depat,
                   p_attribute9                    => r_emp.plant_code,
                   p_attribute10                   => r_emp.plant,
                   p_attribute11                   => r_emp.supervise_emp_code,
                   p_attribute12                   => r_emp.old_employee_code,
                   -- Output Data Elements
                   -- ----------------------------------
                   p_employee_number               => lc_employee_number,
                   p_object_version_number         => v_object_version_number,
                   p_effective_start_date          => ld_effective_start_date,
                   p_effective_end_date            => ld_effective_end_date,
                   p_full_name                     => lc_full_name,
                   p_comment_id                    => ln_comment_id,
                   p_name_combination_warning      => lb_name_combination_warning,
                   p_assign_payroll_warning        => lb_assign_payroll_warning,
                   p_orig_hire_warning             => lb_orig_hire_warning
                  );

               UPDATE ynppo_temp_employee
                  SET status = 'Updated'
                WHERE ID = r_emp.ID;
            EXCEPTION
               WHEN OTHERS
               THEN
                  --ROLLBACK;
                  UPDATE ynppo_temp_employee
                     SET status = 'Not Updated'
                   WHERE ID = r_emp.ID;

                  DBMS_OUTPUT.put_line (SQLERRM);
            END;
         -- COMMIT;
         END IF;
      END LOOP;
   END;

Query to get Absence Detail Employee wise. Approved Absence details.

SELECT papf.EMPLOYEE_NUMBER
,papf.FULL_NAME
,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 papf.EMPLOYEE_NUMBER=nvl(:p_employee_number,papf.EMPLOYEE_NUMBER)
order by papf.EMPLOYEE_NUMBER,paa.date_start