Useful SQLs/ PLSQLs

I am listing here some of the SQL Queries and PLSQL blocks which we use very often in Oracle Applications. It is cumbersome to see this data in application through delivered screens.

1. Finding the value for a profile


SELECT fpot.user_profile_option_name profile,
fpov.profile_option_value VALUE,
decode(fpov.level_id,
10001,
'SITE',
10002,
'APPLICATION',
10003,
'RESPONSIBILITY',
10004,
'USER') "Apply On",
fa.application_name application,
fr.responsibility_name responsibility,
fu.user_name "USER"
FROM fnd_profile_option_values fpov,
fnd_profile_options_tl fpot,
fnd_profile_options fpo,
fnd_application_tl fa,
fnd_responsibility_vl fr,
fnd_user fu,
fnd_logins fl
WHERE fpo.profile_option_id = fpov.profile_option_id
AND TRIM(fpot.profile_option_name) = TRIM(fpo.profile_option_name)
AND fa.application_id(+) = fpov.level_value
AND fr.application_id(+) = fpov.level_value_application_id
AND fr.responsibility_id(+) = fpov.level_value
AND fu.user_id(+) = fpov.level_value
AND fl.login_id(+) = fpov.last_update_login
-- AND fpo.profile_option_name = 'FND_CUSTOM_OA_DEFINTION'
-- AND fpov.profile_option_value = 1067
-- AND fr.responsibility_name like '%Manager%'
AND fpot.user_profile_option_name = 'MO: Operating Unit'
ORDER BY 1;


2. Element Entries


SELECT ee.element_type_id '-' elt.element_name el_name,
to_char(ee.assignment_id) asId,
to_char(ee.element_entry_id) eeId,
ee.effective_start_date esd,
ee.effective_end_date eed,
to_char(ee.creator_id) Creator,
to_char(ee.element_link_id) el_id,
to_char(ee.object_version_number) ovn
FROM apps.pay_element_entries_f ee, pay_element_types_f elt
WHERE ee.assignment_id IN (SELECT assignment_id
FROM per_all_assignments_f
WHERE person_id = &v_personId AND primary_flag = 'Y')
AND elt.element_type_id(+) = ee.element_type_id
AND ee.effective_start_date BETWEEN elt.effective_start_date AND elt.effective_end_date;


3. Element Values


SELECT to_char(elt.element_type_id) '-'elt.element_name el_name,
to_char(eev.input_value_id) '-' piv.NAME iv_name,
to_char(eev.element_entry_id) eeId,
to_char(eev.element_entry_value_id) eevId,
eev.screen_entry_value,
eev.effective_start_date esd,
eev.effective_end_date eed
FROM pay_element_entry_values_f eev,
pay_input_values_f piv,
pay_element_types_f elt
WHERE eev.element_entry_id IN (
SELECT element_entry_id
FROM apps.pay_element_entries_f ee
WHERE assignment_id IN
(SELECT assignment_id
FROM per_all_assignments_f
WHERE person_id = &v_personId AND primary_flag = 'Y'))
AND elt.element_type_id(+) = piv.element_type_id
AND eev.effective_start_date BETWEEN elt.effective_start_date AND elt.effective_end_date
AND eev.input_value_id = piv.input_value_id
AND eev.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
ORDER BY 1, 2;


4. Finding User name against a responsibility


SELECT usr.user_name,
resp.responsibility_name
FROM fnd_user usr,
fnd_responsibility_vl resp,
fnd_user_resp_groups_direct usr_resp
WHERE usr.user_id = usr_resp.user_id
AND usr_resp.responsibility_id = resp.responsibility_id
AND Upper(resp.responsibility_name) LIKE Upper('%HRMS%Workbench%');


5. Finding menu for a given function


SELECT fn.PROMPT ,
fnf.user_function_name,
men.user_menu_name ,
fn.DESCRIPTION ,
fn.GRANT_FLAG
FROM FND_MENU_ENTRIES_VL fn ,
fnd_form_functions_vl fnf,
fnd_menus_vl men
WHERE fn.function_id = fnf.function_id
AND fn.menu_id = men.menu_id
AND fn.prompt = 'Change Job'
ORDER BY ENTRY_SEQUENCE;


6. Compile Menus through PLSQL (rather than concurrent program)


declare
l_r number;
l_e varchar2(1000);
begin
fnd_function.compile(l_e,l_r,'N');
end;


Lot of more stuff following this post. Any doubts regarding any query drop a comment. I will try to see if I have the right answer.