REM RMINC_USERSPROGRAMS.sql
REM
REM Copyright Reed-Matthews, Inc. 
REM 
REM Users Programs (RMINC)
REM
REM you tell it a username, and it tells you the program names
REM that this user has run.  You can use this information to
REM better understand a user's processing requirements, and you
REM can use this information to build special concurrent request
REM queues for a set of users.
REM
REM Doesn't report on programs that restart the concurrent
REM manager (RESTART), reprint reports (FNDREPRINT) or 
REM run event alerts (ALECTC)
REM
ttitle "Programs Users Have Run"
column requestor heading 'USER' format a20
column program heading 'PROGRAM NAME' format a100
column substr(program_short_name,1,30) heading 'PROGRAM SHORT NAME' format a30
set pagesize 999
SELECT distinct (substr(PROGRAM,1,100)) PROGRAM,
       substr(PROGRAM_SHORT_NAME,1,30)
FROM   FND_CONC_REQ_SUMMARY_V 
WHERE  PHASE_CODE = 'C' 
and    ((REQUESTOR = '&1')
 or    (REQUESTOR = '&2')
 or    (REQUESTOR = '&3')
 or    (REQUESTOR = '&4')
 or    (REQUESTOR = '&5') 
 or    (REQUESTOR = '&6')
 or    (REQUESTOR = '&7')
 or    (REQUESTOR = '&8')
 or    (REQUESTOR = '&9') 
 or    (REQUESTOR = '&10')
 or    (REQUESTOR = '&11')
 or    (REQUESTOR = '&12')
 or    (REQUESTOR = '&13')) 
and    substr(PROGRAM_SHORT_NAME,1,30) NOT IN  ('RESTART','FNDREPRINT','ALECTC')
order by PROGRAM DESC;