1. query to list all the responsibilities attached to a user
select fu.user_name, fr.responsibility_name, furg.start_date, furg.end_date
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('lang')
2.to find from which responsibility a concurrent program can be run
select distinct
a.user_concurrent_program_name,
a.description,
request_group_name,
e.responsibility_name
from
fnd_concurrent_programs_tl a,
fnd_request_groups b,
fnd_request_group_units c,
fnd_responsibility d,
fnd_responsibility_tl e
where
a.concurrent_program_id = c.request_unit_id
and b.request_group_id = c.request_group_id
and b.request_group_id = d.request_group_id
and d.responsibility_id = e.responsibility_id
and a.application_id = b.application_id
and b.application_id = c.application_id
and d.application_id = e.application_id
and a.concurrent_program_id = :p_conc_program_id
3.provide concurrent program name to the following query. It lists all the request sets which are created with the concurrent program given.
select distinct user_request_set_name
from fnd_request_sets_tl
where request_set_id in
(select request_set_id
from fnd_request_set_programs
where concurrent_program_id =
(select concurrent_program_id
from fnd_concurrent_programs_tl
where upper(user_concurrent_program_name) = upper( '&enter_prog_name')));
4. provide the request set name to the following query. It lists all concurrent programs of this request set.
select user_concurrent_program_name
from fnd_concurrent_programs_tl
where concurrent_program_id in
(select concurrent_program_id
from fnd_request_set_programs
where request_set_id =
(select request_set_id
from fnd_request_sets_tl
where upper(user_request_set_name) = upper('&request_set_name')));
5. query to list concurrent program details with its parameter, values set and default value/type:
select fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, fav.application_short_name
, fav.application_name
, fav.application_id
, fdfcuv.end_user_column_name
, fdfcuv.form_left_prompt prompt
, fdfcuv.enabled_flag
, fdfcuv.required_flag
, fdfcuv.display_flag
, fdfcuv.flex_value_set_id
, ffvs.flex_value_set_name
, flv.meaning default_type
, fdfcuv.default_value
from fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl fdfcuv
, fnd_flex_value_sets ffvs
, fnd_lookup_values flv
, fnd_application_vl fav
where fcp.concurrent_program_id = fcpl.concurrent_program_id
and fcpl.user_concurrent_program_name = :conc_prg_name
and fcpl.language = 'us'
and fav.application_id=fcp.application_id
and fdfcuv.descriptive_flexfield_name = '$srs$.' || fcp.concurrent_program_name
and ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
and flv.lookup_type(+) = 'flex_default_type'
and flv.lookup_code(+) = fdfcuv.default_type
and flv.language(+) = userenv ('lang');
6. query to find out concurrent program details and its parameters
select fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, fcp.concurrent_program_id
, fav.application_short_name
, fav.application_name
, fav.application_id
, fdfcuv.end_user_column_name
, fdfcuv.form_left_prompt prompt
, fdfcuv.enabled_flag
, fdfcuv.required_flag
, fdfcuv.display_flag
from fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl fdfcuv
, fnd_application_vl fav
where fcp.concurrent_program_id = fcpl.concurrent_program_id
and fcpl.user_concurrent_program_name = :conc_prg_name
and fav.application_id=fcp.application_id
and fcpl.language = 'US'
and fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name;
7 For checking the locks in concurrent jobs
select decode(request,0,'holder: ','waiter: ')||sid sess,inst_id,id1, id2, lmode, request, type from gv$lock
where (id1, id2, type) in (select id1, id2, type from gv$lock where request>0) order by id1,request;
8 For checking the concurrent programs running currently with details of processed time-- and start date
select distinct c.user_concurrent_program_name,round(((sysdate-a.actual_start_date)*24*60*60/60),2) as process_time,
a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,(a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 as lag_time,d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.fnd_concurrent_programs_tl c,apps.fnd_user d
where a.concurrent_program_id=b.concurrent_program_id and b.concurrent_program_id=c.concurrent_program_id and
a.requested_by=d.user_id and status_code='r' order by process_time desc;
9 for checking last run of a concurrent program along with processed time
-- useful to find the details of concurrent programs which run daily and comparison purpose
select distinct c.user_concurrent_program_name,
round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) as process_time,
a.request_id,a.parent_request_id,to_char(a.request_date,'dd-mon-yy hh24:mi:ss'),to_char(a.actual_start_date,'dd-mon-yy hh24:mi:ss'),
to_char(a.actual_completion_date,'dd-mon-yy hh24:mi:ss'), (a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 as lag_time,
d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b ,
apps.fnd_concurrent_programs_tl c,
apps.fnd_user d
where a.concurrent_program_id= b.concurrent_program_id and
b.concurrent_program_id=c.concurrent_program_id and
a.requested_by =d.user_id and
-- trunc(a.actual_completion_date) = '24-aug-2005'
c.user_concurrent_program_name='incentive compensation analytics - odi' -- and argument_text like '%, , , , ,%';
-- and status_code!='c'
10 For checking the last run of concurrent program.
- use below query to check all the concurrent request running which may refer given package
-- this is very useful check before compiling any package on given instance.
-- the query can be modified as per requirement.
-- remove fnd_concurrent_requests table and joins to check all program dependent on given package.
select
fcr.request_id
,fcpv.user_concurrent_program_name
,fcpv.concurrent_program_name
,fcpv.concurrent_program_id
,fcr.status_code
,fcr.phase_code
from fnd_concurrent_programs_vl fcpv
,fnd_executables fe
,sys.dba_dependencies dd
,fnd_concurrent_requests fcr
where fcpv.executable_id = fe.executable_id
and fe.execution_method_code = 'i'
and substr(fe.execution_file_name,1,instr(fe.execution_file_name, '.', 1, 1) - 1) = upper(dd.name)
and dd.referenced_type in ('view', 'table', 'trigger', 'package') -- add as required
--and referenced_owner = 'xxcus'
and dd.referenced_name = upper('&package_name')
and fcr.concurrent_program_id = fcpv.concurrent_program_id
and fcr.phase_code not in ( 'c','p');
11 the following query will display the time taken to execute the concurrent programs
--for a particular user with the latest concurrent programs sorted in least time taken
-- to complete the request.
select
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.actual_completion_date end_on,
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,
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_start_date desc;
12 : by using the below query we can get sid,serial#,spid of the concurrent request..
select a.request_id, d.sid, d.serial# , c.spid
from apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
where a.controlling_manager = b.concurrent_process_id
and c.pid = b.oracle_process_id
and b.session_id=d.audsid
and a.request_id = &request_id
and a.phase_code = 'r';
13 : by using below concurrent manager and program rules...
--gives detail of the concurrent_queue_name and user_concurrent_program_name
select b.concurrent_queue_name, c.user_concurrent_program_name
from fnd_concurrent_queue_content a, fnd_concurrent_queues b, fnd_concurrent_programs_vl c
where a.queue_application_id = 283
and a.concurrent_queue_id = b.concurrent_queue_id
and a.type_id = c.concurrent_program_id
order by decode(include_flag, 'i', 1, 2), type_code;
14 : gives details of running concurrent jobs
select distinct c.user_concurrent_program_name,
round(((sysdate-a.actual_start_date)*24*60*60/60),2) as process_time,
a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,
(a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 as lag_time,
d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b ,
apps.fnd_concurrent_programs_tl c,
apps.fnd_user d
where a.concurrent_program_id=b.concurrent_program_id and
b.concurrent_program_id=c.concurrent_program_id and
a.requested_by=d.user_id and
status_code='r' order by process_time desc;
15 : gives detail of concurrent job completed and pending
select
fcr.request_id
,fcpv.user_concurrent_program_name
,fcpv.concurrent_program_name
,fcpv.concurrent_program_id
,fcr.status_code
,fcr.phase_code
from fnd_concurrent_programs_vl fcpv
,fnd_executables fe
,sys.dba_dependencies dd
,fnd_concurrent_requests fcr
where fcpv.executable_id = fe.executable_id
and fe.execution_method_code = 'i'
and substr(fe.execution_file_name,1,instr(fe.execution_file_name, '.', 1, 1) - 1) = upper(dd.name)
and dd.referenced_type in ('view', 'table', 'trigger', 'package') -- add as required
--and referenced_owner = 'xxcus'
and dd.referenced_name = upper('&package_name')
and fcr.concurrent_program_id = fcpv.concurrent_program_id
and fcr.phase_code not in ( 'c','p');
16 :gives detail of running and completed concurrent jobs with start date and end date
-- latest one at the top
select
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.actual_completion_date end_on,
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,
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_start_date desc;
17 : wait events details related with concurrent programs
select s.saddr, s.sid, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal,
upper (s.program) program, s.type, s.sql_address, s.sql_hash_value,
s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id,
s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled,
s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2,
s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#,
s.wait_class, s.wait_time, s.seconds_in_wait, s.state,
s.wait_time_micro, s.time_remaining_micro,
s.time_since_last_wait_micro, s.service_name, s.sql_trace,
s.sql_trace_waits, s.sql_trace_binds, s.sql_trace_plan_stats,
s.session_edition_id, s.creator_addr, s.creator_serial#
from v$session s
where ( (s.username is not null)
and (nvl (s.osuser, 'x') <> 'system')
and (s.type <> 'background') and status='active'
)
order by "program";
18 : to find the pid of the concurrent job and kill it.
select a.inst_id, sid, b.spid
from gv$session a, gv$process b,apps.fnd_concurrent_requests c where a.paddr = b.addr and request_id ='31689665'
and a.inst_id = b.inst_id and c.os_process_id = a.process;
19 :to find the database sid of the concurrent job
-- we need our concurrent request id as an input.
-- c.spid= is the operating system process id
-- d.sid= is the oracle process id
sql> column process heading "fndlibr pid"
select a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.spid
from apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
where a.controlling_manager = b.concurrent_process_id
and c.pid = b.oracle_process_id
and b.session_id=d.audsid
and a.request_id = &request_id
and a.phase_code = 'r';
