API to Delete EIT Structure

--select * from fnd_descr_flex_contexts  where descriptive_flex_context_code = 'XXHR_XXX_EDUCATION'-- EIT Details

--select *   from fnd_descr_flex_col_usage_vl where descriptive_flex_context_code = 'XXHR_XXX_EDUCATION' - EIT Column Details

DECLARE

   CURSOR c1

   IS

      SELECT *

        FROM fnd_descr_flex_contexts

       WHERE descriptive_flex_context_code = 'XXHR_XXX_EDUCATION';

BEGIN

   FOR i IN c1

   LOOP

      BEGIN

         fnd_flex_dsc_api.delete_context (

            appl_short_name   => 'PER',                                  -- HR

            flexfield_name    => i.descriptive_flexfield_name,

            context           => i.descriptive_flex_context_code);

         DBMS_OUTPUT.put_line (

               i.descriptive_flex_context_code

            || ' has been deleted Successfully!');

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line (

                  ' Inner Exception: '

               || i.descriptive_flex_context_code

               || ' - '

               || SQLERRM);

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (' Main Exception: ' || SQLERRM);

END;

API to Update Absence Type

DECLARE

   CURSOR c1

   IS

      SELECT *

        FROM per_absence_attendance_types

       WHERE business_group_id = 10549 AND name = 'Annual Leave';

   l_attribute5   NUMBER := 68896;

BEGIN

   FOR i IN c1

   LOOP

      BEGIN

         hr_absence_type_api.update_absence_type (

            p_validate                     => FALSE,

            p_absence_attendance_type_id   => i.absence_attendance_type_id,

            p_date_end                     => i.date_end,

            p_attribute5                   => l_attribute5,

            p_object_version_number        => i.object_version_number);

         COMMIT;

         DBMS_OUTPUT.put_line (

            i.absence_attendance_type_id || ' has been Updated ');

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line (

               i.absence_attendance_type_id || ' Failure -->  ' || SQLERRM);

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (' Failure -->  ' || SQLERRM);

END;

API to Create Security Rule Usages

--select * from fnd_flex_value_rule_usages where flex_value_set_id = 1014149 order by creation_date desc

DECLARE

   l_flex_value_set_name      VARCHAR2 (100) := 'XX_GL_FUTURE1_VS';

   l_flex_value_rule_name     VARCHAR2 (100) := NULL;

   l_responsibility_key       VARCHAR2 (100) := NULL;

   l_application_short_name   VARCHAR2 (100) := NULL;

BEGIN

   SELECT flex_value_rule_name

     INTO l_flex_value_rule_name

     FROM fnd_flex_value_rules

    WHERE flex_value_rule_id = 1858;

   SELECT fr.responsibility_key, fa.application_short_name

     INTO l_responsibility_key, l_application_short_name

     FROM fnd_responsibility fr,

          fnd_responsibility_tl frt,

          fnd_application fa,

          fnd_application_tl fat

    WHERE     fr.responsibility_id = frt.responsibility_id

          AND frt.language = 'US'

          AND fa.application_id = fr.application_id

          AND fr.application_id = fat.application_id

          AND fat.language = 'US'

          AND responsibility_name = 'XX GL Super User';

   DBMS_OUTPUT.PUT_LINE (l_responsibility_key);

   DBMS_OUTPUT.PUT_LINE (l_application_short_name);

   fnd_flex_loader_apis.up_vset_security_usage (

      p_upload_mode              => NULL,

      p_flex_value_set_name      => l_flex_value_set_name,

      p_flex_value_rule_name     => l_flex_value_rule_name,

      p_parent_flex_value_low    => NULL,

      p_application_short_name   => l_application_short_name,

      p_responsibility_key       => l_responsibility_key,

      p_last_update_date         => SYSDATE,

      p_owner                    => 'BIJOY',

      p_parent_flex_value_high   => NULL);

   DBMS_OUTPUT.put_line ('Security Added');

   COMMIT;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (SQLERRM);

END;

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;