Concurrent
Manager and program related scripts
SQL Script to Troubleshoot a long-running concurrent request
set term on
set feedback on
set echo on
set arraysize 4
set linesize 200
set pages 9999
set underline =;
column username format A15
column sid format 9990 heading SID
column type format A4
column lmode format 990 heading 'HELD'
column request format 990 heading 'REQ'
column id1 format 9999990
column id2 format 9999990
column sql_text format a100
column name format a80
break on id1 skip 1 dup
undefine v_request_id
define v_request_id
undefine v_spid
define v_spid
undefine v_sid
define v_sid
spool vj_concurrent_monitor.lst
Prompt Enter the concurrent_request_id
Accept v_request_id
prompt checking requests
select oracle_process_id from fnd_concurrent_requests where request_id='&v_request_id';
Prompt Enter the operating system oracle process id for this concurrent request
accept v_spid
Prompt Getting the sid
SELECT SID,SERIAL#,LOGON_TIME FROM V$SESSION WHERE PADDR IN
(SELECT ADDR FROM V$PROCESS WHERE SPID='&v_spid');
prompt Enter the session id for this concurrent request
accept v_sid
prompt memory usage for this session
SELECT A.SID,A.USERNAME,B.VALUE,c.name FROM V$SESSION a,V$SESSTAT B,V$STATNAME C WHERE A.SID=B.SID
AND B.STATISTIC#=C.STATISTIC# AND C.NAME like'%memor%' and a.sid='&v_sid';
prompt resource usage for this session
SELECT A.SID,A.USERNAME,B.VALUE,c.name FROM V$SESSION a,V$SESSTAT B,V$STATNAME C WHERE A.SID=B.SID
AND B.STATISTIC#=C.STATISTIC# and a.sid='&v_sid' order by b.value;
prompt this session waited on
select sid,event,wait_time,state from v$session_wait where sid='&v_sid' order by wait_time;
prompt current sql executing by this session
select a.sid,b.sorts,b.executions,b.loads,b.parse_calls,b.disk_reads,
b.buffer_gets,b.rows_processed,C.sql_text from v$session a,v$sqlarea b,V$SQLTEXT C
where a.sql_address=b.address and b.address=c.address and a.sid='&v_sid';
prompt sql which is taking more than 3mb in shared pool
prompt nosql should take morethan 1mb in shared pool.
prompt please ask the developers to tune the following sql statements
select name,
namespace,type,sharable_mem/(1024*1024) sharablemem,loads,executions,locks,pins,kept from v$db_object_cache
where SHARABLE_MEM>3000000;
prompt sort segments using by this session
SELECT s.username,s.sid,s.osuser,s.process,s.machine,u.extents, u.blocks,u.tablespace FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr order by extents;
and s.sid='&v_sid';
prompt current temp segments free in this instance
SELECT tablespace_name, extent_size, total_extents, used_extents, free_extents, max_used_size FROM v$sort_segment;
prompt total system events at this time
select event,total_waits waits, total_timeouts timeouts, time_waited total_time from v$system_event order by total_waits;
prompt latch contention if thery is any
SELECT latch#, name, gets, misses, sleeps FROM v$latch WHERE sleeps>0 ORDER BY sleeps ;
prompt the latch which is sleeping
select name, sleeps,latch# from v$latch_children where sleeps>4 order by sleeps;
spool off
clear columns
clear breaks
How to find out which request is handle by which concurrent queue.
a) First find out short_name of a program and then pass it as parameter to below query.
b) The below query will give you output
I - Included - Included in new concurrent queue
E - excluded from Standard Manager
This way you know now this running program (concurrent request) is handled by new manager and not part of standard manager.
SELECT A.INCLUDE_FLAG, A.QUEUE_APPLICATION_ID, C.USER_CONCURRENT_QUEUE_NAME,
B.CONCURRENT_PROGRAM_NAME
FROM APPLSYS.FND_CONCURRENT_QUEUE_CONTENT A, APPLSYS.FND_CONCURRENT_PROGRAMS B, APPS.FND_CONCURRENT_QUEUES_VL C
WHERE type_id = b.concurrent_program_id and b.concurrent_program_name = ‘&SHORT_NAME’ and c.concurrent_queue_id = a.concurrent_queue_id
How to find out Summary of Concurrent requests.
SELECT
request_id, SUBSTR(requestor,1,25), SUBSTR(program,1,50), SUBSTR(user_concurrent_program_name,1,100),
TO_CHAR(actual_start_date,’dd/mm/yy :hh24:mi’) start_date,
TO_CHAR(actual_completion_date,’dd/mm/yy :hh24:mi’) completion_date,
FLOOR((ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24) “in Hours”,
(((ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24)-(FLOOR((ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24)))*60 “In_Min”
–requestor, program, user_concurrent_program_name
FROM fnd_conc_req_summary_v
WHERE (ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24*60 >10
How to find database SID from a Concurrent request.
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’;
You need your concurrent request ID as an input.
c.SPID= is the operating system process id
d.sid= is the Oracle process id
Cancel Concurrent requests. We don't need concurrent requests which are scheduled in production to keep running in test. We use the following update to cancel them.
update fnd_concurrent_requests
set phase_code='C',
status_code='D'
where phase_code = 'P'
and concurrent_program_id not in (
select concurrent_program_id
from fnd_concurrent_programs_tl
where user_concurrent_program_name like '%Synchronize%tables%'
or user_concurrent_program_name like '%Workflow%Back%'
or user_concurrent_program_name like '%Sync%responsibility%role%'
or user_concurrent_program_name like '%Workflow%Directory%')
and (status_code = 'I' OR status_code = 'Q');
Use the SQL below to only cancel the running requests connecting as sys
UPDATE applsys.fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE phase_code = 'R' and status_code ='R'
/
commit
Also, please put all Pending Jobs on Hold, using the SQL below connecting as sys
update applsys.fnd_concurrent_requests
set hold_flag='Y' where
phase_code='P' and hold_flag='N'
/
commit
update fnd_concurrent_requests fcr
set phase_code = 'C',
status_code = 'D'
where fcr.PHASE_CODE <> 'C'
and (fcr.program_application_id,fcr.CONCURRENT_PROGRAM_id) in
(select fcp.application_id,fcp.concurrent_program_id from fnd_concurrent_programs fcp,fnd_executables_vl fev
where fcp.executable_application_id=fev.application_id and fcp.executable_id=fev.executable_id
and (upper(fev.user_executable_name) like 'AL%MAIL%'
or upper(fev.user_executable_name) like 'AL%FTP%'
or upper(fev.user_executable_name) like 'AL%EXCEL%'))
/
To change the number of processes for the standard manager
update FND_CONCURRENT_QUEUE_SIZE
set min_processes = 4
where concurrent_queue_id = 0;
how to find params passed to request from backend
select CONCURRENT_PROGRAM_ID,CONCURRENT_PROGRAM_NAME from fnd_concurrent_programs where concurrent_program_name like '';
select REQUEST_ID,CONCURRENT_PROGRAM_ID,substr(ARGUMENT_TEXT,1,60)params,status_code,phase_code from fnd_concurrent_requests where CONCURRENT_PROGRAM_ID=;
To increase the jvm for OPP
"From Note ID 737311.1 we need to do step 3 We are changing this parameter to 2048m as mentioned below in script.
Configure the Output Post Processor's JVM. These steps set the JVM to 2GB, depending upon
your server's size you might find 3 GB (-mx3072m), 4GB (-mx4096m) or even 5GB (-mx5120m) is a better value. This setting prevents the error ""java.lang.OutOfMemoryError: Java heap space""
in the Output Post Processor's log associated to the Subledger Accounting Program.
Login to SQL*Plus as APPS.
SQL>update FND_CP_SERVICES set DEVELOPER_PARAMETERS =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
Bounce the concurrent managers.
one more eg:
To determine current heap size:
select DEVELOPER_PARAMETERS
from FND_CP_SERVICES
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
To increase heap size to 1024:
update FND_CP_SERVICES
set DEVELOPER_PARAMETERS = 'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
Query to find pending concurrent requests
select count(*)
from APPS.FND_ 2 CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.PHASE_CODE = 'P'
and b.requested_start_date <= sysdate
Pending job details
"SELECT c.user_name, request_id, phase_code, status_code, hold_flag,
TO_CHAR(requested_start_date,'DD-MON-YY:HH24:MM:SS') Requested_Start_Date,
user_concurrent_program_name, b.concurrent_program_id
FROM applsys.fnd_concurrent_requests a,
applsys.fnd_concurrent_programs_tl b,
applsys.fnd_user c
WHERE a.phase_code = 'P'
AND a.concurrent_program_id = b.concurrent_program_id
AND b.LANGUAGE = 'US'
AND c.user_id = a.requested_by
ORDER BY user_concurrent_program_name;"
Cancel scheduled concurrent Request “Gather Schema Statistics” sqlplus apps/apps
sql>
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I') and concurrent_program_id=38121;
Commit;
Exit
Putting all concurrent jobs on hold:
Update applsys.fnd_concurrent_requests set hold_flag='Y' where phase_code in ('R','P','I');
Cancel scheduled concurrent Request “Gather Schema Statistics”
sqlplus apps/apps
sql>
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I') and concurrent_program_id=38121;
Commit;
Exit
To terminate request from backend
SQL> select REQUEST_ID,ORACLE_ID,ORACLE_PROCESS_ID,ORACLE_SESSION_ID,OS_PROCESS_ID from applsys.FND_CONCURRENT_REQUESTS where REQUEST_ID=577945;
REQUEST_ID ORACLE_ID ORACLE_PROCESS_ID ORACLE_SESSION_ID
---------- ---------- ------------------------------ -----------------
OS_PROCESS_ID
------------------------------------------------------------------------------------------------------------------------------------
577945 900 13348 1242142
13299
SQL> !kill -9 13348
/bin/ksh: kill: 13348: No such process
SQL> update APPLSYS.fnd_Concurrent_requests set PHASE_CODE='C', STATUS_CODE='D' where REQUEST_ID=577945;
1 row updated.
SQL> commit;
Commit complete.
Schedule “Purge Concurrent Request and/or Manager Data”
Cancel existing scheduled request before scheduling new.
# ebappsenv
# sqlplus apps
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I')
and concurrent_program_id=32263;
Parameter:
Entity : REQUEST
Mode : Age
Mode Value : 15 à On non-prod environments as per case# 734780
Schedule:
on specific days : Wednesday and Saturday at 19:00 CET
Run Gather Schema Statistics as a Concurrent request
Cancel any pending jobs for “Gather Schema Statistics”
Sqlplus apps/appspasswd
Sql>
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I') and concurrent_program_id=38121;
Login to Oracle applications of target instance as sysadmin thru Appjump
Select system administrator Responisibility
Verify whether Concurrent Request “Gather Schema Statistics” is running or not
If not running, schedule the request to run immediately with Parameters : ALL,10
To check status fo running requests:
column REQUEST heading 'Request' format a8
column PHASE heading 'Phase' format A8
column STATUS heading 'Status' format A8
column PROGRAM heading 'Program Name' format A40
column SHORT heading 'Short Name' format A15
column REQUESTOR heading 'Requestor' format A10
column START_TIME heading 'Start Time' format A15
column RUN_TIME justify left heading 'Time(m)' format 999999.9
column OSPID heading 'OSPID' format a5
column OS_PIDa heading 'OSPIDA' format a6
column SID heading 'SID' format 99999
column serial# heading 'Serial#' format 99999
select substr(fcrv.request_id,1,8)REQUEST,
decode(fcrv.phase_code,'P','Pending','R','Running','I','Inactive','Completed')PHASE,
decode(fcrv.status_code,
'A','Waiting',
'B','Resuming',
'C','Normal',
'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',fcrv.status_code)STATUS,
substr(fcrv.program,1,40)PROGRAM,substr(fcrv.PROGRAM_SHORT_NAME,1,15)SHORT,
substr(fcrv.requestor,1,15)REQUESTOR,
-- to_char(fcrv.actual_start_date,'MM/DD/RR HH24:MI')START_TIME,
round(((sysdate - fcrv.actual_start_date)*1440),1)RUN_TIME,
substr(fcr.oracle_process_id,1,7)OSPID,s.sid,s.serial#
from apps.fnd_conc_req_summary_v fcrv,
apps.fnd_concurrent_requests fcr,
v$session s,v$process p
where fcrv.phase_code = 'R'
and fcrv.request_id = fcr.request_id
and s.paddr = p.addr
and fcr.oracle_process_id = p.spid
and fcrv.concurrent_program_id not in ('40112','40113','36887')
--and trunc(fcrv.actual_start_date) like trunc(sysdate)
order by PHASE, STATUS, REQUEST desc;
Take export dump of concurrent tables and import
exp userid=applsys/xxxx file=conc.dmp log=exp.log tables=FND_CONCURRENT_QUEUES,FND_CONCURRENT_QUEUES_TL,
FND_CONCURRENT_QUEUE_SIZE, FND_CONCURRENT_QUEUE_CONTENT
Truncate the fnd concurrent tables
SQL> truncate table fnd_Concurrent_queues;
Table truncated.
SQL> truncate table FND_CONCURRENT_QUEUES_TL;
Table truncated.
SQL> truncate table FND_CONCURRENT_QUEUE_SIZE;
Table truncated.
SQL> truncate table FND_CONCURRENT_QUEUE_CONTENT;
Table truncated.
Import the data back into the fnd tables which was exported as part of step above
imp userid=applsys/apps ignore=y file=conc.dmp full=y log=imp.log
Check whether the GSM is Up and Running in the system --> Profile option or using the Following Query :
select DECODE(b.profile_option_value, 'Y', 'Enabled', 'Disabled') DETAILS
from fnd_profile_options a, fnd_profile_option_values b
where a.APPLICATION_ID = b.APPLICATION_ID
and a.PROFILE_OPTION_ID = b.PROFILE_OPTION_ID
and a.PROFILE_OPTION_NAME = 'CONC_GSM_ENABLED';
Check whether the Service Manager is up and Running by the following Query :
select CONCURRENT_QUEUE_NAME, ENABLED_FLAG, MAX_PROCESSES, RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME like 'FNDSM%';
The following SQL scripts located under $FND_TOP/sql are useful when diagnosing concurrent manager problems:
afimchk.sql Tells the status of the ICM and PMON method
afcmstat.sql Lists active manager processes
afrqrun.sql Lists all the running, waiting and Terminating requests
afrqwait.sql Lists requests that are constrained and waiting for the ICM to release them.
afrqscm.sql Prints log file name of managers that can run a given request. It can be used to check for possible errors when a request stays in pending status. It requires a request id value.
afcmcreq.sql Prints the log file name of the manager that processed the request
afrqstat.sql Summary of completed concurrent requests grouped by completion status and execution type. It requires number of days prior to today on which to report parameter.
afimlock.sql Lists locks that the ICM is waiting to get
afcmrrq.sql Lists managers that currently are running a request
Reports on requests that having been running for over a specified amount of time (hard coded as 4 hours). Exceptions, in addition to the defaults below, can be added by entering the program ID for the program exceptions under multi-items.
Runs Every 20 minutes
Uses the following query:
select b.REQUEST_ID, a.DESCRIPTION, b.phase_code,
(sysdate - b.actual_start_date) * 24 "running",
to_char(sysdate, 'mm/dd/yyyy hh:mi') "now",
to_char(b.request_date, 'mm/dd/yyyy hh:mi') "request_date",
to_char(b.actual_start_date, 'mm/dd/yyyy hh:mi') "start_time",
b.program_application_id "program_application_id",
b.concurrent_program_id "concurrent_program_id"
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.STATUS_CODE = 'R'
and b.PHASE_CODE = 'R'
and ((sysdate - b.actual_start_date) * 24) > 4
and a.CONCURRENT_PROGRAM_ID NOT IN(36887,43393,38121,42789,31556)
Excludes:
36887 - Workflow Mailer
43393 - ITM Adapter
38121 - Gather Schema Statistics
42789 - OAM Applications Dashboard Collection
31556 - Planning Manager
Monitors pending jobs exceeds the specified threshold. Excessive pending jobs may indicate an issue with the Concurrent Manager. Uses the following query:
select a.concurrent_program_name, b.REQUEST_ID, a.description,
to_char(b.request_date, 'mm/dd/yyyy hh:mi:ss') ""request_date"",
to_char(b.requested_start_date, 'mm/dd/yyyy hh:mi:ss') ""request_start""
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.PHASE_CODE = 'P'
and b.requested_start_date <= sysdate
The error threshold monitor will alert if the number of failed jobs exceeds the user defined threshold in a 30 minute period.
select a.concurrent_program_name, b.REQUEST_ID, a.description,
b.status_code, b.phase_code
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.STATUS_CODE IN ('E')
and b.actual_completion_date > sysdate - 1/48
Find request which are put on hold
SQL> select REQUEST_ID from fnd_concurrent_requests where phase_code ='P' and hold_flag='Y';
To find oracle_process id for a request id to pull trace file from udump:
select oracle_process_id ,
decode(status_code,'R','Running','D','Canceled','E','Error','X','Terminated','G','Warning','T','Terminating')""Status_code"",
phase_code,to_char(actual_start_date,'DD-MON-YYYY=>hh24:mi:ss') ""Login Time""
from apps.fnd_concurrent_requests where request_id='&Enter_conn_req_id'
To find spid of a request to get the trace file
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
To check failed jobs submitted by an user
set lines 1000 pages 100
clear columns
col "Submitted By" format a15 word_wrap
select user_name "Submitted By", request_id "Request #",
to_char(cr.request_date,'dd-mon-rr hh24:mi') "Submitted on",
to_char(cr.last_update_date,'dd-mon-rr hh24:mi') "Failed on"
from applsys.fnd_concurrent_requests cr,
applsys.fnd_user u
where u.user_id = cr.requested_by
and user_name like '%BATCH%'
and cr.status_code ='E'
and cr.phase_code ='C'
and cr.request_date > sysdate - 1
order by 1
Statement to put the jobs on hold and release them lateron
To hold the requests (as apps user):
1) Drop table apps.str_dba_conc_req_hold ;
2) Create table apps.str_dba_conc_req_hold as select * from fnd_Concurrent_requests where PHASE_CODE='P' and hold_flag='N';
3) select count(*) from apps.str_dba_conc_req_hold ;
4) update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and hold_flag='N' and request_id in (select request_id from apps.str_dba_conc_req_hold) ;
NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same
5) Commit;
To Release these requests in prod after patching, here is the step :
6) a. update fnd_Concurrent_requests set hold_flag='N' where request_id in (select request_id from apps.str_dba_conc_req_hold);
b. commit;
How to take cm program trace.
Responsibility: System Administrator
Navigate: Concurrent > Program > Define
Query Concurrent Program
Select the Enable Trace Checkbox
Responsibility: System Administrator
Navigate: Profiles > System
Query Profile Option Concurrent: Allow Debugging
Set profile to Yes
Logon to the Responsibility that runs the Concurrent Program
In the Submit Request Screen click on Debug Options (B)
Select the Checkbox for SQL Trace
To submit active user request from backend
CONCSUB APPS/APPS SYSADMIN "System Administrator" SYSADMIN CONCURRENT FND FNDSCURS PROGRAM_NAME='"Active Users"'
sql program to submit request from backend
"SET SERVEROUTPUT ON
declare
req_id number;
begin
DBMS_OUTPUT.PUT_LINE('In begin');
fnd_global.APPS_INITIALIZE (0, 21758, 671);
req_id := FND_REQUEST.SUBMIT_REQUEST(application => 'FND',program => 'FNDSCURS',description => '',start_time =>
'',sub_request => FALSE);
if (req_id = 0) then
/* Handle submission error */
DBMS_OUTPUT.PUT_LINE('Request ID :' || req_id);
DBMS_OUTPUT.PUT_LINE('As the request ID is 0, the request was not submitted');
DBMS_OUTPUT.PUT_LINE('Please verify this part again');
else
DBMS_OUTPUT.PUT_LINE('Request ID :' || req_id);
DBMS_OUTPUT.PUT_LINE('Request submitted successfully');
commit;
end if;
end;
/
Meaning of status_code and phase_code in FND_CONCURRENT_REQUESTS table STATUS_CODE Column:
A - Waiting
B - Resuming
C - Normal
D - Cancelled
E - Error
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
PHASE_CODE column
C - Completed
I - Inactive
P - Pending
R - Running
set term on
set feedback on
set echo on
set arraysize 4
set linesize 200
set pages 9999
set underline =;
column username format A15
column sid format 9990 heading SID
column type format A4
column lmode format 990 heading 'HELD'
column request format 990 heading 'REQ'
column id1 format 9999990
column id2 format 9999990
column sql_text format a100
column name format a80
break on id1 skip 1 dup
undefine v_request_id
define v_request_id
undefine v_spid
define v_spid
undefine v_sid
define v_sid
spool vj_concurrent_monitor.lst
Prompt Enter the concurrent_request_id
Accept v_request_id
prompt checking requests
select oracle_process_id from fnd_concurrent_requests where request_id='&v_request_id';
Prompt Enter the operating system oracle process id for this concurrent request
accept v_spid
Prompt Getting the sid
SELECT SID,SERIAL#,LOGON_TIME FROM V$SESSION WHERE PADDR IN
(SELECT ADDR FROM V$PROCESS WHERE SPID='&v_spid');
prompt Enter the session id for this concurrent request
accept v_sid
prompt memory usage for this session
SELECT A.SID,A.USERNAME,B.VALUE,c.name FROM V$SESSION a,V$SESSTAT B,V$STATNAME C WHERE A.SID=B.SID
AND B.STATISTIC#=C.STATISTIC# AND C.NAME like'%memor%' and a.sid='&v_sid';
prompt resource usage for this session
SELECT A.SID,A.USERNAME,B.VALUE,c.name FROM V$SESSION a,V$SESSTAT B,V$STATNAME C WHERE A.SID=B.SID
AND B.STATISTIC#=C.STATISTIC# and a.sid='&v_sid' order by b.value;
prompt this session waited on
select sid,event,wait_time,state from v$session_wait where sid='&v_sid' order by wait_time;
prompt current sql executing by this session
select a.sid,b.sorts,b.executions,b.loads,b.parse_calls,b.disk_reads,
b.buffer_gets,b.rows_processed,C.sql_text from v$session a,v$sqlarea b,V$SQLTEXT C
where a.sql_address=b.address and b.address=c.address and a.sid='&v_sid';
prompt sql which is taking more than 3mb in shared pool
prompt nosql should take morethan 1mb in shared pool.
prompt please ask the developers to tune the following sql statements
select name,
namespace,type,sharable_mem/(1024*1024) sharablemem,loads,executions,locks,pins,kept from v$db_object_cache
where SHARABLE_MEM>3000000;
prompt sort segments using by this session
SELECT s.username,s.sid,s.osuser,s.process,s.machine,u.extents, u.blocks,u.tablespace FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr order by extents;
and s.sid='&v_sid';
prompt current temp segments free in this instance
SELECT tablespace_name, extent_size, total_extents, used_extents, free_extents, max_used_size FROM v$sort_segment;
prompt total system events at this time
select event,total_waits waits, total_timeouts timeouts, time_waited total_time from v$system_event order by total_waits;
prompt latch contention if thery is any
SELECT latch#, name, gets, misses, sleeps FROM v$latch WHERE sleeps>0 ORDER BY sleeps ;
prompt the latch which is sleeping
select name, sleeps,latch# from v$latch_children where sleeps>4 order by sleeps;
spool off
clear columns
clear breaks
How to find out which request is handle by which concurrent queue.
a) First find out short_name of a program and then pass it as parameter to below query.
b) The below query will give you output
I - Included - Included in new concurrent queue
E - excluded from Standard Manager
This way you know now this running program (concurrent request) is handled by new manager and not part of standard manager.
SELECT A.INCLUDE_FLAG, A.QUEUE_APPLICATION_ID, C.USER_CONCURRENT_QUEUE_NAME,
B.CONCURRENT_PROGRAM_NAME
FROM APPLSYS.FND_CONCURRENT_QUEUE_CONTENT A, APPLSYS.FND_CONCURRENT_PROGRAMS B, APPS.FND_CONCURRENT_QUEUES_VL C
WHERE type_id = b.concurrent_program_id and b.concurrent_program_name = ‘&SHORT_NAME’ and c.concurrent_queue_id = a.concurrent_queue_id
How to find out Summary of Concurrent requests.
SELECT
request_id, SUBSTR(requestor,1,25), SUBSTR(program,1,50), SUBSTR(user_concurrent_program_name,1,100),
TO_CHAR(actual_start_date,’dd/mm/yy :hh24:mi’) start_date,
TO_CHAR(actual_completion_date,’dd/mm/yy :hh24:mi’) completion_date,
FLOOR((ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24) “in Hours”,
(((ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24)-(FLOOR((ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24)))*60 “In_Min”
–requestor, program, user_concurrent_program_name
FROM fnd_conc_req_summary_v
WHERE (ACTUAL_COMPLETION_DATE- ACTUAL_START_DATE)*24*60 >10
How to find database SID from a Concurrent request.
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’;
You need your concurrent request ID as an input.
c.SPID= is the operating system process id
d.sid= is the Oracle process id
Cancel Concurrent requests. We don't need concurrent requests which are scheduled in production to keep running in test. We use the following update to cancel them.
update fnd_concurrent_requests
set phase_code='C',
status_code='D'
where phase_code = 'P'
and concurrent_program_id not in (
select concurrent_program_id
from fnd_concurrent_programs_tl
where user_concurrent_program_name like '%Synchronize%tables%'
or user_concurrent_program_name like '%Workflow%Back%'
or user_concurrent_program_name like '%Sync%responsibility%role%'
or user_concurrent_program_name like '%Workflow%Directory%')
and (status_code = 'I' OR status_code = 'Q');
Use the SQL below to only cancel the running requests connecting as sys
UPDATE applsys.fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE phase_code = 'R' and status_code ='R'
/
commit
Also, please put all Pending Jobs on Hold, using the SQL below connecting as sys
update applsys.fnd_concurrent_requests
set hold_flag='Y' where
phase_code='P' and hold_flag='N'
/
commit
update fnd_concurrent_requests fcr
set phase_code = 'C',
status_code = 'D'
where fcr.PHASE_CODE <> 'C'
and (fcr.program_application_id,fcr.CONCURRENT_PROGRAM_id) in
(select fcp.application_id,fcp.concurrent_program_id from fnd_concurrent_programs fcp,fnd_executables_vl fev
where fcp.executable_application_id=fev.application_id and fcp.executable_id=fev.executable_id
and (upper(fev.user_executable_name) like 'AL%MAIL%'
or upper(fev.user_executable_name) like 'AL%FTP%'
or upper(fev.user_executable_name) like 'AL%EXCEL%'))
/
To change the number of processes for the standard manager
update FND_CONCURRENT_QUEUE_SIZE
set min_processes = 4
where concurrent_queue_id = 0;
how to find params passed to request from backend
select CONCURRENT_PROGRAM_ID,CONCURRENT_PROGRAM_NAME from fnd_concurrent_programs where concurrent_program_name like '';
select REQUEST_ID,CONCURRENT_PROGRAM_ID,substr(ARGUMENT_TEXT,1,60)params,status_code,phase_code from fnd_concurrent_requests where CONCURRENT_PROGRAM_ID=;
To increase the jvm for OPP
"From Note ID 737311.1 we need to do step 3 We are changing this parameter to 2048m as mentioned below in script.
Configure the Output Post Processor's JVM. These steps set the JVM to 2GB, depending upon
your server's size you might find 3 GB (-mx3072m), 4GB (-mx4096m) or even 5GB (-mx5120m) is a better value. This setting prevents the error ""java.lang.OutOfMemoryError: Java heap space""
in the Output Post Processor's log associated to the Subledger Accounting Program.
Login to SQL*Plus as APPS.
SQL>update FND_CP_SERVICES set DEVELOPER_PARAMETERS =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
Bounce the concurrent managers.
one more eg:
To determine current heap size:
select DEVELOPER_PARAMETERS
from FND_CP_SERVICES
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
To increase heap size to 1024:
update FND_CP_SERVICES
set DEVELOPER_PARAMETERS = 'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
Query to find pending concurrent requests
select count(*)
from APPS.FND_ 2 CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.PHASE_CODE = 'P'
and b.requested_start_date <= sysdate
Pending job details
"SELECT c.user_name, request_id, phase_code, status_code, hold_flag,
TO_CHAR(requested_start_date,'DD-MON-YY:HH24:MM:SS') Requested_Start_Date,
user_concurrent_program_name, b.concurrent_program_id
FROM applsys.fnd_concurrent_requests a,
applsys.fnd_concurrent_programs_tl b,
applsys.fnd_user c
WHERE a.phase_code = 'P'
AND a.concurrent_program_id = b.concurrent_program_id
AND b.LANGUAGE = 'US'
AND c.user_id = a.requested_by
ORDER BY user_concurrent_program_name;"
Cancel scheduled concurrent Request “Gather Schema Statistics” sqlplus apps/apps
sql>
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I') and concurrent_program_id=38121;
Commit;
Exit
Putting all concurrent jobs on hold:
Update applsys.fnd_concurrent_requests set hold_flag='Y' where phase_code in ('R','P','I');
Cancel scheduled concurrent Request “Gather Schema Statistics”
sqlplus apps/apps
sql>
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I') and concurrent_program_id=38121;
Commit;
Exit
To terminate request from backend
SQL> select REQUEST_ID,ORACLE_ID,ORACLE_PROCESS_ID,ORACLE_SESSION_ID,OS_PROCESS_ID from applsys.FND_CONCURRENT_REQUESTS where REQUEST_ID=577945;
REQUEST_ID ORACLE_ID ORACLE_PROCESS_ID ORACLE_SESSION_ID
---------- ---------- ------------------------------ -----------------
OS_PROCESS_ID
------------------------------------------------------------------------------------------------------------------------------------
577945 900 13348 1242142
13299
SQL> !kill -9 13348
/bin/ksh: kill: 13348: No such process
SQL> update APPLSYS.fnd_Concurrent_requests set PHASE_CODE='C', STATUS_CODE='D' where REQUEST_ID=577945;
1 row updated.
SQL> commit;
Commit complete.
Schedule “Purge Concurrent Request and/or Manager Data”
Cancel existing scheduled request before scheduling new.
# ebappsenv
# sqlplus apps
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I')
and concurrent_program_id=32263;
Parameter:
Entity : REQUEST
Mode : Age
Mode Value : 15 à On non-prod environments as per case# 734780
Schedule:
on specific days : Wednesday and Saturday at 19:00 CET
Run Gather Schema Statistics as a Concurrent request
Cancel any pending jobs for “Gather Schema Statistics”
Sqlplus apps/appspasswd
Sql>
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I') and concurrent_program_id=38121;
Login to Oracle applications of target instance as sysadmin thru Appjump
Select system administrator Responisibility
Verify whether Concurrent Request “Gather Schema Statistics” is running or not
If not running, schedule the request to run immediately with Parameters : ALL,10
To check status fo running requests:
column REQUEST heading 'Request' format a8
column PHASE heading 'Phase' format A8
column STATUS heading 'Status' format A8
column PROGRAM heading 'Program Name' format A40
column SHORT heading 'Short Name' format A15
column REQUESTOR heading 'Requestor' format A10
column START_TIME heading 'Start Time' format A15
column RUN_TIME justify left heading 'Time(m)' format 999999.9
column OSPID heading 'OSPID' format a5
column OS_PIDa heading 'OSPIDA' format a6
column SID heading 'SID' format 99999
column serial# heading 'Serial#' format 99999
select substr(fcrv.request_id,1,8)REQUEST,
decode(fcrv.phase_code,'P','Pending','R','Running','I','Inactive','Completed')PHASE,
decode(fcrv.status_code,
'A','Waiting',
'B','Resuming',
'C','Normal',
'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',fcrv.status_code)STATUS,
substr(fcrv.program,1,40)PROGRAM,substr(fcrv.PROGRAM_SHORT_NAME,1,15)SHORT,
substr(fcrv.requestor,1,15)REQUESTOR,
-- to_char(fcrv.actual_start_date,'MM/DD/RR HH24:MI')START_TIME,
round(((sysdate - fcrv.actual_start_date)*1440),1)RUN_TIME,
substr(fcr.oracle_process_id,1,7)OSPID,s.sid,s.serial#
from apps.fnd_conc_req_summary_v fcrv,
apps.fnd_concurrent_requests fcr,
v$session s,v$process p
where fcrv.phase_code = 'R'
and fcrv.request_id = fcr.request_id
and s.paddr = p.addr
and fcr.oracle_process_id = p.spid
and fcrv.concurrent_program_id not in ('40112','40113','36887')
--and trunc(fcrv.actual_start_date) like trunc(sysdate)
order by PHASE, STATUS, REQUEST desc;
Take export dump of concurrent tables and import
exp userid=applsys/xxxx file=conc.dmp log=exp.log tables=FND_CONCURRENT_QUEUES,FND_CONCURRENT_QUEUES_TL,
FND_CONCURRENT_QUEUE_SIZE, FND_CONCURRENT_QUEUE_CONTENT
Truncate the fnd concurrent tables
SQL> truncate table fnd_Concurrent_queues;
Table truncated.
SQL> truncate table FND_CONCURRENT_QUEUES_TL;
Table truncated.
SQL> truncate table FND_CONCURRENT_QUEUE_SIZE;
Table truncated.
SQL> truncate table FND_CONCURRENT_QUEUE_CONTENT;
Table truncated.
Import the data back into the fnd tables which was exported as part of step above
imp userid=applsys/apps ignore=y file=conc.dmp full=y log=imp.log
Check whether the GSM is Up and Running in the system --> Profile option or using the Following Query :
select DECODE(b.profile_option_value, 'Y', 'Enabled', 'Disabled') DETAILS
from fnd_profile_options a, fnd_profile_option_values b
where a.APPLICATION_ID = b.APPLICATION_ID
and a.PROFILE_OPTION_ID = b.PROFILE_OPTION_ID
and a.PROFILE_OPTION_NAME = 'CONC_GSM_ENABLED';
Check whether the Service Manager is up and Running by the following Query :
select CONCURRENT_QUEUE_NAME, ENABLED_FLAG, MAX_PROCESSES, RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME like 'FNDSM%';
The following SQL scripts located under $FND_TOP/sql are useful when diagnosing concurrent manager problems:
afimchk.sql Tells the status of the ICM and PMON method
afcmstat.sql Lists active manager processes
afrqrun.sql Lists all the running, waiting and Terminating requests
afrqwait.sql Lists requests that are constrained and waiting for the ICM to release them.
afrqscm.sql Prints log file name of managers that can run a given request. It can be used to check for possible errors when a request stays in pending status. It requires a request id value.
afcmcreq.sql Prints the log file name of the manager that processed the request
afrqstat.sql Summary of completed concurrent requests grouped by completion status and execution type. It requires number of days prior to today on which to report parameter.
afimlock.sql Lists locks that the ICM is waiting to get
afcmrrq.sql Lists managers that currently are running a request
Reports on requests that having been running for over a specified amount of time (hard coded as 4 hours). Exceptions, in addition to the defaults below, can be added by entering the program ID for the program exceptions under multi-items.
Runs Every 20 minutes
Uses the following query:
select b.REQUEST_ID, a.DESCRIPTION, b.phase_code,
(sysdate - b.actual_start_date) * 24 "running",
to_char(sysdate, 'mm/dd/yyyy hh:mi') "now",
to_char(b.request_date, 'mm/dd/yyyy hh:mi') "request_date",
to_char(b.actual_start_date, 'mm/dd/yyyy hh:mi') "start_time",
b.program_application_id "program_application_id",
b.concurrent_program_id "concurrent_program_id"
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.STATUS_CODE = 'R'
and b.PHASE_CODE = 'R'
and ((sysdate - b.actual_start_date) * 24) > 4
and a.CONCURRENT_PROGRAM_ID NOT IN(36887,43393,38121,42789,31556)
Excludes:
36887 - Workflow Mailer
43393 - ITM Adapter
38121 - Gather Schema Statistics
42789 - OAM Applications Dashboard Collection
31556 - Planning Manager
Monitors pending jobs exceeds the specified threshold. Excessive pending jobs may indicate an issue with the Concurrent Manager. Uses the following query:
select a.concurrent_program_name, b.REQUEST_ID, a.description,
to_char(b.request_date, 'mm/dd/yyyy hh:mi:ss') ""request_date"",
to_char(b.requested_start_date, 'mm/dd/yyyy hh:mi:ss') ""request_start""
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.PHASE_CODE = 'P'
and b.requested_start_date <= sysdate
The error threshold monitor will alert if the number of failed jobs exceeds the user defined threshold in a 30 minute period.
select a.concurrent_program_name, b.REQUEST_ID, a.description,
b.status_code, b.phase_code
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.STATUS_CODE IN ('E')
and b.actual_completion_date > sysdate - 1/48
Find request which are put on hold
SQL> select REQUEST_ID from fnd_concurrent_requests where phase_code ='P' and hold_flag='Y';
To find oracle_process id for a request id to pull trace file from udump:
select oracle_process_id ,
decode(status_code,'R','Running','D','Canceled','E','Error','X','Terminated','G','Warning','T','Terminating')""Status_code"",
phase_code,to_char(actual_start_date,'DD-MON-YYYY=>hh24:mi:ss') ""Login Time""
from apps.fnd_concurrent_requests where request_id='&Enter_conn_req_id'
To find spid of a request to get the trace file
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
To check failed jobs submitted by an user
set lines 1000 pages 100
clear columns
col "Submitted By" format a15 word_wrap
select user_name "Submitted By", request_id "Request #",
to_char(cr.request_date,'dd-mon-rr hh24:mi') "Submitted on",
to_char(cr.last_update_date,'dd-mon-rr hh24:mi') "Failed on"
from applsys.fnd_concurrent_requests cr,
applsys.fnd_user u
where u.user_id = cr.requested_by
and user_name like '%BATCH%'
and cr.status_code ='E'
and cr.phase_code ='C'
and cr.request_date > sysdate - 1
order by 1
Statement to put the jobs on hold and release them lateron
To hold the requests (as apps user):
1) Drop table apps.str_dba_conc_req_hold ;
2) Create table apps.str_dba_conc_req_hold as select * from fnd_Concurrent_requests where PHASE_CODE='P' and hold_flag='N';
3) select count(*) from apps.str_dba_conc_req_hold ;
4) update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and hold_flag='N' and request_id in (select request_id from apps.str_dba_conc_req_hold) ;
NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same
5) Commit;
To Release these requests in prod after patching, here is the step :
6) a. update fnd_Concurrent_requests set hold_flag='N' where request_id in (select request_id from apps.str_dba_conc_req_hold);
b. commit;
How to take cm program trace.
Responsibility: System Administrator
Navigate: Concurrent > Program > Define
Query Concurrent Program
Select the Enable Trace Checkbox
Responsibility: System Administrator
Navigate: Profiles > System
Query Profile Option Concurrent: Allow Debugging
Set profile to Yes
Logon to the Responsibility that runs the Concurrent Program
In the Submit Request Screen click on Debug Options (B)
Select the Checkbox for SQL Trace
To submit active user request from backend
CONCSUB APPS/APPS SYSADMIN "System Administrator" SYSADMIN CONCURRENT FND FNDSCURS PROGRAM_NAME='"Active Users"'
sql program to submit request from backend
"SET SERVEROUTPUT ON
declare
req_id number;
begin
DBMS_OUTPUT.PUT_LINE('In begin');
fnd_global.APPS_INITIALIZE (0, 21758, 671);
req_id := FND_REQUEST.SUBMIT_REQUEST(application => 'FND',program => 'FNDSCURS',description => '',start_time =>
'',sub_request => FALSE);
if (req_id = 0) then
/* Handle submission error */
DBMS_OUTPUT.PUT_LINE('Request ID :' || req_id);
DBMS_OUTPUT.PUT_LINE('As the request ID is 0, the request was not submitted');
DBMS_OUTPUT.PUT_LINE('Please verify this part again');
else
DBMS_OUTPUT.PUT_LINE('Request ID :' || req_id);
DBMS_OUTPUT.PUT_LINE('Request submitted successfully');
commit;
end if;
end;
/
Meaning of status_code and phase_code in FND_CONCURRENT_REQUESTS table STATUS_CODE Column:
A - Waiting
B - Resuming
C - Normal
D - Cancelled
E - Error
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
PHASE_CODE column
C - Completed
I - Inactive
P - Pending
R - Running
No comments:
Post a Comment