Scribd
Upload
212 views45 pages

Payroll API: Absence & Batch Entry

This document discusses APIs used for payroll integration in Oracle applications. Specifically, it covers the hr_person_absence_api API which is used to insert absence data in bulk to the Oracle table, and the pay_batch_element_entry_api API which inserts payroll element data in bulk. It provides examples of how to use these APIs, including specifications for an API package to call the APIs, sample insert statements and queries to retrieve data.

Uploaded by

EngOsamaHelal
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
212 views45 pages

Payroll API: Absence & Batch Entry

This document discusses APIs used for payroll integration in Oracle applications. Specifically, it covers the hr_person_absence_api API which is used to insert absence data in bulk to the Oracle table, and the pay_batch_element_entry_api API which inserts payroll element data in bulk. It provides examples of how to use these APIs, including specifications for an API package to call the APIs, sample insert statements and queries to retrieve data.

Uploaded by

EngOsamaHelal
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
  • Overview
  • API Navigation and Usage
  • Queries Related to APIs
  • Program Using APIs
  • Spec of API
  • Enter Absence/Leave in Oracle HRMS using API

[Link]

com/2014/01/23/payroll-intergration-api-absence-and-
batch-element-entry/

Payroll Integration API Absence and


Batch Element Entry

O verview
API is a way to insert the data from custom table to Oracle seeded
table like interface in oracle apps.
To program a API or Interface we have to know the front end and
where the data gets saved(tables). So it is always a good approach to
know the requirement, flow and the backend.

This area which we are going to deal is payroll Integration. Here we get the Data from the
custom table and then we insert into Oracle seeded table by using the API(Application
Programmable Interface). The custom table is that in OTL(Oracle Time and Labour) which
is a custom module designed for moiss.

Bascially a Interface or API is used to load a bulk of data to the Oracle Seeded table.
The hr_person_absence_api API inserts absence data bulk to the table
andpay_batch_element_entry_api API Inserts a bulk of elements based on
assignment set. This groups as Inputs that has to be applied for elements.

Here I have developed a package which has both the functionalities of Absence and Batch
Element Entry

APIs are

pay_batch_element_entry_api pay_batch_headers, pay_batch_lines,


pay_batch_control_totals
hr_person_absence_api

Flow
PAY_BATCH_ELEMENT_ENTRY_API
pay_batch_element_entry_api API inserts data in a batch depending on group
of elements or assignment set.
The Navigation is
Mass Information Exchange(MIX) Batch Element Entry

The Navigation is
Mass Information Exchange(MIX) Batch Element Entry Element Lines

HR_PERSON_ABSENCE_API
The Navigation is
Global HRMS Manager People Enter and Maintain Others
Absence

In oracle apps for Integrating the custom data to oracle absence table we have
the HR_PERSON_ABSENCE_APIAPIto delete or to update we can also use this
API. Before programming this API and moving the data from custom table it is

This below screenshot is a example of a sick leave for a person


Queries Related to HR_PERSON_ABSENCE_API

To Get Employee Details from PER_ALL_PEOPLE_Ftable

SELECT*
FROMPER_ALL_PEOPLE_FPAPF,PER_ALL_ASSIGNMENTS_FPAAF
WHEREPAPF.EMPLOYEE_NUMBER=2108
ANDPAPF.CURRENT_EMPLOYEE_FLAG=Y
ANDPAPF.EMPLOYEE_NUMBERISNOTNULL
ANDPAAF.PERSON_ID=PAPF.PERSON_ID
ANDSYSDATEBETWEENPAAF.EFFECTIVE_START_DATE
ANDPAAF.EFFECTIVE_END_DATE;
To Test the API whether it is working or not just click on the API and press F4
now a window gets opened. Just Click on the top on the option Toggle Spec or
Toggle Body now take the specification and body seperate compile and check
whether it is working or not
To Get Employees Absence Details from PER_ALL_PEOPLE_Ftable
SELECTPAPF.PERSON_ID,
PAPF.EFFECTIVE_START_DATE,
PAPF.EFFECTIVE_END_DATE,
PAPF.LAST_NAME,
PAV.CATEGORY_MEANING
FROMPER_ALL_PEOPLE_FPAPF,
PER_ALL_ASSIGNMENTS_FPAAF,
PER_ABSENCE_ATTENDANCES_VPAV
WHEREPAPF.EMPLOYEE_NUMBER=2108
ANDPAPF.CURRENT_EMPLOYEE_FLAG=Y
ANDPAPF.EMPLOYEE_NUMBERISNOTNULL
ANDPAAF.PERSON_ID=PAPF.PERSON_ID
ANDSYSDATEBETWEENPAAF.EFFECTIVE_START_DATE
ANDPAAF.EFFECTIVE_END_DATE
ANDPAV.PERSON_ID=PAPF.PERSON_ID;

Queries related to pay_batch_element_entry_api


Getting the Element Type Id and its Information
select*fromPAY_ELEMENT_TYPES_Fwhereelement_name=Overti
meOTMSpecialInputs

SELECT*
FROMPAY_ELEMENT_TYPES_F
WHEREelement_name=HolidayPaySpecialInputs;
SELECT*
FROMPAY_ELEMENT_TYPES_F
WHEREelement_name=ProductionBonus;

Getting the info of Batches


SELECTPBH.BATCH_NAMEFROMPAY_BATCH_HEADERS
PBH,PAY_BATCH_LINESPBLWHEREPBH.BATCH_ID=PBL.BATCH_ID;
SELECTPBL.*,PBH.*FROMPAY_BATCH_HEADERS
PBH,PAY_BATCH_LINES
PBLWHEREPBH.BATCH_ID=PBL.BATCH_IDANDBATCH_NAMELIKEMY
%TEST%;
Getting the Employee details from custom table
SELECTPAPF.PERSON_ID,
PAPF.EFFECTIVE_START_DATE,
PAPF.EFFECTIVE_END_DATE,
PAPF.LAST_NAME,
PAAF.ASSIGNMENT_ID
FROMPER_ALL_PEOPLE_FPAPF,
PER_ALL_ASSIGNMENTS_FPAAF
WHEREPAPF.EMPLOYEE_NUMBER=2108
ANDPAPF.CURRENT_EMPLOYEE_FLAG=Y
ANDPAPF.EMPLOYEE_NUMBERISNOTNULL
ANDPAAF.PERSON_ID=PAPF.PERSON_ID
ANDSYSDATEBETWEENPAAF.EFFECTIVE_START_DATE
ANDPAAF.EFFECTIVE_END_DATE;

To Check Whether the data is Inserted or not


SELECTPBL.*,PBH.*
FROMPAY_BATCH_HEADERSPBH,PAY_BATCH_LINESPBL
WHEREPBH.BATCH_ID=PBL.BATCH_IDANDBATCH_NAMELIKE%WORKS
HOP%;

SELECTPBL.*,PBH.*
FROMPAY_BATCH_HEADERSPBH,PAY_BATCH_LINESPBL
WHEREPBH.BATCH_ID=PBL.BATCH_IDANDBATCH_NAMELIKE%HOLI
%;

SELECTPBL.*,PBH.*
FROMPAY_BATCH_HEADERSPBH,PAY_BATCH_LINESPBL
WHEREPBH.BATCH_ID=PBL.BATCH_IDANDBATCH_NAMELIKE%MOISS
_OVERTIME%;

Insert Statements

INSERTINTOxxtest_emp_attendance(employee_id,
att_date,
holiday_overtime,
created_by,
creation_date,
day_type)
VALUES(113,
TO_DATE(01/02/2012,mm/dd/yyyy),
5,
1310,
TO_DATE(01/19/2014,mm/dd/yyyy),
WD);
INSERTINTOxxtest_emp_attendance(employee_id,
att_date,
workday_overtime,
created_by,
creation_date,
day_type)
VALUES(113,
TO_DATE(01/03/2012,mm/dd/yyyy),
5,
1310,
TO_DATE(01/19/2014,mm/dd/yyyy),
WD);

INSERTINTOxxtest_emp_attendance(employee_id,
att_date,
workshop_bonus,
created_by,
creation_date,
day_type)
VALUES(113,
TO_DATE(01/04/2012,mm/dd/yyyy),
5,
1310,
TO_DATE(01/19/2014,mm/dd/yyyy),
WD);

INSERTINTOxxtest_emp_attendance(employee_id,
att_date,
rig_bonus,
created_by,
creation_date,
day_type)
VALUES(113,
TO_DATE(01/05/2012,mm/dd/yyyy),
4,
1310,
TO_DATE(01/19/2014,mm/dd/yyyy),
WD);

Testing Queries

select*fromxxtest_emp_attendance
General Util Queries from Oracle Apps.

selecttextActual_Queryfromuser_viewswhereview_name=PAY_
ELEMENT_TYPES_DENORM_V;

Program using APIs

Spec of API

CREATEORREPLACEPACKAGEMOISS_PAYROLL_INT_API
IS
PROCEDUREMOISS_ABSENCE_PAYROLL(ERRBUFOUTVARCHAR2,
RETCODEOUTNUMBER,
EMP_NONUMBER,
START_DATE_VVARCHAR2,
END_DATE_VVARCHAR2
);

PROCEDUREMOISS_BONUS_OVERTIME_ALL_E(ERRBUFOUTVARCHAR2,
RETCODEOUTNUMBER,
START_DATE_VVARCHAR2,
END_DATE_VVARCHAR2
);

PROCEDUREMOISS_WORKDAY_OVERTIME_PAYROLL(ERRBUFOUTVARCHAR2
,
RETCODEOUTNUMBER,
START_DATE_VVARCHAR2,
END_DATE_VVARCHAR2
);

PROCEDUREMOISS_HOLIDAY_OVERTIME_PAYROLL(ERRBUFOUTVARCHAR2
,
RETCODEOUTNUMBER,
START_DATE_VDATE,
END_DATE_VDATE
);

PROCEDUREMOISS_WORKSHOP_BONUS_PAYROLL(ERRBUFOUTVARCHAR2,
RETCODEOUTNUMBER,
START_DATE_VDATE,
END_DATE_VDATE
);
PROCEDUREMOISS_RIG_BONUS_PAYROLL(ERRBUFOUTVARCHAR2,
RETCODEOUTNUMBER,
START_DATE_VDATE,
END_DATE_VDATE
);
END;

Body of API

/*Formattedon1/22/201411:22:14AM(QP5v5.114.809.3010)
*/
CREATEORREPLACEPACKAGEBODYMOISS_PAYROLL_INT_API
AS
PROCEDUREMOISS_ABSENCE_PAYROLL(ERRBUFOUTVARCHAR2,
RETCODEOUTNUMBER,
EMP_NONUMBER,
START_DATE_VVARCHAR2,
END_DATE_VVARCHAR2
)
IS
CURSORc
IS
SELECTDISTINCTEMPLOYEE_ID,ATT_DATE
FROMmoiss_emp_attendance
WHEREday_type=AB
ANDATT_DATEBETWEENTRUNC(TO_DATE(START_DATE_V,
RRRR/MM/DDHH24:MI:SS))
ANDTRUNC(TO_DATE(END_DATE_V,RRRR/MM/DDHH24:MI:SS))
ANDemployee_id=EMP_NO;

SELECT*FROMmoiss_emp_attendance

x_absence_daysnumber;
x_absence_hoursnumber:=10;
:=ABS_HOUR_V;Ifapplicablewehavetoinclude
x_absence_attendance_type_idnumber;ABSENCEATTENDANCE
TYPEID
:=ATT_TYPE_ID_V;Ifapplicablewehavetoinclude
x_absence_attendance_idnumber;
x_object_version_numbernumber;
x_occurrencenumber;
x_dur_dys_less_warningboolean;
x_dur_hrs_less_warningboolean;
x_exceeds_pto_entit_warningboolean;
x_exceeds_run_total_warningboolean;
x_abs_overlap_warningboolean;
x_abs_day_after_warningboolean;
x_dur_overwritten_warningboolean;
x_con_countnumber;
BEGIN
retcode:=0;
errbuf:=Success;
x_con_count:=0;
fnd_file.put_line(fnd_file.LOG,Start);

DBMS_OUTPUT.PUT_LINE(
THEvaluesAREIS||START_DATE_V||END_DATE_V
);

FND_FILE.PUT_LINE(fnd_file.LOG,)
TokeepcheckfortheConcurrent
x_con_count:=0;
Countstheloopofnumberthatistotalnumberofloops
thathastobegoing

fnd_global.apps_initialize(user_id=>fnd_global.user_id,
resp_id=>fnd_global.resp_id,
resp_appl_id=>fnd_global.resp_appl_id);

SELECTabsence_attendance_type_id
INTOx_absence_attendance_type_id
FROMper_absence_attendance_types
WHEREbusiness_group_id=fnd_global.per_business_group_id
ANDnameLIKE%Personal%
ANDROWNUM=1;

FORIINC
LOOP
hr_person_absence_api.create_person_absence(
p_validate=>FALSE,
p_effective_date=>I.ATT_DATE,
p_person_id=>I.EMPLOYEE_ID,
p_business_group_id=>fnd_global.per_business_group_id
,
101,
p_absence_attendance_type_id=>x_absence_attendance_type_id
,
71,VACATIONLEAVE
p_date_projected_start=>I.ATT_DATE,
p_time_projected_start=>NULL,
Ifapplicablewehavetoinclude
p_date_projected_end=>I.ATT_DATE,
p_time_projected_end=>NULL,
Ifapplicablewehavetoinclude
p_date_start=>I.ATT_DATE,
p_time_start=>NULL,
Ifapplicablewehavetoinclude18:00,
p_date_end=>I.ATT_DATE,
p_time_end=>NULL,
Ifapplicablewehavetoinclude20:00,
p_date_start=>sysdate,
p_date_end=>sysdate+1,
p_absence_days=>x_absence_days,
p_absence_hours=>x_absence_hours,
p_absence_attendance_id=>x_absence_attendance_id,
p_object_version_number=>x_object_version_number,
p_occurrence=>x_occurrence,
p_dur_dys_less_warning=>x_dur_dys_less_warning,
p_dur_hrs_less_warning=>x_dur_hrs_less_warning,
p_exceeds_pto_entit_warning=>x_exceeds_pto_entit_warning,
p_exceeds_run_total_warning=>x_exceeds_run_total_warning,
p_abs_overlap_warning=>x_abs_overlap_warning,
p_abs_day_after_warning=>x_abs_day_after_warning,
p_dur_overwritten_warning=>x_dur_overwritten_warning
);

x_con_count:=1+x_con_count;
fnd_file.put_line(
fnd_file.LOG,
x_con_count
||
||EmployeeID
||I.EMPLOYEE_ID
||AttDate
||I.ATT_DATE
);
ENDLOOP;

IFx_con_count=0
THEN
fnd_file.put_line(fnd_file.LOG,NoDataFound);
ENDIF;

COMMIT;
EXCEPTION
WHENNO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE(Nodatafound);
retcode:=1;
errbuf:=
NODATAFOUND
||SUBSTR(SQLERRM,1,1000)
||DBMS_UTILITY.format_error_backtrace;
WAR_V:=errbuf;
fnd_file.put_line(
fnd_file.LOG,
NoDataFound||DBMS_UTILITY.format_error_backtrace
);
WHENOTHERS
THEN
DBMS_OUTPUT.PUT_LINE(Nodatafound);
retcode:=1;
errbuf:=
ErrorinPayrollABSENCEIntegration
||SUBSTR(SQLERRM,1,1000)
||DBMS_UTILITY.format_error_backtrace;
fnd_file.put_line(
fnd_file.LOG,
NoDataFound||DBMS_UTILITY.format_error_backtrace
);

WAR_V:=errbuf;
ENDMOISS_ABSENCE_PAYROLL;

PROCEDUREMOISS_BONUS_OVERTIME_ALL_E(ERRBUFOUTVARCHAR2,
RETCODEOUTNUMBER,
START_DATE_VVARCHAR2,
END_DATE_VVARCHAR2
)
IS
ERRBUFVARCHAR2(100),
RETCODEOUTNUMBER,
BEGIN
fnd_file.put_line(fnd_file.LOG,Procedure1Started);
MOISS_PAYROLL_INT_API.MOISS_WORKDAY_OVERTIME_PAYROLL(ERRBUF,
RETCODE,START_DATE_V,END_DATE_V);
fnd_file.put_line(fnd_file.LOG,);
MOISS_PAYROLL_INT_API.MOISS_HOLIDAY_OVERTIME_PAYROLL(ERRBUF
,RETCODE,START_DATE_V,END_DATE_V);
MOISS_PAYROLL_INT_API.MOISS_WORKSHOP_BONUS_PAYROLL(ERRBUF,R
ETCODE,START_DATE_V,END_DATE_V);
MOISS_PAYROLL_INT_API.MOISS_RIG_BONUS_PAYROLL(ERRBUF,RETCOD
E,START_DATE_V,END_DATE_V);
fnd_file.put_line(fnd_file.LOG,Procedure1Ended);
EXCEPTION
whenothersthen
fnd_file.put_line(fnd_file.LOG,ExeEnv||
DBMS_UTILITY.format_error_backtrace);
ENDMOISS_BONUS_OVERTIME_ALL_E;

PROCEDUREMOISS_WORKDAY_OVERTIME_PAYROLL(ERRBUFOUTVARCHAR2
,
RETCODEOUTNUMBER,
START_DATE_VVARCHAR2,
END_DATE_VVARCHAR2
)
IS
v_batch_idvarchar2(60);
x_batch_idnumber:=0;
x_ver_nonumber;
x_batch_line_idnumber;
g_ver_nonumber;
x_ver_no_1number;
x_con_countnumber;
x_element_type_idnumber;

CURSORc
IS
SELECTDISTINCTatt_date,
WORKDAY_OVERTIME,
PAF.ASSIGNMENT_ID,
paf.assignment_number
FROMmoiss_emp_attendancemea,per_assignments_fpaf
WHEREpaf.person_id=mea.employee_id
ANDWORKDAY_OVERTIMEISNOTNULL
ANDATT_DATEBETWEENSTART_DATE_VANDEND_DATE_V;
BEGIN
retcode:=0;
errbuf:=Success;
x_con_count:=0;
fnd_file.put_line(fnd_file.LOG,Start);

DBMS_OUTPUT.PUT_LINE(
THEvaluesAREIS||START_DATE_V||END_DATE_V
);

FND_FILE.PUT_LINE(fnd_file.LOG,)

TokeepcheckfortheConcurrent
x_con_count:=0;
Countstheloopofnumberthatistotalnumberofloops
thathastobegoing
fnd_global.apps_initialize(user_id=>fnd_global.user_id,
resp_id=>fnd_global.resp_id,
resp_appl_id=>fnd_global.resp_appl_id);

SELECTTO_CHAR(SYSDATE)INTOv_batch_idFROMDUAL;

SELECTELEMENT_TYPE_ID
INTOx_element_type_id
FROMPAY_ELEMENT_TYPES_F
WHEREelement_nameLIKE%OvertimeOTMSpecialInputs%
ANDROWNUM=1;

FORiINc
LOOP
pay_batch_element_entry_api.create_batch_header(
p_validate=>FALSE,
p_session_date=>SYSDATE,
p_batch_name=>MOISS_OVERTIME||v_batch_id,
p_batch_status=>U,unprocessed
p_business_group_id=>fnd_global.per_business_group_id,
202,101business_group_id
p_action_if_exists=>I,
i=createnewentry,u=updateentry
p_batch_reference=>NULL,
g_user_name||||to_char(sysdate,ddmonyyyy)
p_batch_source=>NULL,nameofthebatch
p_comments=>NULL,
p_date_effective_changes=>NULL,
p_purge_after_transfer=>N,
p_reject_if_future_changes=>Y,
p_batch_id=>x_batch_id,
p_object_version_number=>g_ver_no,
p_reject_if_results_exists=>Y,
p_purge_after_rollback=>N,
p_batch_type=>NULL,
p_reject_entry_not_removed=>N,
p_rollback_entry_updates=>N
);
DBMS_OUTPUT.put_line(
v_batch_id||v_batch_id||x_batch_id||g_ver_no
);
pay_batch_element_entry_api.create_batch_line(
p_validate=>FALSE,
p_session_date=>SYSDATE,
p_batch_id=>x_batch_id,
p_assignment_id=>i.ASSIGNMENT_ID,
p_assignment_number=>i.assignment_number,
p_batch_sequence=>NULL,
p_effective_date=>SYSDATE,l_effective_date
p_element_type_id=>x_element_type_id,
OvertimeOTMSpecialInputs
p_entry_type=>E,
p_value_1=>i.WORKDAY_OVERTIME,
holidayovertimehrs
p_value_2=>15,AssumingValue
regularovertimehrs,
p_batch_line_id=>x_batch_line_id,
p_object_version_number=>x_ver_no_1
);

x_con_count:=1+x_con_count;
fnd_file.put_line(
fnd_file.LOG,
x_con_count||||EmployeeID||I.WORKDAY_OVERTIME
||
I.ASSIGNMENT_ID||I.assignment_number||AttDate

||I.ATT_DATE
);
ENDLOOP;
IFx_con_count=0
THEN
fnd_file.put_line(fnd_file.LOG,NoDataFound);
ENDIF;

COMMIT;
EXCEPTION
WHENNO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE(Nodatafound);
retcode:=1;
errbuf:=
NODATAFOUND
||SUBSTR(SQLERRM,1,1000)
||DBMS_UTILITY.format_error_backtrace;
WAR_V:=errbuf;
fnd_file.put_line(
fnd_file.LOG,
NoDataFound||DBMS_UTILITY.format_error_backtrace
);
WHENOTHERS
THEN
DBMS_OUTPUT.PUT_LINE(Nodatafound);
retcode:=1;
errbuf:=
ErrorinPayrollABSENCEIntegration
||SUBSTR(SQLERRM,1,1000)
||DBMS_UTILITY.format_error_backtrace;
fnd_file.put_line(
fnd_file.LOG,
NoDataFound||DBMS_UTILITY.format_error_backtrace
);

WAR_V:=errbuf;
END;

PROCEDUREMOISS_HOLIDAY_OVERTIME_PAYROLL(ERRBUFOUTVARCHAR2
,
RETCODEOUTNUMBER,
START_DATE_VDATE,
END_DATE_VDATE
)
IS
v_batch_idVARCHAR2(60);
x_batch_idnumber;
x_ver_nonumber;
x_batch_line_idnumber;
g_ver_nonumber;
x_ver_no_1number;
x_con_countnumber;
x_element_type_idnumber;

CURSORc
IS
SELECTDISTINCTatt_date,
HOLIDAY_OVERTIME,
PAF.ASSIGNMENT_ID,
paf.assignment_number
FROMmoiss_emp_attendancemea,per_assignments_fpaf
WHEREpaf.person_id=mea.employee_id
ANDMEA.HOLIDAY_OVERTIMEISNOTNULL
ANDATT_DATEBETWEENSTART_DATE_VANDEND_DATE_V;
BEGIN
retcode:=0;
errbuf:=Success;
x_con_count:=0;
fnd_file.put_line(fnd_file.LOG,Start);

DBMS_OUTPUT.PUT_LINE(
THEvaluesAREIS||START_DATE_V||END_DATE_V
);

FND_FILE.PUT_LINE(fnd_file.LOG,)

TokeepcheckfortheConcurrent
x_con_count:=0;
Countstheloopofnumberthatistotalnumberofloops
thathastobegoing

fnd_global.apps_initialize(user_id=>fnd_global.user_id,
resp_id=>fnd_global.resp_id,
resp_appl_id=>fnd_global.resp_appl_id);

SELECTTO_CHAR(SYSDATE)INTOv_batch_idFROMDUAL;

SELECTELEMENT_TYPE_ID
INTOx_element_type_id
FROMPAY_ELEMENT_TYPES_F
WHEREelement_nameLIKE%OTLHolidayPay%ANDROWNUM=1;
FORiINc
LOOP
pay_batch_element_entry_api.create_batch_header(
p_validate=>FALSE,
p_session_date=>SYSDATE,
p_batch_name=>M_HOLI_OVERTIME||v_batch_id,
p_batch_status=>U,unprocessed
p_business_group_id=>fnd_global.per_business_group_id,
202,101business_group_id
p_action_if_exists=>I,
i=createnewentry,u=updateentry
p_batch_reference=>NULL,
g_user_name||||to_char(sysdate,ddmonyyyy)
p_batch_source=>NULL,nameofthebatch
p_comments=>NULL,
p_date_effective_changes=>NULL,
p_purge_after_transfer=>N,
p_reject_if_future_changes=>Y,
p_batch_id=>x_batch_id,
p_object_version_number=>g_ver_no,
p_reject_if_results_exists=>Y,
p_purge_after_rollback=>N,
p_batch_type=>NULL,
p_reject_entry_not_removed=>N,
p_rollback_entry_updates=>N
);
DBMS_OUTPUT.put_line(
v_batch_id||v_batch_id||x_batch_id||g_ver_no
);
pay_batch_element_entry_api.create_batch_line(
p_validate=>FALSE,
p_session_date=>SYSDATE,
p_batch_id=>x_batch_id,
p_assignment_id=>i.ASSIGNMENT_ID,
p_assignment_number=>i.assignment_number,
p_batch_sequence=>NULL,
p_effective_date=>SYSDATE,l_effective_date
p_element_type_id=>x_element_type_id,
EarningspaidforHolidaystraighttime)
p_entry_type=>E,
p_value_1=>i.HOLIDAY_OVERTIME,
holidayovertimehrs
p_value_2=>15,
AssumingValue(Valuestobenoted)
regularovertimehrs,
p_batch_line_id=>x_batch_line_id,
p_object_version_number=>x_ver_no_1
);
x_con_count:=1+x_con_count;
fnd_file.put_line(
fnd_file.LOG,
x_con_count||||EmployeeID||I.HOLIDAY_OVERTIME
||
I.ASSIGNMENT_ID||I.assignment_number||AttDate

||I.ATT_DATE
);
ENDLOOP;

IFx_con_count=0
THEN
fnd_file.put_line(fnd_file.LOG,NoDataFound);
ENDIF;

COMMIT;
EXCEPTION
WHENNO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE(Nodatafound);
retcode:=1;
errbuf:=
NODATAFOUND
||SUBSTR(SQLERRM,1,1000)
||DBMS_UTILITY.format_error_backtrace;
WAR_V:=errbuf;
fnd_file.put_line(
fnd_file.LOG,
NoDataFound||DBMS_UTILITY.format_error_backtrace
);
WHENOTHERS
THEN
DBMS_OUTPUT.PUT_LINE(Nodatafound);
retcode:=1;
errbuf:=
ErrorinPayrollABSENCEIntegration
||SUBSTR(SQLERRM,1,1000)
||DBMS_UTILITY.format_error_backtrace;
fnd_file.put_line(
fnd_file.LOG,
NoDataFound||DBMS_UTILITY.format_error_backtrace
);

WAR_V:=errbuf;
END;

PROCEDUREMOISS_WORKSHOP_BONUS_PAYROLL(ERRBUFOUTVARCHAR2,
RETCODEOUTNUMBER,
START_DATE_VDATE,
END_DATE_VDATE
)
IS
v_batch_idVARCHAR2(60);
x_batch_idnumber;
x_ver_nonumber;
x_batch_line_idnumber;
g_ver_nonumber;
x_ver_no_1number;
x_con_countnumber;
x_element_type_idnumber;
CURSORc
IS
SELECTDISTINCTatt_date,
WORKSHOP_BONUS,
PAF.ASSIGNMENT_ID,
paf.assignment_number
FROMmoiss_emp_attendancemea,per_assignments_fpaf
WHEREpaf.person_id=mea.employee_id
ANDMEA.WORKSHOP_BONUSISNOTNULL
ANDATT_DATEBETWEENSTART_DATE_VANDEND_DATE_V;
BEGIN
retcode:=0;
errbuf:=Success;
x_con_count:=0;
fnd_file.put_line(fnd_file.LOG,Start);

DBMS_OUTPUT.PUT_LINE(
THEvaluesAREIS||START_DATE_V||END_DATE_V
);

FND_FILE.PUT_LINE(fnd_file.LOG,)

TokeepcheckfortheConcurrent
x_con_count:=0;
Countstheloopofnumberthatistotalnumberofloops
thathastobegoing

fnd_global.apps_initialize(user_id=>fnd_global.user_id,
resp_id=>fnd_global.resp_id,
resp_appl_id=>fnd_global.resp_appl_id);
SELECTTO_CHAR(SYSDATE)INTOv_batch_idFROMDUAL;
SELECTELEMENT_TYPE_ID
INTOx_element_type_id
FROMPAY_ELEMENT_TYPES_F
WHEREelement_nameLIKE%ProductionBonus%ANDROWNUM=1;
FORiINc
LOOP
pay_batch_element_entry_api.create_batch_header(
p_validate=>FALSE,
p_session_date=>SYSDATE,
p_batch_name=>WORKSHOP_BONUS||v_batch_id,
p_batch_status=>U,unprocessed
p_business_group_id=>fnd_global.per_business_group_id,
202,101business_group_id
p_action_if_exists=>I,
i=createnewentry,u=updateentry
p_batch_reference=>NULL,
g_user_name||||to_char(sysdate,ddmonyyyy)
p_batch_source=>NULL,nameofthebatch
p_comments=>NULL,
p_date_effective_changes=>NULL,
p_purge_after_transfer=>N,
p_reject_if_future_changes=>Y,
p_batch_id=>x_batch_id,
p_object_version_number=>g_ver_no,
p_reject_if_results_exists=>Y,
p_purge_after_rollback=>N,
p_batch_type=>NULL,
p_reject_entry_not_removed=>N,
p_rollback_entry_updates=>N
);
DBMS_OUTPUT.put_line(
v_batch_id||v_batch_id||x_batch_id||g_ver_no
);
pay_batch_element_entry_api.create_batch_line(
p_validate=>FALSE,
p_session_date=>SYSDATE,
p_batch_id=>x_batch_id,
p_assignment_id=>i.ASSIGNMENT_ID,
p_assignment_number=>i.assignment_number,
p_batch_sequence=>NULL,
p_effective_date=>SYSDATE,l_effective_date
p_element_type_id=>x_element_type_id,ProductionBonus
p_entry_type=>E,
p_value_1=>i.WORKSHOP_BONUS,
holidayovertimehrs
p_value_2=>15,
AssumingValue(Valuestobenoted)
regularovertimehrs,
p_batch_line_id=>x_batch_line_id,
p_object_version_number=>x_ver_no_1
);
x_con_count:=1+x_con_count;
fnd_file.put_line(
fnd_file.LOG,
x_con_count||||EmployeeID||I.WORKSHOP_BONUS
||
I.ASSIGNMENT_ID||I.assignment_number||AttDate

||I.ATT_DATE
);
ENDLOOP;

IFx_con_count=0
THEN
fnd_file.put_line(fnd_file.LOG,NoDataFound);
ENDIF;

COMMIT;
EXCEPTION
WHENNO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE(Nodatafound);
retcode:=1;
errbuf:=
NODATAFOUND
||SUBSTR(SQLERRM,1,1000)
||DBMS_UTILITY.format_error_backtrace;
WAR_V:=errbuf;
fnd_file.put_line(
fnd_file.LOG,
NoDataFound||DBMS_UTILITY.format_error_backtrace
);
WHENOTHERS
THEN
DBMS_OUTPUT.PUT_LINE(Nodatafound);
retcode:=1;
errbuf:=
ErrorinPayrollABSENCEIntegration
||SUBSTR(SQLERRM,1,1000)
||DBMS_UTILITY.format_error_backtrace;
fnd_file.put_line(
fnd_file.LOG,
NoDataFound||DBMS_UTILITY.format_error_backtrace
);

WAR_V:=errbuf;
END;

PROCEDUREMOISS_RIG_BONUS_PAYROLL(ERRBUFOUTVARCHAR2,
RETCODEOUTNUMBER,
START_DATE_VDATE,
END_DATE_VDATE
)
IS
v_batch_idVARCHAR2(60);
x_batch_idnumber;
x_ver_nonumber;
x_batch_line_idnumber;
g_ver_nonumber;
x_ver_no_1number;
x_con_countnumber;
x_element_type_idnumber;

CURSORc
IS
SELECTDISTINCTatt_date,
RIG_BONUS,
PAF.ASSIGNMENT_ID,
paf.assignment_number
FROMmoiss_emp_attendancemea,per_assignments_fpaf
WHEREpaf.person_id=mea.employee_id
ANDMEA.WORKSHOP_BONUSISNOTNULL
ANDATT_DATEBETWEENSTART_DATE_VANDEND_DATE_V;
BEGIN
retcode:=0;
errbuf:=Success;
x_con_count:=0;
fnd_file.put_line(fnd_file.LOG,Start);

DBMS_OUTPUT.PUT_LINE(
THEvaluesAREIS||START_DATE_V||END_DATE_V
);

FND_FILE.PUT_LINE(fnd_file.LOG,)

TokeepcheckfortheConcurrent
x_con_count:=0;
Countstheloopofnumberthatistotalnumberofloops
thathastobegoing

fnd_global.apps_initialize(user_id=>fnd_global.user_id,
resp_id=>fnd_global.resp_id,
resp_appl_id=>fnd_global.resp_appl_id);
SELECTTO_CHAR(SYSDATE)INTOv_batch_idFROMDUAL;
SELECTELEMENT_TYPE_ID
INTOx_element_type_id
FROMPAY_ELEMENT_TYPES_F
WHEREelement_nameLIKE%ProductionBonus%ANDROWNUM=1;
FORiINc
LOOP
pay_batch_element_entry_api.create_batch_header(
p_validate=>FALSE,
p_session_date=>SYSDATE,
p_batch_name=>RIG_BONUS||v_batch_id,
p_batch_status=>U,unprocessed
p_business_group_id=>fnd_global.per_business_group_id,
202,101business_group_id
p_action_if_exists=>I,
i=createnewentry,u=updateentry
p_batch_reference=>NULL,
g_user_name||||to_char(sysdate,ddmonyyyy)
p_batch_source=>NULL,nameofthebatch
p_comments=>NULL,
p_date_effective_changes=>NULL,
p_purge_after_transfer=>N,
p_reject_if_future_changes=>Y,
p_batch_id=>x_batch_id,
p_object_version_number=>g_ver_no,
p_reject_if_results_exists=>Y,
p_purge_after_rollback=>N,
p_batch_type=>NULL,
p_reject_entry_not_removed=>N,
p_rollback_entry_updates=>N
);
DBMS_OUTPUT.put_line(
v_batch_id||v_batch_id||x_batch_id||g_ver_no
);
pay_batch_element_entry_api.create_batch_line(
p_validate=>FALSE,
p_session_date=>SYSDATE,
p_batch_id=>x_batch_id,
p_assignment_id=>i.ASSIGNMENT_ID,
p_assignment_number=>i.assignment_number,
p_batch_sequence=>NULL,
p_effective_date=>SYSDATE,l_effective_date
p_element_type_id=>x_element_type_id,
ProductionBonus(HastobechangedforRigBonus)
p_entry_type=>E,
p_value_1=>i.RIG_BONUS,
holidayovertimehrs
p_value_2=>15,
AssumingValue(Valuestobenoted)
regularovertimehrs,
p_batch_line_id=>x_batch_line_id,
p_object_version_number=>x_ver_no_1
);
x_con_count:=1+x_con_count;
fnd_file.put_line(
fnd_file.LOG,
x_con_count||||EmployeeID||I.RIG_BONUS||I.
ASSIGNMENT_ID||I.assignment_number||AttDate
||I.
ATT_DATE);
ENDLOOP;
IFx_con_count=0
THEN
fnd_file.put_line(fnd_file.LOG,NoDataFound);
ENDIF;
COMMIT;
EXCEPTION
WHENNO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE(Nodatafound);
retcode:=1;
errbuf:=
NODATAFOUND
||SUBSTR(SQLERRM,1,1000)
||DBMS_UTILITY.format_error_backtrace;
WAR_V:=errbuf;
fnd_file.put_line(
fnd_file.LOG,
NoDataFound||DBMS_UTILITY.format_error_backtrace
);
WHENOTHERS
THEN
DBMS_OUTPUT.PUT_LINE(Nodatafound);
retcode:=1;
errbuf:=
ErrorinPayrollABSENCEIntegration
||SUBSTR(SQLERRM,1,1000)
||DBMS_UTILITY.format_error_backtrace;
fnd_file.put_line(
fnd_file.LOG,
NoDataFound||DBMS_UTILITY.format_error_backtrace
);
WAR_V:=errbuf;
END;
ENDMOISS_PAYROLL_INT_API;
Program Body for Testing or Sample data Test

CREATEORREPLACEPACKAGEBODYMOISS_PAYROLL_INT_API
AS
PROCEDUREMOISS_ABSENCE_PAYROLL(BUISNESS_G_VNUMBER,
START_DATE_VVARCHAR2,
END_DATE_VVARCHAR2,
WAR_VOUTVARCHAR2)
IS
CURSORc
IS
SELECTDISTINCTEMPLOYEE_ID,ATT_DATE
FROMmoiss_emp_attendance
WHEREday_type=WO
ANDATT_DATEBETWEENTO_DATE(START_DATE_V,MM/DD/YYYY)
TO_DATE(10/19/2013,MM/DD/YYYY)
ANDTO_DATE(END_DATE_V,MM/DD/YYYY)
TO_DATE(10/20/2013,MM/DD/YYYY)
ANDemployee_id=30;

SELECT*FROMmoiss_emp_attendance
x_START_DATE_Vdate=10/18/2013;
x_absence_daysnumber;
x_absence_hoursnumber:=10;
:=ABS_HOUR_V;Ifapplicablewehavetoinclude
X_absence_attendance_type_idnumber:=7;
:=ATT_TYPE_ID_V;Ifapplicablewehavetoinclude
x_absence_attendance_idnumber;
x_object_version_numbernumber;
x_occurrencenumber;
x_dur_dys_less_warningboolean;
x_dur_hrs_less_warningboolean;
x_exceeds_pto_entit_warningboolean;
x_exceeds_run_total_warningboolean;
x_abs_overlap_warningboolean;
x_abs_day_after_warningboolean;
x_dur_overwritten_warningboolean;
BEGIN

DBMS_OUTPUT.PUT_LINE(THEvaluesAREIS||BUISNESS_G_V||
START_DATE_V||END_DATE_V);
FORIINC
LOOP

hr_person_absence_api.create_person_absence(
p_validate=>FALSE,
p_effective_date=>I.ATT_DATE,
p_person_id=>I.EMPLOYEE_ID,
p_business_group_id=>BUISNESS_G_V,
101,
p_absence_attendance_type_id=>X_absence_attendance_type_id
,
71,VACATIONLEAVE
p_date_projected_start=>I.ATT_DATE,
p_time_projected_start=>NULL,
Ifapplicablewehavetoinclude
p_date_projected_end=>I.ATT_DATE,
p_time_projected_end=>NULL,
Ifapplicablewehavetoinclude
p_date_start=>I.ATT_DATE,
p_time_start=>NULL,
Ifapplicablewehavetoinclude18:00,
p_date_end=>I.ATT_DATE,
p_time_end=>NULL,
Ifapplicablewehavetoinclude20:00,
p_date_start=>sysdate,
p_date_end=>sysdate+1,
p_absence_days=>x_absence_days,
p_absence_hours=>x_absence_hours,
p_absence_attendance_id=>x_absence_attendance_id,
p_object_version_number=>x_object_version_number,
p_occurrence=>x_occurrence,
p_dur_dys_less_warning=>x_dur_dys_less_warning,
p_dur_hrs_less_warning=>x_dur_hrs_less_warning,
p_exceeds_pto_entit_warning=>x_exceeds_pto_entit_warning,
p_exceeds_run_total_warning=>x_exceeds_run_total_warning,
p_abs_overlap_warning=>x_abs_overlap_warning,
p_abs_day_after_warning=>x_abs_day_after_warning,
p_dur_overwritten_warning=>x_dur_overwritten_warning
);
ENDLOOP;

COMMIT;
EXCEPTION
WHENOTHERS
THEN
WAR_V:=ERROROCCURED||sqlERRM;
ENDMOISS_ABSENCE_PAYROLL;

/*Formattedon1/16/20146:13:49PM(QP5v5.114.809.3010)
*/
PROCEDUREMOISS_WORKDAY_OVERTIME_PAYROLL(BUISNESS_G_VNUMBE
R,
START_DATE_VVARCHAR2,
END_DATE_VVARCHAR2,
WAR_VOUTVARCHAR2)
IS
v_batch_idvarchar2(60);
x_batch_idnumber:=0;
x_ver_nonumber;
x_batch_line_idnumber;
g_ver_nonumber;
x_ver_no_1number;
CURSORc
IS
SELECTDISTINCT
WORKDAY_OVERTIME,PAF.ASSIGNMENT_ID,paf.assignment_number
FROMxxtest_emp_attendancemea,per_assignments_fpaf
WHEREpaf.person_id=mea.employee_id
ANDWORKDAY_OVERTIMEISNOTNULL;
BEGIN
SELECTTO_CHAR(SYSDATE)INTOv_batch_idFROMDUAL;
FORiINc
LOOP
pay_batch_element_entry_api.create_batch_header(
p_validate=>FALSE,
p_session_date=>SYSDATE,
p_batch_name=>MOISS_OVERTIME||v_batch_id,
p_batch_status=>U,unprocessed
p_business_group_id=>202,
202,101business_group_id
p_action_if_exists=>I,
i=createnewentry,u=updateentry
p_batch_reference=>NULL,
g_user_name||||to_char(sysdate,ddmonyyyy)
p_batch_source=>NULL,nameofthebatch
p_comments=>NULL,
p_date_effective_changes=>NULL,
p_purge_after_transfer=>N,
p_reject_if_future_changes=>Y,
p_batch_id=>x_batch_id,
p_object_version_number=>g_ver_no,
p_reject_if_results_exists=>Y,
p_purge_after_rollback=>N,
p_batch_type=>NULL,
p_reject_entry_not_removed=>N,
p_rollback_entry_updates=>N
);
DBMS_OUTPUT.put_line(
v_batch_id||v_batch_id||x_batch_id||g_ver_no
);
pay_batch_element_entry_api.create_batch_line(
p_validate=>FALSE,
p_session_date=>SYSDATE,
p_batch_id=>x_batch_id,
p_assignment_id=>i.ASSIGNMENT_ID,
p_assignment_number=>i.assignment_number,
p_batch_sequence=>NULL,
p_effective_date=>SYSDATE,l_effective_date
p_element_type_id=>50944,OvertimeOTMSpecialInputs
p_entry_type=>E,
p_value_1=>i.WORKDAY_OVERTIME,
holidayovertimehrs
p_value_2=>15,AssumingValue
regularovertimehrs,
p_batch_line_id=>x_batch_line_id,
p_object_version_number=>x_ver_no_1
);
ENDLOOP;
COMMIT;
EXCEPTION
WHENOTHERS
THEN
WAR_V:=ERROROCCURED||sqlERRM;
dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

PROCEDUREMOISS_HOLIDAY_OVERTIME_PAYROLL(BUISNESS_G_VNUMBE
R,
START_DATE_VVARCHAR2,
END_DATE_VVARCHAR2,
WAR_VOUTVARCHAR2)
IS
v_batch_idVARCHAR2(60);
x_batch_idnumber;
x_ver_nonumber;
x_batch_line_idnumber;
g_ver_nonumber;
x_ver_no_1number;
CURSORc
IS
SELECTDISTINCT
HOLIDAY_OVERTIME,PAF.ASSIGNMENT_ID,paf.assignment_number
FROMxxtest_emp_attendancemea,per_assignments_fpaf
WHEREpaf.person_id=mea.employee_id
ANDMEA.HOLIDAY_OVERTIMEISNOTNULL;

BEGIN
SELECTTO_CHAR(SYSDATE)INTOv_batch_idFROMDUAL;
FORiINc
LOOP
pay_batch_element_entry_api.create_batch_header(
p_validate=>FALSE,
p_session_date=>SYSDATE,
p_batch_name=>M_HOLI_OVERTIME||v_batch_id,
p_batch_status=>U,unprocessed
p_business_group_id=>202,
fnd_global.per_business_group_id,
202,101business_group_id
p_action_if_exists=>I,
i=createnewentry,u=updateentry
p_batch_reference=>NULL,
g_user_name||||to_char(sysdate,ddmonyyyy)
p_batch_source=>NULL,nameofthebatch
p_comments=>NULL,
p_date_effective_changes=>NULL,
p_purge_after_transfer=>N,
p_reject_if_future_changes=>Y,
p_batch_id=>x_batch_id,
p_object_version_number=>g_ver_no,
p_reject_if_results_exists=>Y,
p_purge_after_rollback=>N,
p_batch_type=>NULL,
p_reject_entry_not_removed=>N,
p_rollback_entry_updates=>N
);
DBMS_OUTPUT.put_line(
v_batch_id||v_batch_id||x_batch_id||g_ver_no
);
pay_batch_element_entry_api.create_batch_line(
p_validate=>FALSE,
p_session_date=>SYSDATE,
p_batch_id=>x_batch_id,
p_assignment_id=>i.ASSIGNMENT_ID,
p_assignment_number=>i.assignment_number,
p_batch_sequence=>NULL,
p_effective_date=>SYSDATE,l_effective_date
p_element_type_id=>50749,EarningspaidforHoliday
straighttime)
p_entry_type=>E,
p_value_1=>i.HOLIDAY_OVERTIME,
holidayovertimehrs
p_value_2=>15,AssumingValue(Valuestobenoted)
regularovertimehrs,
p_batch_line_id=>x_batch_line_id,
p_object_version_number=>x_ver_no_1
);
ENDLOOP;
COMMIT;

EXCEPTION
WHENOTHERS
THEN
dbms_output.put_line(dbms_utility.format_error_backtrace);
WAR_V:=ERROROCCURED||sqlERRM;
END;

PROCEDUREMOISS_WORKSHOP_BONUS_PAYROLL(BUISNESS_G_VNUMBER,
START_DATE_VVARCHAR2,
END_DATE_VVARCHAR2,
WAR_VOUTVARCHAR2)
IS
v_batch_idVARCHAR2(60);
x_batch_idnumber;
x_ver_nonumber;
x_batch_line_idnumber;
g_ver_nonumber;
x_ver_no_1number;

CURSORc
IS

SELECTDISTINCT
WORKSHOP_BONUS,PAF.ASSIGNMENT_ID,paf.assignment_number
FROMxxtest_emp_attendancemea,per_assignments_fpaf
WHEREpaf.person_id=mea.employee_id
ANDMEA.WORKSHOP_BONUSISNOTNULL;

BEGIN

SELECTTO_CHAR(SYSDATE)INTOv_batch_idFROMDUAL;

FORiINc
LOOP
pay_batch_element_entry_api.create_batch_header(
p_validate=>FALSE,
p_session_date=>SYSDATE,
p_batch_name=>WORKSHOP_BONUS||v_batch_id,
p_batch_status=>U,unprocessed
p_business_group_id=>202,
fnd_global.per_business_group_id,
202,101business_group_id
p_action_if_exists=>I,
i=createnewentry,u=updateentry
p_batch_reference=>NULL,
g_user_name||||to_char(sysdate,ddmonyyyy)
p_batch_source=>NULL,nameofthebatch
p_comments=>NULL,
p_date_effective_changes=>NULL,
p_purge_after_transfer=>N,
p_reject_if_future_changes=>Y,
p_batch_id=>x_batch_id,
p_object_version_number=>g_ver_no,
p_reject_if_results_exists=>Y,
p_purge_after_rollback=>N,
p_batch_type=>NULL,
p_reject_entry_not_removed=>N,
p_rollback_entry_updates=>N
);
DBMS_OUTPUT.put_line(
v_batch_id||v_batch_id||x_batch_id||g_ver_no
);
pay_batch_element_entry_api.create_batch_line(
p_validate=>FALSE,
p_session_date=>SYSDATE,
p_batch_id=>x_batch_id,
p_assignment_id=>i.ASSIGNMENT_ID,
p_assignment_number=>i.assignment_number,
p_batch_sequence=>NULL,
p_effective_date=>SYSDATE,l_effective_date
p_element_type_id=>51661,ProductionBonus
p_entry_type=>E,
p_value_1=>i.WORKSHOP_BONUS,
holidayovertimehrs
p_value_2=>15,AssumingValue(Valuestobenoted)
regularovertimehrs,
p_batch_line_id=>x_batch_line_id,
p_object_version_number=>x_ver_no_1
);
DBMS_OUTPUT.put_line(
v_batch_id||x_batch_line_id||x_ver_no_1);
ENDLOOP;

COMMIT;

EXCEPTION
WHENOTHERS
THEN
dbms_output.put_line(dbms_utility.format_error_backtrace);
WAR_V:=ERROROCCURED||sqlERRM;
END;
ENDMOISS_PAYROLL_INT_API;
P rogram Execution

DECLARE
VAR_VVARCHAR2(500);
begin
MOISS_PAYROLL_INT_API.MOISS_ABSENCE_PAYROLL(202,TO_DATE
(01/02/2014,mm/dd/yyyy),TO_DATE(01/02/2014,
mm/dd/yyyy),VAR_V)
MOISS_PAYROLL_INT_API.MOISS_WORKDAY_OVERTIME_PAYROLL(202,
TO_DATE(01/02/2012,mm/dd/yyyy),TO_DATE
(01/02/2012,mm/dd/yyyy),VAR_V);
MOISS_PAYROLL_INT_API.MOISS_BONUS_OVERTIME_ALL_E(01MAR
2012,31MAR2012);
MOISS_PAYROLL_INT_API.MOISS_ABSENCE_PAYROLL(29,01MAR
2012,31MAR2012);
MOISS_PAYROLL_INT_API.MOISS_WORKSHOP_BONUS_PAYROLL(202,
TO_DATE(01/02/2012,mm/dd/yyyy),TO_DATE
(01/02/2012,mm/dd/yyyy),VAR_V);
dbms_output.put_line(WehavethisError||var_v);
end;

Note:

To run as PLSQL procedure it there is no need for ErrBuf and retcode elements. But they
are mandatory while using in the oracle apps.

----------------------------------

[Link]
[Link]
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;

Common questions

Powered by AI

Customizing APIs for organizational needs is crucial as it ensures that specific business processes are supported effectively within Oracle’s framework. Custom APIs align the software with unique organizational requirements, enhancing operational efficiency and ensuring that the data management processes integrate seamlessly with existing workflows and policies .

SQL queries assist in verifying API operations by allowing developers to execute specific selections to confirm data insertions, updates, or integrity. Queries like those checking PAY_BATCH_HEADERS or xxtest_emp_attendance ensure that the API performs the intended operations and that records are accurately maintained in the respective tables .

The HR_PERSON_ABSENCE_API can be utilized to manage employee absences by inserting, updating, and deleting absence entries in Oracle HRMS. It helps ensure that absences are properly logged and can be retrieved or adjusted as needed. Testing the API functionalities ensures the reliability of these entries by integrating data from custom tables to standard Oracle databases .

Error handling in MOISS_WORKSHOP_BONUS_PAYROLL is managed through exception blocks, which capture and output specific errors using the dbms_output.put_line method. It also uses Oracle's dbms_utility.format_error_backtrace to provide detailed error messages, helping diagnose and resolve issues efficiently .

Challenges include ensuring data consistency, managing transactions and rollbacks efficiently, addressing complex error handling, and maintaining the integrity of business rules across various payroll scenarios. Furthermore, integration requires a deep understanding of both Oracle’s structure and specific business needs to align custom modules accurately .

pay_batch_element_entry_api enables batch processing by allowing the creation of batch headers and lines. It processes bulk data for elements based on assignment sets, which allows efficient handling of payroll batch entries. The API also provides a detailed structure by interfacing with tables like PAY_BATCH_HEADERS and PAY_BATCH_LINES .

MOISS_PAYROLL_INT_API is significant as it encapsulates multiple procedures to handle different payroll needs, such as absence, overtime, and bonuses, through a streamlined interface. This reduces complexity when interacting with Oracle's backend processes, ensuring data integrity and consistency across modules. The API simplifies integration by providing centralized control over payroll procedures .

The key steps using hr_person_absence_api include: navigating to Global HRMS Manager to enter and maintain data, understanding the front end where the data will be saved, using the API to check absence details, and using SQL queries to execute or validate the integration process through tables like PER_ALL_PEOPLE_F .

Testing payroll APIs like hr_person_absence_api can be conducted by executing the API and using SQL queries to verify that correct data entries are made. This involves navigating interface forms, checking data flow into tables, and ensuring functional outputs align with expected trends or records in the HRMS system .

Understanding backend tables and data flow is essential because APIs interact directly with underlying tables to insert or update data. Knowing where data is stored helps ensure the correct mapping of inputs and enhances debugging and testing processes. It ensures that custom data aligns with Oracle's seeded tables for seamless integration .

https://pacesettergraam.wordpress.com/2014/01/23/payroll-intergration-api-absence-and- (https://pacesettergraam.wordpress.com
pay_batch_element_entry_api API inserts data in a batch depending on group of elements or assignment set. The Navigation is
Global HRMS Manager → People → Enter and Maintain → Others → Absence In oracle apps for Integrating the custom data to oracl
Queries Related to HR_PERSON_ABSENCE_API To Get Employee Details from PER_ALL_PEOPLE_F table SELECT * FROM PER_ALL_PE
To Get Employee‘s Absence Details from PER_ALL_PEOPLE_F table SELECT PAPF.PERSON_ID, PAPF.EFFECTIVE_START_DATE, PAPF.EFFECTIV
select * from PAY_ELEMENT_TYPES_F where element_name=‘Overti me OTM Special Inputs’ SELECT * FROM PAY_ELEMENT_TYPES_F WHERE
WHERE PBH.BATCH_ID = PBL.BATCH_ID AND BATCH_NAME LIKE‘%WORKS HOP%’; SELECT PBL.*, PBH.* FROM PAY_BATCH_HEADERS PBH, PAY_B
INSERTINTO xxtest_emp_attendance (employee_id, att_date, workday_overtime, created_by, creation_date, day_type) VALUES(11
att_date, rig_bonus, created_by, creation_date, day_type) VALUES(113, TO_DATE(’01/05/2012′,‘mm/dd/yyyy’), 4, 1310, TO_DATE(’0
START_DATE_V VARCHAR2, END_DATE_V VARCHAR2 ); PROCEDURE MOISS_BONUS_OVERTIME_ALL_E(ERRBUF OUTVARCHAR2, RETCODE OUTNUMBER,

You might also like