API to Create User Rows - User Table

DECLARE

   CURSOR c1

   IS

      SELECT DISTINCT airport, description

        FROM xx_ticket_t

       WHERE process_flag = 'N';

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

   l_user_table_id           NUMBER := NULL;

   l_business_group_id       NUMBER := 10665;

   l_seq                     NUMBER := 399;

   l_user_row_id             NUMBER := NULL;

   l_object_version_number   NUMBER := NULL;

   l_effective_start_date    DATE := NULL;

   l_effective_end_date      DATE := NULL;

   l_err_msg                 VARCHAR2 (1000) := NULL;

BEGIN

   SELECT user_table_id

     INTO l_user_table_id

     FROM pay_user_tables

    WHERE     user_table_name = 'XXHR_TICKET_RULES'

          AND business_group_id = l_business_group_id;

   FOR i IN c1

   LOOP

      BEGIN

         l_user_row_id := NULL;

         l_object_version_number := NULL;

         l_effective_start_date := NULL;

         l_effective_end_date := NULL;

         l_err_msg := NULL;

         l_seq := l_seq + 1;

         pay_user_row_api.create_user_row (

            p_validate                     => FALSE,

            p_effective_date               => l_effective_date,

            p_user_table_id                => l_user_table_id,

            p_row_low_range_or_name        => i.airport,

            p_display_sequence             => l_seq,

            p_business_group_id            => l_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   => i.description);

         COMMIT;

         UPDATE xx_ticket_t

            SET process_flag = 'Y', err_msg = NULL

          WHERE airport = i.airport;

      EXCEPTION

         WHEN OTHERS

         THEN

            l_err_msg := SQLERRM;

            UPDATE xx_ticket_t

               SET process_flag = 'N', err_msg = l_err_msg

             WHERE airport = i.airport;

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

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

END;