API to Delete User Column Instance - User Table

DECLARE

   CURSOR c1

   IS

      SELECT puci.*

        FROM pay_user_tables put,

             pay_user_columns puc,

             pay_user_column_instances_f puci

       WHERE     put.business_group_id = 10665

             AND put.user_table_name = 'XXHR_TICKET_RULES'

             AND put.user_table_id = puc.user_table_id

             AND puc.user_column_name IN

                    ('COLUMN_NAME')

             AND puci.user_column_id = puc.user_column_id

             AND TRUNC (SYSDATE) BETWEEN puci.effective_start_date

                                     AND puci.effective_end_date;

   l_effective_start_date    DATE;

   l_effective_end_date      DATE;

BEGIN

   FOR i IN c1

   LOOP

      BEGIN

        

         l_effective_start_date := NULL;

         l_effective_end_date := NULL;

         pay_user_column_instance_api.delete_user_column_instance (

            p_validate                  => FALSE,

            p_effective_date            => SYSDATE,

            p_user_column_instance_id   => i.user_column_instance_id,

            p_datetrack_update_mode     => 'ZAP',

            p_object_version_number     => i.object_version_number,

            p_effective_start_date      => l_effective_start_date,

            p_effective_end_date        => l_effective_end_date);

         COMMIT;

         DBMS_OUTPUT.put_line (

            i.user_column_instance_id || ' has been deleted !!!');

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line (

                  i.user_column_instance_id

               || ' Deletion Failed --> '

               || SQLERRM);

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

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

END;