Delete pending leaves using reference table PER_ABSENCE_ATTENDANCES

create or replace procedure xx_delete_pending_leaves as

   CURSOR c1 IS

      SELECT hat.transaction_id, hats.transaction_step_id
        FROM hr_api_transactions hat, hr_api_transaction_steps hats
       WHERE hat.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'
         AND hat.transaction_group = 'ABSENCE_MGMT'
         AND hat.transaction_identifier = 'ABSENCES'
         AND hat.transaction_ref_id IS NOT NULL
         AND hat.status = 'Y'
         AND hat.transaction_id = hats.transaction_id;
        
   l_transaction_id        NUMBER;
   l_transaction_step_id   NUMBER;
   l_error                 VARCHAR2 (300);

BEGIN

  OPEN c1;

   LOOP

      FETCH c1 INTO l_transaction_id, l_transaction_step_id;
       EXIT WHEN c1%NOTFOUND;

      IF l_transaction_step_id IS NOT NULL  THEN

         DELETE FROM hr_api_transaction_values
          WHERE transaction_step_id = l_transaction_step_id;

         DBMS_OUTPUT.put_line

                     (   'Deleted Transaction Value of transaction step id: '

                      || l_transaction_step_id

                     );

      END IF;

      IF l_transaction_id IS NOT NULL THEN    

         DELETE FROM hr_api_transaction_steps
               WHERE transaction_id = l_transaction_id;
         DBMS_OUTPUT.put_line

                           (  
                           'Deleted Transaction step of transaction id: '|| l_transaction_id
                           );

         DELETE FROM hr_api_transactions
               WHERE transaction_id = l_transaction_id;        
         DBMS_OUTPUT.put_line ('Deleted Transaction ID: ' || l_transaction_id);
      END IF;
   END LOOP;
 CLOSE c1;
   COMMIT;

 EXCEPTION

   WHEN OTHERS THEN

      l_error := SUBSTR (SQLERRM, 1, 200);

      DBMS_OUTPUT.put_line (   'Other Error in xx_delete_pending_leaves- ' || l_error);

END xx_delete_pending_leaves;