REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	a_cm_long_reqs.sql
REM
REM <SQLDIR_GRP>APPS_CONC_MAN MOST</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy (vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Prints concurrent requests that ran for over "X" minutes.
REM	Calls analyzereq.sql that can analyze conc request of your choice.
REM	</SQLDIR_TXT>
REM
REM Usage:
REM	a_cm_long_reqs.sql
REM 
REM Example:
REM	a_cm_long_reqs.sql
REM
REM
REM History:
REM	12-04-2001	VMOGILEV	Created
REM
REM

set lines 300
set trims on
set pages 60
set head on

ttit off
btit off

alter session set nls_Date_format='DD-MON-RRRR HH24:MI';

accept minutes prompt "Enter runtime minutes limit to report [DEFAULT=60]: "

col runtime format 9999999    heading "Runtime|[min]"
col asd format a23            Heading "Start Time"
col acd format a23            Heading "End Time"
col cpn format a15             heading "Program"
col ucpn format a25 trunc     heading "Program Name"
--col logfile_name format a85   heading "Log File"  newline
--col argument_text format a300 heading "Arguments" newline

SELECT f.request_id
,      TRUNC(((f.actual_completion_date-f.actual_start_date)/(1/24))*60) runtime
,      f.actual_start_date       asd
,      f.actual_completion_date  acd
,      DECODE(p.concurrent_program_name,
          'ALECDC',p.concurrent_program_name||'['||f.description||']'
                  ,p.concurrent_program_name)            cpn
,      pt.user_concurrent_program_name      ucpn
,      f.phase_code
,      f.status_code
--,      f.logfile_name
--,      f.argument_text
FROM apps.fnd_concurrent_programs p
,    apps.fnd_concurrent_programs_tl pt
,    apps.fnd_concurrent_requests f
WHERE  TRUNC(((f.actual_completion_date-f.actual_start_date)/(1/24))*60) > NVL('&minutes',60)
and    f.concurrent_program_id = p.concurrent_program_id
and    f.program_application_id = p.application_id
and    f.concurrent_program_id = pt.concurrent_program_id
and    f.program_application_id = pt.application_id
AND    pt.language = USERENV('Lang')
ORDER by f.actual_completion_date-f.actual_start_date desc

spool a_cm_long_reqs.log
/

--prompt 1 = REQ_ID to analyze

--@analyzereq.sql

spool off