Logo Background

Oracle Script For Concurrent Request Information

  • By on March 16, 2009 | No Comments

    This command is proven useful to fetch the responsibility name, instance name, log file location, output file location and running time of concurrent request in Oracle Applications

    SELECT a.request_id
    ,frt.responsibility_name
    ,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
    ,a.description
    ,a.ARGUMENT_TEXT
    ,b.node_name
    ,b.db_instance
    ,a.oracle_process_id "SPID"
    ,a.logfile_name
    ,a.logfile_node_name
    ,a.outfile_name
    ,q.concurrent_queue_name
    ,a.phase_code,a.status_code, a.completion_text
    , actual_start_date
    , actual_completion_date
    , fu.user_name
    ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
    ,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
    FROM APPLSYS.fnd_Concurrent_requests a2,
    APPLSYS.fnd_concurrent_programs c2
    WHERE c2.concurrent_program_id = c.concurrent_program_id
    AND a2.concurrent_program_id = c2.concurrent_program_id
    AND a2.program_application_id = c2.application_id
    AND a2.phase_code || '' = 'C') avg_mins
    ,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
    FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs c
    ,APPLSYS.fnd_concurrent_programs_tl ctl
    ,apps.fnd_user fu
    ,apps.FND_RESPONSIBILITY_TL frt
    WHERE a.controlling_manager = b.concurrent_process_id
    AND a.concurrent_program_id = c.concurrent_program_id
    AND a.program_application_id = c.application_id
    AND a.phase_code = 'C'
    AND b.queue_application_id = q.application_id
    AND b.concurrent_queue_id = q.concurrent_queue_id
    AND ctl.concurrent_program_id = c.concurrent_program_id
    AND ctl.language = 'US'
    AND a.requested_by = fu.user_id
    AND a.responsibility_id = frt.responsibility_id
    AND a.request_id = '15255945'
    ORDER BY a.actual_start_date DESC
    Previous
    Next
    » Oracle Patch Log File Troubleshooting
Leave a Comment