--   FILE:    fndreq.sql
--
--   DESCRIPTION:
--            This script will list running concurrent requests.
--
--   AUTHOR:  Andy Rivenes, arivenes@appsdba.com, www.appsdba.com
--            Copyright (C) 1998-2001 AppsDBA Consulting
--
--   DATE:    10/24/97
--
--   REQUIREMENTS:
--
--   MODIFICATIONS: Steven J. Avampato 01/09/2001
--                  Changed join between v$process and v$session
--                  to be on the hash address instead of the pid.
--                  Stale information from the v$session was being
--                  reported.
--
-- 
-- 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 PAGESIZE 9999;
SET VERIFY off;
SET FEEDBACK off;
SET LINESIZE 132;
--
COLUMN concreq    HEADING  'Conc Req ID'                FORMAT A12;
COLUMN clproc     HEADING  'Client|Process'             FORMAT A11;
COLUMN opid       HEADING  'ORACLE PID'                 FORMAT A10;
COLUMN reqph      HEADING  'Req Phase'                  FORMAT A10;
COLUMN reqst      HEADING  'Req Status'                 FORMAT A10;
COLUMN dbuser     HEADING  'DB User'                    FORMAT A10;
COLUMN svrproc    HEADING  'Srvr|Process'               FORMAT A10;
COLUMN sid        HEADING  'SID'                        FORMAT 99999;
COLUMN serial#    HEADING  'Serial#'                    FORMAT 99999;
SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
       SUBSTR(proc.os_process_id,1,15) clproc,
       SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
       SUBSTR(look.meaning,1,10) reqph,
       SUBSTR(look1.meaning,1,10) reqst,
       SUBSTR(vsess.username,1,10) dbuser,
       SUBSTR(vproc.spid,1,10) svrproc,
       vsess.sid sid,
       vsess.serial# serial#
FROM   fnd_concurrent_requests req,
       fnd_concurrent_processes proc,
       fnd_lookups look,
       fnd_lookups look1,
       v$process vproc,
       v$session vsess
WHERE  req.controlling_manager = proc.concurrent_process_id(+)
AND    req.status_code = look.lookup_code
AND    look.lookup_type = 'CP_STATUS_CODE'
AND    req.phase_code = look1.lookup_code
AND    look1.lookup_type = 'CP_PHASE_CODE'
AND    look1.meaning = 'Running'
AND    proc.oracle_process_id = vproc.pid(+)
AND    vproc.addr = vsess.paddr(+);