Friday, July 5, 2013

Query to list concurrent program details with its parameter, values set and default value/type AND Attached Responsibilities

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';

8 comments:

  1. Very Nice Bro.. It's Very useful Info....

    ReplyDelete
  2. Thank you for these. They are very helpful.

    Would you have one that lists all of the stages of scheduled request set and the parameters that were set for each stage at the time of submission? I can see the parameters after the set has run, however, I would like to see the parms after the set has been submitted/scheduled but has not run yet.

    ReplyDelete
  3. Manohar Babu: You are human Gold!

    ReplyDelete