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/