API to Create User Column Instance - User Table

DECLARE

   CURSOR c1

   IS

      SELECT ROWID, t.*

        FROM xx_ticket_t t

       WHERE process_flag_instance = 'N';

   l_user_table_name           VARCHAR2 (500) := 'XXHR_TICKET_RULES';

   l_user_column_instance_id   NUMBER;

   l_object_version_number     NUMBER;

   l_effective_start_date      DATE;

   l_effective_end_date        DATE;

   l_user_column_id            NUMBER;

   l_user_row_id               NUMBER;

   l_effective_date            DATE := '01-JAN-1951';

   l_business_group_id         NUMBER := 10665;

   l_err_msg                   VARCHAR2 (500);

BEGIN

   FOR i IN c1

   LOOP

      BEGIN

         l_user_column_id := NULL;

         l_user_row_id := NULL;

         l_user_column_instance_id := NULL;

         l_object_version_number := NULL;

         l_effective_start_date := NULL;

         l_effective_end_date := NULL;

         l_err_msg := NULL;

         SELECT puc.user_column_id

           INTO l_user_column_id

           FROM pay_user_tables put, pay_user_columns puc

          WHERE     put.business_group_id = l_business_group_id

                AND put.user_table_id = puc.user_table_id

                AND put.user_table_name = l_user_table_name

                AND UPPER (puc.user_column_name) =

                       UPPER (i.user_table_column);

         SELECT pur.user_row_id

           INTO l_user_row_id

           FROM pay_user_tables put,

                pay_user_rows_f pur,

                pay_user_rows_f_tl purtl

          WHERE     put.user_table_id = pur.user_table_id

                AND put.business_group_id = 10665

                AND purtl.user_row_id = pur.user_row_id

                AND purtl.language = 'US'

                AND TRUNC (SYSDATE) BETWEEN pur.effective_start_date

                                        AND pur.effective_end_date

                AND put.user_table_name = l_user_table_name

                AND UPPER (purtl.row_low_range_or_name) = UPPER (i.airport);

         pay_user_column_instance_api.create_user_column_instance (

            p_validate                  => FALSE,

            p_effective_date            => l_effective_date,

            p_user_row_id               => l_user_row_id,

            p_user_column_id            => l_user_column_id,

            p_value                     => i.amount,

            p_business_group_id         => l_business_group_id,

            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);

         COMMIT;

         UPDATE xx_ticket_t

            SET process_flag_instance = 'Y', err_msg_instance = NULL

          WHERE ROWID = i.ROWID;

      EXCEPTION

         WHEN OTHERS

         THEN

            l_err_msg := SUBSTR (SQLERRM, 1, 4000);

            UPDATE xx_ticket_t

               SET process_flag_instance = 'N', err_msg_instance = l_err_msg

             WHERE ROWID = i.ROWID;

            COMMIT;

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Main Exception --> ' || SQLERRM);

END;