Delete leave/absence using hr_person_absence_api.delete_person_absence

Step 1:

Create procedure

create or replace procedure apps.xx_absence_delete
 (
   p_absence_attendance_id number
 )
 as
l_object_version_number number;
begin

select object_version_number into l_object_version_number
  from per_absence_attendances
 where absence_attendance_id = p_absence_attendance_id;

hr_person_absence_api.delete_person_absence
(p_validate => FALSE
,p_absence_attendance_id => p_absence_attendance_id--30504
,p_object_version_number => l_object_version_number
);
exception
when others then null;
commit;
end;

Step 2:

Execute procedure

exec xx_absence_delete (30504);

Step 3:

Check deleted leave/absence

select object_version_number
  from per_absence_attendances
 where absence_attendance_id = 30504;

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;

Integration between OBIEE and Oracle E-Business Suite

Configuring OBIEE to use the Oracle E-Business Suite for authentication is straight forward and can be completed in a test environment with only a small amount of effort.  It is technically accomplished through the sharing of the E-Business Suite session cookie.
Further documentation on the specific steps to configure OBIEE to use the E-Business Suite for authentication can be found on Metalink as well as in the OBIEE documentation.  A high level summary is as follows:

1. Using the BI Admin client tool, modify the RPD file to add a connection to the E-Business Suite database.
2. Add an initialization block to the RPD file that calls the E-Business Suite API APP_SESSION.validate_icx_session and then call FND_GLOBAL to collect the variables resp_id, resp_appl_id, security_group_id, resp_name, user_id, employee_id and user_name.
3. Edit the OBIEE configuration files authenicationschema.xml and instanceconfig.xml
4. Create a menu function to launch OBIEE. You must use the SSWA OracleOasis.jsp$mode=OBIEE
5. Populate the system profile option ‘FND: Oracle Business Intelligence Suite EE base URL’ with the url for OBIEE. For example: http://theobieeserver.yourcompany.com:9704
6. Upload the modified RPD file using Enterprise Manager and bounce all OBIEE services

Query to get list of active employee having Marketing Manager Position

select distinct paa.assignment_number, ppx.first_name, ps.name
from per_all_assignments_f paa,
per_people_x ppx, per_all_positions ps
where paa.position_id = ps.position_id
and ps.name = 'Marketing Manager'               
and paa.business_group_id = 7671 -- use your business group id
and ppx.person_id = paa.person_id
and trunc(sysdate) between trunc(ppx.effective_start_date) and trunc(ppx.effective_end_date)                  
order by paa.assignment_number;

R12 User Guide

R12 User Guide

EOImpl should be declared abstract

In order to avoid the error just Copy past the below lines into your EOImpl

public void setLastUpdateLogin( oracle.jbo.domain.Number n ) {}
public void setLastUpdatedBy( oracle.jbo.domain.Number n ) {}
public void setLastUpdateDate( oracle.jbo.domain.Date n ) {}
public void setCreationDate( oracle.jbo.domain.Date n ) {}
public void setCreatedBy( oracle.jbo.domain.Number n ) {}

Query to get employee leave history Oracle EBS

select distinct
       papf.employee_number "Employee Number",
       papf.full_name "Emp. Name",
       paat.name "Leave Name",     
       paa.date_start "Start Date",
       paa.date_end "End Date"
  from per_all_people_f papf,
       per_all_assignments_f paaf,
       per_absence_attendance_types paat,
       per_absence_attendances paa,
       per_business_groups pbg
 where papf.person_id = paaf.person_id
   and paat.absence_attendance_type_id = paa.absence_attendance_id
   and paa.person_id = papf.person_id
   and pbg.business_group_id = paaf.business_group_id
order by 1 asc, date_start desc;