Hi,
I am running some SQL to try to find how long, in hours:mins:seconds a concurrent request in Oracle EBS takes to run.
I have a basic start here:
SELECT fcr.actual_start_date start_
, fcr.actual_completion_date end_
, fcr.actual_completion_date - fcr.actual_start_date diff_1
-- , TO_CHAR(fcr.actual_completion_date, 'HH24:MI:SS') - TO_CHAR(fcr.actual_start_date, 'HH24:MI:SS') diff_
FROM applsys.fnd_concurrent_requests fcr
, applsys.fnd_concurrent_programs fcp
, applsys.fnd_concurrent_programs_tl fcpt
WHERE fcp.application_id = fcpt.application_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcpt.user_concurrent_program_name LIKE '%Invoice%Validation%'
AND fcr.actual_completion_date IS NOT NULL
ORDER BY fcr.actual_completion_date DESC;
START_ END_ DIFF_1
##############################################################################
20/09/2012 02:57:50 20/09/2012 02:58:06 0.000185185185185185
20/09/2012 02:55:44 20/09/2012 02:55:50 6.94444444444444E-5
20/09/2012 02:53:39 20/09/2012 02:53:50 0.000127314814814815
20/09/2012 02:43:28 20/09/2012 02:52:05 0.0059837962962963
20/09/2012 02:32:44 20/09/2012 02:32:57 0.000150462962962963
20/09/2012 02:31:08 20/09/2012 02:31:15 8.10185185185185E-5
20/09/2012 02:29:08 20/09/2012 02:29:14 6.94444444444444E-5
20/09/2012 02:28:05 20/09/2012 02:28:09 4.62962962962963E-5
20/09/2012 02:24:01 20/09/2012 02:26:23 0.00164351851851852
20/09/2012 02:21:58 20/09/2012 02:22:02 4.62962962962963E-5
I'd really like to get the difference expressed in hours, minutes and seconds.
I tried:
, TO_CHAR(fcr.actual_completion_date, 'HH24:MI:SS') - TO_CHAR(fcr.actual_start_date, 'HH24:MI:SS') diff_
But that doesn't work - I get an
ORA-01722: invalid number error.
I also tried:
, TO_CHAR(fcr.actual_completion_date - fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') diff_
But got an
ORA-01481: invalid number format model error
I apologise for being lazy, stupid, useless etc. as I'm sure there are lots of docs I should have read.
I just wondered if someone might be able to advise how to get the difference expressed in hh:mm:ss
Any advice much appreciated.
Thanks