API - pay_element_entry_api.create_element_entry
set serveroutput on;
DECLARE
l_date date := '01-APR-2020'; /*Start date of element*/
x NUMBER := 0;
input_value_id2 number;
input_value_id3 number;
LV_ERROR_MSG VARCHAR2(240);
LD_EFFECTIVE_START_DATE DATE;
LD_EFFECTIVE_END_DATE DATE;
LN_ELEMENT_ENTRY_ID NUMBER;
LN_OBJECT_VERSION_NUMBER NUMBER;
LB_CREATE_WARNING BOOLEAN;
lb_delete_warning BOOLEAN;
Cursor CREATE_ELEMENT_CUR Is
SELECT
emd.emp_name,
emd.employee_number,
paaf.assignment_id,
paaf.object_version_number,
emd.person_id,
emd.start_date,
ead.end_date,
emd.total_amt march_da,
ead.total_amt april_da,
emd.FIRST_NAME,
emd.TIME_PERIOD_ID,
emd.EMPLOYMENT_CATEGORY,
( ead.total_amt - emd.total_amt) difference_in_da,
(select pelf.element_link_id
from PAY_ELEMENT_LINKS_F PELF,pay_element_types_f petf2
where petf2.element_type_id = pelf.element_type_id
and petf2.element_name = 'Dearness Allowance Adjustment') element_link_id /*Name of Element */
,(select pivf2.input_value_id input_value_id1
from pay_input_values_f pivf2, pay_element_types_f petf2
where petf2.element_name = 'Dearness Allowance Adjustment'
AND pivf2.element_type_id = petf2.element_type_id
AND pivf2.NAME = 'Pay Value') input_value_id1
FROM
emp_mar_da emd,
emp_apr_da ead,
per_All_assignments_f paaf
WHERE
emd.person_id = ead.person_id
and emd.person_id=paaf.person_id
AND sysdate BETWEEN TRUNC(paaf.effective_start_date) AND TRUNC(paaf.effective_end_date)
;
BEGIN
FOR i in CREATE_ELEMENT_CUR
LOOP
/*Create custom table to get errors or success message and status if any as per requirements*/
insert into XXEMP_UPD values(
i.emp_name,
i.PERSON_ID,
i.EMPLOYMENT_CATEGORY,
i.FIRST_NAME,
i.EMPLOYEE_NUMBER,
i.difference_in_da,
i.START_DATE,
i.END_DATE,
null,
null,
i.march_da,
i.april_da);
input_value_id1 := i.input_value_id1;
BEGIN
pay_element_entry_api.create_element_entry
( -- Input data elements
-- -----------------------------
p_effective_date => l_date ,
P_BUSINESS_GROUP_ID => 83, -- Put BUSINESS_GROUP_ID as per client
P_ASSIGNMENT_ID => i.assignment_id,
P_ELEMENT_LINK_ID => i.element_link_id, --Link_id of User Declared Income Tax
p_entry_type => 'E',
P_INPUT_VALUE_ID1 => i.input_value_id1, --Input_Value_Id for entry_value1
P_ENTRY_VALUE1 => i.difference_in_da,
-- Output data elements
-----------------------------------
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_element_entry_id => ln_element_entry_id,
p_object_version_number => ln_object_version_number,
p_create_warning => lb_create_warning,
P_OVERRIDE_USER_ENT_CHK => 'Y' );
x := x + 1;
update XXEMP_UPD EMAU set EMAU.ELEMENT_STATUS = 'S' , EMAU.ERROR_MSG ='Success'
where i.person_id = EMAU.person_id;
EXCEPTION WHEN OTHERS THEN
LV_ERROR_MSG:=SUBSTR(SQLERRM,1,230);
update XXEMP_UPD EMAU set EMAU.ELEMENT_STATUS = 'E' , EMAU.ERROR_MSG = LV_ERROR_MSG
where i.person_id = EMAU.person_id;
dbms_output.put_line(SQLERRM);
END;
END LOOP;
COMMIT;
dbms_output.put_line('Employees Processed: '||x);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
COMMIT;
END;
/**Comment below for any clarification**/
