Script to initialize the context of profiles,find out the values of a profile option

Script to initialize the context of profiles

Sometimes you want to run queries that reference profile options. To do this, you should first initialize the context of the current session to the user, responsibility and application being used. You can also set the context of the current inventory organization for queries that reference the organization context.

a. Set context

PROMPT Initialize context of profiles, etc.

PROMPT Note you can query the user id, responsibility id,

PROMPT and application id from the FND tables.

execute fnd_global.APPS_INITIALIZE(&UserID, &ResponsibilityID, &ApplicationID);

b. Set organization

PROMPT Set organization

PROMPT Note you can query the organization idea from mtl_parameters.

EXECUTE fnd_client_info.set_org_context('&OrganizationID');

 

====================

 

Script to find out the values of a profile option

It is sometimes hard to know where a profile option is set. A user might have a profile option set, an application, responsibility, and these might result in unexpected results. The following prompts for the profile name that a user sees. You could also query for the internal profile code instead by using the column profile_option_name instead.

SELECT   b.user_profile_option_name "Long Name",

         a.profile_option_name

               "Short Name",

         DECODE (

            TO_CHAR (c.level_id),

            '10001', 'Site',

            '10002', 'Application',

            '10003', 'Responsibility',

            '10004', 'User',

            'Unknown'

         ) "Level",

         DECODE (

            TO_CHAR (c.level_id),

            '10001', 'Site',

            '10002', NVL (h.application_short_name, TO_CHAR (c.level_value)),

            '10003', NVL (g.responsibility_name, TO_CHAR (c.level_value)),

            '10004', NVL (e.user_name, TO_CHAR (c.level_value)),

            'Unknown'

         ) "Level Value",

         c.profile_option_value "Profile Value",

         c.profile_option_id

               "Profile ID",

         TO_CHAR (c.last_update_date, 'DD-MON-YYYY HH24:MI') "Updated Date",

         NVL (d.user_name, TO_CHAR (c.last_updated_by))

               "Updated By"

    FROM apps.fnd_profile_options a,

         apps.fnd_profile_options_vl b,

         apps.fnd_profile_option_values c,

         apps.fnd_user d,

         apps.fnd_user e,

         apps.fnd_responsibility_vl g,

         apps.fnd_application h

   WHERE b.user_profile_option_name LIKE '&ProfileName'

     AND a.profile_option_name = b.profile_option_name

     AND a.profile_option_id = c.profile_option_id

     AND a.application_id = c.application_id

     AND c.last_updated_by = d.user_id(+)

     AND c.level_value = e.user_id(+)

     AND c.level_value = g.responsibility_id(+)

     AND c.level_value = h.application_id(+)

ORDER BY b.user_profile_option_name,

         C.level_id,

         DECODE (

            TO_CHAR (C.level_id),

            '10001', 'Site',

            '10002', NVL (h.application_short_name, TO_CHAR (C.level_value)),

            '10003', NVL (g.responsibility_name, TO_CHAR (C.level_value)),

            '10004', NVL (e.user_name, TO_CHAR (C.level_value)),

            'Unknown'

         );

 

========================