andrewv3-blog

[andrewv3-blog]

Searching for Profile Options

I use this query to find profile option values in Oracle as there isn't a quick way to find the value for more than 1 responsibility or user at a time.

Note, if there is a profile with no value at any level, then the query will not return any results.

SELECT   user_profile_option_name,          DECODE (level_id,                  '10001', 'Site',                  '10002', 'Appl',                  '10003', 'Resp',                  '10004', 'User',                  '10005', 'Server',                  level_id) AT,          DECODE (level_id,                  '10001', ' ',                  '10002', (SELECT application_name                              FROM apps.fnd_application_tl                             WHERE application_id = level_value),                  '10003', (SELECT responsibility_name                              FROM apps.fnd_responsibility_tl                             WHERE responsibility_id = level_value                               AND application_id =                                                   v.level_value_application_id),                  '10004', (SELECT user_name                              FROM apps.fnd_user                             WHERE user_id = level_value),                  level_value) "level_value",          profile_option_value,          u.user_name updated_by,          v.last_update_date     FROM apps.fnd_profile_options o,          apps.fnd_profile_option_values v,          apps.fnd_profile_options_tl tl,          apps.fnd_user u    WHERE o.profile_option_name = tl.profile_option_name      AND o.profile_option_id = v.profile_option_id      AND tl.user_profile_option_name LIKE '&profile'      AND u.user_id = v.last_updated_by ORDER BY user_profile_option_name, level_id, level_value,          profile_option_value;

[andrewv3-blog]

Finding SQL objects quickly in Oracle R12

Looking for specific error messages thrown by Oracle Forms/JSPs can be relatively easy. Rather than looking through the database packages which can be slow, using grep, its possible to look through the source files for your error message.

Oracle Directories are laid out by product. For example, the Enterprise Asset maangement product sits in $EAM_TOP and Payables sits in $AP_TOP.

The directory $XX_TOP/patch/115/sql contains all the package source that resides in the database. So, if I'm looking for an EAM package, it will sit in $EAM_TOP/patch/115/sql.

Then a quick grep command can help find the message.

grep -i EAM_PM_HD_NOT_UNIQUE_SET_NAME $EAM_TOP/patch/115/sql/*

To find the message name, look in the table FND_NEW_MESSAGES.