requests.sql

select USER_CONCURRENT_PROGRAM_NAME as "程序名称",
       count(*) as "运行次数",
       max(round(nvl((nvl(frai.ACTUAL_COMPLETION_DATE, sysdate) - frai.ACTUAL_START_DATE),0) * 24 * 3600 / 60,2)) as "最大运行时间(分钟)",
       min(round(nvl((nvl(frai.ACTUAL_COMPLETION_DATE, sysdate) - frai.ACTUAL_START_DATE),2) * 24 * 3600,2)) as "最小运行时间(分钟)",
       sum(round(nvl((nvl(frai.ACTUAL_COMPLETION_DATE, sysdate) - frai.ACTUAL_START_DATE),0) * 24 * 3600 / 60,2)) as "总运行时间(分钟)",
       trunc(avg(round(nvl((nvl(frai.ACTUAL_COMPLETION_DATE, sysdate) - frai.ACTUAL_START_DATE),2) * 24 * 3600 / 60,2))) as "平均运行时间(分钟)"
  from (SELECT R.REQUEST_ID REQUEST_ID,
               R.PHASE_CODE PHASE_CODE,
               R.STATUS_CODE STATUS_CODE,
               R.REQUEST_DATE REQUEST_DATE,
               b.RESPONSIBILITY_NAME RESPONSIBILITY_NAME,
               R.REQUESTED_START_DATE REQUESTED_START_DATE,
               R.ACTUAL_START_DATE ACTUAL_START_DATE,
               R.ACTUAL_COMPLETION_DATE ACTUAL_COMPLETION_DATE,
               round((nvl(R.ACTUAL_COMPLETION_DATE, sysdate) -
                     R.ACTUAL_START_DATE) * 24 * 60 * 60) as run_time,
               R.COMPLETION_TEXT COMPLETION_TEXT,
               R.ARGUMENT_TEXT ARGUMENT_TEXT,
               R.IMPLICIT_CODE IMPLICIT_CODE,
               PB.CONCURRENT_PROGRAM_NAME PROGRAM_SHORT_NAME,
               PT.USER_CONCURRENT_PROGRAM_NAME USER_CONCURRENT_PROGRAM_NAME,
               U.USER_NAME REQUESTOR,
               o.file_node_name as publish_file_node_name,
               o.file_name publish_file_name,
               round(o.file_size / 1024 / 1024, 2) publish_file_size,
               o.file_type publish_file_type,
               r.outfile_name OUTFILE_NAME,
               round(r.ofile_size / 1024 / 1024, 2) as ofile_size,
               r.logfile_node_name LOGFILE_NODE_NAME,
               r.logfile_name LOGFILE_NAME,
               round(r.lfile_size / 1024 / 1024, 2) as lfile_size,
               qt.concurrent_queue_name
        -- qt.concurrent_queue_name
          FROM APPS.FND_CONCURRENT_PROGRAMS_TL PT,
               APPS.FND_CONCURRENT_PROGRAMS    PB,
               APPS.fnd_user                   U,
               APPS.FND_PRINTER_STYLES_TL      S,
               APPS.FND_CONCURRENT_REQUESTS    R,
               APPS.fnd_responsibility_tl      b,
               APPS.fnd_conc_req_outputs       o,
               apps.FND_CONCURRENT_QUEUES_TL   qt,
               apps.fnd_concurrent_processes   cp
         WHERE PB.APPLICATION_ID = R.PROGRAM_APPLICATION_ID
           AND PB.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID
           AND PB.APPLICATION_ID = PT.APPLICATION_ID
           AND PB.CONCURRENT_PROGRAM_ID = PT.CONCURRENT_PROGRAM_ID
           AND U.USER_ID = R.REQUESTED_BY
           AND S.PRINTER_STYLE_NAME(+) = R.PRINT_STYLE
           and to_char(R.Request_Date, 'yyyy-mm-dd') >= '2021-07-30'
           and to_char(R.Request_Date, 'yyyy-mm-dd') <= '2021-09-01'
              --and r.phase_code = 'C'
           and b.RESPONSIBILITY_ID = r.responsibility_id
           and b.language = 'ZHS'
           and PT.Language = 'ZHS'
           and s.language = 'ZHS'
           and r.request_id = o.concurrent_request_id(+)
           and r.controlling_manager = cp.concurrent_process_id(+)
           and cp.queue_application_id = qt.application_id
           and cp.concurrent_queue_id = qt.concurrent_queue_id
           and qt.language = 'ZHS') frai
 where frai.status_code = 'C'
 group by USER_CONCURRENT_PROGRAM_NAME