# SQL query to find profile option values by level in Oracle EBS > Source: https://netray.co/erp-help/ebs-profile-option-values-sql-query-by-level > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: ERP Help ## 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. ## Kind how-to ## System oracle-ebs ## Erp Oracle E-Business Suite ## Module System Administration / Profile Options ## Query SQL query to find profile option values by level in Oracle EBS ## Alternate Queries - 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 ## Applies To Oracle E-Business Suite 11i, R12.1 and R12.2 ## Steps ### List values for one profile option at every level **Items:** - Find the internal name: query FND_PROFILE_OPTIONS_TL by USER_PROFILE_OPTION_NAME to get PROFILE_OPTION_NAME. - Join FND_PROFILE_OPTIONS on PROFILE_OPTION_NAME to get PROFILE_OPTION_ID and APPLICATION_ID. - Join FND_PROFILE_OPTION_VALUES on both PROFILE_OPTION_ID and APPLICATION_ID. - Decode LEVEL_ID: 10001 is Site, 10002 Application, 10003 Responsibility, 10004 User. - Resolve LEVEL_VALUE to a name using FND_APPLICATION_TL, FND_RESPONSIBILITY_TL or FND_USER depending on the level. - Filter on PROFILE_OPTION_VALUE if you need to find where a specific value is set. - Remember that FND_PROFILE_OPTION_VALUES stores one row per set level, so a missing row means the value is inherited from a higher level. ## Sections ### The core query **Body:** 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. **Code:** 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 **Body:** 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. **Code:** - 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 **Body:** 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. **Code:** 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%'); ## 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. ## Ai Angle 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. ## Faq ### What are the profile option level IDs in Oracle EBS? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms fnd_profile_option_values, profile option query oracle apps, profile option level id 10001 10002 10003 10004, fnd_profile.value null, oracle ebs profile options sql, find profile options after clone, MO operating unit profile query, apps_initialize profile --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json