askMLabs

SQL>

prompt
accept request prompt 'Please enter the concurrent request id for the appropriate concurrent program:'
prompt

column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off

SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;

SQL>
set linesize 300;
set head on;
col sid_serial for a13;
col db_pid for a6;
col CMGR_Program for a65;
col user_name for a13;
col phase for a10;
col status for a10;
col start_time for a11;

break on USER_NAME

SELECT fu.user_name user_name,
TO_CHAR(NVL(cr.actual_start_date, cr.requested_start_date), 'DD.MM HH24:MI') start_time,
cr.request_id request_id,
decode(cr.parent_request_id, -1, 0, cr.parent_request_id) par_req_id,
DECODE(cr.phase_code, 'C', 'Completed', 'I', 'Inactive', 'P', 'Pending', 'R', 'Running', cr.phase_code) phase,
DECODE(cr.status_code, 'A', 'Waiting', 'B', 'Resuming', 'C', 'Normal', 'D', 'Cancelled', 'E', 'Errored', 'F', 'Scheduled',
'G', 'Warning', 'H', 'On Hold', 'I', 'Normal', 'M', 'No Manager', 'Q', 'Standby', 'R', 'Normal', 'S', 'Suspended', 'T',
'Terminating', 'U', 'Disabled', 'W', 'Paused', 'X', 'Terminated', 'Z', 'Waiting', cr.status_code) status,
cr.oracle_process_id db_pid,
vs.SID || ',' || vs.serial# sid_serial,
(SELECT SUBSTR(cp.concurrent_program_name || '-' || cpl.user_concurrent_program_name, 1, 65)
FROM APPS.fnd_concurrent_programs cp,
APPS.fnd_concurrent_programs_tl cpl
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cpl.application_id = cr.program_application_id
AND cpl.concurrent_program_id = cr.concurrent_program_id
AND cpl.LANGUAGE = USERENV('LANG')
) CMGR_Program
FROM APPS.fnd_concurrent_requests cr,
v$process vp,
v$session vs,
APPS.fnd_user fu
WHERE
cr.phase_code <> 'I'
AND (cr.phase_code < 'C' OR cr.phase_code > 'C')
AND cr.status_code NOT IN ('U', 'X', 'D', 'E', 'C', 'I')
AND cr.oracle_process_id = vp.spid (+)
AND cr.oracle_session_id = vs.audsid (+)
AND fu.user_id = cr.requested_by
ORDER BY sid_serial,
phase_code desc,
STATUS_CODE,
2 DESC,
1
;

Sample output

USER_NAME     START_TIME  REQUEST_ID PAR_REQ_ID PHASE      STATUS     DB_PID SID_SERIAL    CMGR_PROGRAM
------------- ----------- ---------- ---------- ---------- ---------- ------ ------------- -----------------------------------------------------------------
501282944     26.09 21:00   13737882   12462882 Pending    Standby           ,             FNDGSCST-Gather Schema Statistics
SYSADMIN      22.09 01:30   13892885   13694882 Pending    Standby           ,             FNDCPPUR-Purge Concurrent Request and/or Manager Data
21.09 18:39   13921062   13921061 Pending    Standby           ,             FNDOAMCOL-OAM Applications Dashboard Collection

set serveroutput on
set echo on
set timing on
set feedback on
set long 10000
set pagesize 132
set linesize 80
col PLATFORM_CODE form a5
col HOST form a20
col DOMAIN form a30
col WEBHOST form a30
col VIRTUAL_IP form a20
col status form a20
col ConcMgr form a8
col Forms form a8
col WebServer form a8
col Admin form a8
col Database form a8
col last_monitored form a40
--
select
NODE_NAME,
to_char(CREATION_DATE, 'DD-MON-RR HH24:MI') creation_date,
PLATFORM_CODE,
decode(STATUS,'Y','ACTIVE','INACTIVE') Status,
decode(SUPPORT_CP,'Y', 'ConcMgr','No') ConcMgr,
decode(SUPPORT_FORMS,'Y','Forms', 'No') Forms,
decode(SUPPORT_WEB,'Y','Web', 'No') WebServer,
decode(SUPPORT_ADMIN, 'Y','Admin', 'No') Admin,
decode(SUPPORT_DB, 'Y','Rdbms', 'No') Database,
to_char(LAST_MONITORED_TIME, 'DD-MON-RR HH24:MI:SS') last_monitored,
NODE_MODE,
SERVER_ADDRESS,
HOST,
DOMAIN,
WEBHOST,
VIRTUAL_IP,
SERVER_ID
from fnd_nodes
where node_name != 'AUTHENTICATION;

SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins      fl
,fnd_user        fu
,v$locked_object vlocked
,v$process       vp
,v$session       vs
,dba_objects     c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status
,'XX') != 'KILLED';

select request_group_name from FND_REQUEST_GROUPS where REQUEST_GROUP_ID =(SELECT request_group_id FROM FND_REQUEST_GROUP_UNITS WHERE REQUEST_UNIT_ID=(select CONCURRENT_PROGRAM_ID from
FND_CONCURRENT_PROGRAMS where CONCURRENT_PROGRAM_NAME ='<report short name>'));

clear col
clear breaks

set pages 9000
set lines 132
set verify off

col pov    format a45 word_wrapped  heading "Profile Value"
col lo     format a5                heading "Level"
col lov    format a40               heading "Value"
col pon    noprint    new_value n_pon
col upon   noprint    new_value n_upon
col sda    noprint    new_value n_sda
col eda    noprint    new_value n_eda
col cd     noprint    new_value n_cd
col cb     noprint    new_value n_cb
col d      format a78 word_wrapped noprint    new_value n_d

break on pon skip page

ttitle -
"Creation Date:   "      n_cd    "    Created By: "          n_cb   -
skip 1 -
"Date Active From:"      n_sda   "    To:"     n_eda  -
skip 1 -
"Profile Option Name: "  n_pon   -
skip 1 -
"User Profile Name:   "  n_upon  -
skip 1 -
"Profile Description: "          -
skip 1 -
n_d                              -

select        lpad(fpo.profile_option_name,55)         pon
,             lpad(fpot.user_profile_option_name,55)   upon
,             fpot.description                          d
,             lpad(fpo.start_date_active,15)           sda
,             lpad(fpo.end_date_active,15)             eda
,             lpad(fpo.creation_date,15)               cd
,             lpad(fu.user_name,20)                    cb
,             'Site'                                   lo
,             'SITE'                                   lov
,             fpov.profile_option_value                pov
from       FND_PROFILE_OPTIONS_TL      fpot
,          FND_PROFILE_OPTIONS         fpo
,          FND_PROFILE_OPTION_VALUES   fpov
,          FND_USER                    fu
where    fpot.user_profile_option_name like '&&profile_like'
and      fpot.profile_option_name = fpo.profile_option_name
and      fpo.application_id       = fpov.application_id
and      fpo.profile_option_id    = fpov.profile_option_id
and      fpo.created_by           = fu.user_id
and      fpot.language            = Userenv('Lang')
and      fpov.level_id            = 10001                  /* Site Level */
union all
select        lpad(fpo.profile_option_name,55)         pon
,             lpad(fpot.user_profile_option_name,55)   upon
,             fpot.description                          d
,             lpad(fpo.start_date_active,15)           sda
,             lpad(fpo.end_date_active,15)             eda
,             lpad(fpo.creation_date,15)               cd
,             lpad(fu.user_name,20)                    cb
,             'Apps'                                   lo
,             fa.application_name                      lov
,             fpov.profile_option_value                pov
from      FND_PROFILE_OPTIONS_TL      fpot
,         FND_PROFILE_OPTIONS         fpo
,         FND_PROFILE_OPTION_VALUES   fpov
,         FND_USER                    fu
,         FND_APPLICATION_TL          fa
where    fpot.user_profile_option_name like '&&profile_like'
and      fpot.profile_option_name = fpo.profile_option_name
and      fpo.profile_option_id    = fpov.profile_option_id
and      fpo.created_by           = fu.user_id
and      fpot.language            = Userenv('Lang')
and      fpov.level_id            = 10002                  /* Application Level */
and      fpov.level_value         = fa.application_id
union all
select        lpad(fpo.profile_option_name,55)         pon
,             lpad(fpot.user_profile_option_name,55)   upon
,             fpot.description                          d
,             lpad(fpo.start_date_active,15)           sda
,             lpad(fpo.end_date_active,15)             eda
,             lpad(fpo.creation_date,15)               cd
,             lpad(fu.user_name,20)                    cb
,             'Resp'                                   lo
,             frt.responsibility_name                   lov
,             fpov.profile_option_value                pov
from     FND_PROFILE_OPTIONS_TL      fpot
,        FND_PROFILE_OPTIONS         fpo
,        FND_PROFILE_OPTION_VALUES   fpov
,        FND_USER                    fu
,        FND_RESPONSIBILITY_TL          frt
where    fpot.user_profile_option_name like '&&profile_like'
and      fpot.profile_option_name        = fpo.profile_option_name
and      fpo.profile_option_id           = fpov.profile_option_id
and      fpo.created_by                  = fu.user_id
and      frt.language                    = Userenv('Lang')
and      fpot.language                   = Userenv('Lang')
and      fpov.level_id                   = 10003                  /* Responsibility Level */
and      fpov.level_value                = frt.responsibility_id
and      fpov.level_value_application_id = frt.application_id
union all
select        lpad(fpo.profile_option_name,55)         pon
,             lpad(fpot.user_profile_option_name,55)   upon
,             fpot.description                          d
,             lpad(fpo.start_date_active,15)           sda
,             lpad(fpo.end_date_active,15)             eda
,             lpad(fpo.creation_date,15)               cd
,             lpad(fu.user_name,20)                    cb
,             'User'                                   lo
,             fu2.user_name                            lov
,             fpov.profile_option_value                pov
from     FND_PROFILE_OPTIONS_TL      fpot
,        FND_PROFILE_OPTIONS         fpo
,        FND_PROFILE_OPTION_VALUES   fpov
,        FND_USER                    fu
,        FND_USER                    fu2
where    fpot.user_profile_option_name like '&&profile_like'
and      fpot.profile_option_name = fpo.profile_option_name
and      fpo.profile_option_id    = fpov.profile_option_id
and      fpo.created_by           = fu.user_id
and      fpov.level_id            = 10004                  /* User Level */
and      fpov.level_value         = fu2.user_id
and      fpot.language            = Userenv('Lang')
order by upon, lo, lov;