EBS – Core HR – Absence / Leave Creation API

By | September 9, 2012
Requirement:

There is requirement to create leave (Training/ business Type) using API. User will submit the leave from OAF Page (using self service) and upon approval leave will be created. Upon Approval, this API will be called to create the leave in oracle base tables.
Assumption:

Leave Should be among following types (Training Leave/ Business Leave)
———-API Code Starts Here———–
CREATE OR REPLACE package APPS.LSG_EXT_LEARNING_OAF_PKG as
PROCEDURE LSG_leave_api (P_REQUEST_ID IN NUMBER, P_absence_attendance_id oUT number, P_erroR_msg out varchar2);
end LSG_EXT_LEARNING_OAF_PKG;
/

CREATE OR REPLACE package BODY APPS.LSG_EXT_LEARNING_OAF_PKG
AS
PROCEDURE LSG_leave_api (P_REQUEST_ID IN NUMBER, P_absence_attendance_id oUT number, P_erroR_msg out varchar2)
IS
l_absence_days number; –in out
l_absence_hours number ;– inout
l_absence_attendance_id number ;
l_object_version_number number ;
l_occurrence 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_attendance_type_id number;
l_leave_type varchar2(100);
l_error_msg varchar2(500);
l_validation boolean:=TRUE;
l_leave_status varchar2(100);
l_attribute_category varchar2(100);

X_USER_ID NUMBER;
X_RESP_ID NUMBER;
X_APPL_ID NUMBER;
l_res_name varchar2(100);

cursor c1 is select * from
LSG_OTA_NOTRNG_HISTORIES
where REQUEST_ID = P_REQUEST_ID;

begin

SELECT APPLICATION_ID
INTO X_APPL_ID
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = ‘PER’;

for rec in c1 loop

l_validation:= TRUE;
X_RESP_ID := fnd_profile.VALUE(‘RESP_ID’);
X_USER_ID := rec.created_by;
FND_GLOBAL.APPS_INITIALIZE(X_USER_ID, X_RESP_ID, X_APPL_ID);

BEGIN

–This is coming from DFF ‘Additional Absence Details’, We have configured ‘Business Leave’ and ‘Training Leave’ .
–tables FND_DESCR_FLEX_CONTEXTS_VL contains this information.
l_leave_type := trim(rec.leave_type);
If l_leave_type = ‘BUSINESS_LEAVE’ Then
   l_leave_type := ‘BUSINESS LEAVE’;
elsif l_leave_type = ‘TRAINING_LEAVE’ Then
  l_leave_type := ‘TRAINING LEAVE’;
end if;

SELECT paat.absence_attendance_type_id
INTO l_absence_attendance_type_id
FROM per_absence_attendance_types paat
WHERE UPPER (TRIM (paat.NAME)) = l_leave_type
AND BUSINESS_GROUP_ID = rec.business_group_id;

dbms_output.put_line(‘l_absence_attendance_type_id ‘l_absence_attendance_type_id );

EXCEPTION WHEN OTHERS THEN
  l_validation:= FALSE;
  L_ERROR_MSG := ‘Absence Attendance Type ‘ ||l_leave_type’ ||Id for BG_ID ‘||rec.business_group_id||’ is   NULL’;

END;

BEGIN

select DESCRIPTIVE_FLEX_CONTEXT_CODE
into l_attribute_category
from FND_DESCR_FLEX_CONTEXTS_VL vl
where 1=1
and APPLICATION_ID =800
and ENABLED_FLAG = ‘Y’
and Upper(DESCRIPTIVE_FLEX_CONTEXT_CODE) = l_leave_type;

EXCEPTION WHEN OTHERS THEN
l_validation:= FALSE;
L_ERROR_MSG := ‘Attribute Category ‘ ||l_attribute_category’|| Not Found’;
END;

if (l_validation) then
begin
l_absence_days := rec.absence_days;
HR_PERSON_ABSENCE_API.CREATE_PERSON_ABSENCE
( p_validate => false
,p_effective_date => sysdate
,p_person_id => rec.person_id
,p_business_group_id => rec.business_group_id
,p_absence_attendance_type_id => l_absence_attendance_type_id
,p_date_start => rec.leave_start_date
,p_date_end => rec.leave_end_date
,p_absence_days => l_absence_days
,p_absence_hours => l_absence_hours
,p_attribute_category => l_attribute_category
,p_attribute1 => rec.paa_attribute1
,p_attribute2 => rec.paa_attribute2
,p_attribute3 => rec.paa_attribute3
,p_attribute4 => rec.paa_attribute4
,p_attribute5 => rec.paa_attribute5
,p_attribute6 => rec.paa_attribute6
,p_attribute7 => rec.paa_attribute7
,p_attribute8 => rec.paa_attribute8
,p_attribute9 => rec.paa_attribute9
,p_attribute10 => rec.paa_attribute10
,p_attribute11 => rec.paa_attribute11
,p_attribute12 => rec.paa_attribute12
,p_attribute13 => rec.paa_attribute13
,p_attribute14 => rec.paa_attribute14
,p_attribute15 => rec.paa_attribute15
,p_attribute16 => rec.paa_attribute16
,p_attribute17 => rec.paa_attribute17
,p_attribute18 => rec.paa_attribute18
,p_attribute19 => rec.paa_attribute19
,p_attribute20 => rec.paa_attribute20
,p_absence_attendance_id => l_absence_attendance_id
,p_object_version_number => l_object_version_number
,p_occurrence => l_occurrence
,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
);

P_absence_attendance_id:= l_absence_attendance_id;
EXCEPTION WHEN OTHERS THEN

dbms_output.put_line(‘7… ‘);

L_ERROR_MSG := SQLCODE||’ ‘||SQLERRM;
P_absence_attendance_id := -1;
P_erroR_msg := L_ERROR_MSG;

end;

else

P_absence_attendance_id := -1;
P_erroR_msg := L_ERROR_MSG;

end if;
end loop;

EXCEPTION WHEN OTHERS THEN

L_ERROR_MSG := SQLCODE

‘ ‘

SQLERRM;
end ;
end LSG_EXT_LEARNING_OAF_PKG;

/ ———-API Code Ends  Here———–   –Testing whether leave is created by API  

SELECT paat.absence_attendance_type_id
FROM per_absence_attendance_types paat
WHERE UPPER (TRIM (paat.NAME)) = ‘BUSINESS LEAVE’
AND BUSINESS_GROUP_ID =339;   — select * from per_absence_attendances
where BUSINESS_GROUP_ID = 339
and person_id = 39474
and absence_attendance_type_id = 20176
order by creation_date desc

  • Nice job bro

  • first leave will be created with approved status and then user will submit it from OAF Page. is it like that?

  • Ashraf Khan

    CAN provide multiple sql table joinning query in Core Hr to view — as well as payroll aslso on user view level query.

  • Ashraf Khan

    This query will run in toad to gather information realted passport,gosi,recruitment,housing,bonus,arrear & payroll.

  • fanni339

    Dear Ashraf

    Main Tables to get employees relevant information from tables are following

    per_all_people_f (contains employee personal profile)
    per_all_assignments_f (contains employee’s assignment related information)
    per_periods_of_service (contains service related information)
    per_extra_people_info (contains Person EIT inforamtion)

    For Payroll Relevant Tables See Below

    Pay_Element_Entries_F (Elements Associated to an employee’s assignment)
    Pay_Payroll_Action (Payrol runs in your BG)
    Pay_assignment_actions (All assignments processed in a payroll)
    Pay_Run_Results (elements processed for one assignment in a payroll)
    Pay_Run_Result_Values (Values for elements processed)

    I hope it will help and you can join them.
    Regards
    Farhan

  • Ashraf Khan

    ya thanks…if give some good level query in EIT(visa,iqma,gosi OR other ,bonus,payroll employee related.