System Profiles and values

From beginner's point of view, Profiles are something similar to global variables used by Oracle to set context based values based on which the application behavior can be controlled. There are seeded profiles(part and parcel with EBS) and we have flexibility to define custom profiles. We can control the values which can be allowed to enter as well. We need to be careful while changing values for seeded profiles as it could impact the system behavior.

Oracle allows the profile values to be set at different Levels i.e

• Site Level
• Application Level
• Responsibility Level
• User Level

The hierarchy that is followed in Oracle Applications to get the value of the profile is

• User Level
• Responsibility Level
• Application Level
• Site Level

User Level is the lowest level and oracle takes this value (if it is set) for a logged in user

Some profiles are set at responsibility level (Eg: Ledger ID) which is applicable whenever that responsibility is accessed

Site level is the highest level; if no application/resp/user values were set,this value will be considered 

Once the value is set a record gets inserted into fnd_profile_option_values. One record gets inserted for each level’s value for that profile. Oracle uses the following values for the level_id column to identify different levels

10001 è Site,

10002 è Application,

10003 è Responsibility,

10004 è User

If we are retrieving the value for a profile using SQL then we must mention the level as well in where clause or else it will go into too many rows exception.

The sql to get the values of a profile for all levels is as below

SELECT   SUBSTR (e.profile_option_name, 1, 35) PROFILE,

         DECODE (a.level_id,

                 10001, 'Site',

                 10002, 'Application',

                 10003, 'Resp',

                 10004, 'User'

                ) LEVEL,

         DECODE (a.level_id,

                 10001, 'Site',

                 10002, c.application_short_name,

                 10003, b.responsibility_name,

                 10004, d.user_name

                ) lvalue,

         NVL (a.profile_option_value, 'Is Null') VALUE

    FROM fnd_profile_option_values a,

         fnd_responsibility_tl b,

         fnd_application c,

         fnd_user d,

         fnd_profile_options e

   WHERE e.profile_option_name LIKE '%MO%'

     AND e.profile_option_id = a.profile_option_id

     AND a.level_value = b.responsibility_id(+)

     AND a.level_value = c.application_id(+)

     AND a.level_value = d.user_id(+)

ORDER BY 1, 2