--  FILE:   fnd_long.sql
--
--  AUTHOR: Andy Rivenes, arivenes@appsdba.com, www.appsdba.com
--          Copyright (C) 1999 AppsDBA Consulting
--
--  DATE:   unknown
--
--  DESCRIPTION:
--          Query to display long running concurrent requests.
--          
--  MODIFICATIONS:
--          A. Rivenes, 08/09/1999, Changed elapsed time to minutes.
--
-- 
-- This program is free software: you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program.  If not, see <http://www.gnu.org/licenses/>.
--           
--
SET LINESIZE 132;
--
COLUMN request_id   HEADING 'Request ID'      FORMAT 99999999;
COLUMN strttime     HEADING 'Start|Time'      FORMAT A17;
COLUMN endtime      HEADING 'End|Time'        FORMAT A17;
COLUMN rtime        HEADING 'Elapsed|(Min)'   FORMAT 9990.99;
SELECT REQUEST_ID,
       TO_CHAR(ACTUAL_START_DATE,'MM/DD/YY HH:MI:SS') strttime, 
       TO_CHAR(ACTUAL_COMPLETION_DATE,'MM/DD/YY HH:MI:SS') endtime,
--       ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE time,
--       TO_NUMBER(TO_CHAR(ACTUAL_COMPLETION_DATE,'JSSSSS')) - TO_NUMBER(TO_CHAR(ACTUAL_START_DATE,'JSSSSS')) seconds,
       ROUND((ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE)*(60*24),2) rtime,
       OUTCOME_CODE,
       SUBSTR(completion_text,1,20) compl_txt
  FROM fnd_concurrent_requests
-- WHERE TO_NUMBER(TO_CHAR(ACTUAL_COMPLETION_DATE,'JSSSSS')) - TO_NUMBER(TO_CHAR(ACTUAL_START_DATE,'JSSSSS')) > 3600
-- WHERE ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE > 0.01
 WHERE ACTUAL_START_DATE > sysdate-10
 ORDER BY 2
/