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;