Payment Method Master Query with all Tab Details

SELECT popmf.org_payment_method_id,

       popmf.org_payment_method_name,

       pea.external_account_id, -- Bank information --> Get Segment Information from "Bank Details KeyFlexField" KF in regard with your legislation

       pea.segment1 bank_segment1, -- Check the KFF, get the look ups used and decode the values of segments

       pea.segment2 bank_segment2,

       pea.segment4 bank_segment4,

       pea.segment5 bank_segment5,

       ppga.set_of_books_id,

       (SELECT name

          FROM gl_ledgers

         WHERE ledger_id = ppga.set_of_books_id)

          ledger_name,

       ppga.gl_cash_ac_id,

       (SELECT    segment1

               || '.'

               || segment2

               || '.'

               || segment3

               || '.'

               || segment4

               || '.'

               || segment5

               || '.'

               || segment6

               || '.'

               || segment7

          FROM gl_code_combinations

         WHERE code_combination_id = ppga.gl_cash_ac_id)

          gl_cash_account,

       ppga.gl_cash_clearing_ac_id,

       (SELECT    segment1

               || '.'

               || segment2

               || '.'

               || segment3

               || '.'

               || segment4

               || '.'

               || segment5

               || '.'

               || segment6

               || '.'

               || segment7

          FROM gl_code_combinations

         WHERE code_combination_id = ppga.gl_cash_clearing_ac_id)

          cash_clearning_account,

       ppga.gl_control_ac_id,

       (SELECT    segment1

               || '.'

               || segment2

               || '.'

               || segment3

               || '.'

               || segment4

               || '.'

               || segment5

               || '.'

               || segment6

               || '.'

               || segment7

          FROM gl_code_combinations

         WHERE code_combination_id = ppga.gl_cash_clearing_ac_id)

          payroll_control_account,

       cbaua.bank_account_id cash_mgmt_bank_account_id,

       cba.bank_account_name cash_mgmt_bank_account_name,

       popmf.transfer_to_gl_flag,

       popmf.cost_payment,

       popmf.cost_cleared_payment,

       popmf.cost_cleared_void_payment

  FROM pay_org_payment_methods_f popmf,

       pay_external_accounts pea,

       pay_payment_gl_accounts_f ppga,

       ce_bank_acct_uses_all cbaua,

       ce_bank_accounts cba

WHERE     popmf.business_group_id = 81

       AND popmf.org_payment_method_id = 72

       AND popmf.external_account_id = pea.external_account_id

       AND TRUNC (SYSDATE) BETWEEN popmf.effective_start_date

                               AND popmf.effective_end_date

       AND pea.enabled_flag = 'Y'

       AND ppga.org_payment_method_id = popmf.org_payment_method_id

       AND TRUNC (SYSDATE) BETWEEN ppga.effective_start_date

                               AND ppga.effective_end_date

       AND cbaua.payroll_bank_account_id = popmf.external_account_id

       AND cba.bank_account_id = cbaua.bank_account_id

API to Create User Table Column Instance

DECLARE

   l_user_column_instance_id   NUMBER;

   l_object_version_number     NUMBER;

   l_effective_start_date      DATE;

   l_effective_end_date        DATE;

   CURSOR c1 (

      lc_bbgid NUMBER)

   IS

      SELECT pur.user_row_id, puc.*

        FROM pay_user_tables put, pay_user_rows_f pur, pay_user_columns puc

       WHERE     put.user_table_id = pur.user_table_id

             AND put.business_group_id = lc_bbgid

             AND SYSDATE BETWEEN pur.effective_start_date

                             AND pur.effective_end_date

             AND puc.user_table_id = put.user_table_id

             AND put.user_table_name = 'XXHR_USER_HOOKS';

   CURSOR c2

   IS

        SELECT pbg.business_group_id, pbg.name entity

          FROM per_business_groups pbg

         WHERE     pbg.business_group_id = 81;

BEGIN

   FOR j IN c2

   LOOP

      FOR i IN c1(j.business_group_id)

      LOOP

         BEGIN

            l_user_column_instance_id := NULL;

            l_object_version_number := NULL;

            l_effective_start_date := NULL;

            l_effective_end_date := NULL;

            pay_user_column_instance_api.create_user_column_instance (

               p_validate                  => FALSE,

               p_effective_date            => '01-JAN-1951',

               p_user_row_id               => i.user_row_id,

               p_user_column_id            => i.user_column_id,

               p_value                     => 'Y',

               p_business_group_id         => j.business_group_id,

               p_legislation_code          => NULL,

               p_user_column_instance_id   => l_user_column_instance_id,

               p_object_version_number     => l_object_version_number,

               p_effective_start_date      => l_effective_start_date,

               p_effective_end_date        => l_effective_end_date);

            DBMS_OUTPUT.put_line (

                  l_user_column_instance_id

               || ' has been Created --> '

               || i.business_group_id);

            COMMIT;

         EXCEPTION

            WHEN OTHERS

            THEN

               DBMS_OUTPUT.put_line (i.business_group_id || '--> Failed!');

         END;

      END LOOP;

   END LOOP;

END;

API to Create User Table Rows

DECLARE

   l_user_table_id           NUMBER := NULL;

   l_object_version_number   NUMBER := NULL;

   l_display_sequence        NUMBER := 10;

   l_user_row_id             NUMBER := NULL;

   l_effective_start_date    DATE := NULL;

   l_effective_end_date      DATE := NULL;

   CURSOR c1

   IS

      SELECT pbg.business_group_id, pbg.name entity

        FROM per_business_groups pbg

       WHERE pbg.business_group_id = 81;

BEGIN

   FOR i IN c1

   LOOP

      l_user_table_id := NULL;

      l_user_row_id := NULL;

      l_object_version_number := NULL;

      l_effective_start_date := NULL;

      l_effective_end_date := NULL;

      SELECT user_table_id

        INTO l_user_table_id

        FROM pay_user_tables

       WHERE     user_table_name = 'XXHR_USER_HOOKS'

             AND business_group_id = i.business_group_id;

      BEGIN

         pay_user_row_api.create_user_row (

            p_validate                     => FALSE,

            p_effective_date               => '01-JAN-1951',

            p_user_table_id                => l_user_table_id,

            p_row_low_range_or_name        => 'VALIDATE_EMIRATES_ID',

            p_display_sequence             => l_display_sequence,

            p_business_group_id            => i.business_group_id,

            p_user_row_id                  => l_user_row_id,

            p_object_version_number        => l_object_version_number,

            p_effective_start_date         => l_effective_start_date,

            p_effective_end_date           => l_effective_end_date,

            p_base_row_low_range_or_name   => 'VALIDATE_EMIRATES_ID');

         COMMIT;

         DBMS_OUTPUT.put_line (

            l_user_row_id || ' has been Created --> ' || i.business_group_id);

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line (i.business_group_id || '--> Failed!');

      END;

   END LOOP;

END;