API's to Rollback Enhanced RetroPay, Update RetroPay Assignment Status, Delete Retro Entries & Delete RetroPay Assignment

Step 1: Rollback Enhanced RetroPay in Error

DECLARE

   CURSOR c1

   IS

    select * from pay_assignment_actions where payroll_action_id = 40825 and action_status  = 'E';

BEGIN

   FOR i IN c1

   LOOP

      py_rollback_pkg.

       rollback_ass_action (

         p_assignment_action_id   => i.assignment_action_id,

         p_rollback_mode          => 'ROLLBACK');

--      COMMIT;

      DBMS_OUTPUT.

       put_line (

         i.assignment_action_id || 'Assg Action has been Rolled Back !!!');

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;

Step 2: Update RetroPay Assignment Status to Deferred

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;

Step 3: Purge RetroPay Entries

DECLARE

   CURSOR c1

   IS

      SELECT pra.retro_assignment_id, pre.owner_type, pre.element_entry_id

        FROM pay_retro_assignments pra, pay_retro_entries pre

       WHERE assignment_id = 61

             AND pra.retro_assignment_id = pre.retro_assignment_id

             AND (SELECT hl.meaning

                    FROM hr_lookups hl

                   WHERE lookup_type = 'ADVANCE_RETRO_STATUS'

                         AND hl.lookup_code = pra.approval_status) =

                    'Deferred';

BEGIN

   FOR i IN c1

   LOOP

      BEGIN

         pay_retro_status_internal.

          delete_retro_entry (

            p_retro_assignment_id   => i.retro_assignment_id,

            p_element_entry_id      => i.element_entry_id,

            p_owner_type            => i.owner_type);

         COMMIT;

         DBMS_OUTPUT.

          put_line (i.retro_assignment_id || ' --> has been 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;

Step 4: Purge RetroPay Assignment Status

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;