DECLARE
CURSOR get_pay_details IS
SELECT * FROM pay_grade_rules_f;
l_effective_start_date DATE := NULL;
l_effective_end_date DATE := NULL;
l_grade_rule_id NUMBER := NULL;
l_object_version_number NUMBER := NULL;
l_err_msg VARCHAR2 (500) := NULL;
l_rate_id NUMBER := NULL;
BEGIN
Before deleting the grade I would recommend you to understanding the p_datetrack_delete_mode Control Parameter.
The p_datetract_update_mode control parameter enables one to define the type of DateTrack deletion that has to be made. This parameter is mandatory and must be set to one of the following said values:
1. ZAP: ZAP mode Completely removes the grade rate from the database
2. DELETE : DELETE mode Sets end date to effective date
3. FUTURE_CHANGE : It removes all scheduled changes
4. DELETE_NEXT_CHANGE: It removes next change
FOR i IN get_pay_details
LOOP
BEGIN
hr_grade_rate_value_api.delete_grade_rate_value (
p_validate => FALSE,
p_effective_date => SYSDATE,
p_datetrack_delete_mode => 'ZAP',
p_grade_rule_id => i.grade_rule_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
);
COMMIT;
DBMS_OUTPUT.put_line (
'Grate Rate has been Deleted: ' || i.grade_rule_id
);
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := SQLERRM;
DBMS_OUTPUT.put_line ('Inner 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;
