Useful queries for Oracle EBS
Posted: Fri Jan 12, 2007 2:13 am
. <b>To check responsibility which contact given function</b>
1. To get form Id from database
SELECT FUNCTION_ID, USER_FUNCTION_NAME FROM FND_FORM_FUNCTIONS_TL
You can also use
SELECT FUNCTION_ID, FUNCTION_NAME FROM FND_FORM_FUNCTIONS
2. Pass The obtained Function id or function name to following query
SELECT frv.responsibility_name, frv.description
FROM fnd_responsibility_vl frv, fnd_form_functions fff
WHERE fff.function_name = 'FND_FNDATDAG' ------Pass Function name Here
--f.form_id =p_form_id
AND frv.menu_id NOT IN (
SELECT frf.action_id
FROM fnd_resp_functions frf
WHERE frf.action_id = frv.menu_id
AND frf.rule_type = 'M')
AND frv.menu_id IN (SELECT me.menu_id
FROM fnd_menu_entries me
START WITH me.function_id = fff.function_id
CONNECT BY PRIOR me.menu_id = me.sub_menu_id)
AND fff.function_id NOT IN (
SELECT frf.action_id
FROM fnd_resp_functions frf
WHERE frf.action_id = fff.function_id AND frf.rule_type = 'F')
? <b>To Check The Profile Options Which Are Modified</b>
SELECT t.user_profile_option_name, profile_option_value, v.creation_date,
v.last_update_date,
v.creation_date - v.last_update_date "change Status",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;
? <b>Query used To Check The Form personalization</b>
SELECT ffcr.function_name, ffft.user_function_name "User Form Name", ffcr.form_name "from Source File Name",
Ffcr.ID "Form ID",
(SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = ffcr.created_by) "Created By ",
ffcr.creation_date,
ffcr.last_update_date,
ffcr.SEQUENCE,
ffcr.rule_key,
ffcr.description,
ffcr.rule_type,
ffcr.enabled,
ffcr.trigger_event,
ffcr.trigger_object,
ffcr.condition,
ffcr.fire_in_enter_query
FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
For More detail on form Personalization Use Following Tables (Rule_id) is reference key these tables
applsys.fnd_form_custom_actions,applsys.fnd_form_custom_scopes
By using Following query you can get application wise detail
SELECT (SELECT application_name
FROM fnd_application_tl fa
WHERE fa.application_id = ff.application_id) application, ffcr.ID,
ffcr.SEQUENCE, ffcr.function_name,
REPLACE (ffcr.description, CHR (39), CHR (39) || CHR (39)) description,
ffcr.trigger_event, ffcr.trigger_object,
REPLACE (REPLACE (ffcr.condition, CHR (10), CHR (32)),
CHR (39),
CHR (39) || CHR (39)
) condition,
ffcr.enabled, ffcr.fire_in_enter_query, ffcr.rule_key, ffcr.form_name,
ffcr.rule_type
FROM apps.fnd_form_custom_rules ffcr, applsys.fnd_form ff
WHERE (ff.form_name) = (ffcr.form_name)
? <b>How To Check Patch Level Status</b>
select a.application_name,
decode(b.status,'I','Installed','S','Shared','N/A') STATUS,PATCH_LEVEL
from APPS.fnd_application_vl a,
APPS.fnd_product_installations b
where a.application_id = b.application_id
? <b>Get Request Group name Associated With Application</b>
SELECT g.request_group_name, c.user_concurrent_program_name,
a.application_name, g.application_id, g.request_group_id,
u.unit_application_id, u.request_unit_id
FROM fnd_request_groups g,
fnd_request_group_units u,
fnd_application_tl a,
fnd_concurrent_programs_tl c
WHERE g.request_group_id = u.request_group_id
AND u.unit_application_id = a.application_id
AND u.request_unit_id = c.concurrent_program_id
ORDER BY c.user_concurrent_program_name,
a.application_name,
g.request_group_id
1. To get form Id from database
SELECT FUNCTION_ID, USER_FUNCTION_NAME FROM FND_FORM_FUNCTIONS_TL
You can also use
SELECT FUNCTION_ID, FUNCTION_NAME FROM FND_FORM_FUNCTIONS
2. Pass The obtained Function id or function name to following query
SELECT frv.responsibility_name, frv.description
FROM fnd_responsibility_vl frv, fnd_form_functions fff
WHERE fff.function_name = 'FND_FNDATDAG' ------Pass Function name Here
--f.form_id =p_form_id
AND frv.menu_id NOT IN (
SELECT frf.action_id
FROM fnd_resp_functions frf
WHERE frf.action_id = frv.menu_id
AND frf.rule_type = 'M')
AND frv.menu_id IN (SELECT me.menu_id
FROM fnd_menu_entries me
START WITH me.function_id = fff.function_id
CONNECT BY PRIOR me.menu_id = me.sub_menu_id)
AND fff.function_id NOT IN (
SELECT frf.action_id
FROM fnd_resp_functions frf
WHERE frf.action_id = fff.function_id AND frf.rule_type = 'F')
? <b>To Check The Profile Options Which Are Modified</b>
SELECT t.user_profile_option_name, profile_option_value, v.creation_date,
v.last_update_date,
v.creation_date - v.last_update_date "change Status",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;
? <b>Query used To Check The Form personalization</b>
SELECT ffcr.function_name, ffft.user_function_name "User Form Name", ffcr.form_name "from Source File Name",
Ffcr.ID "Form ID",
(SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = ffcr.created_by) "Created By ",
ffcr.creation_date,
ffcr.last_update_date,
ffcr.SEQUENCE,
ffcr.rule_key,
ffcr.description,
ffcr.rule_type,
ffcr.enabled,
ffcr.trigger_event,
ffcr.trigger_object,
ffcr.condition,
ffcr.fire_in_enter_query
FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
For More detail on form Personalization Use Following Tables (Rule_id) is reference key these tables
applsys.fnd_form_custom_actions,applsys.fnd_form_custom_scopes
By using Following query you can get application wise detail
SELECT (SELECT application_name
FROM fnd_application_tl fa
WHERE fa.application_id = ff.application_id) application, ffcr.ID,
ffcr.SEQUENCE, ffcr.function_name,
REPLACE (ffcr.description, CHR (39), CHR (39) || CHR (39)) description,
ffcr.trigger_event, ffcr.trigger_object,
REPLACE (REPLACE (ffcr.condition, CHR (10), CHR (32)),
CHR (39),
CHR (39) || CHR (39)
) condition,
ffcr.enabled, ffcr.fire_in_enter_query, ffcr.rule_key, ffcr.form_name,
ffcr.rule_type
FROM apps.fnd_form_custom_rules ffcr, applsys.fnd_form ff
WHERE (ff.form_name) = (ffcr.form_name)
? <b>How To Check Patch Level Status</b>
select a.application_name,
decode(b.status,'I','Installed','S','Shared','N/A') STATUS,PATCH_LEVEL
from APPS.fnd_application_vl a,
APPS.fnd_product_installations b
where a.application_id = b.application_id
? <b>Get Request Group name Associated With Application</b>
SELECT g.request_group_name, c.user_concurrent_program_name,
a.application_name, g.application_id, g.request_group_id,
u.unit_application_id, u.request_unit_id
FROM fnd_request_groups g,
fnd_request_group_units u,
fnd_application_tl a,
fnd_concurrent_programs_tl c
WHERE g.request_group_id = u.request_group_id
AND u.unit_application_id = a.application_id
AND u.request_unit_id = c.concurrent_program_id
ORDER BY c.user_concurrent_program_name,
a.application_name,
g.request_group_id