1 Reply Latest reply on Mar 20, 2015 5:06 PM by Cuauhtemoc Amox

    Profile option update at responsibility level from backend issues

    user11951005

      Hi

       

      Last week I was asked to update 900 responsibility profiles on a very short time  window, in order to fulfill the request I have developed a custom code with the FND_PROFILE.save API, but soon I found that it wasn't an easy task because there isn't to much documentation about it, so I decided to create this paper in order to spare time and bad moments to other IT fellas.

       

       

      First trick

      Use the below query to get the responsibility id and its application id

       

      SELECT fr.responsibility_id, fr.application_id

                 FROM FND_RESPONSIBILITY fr, fnd_responsibility_tl frt

                WHERE frt.responsibility_NAME = "responsibility Name"

                      AND frt.responsibility_id = fr.RESPONSIBILITY_ID;

       

      Second trick

      Use the below query to get the profile system name

       

               SELECT A.PROFILE_OPTION_NAME

                 FROM FND_PROFILE_OPTIONS A, FND_PROFILE_OPTIONS_TL B

                WHERE     A.PROFILE_OPTION_NAME = B.PROFILE_OPTION_NAME

                      AND B.USER_PROFILE_OPTION_NAME = "Profile Name";

       

      Third trick

      If you want to update a responsibility profiles, you cant use the profile user visible option value, instead you have to work with the profile system option value, but here lays the trick question; oracle doesn't have a table or a view which holds  each specific profile system values, instead you have to search for each specific profile queries inside the profile option in the Application developer responsibility

       

       

      Example:

       

       

      The 'MO: Operation Unit'  query to get its profile value is

       

       

      SELECT 'MO: Operation Unit' as PROFILE_NAME

      ,TO_CHAR(ORGANIZATION_ID) AS SYSTEM_OPTION_VALUE

      , NAME  as VISIBLE_OPTION_VALUE

      FROM HR_OPERATING_UNITS

       

      Four trick

      Don't forget to add a commit statement after the the FND_PROFILE.save execution, because the function doesn't commits by it self

       

      Example

               l_success :=

                  FND_PROFILE.save (x_name                 => vPROFILENAME,

                                    x_value                => vPROFILEVAl,

                                    x_level_name           => 'RESP',

                                    x_level_value          => l_resp_id, --responsibility_id

                                    x_level_value_app_id   => l_resp_app_id    --401

                                                                           );

               COMMIT;

       

               IF l_success

               THEN

                  DBMS_OUTPUT.put_line (               'Profile Updated successfully at responsiblity Level');

               ELSE

                  DBMS_OUTPUT.put_line ( l_resp_id || ' ' || l_resp_app_id || ' ' || vPROFILEOPT || ' ' || vPROFILEVAl || ' Profile Update Failed at site Level. Error:' || SQLERRM);

               END IF;


      I hope you find this post useful and good look with your costume codes

       

      Thanks for the time


      José Vela



        • 1. Re: Profile option update at responsibility level from backend issues
          Cuauhtemoc Amox

          Gracias Pepe.

           

          A recommendation for code display, switch to advanced editor and after selecting the code click on the last icon on the bottom row of the toolbar (>>), select syntax highlighting and then SQL.I think there is an option now on the forum to create Blogs posts, which might have more features for formatting write ups


          SELECT fr.responsibility_id, fr.application_id
                     FROM FND_RESPONSIBILITY fr, fnd_responsibility_tl frt
                    WHERE frt.responsibility_NAME = "responsibility Name"
                          AND frt.responsibility_id = fr.RESPONSIBILITY_ID;