FND_FLEX_VALUE_HIERARCHIES
• FND_FLEX_VALUE_SETS
• FND_ID_FLEX_SEGMENTS
• FND_FLEX_VALUE_NORM_HIERARCHY
• FND_FLEX_HIERARCHIES
• FND_FLEX_VALUE
• FND_FLEX_VALIDATION_EVENTS
• FND_FLEX_VALUE_RULE_LINES
• FND_FLEX_VALUE_RULE
• FND_FLEX_VALUE_RULE_USAGE
• FND_RESPONSIBLITY
• FND_TABLES
• FND_FLEX_VALIDATION_TABLES
====================Query to find out the responsibilities attached to a user in Oracle Application
Select b.user_name, c.responsibility_name, a.START_DATE, a.END_DATE
from fnd_user_resp_groups_direct a, fnd_user b, fnd_responsibility_tl c
where a.user_id = b.user_id
and a.responsibility_id = c.responsibility_id
and b.user_name='&username';
==================
===================application wise respnsibiltiies ==============
SELECT (SELECT application_name
FROM fnd_application_tl fa
WHERE fa.application_id = frt.application_id) application,
frt.responsibility_id, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt
where frt.responsibility_name like 'DKCT%Tal%'
order by 3
---------------Application Names-------------------
SELECT application_name, application_short_name, fa.application_id
FROM fnd_application fa, fnd_application_tl fat
WHERE application_short_name LIKE '%Q%'
AND fa.application_id = fat.application_id
ORDER BY fa.application_id
Query to list concurrent program details with its parameter, values set and default value/type
SQL Query to list all Oracle Reports assigned to a Responsibility
SELECT fcpl.user_concurrent_program_name "REPORT NAME",
fnrtl.responsibility_name, frg.request_group_name,
fcp.concurrent_program_name "CONCURRENT PROGRAM SHORT NAME"
FROM apps.fnd_request_groups frg,
apps.fnd_request_group_units frgu,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_programs_tl fcpl,
apps.fnd_executables fe,
apps.fnd_responsibility fnr,
apps.fnd_responsibility_tl fnrtl
WHERE frg.application_id = frgu.application_id
AND frg.request_group_id = frgu.request_group_id
AND frg.request_group_id = fnr.request_group_id
AND frg.application_id = fnr.application_id
AND fnr.responsibility_id = fnrtl.responsibility_id
AND frgu.request_unit_id = fcp.concurrent_program_id
AND frgu.unit_application_id = fcp.application_id
AND fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fe.execution_method_code LIKE 'P' --'Oracle Reports'
AND fcp.executable_id = fe.executable_id
AND fe.application_id = fcp.executable_application_id
-- AND fnrtl.responsibility_name LIKE '&Responsibility_Name'
-- Example Resp. Name : Inventory, Vision Operations (USA)
AND fnrtl.LANGUAGE = 'US'
AND fcpl.LANGUAGE = 'US';
=================================================================
Application wise reports name===============
SELECT cpv.user_concurrent_program_name "Concurrent Program Name",
cpv.concurrent_program_name "Program Short Name",
efv.application_name "Application",
-- cpv.enabled_flag "Enabled Flag",
-- cpv.description "Description",
-- cpv.output_file_type "Output Format",
fu.user_name "Created By (userid)",
-- ppf.full_name "Created By (username)",
DECODE(efv.execution_method_code,
'I', 'PL/SQL Stored Procedure',
'H', 'Host',
'S', 'Immediate',
'J', 'Java Stored Procedure',
'K', 'Java Concurrent Program',
'M', 'Multi Language Function',
'P', 'Oracle Reports',
'B', 'Request Set Stage Function',
'A', 'Spawned',
'L', 'SQL*Loader',
'Q', 'SQL*Plus',
'E', 'Pearl Concurrent Programm',
'Unknown') "Execution Method",
efv.executable_name "Executable Name",
efv.execution_file_name "Execution Filename"
FROM fnd_executables_form_v efv,
fnd_concurrent_programs_vl cpv,
fnd_user fu
-- per_all_people_f ppf
WHERE efv.executable_id = cpv.executable_id
AND efv.application_id = cpv.application_id
AND cpv.created_by = fu.user_id
and upper(cpv.user_concurrent_program_name) like 'D%'----------------------------------custom reports
-- AND fu.employee_id = ppf.person_id
and efv.execution_method_code='P'
-- AND cpv.user_concurrent_program_name = 'Active Users' -- // change it
ORDER BY cpv.user_concurrent_program_name;
==========================================================================================================
SELECT Distinct fcpl.user_concurrent_program_name "REPORT NAME",
fna.APPLICATION_NAME
-- fnrtl.responsibility_name, frg.request_group_name,
--fcp.concurrent_program_name "CONCURRENT PROGRAM SHORT NAME"
FROM apps.fnd_request_groups frg,
apps.fnd_request_group_units frgu,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_programs_tl fcpl,
apps.fnd_executables fe,
apps.fnd_responsibility fnr,
apps.fnd_responsibility_tl fnrtl,
apps.fnd_application_tl fna
WHERE frg.application_id = frgu.application_id
AND frg.request_group_id = frgu.request_group_id
AND frg.request_group_id = fnr.request_group_id
AND frg.application_id = fnr.application_id
and frg.application_id = fna.application_id
AND fnr.responsibility_id = fnrtl.responsibility_id
AND frgu.request_unit_id = fcp.concurrent_program_id
AND frgu.unit_application_id = fcp.application_id
AND fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fe.execution_method_code LIKE 'P' --'Oracle Reports'
AND fcp.executable_id = fe.executable_id
AND fe.application_id = fcp.executable_application_id
and fcpl.user_concurrent_program_name like 'D%'
-- AND fnrtl.responsibility_name LIKE '&Responsibility_Name'
-- Example Resp. Name : Inventory, Vision Operations (USA)
AND fnrtl.LANGUAGE = 'US'
AND fcpl.LANGUAGE = 'US';
============================================
To find from which responsibility a concurrent program can be run
SELECT distinct
a.user_concurrent_program_name,
a.description,
request_group_name,
e.responsibility_name
FROM
fnd_concurrent_programs_tl a,
fnd_request_groups b,
fnd_request_group_units c,
fnd_responsibility d,
fnd_responsibility_tl e
WHERE
a.concurrent_program_id = c.request_unit_id
and b.request_group_id = c.request_group_id
and b.request_group_id = d.request_group_id
and d.responsibility_id = e.responsibility_id
and a.application_id = b.application_id
and b.application_id = c.application_id
and d.application_id = e.application_id
and a.concurrent_program_id = :p_conc_program_id
Provide Concurrent Program name to the following query.
It lists all the Request Sets which are created with the Concurrent Program given.
SELECT DISTINCT user_request_set_name
FROM FND_REQUEST_SETS_TL
WHERE request_set_id IN
(SELECT request_set_id
FROM FND_REQUEST_SET_PROGRAMS
WHERE concurrent_program_id =
(SELECT CONCURRENT_PROGRAM_ID
FROM fnd_concurrent_programs_tl
WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Enter_Prog_name')));
Provide the Request Set name to the following query.
It lists all concurrent programs of this Request Set.
SELECT USER_CONCURRENT_PROGRAM_NAME
FROM fnd_concurrent_programs_tl
WHERE CONCURRENT_PROGRAM_ID IN
(SELECT concurrent_program_id
FROM FND_REQUEST_SET_PROGRAMS
WHERE request_set_id =
(SELECT request_set_id
FROM FND_REQUEST_SETS_TL
WHERE upper(user_request_set_name) = upper('&Request_Set_Name')));
Query to list concurrent program details with its parameter, values set and default value/type:
SELECT fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, fav.APPLICATION_SHORT_NAME
, fav.APPLICATION_NAME
, fav.application_id
, fdfcuv.end_user_column_name
, fdfcuv.form_left_prompt prompt
, fdfcuv.enabled_flag
, fdfcuv.required_flag
, fdfcuv.display_flag
, fdfcuv.flex_value_set_id
, ffvs.flex_value_set_name
, flv.meaning default_type
, fdfcuv.DEFAULT_VALUE
FROM fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl fdfcuv
, fnd_flex_value_sets ffvs
, fnd_lookup_values flv
, fnd_application_vl fav
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = :conc_prg_name
AND fcpl.LANGUAGE = 'US'
AND fav.application_id=fcp.application_id
AND fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = fdfcuv.default_type
AND flv.LANGUAGE(+) = USERENV ('LANG');
Query to find out concurrent program details and its parameters
SELECT fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, fcp.concurrent_program_id
, fav.APPLICATION_SHORT_NAME
, fav.APPLICATION_NAME
, fav.application_id
, fdfcuv.end_user_column_name
, fdfcuv.form_left_prompt prompt
, fdfcuv.enabled_flag
, fdfcuv.required_flag
, fdfcuv.display_flag
FROM fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl fdfcuv
, fnd_application_vl fav
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = :conc_prg_name
AND fav.application_id=fcp.application_id
AND fcpl.LANGUAGE = 'US'
AND fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name;
Query 1:For checking the locks in concurrent jobs
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,inst_id,id1, id2, lmode, request, type FROM gV$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request>0) ORDER BY id1,request;
-- Query 2:For checking the concurrent programs running currently with Details of Processed time-- and Start Date
SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,(a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 AS lag_time,d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.FND_CONCURRENT_PROGRAMS_TL c,apps.fnd_user d
WHERE a.concurrent_program_id=b.concurrent_program_id AND b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by=d.user_id AND status_code='R' order by Process_time desc;
-- Query 3:For checking last run of a Concurrent Program along with Processed time
-- Useful to find the Details of Concurrent programs which run daily and comparison purpose
SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time,
a.request_id,a.parent_request_id,To_Char(a.request_date,'DD-MON-YY HH24:MI:SS'),To_Char(a.actual_start_date,'DD-MON-YY HH24:MI:SS'),
To_Char(a.actual_completion_date,'DD-MON-YY HH24:MI:SS'), (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b ,
apps.FND_CONCURRENT_PROGRAMS_TL c,
apps.fnd_user d
WHERE a.concurrent_program_id= b.concurrent_program_id AND
b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by =d.user_id AND
-- trunc(a.actual_completion_date) = '24-AUG-2005'
c.USER_CONCURRENT_PROGRAM_NAME='Incentive Compensation Analytics - ODI' -- and argument_text like '%, , , , ,%';
-- and status_code!='C'
-- Query 4:For Checking the last run of concurrent Program.
- Use below query to check all the concurrent request running which may refer given package
-- This is very useful check before compiling any package on given instance.
-- The query can be modified as per requirement.
-- Remove FND_CONCURRENT_REQUESTS table and joins to check all program dependent on given package.
SELECT
FCR.REQUEST_ID
,FCPV.USER_CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_ID
,FCR.STATUS_CODE
,FCR.PHASE_CODE
FROM FND_CONCURRENT_PROGRAMS_VL FCPV
,FND_EXECUTABLES FE
,SYS.DBA_DEPENDENCIES DD
,FND_CONCURRENT_REQUESTS FCR
WHERE FCPV.EXECUTABLE_ID = FE.EXECUTABLE_ID
AND FE.EXECUTION_METHOD_CODE = 'I'
AND SUBSTR(FE.EXECUTION_FILE_NAME,1,INSTR(FE.EXECUTION_FILE_NAME, '.', 1, 1) - 1) = UPPER(DD.NAME)
AND DD.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'TRIGGER', 'PACKAGE') -- add as required
--AND referenced_owner = 'XXCUS'
AND DD.REFERENCED_NAME = UPPER('&Package_name')
AND FCR.CONCURRENT_PROGRAM_ID = FCPV.CONCURRENT_PROGRAM_ID
AND fcr.phase_code NOT IN ( 'C','P');
-- Query 5:The following query will display the time taken to execute the concurrent Programs
--for a particular user with the latest concurrent programs sorted in least time taken
-- to complete the request.
SELECT
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.actual_completion_date end_on,
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference,
p.concurrent_program_name concurrent_program_name,
decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
f.status_code
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
order by
f.actual_start_date desc;
--Query 6: By using the below Query we can get sid,serial#,spid of the concurrent Request..
SELECT a.request_id, d.sid, d.serial# , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';
--Query 7: By using below Concurrent Manager and Program rules...
--Gives Detail of the Concurrent_queue_name and User_concurrent_program_name
SELECT b.concurrent_queue_name, c.user_concurrent_program_name
FROM FND_CONCURRENT_QUEUE_CONTENT a, fnd_concurrent_queues b, fnd_concurrent_programs_vl c
WHERE a.queue_application_id = 283
and a.concurrent_queue_id = b.concurrent_queue_id
and a.type_id = c.concurrent_program_id
order by decode(INCLUDE_FLAG, 'I', 1, 2), type_code;
--Query 8: Gives Details of Running Concurrent jobs
SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,
(a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b ,
apps.FND_CONCURRENT_PROGRAMS_TL c,
apps.fnd_user d
WHERE a.concurrent_program_id=b.concurrent_program_id AND
b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by=d.user_id AND
status_code='R' order by Process_time desc;
-- Query 9: Gives detail of Concurrent job completed and pending
SELECT
FCR.REQUEST_ID
,FCPV.USER_CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_ID
,FCR.STATUS_CODE
,FCR.PHASE_CODE
FROM FND_CONCURRENT_PROGRAMS_VL FCPV
,FND_EXECUTABLES FE
,SYS.DBA_DEPENDENCIES DD
,FND_CONCURRENT_REQUESTS FCR
WHERE FCPV.EXECUTABLE_ID = FE.EXECUTABLE_ID
AND FE.EXECUTION_METHOD_CODE = 'I'
AND SUBSTR(FE.EXECUTION_FILE_NAME,1,INSTR(FE.EXECUTION_FILE_NAME, '.', 1, 1) - 1) = UPPER(DD.NAME)
AND DD.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'TRIGGER', 'PACKAGE') -- add as required
--AND referenced_owner = 'XXCUS'
AND DD.REFERENCED_NAME = UPPER('&Package_name')
AND FCR.CONCURRENT_PROGRAM_ID = FCPV.CONCURRENT_PROGRAM_ID
AND fcr.phase_code NOT IN ( 'C','P');
-- Query 10:Gives Detail of Running and Completed Concurrent jobs with Start date and end date
-- Latest one at the top
select
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.actual_completion_date end_on,
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference,
p.concurrent_program_name concurrent_program_name,
decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
f.status_code
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
order by
f.actual_start_date desc;
--- Query 11 wait events details related with Concurrent programs
SELECT s.saddr, s.SID, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal,
UPPER (s.program) program, s.TYPE, s.sql_address, s.sql_hash_value,
s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id,
s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled,
s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2,
s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#,
s.wait_class, s.wait_time, s.seconds_in_wait, s.state,
s.wait_time_micro, s.time_remaining_micro,
s.time_since_last_wait_micro, s.service_name, s.sql_trace,
s.sql_trace_waits, s.sql_trace_binds, s.sql_trace_plan_stats,
s.session_edition_id, s.creator_addr, s.creator_serial#
FROM v$session s
WHERE ( (s.username IS NOT NULL)
AND (NVL (s.osuser, 'x') <> 'SYSTEM')
AND (s.TYPE <> 'BACKGROUND') AND STATUS='ACTIVE'
)
ORDER BY "PROGRAM";
-- Query 12 To find the pid of the Concurrent job and kill it.
Select a.inst_id, sid, b.spid
from gv$session a, gv$process b,apps.fnd_concurrent_requests c where a.paddr = b.addr and request_ID ='31689665'
AND a.inst_id = b.inst_id and c.os_process_id = a.process;
-- Query 13:To find the Database SID of the Concurrent job
-- We need our concurrent request ID as an input.
-- c.SPID= is the operating system process id
-- d.sid= is the Oracle process id
SQL> column process heading "FNDLIBR PID"
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';
Concurrent Request Details in Oracle Apps R12 - Query
SELECT r.request_id,
DECODE (r.description,
NULL, pt.user_concurrent_program_name,
r.description || ' ( ' || pt.user_concurrent_program_name
|| ' ) '
) program,
r.phase_code,
r.status_code,
fnd_amp_private.get_phase (r.phase_code,
r.status_code,
r.hold_flag,
p.enabled_flag,
r.requested_start_date,
r.request_id
) phase,
fnd_amp_private.get_status (r.phase_code,
r.status_code,
r.hold_flag,
p.enabled_flag,
r.requested_start_date,
r.request_id
) status,
u.user_name,
rt.responsibility_name,
AT.application_name,
r.requested_start_date,
r.actual_start_date,
r.actual_completion_date,
r.request_date,
r.argument_text,
r.oracle_session_id,
r.completion_text,
r.controlling_manager,
r.requested_by,
r.program_application_id,
r.concurrent_program_id,
r.is_sub_request,
r.parent_request_id,
r.queue_method_code,
r.cd_id,
r.hold_flag,
p.enabled_flag,
p.concurrent_program_name,
p.user_concurrent_program_name,
r.os_process_id,
r.nls_language,
r.nls_territory,
r.nls_numeric_characters,
r.description
FROM fnd_concurrent_programs_tl pt,
fnd_responsibility_tl rt,
fnd_application_tl AT,
fnd_concurrent_programs_vl p,
fnd_user u,
fnd_concurrent_requests r
WHERE r.program_application_id = p.application_id
AND r.concurrent_program_id = p.concurrent_program_id
AND pt.concurrent_program_id = p.concurrent_program_id
AND pt.application_id = p.application_id
AND pt.LANGUAGE = USERENV ('LANG')
AND u.user_id = r.requested_by
AND rt.application_id = r.responsibility_application_id
AND rt.responsibility_id = r.responsibility_id
AND rt.LANGUAGE = USERENV ('LANG')
AND AT.application_id = r.program_application_id
AND AT.LANGUAGE = USERENV ('LANG')
AND r.request_id = :request_id;
• FND_FLEX_VALUE_SETS
• FND_ID_FLEX_SEGMENTS
• FND_FLEX_VALUE_NORM_HIERARCHY
• FND_FLEX_HIERARCHIES
• FND_FLEX_VALUE
• FND_FLEX_VALIDATION_EVENTS
• FND_FLEX_VALUE_RULE_LINES
• FND_FLEX_VALUE_RULE
• FND_FLEX_VALUE_RULE_USAGE
• FND_RESPONSIBLITY
• FND_TABLES
• FND_FLEX_VALIDATION_TABLES
====================Query to find out the responsibilities attached to a user in Oracle Application
Select b.user_name, c.responsibility_name, a.START_DATE, a.END_DATE
from fnd_user_resp_groups_direct a, fnd_user b, fnd_responsibility_tl c
where a.user_id = b.user_id
and a.responsibility_id = c.responsibility_id
and b.user_name='&username';
==================
===================application wise respnsibiltiies ==============
SELECT (SELECT application_name
FROM fnd_application_tl fa
WHERE fa.application_id = frt.application_id) application,
frt.responsibility_id, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt
where frt.responsibility_name like 'DKCT%Tal%'
order by 3
---------------Application Names-------------------
SELECT application_name, application_short_name, fa.application_id
FROM fnd_application fa, fnd_application_tl fat
WHERE application_short_name LIKE '%Q%'
AND fa.application_id = fat.application_id
ORDER BY fa.application_id
Query to list concurrent program details with its parameter, values set and default value/type
SQL Query to list all Oracle Reports assigned to a Responsibility
SELECT fcpl.user_concurrent_program_name "REPORT NAME",
fnrtl.responsibility_name, frg.request_group_name,
fcp.concurrent_program_name "CONCURRENT PROGRAM SHORT NAME"
FROM apps.fnd_request_groups frg,
apps.fnd_request_group_units frgu,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_programs_tl fcpl,
apps.fnd_executables fe,
apps.fnd_responsibility fnr,
apps.fnd_responsibility_tl fnrtl
WHERE frg.application_id = frgu.application_id
AND frg.request_group_id = frgu.request_group_id
AND frg.request_group_id = fnr.request_group_id
AND frg.application_id = fnr.application_id
AND fnr.responsibility_id = fnrtl.responsibility_id
AND frgu.request_unit_id = fcp.concurrent_program_id
AND frgu.unit_application_id = fcp.application_id
AND fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fe.execution_method_code LIKE 'P' --'Oracle Reports'
AND fcp.executable_id = fe.executable_id
AND fe.application_id = fcp.executable_application_id
-- AND fnrtl.responsibility_name LIKE '&Responsibility_Name'
-- Example Resp. Name : Inventory, Vision Operations (USA)
AND fnrtl.LANGUAGE = 'US'
AND fcpl.LANGUAGE = 'US';
=================================================================
Application wise reports name===============
SELECT cpv.user_concurrent_program_name "Concurrent Program Name",
cpv.concurrent_program_name "Program Short Name",
efv.application_name "Application",
-- cpv.enabled_flag "Enabled Flag",
-- cpv.description "Description",
-- cpv.output_file_type "Output Format",
fu.user_name "Created By (userid)",
-- ppf.full_name "Created By (username)",
DECODE(efv.execution_method_code,
'I', 'PL/SQL Stored Procedure',
'H', 'Host',
'S', 'Immediate',
'J', 'Java Stored Procedure',
'K', 'Java Concurrent Program',
'M', 'Multi Language Function',
'P', 'Oracle Reports',
'B', 'Request Set Stage Function',
'A', 'Spawned',
'L', 'SQL*Loader',
'Q', 'SQL*Plus',
'E', 'Pearl Concurrent Programm',
'Unknown') "Execution Method",
efv.executable_name "Executable Name",
efv.execution_file_name "Execution Filename"
FROM fnd_executables_form_v efv,
fnd_concurrent_programs_vl cpv,
fnd_user fu
-- per_all_people_f ppf
WHERE efv.executable_id = cpv.executable_id
AND efv.application_id = cpv.application_id
AND cpv.created_by = fu.user_id
and upper(cpv.user_concurrent_program_name) like 'D%'----------------------------------custom reports
-- AND fu.employee_id = ppf.person_id
and efv.execution_method_code='P'
-- AND cpv.user_concurrent_program_name = 'Active Users' -- // change it
ORDER BY cpv.user_concurrent_program_name;
==========================================================================================================
SELECT Distinct fcpl.user_concurrent_program_name "REPORT NAME",
fna.APPLICATION_NAME
-- fnrtl.responsibility_name, frg.request_group_name,
--fcp.concurrent_program_name "CONCURRENT PROGRAM SHORT NAME"
FROM apps.fnd_request_groups frg,
apps.fnd_request_group_units frgu,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_programs_tl fcpl,
apps.fnd_executables fe,
apps.fnd_responsibility fnr,
apps.fnd_responsibility_tl fnrtl,
apps.fnd_application_tl fna
WHERE frg.application_id = frgu.application_id
AND frg.request_group_id = frgu.request_group_id
AND frg.request_group_id = fnr.request_group_id
AND frg.application_id = fnr.application_id
and frg.application_id = fna.application_id
AND fnr.responsibility_id = fnrtl.responsibility_id
AND frgu.request_unit_id = fcp.concurrent_program_id
AND frgu.unit_application_id = fcp.application_id
AND fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fe.execution_method_code LIKE 'P' --'Oracle Reports'
AND fcp.executable_id = fe.executable_id
AND fe.application_id = fcp.executable_application_id
and fcpl.user_concurrent_program_name like 'D%'
-- AND fnrtl.responsibility_name LIKE '&Responsibility_Name'
-- Example Resp. Name : Inventory, Vision Operations (USA)
AND fnrtl.LANGUAGE = 'US'
AND fcpl.LANGUAGE = 'US';
============================================
To find from which responsibility a concurrent program can be run
SELECT distinct
a.user_concurrent_program_name,
a.description,
request_group_name,
e.responsibility_name
FROM
fnd_concurrent_programs_tl a,
fnd_request_groups b,
fnd_request_group_units c,
fnd_responsibility d,
fnd_responsibility_tl e
WHERE
a.concurrent_program_id = c.request_unit_id
and b.request_group_id = c.request_group_id
and b.request_group_id = d.request_group_id
and d.responsibility_id = e.responsibility_id
and a.application_id = b.application_id
and b.application_id = c.application_id
and d.application_id = e.application_id
and a.concurrent_program_id = :p_conc_program_id
Provide Concurrent Program name to the following query.
It lists all the Request Sets which are created with the Concurrent Program given.
SELECT DISTINCT user_request_set_name
FROM FND_REQUEST_SETS_TL
WHERE request_set_id IN
(SELECT request_set_id
FROM FND_REQUEST_SET_PROGRAMS
WHERE concurrent_program_id =
(SELECT CONCURRENT_PROGRAM_ID
FROM fnd_concurrent_programs_tl
WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Enter_Prog_name')));
Provide the Request Set name to the following query.
It lists all concurrent programs of this Request Set.
SELECT USER_CONCURRENT_PROGRAM_NAME
FROM fnd_concurrent_programs_tl
WHERE CONCURRENT_PROGRAM_ID IN
(SELECT concurrent_program_id
FROM FND_REQUEST_SET_PROGRAMS
WHERE request_set_id =
(SELECT request_set_id
FROM FND_REQUEST_SETS_TL
WHERE upper(user_request_set_name) = upper('&Request_Set_Name')));
Query to list concurrent program details with its parameter, values set and default value/type:
SELECT fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, fav.APPLICATION_SHORT_NAME
, fav.APPLICATION_NAME
, fav.application_id
, fdfcuv.end_user_column_name
, fdfcuv.form_left_prompt prompt
, fdfcuv.enabled_flag
, fdfcuv.required_flag
, fdfcuv.display_flag
, fdfcuv.flex_value_set_id
, ffvs.flex_value_set_name
, flv.meaning default_type
, fdfcuv.DEFAULT_VALUE
FROM fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl fdfcuv
, fnd_flex_value_sets ffvs
, fnd_lookup_values flv
, fnd_application_vl fav
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = :conc_prg_name
AND fcpl.LANGUAGE = 'US'
AND fav.application_id=fcp.application_id
AND fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = fdfcuv.default_type
AND flv.LANGUAGE(+) = USERENV ('LANG');
Query to find out concurrent program details and its parameters
SELECT fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, fcp.concurrent_program_id
, fav.APPLICATION_SHORT_NAME
, fav.APPLICATION_NAME
, fav.application_id
, fdfcuv.end_user_column_name
, fdfcuv.form_left_prompt prompt
, fdfcuv.enabled_flag
, fdfcuv.required_flag
, fdfcuv.display_flag
FROM fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl fdfcuv
, fnd_application_vl fav
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = :conc_prg_name
AND fav.application_id=fcp.application_id
AND fcpl.LANGUAGE = 'US'
AND fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name;
Query 1:For checking the locks in concurrent jobs
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,inst_id,id1, id2, lmode, request, type FROM gV$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request>0) ORDER BY id1,request;
-- Query 2:For checking the concurrent programs running currently with Details of Processed time-- and Start Date
SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,(a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 AS lag_time,d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.FND_CONCURRENT_PROGRAMS_TL c,apps.fnd_user d
WHERE a.concurrent_program_id=b.concurrent_program_id AND b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by=d.user_id AND status_code='R' order by Process_time desc;
-- Query 3:For checking last run of a Concurrent Program along with Processed time
-- Useful to find the Details of Concurrent programs which run daily and comparison purpose
SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time,
a.request_id,a.parent_request_id,To_Char(a.request_date,'DD-MON-YY HH24:MI:SS'),To_Char(a.actual_start_date,'DD-MON-YY HH24:MI:SS'),
To_Char(a.actual_completion_date,'DD-MON-YY HH24:MI:SS'), (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b ,
apps.FND_CONCURRENT_PROGRAMS_TL c,
apps.fnd_user d
WHERE a.concurrent_program_id= b.concurrent_program_id AND
b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by =d.user_id AND
-- trunc(a.actual_completion_date) = '24-AUG-2005'
c.USER_CONCURRENT_PROGRAM_NAME='Incentive Compensation Analytics - ODI' -- and argument_text like '%, , , , ,%';
-- and status_code!='C'
-- Query 4:For Checking the last run of concurrent Program.
- Use below query to check all the concurrent request running which may refer given package
-- This is very useful check before compiling any package on given instance.
-- The query can be modified as per requirement.
-- Remove FND_CONCURRENT_REQUESTS table and joins to check all program dependent on given package.
SELECT
FCR.REQUEST_ID
,FCPV.USER_CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_ID
,FCR.STATUS_CODE
,FCR.PHASE_CODE
FROM FND_CONCURRENT_PROGRAMS_VL FCPV
,FND_EXECUTABLES FE
,SYS.DBA_DEPENDENCIES DD
,FND_CONCURRENT_REQUESTS FCR
WHERE FCPV.EXECUTABLE_ID = FE.EXECUTABLE_ID
AND FE.EXECUTION_METHOD_CODE = 'I'
AND SUBSTR(FE.EXECUTION_FILE_NAME,1,INSTR(FE.EXECUTION_FILE_NAME, '.', 1, 1) - 1) = UPPER(DD.NAME)
AND DD.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'TRIGGER', 'PACKAGE') -- add as required
--AND referenced_owner = 'XXCUS'
AND DD.REFERENCED_NAME = UPPER('&Package_name')
AND FCR.CONCURRENT_PROGRAM_ID = FCPV.CONCURRENT_PROGRAM_ID
AND fcr.phase_code NOT IN ( 'C','P');
-- Query 5:The following query will display the time taken to execute the concurrent Programs
--for a particular user with the latest concurrent programs sorted in least time taken
-- to complete the request.
SELECT
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.actual_completion_date end_on,
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference,
p.concurrent_program_name concurrent_program_name,
decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
f.status_code
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
order by
f.actual_start_date desc;
--Query 6: By using the below Query we can get sid,serial#,spid of the concurrent Request..
SELECT a.request_id, d.sid, d.serial# , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';
--Query 7: By using below Concurrent Manager and Program rules...
--Gives Detail of the Concurrent_queue_name and User_concurrent_program_name
SELECT b.concurrent_queue_name, c.user_concurrent_program_name
FROM FND_CONCURRENT_QUEUE_CONTENT a, fnd_concurrent_queues b, fnd_concurrent_programs_vl c
WHERE a.queue_application_id = 283
and a.concurrent_queue_id = b.concurrent_queue_id
and a.type_id = c.concurrent_program_id
order by decode(INCLUDE_FLAG, 'I', 1, 2), type_code;
--Query 8: Gives Details of Running Concurrent jobs
SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,
(a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b ,
apps.FND_CONCURRENT_PROGRAMS_TL c,
apps.fnd_user d
WHERE a.concurrent_program_id=b.concurrent_program_id AND
b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by=d.user_id AND
status_code='R' order by Process_time desc;
-- Query 9: Gives detail of Concurrent job completed and pending
SELECT
FCR.REQUEST_ID
,FCPV.USER_CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_ID
,FCR.STATUS_CODE
,FCR.PHASE_CODE
FROM FND_CONCURRENT_PROGRAMS_VL FCPV
,FND_EXECUTABLES FE
,SYS.DBA_DEPENDENCIES DD
,FND_CONCURRENT_REQUESTS FCR
WHERE FCPV.EXECUTABLE_ID = FE.EXECUTABLE_ID
AND FE.EXECUTION_METHOD_CODE = 'I'
AND SUBSTR(FE.EXECUTION_FILE_NAME,1,INSTR(FE.EXECUTION_FILE_NAME, '.', 1, 1) - 1) = UPPER(DD.NAME)
AND DD.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'TRIGGER', 'PACKAGE') -- add as required
--AND referenced_owner = 'XXCUS'
AND DD.REFERENCED_NAME = UPPER('&Package_name')
AND FCR.CONCURRENT_PROGRAM_ID = FCPV.CONCURRENT_PROGRAM_ID
AND fcr.phase_code NOT IN ( 'C','P');
-- Query 10:Gives Detail of Running and Completed Concurrent jobs with Start date and end date
-- Latest one at the top
select
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.actual_completion_date end_on,
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference,
p.concurrent_program_name concurrent_program_name,
decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
f.status_code
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
order by
f.actual_start_date desc;
--- Query 11 wait events details related with Concurrent programs
SELECT s.saddr, s.SID, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal,
UPPER (s.program) program, s.TYPE, s.sql_address, s.sql_hash_value,
s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id,
s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled,
s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2,
s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#,
s.wait_class, s.wait_time, s.seconds_in_wait, s.state,
s.wait_time_micro, s.time_remaining_micro,
s.time_since_last_wait_micro, s.service_name, s.sql_trace,
s.sql_trace_waits, s.sql_trace_binds, s.sql_trace_plan_stats,
s.session_edition_id, s.creator_addr, s.creator_serial#
FROM v$session s
WHERE ( (s.username IS NOT NULL)
AND (NVL (s.osuser, 'x') <> 'SYSTEM')
AND (s.TYPE <> 'BACKGROUND') AND STATUS='ACTIVE'
)
ORDER BY "PROGRAM";
-- Query 12 To find the pid of the Concurrent job and kill it.
Select a.inst_id, sid, b.spid
from gv$session a, gv$process b,apps.fnd_concurrent_requests c where a.paddr = b.addr and request_ID ='31689665'
AND a.inst_id = b.inst_id and c.os_process_id = a.process;
-- Query 13:To find the Database SID of the Concurrent job
-- We need our concurrent request ID as an input.
-- c.SPID= is the operating system process id
-- d.sid= is the Oracle process id
SQL> column process heading "FNDLIBR PID"
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';
Concurrent Request Details in Oracle Apps R12 - Query
SELECT r.request_id,
DECODE (r.description,
NULL, pt.user_concurrent_program_name,
r.description || ' ( ' || pt.user_concurrent_program_name
|| ' ) '
) program,
r.phase_code,
r.status_code,
fnd_amp_private.get_phase (r.phase_code,
r.status_code,
r.hold_flag,
p.enabled_flag,
r.requested_start_date,
r.request_id
) phase,
fnd_amp_private.get_status (r.phase_code,
r.status_code,
r.hold_flag,
p.enabled_flag,
r.requested_start_date,
r.request_id
) status,
u.user_name,
rt.responsibility_name,
AT.application_name,
r.requested_start_date,
r.actual_start_date,
r.actual_completion_date,
r.request_date,
r.argument_text,
r.oracle_session_id,
r.completion_text,
r.controlling_manager,
r.requested_by,
r.program_application_id,
r.concurrent_program_id,
r.is_sub_request,
r.parent_request_id,
r.queue_method_code,
r.cd_id,
r.hold_flag,
p.enabled_flag,
p.concurrent_program_name,
p.user_concurrent_program_name,
r.os_process_id,
r.nls_language,
r.nls_territory,
r.nls_numeric_characters,
r.description
FROM fnd_concurrent_programs_tl pt,
fnd_responsibility_tl rt,
fnd_application_tl AT,
fnd_concurrent_programs_vl p,
fnd_user u,
fnd_concurrent_requests r
WHERE r.program_application_id = p.application_id
AND r.concurrent_program_id = p.concurrent_program_id
AND pt.concurrent_program_id = p.concurrent_program_id
AND pt.application_id = p.application_id
AND pt.LANGUAGE = USERENV ('LANG')
AND u.user_id = r.requested_by
AND rt.application_id = r.responsibility_application_id
AND rt.responsibility_id = r.responsibility_id
AND rt.LANGUAGE = USERENV ('LANG')
AND AT.application_id = r.program_application_id
AND AT.LANGUAGE = USERENV ('LANG')
AND r.request_id = :request_id;
No comments:
Post a Comment