SQL query to find profile option values by level in Oracle EBS
SQL query to find profile option values by level in Oracle EBS
Also searched as
- fnd_profile_option_values query site responsibility user
- where are profile options stored oracle apps
- find all profile options set at user level
- FND_PROFILE.VALUE returns null why
Short answer
Profile definitions live in FND_PROFILE_OPTIONS and FND_PROFILE_OPTIONS_TL. Values live in FND_PROFILE_OPTION_VALUES, keyed by LEVEL_ID (10001 site, 10002 application, 10003 responsibility, 10004 user) and LEVEL_VALUE. Join them to see who has what set and where.
Applies to: Oracle E-Business Suite 11i, R12.1 and R12.2
List values for one profile option at every level
- 1Find the internal name: query FND_PROFILE_OPTIONS_TL by USER_PROFILE_OPTION_NAME to get PROFILE_OPTION_NAME.
- 2Join FND_PROFILE_OPTIONS on PROFILE_OPTION_NAME to get PROFILE_OPTION_ID and APPLICATION_ID.
- 3Join FND_PROFILE_OPTION_VALUES on both PROFILE_OPTION_ID and APPLICATION_ID.
- 4Decode LEVEL_ID: 10001 is Site, 10002 Application, 10003 Responsibility, 10004 User.
- 5Resolve LEVEL_VALUE to a name using FND_APPLICATION_TL, FND_RESPONSIBILITY_TL or FND_USER depending on the level.
- 6Filter on PROFILE_OPTION_VALUE if you need to find where a specific value is set.
- 7Remember that FND_PROFILE_OPTION_VALUES stores one row per set level, so a missing row means the value is inherited from a higher level.
The core query
This query returns each set value with a readable level and name. Replace the profile name with the one you are investigating. The responsibility branch joins FND_RESPONSIBILITY_TL using both the responsibility id and its application id, which is stored in LEVEL_VALUE_APPLICATION_ID for responsibility level rows.
Run it from SQL*Plus or any tool connected to the APPS schema. It is read only. Using the TL tables with a language filter avoids duplicate rows in multi-language installations.
SELECT pot.user_profile_option_name, DECODE(v.level_id, 10001, 'Site', 10002, 'Application', 10003, 'Responsibility', 10004, 'User') lvl, DECODE(v.level_id, 10001, 'Site', 10002, a.application_name, 10003, r.responsibility_name, 10004, u.user_name) level_name, v.profile_option_value FROM apps.fnd_profile_options po, apps.fnd_profile_options_tl pot, apps.fnd_profile_option_values v, apps.fnd_application_tl a, apps.fnd_responsibility_tl r, apps.fnd_user u WHERE po.profile_option_name = pot.profile_option_name AND pot.language = 'US' AND v.application_id = po.application_id AND v.profile_option_id = po.profile_option_id AND a.application_id(+) = DECODE(v.level_id, 10002, v.level_value, NULL) AND a.language(+) = 'US' AND r.responsibility_id(+) = DECODE(v.level_id, 10003, v.level_value, NULL) AND r.application_id(+) = DECODE(v.level_id, 10003, v.level_value_application_id, NULL) AND r.language(+) = 'US' AND u.user_id(+) = DECODE(v.level_id, 10004, v.level_value, NULL) AND pot.user_profile_option_name = 'MO: Operating Unit' ORDER BY 2, 3;
How profile values resolve at run time
When a form or program asks for a profile value, EBS looks from the most specific level to the least: user, then responsibility, then application, then site. The first level with a value wins. In R12 some profiles can also be set at server and organization levels, which are separate level ids, and not every profile allows every level.
This is why a user can see a different value from a colleague with the same responsibility: a user-level override beats the responsibility value. When someone reports that a setting does not take effect, query for rows at a more specific level first.
PL/SQL code reads the resolved value with FND_PROFILE.VALUE('PROFILE_NAME'), which uses the current session context. It returns null if the session has not been initialised with FND_GLOBAL.APPS_INITIALIZE for a user, responsibility and application.
BEGIN fnd_global.apps_initialize(user_id => :uid, resp_id => :rid, resp_appl_id => :aid); END;
SELECT fnd_profile.value('ORG_ID') FROM dual;Useful audit queries
To find every user-level override of a profile, filter LEVEL_ID 10004 and join FND_USER. To find where a particular value is set across all profiles, drop the profile name filter and filter PROFILE_OPTION_VALUE. This is how teams hunt for a hard-coded server name or directory path after cloning production to test.
After a clone, the profile values that point at production URLs, mailer hosts and directories are the usual cause of test systems sending email or writing files to the wrong place. Search for the production host name across the value column and fix the matches.
To see who changed a value and when, check LAST_UPDATE_DATE and LAST_UPDATED_BY on FND_PROFILE_OPTION_VALUES, or enable Sign-On Audit and profile auditing options if your policy requires a full history.
SELECT po.profile_option_name, v.level_id, v.level_value, v.profile_option_value, v.last_update_date FROM apps.fnd_profile_options po, apps.fnd_profile_option_values v WHERE v.profile_option_id = po.profile_option_id AND v.application_id = po.application_id AND UPPER(v.profile_option_value) LIKE UPPER('%prodhost%');Common pitfalls
- !Joining on PROFILE_OPTION_ID alone without APPLICATION_ID, which can return the wrong rows.
- !Forgetting the language filter on TL tables and getting duplicates.
- !Missing the LEVEL_VALUE_APPLICATION_ID join for responsibility rows.
- !Assuming a missing row means null, when it means the value is inherited from a higher level.
- !Calling FND_PROFILE.VALUE in a script without APPS_INITIALIZE and getting null.
How an ERP-grounded AI assistant handles this
This is a natural fit for a grounded assistant. Ask which users have a MO: Operating Unit override, or where a production hostname is still set after a clone, and it builds the join, shows the query and returns the rows from a read-only connection. It follows your EBS roles. The same assistant can feed the result into a FNDLOAD or script plan for you to review.
Frequently asked questions
What are the profile option level IDs in Oracle EBS?
10001 is Site, 10002 is Application, 10003 is Responsibility and 10004 is User. Some releases also use further level ids for server and organization, which apply only to profiles that allow those levels.
Where are profile option values stored?
In FND_PROFILE_OPTION_VALUES, with the definition in FND_PROFILE_OPTIONS and display names in FND_PROFILE_OPTIONS_TL. Each row is keyed by profile option, application, level id and level value.
Why does FND_PROFILE.VALUE return null in my script?
The session has no user, responsibility and application context. Call FND_GLOBAL.APPS_INITIALIZE with valid ids first, or set the context from the concurrent request. Also confirm the profile is actually set at a level that resolves for that context.
How do I find profiles still pointing at production after a clone?
Search the PROFILE_OPTION_VALUE column for the production host name or path across all profile options, then update the matches to the test values. Check the mailer, URLs and directory profiles in particular.
Related
SQL to Find Responsibilities Assigned to a User in Oracle EBS
Join FND_USER to FND_USER_RESP_GROUPS on USER_ID, then to FND_RESPONSIBILITY_TL on RESPONSIBILITY_ID and APPLICATION_ID to get the responsibility name, filtering the END_DATE columns to show only currently active assignments. This is the standard query DBAs and support teams use instead of clicking through System Administrator > Security > User > Define for every user.
How-toHow to use FNDLOAD to migrate lookups, profiles and menus between Oracle EBS instances
FNDLOAD is the command line utility that downloads AOL setup data into a flat .ldt file using a .lct configuration file, and uploads that file into another instance. The pattern is the same for every object: pick the correct .lct, download with the entity name, review the .ldt, then upload on the target as the APPS user.
Error fixFix Oracle EBS Workflow Notification Mailer Not Sending Emails
When EBS notifications stop reaching inboxes, check the mailer status in Oracle Applications Manager (OAM) Workflow Manager first - a Suspended or Error status almost always points at an SMTP or IMAP connectivity or credential problem introduced by a mail server change, not at Workflow itself. Fix the mail server configuration, restart the mailer component, and requeue the backlog.
How-toHow to Use Forms Personalization in Oracle EBS (No CUSTOM.pll Needed)
Forms Personalization lets you change field properties, set defaults, run built-ins and show messages on any Oracle Forms screen through Help > Diagnostics > Custom Code > Personalize, with no custom library or forms compile required. Rules are stored per form and responsibility in FND_FORM_CUSTOM_RULES and apply at runtime for every user who opens that form.
Error fixFix FRM-40735: WHEN-VALIDATE-RECORD Trigger Raised Unhandled Exception ORA-06508
FRM-40735 with ORA-06508 almost always means a PL/SQL package used by the form was recompiled while your session still held the old cached state, or the trigger's own exception handling does not trap a real data error. Exit the form completely and re-enter it; if the error repeats for all users, recompile invalid objects on the database and check for a bad custom trigger.
Error fixOracle EBS Concurrent Manager Will Not Start: How to Fix It
A concurrent manager that will not start in Oracle EBS is almost always one of three things: the node registered in FND_NODES no longer matches the actual hostname (common after cloning), stale OS processes are blocking a fresh start, or the database and listener the manager connects to are unreachable. Check the internal manager log, verify the node name, clear stale processes, and restart with adcmctl.sh.
AI for ERPAI for Oracle E-Business Suite, Without Leaving On-Prem
Add AI to Oracle E-Business Suite 12.2 without moving off-prem. Query concurrent programs, interface tables, and AP/PO data with a private LLM. See how.
Stuck on Oracle E-Business Suite?
Talk to engineers who work inside Oracle E-Business Suite every week, and who build private AI that answers these questions from your own ERP data.