Uploading Data to Batch Element Entries

Uploading Data to Batch Element Entries

Migrating Data from Legacy System to Oracle Apps is one hell of a task, i am sure you all would agree to that, and for the same reason Oracle provides a lot of options to make our lives easier. With options like WebADI, Pl/SQL APIs, third party Data Loaders,Data Warehousing, etc uploading data onto Oracle Apps becomes really easy and fast.

Here we are going to see one such example, Uploading Data to Batch Element Entries. We would be uploading data to this in two ways, WebADI and PAY_BATCH_ELEMENT_ENTRY_API

• WebADI

Steps;

1. Open the Batch Element Entry form (HRMS Super User -> Mass Information eXchange: MIX -> Batch Element Entry)

2. Create a Batch that you would like to create Entries for. Check appropriate Batch Control Options and Save.

3. Now Navigate to the BEE Spreadsheeter function (HRMS Super User -> Mass Information eXchange: MIX -> Bee Spreadsheeter). A JSP page will open, as shown below.

4. Select the Batch you just created under the Batch Name field and click Go.

5. Select the Batch and choose the Batch Process throught the LOV. In this case, Create Lines

6. Click on the Process button. You would be redirected to a new page, where you will be prompted to enter the Name of the Element for which you want to create the Batch Element Entries.

7. Select the Element and click Go.

8. A .xl file will download onto your machine. Open the file and it will load onto your machine a template for uploading the Data.(Make sure Macros are allowed in you Microsoft Office.)

9. In Microsoft Office Excel

i. Document Loads : Template is created.

ii. Fill in the template with the data.

iii. Upload the Data

iv. Select the appropriate Parameters and click Upload

v. Once the Data is uploaded, you will get a confirmation.

Your work here is done.

Navigate back to the Batch Element Entry Screen and query for the Batch. Click on the Element Lines button to check the Element Entries.

You can now process the Batch to Validate and Transfer this BEE Lines to Assignment Entries.

• 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