-->: To find the
responsibilty for which form was assigned
SELECT fu.user_name, fu.description, furg.start_date,
frvl.responsibility_name, fff.function_name, ff.form_name
FROM fnd_user fu,
fnd_user_resp_groups furg,
fnd_responsibility_vl frvl,
fnd_compiled_menu_functions fcmf,
fnd_form_functions_vl fff,
fnd_form_vl ff
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = frvl.responsibility_id
AND frvl.menu_id = fcmf.menu_id
AND fff.function_id = fcmf.function_id
AND fff.form_id = ff.form_id
AND (fu.end_date IS
NULL OR fu.end_date >= SYSDATE)
AND (furg.end_date IS
NULL OR furg.end_date >= SYSDATE)
AND (frvl.end_date IS
NULL OR frvl.end_date >= SYSDATE)
AND form_name IN
('FORMNAME');
-->: To find the
latest application version
select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION
version, START_DATE_ACTIVE updated,
ROW_SOURCE_COMMENTS "how it is
done",
BASE_RELEASE_FLAG "Base
version"
FROM AD_RELEASES
where END_DATE_ACTIVE IS
NULL;
-->: To find the
base application version
select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION
version, START_DATE_ACTIVE when updated,
ROW_SOURCE_COMMENTS "how it is
done"
from AD_RELEASES
where BASE_RELEASE_FLAG = 'Y'
;
-->: To find all
available application version
select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION
version, START_DATE_ACTIVE when updated,
END_DATE_ACTIVE "when lasted",
CASE WHEN
BASE_RELEASE_FLAG = 'Y' Then 'BASE VERSION' ELSE 'Upgrade' END "BASE/UPGRADE", ROW_SOURCE_COMMENTS "how it is done"
from AD_RELEASES;
-->: How many
users are connected to Oracle Applications.
select distinct
fu.user_name User_Name,fr.RESPONSIBILITY_KEY
Responsibility
from fnd_user fu, fnd_responsibility
fr, icx_sessions ic
where fu.user_id = ic.user_id AND
fr.responsibility_id = ic.responsibility_id AND
ic.disabled_flag='N'
AND
ic.responsibility_id is not
null AND
ic.last_connect like sysdate;
-->: HOW TO find
a concurrent program's trace file
SELECT req.request_id ,req.logfile_node_name
node
,req.oracle_Process_id ,req.enable_trace
,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
,prog.user_concurrent_program_name
,execname.execution_file_name
,execname.subroutine_name ,phase_code
,status_code ,ses.SID ,ses.serial# ,ses.module
,ses.machine
FROM fnd_concurrent_requests req
,v$session
ses ,v$process proc ,v$parameter dest
,v$parameter
dbnm ,fnd_concurrent_programs_vl prog
,fnd_executables execname
WHERE 1=1
--AND req.request_id = &request
AND req.oracle_process_id=proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME='user_dump_dest'
AND dbnm.NAME='db_name'
AND req.concurrent_program_id
= prog.concurrent_program_id
AND req.program_application_id
= prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id=execname.executable_id;
-->: To Get
Detail of Locks with Object Locked
SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name
locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS
os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins fl
,fnd_user fu
,v$locked_object vlocked
,v$process vp
,v$session
vs
,dba_objects c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id
= c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
--AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') ||
'%'
AND nvl(vs.status,'XX') !=
'KILLED';
-->: 1) Query
useful when user wants to get application wise responsibility list
SELECT (SELECT
application_short_name
FROM fnd_application fa
WHERE fa.application_id = frt.application_id) application,
frt.responsibility_id, frt.responsibility_name
FROM apps.fnd_responsibility_tl
frt;
-->: 2)User to
check menu attached with a reponsilblity
SELECT DISTINCT
a.responsibility_name, c.user_menu_name
FROM apps.fnd_responsibility_tl
a,
apps.fnd_responsibility b,
apps.fnd_menus_tl c,
apps.fnd_menus d,
apps.fnd_application_tl e,
apps.fnd_application f
WHERE a.responsibility_id(+)
= b.responsibility_id
--AND a.responsibility_name=
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE
= 'US';
-->: 3)To get
submenus and Function attached to this Main menu
SELECT c.prompt, c.description
FROM apps.fnd_menus_tl a, fnd_menu_entries_tl
c
WHERE a.menu_id = c.menu_id
p_MENU_NAME is parameter
-->: 4)To get
assigned responsibility to a user.
SELECT UNIQUE
u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+)
= u.user_id
AND g.responsibility_application_id
= a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY
SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60);
-->: 4)To get
responsibility and attached request groups.
SELECT responsibility_name
responsibility,
request_group_name,
frg.description
FROM fnd_request_groups frg,
fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY
responsibility_name
-->: 5) Purpose
To get all Request attached to a responsibility
SELECT responsibility_name ,
frg.request_group_name,
fcpv.user_concurrent_program_name,
fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'P'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY
responsibility_name
-->: 6)To get
all request with application
SELECT fa.application_short_name,
fcpv.user_concurrent_program_name,
description,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage
Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored
Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
output_file_type,
program_type,
printer_name,
minimum_width,
minimum_length,
concurrent_program_name,
concurrent_program_id
FROM fnd_concurrent_programs_vl
fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
ORDER BY
description
-->: 6) To
calculate request time
SELECT f.request_id ,
pt.user_concurrent_program_name
user_concurrent_program_name
, f.actual_start_date
actual_start_date
, f.actual_completion_date
actual_completion_date,
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
|| ' HOURS '
||
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
|| ' MINUTES '
||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS '
time_difference
,
DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.description||']',p.concurrent_program_name)
concurrent_program_name
, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase
, f.status_code
FROM apps.fnd_concurrent_programs
p
, apps.fnd_concurrent_programs_tl
pt
, apps.fnd_concurrent_requests
f
WHERE f.concurrent_program_id
= p.concurrent_program_id
and f.program_application_id
= p.application_id
and f.concurrent_program_id
= pt.concurrent_program_id
and f.program_application_id
= pt.application_id
AND pt.language
= USERENV('Lang')
and f.actual_start_date is
not null
ORDER by
f.actual_completion_date-f.actual_start_date desc;
No comments:
Post a Comment