Search Results fnd_profile_option_values




The FND_PROFILE_OPTION_VALUES table is a critical repository within Oracle E-Business Suite (EBS) Release 12.1.1 and 12.2.2, storing user, responsibility, site, and application-level profile option values. These profile options govern system behavior, security, and functional configurations across the Oracle EBS environment. Understanding this table's structure, relationships, and usage is essential for system administrators, developers, and functional consultants.

Table Structure and Key Columns

The FND_PROFILE_OPTION_VALUES table consists of the following key columns:
  • PROFILE_OPTION_ID: Foreign key to FND_PROFILE_OPTIONS, identifying the profile option.
  • LEVEL_ID: Numeric value indicating the hierarchy level (Site=1, Application=2, Responsibility=3, User=4, Server=5, Org=6).
  • LEVEL_VALUE: Contains the specific ID corresponding to the level (e.g., USER_ID for user-level profiles).
  • LEVEL_VALUE_APPLICATION_ID: Application ID when level is Application or Responsibility.
  • PROFILE_OPTION_VALUE: The actual configured value for the profile option.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY: Audit columns tracking modifications.

Hierarchy and Precedence Rules

Profile options follow a strict hierarchy where lower-level values override higher-level settings:
  1. Site Level (1): Applies to all users unless overridden.
  2. Application Level (2): Specific to an application module.
  3. Responsibility Level (3): Applies to users with the assigned responsibility.
  4. User Level (4): Specific to individual users.
  5. Server Level (5): Node-specific configurations in clustered environments.
  6. Organization Level (6): For Multi-Org configurations.

Technical Implementation

The table integrates with Oracle EBS's profile option framework through:
  • PL/SQL API FND_PROFILE package (GET_VALUE, PUT, SAVE functions)
  • Concurrent program "Synchronize Profile Values" (FNDSYCP) to sync cached values
  • Dependencies with FND_PROFILE_OPTIONS (definitions) and FND_PROFILE_OPTION_VALUES tables

Common Use Cases

  1. System Configuration: Setting global parameters like GL_SET_OF_BKS_ID for financials.
  2. Security Controls: Implementing function security via FND:VALIDATION_FUNCTION.
  3. Personalization: User-specific preferences like date formats (ICX_DATE_FORMAT).
  4. Performance Tuning: Adjusting memory parameters (OLTP_PAGE_BUFFER_SIZE).

Administration Best Practices

  • Use Oracle's standard profile screens (System Administrator > Profile > System) for changes
  • Document all custom profile option settings in a configuration repository
  • Leverage FNDLOAD for migrating profile options between environments
  • Monitor performance impacts of profile options with hierarchical values
  • Regularly audit profile values using SQL queries against FND_PROFILE_OPTION_VALUES

Query Examples

-- Find all user-level profile values for a specific option
SELECT fu.user_name, fpov.profile_option_value
FROM fnd_profile_option_values fpov, fnd_user fu
WHERE fpov.profile_option_id = (SELECT profile_option_id 
                               FROM fnd_profile_options 
                               WHERE profile_option_name = 'PROFILE_OPTION_NAME')
AND fpov.level_id = 4
AND fpov.level_value = fu.user_id;
The FND_PROFILE_OPTION_VALUES table serves as the operational backbone for Oracle EBS's flexible configuration system. Proper management of this table ensures system stability while enabling granular control over application behavior across diverse implementation scenarios.