API to Create User Table Columns

DECLARE

   l_user_table_id           NUMBER := NULL;

   l_object_version_number   NUMBER := NULL;

   l_user_column_id          NUMBER := NULL;

   CURSOR c1

   IS

        SELECT pbg.business_group_id, pbg.name entity

          FROM per_business_groups pbg

         WHERE     pbg.business_group_id = 81;     

   CURSOR c2

   IS

      SELECT 'EIT_VALIDATIONS' t FROM DUAL

      UNION

      SELECT 'ABSENCES_VALIDATIONS' t FROM DUAL

      UNION

      SELECT 'ELEMENT_VALIDATIONS' t FROM DUAL

      UNION

      SELECT 'PAYMENT_METHOD_VALIDATIONS' t FROM DUAL

      UNION

      SELECT 'QUICKPAY_VALIDATIONS' t FROM DUAL

      UNION

      SELECT 'SALARY_VALIDATIONS' t FROM DUAL;

BEGIN

   FOR i IN c1

   LOOP

      l_user_table_id := NULL;

      l_user_column_id := 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;

      FOR j IN c2

      LOOP

         BEGIN

            pay_user_column_api.create_user_column (

               p_validate                => FALSE,

               p_business_group_id       => i.business_group_id,

               p_user_table_id           => l_user_table_id,

               p_user_column_name        => j.t,

               p_user_column_id          => l_user_column_id,

               p_object_version_number   => l_object_version_number);

            COMMIT;

            DBMS_OUTPUT.put_line (

                  l_user_column_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 LOOP;

END;