Scribd
Upload
4 views2 pages

Oracle PL/SQL Delete Pay Elements

The document contains a PL/SQL script that defines a cursor to fetch employee payroll data from multiple tables based on specific criteria. It then iterates through the fetched records to delete payroll element entries using the 'pay_element_entry_api.delete_element_entry' procedure, committing each deletion and handling any exceptions that may occur. The script is designed to operate within a specific date range and for a particular payroll ID.

Uploaded by

Amit
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
4 views2 pages

Oracle PL/SQL Delete Pay Elements

The document contains a PL/SQL script that defines a cursor to fetch employee payroll data from multiple tables based on specific criteria. It then iterates through the fetched records to delete payroll element entries using the 'pay_element_entry_api.delete_element_entry' procedure, committing each deletion and handling any exceptions that may occur. The script is designed to operate within a specific date range and for a particular payroll ID.

Uploaded by

Amit
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

DECLARE

CURSOR fetch_det
IS
SELECT ppf.EMPLOYEE_NUMBER,
ppf.FULL_NAME, -- pasf.assignment_number , pasf.assignment_id ,
petf.ELEMENT_NAME,
peevf.SCREEN_ENTRY_VALUE,
peef.source_start_date,
peef.source_end_date,
peef.element_entry_id,
peef.object_version_number
FROM apps.pay_element_entries_f peef,
apps.per_all_assignments_f pasf,
apps.pay_element_types_f petf,
apps.pay_element_entry_values_f peevf,
apps.pay_input_values_f pivf,
apps.per_all_people_f ppf
WHERE pasf.ASSIGNMENT_ID = peef.ASSIGNMENT_ID
AND ppf.PERSON_ID = pasf.PERSON_ID
AND ppf.CURRENT_EMPLOYEE_FLAG = 'Y'
AND petf.ELEMENT_TYPE_ID = peef.ELEMENT_TYPE_ID
AND peef.EFFECTIVE_START_DATE BETWEEN pasf.EFFECTIVE_START_DATE
AND pasf.EFFECTIVE_END_DATE
AND peef.CREATOR_TYPE IN ('EE', 'RR')
AND peevf.ELEMENT_ENTRY_ID = peef.ELEMENT_ENTRY_ID
AND pivf.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID
AND peevf.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID
AND peef.ELEMENT_TYPE_ID = pivf.ELEMENT_TYPE_ID
AND SYSDATE BETWEEN ppf.EFFECTIVE_START_DATE
AND ppf.EFFECTIVE_END_DATE
AND pasf.PRIMARY_FLAG = 'Y'
AND [Link] = 'Pay Value'
AND pasf.PAYROLL_ID = 62
AND TRUNC (peef.CREATION_DATE) BETWEEN '20-AUG-2024' AND '21-AUG-2024'
AND peef.source_end_date <= '31-DEC-2023'
and peef.created_by=7526;

l_effective_start_date DATE := NULL;


l_effective_end_date DATE := NULL;
l_element_entry_id NUMBER := NULL;
l_delete_warning BOOLEAN;
l_err_msg VARCHAR2 (500) := NULL;
BEGIN
FOR i IN fetch_det
LOOP
BEGIN
pay_element_entry_api.delete_element_entry (
p_validate => FALSE,
p_datetrack_delete_mode => 'ZAP', -- ZAP to Purge from Database
p_effective_date => TO_DATE ('01-AUG-2024'),
p_element_entry_id => i.element_entry_id,
p_object_version_number => i.object_version_number,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_delete_warning => l_delete_warning
);
COMMIT;
DBMS_OUTPUT.put_line (
'Element has been deleted: ' || i.element_entry_id
);
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := SQLERRM;
DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := SQLERRM;
DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);
END;

You might also like