Oracle E-Business ( EBS ) Workflow Troubleshoot SQL and Commands

WF Mailer Troubleshoot SQL and Commands

Hi

I would like to share very useful SQLs and commands, I use for every Oracle Workflow troubleshootings. It is very helpful and I hope it will be for you

—-Find Log File of WF Mailer

select fl.meaning,fcp.process_status_code, decode(fcq.concurrent_queue_name,'WFMLRSVC', 'mailer container',Outbound 

'WFALSNRSVC','listener container',fcq.concurrent_queue_name) as "Container",

fcp.concurrent_process_id,os_process_id, fcp.logfile_name

from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl

where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'

and fl.lookup_type='CP_PROCESS_STATUS_CODE' and fl.lookup_code=fcp.process_status_code

and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')

order by fcp.logfile_name;

–Find Outbound Errors in Log

grep -i '^\[[A-Za-z].*\(in\|out\).*boundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' <logfilename> | tail -10 ;

–Find Inbound Errors in Log

grep -i '^\[[A-Za-z].*.*inboundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' <logfilename> | tail -10 ;

–Checking workflow Components status wheather are they running or stopped.

SELECT component_type, component_name,Component_status,COMPONENT_STATUS_INFO Error FROM fnd_svc_components WHERE component_type LIKE 'WF%' ORDER BY 1 DESC;

–Check The Workflow notification has ben sent or not

SELECT notification_id, MESSAGE_TYPE, message_name, recipient_role, status, mail_status,

begin_date,from_user,to_user FROM wf_notifications where mail_status='MAIL' ORDER BY begin_date DESC

-where notification_id=<notification_id>

 –If mail_status is MAIL , it means the email delivery is pending for workflow mailer to send the notification

–If mail_status is SENT, its means mailer has sent email

–If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is “Don’t send email”

–Notification preference of user can be set by user by login in application + click on preference + the notification preference

— Check WF NTF Mailer Credentials is using by another Instance

select c.component_id, c.component_name, p.parameter_id, p.parameter_name, v.parameter_value value

from fnd_svc_comp_param_vals_v v, fnd_svc_comp_params_b p, fnd_svc_components c

where c.component_type = 'WF_MAILER' and v.component_id = c.component_id and v.parameter_id = p.parameter_id and p.parameter_name in ( 'INBOUND_SERVER','ACCOUNT', 'REPLYTO')

order by c.component_id, c.component_name,p.parameter_name;

—-Find NID for Debug SQL — $FND_TOP/sql/wfmlrdbg.sql

select mail_status, status from wf_notifications where notification_id='63754374'


sqlplus apps/password @$FND_TOP/sql/wfmlrdbg.sql 63754374

–Check Space Value in Email Adresses

select name, '['||substr(email_address, 1, 25)||']', orig_system from wf_local_roles where email_address like '% %';

—Query to Check Workflow Mailer Backlog

–State=Ready implies that emails are not being sent & Waiting mailer to sent emails

select tab.msg_state, count(*) from applsys.aq$wf_notification_out tab group by tab.msg_state ;

–Check any particular Alert Message email has be pending by Mailer

select decode(wno.state,

0, '0 = Pending in mailer queue',

1, '1 = Pending in mailer queue',

2, '2 = Sent by mailer on '||to_char(DEQ_TIME),

3, '3 = Exception', 4,'4 = Wait', to_char(state)) State,

to_char(DEQ_TIME),

wno.user_data.TEXT_VC

from wf_notification_out wno

where corrid='APPS:ALR'

and upper(wno.user_data.TEXT_VC) like '%<Subject of Alert Email>%';

–Check Wheather workflow background Engine is workfing for given workflow or not in last 2 days

— Note: Workflow Deferred activities are run by workflow background engine.

select a.argument1,a.phase_code, a.status_code ,a.actual_start_date,a.* from fnd_concurrent_requests a

where CONCURRENT_PROGRAM_ID =

(select concurrent_program_id from fnd_concurrent_programs where

CONCURRENT_PROGRAM_NAME='FNDWFBG')

and last_update_Date>sysdate-2 and argument1='<Workflow Item Type>'

order by last_update_date desc

— Check wheather any business event is pending to process

— ie. Query to get event status & parameters value of particular event in wf_deferred table.

select wd.user_Data.event_name,wd.user_Data.event_key,

rank() over ( partition by wd.user_Data.event_name, wd.user_Data.event_key order by n.name) as serial_no,

n.NAME Parameter_name, N.value Parameter_value ,

decode(state, 0, '0 = Ready', 1, '1 = Delayed', 2, '2 = Retained',

3, '3 = Exception', 4,'4 = Wait', to_char(state)) state,

wd.user_Data.SEND_DATE,

wd.user_Data.ERROR_MESSAGE,

wd.user_Data.ERROR_STACK,

wd.msgid,wd.delay

from WF_DEFERRED wd , TABLE(wd.user_Data.PARAMETER_LIST) n

where lower(wd.user_data.event_name)='<event Name >'

order by wd.user_Data.event_name, wd.user_Data.event_key, n.name

–Component Configuration in Background

sqlplus apps/password @$FND_TOP/sql/afsvcpup.sql

Please read our beginner tutorial if you want to start to learn Oracle E-Business

https://ittutorial.org/oracle-e-business-suite-ebs-tutorials-for-junior-oracle-apps-dba/