API to Delete RetroPay Assignment - PAY_RETRO_ASSIGNMENTS

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;