API to Attach Profile Options at Various Level

DECLARE

   CURSOR get_resp

   IS

      SELECT responsibility_id, application_id, responsibility_name

        FROM fnd_responsibility_tl

       WHERE responsibility_name = 'XXH AR Super User' AND language = 'US';

   CURSOR get_profile

   IS

      SELECT profile_option_name,

             user_profile_option_name,

             CASE

                WHEN user_profile_option_name = 'HR: Security Profile'

                THEN

                   '1062'

                WHEN user_profile_option_name = 'HR:Business Group'

                THEN

                   '81'

                WHEN user_profile_option_name = 'HR:User Type'

                THEN

                   'INT'

                WHEN user_profile_option_name = 'GL Ledger Name'

                THEN

                   'DHCI Ledger'

                WHEN user_profile_option_name = 'MO: Security Profile'

                THEN

                   '1062'

                WHEN user_profile_option_name = 'MO: Operating Unit'

                THEN

                   '101'

                WHEN user_profile_option_name = 'GL: Data Access Set'

                THEN

                   '1000'

             END

                profile_value

        FROM fnd_profile_options_vl

       WHERE user_profile_option_name IN

                ('HR: Security Profile',

                 'HR:Business Group',

                 'HR:User Type',

                 'GL Ledger Name',

                 'MO: Security Profile',

                 'MO: Operating Unit',

                 'GL: Data Access Set');

   l_return_status   BOOLEAN := FALSE;

BEGIN

   FOR i IN get_resp

   LOOP

      FOR j IN get_profile

      LOOP

         l_return_status := FALSE;

         l_return_status :=

            fnd_profile.save (x_name                 => j.profile_option_name,

                              x_value                => j.profile_value,

                              x_level_name           => 'RESP', --SITE, APPL, USER

                              x_level_value          => i.responsibility_id, -- Site ID, Application ID, User ID

                              x_level_value_app_id   => i.application_id -- Pass Null if not at Responsibility Level

);

         IF l_return_status

         THEN

            DBMS_OUTPUT.

             put_line (

                  j.user_profile_option_name

               || ' has been attached with value '

               || j.profile_value

               || ' for '

               || i.responsibility_name);

         ELSE

            DBMS_OUTPUT.

             put_line (

                  'Failure: '

               || j.user_profile_option_name

               || ' has not been attached with value '

               || j.profile_value

               || ' for '

               || i.responsibility_name);

         END IF;

         COMMIT;

      END LOOP;

   END LOOP;

END;