Payroll Intergration API – Absence and Batch Element Entry


Overview

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 and pay_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

API’s 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

1.

 

The Navigation is

Mass Information Exchange(MIX) → Batch Element Entry → Element Lines

2

HR_PERSON_ABSENCE_API

The Navigation is

Global HRMS Manager → People → Enter and Maintain → Others → Absence

3

In oracle apps for Integrating the custom data to oracle absence table we have the HR_PERSON_ABSENCE_API API to 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

4

 

Queries Related to HR_PERSON_ABSENCE_API

 

 

To Get Employee Details from PER_ALL_PEOPLE_F table

 

SELECT *

FROM PER_ALL_PEOPLE_F PAPF, PER_ALL_ASSIGNMENTS_F PAAF

WHERE PAPF.EMPLOYEE_NUMBER =‘2108’

AND PAPF.CURRENT_EMPLOYEE_FLAG =‘Y’

AND PAPF.EMPLOYEE_NUMBER ISNOTNULL

AND PAAF.PERSON_ID = PAPF.PERSON_ID

ANDSYSDATEBETWEEN PAAF.EFFECTIVE_START_DATE

AND PAAF.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

5

To Get Employee‘s Absence Details from PER_ALL_PEOPLE_F table

SELECT PAPF.PERSON_ID,

PAPF.EFFECTIVE_START_DATE,

PAPF.EFFECTIVE_END_DATE,

PAPF.LAST_NAME,

PAV.CATEGORY_MEANING

FROM PER_ALL_PEOPLE_F PAPF,

PER_ALL_ASSIGNMENTS_F PAAF,

PER_ABSENCE_ATTENDANCES_V PAV

WHERE PAPF.EMPLOYEE_NUMBER =‘2108’

AND PAPF.CURRENT_EMPLOYEE_FLAG =‘Y’

AND PAPF.EMPLOYEE_NUMBER ISNOTNULL

AND PAAF.PERSON_ID = PAPF.PERSON_ID

ANDSYSDATEBETWEEN PAAF.EFFECTIVE_START_DATE

AND PAAF.EFFECTIVE_END_DATE

AND PAV.PERSON_ID = PAPF.PERSON_ID;

 

Queries related to pay_batch_element_entry_api

Getting the Element Type Id and its Information

 

select * from PAY_ELEMENT_TYPES_F where element_name=‘Overtime OTM Special Inputs’

 

SELECT *

FROM PAY_ELEMENT_TYPES_F

WHERE element_name =‘Holiday Pay Special Inputs’;

SELECT *

FROM PAY_ELEMENT_TYPES_F

WHERE element_name =‘Production Bonus’;

 

Getting the info of Batches

SELECT PBH.BATCH_NAME FROM PAY_BATCH_HEADERS PBH, PAY_BATCH_LINES PBL WHERE PBH.BATCH_ID=PBL.BATCH_ID;

SELECT PBL.* ,PBH.* FROM PAY_BATCH_HEADERS PBH, PAY_BATCH_LINES PBL WHERE PBH.BATCH_ID=PBL.BATCH_ID AND BATCH_NAME LIKE‘MY%TEST%’;

Getting the Employee details from custom table

SELECT PAPF.PERSON_ID,

PAPF.EFFECTIVE_START_DATE,

PAPF.EFFECTIVE_END_DATE,

PAPF.LAST_NAME,

PAAF.ASSIGNMENT_ID

FROM PER_ALL_PEOPLE_F PAPF,

PER_ALL_ASSIGNMENTS_F PAAF

WHERE PAPF.EMPLOYEE_NUMBER =‘2108’

AND PAPF.CURRENT_EMPLOYEE_FLAG =‘Y’

AND PAPF.EMPLOYEE_NUMBER ISNOTNULL

AND PAAF.PERSON_ID = PAPF.PERSON_ID

ANDSYSDATEBETWEEN PAAF.EFFECTIVE_START_DATE

AND PAAF.EFFECTIVE_END_DATE;

 

To Check Whether the data is Inserted or not

SELECT PBL.*, PBH.*

FROM PAY_BATCH_HEADERS PBH, PAY_BATCH_LINES PBL

WHERE PBH.BATCH_ID = PBL.BATCH_ID AND BATCH_NAME LIKE‘%WORKSHOP%’;

 

 

SELECT PBL.*, PBH.*

FROM PAY_BATCH_HEADERS PBH, PAY_BATCH_LINES PBL

WHERE PBH.BATCH_ID = PBL.BATCH_ID AND BATCH_NAME LIKE‘%HOLI%’;

 

 

SELECT PBL.*, PBH.*

FROM PAY_BATCH_HEADERS PBH, PAY_BATCH_LINES PBL

WHERE PBH.BATCH_ID = PBL.BATCH_ID AND BATCH_NAME LIKE‘%MOISS_OVERTIME%’;

 

 

 

Insert Statements

 

INSERTINTO xxtest_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’);

 

 

INSERTINTO xxtest_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’);

 

 

 

INSERTINTO xxtest_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’);

 

 

 

INSERTINTO xxtest_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 * from xxtest_emp_attendance

General Util Queries from Oracle Apps.

 

select textActual_Query from user_views where view_name=‘PAY_ELEMENT_TYPES_DENORM_V’;

 

Program using API’s

 

Spec of API

 

CREATEORREPLACEPACKAGEMOISS_PAYROLL_INT_API

IS

PROCEDURE MOISS_ABSENCE_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

EMP_NO NUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2

);

 

 

PROCEDURE MOISS_BONUS_OVERTIME_ALL_E(ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2

);

 

PROCEDURE MOISS_WORKDAY_OVERTIME_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2

);

 

 

PROCEDURE MOISS_HOLIDAY_OVERTIME_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V DATE,

END_DATE_V DATE

);

 

PROCEDURE MOISS_WORKSHOP_BONUS_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V DATE,

END_DATE_V DATE

);

 

 

PROCEDURE MOISS_RIG_BONUS_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V DATE,

END_DATE_V DATE

);

END;

 

 

 

 

 

Body of API

 

 

/* Formatted on 1/22/2014 11:22:14 AM (QP5 v5.114.809.3010) */

CREATEORREPLACEPACKAGEBODYMOISS_PAYROLL_INT_API

AS

PROCEDURE MOISS_ABSENCE_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

EMP_NO NUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2

)

IS

CURSOR c

IS

SELECTDISTINCT EMPLOYEE_ID, ATT_DATE

FROMmoiss_emp_attendance

WHERE day_type =‘AB’

AND ATT_DATE BETWEENTRUNC(TO_DATE(START_DATE_V,

‘RRRR/MM/DD HH24:MI:SS’))

ANDTRUNC(TO_DATE( END_DATE_V,‘RRRR/MM/DD HH24:MI:SS’))

AND employee_id = EMP_NO;

 

–SELECT * FROM moiss_emp_attendance

 

x_absence_days number;

x_absence_hours number:=10;

— :=ABS_HOUR_V; If applicable we have to include

x_absence_attendance_type_id number;–ABSENCE ATTENDANCE TYPE ID

— := ATT_TYPE_ID_V; If applicable we have to include

x_absence_attendance_id number;

x_object_version_number number;

x_occurrence number;

x_dur_dys_less_warning boolean;

x_dur_hrs_less_warning boolean;

x_exceeds_pto_entit_warning boolean;

x_exceeds_run_total_warning boolean;

x_abs_overlap_warning boolean;

x_abs_day_after_warning boolean;

x_dur_overwritten_warning boolean;

x_con_count number;

BEGIN

retcode :=0;

errbuf :=‘Success’;

x_con_count :=0;

fnd_file.put_line (fnd_file.LOG,‘Start’);

 

DBMS_OUTPUT.PUT_LINE(

‘THE values ARE IS ‘ || START_DATE_V || END_DATE_V

);

 

— FND_FILE.PUT_LINE(fnd_file.LOG, )

 

–To keep check for the Concurrent —

x_con_count :=0;

–Counts the loop of number that is total number of loops that has to be going

 

 

fnd_global.apps_initialize (user_id =>fnd_global.user_id,

resp_id =>fnd_global.resp_id,

resp_appl_id =>fnd_global.resp_appl_id);

 

SELECT absence_attendance_type_id

INTO x_absence_attendance_type_id

FROM per_absence_attendance_types

WHERE business_group_id =fnd_global.per_business_group_id

ANDnameLIKE‘%Personal%’

ANDROWNUM=1;

 

 

FOR I IN C

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, —VACATION LEAVE

p_date_projected_start => I.ATT_DATE,

p_time_projected_start =>NULL,

–If applicable we have to include

p_date_projected_end => I.ATT_DATE,

p_time_projected_end =>NULL,

–If applicable we have to include

p_date_start => I.ATT_DATE,

p_time_start =>NULL,

–If applicable we have to include ’18:00′,

p_date_end => I.ATT_DATE,

p_time_end =>NULL,

–If applicable we have to include ’20: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

|| ‘-‘

|| ‘Employee ID – ‘

|| I.EMPLOYEE_ID

|| ‘ – – Att Date- – – ‘

|| I.ATT_DATE

);

ENDLOOP;

 

IF x_con_count =0

THEN

fnd_file.put_line (fnd_file.LOG,‘No Data Found’);

ENDIF;

 

COMMIT;

EXCEPTION

WHENNO_DATA_FOUND

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

retcode :=1;

errbuf :=

‘NO DATA FOUND’

|| SUBSTR(SQLERRM,1,1000)

|| DBMS_UTILITY.format_error_backtrace;

— WAR_V := errbuf;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

WHENOTHERS

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

retcode :=1;

errbuf :=

‘Error in Payroll ABSENCE Integration’

|| SUBSTR(SQLERRM,1,1000)

|| DBMS_UTILITY.format_error_backtrace;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

 

— WAR_V := errbuf;

END MOISS_ABSENCE_PAYROLL;

 

— ————————————————————————————————————————————————————————————————————————————————————

 

PROCEDURE MOISS_BONUS_OVERTIME_ALL_E(ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2

)

IS

–ERRBUF VARCHAR2(100),

— RETCODE OUT NUMBER,

BEGIN

fnd_file.put_line (fnd_file.LOG,‘Procedure1 Started’);

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, RETCODE, START_DATE_V, END_DATE_V );

MOISS_PAYROLL_INT_API.MOISS_RIG_BONUS_PAYROLL(ERRBUF, RETCODE, START_DATE_V, END_DATE_V);

fnd_file.put_line (fnd_file.LOG,‘Procedure1 Ended’);

EXCEPTION

whenothersthen

fnd_file.put_line (fnd_file.LOG,‘Exe Env’ || DBMS_UTILITY.format_error_backtrace);

END MOISS_BONUS_OVERTIME_ALL_E;

 

 

 

PROCEDURE MOISS_WORKDAY_OVERTIME_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2

)

IS

v_batch_id varchar2(60);

x_batch_id number:=0;

x_ver_no number;

x_batch_line_id number;

g_ver_no number;

x_ver_no_1 number;

x_con_count number;

x_element_type_id number;

 

CURSOR c

IS

SELECTDISTINCT att_date,

WORKDAY_OVERTIME,

PAF.ASSIGNMENT_ID,

paf.assignment_number

FROMmoiss_emp_attendance mea, per_assignments_f paf

WHERE paf.person_id = mea.employee_id

AND WORKDAY_OVERTIME ISNOTNULL

AND ATT_DATE BETWEEN START_DATE_V AND END_DATE_V;

BEGIN

–retcode := 0;

–errbuf := ‘Success’;

x_con_count :=0;

fnd_file.put_line (fnd_file.LOG,‘Start’);

 

DBMS_OUTPUT.PUT_LINE(

‘THE values ARE IS ‘ || START_DATE_V || END_DATE_V

);

 

— FND_FILE.PUT_LINE(fnd_file.LOG, )

 

–To keep check for the Concurrent —

x_con_count :=0;

–Counts the loop of number that is total number of loops that has to be going

 

 

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)INTO v_batch_id FROM DUAL;

 

SELECT ELEMENT_TYPE_ID

INTO x_element_type_id

FROM PAY_ELEMENT_TYPES_F

WHERE element_name LIKE‘%Overtime OTM Special Inputs%’

ANDROWNUM=1;

 

FOR i IN c

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, 101 — business_group_id

p_action_if_exists =>‘I’,

— i = create new entry, u = update entry

p_batch_reference =>NULL,

–g_user_name||’ – ‘||to_char(sysdate,’ddmonyyyy’)

p_batch_source =>NULL,–name of the batch

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,

–Overtime OTM Special Inputs

p_entry_type =>‘E’,

p_value_1 => i.WORKDAY_OVERTIME,

–holiday over time hrs–

p_value_2 =>’15’,–Assuming Value

–regular over time hrs– ,

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 || ‘-‘ || ‘Employee ID – ‘ || I.WORKDAY_OVERTIME ||

I.ASSIGNMENT_ID || I.assignment_number || ‘ – – Att Date- – – ‘

|| I.ATT_DATE

);

ENDLOOP;

 

IF x_con_count =0

THEN

fnd_file.put_line (fnd_file.LOG,‘No Data Found’);

ENDIF;

 

COMMIT;

EXCEPTION

WHENNO_DATA_FOUND

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

–retcode := -1;

–errbuf :=

— ‘NO DATA FOUND’

— || SUBSTR (SQLERRM, 1, 1000)

— || DBMS_UTILITY.format_error_backtrace;

— WAR_V := errbuf;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

WHENOTHERS

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

— retcode := -1;

— errbuf :=

— ‘Error in Payroll ABSENCE Integration’

— || SUBSTR (SQLERRM, 1, 1000)

— || DBMS_UTILITY.format_error_backtrace;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

 

— WAR_V := errbuf;

END;

 

————————————————————————————————————————————————————————————————————————————————————

 

 

 

PROCEDURE MOISS_HOLIDAY_OVERTIME_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V DATE,

END_DATE_V DATE

)

IS

v_batch_id VARCHAR2(60);

x_batch_id number;

x_ver_no number;

x_batch_line_id number;

g_ver_no number;

x_ver_no_1 number;

x_con_count number;

x_element_type_id number;

 

CURSOR c

IS

SELECTDISTINCT att_date,

HOLIDAY_OVERTIME,

PAF.ASSIGNMENT_ID,

paf.assignment_number

FROMmoiss_emp_attendance mea, per_assignments_f paf

WHERE paf.person_id = mea.employee_id

AND MEA.HOLIDAY_OVERTIME ISNOTNULL

AND ATT_DATE BETWEEN START_DATE_V AND END_DATE_V;

BEGIN

retcode :=0;

errbuf :=‘Success’;

x_con_count :=0;

fnd_file.put_line (fnd_file.LOG,‘Start’);

 

DBMS_OUTPUT.PUT_LINE(

‘THE values ARE IS ‘ || START_DATE_V || END_DATE_V

);

 

— FND_FILE.PUT_LINE(fnd_file.LOG, )

 

–To keep check for the Concurrent —

x_con_count :=0;

–Counts the loop of number that is total number of loops that has to be going

 

 

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)INTO v_batch_id FROM DUAL;

 

SELECT ELEMENT_TYPE_ID

INTO x_element_type_id

FROM PAY_ELEMENT_TYPES_F

WHERE element_name LIKE‘%OTL Holiday Pay%’ANDROWNUM=1;

 

 

 

FOR i IN c

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, 101 — business_group_id

p_action_if_exists =>‘I’,

— i = create new entry, u = update entry

p_batch_reference =>NULL,

–g_user_name||’ – ‘||to_char(sysdate,’ddmonyyyy’)

p_batch_source =>NULL,–name of the batch

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,

–Earnings paid for Holiday-straight time)

p_entry_type =>‘E’,

p_value_1 => i.HOLIDAY_OVERTIME,

–holiday over time hrs–

p_value_2 =>15,

–Assuming Value (Values to be noted……)

–regular over time hrs– ,

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 || ‘-‘ || ‘Employee ID – ‘ || I.HOLIDAY_OVERTIME ||

I.ASSIGNMENT_ID || I.assignment_number || ‘ – – Att Date- – – ‘

|| I.ATT_DATE

);

ENDLOOP;

 

IF x_con_count =0

THEN

fnd_file.put_line (fnd_file.LOG,‘No Data Found’);

ENDIF;

 

COMMIT;

EXCEPTION

WHENNO_DATA_FOUND

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

retcode :=1;

errbuf :=

‘NO DATA FOUND’

|| SUBSTR(SQLERRM,1,1000)

|| DBMS_UTILITY.format_error_backtrace;

— WAR_V := errbuf;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

retcode :=1;

errbuf :=

‘Error in Payroll ABSENCE Integration’

|| SUBSTR(SQLERRM,1,1000)

|| DBMS_UTILITY.format_error_backtrace;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

 

— WAR_V := errbuf;

END;

 

————————————————————————————————————————————————————————————————————————————————————

 

PROCEDURE MOISS_WORKSHOP_BONUS_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V DATE,

END_DATE_V DATE

)

IS

v_batch_id VARCHAR2(60);

x_batch_id number;

x_ver_no number;

x_batch_line_id number;

g_ver_no number;

x_ver_no_1 number;

x_con_count number;

x_element_type_id number;

 

CURSOR c

IS

SELECTDISTINCT att_date,

WORKSHOP_BONUS,

PAF.ASSIGNMENT_ID,

paf.assignment_number

FROMmoiss_emp_attendance mea, per_assignments_f paf

WHERE paf.person_id = mea.employee_id

AND MEA.WORKSHOP_BONUS ISNOTNULL

AND ATT_DATE BETWEEN START_DATE_V AND END_DATE_V;

BEGIN

retcode :=0;

errbuf :=‘Success’;

x_con_count :=0;

fnd_file.put_line (fnd_file.LOG,‘Start’);

 

DBMS_OUTPUT.PUT_LINE(

‘THE values ARE IS ‘ || START_DATE_V || END_DATE_V

);

 

— FND_FILE.PUT_LINE(fnd_file.LOG, )

 

–To keep check for the Concurrent —

x_con_count :=0;

–Counts the loop of number that is total number of loops that has to be going

 

fnd_global.apps_initialize (user_id =>fnd_global.user_id,

resp_id =>fnd_global.resp_id,

resp_appl_id =>fnd_global.resp_appl_id);

SELECT TO_CHAR(SYSDATE)INTO v_batch_id FROM DUAL;

 

SELECT ELEMENT_TYPE_ID

INTO x_element_type_id

FROM PAY_ELEMENT_TYPES_F

WHERE element_name LIKE‘%Production Bonus%’ANDROWNUM=1;

FOR i IN c

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, 101 — business_group_id

p_action_if_exists =>‘I’,

— i = create new entry, u = update entry

p_batch_reference =>NULL,

–g_user_name||’ – ‘||to_char(sysdate,’ddmonyyyy’)

p_batch_source =>NULL,–name of the batch

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,–Production Bonus

p_entry_type =>‘E’,

p_value_1 => i.WORKSHOP_BONUS,

–holiday over time hrs–

p_value_2 =>15,

–Assuming Value (Values to be noted……)

–regular over time hrs– ,

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 || ‘-‘ || ‘Employee ID – ‘ || I.WORKSHOP_BONUS ||

I.ASSIGNMENT_ID || I.assignment_number || ‘ – – Att Date- – – ‘

|| I.ATT_DATE

);

ENDLOOP;

 

IF x_con_count =0

THEN

fnd_file.put_line (fnd_file.LOG,‘No Data Found’);

ENDIF;

 

COMMIT;

EXCEPTION

WHENNO_DATA_FOUND

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

retcode :=1;

errbuf :=

‘NO DATA FOUND’

|| SUBSTR(SQLERRM,1,1000)

|| DBMS_UTILITY.format_error_backtrace;

— WAR_V := errbuf;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

WHENOTHERS

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

retcode :=1;

errbuf :=

‘Error in Payroll ABSENCE Integration’

|| SUBSTR(SQLERRM,1,1000)

|| DBMS_UTILITY.format_error_backtrace;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

 

— WAR_V := errbuf;

END;

 

PROCEDURE MOISS_RIG_BONUS_PAYROLL (ERRBUF OUTVARCHAR2,

RETCODE OUTNUMBER,

START_DATE_V DATE,

END_DATE_V DATE

)

IS

v_batch_id VARCHAR2(60);

x_batch_id number;

x_ver_no number;

x_batch_line_id number;

g_ver_no number;

x_ver_no_1 number;

x_con_count number;

x_element_type_id number;

 

CURSOR c

IS

SELECTDISTINCT att_date,

RIG_BONUS,

PAF.ASSIGNMENT_ID,

paf.assignment_number

FROMmoiss_emp_attendance mea, per_assignments_f paf

WHERE paf.person_id = mea.employee_id

AND MEA.WORKSHOP_BONUS ISNOTNULL

AND ATT_DATE BETWEEN START_DATE_V AND END_DATE_V;

BEGIN

retcode :=0;

errbuf :=‘Success’;

x_con_count :=0;

fnd_file.put_line (fnd_file.LOG,‘Start’);

 

DBMS_OUTPUT.PUT_LINE(

‘THE values ARE IS ‘ || START_DATE_V || END_DATE_V

);

 

— FND_FILE.PUT_LINE(fnd_file.LOG, )

 

–To keep check for the Concurrent —

x_con_count :=0;

–Counts the loop of number that is total number of loops that has to be going

 

fnd_global.apps_initialize (user_id =>fnd_global.user_id,

resp_id =>fnd_global.resp_id,

resp_appl_id =>fnd_global.resp_appl_id);

SELECT TO_CHAR(SYSDATE)INTO v_batch_id FROM DUAL;

SELECT ELEMENT_TYPE_ID

INTO x_element_type_id

FROM PAY_ELEMENT_TYPES_F

WHERE element_name LIKE‘%Production Bonus%’AND ROWNUM=1;

FOR i IN c

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, 101 — business_group_id

p_action_if_exists =>‘I’,

— i = create new entry, u = update entry

p_batch_reference =>NULL,

–g_user_name||’ – ‘||to_char(sysdate,’ddmonyyyy’)

p_batch_source =>NULL,–name of the batch

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,

–Production Bonus (Has to be changed for Rig Bonus)

p_entry_type =>‘E’,

p_value_1 => i.RIG_BONUS,

–holiday over time hrs–

p_value_2 =>15,

–Assuming Value (Values to be noted……)

–regular over time hrs– ,

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 || ‘-‘ || ‘Employee ID – ‘ || I.RIG_BONUS || I.

ASSIGNMENT_ID || I.assignment_number || ‘ – – Att Date- – – ‘ || I.

ATT_DATE );

END LOOP;

IF x_con_count =0

THEN

fnd_file.put_line (fnd_file.LOG,‘No Data Found’);

ENDIF;

COMMIT;

EXCEPTION

WHENNO_DATA_FOUND

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

retcode :=1;

errbuf :=

‘NO DATA FOUND’

|| SUBSTR(SQLERRM,1,1000)

|| DBMS_UTILITY.format_error_backtrace;

— WAR_V := errbuf;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

WHENOTHERS

THEN

DBMS_OUTPUT.PUT_LINE(‘No data found’);

retcode :=1;

errbuf :=

‘Error in Payroll ABSENCE Integration’

|| SUBSTR(SQLERRM,1,1000)

|| DBMS_UTILITY.format_error_backtrace;

fnd_file.put_line (

fnd_file.LOG,

‘No Data Found’ || DBMS_UTILITY.format_error_backtrace

);

— WAR_V := errbuf;

END;

END MOISS_PAYROLL_INT_API;

Program Body for Testing or Sample data Test

 

CREATEORREPLACEPACKAGEBODYMOISS_PAYROLL_INT_API

AS

PROCEDURE MOISS_ABSENCE_PAYROLL (BUISNESS_G_V NUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2,

WAR_V OUTVARCHAR2)

IS

CURSOR c

IS

SELECTDISTINCT EMPLOYEE_ID, ATT_DATE

FROMmoiss_emp_attendance

WHERE day_type =‘WO’

AND ATT_DATE BETWEENTO_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’)

AND employee_id =30;

 

–SELECT * FROM moiss_emp_attendance

— x_START_DATE_V date=’10/18/2013′;

x_absence_days number;

x_absence_hours number:=10;

— :=ABS_HOUR_V; If applicable we have to include

X_absence_attendance_type_id number:=7;

— := ATT_TYPE_ID_V; If applicable we have to include

x_absence_attendance_id number;

x_object_version_number number;

x_occurrence number;

x_dur_dys_less_warning boolean;

x_dur_hrs_less_warning boolean;

x_exceeds_pto_entit_warning boolean;

x_exceeds_run_total_warning boolean;

x_abs_overlap_warning boolean;

x_abs_day_after_warning boolean;

x_dur_overwritten_warning boolean;

BEGIN

 

DBMS_OUTPUT.PUT_LINE(‘THE values ARE IS ‘||BUISNESS_G_V||START_DATE_V||END_DATE_V);

FOR I IN C

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, —VACATION LEAVE

p_date_projected_start => I.ATT_DATE,

p_time_projected_start =>NULL,

–If applicable we have to include

p_date_projected_end => I.ATT_DATE,

p_time_projected_end =>NULL,

–If applicable we have to include

p_date_start => I.ATT_DATE,

p_time_start =>NULL,

–If applicable we have to include ’18:00′,

p_date_end => I.ATT_DATE,

p_time_end =>NULL,

–If applicable we have to include ’20: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 :=‘ERROR OCCURED’||sqlERRM;

END MOISS_ABSENCE_PAYROLL;

————————————————————————————————————————————————————————————————————————————————————

 

/* Formatted on 1/16/2014 6:13:49 PM (QP5 v5.114.809.3010) */

PROCEDURE MOISS_WORKDAY_OVERTIME_PAYROLL (BUISNESS_G_V NUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2,

WAR_V OUTVARCHAR2)

IS

v_batch_id varchar2(60);

x_batch_id number:=0;

x_ver_no number;

x_batch_line_id number;

g_ver_no number;

x_ver_no_1 number;

 

CURSOR c

IS

SELECT DISTINCT

WORKDAY_OVERTIME, PAF.ASSIGNMENT_ID, paf.assignment_number

FROM xxtest_emp_attendance mea, per_assignments_f paf

WHERE paf.person_id = mea.employee_id

AND WORKDAY_OVERTIME ISNOTNULL;

BEGIN

SELECT TO_CHAR(SYSDATE)INTO v_batch_id FROM DUAL;

FOR i IN c

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, 101 — business_group_id

p_action_if_exists =>‘I’,

— i = create new entry, u = update entry

p_batch_reference =>NULL,

–g_user_name||’ – ‘||to_char(sysdate,’ddmonyyyy’)

p_batch_source =>NULL,–name of the batch

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,–Overtime OTM Special Inputs

p_entry_type =>‘E’,

p_value_1 => i.WORKDAY_OVERTIME,

–holiday over time hrs–

p_value_2 =>’15’,–Assuming Value

–regular over time hrs– ,

p_batch_line_id => x_batch_line_id,

p_object_version_number => x_ver_no_1

);

END LOOP;

COMMIT;

EXCEPTION

WHENOTHERS

THEN

WAR_V :=‘ERROR OCCURED’||sqlERRM;

dbms_output.put_line(dbms_utility.format_error_backtrace);

END;

————————————————————————————————————————————————————————————————————————————————————

PROCEDURE MOISS_HOLIDAY_OVERTIME_PAYROLL (BUISNESS_G_V NUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2,

WAR_V OUTVARCHAR2)

IS

v_batch_id VARCHAR2(60);

x_batch_id number;

x_ver_no number;

x_batch_line_id number;

g_ver_no number;

x_ver_no_1 number;

CURSOR c

IS

SELECT DISTINCT

HOLIDAY_OVERTIME, PAF.ASSIGNMENT_ID, paf.assignment_number

FROM xxtest_emp_attendance mea, per_assignments_f paf

WHERE paf.person_id = mea.employee_id

AND MEA.HOLIDAY_OVERTIME ISNOTNULL;

 

BEGIN

SELECT TO_CHAR(SYSDATE)INTO v_batch_id FROM DUAL;

FOR i IN c

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, 101 — business_group_id

p_action_if_exists =>‘I’,

— i = create new entry, u = update entry

p_batch_reference =>NULL,

–g_user_name||’ – ‘||to_char(sysdate,’ddmonyyyy’)

p_batch_source =>NULL,–name of the batch

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,–Earnings paid for Holiday-straight time)

p_entry_type =>‘E’,

p_value_1 => i.HOLIDAY_OVERTIME,

–holiday over time hrs–

p_value_2 =>15,–Assuming Value (Values to be noted……)

–regular over time hrs– ,

p_batch_line_id => x_batch_line_id,

p_object_version_number => x_ver_no_1

);

ENDLOOP;

COMMIT;

 

EXCEPTION

WHEN OTHERS

THEN

dbms_output.put_line(dbms_utility.format_error_backtrace);

WAR_V :=‘ERROR OCCURED’||sqlERRM;

END;

————————————————————————————————————————————————————————————————————————————————————

PROCEDURE MOISS_WORKSHOP_BONUS_PAYROLL (BUISNESS_G_V NUMBER,

START_DATE_V VARCHAR2,

END_DATE_V VARCHAR2,

WAR_V OUTVARCHAR2)

IS

v_batch_id VARCHAR2(60);

x_batch_id number;

x_ver_no number;

x_batch_line_id number;

g_ver_no number;

x_ver_no_1 number;

 

CURSOR c

IS

 

SELECT DISTINCT

WORKSHOP_BONUS, PAF.ASSIGNMENT_ID, paf.assignment_number

FROM xxtest_emp_attendance mea, per_assignments_f paf

WHERE paf.person_id = mea.employee_id

AND MEA.WORKSHOP_BONUS ISNOTNULL;

 

BEGIN

 

SELECTTO_CHAR(SYSDATE)INTO v_batch_id FROM DUAL;

 

FOR i IN c

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, 101 — business_group_id

p_action_if_exists =>‘I’,

— i = create new entry, u = update entry

p_batch_reference =>NULL,

–g_user_name||’ – ‘||to_char(sysdate,’ddmonyyyy’)

p_batch_source =>NULL,–name of the batch

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,–Production Bonus

p_entry_type =>‘E’,

p_value_1 => i.WORKSHOP_BONUS,

–holiday over time hrs–

p_value_2 =>15,–Assuming Value (Values to be noted……)

–regular over time hrs– ,

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 :=‘ERROR OCCURED’||sqlERRM;

END;

END MOISS_PAYROLL_INT_API;

 

Program Execution

 

DECLARE

VAR_V VARCHAR2(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(’01-MAR-2012′,’31-MAR-2012′);

MOISS_PAYROLL_INT_API.MOISS_ABSENCE_PAYROLL(29,’01-MAR-2012′,’31-MAR-2012′);

–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(‘We have this Error’||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.

 

Now we have to deploy this procedure. Just get the last post which is deployment.

About pacesettergraam

A good and realistic person

Posted on January 23, 2014, in OAF, oracle apps and tagged , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: