Uploading Data to Batch Element Entries using API

Posted on by

 

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