Profiles

Oracle Applications provides a way to alter the behavior of a process/business flow during run time by setting up a Profile option. The standard Oracle Applications code look for the value of profile option and depending upon the value, it takes appropriate decision. For example, we may want to setup different limits for different users for approval of a purchase order. In this case, we set the amount for each user in a standard profile option. During approval business process, Oracle Applications can look at this limit and take appropriate action.

Oracle Applications uses a set of profile options that are common to all the application products. Further each module of Oracle Applications comes with several pre-defined profile options. These profile options are set with appropriate values during the module configuration by functional expert. The value to be set for different standard profile options depends on the business requirement.  

The profile options can be setup at Site, Application, Responsibility and User level. If a profile is setup at multiple levels, the lowest level value takes precedence over the higher level. For example, a profile value setup at user level takes precedence over the profile value setup at Responsibility level and so on.

In addition to the standard profile options, it is also possible to define custom profile options. The custom profiles can be used in custom code developed during Oracle applications implementations. A custom profile option is defined using Application developer responsibility.

Profile values are maintained by system administrator.

Business Uses Scenarios

Some of the real life business scenarios where profile options are used are as follows:

1. A Profile option ‘Max Discount Percentage’ can be set against each Order entry clerk. Depending upon this value, Order entry clerk can give the discount to a customer.
2. A profile option ‘Debug ON’ can be set to Yes or No. When it is set to Yes, program will run in the debug mode.
3. A profile option ‘MO: Operating Unit’ can be set for each responsibility. When a user logs to that responsibility, he will see data only for that particular operating unit.

How to create a new profile

Go to Application Developer Responsibility and navigate to Profile function. Press F11 to enter query and enter ‘CONC_SAVE_OUTPUT’. Press Control F11 to execute the query. The following screen comes up:

The various fields used in the above screen are explained below:

Name: Profile Code. This has to be unique in the system. This code is normally used in the FND functions to derive the value of a profile option in PL/SQL programs.

Application: Application Name to which profile is attached.

User Profile Name: Descriptive name of the profile

Description: Description of the profile

Hierarchy Type: Hierarchy type defines the applicable levels where profiles can be set. Profiles Hierarchy type and the corresponding hierarchy levels are given in the below table:

Hierarchy Type	Applicable Levels
Security	Site, Application, Responsibility, User
Server	Site, Server, User
Server-Responsibility	Site, Server, Responsibility, User
Organization	Site, Organization, User

When a hierarchy type is selected, only the corresponding levels are enabled. Further, the profile values are evaluated from bottom to top. That means, for example, for a ‘Security’ hierarchy type, a profile value set at user level will take precedence over the value set at responsibility level, and the value set at responsibility level will take precedence over the application level and so on.  

Active Dates:

 Start and end date of the profile. By Default, start date is system date and end date is NULL. A profile can be disabled by putting an end date.

  

User Access:

A profile value can be changed by user’s personal profile window also. In these fields we decide if the end user can view the profile value and if the value of the profile can be changed.

Visible : If checked, Profile will be visible to the end user

Updatable : If checked, Profile can be updated by the end user. If not checked, then only system administrator can set the value of the profile at user level.

SQL Validation

Sometime it is required to provide a list of values for setting the profile option values. In this case, we can provide a SQL command to select the values. For example, in the screen above, we have the following SQL:

SQL="select meaning \"Concurrent:Save Output\",lookup_code

 into :visible_option_value,:profile_option_value

 from fnd_lookups

 where lookup_type = 'YES_NO'"

COLUMN="\"Concurrent:Save Output\"(*)"

In the above SQL, :visible_option_value is the profile option value visible to the user and :profile_option_value is the profile option value that will be internally stored.

The column alias is enclosed with slash and double quote at the beginning and end of the column name if there is space in the name. The keyword COLUMN is used to indicate which columns to show in the LOV. Column length can be explicitly given or it can be dynamically derived by using (*) after the column name. The following example shows the use of COLUMN keyword.

COLUMN="Department(20), LOCATION(*)"

  

How to setup a profile value

Profile values are setup using System Administrator responsibility. Navigate to Profile => System, and Enter ‘Concurrent:Save Output’ in profile filed. Click on FIND button. (If value needs to be setup at other levels, corresponding level values can be entered before clicking on FIND button).

In the below screen, profile values can be setup at the appropriate value.

Technical details

Profile definition is stored in the following tables:

FND_PROFILE_OPTIONS

FND_PROFILE_OPTIONS_TL

These tables can be joined by column PROFILE_OPTION_NAME.

The value for a profile is stored in the following table:

FND_PROFILE_OPTION_VALUES

We can use the following statement to retrieve the value of a profile during run time:

l_profile_value := FND_PROFILE.VALUE(‘<Profile Short Name>’);

One of the most widely used profile is ‘MO: Operating Unit’ Profile. This profile has a code of ORG_ID. To get the value of current operating unit, use the following statement:

l_org_id := FND_PROFILE.VALUE(‘ORG_ID’);

To set a particular operating unit (for example, in SQLPLUS or TOAD), use the following PL/SQL code:

BEGIN

DBMS_APPLICATION_INFO.SET_CLIENT_INFO(‘204’);

--204 is the ORG_ID value.

END;