API to Update RetroPay Status - PAY_RETRO_ASSIGNMENTS

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;