API to Create User Table Definition

--select * from pay_user_tables where user_table_name = 'XXHR_USER_HOOKS'

DECLARE

   l_user_table_id           NUMBER := NULL;

   l_object_version_number   NUMBER := 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

      BEGIN

         l_user_table_id := NULL;

         l_user_table_id := NULL;

         pay_user_table_api.create_user_table (

            p_validate                => FALSE,

            p_effective_date          => '01-JAN-1951',

            p_business_group_id       => i.business_group_id,

            p_range_or_match          => 'M'                          -- Match

                                            ,

            p_user_key_units          => 'T'                           -- Text

                                            ,

            p_user_table_name         => 'XXHR_USER_HOOKS',

            p_user_row_title          => NULL,

            p_user_table_id           => l_user_table_id,

            p_object_version_number   => l_object_version_number);

         COMMIT;

         DBMS_OUTPUT.put_line (

               l_user_table_id

            || ' has been Created --> '

            || i.business_group_id);

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line (

               i.business_group_id || '--> Failed!' || SQLERRM);

      END;

   END LOOP;

END;