Wednesday, April 6, 2016

Enter Absence/Leave in Oracle HRMS using API

First of All we will create procedure.

CREATE OR REPLACE procedure APPS.xx_absence_insert
(
p_person_id                  number,
p_business_group_id          number,
p_absence_attendance_type_id number,
p_abs_attendance_reason_id   number,
p_effective_date             varchar2,
p_date_start                 varchar2,
p_date_end                   varchar2,
p_absence_days               number
)
as

l_absence_hours               Number;
l_absence_attendance_id       Number;                                               
l_object_version_number       Number;                                                
l_occurance                   Number;                                                
l_dur_dys_less_warning        boolean;                                               
l_dur_hrs_less_warning        boolean;                                               
l_exceeds_pto_entit_warning   boolean;                                               
l_exceeds_run_total_warning   boolean;                                               
l_abs_overlap_warning         boolean;                                               
l_abs_day_after_warning       boolean;                                               
l_dur_overwritten_warning     boolean;
l_absence_days                Number := p_absence_days;

BEGIN

HR_PERSON_ABSENCE_API.create_person_absence                                                                 
  (
   p_validate                    => FALSE                                
  ,p_effective_date              => to_date(p_effective_date, 'DD-MON-YYYY')                                            
  ,p_person_id                   => p_person_id                                          
  ,p_business_group_id           => p_business_group_id                                        
  ,p_absence_attendance_type_id  => p_absence_attendance_type_id                                          
  ,p_abs_attendance_reason_id    => p_abs_attendance_reason_id                                                              
  ,p_date_start                  => to_date(p_date_start , 'DD-MON-YYYY')                                                                   
  ,p_date_end                    => to_date(p_date_end, 'DD-MON-YYYY')                                                            
  ,p_absence_days                => l_absence_days                                             
  ,p_absence_hours               => l_absence_hours                                                                                  
  ,p_absence_attendance_id       => l_absence_attendance_id                                               
  ,p_object_version_number       => l_object_version_number                                                 
  ,p_occurrence                  => l_occurance                                                
  ,p_dur_dys_less_warning        => l_dur_dys_less_warning                                              
  ,p_dur_hrs_less_warning        => l_dur_hrs_less_warning                                              
  ,p_exceeds_pto_entit_warning   => l_exceeds_pto_entit_warning                                               
  ,p_exceeds_run_total_warning   => l_exceeds_run_total_warning                                               
  ,p_abs_overlap_warning         => l_abs_overlap_warning                                               
  ,p_abs_day_after_warning       => l_abs_day_after_warning  
  ,p_dur_overwritten_warning     => l_dur_overwritten_warning                                              
  );
  commit; 
                                                                                          
END;

Now we will execute procedure

exec xx_absence_insert(373, 626, 51, 78, '28-APR-2002','28-APR-2002','29-APR-2002',to_number(to_date('29-APR-2002','DD-MON-YYYY')-to_date('28-APR-2002','DD-MON-YYYY'))+1);

Now we will confirm the leave entry by using select statement

select distinct papf.full_name,
       paa.date_start,
       paa.date_end,
       paa.absence_days,
       paa.creation_date
 from per_absence_attendances paa, per_all_people_f papf
where papf.person_id = paa.person_id
  and paa.business_group_id = 626
  /*and paa.person_id          = 373*/
  and paa.absence_attendance_type_id = 51
  and trunc(sysdate) = trunc(paa.creation_date)
order by paa.creation_date desc;