Concurrent programs maximum, minimum and average execution times report

The following script will give you the maximum, minimum and average execution times of each concurrent program had been executed in your eBS. It will measure only the successfully completed concurrent requests.

SELECT P.CONCURRENT_PROGRAM_NAME,
         PT.USER_CONCURRENT_PROGRAM_NAME,
         COUNT (*),
            TRUNC (MAX (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE))
         || ' Days'
         || ' + '
         || TO_CHAR (
               TRUNC (SYSDATE)
               + NUMTODSINTERVAL (
                    MAX ( (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE))
                    * 86400,
                    'second'),
               'HH24:MI:SS')
            "MAXIMUM",
            TRUNC (MIN (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE))
         || ' Days'
         || ' + '
         || TO_CHAR (
               TRUNC (SYSDATE)
               + NUMTODSINTERVAL (
                    MIN ( (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE))
                    * 86400,
                    'second'),
               'HH24:MI:SS')
            "MINIMUM",
            TRUNC (AVG (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE))
         || ' Days'
         || ' + '
         || TO_CHAR (
               TRUNC (SYSDATE)
               + NUMTODSINTERVAL (
                    AVG ( (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE))
                    * 86400,
                    'second'),
               'HH24:MI:SS')
            "AVERAGE"
    FROM APPLSYS.FND_CONCURRENT_REQUESTS F,
         APPLSYS.FND_CONCURRENT_PROGRAMS P,
         APPLSYS.FND_USER A,
         APPLSYS.FND_CONCURRENT_PROGRAMS_TL PT
   WHERE     PHASE_CODE = 'C'
         AND STATUS_CODE = 'C'
         AND F.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID
         AND P.CONCURRENT_PROGRAM_ID = PT.CONCURRENT_PROGRAM_ID
         AND A.USER_ID(+) = F.REQUESTED_BY
GROUP BY P.CONCURRENT_PROGRAM_NAME, PT.USER_CONCURRENT_PROGRAM_NAME;

CONCURRENT_PROGRAM_NAME	USER_CONCURRENT_PROGRAM_NAME	COUNT (*)	MAXIMUM	MINIMUM	AVERAGE
XXI_UPD_CONF_ORDER_STATUS	XXI : Update Order Status for Confirmed Orders	33076	0 Days + 00:10:51	0 Days + 00:00:04	0 Days + 00:00:11
FNDWFBG	Workflow Background Process	48905	8 Days + 13:34:25	0 Days + 00:00:00	0 Days + 00:02:42
FAPROJ	Depreciation Projection	37	0 Days + 03:12:10	0 Days + 00:00:35	0 Days + 00:45:31
XXIEX_RUN_STRATEGIES	Request Set OTE Run Strategies	108	0 Days + 19:42:55	0 Days + 07:17:49	0 Days + 12:51:35
...	...	...	...	...	...