API to Update Payment Method

DECLARE

   CURSOR get_details

   IS

      SELECT paaf.assignment_id,

             paaf.effective_start_date,

             dept.account_no,

             per.attribute1,

             dept.bank,

             pppmf.personal_payment_method_id,

             pppmf.object_version_number

        FROM per_all_people_f per,

             per_all_assignments_f paaf,

             XXX_payment_method_29jun2014 dept,

             pay_personal_payment_methods_f pppmf

       WHERE     per.person_id = paaf.person_id

             AND per.person_type_id IN (1126, 1127)

             AND paaf.assignment_type = 'E'

             AND paaf.primary_flag = 'Y'

             AND paaf.assignment_status_type_id IN (1, 2)

             AND SYSDATE BETWEEN per.effective_start_date

                             AND per.effective_end_date

             AND SYSDATE BETWEEN paaf.effective_start_date

                             AND paaf.effective_end_date

             AND dept.legacy_employee_number = per.attribute1

             AND pppmf.assignment_id = paaf.assignment_id;

   l_effective_start_date         DATE := NULL;

   l_effective_end_date           DATE := NULL;

   l_object_version_number        NUMBER := NULL;

   l_external_account_id          NUMBER := NULL;

   l_org_payment_method_id        NUMBER := NULL;

   l_assignment_id                NUMBER := NULL;

   l_personal_payment_method_id   NUMBER := NULL;

   l_comment_id                   NUMBER := NULL;

   l_bank_code                    VARCHAR2 (50) := NULL;

   l_err_msg                      VARCHAR2 (500) := NULL;

BEGIN

   --Initialize Session

   fnd_global.

    apps_initialize (user_id => 1092,                        -- XXX_MIGRATION

                                     resp_id => 50738,    -- XXX HRMS Manager

                                                      resp_appl_id => 800 -- Human Resouces

                                                                         );

   -- Get Organization Payment ID

   BEGIN

      SELECT popm.org_payment_method_id

        INTO l_org_payment_method_id

        FROM pay_org_payment_methods_f popm

       WHERE SYSDATE BETWEEN popm.effective_start_date

                         AND popm.effective_end_date

             AND UPPER (org_payment_method_name) = 'XXX DIRECT DEPOSIT';

   EXCEPTION

      WHEN OTHERS

      THEN

         l_err_msg := 'l_org_payment_id  not found !!!';

         l_org_payment_method_id := NULL;

   END;

   FOR fetch_details IN get_details

   LOOP

      l_err_msg := NULL;

      l_personal_payment_method_id := NULL;

      l_external_account_id := NULL;

      l_object_version_number := NULL;

      l_effective_start_date := NULL;

      l_effective_end_date := NULL;

      l_comment_id := NULL;

      -- Get Employee Bank Code

      BEGIN

         SELECT hl.lookup_code

           INTO l_bank_code

           FROM hr_lookups hl

          WHERE hl.lookup_type = 'AE_BANK_NAMES'

                AND UPPER (hl.meaning) = UPPER (fetch_details.bank);

      EXCEPTION

         WHEN OTHERS

         THEN

            l_err_msg := l_err_msg || ' -  ' || ' l_bank_code  not found !!!';

            l_bank_code := NULL;

      END;

      IF l_org_payment_method_id IS NOT NULL AND l_bank_code IS NOT NULL

      THEN

         BEGIN

            hr_personal_pay_method_api.

             update_personal_pay_method (

               p_validate                     => FALSE,

               p_effective_date               => TO_DATE('01-JUN-2014'),

               p_datetrack_update_mode        => 'UPDATE',

               p_personal_payment_method_id   => fetch_details.

                                                 personal_payment_method_id,

               p_object_version_number        => fetch_details.

                                                 object_version_number,

               p_percentage                   => 100,

               p_priority                     => 1,

               p_territory_code               => 'AE',

               p_segment1                     => l_bank_code, 

               p_segment4                     => fetch_details.account_no,

               p_segment6                     => 0,

               p_comment_id                   => l_comment_id,

               p_external_account_id          => l_external_account_id,

               p_effective_start_date         => l_effective_start_date,

               p_effective_end_date           => l_effective_end_date);

            COMMIT;

            DBMS_OUTPUT.

             put_line (

               l_personal_payment_method_id

               || ' has been Created Successfully');

         EXCEPTION

            WHEN OTHERS

            THEN

               l_err_msg := SQLERRM;

               DBMS_OUTPUT.

                put_line (

                     'Innner Exception: '

                  || fetch_details.assignment_id

                  || ' - '

                  || l_err_msg);

         END;

      ELSE

         DBMS_OUTPUT.put_line ('Org Payment or Bank Code not Found !!!');

         COMMIT;

      END IF;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      l_err_msg := SQLERRM;

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

END;