UPLOADING DATA TO BATCH ELEMENT ENTRIES USING API

Oracle Payroll: Example to Upload Data in Batch Element Entries Using API.

·          PAY_BATCH_ELEMENT_ENTRY_API

Following are the API’s to create BEE Headers and Lines.

1. To Create Batch Header

DECLARE
l_batch_id                NUMBER := NULL;
l_object_version_number   NUMBER := NULL;
BEGIN
pay_batch_element_entry_api.create_batch_header (
p_session_date            => TRUNC (SYSDATE),
p_batch_name              => <<BATCH_NAME>>,
p_business_group_id       => <<Business_Group_ID>>,
p_action_if_exists        => ‘I’,
p_batch_id                => l_batch_id,
p_object_version_number   => l_object_version_number
);
COMMIT;
DBMS_OUTPUT.put_line (l_batch_id || ‘: SUCCESS’);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘Main Exception: ‘ || SQLERRM);
END;
– QUERY
SELECT   batch_id, batch_name
FROM   pay_batch_headers
ORDER BY   batch_id DESC

2. To Create Batch Line (Batch Element Entry)
DECLARE
v_eff_start_date                DATE := NULL;
v_eff_end_date                  DATE := NULL;
v_input_value_id                NUMBER := NULL;
v_element_link_id               NUMBER := NULL;
v_element_name                  VARCHAR2 (500) := NULL;
v_element_type_id               NUMBER := NULL;
v_batch_id                      NUMBER := NULL;
v_batch_line_id                 NUMBER := NULL;
v_batch_object_version_number   NUMBER := NULL;
CURSOR emp_details
is
Select assignment_number, assignment_id
from <<temp_table>> — table where your data is uploaded.

BEGIN
– BLOCK to Get Batch ID
BEGIN
SELECT   batch_id
INTO   v_batch_id
FROM   pay_batch_headers pbh
WHERE   TRIM (pbh.batch_name) = <<Batch_Name>>;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
– Get Element Details as per your Element
BEGIN
SELECT   pivf.input_value_id,
pelf.element_link_id,
petf.element_name,
petf.element_type_id
INTO   v_input_value_id,
v_element_link_id,
v_element_name,
v_element_type_id
FROM   pay_element_types_f petf,
pay_element_links_f pelf,
pay_input_values_f pivf
WHERE   pelf.element_type_id = petf.element_type_id
AND TRUNC (SYSDATE) BETWEEN petf.effective_start_date
AND  petf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN pelf.effective_start_date
AND  pelf.effective_end_date
AND pelf.payroll_id = <<payroll_id>>
AND pivf.element_type_id = pivf.element_type_id
AND pivf.NAME = <<Name of the Input value>>
AND TRUNC (SYSDATE) BETWEEN pivf.effective_start_date
AND  pivf.effective_end_date
AND pivf.element_type_id = pelf.element_type_id
AND petf.element_name = <<Element_name>>;
EXCEPTION
WHEN OTHERS
THEN
v_input_value_id := NULL;
v_element_link_id := NULL;
END;
FOR i IN emp_details
LOOP
BEGIN
– API to create ELEMENT
pay_batch_element_entry_api.create_batch_line (
p_session_date            => TRUNC (SYSDATE),
p_batch_id                => v_batch_id,
p_assignment_id           => i.assignment_id,
p_assignment_number       => i.assignment_number,
p_date_earned             => TRUNC (SYSDATE),
p_effective_date          => TRUNC (SYSDATE),
p_effective_start_date    => v_eff_start_date,
p_effective_end_date      => v_eff_end_date,
p_element_name            => v_element_name,
p_element_type_id         => v_element_type_id,
p_value_3                 => <<value for the input value>>,
p_batch_line_id           => v_batch_line_id,
p_object_version_number   => v_batch_object_version_number
);
COMMIT;
DBMS_OUTPUT.put_line (v_batch_id || ‘: SUCCESS’);
DBMS_OUTPUT.put_line (v_batch_line_id || ‘: SUCCESS’);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘Exception’||i.assignment_number||’ ‘||SQLERRM);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘Main Exception: ‘ || SQLERRM);
END;
–  QUERY
SELECT   *
FROM   PAY_BATCH_LINES
WHERE   BATCH_LINE_ID = <<batch_line_id>> — Batch_line_id generated above

oNorm� & t l ��� �P� -bottom:.25in;line-height:15.75pt;vertical-align: baseline'>BEGIN
SELECT papf.person_id
INTO ln_person_id
FROM per_all_people_f papf
WHERE papf.employee_number = i.emp_number
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND papf.business_group_id = 81;
EXCEPTION
WHEN OTHERS
THEN
v_error := ‘Loc1:’ || i.emp_number || SQLERRM;
– UPDATE XX_Passport_TBL
– SET load_status = ‘E’,
– error_msg = v_error
– WHERE emp_number = i.emp_number;
ln_person_id := NULL;
END;

IF ln_person_id IS NOT NULL
THEN

BEGIN
DBMS_OUTPUT.put_line (‘Before API’);
hr_person_extra_info_api.create_person_extra_info
(p_validate => FALSE,
p_person_id => ln_person_id,
p_information_type => lc_information_type,
p_pei_information_category => lc_pei_information_category,
p_pei_information1 => lc_passport_number,
p_pei_information2 => lc_profession,
p_pei_information3 => lc_issue_date_g,
p_pei_information4 => lc_exp_date_g,
p_pei_information5 => lc_place_of_issue,
p_pei_information7 => lc_issue_date_h,
p_pei_information8 => lc_exp_date_h,
p_person_extra_info_id => ln_person_extra_info_id,
p_object_version_number => ln_object_version_number
);
COMMIT;
DBMS_OUTPUT.put_line (‘After API’);

UPDATE XX_Passport_TBL
SET load_status = ‘S’,
error_msg = ‘Successfullly Loaded’
WHERE emp_number = i.emp_number;

totalrecords_sucess := totalrecords_sucess + 1;
fnd_file.put_line (fnd_file.output,
‘Success: For Employee Number:=’
|| i.emp_number
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
v_error :=
v_error
|| ‘Loc API :’
|| i.emp_number
|| ln_person_id
|| SQLERRM;

UPDATE XX_Passport_TBL
SET load_status = ‘E’,
error_msg = v_error
WHERE emp_number = i.emp_number;

totalrecords_fail := totalrecords_fail + 1;
fnd_file.put_line (fnd_file.LOG,
‘Failure: For Employee Number:=’
|| i.emp_number
|| ‘Error Message –>’
|| v_error
);
END;
ELSE
UPDATE XX_Passport_TBL
SET load_status = ‘E’,
error_msg = i.emp_number || ‘=>Person Does not exist’
WHERE emp_number = i.emp_number;

totalrecords_fail := totalrecords_fail + 1;
fnd_file.put_line (fnd_file.LOG,
‘Failure: For Employee Number:=’
|| i.emp_number
|| ‘Error Message –>’
|| v_error
);
END IF;

totalrecords_tobeupload := totalrecords_tobeupload + 1;
END LOOP;

fnd_file.put_line (fnd_file.output,
‘TOTAL RECORDS TO BE UPLOAD–>’
|| totalrecords_tobeupload
);
fnd_file.put_line (fnd_file.output,
‘TOTAL SUCCESS RECORDS –>’ || totalrecords_sucess
);
fnd_file.put_line (fnd_file.output,
‘TOTAL FAILURE RECORDS–>’ || totalrecords_fail
);
END xx_emp_paasport_conv_proc;