API to Set Profile Option

DECLARE

   l_process_status   BOOLEAN;

   l_profile_name     VARCHAR2 (100) := NULL;

   l_profile_value    VARCHAR2 (1) := 'Y';

   l_profile_level    VARCHAR2 (100) := 'RESP';

   l_profile_option_name VARCHAR2(100) := NULL;

   CURSOR get_resp

   IS

      SELECT frt.responsibility_id,

             frt.responsibility_name,

             frt.application_id

        FROM fnd_responsibility fr, fnd_responsibility_tl frt

       WHERE     data_group_application_id = 800

             AND fr.responsibility_id = frt.responsibility_id

             AND frt.language = 'US'

             AND UPPER (frt.responsibility_name) = 'XXX HRMS SUPER USER';

BEGIN

   --Initialize Session

   fnd_global.

    apps_initialize (user_id => 1091,                                -- BIJOYJ

                                     resp_id => 20420, -- System Administrator

                                                      resp_appl_id => 1 -- Human Resources

                                                                       );

   -- Get Profile Name

   SELECT fpot.profile_option_name, fpot.user_profile_option_name

     INTO l_profile_name, l_profile_option_name

     FROM fnd_profile_options fpo, fnd_profile_options_tl fpot

    WHERE     fpo.profile_option_name = fpot.profile_option_name

          AND UPPER (fpot.user_profile_option_name) = 'HR:QUERY ONLY MODE'

          AND fpot.language = 'US';

   FOR fetch_resp IN get_resp

   LOOP

      BEGIN

         l_process_status :=

            fnd_profile.

             save (x_name                 => l_profile_name, -- Profile Option Name

                   x_value                => l_profile_value, -- Value : Y/N/NULL

                   x_level_name           => l_profile_level, -- USER / APPL / RESP / SITE

                   x_level_value          => fetch_resp.responsibility_id, -- RESP_ID

                   x_level_value_app_id   => fetch_resp.application_id, -- RESP_APPL_ID

                   x_level_value2         => NULL);

         COMMIT;

         IF l_process_status

         THEN

            DBMS_OUTPUT.

             put_line (

                  l_profile_name

               || ' has been assigned to '

               || fetch_resp.responsibility_name);

         ELSE

            DBMS_OUTPUT.

             put_line (

                  'Failure: '

               || l_profile_name

               || ' has not been assigned to '

               || fetch_resp.responsibility_name);

         END IF;

      EXCEPTION

         WHEN OTHERS

         THEN

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

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

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

END;