Query for Profile Options (This will work in read only access too)
SELECT fpot.user_profile_option_name AS "USER_PROFILE_OPTION_NAME",
DECODE (level_id,
'10001', 'Site',
'10002', 'Application',
'10003', 'Responsibility',
'10004', 'User',
'10005', 'Server',
'10007', 'Organization'
) AS "LEVEL",
fpov.profile_option_value AS "Value", fpo.profile_option_name,
SUBSTR (fat.application_name, 1, 20) AS "Application",
(SELECT responsibility_name
FROM fnd_responsibility_tl frt
WHERE frt.responsibility_id = fpov.level_value
AND fpov.level_id = 10003) "Responsibility",
(SELECT user_name
FROM fnd_user fu
WHERE user_id = fpov.level_value
AND fpov.level_id = 10004) "User"
FROM fnd_profile_options fpo,
fnd_profile_options_tl fpot,
fnd_application_tl fat,
fnd_profile_option_values fpov
WHERE fpo.application_id = fat.application_id
AND fpov.application_id = fat.application_id
AND fpov.profile_option_id = fpo.profile_option_id
AND fpo.profile_option_name = fpot.profile_option_name
AND fpov.level_id = NVL (:p_level_id, fpov.level_id)
AND UPPER (fpot.user_profile_option_name) LIKE
NVL (UPPER ('%&User_Profile_Option_Name%'),
UPPER (fpot.user_profile_option_name)
);
------------Calculating time taken to complete execution of the program---------------------------
select user_concurrent_program_name, actual_start_date,actual_completion_date
,trunc(86400*(actual_completion_date - actual_start_date ))-60*(trunc((86400*(actual_completion_date - actual_start_date ))/60)) "Sec"
,trunc((86400*(actual_completion_date - actual_start_date ))/60)-60*(trunc(((86400*(actual_completion_date - actual_start_date ))/60)/60)) "Min"
,trunc(((86400*(actual_completion_date - actual_start_date ))/60)/60)-24*(trunc((((86400*(actual_completion_date - actual_start_date ))/60)/60)/24)) "Hrs"
,r.argument_text,r.request_id,r.parent_request_id
from apps.fnd_concurrent_programs_vl p
,apps.fnd_executables e
,apps.fnd_lookup_values flv
,apps.fnd_concurrent_requests r
,apps.fnd_responsibility_vl resp
,apps.fnd_user fu
where 1=1
and p.user_concurrent_program_name like '%CP%'
and p.executable_id = e.executable_id
and r.requested_by = fu.user_id
and flv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
and flv.lookup_code = e.execution_method_code
and r.concurrent_program_id = p.concurrent_program_id
and resp.responsibility_id = r.responsibility_id
and r.requested_start_date between '27-AUG-2013' and sysdate
-- and r.argument1 = 'parameter1'
-- and r.request_id = 7344623
--and r.parent_request_id IN ('7329248','7313636','7312292') --'7314309',
order by r.requested_start_date desc;
-------User Responsibilities----------
select u.user_name, resp.responsibility_name
from apps.fnd_user_resp_groups g,
apps.fnd_responsibility_vl resp
,apps.fnd_user u
where u.user_name = '837166'
and u.user_id = g.user_id
and g.responsibility_id = resp.responsibility_id
order by 2;
-- Query to find request group and application name for a concurrent program
-------------------------------------------------------------------------------
SELECT cpt.user_concurrent_program_name "Concurrent Program Name",
DECODE(rgu.request_unit_type,
'P', 'Program',
'S', 'Set',
rgu.request_unit_type) "Unit Type",
cp.concurrent_program_name "Concurrent Program Short Name",
rg.application_id "Application ID",
rg.request_group_name "Request Group Name",
fat.application_name "Application Name",
fa.application_short_name "Application Short Name",
fa.basepath "Basepath"
FROM fnd_request_groups rg,
fnd_request_group_units rgu,
fnd_concurrent_programs cp,
fnd_concurrent_programs_tl cpt,
fnd_application fa,
fnd_application_tl fat
WHERE rg.request_group_id = rgu.request_group_id
AND rgu.request_unit_id = cp.concurrent_program_id
AND cp.concurrent_program_id = cpt.concurrent_program_id
AND rg.application_id = fat.application_id
AND fa.application_id = fat.application_id
AND cpt.language = USERENV('LANG')
AND fat.language = USERENV('LANG')
AND cpt.user_concurrent_program_name = 'CP';
-----Getting submitted concurrent requests information---------
select r.request_id,p.user_concurrent_program_name
, e.executable_name, flv.meaning, execution_file_name
,r.requested_start_date,r.actual_start_date,r.actual_completion_date
,fu.user_name
, r.phase_code, r.status_code, r.parent_request_id,
resp.responsibility_name
, r.argument_text, r.argument1, r.argument2, r.argument3,r.argument4,r.argument5,r.argument6,r.argument7,r.argument8,
r.argument9,r.argument10,r.argument11,r.argument12,r.argument13,r.argument14,r.argument15,r.argument16,r.argument17
from apps.fnd_concurrent_programs_vl p
,apps.fnd_executables e
,apps.fnd_lookup_values flv
,apps.fnd_concurrent_requests r
,apps.fnd_responsibility_vl resp
,apps.fnd_user fu
where 1=1
and p.user_concurrent_program_name like 'Statement Generation Program%'
and p.executable_id = e.executable_id
and r.requested_by = fu.user_id
--and r.phase_code ='E'
-- and r.request_id in (7284898,7284934)
--(7284901,7284935)
and flv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
and flv.lookup_code = e.execution_method_code
and r.concurrent_program_id = p.concurrent_program_id
and resp.responsibility_id = r.responsibility_id
-- and r.requested_start_date between '21-AUG-2011' and sysdate
--and argument1 like 'APAC%'
--and r.phase_code != 'C'
order by r.requested_start_date desc;
-----to find session ID for a particular concurrent request-----------
SELECT ses.sid,
ses.serial#
FROM v$session ses,
v$process pro
WHERE ses.paddr = pro.addr
AND pro.spid IN (SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = &request_id);
-----Form Functions and responisbilities------
SELECT LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.entry_sequence SEQUENCE,
LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.prompt prompt,
menu_entry.grant_flag grant_flag,
DECODE (menu_entry.sub_menu_id,
NULL, 'FUNCTION',
DECODE (menu_entry.function_id, NULL, 'SUBMENU', 'BOTH')
) TYPE,
menu2.user_menu_name, func2.user_function_name
FROM fnd_menu_entries_vl menu_entry,
fnd_menus_tl menu,
fnd_form_functions_tl func,
fnd_form_functions_tl func2,
fnd_menus_tl menu2
WHERE menu_entry.sub_menu_id = menu.menu_id(+)
AND menu_entry.function_id = func.function_id(+)
AND menu_entry.sub_menu_id = menu2.menu_id(+)
AND menu_entry.function_id = func2.function_id(+)
AND grant_flag = 'Y'
and func2.user_function_name like '%Customer Hold%'
START WITH menu_entry.menu_id =
(SELECT menu_id
FROM fnd_menus_tl menu2
WHERE menu2.user_menu_name = :MENU_NAME)
CONNECT BY menu_entry.menu_id = PRIOR menu_entry.sub_menu_id
ORDER SIBLINGS BY menu_entry.entry_sequence;
-----Checking Patch Levels-------
select * from apps.ad_bugs
where bug_number = '4602427';
select * from apps.ad_applied_patches
where patch_name = '4602427';
No comments:
Post a Comment