Apps Day to Day Queries

Concurrent Manager/Request Queries

HOW TO find a concurrent program's trace file

Goal
To find the SQL trace file for a concurrent program

Solution
Run the following script:

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
req.request_id
,req.logfile_node_name node
,req.oracle_Process_id
,req.enable_trace
,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
,prog.user_concurrent_program_name
,execname.execution_file_name
,execname.subroutine_name
,phase_code
,status_code
,ses.SID
,ses.serial#
,ses.module
,ses.machine
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 1=1
AND 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 script to lists all the profile settings (all levels) (Version 3.0)
11i update for "SQL script to lists all the profile settings (all levels)".

--
-- DESC: List the values of a Profile Option, or all Profile Options for a
-- user, or application.
--

SET heading OFF
COLUMN "Profile" FORMAT A33 word_wrapped
COLUMN "Value" FORMAT A30 word_wrapped
COLUMN "Levl" FORMAT A4
COLUMN "Location" FORMAT A10 word_wrapped
BREAK ON "Profile" ON "Value" ON "Levl"

ACCEPT v_profile PROMPT "Enter a PROFILE substring value to search (default
'ALL PROFILES') : "
ACCEPT v_username PROMPT "Enter a USER / LOCATION or substring to search
(default 'ALL USERS') : "

SELECT 'Querying ' || name || TO_CHAR( sysdate, '" on" dd-Mon-yyyy "for"')
|| 'Profile like ''%' || UPPER( '&&v_profile')
|| '%'' and User / Location like ''%' || UPPER( '&&v_username') || '%'''
--Showing the query parameters
FROM v$database, dual
/

SET heading ON
set pagesize 60
set newpage 0
--Now set the column headers on that we have specified above
SELECT pot.user_profile_option_name "Profile"
, DECODE( a.profile_option_value
, '1', '1 (may be "Yes")'
, '2', '2 (may be "No")'
, a.profile_option_value) "Value"
, DECODE( a.level_id
, 10001, 'Site'
, 10002, 'Appl'
, 10003, 'Resp'
, 10004, 'User'
, '????') "Levl"
, DECODE( a.level_id
, 10002, e.application_name
, 10003, c.responsibility_name
, 10004, d.user_name
, '-') "Location"
FROM applsys.fnd_application_tl e
, applsys.fnd_user d , applsys.fnd_responsibility_tl c
, applsys.fnd_profile_option_values a , applsys.fnd_profile_options b
, applsys.fnd_profile_options_tl pot
WHERE UPPER( pot.user_profile_option_name) LIKE UPPER( '%&&v_profile%')
AND pot.profile_option_name = b.profile_option_name
AND b.application_id = a.application_id (+)
AND b.profile_option_id = a.profile_option_id (+)
AND a.level_value = c.responsibility_id (+)
AND a.level_value = d.user_id (+) AND a.level_value = e.application_id
(+)
AND( UPPER( e.application_name) LIKE UPPER( '%&&v_username%')
OR UPPER( c.responsibility_name) LIKE UPPER( '%&&v_username%')
OR UPPER( d.user_name) LIKE UPPER( '%&&v_username%'))
ORDER BY "Profile", "Levl", "Location", "Value"
/

CLEAR COLUMN

Version 2.0

*********************Start of Profiler.SQL
SET heading OFF

COLUMN "Profile" FORMAT A35
COLUMN "Value" FORMAT A30
COLUMN "Levl" FORMAT A4
COLUMN "Location" FORMAT A10

BREAK ON "Profile" ON "Value" ON "Levl"

ACCEPT v_profile PROMPT "Enter a PROFILE substring value to search (default 'ALL PROFILES') : "
ACCEPT v_username PROMPT "Enter a USER / LOCATION or substring to search (default 'ALL USERS') : "

SELECT 'Querying ' || name || TO_CHAR( sysdate, '" on" dd-Mon-yyyy "for"')
|| 'Profile like ''%' || UPPER( '&&v_profile')
|| '%'' and User / Location like ''%' || UPPER( '&&v_username')
|| '%'''
--Showing the query parameters
FROM v$database,
dual
/

SET heading ON
--Now set the column headers on that we have specified above
SELECT b.user_profile_option_name "Profile"
, DECODE( a.profile_option_value
, '1', '1 (may be "Yes")'
, '2', '2 (may be "No")'
, a.profile_option_value) "Value"
, DECODE( a.level_id
, 10001, 'Site'
, 10002, 'Appl'
, 10003, 'Resp'
, 10004, 'User'
, '????') "Levl"
, DECODE( a.level_id
, 10002, e.application_name
, 10003, c.responsibility_name
, 10004, d.user_name
, '-') "Location"
FROM applsys.fnd_application e
, applsys.fnd_user d
, applsys.fnd_responsibility c
, applsys.fnd_profile_option_values a
, applsys.fnd_profile_options b
WHERE UPPER( b.user_profile_option_name) LIKE UPPER( '%&&v_profile%')
AND b.application_id = a.application_id (+)
AND b.profile_option_id = a.profile_option_id (+)
AND a.level_value = c.responsibility_id (+)
AND a.level_value = d.user_id (+)
AND a.level_value = e.application_id (+)
AND( UPPER( e.application_name) LIKE UPPER( '%&&v_username%')
OR UPPER( c.responsibility_name) LIKE UPPER( '%&&v_username%')
OR UPPER( d.user_name) LIKE UPPER( '%&&v_username%'))
ORDER BY "Profile", "Levl", "Location", "Value"
/

CLEAR COLUMN


Download Script

Version 1
It asks for a profile name (a wild card will be added to end,
and input will be converted to upper compare).

If profile is valid, BUT NOT assigned then "level" will be
"????".


SELECT b.user_profile_option_name profil,
a.profile_option_value val,
decode(a.level_id,10001,'Site',
10002,'Appl',
10003,'Resp',
10004,'User',
'????') Lev,
decode(a.level_id,10002,e.application_name,
10003,c.responsibility_name,
10004,d.user_name,
' ') loc
FROM applsys.fnd_application e,
applsys.fnd_user d,
applsys.fnd_responsibility c,
applsys.fnd_profile_option_values a,
applsys.fnd_profile_options b
WHERE upper(b.user_profile_option_name) like upper('&profile')||'%'
AND b.application_id = a.application_id (+)
AND b.profile_option_id = a.profile_option_id (+)
AND a.level_value = c.responsibility_id (+)
AND a.level_value = d.user_id (+)
AND a.level_value = e.application_id (+)
ORDER BY 1, a.level_id, 4