Tuesday, August 4, 2015

How to Delete Grade Rate using API in Oracle HRMS

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;