Step 1: Rollback Enhanced
RetroPay in Error
DECLARE
CURSOR c1
IS
select * from pay_assignment_actions where payroll_action_id = 40825 and action_status = 'E';
BEGIN
FOR i IN c1
LOOP
py_rollback_pkg.
rollback_ass_action (
p_assignment_action_id =>
i.assignment_action_id,
p_rollback_mode =>
'ROLLBACK');
-- COMMIT;
DBMS_OUTPUT.
put_line (
i.assignment_action_id || 'Assg Action has been Rolled
Back !!!');
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
Step 2: Update RetroPay Assignment
Status to Deferred
DECLARE
CURSOR c1
IS
SELECT *
FROM pay_retro_assignments pra
WHERE assignment_id = 61
AND (SELECT hl.meaning
FROM hr_lookups hl
WHERE lookup_type = 'ADVANCE_RETRO_STATUS'
AND hl.lookup_code = pra.approval_status) =
'Awaiting Processing';
l_approval_status VARCHAR2
(1) := 'D'; -- Deferred
BEGIN
FOR i IN c1
LOOP
BEGIN
pay_retro_status_internal.
update_retro_asg (p_retro_assignment_id =>
i.retro_assignment_id,
p_reprocess_date =>
i.reprocess_date,
p_start_date =>
i.start_date,
p_approval_status =>
'D');
COMMIT;
DBMS_OUTPUT.
put_line (i.retro_assignment_id || ' --> Status Changed !!!');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.
put_line (
'Inner Exception: '
|| SQLERRM
|| ' - '
|| i.retro_assignment_id);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
Step 3: Purge RetroPay Entries
DECLARE
CURSOR c1
IS
SELECT pra.retro_assignment_id, pre.owner_type, pre.element_entry_id
FROM pay_retro_assignments pra, pay_retro_entries pre
WHERE assignment_id = 61
AND pra.retro_assignment_id = pre.retro_assignment_id
AND (SELECT hl.meaning
FROM hr_lookups hl
WHERE lookup_type = 'ADVANCE_RETRO_STATUS'
AND hl.lookup_code = pra.approval_status) =
'Deferred';
BEGIN
FOR i IN c1
LOOP
BEGIN
pay_retro_status_internal.
delete_retro_entry (
p_retro_assignment_id => i.retro_assignment_id,
p_element_entry_id => i.element_entry_id,
p_owner_type => i.owner_type);
COMMIT;
DBMS_OUTPUT.
put_line (i.retro_assignment_id || ' --> has been Deleted !!!');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.
put_line (
'Inner Exception: '
|| SQLERRM
|| ' - '
|| i.retro_assignment_id);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
Step 4: Purge RetroPay Assignment
Status
DECLARE
CURSOR c1
IS
SELECT *
FROM pay_retro_assignments pra
WHERE assignment_id = 951
AND (SELECT hl.meaning
FROM hr_lookups hl
WHERE lookup_type = 'ADVANCE_RETRO_STATUS'
AND hl.lookup_code = pra.approval_status) =
'Deferred';
l_replaced_retro_asg_id NUMBER
:= NULL;
BEGIN
FOR i IN c1
LOOP
BEGIN
l_replaced_retro_asg_id := NULL;
pay_retro_status_internal.
delete_retro_asg (
p_retro_assignment_id => i.retro_assignment_id,
p_replaced_retro_asg_id => l_replaced_retro_asg_id);
COMMIT;
DBMS_OUTPUT.
put_line (
i.retro_assignment_id || ' --> Retro Assignment
Deleted !!!');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.
put_line (
'Inner Exception: '
|| SQLERRM
|| ' - '
|| i.retro_assignment_id);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
