REM RMINC_run_alone.sql
REM
REM Copyright 2006, All Rights Reserved, Reed-Matthews, Inc.
REM Concurrent Program Name:  Concurrent Programs With Run Alone Enabled
REM
REM Shows which programs are defined with Run Alone set, and if 
REM you've ever run any programs with 'Run Alone' set

set heading on 
set feedback on
REM First, tell us which programs can Run Alone:
set linesize 180
set pagesize 999
column Program format a23 heading 'Program'
column 'Program Name' format a40 heading 'Program Name'
column 'User Name' format a15 heading 'User'
column Description format a25 heading 'Description'

ttitle center 'Programs That CAN Run Alone' SKIP 2

select substr(a.concurrent_program_name,1,23) Program,
substr(a.user_concurrent_program_name,1,40) "Program Name",
substr(b.user_name,1,15) "User Name", 
substr(b.description,1,25) Description
from fnd_concurrent_programs_vl a,
fnd_user b
where a.run_alone_flag='Y'
and a.last_updated_by=b.user_id;

REM Second, tell us if any programs have run with Run Alone set:

ttitle center 'Programs That HAVE Run Alone In The Past' SKIP 2

column STARTED format a18 heading 'Started'
column FINISHED format a18 heading 'Finished'
column RUNTIME format 999999.99 heading 'Minutes'
column NAME format a20 heading 'Program Name'
column status format a6 heading 'Status'
column USER format a10 heading 'User'
column argument_text format a100 heading 'Parameters Passed'
column request format 99999999 heading 'Request'
set pagesize 180
select
    to_char(req.actual_start_date,'DD-MON-RR HH24:MI:SS') STARTED,
    to_char(req.actual_completion_date,'DD-MON-RR HH24:MI:SS') FINISHED,
    req.request_id Request,
    prg.user_concurrent_program_name Name,
    prg.concurrent_program_id ProgID,
    usr.user_name USERNAME,
    argument_text,
    status_code status,
    round((req.actual_completion_date-req.actual_start_date)*(60*24),6) RUNTIME
from fnd_user usr,
     fnd_concurrent_programs_vl prg,
     fnd_concurrent_requests req
where
     req.concurrent_program_id = prg.concurrent_program_id
     and req.program_application_id = prg.application_id
     and req.requested_by = usr.user_id
     and prg.run_alone_flag='Y';