EBS WF Background Process SQL

Monitor the progress on what is happening with the WF Background Processes by running these:

1. Show what is exactly on the Background Process Deferred Queue ready for processing

select w.user_data.itemtype "Item Type", w.user_data.itemkey "Item Key",
decode(w.state, 0, '0 = Ready',
1, '1 = Delayed',
2, '2 = Retained',
3, '3 = Exception',
to_char(w.state)) State,
w.priority, w.ENQ_TIME, w.DEQ_TIME, w.msgid
from wf_deferred_table_m w
where w.user_data.itemtype = '&item_type';

2. Monitor the Queue by Item Type ie APPS<itemtype>

select corrid, user_data user_data
from wf_deferred_table_m
where state = 0
and corrid = '&Corrid'
order by priority, enq_time; 

3. Monitor the Deferred Queue to see exactly what's coming off next

SELECT wfdtm.corrid, wfdtm.user_data.ITEMTYPE ITEM_TYPE,
wfdtm.user_data.ITEMKEY ITEM_KEY, wfdtm.enq_time,
DECODE(wfdtm.state,
0, '0 = Ready',
1, '1 = Delayed',
2, '2 = Retained',
3, '3 = Exception',
TO_CHAR(SUBSTR(wfdtm.state,1,12))) State
FROM wf_deferred_table_m wfdtm
WHERE wfdtm.state = 0
ORDER BY wfdtm.priority, wfdtm.enq_time; 

4. Show what frequency the FNDWFBG request is going to be resubmitted and its parameters:

select r.REQUEST_ID, r.REQUESTED_BY, r.PHASE_CODE, p.USER_CONCURRENT_PROGRAM_NAME, r.ARGUMENT_TEXT "Arguments",
Nvl(Substr(R.Argument_Text,0,Instr(R.Argument_Text,',')-1),'All Items') Item_Type,
Substr(R.Argument_Text,Instr(R.Argument_Text,',')+1,Instr(R.Argument_Text,',',1,2)-Instr(R.Argument_Text,',')-1) Min_Threshold,
Substr(R.Argument_Text,Instr(R.Argument_Text,',',1,2)+1,(Instr(R.Argument_Text,',',1,3)-1)-(Instr(R.Argument_Text,',',1,2))) Max_Threshold,
Substr(R.Argument_Text,Instr(R.Argument_Text,',',1,3)+1,(Instr(R.Argument_Text,',',1,4)-1)-(Instr(R.Argument_Text,',',1,3))) Deferred,
substr(r.ARGUMENT_TEXT,instr(r.ARGUMENT_TEXT,',',1,4)+1,(instr(r.ARGUMENT_TEXT,',',1,5)-1)-(instr(r.ARGUMENT_TEXT,',',1,4))) TIMEOUT,
substr(r.ARGUMENT_TEXT,instr(r.ARGUMENT_TEXT,',',1,5)+1) STUCK,
r.RESUBMIT_INTERVAL EVERY, r.RESUBMIT_INTERVAL_UNIT_CODE SO_OFTEN, r.RESUBMIT_END_DATE
FROM fnd_concurrent_requests r, FND_CONCURRENT_PROGRAMS_TL p
WHERE r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
and p.USER_CONCURRENT_PROGRAM_NAME LIKE 'Workflow%Background%'
AND p.LANGUAGE = 'US'
and r.ACTUAL_COMPLETION_DATE is null
and r.PHASE_CODE in ('P','R');

 

Output Post Processor (OPP) Fails

 R12 E-Business Suite Output Post Processor (OPP) Fails To Pick Up Concurrent Requests With Error 'Unable to find an Output Post Processor service to post-process request nnnnn' (Doc ID 2215371.1)

 

SELECT fcp.concurrent_process_id  FROM apps.fnd_concurrent_queues fcq, apps.fnd_concurrent_processes fcp  WHERE concurrent_queue_name = 'FNDCPOPP'AND fcq.concurrent_queue_id = fcp.concurrent_queue_idAND fcq.application_id = fcp.queue_application_id AND fcp.process_status_code = 'A'

Oracle EBS workflow sql handy for your job

select w.user_data.itemtype "Item Type", w.enq_time,w.user_data.itemkey "Item Key"  from apps.wf_deferred_table_m w order by w.enq_time desc

SELECT component_name, component_status FROM apps.fnd_svc_componentsWHERE component_type = 'WF_MAILER';

select message_type, count(1) from apps.wf_notifications where status='OPEN' and mail_status='MAIL' group by message_type;

select running_processes from apps.fnd_concurrent_queues where concurrent_queue_name = 'WFMLRSVC';

select component_status from apps.fnd_svc_components where component_id = (select component_id from apps.fnd_svc_components where component_name = 'Workflow Notification Mailer');

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.phase_code = 'R'

select fcq.USER_CONCURRENT_QUEUE_NAME Container_Name, DECODE(fcp.OS_PROCESS_ID,NULL,'Not Running',fcp.OS_PROCESS_ID) PROCID, fcq.MAX_PROCESSES TARGET, fcq.RUNNING_PROCESSES ACTUAL, fcq.ENABLED_FLAG ENABLED, fsc.COMPONENT_NAME, fsc.STARTUP_MODE, fsc.COMPONENT_STATUS from APPS.FND_CONCURRENT_QUEUES_VL fcq, APPS.FND_CP_SERVICES fcs, APPS.FND_CONCURRENT_PROCESSES fcp, apps.fnd_svc_components fsc

where fcq.MANAGER_TYPE = fcs.SERVICE_ID and fcs.SERVICE_HANDLE = 'FNDCPGSC' and fsc.COMPONENT_STATUS = 'RUNNING' and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+) and fcq.concurrent_queue_id = fcp.concurrent_queue_id(+) and fcq.application_id = fcp.queue_application_id(+) and fcp.process_status_code(+) = 'A'order by fcp.OS_PROCESS_ID, fsc.STARTUP_MODE