Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Thursday, August 25, 2016

WORKFLOW MAILER DEBUGGING SCRIPT FOR DEBUGGING EMAILS ISSUES

This article containts various Workflow and Business Event debugging scripts.

--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,2,3;


--Query to get the log file of active workflow mailer and workflow agent listener Container
--Note All Workflow Agent Components logs will stored in single file ie. container log file.
select fl.meaning,fcp.process_status_code, decode(fcq.concurrent_queue_name,'WFMLRSVC', 'mailer container',
'WFALSNRSVC','listener container',fcq.concurrent_queue_name),
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;



--Linux Shell script Command to get outbound error in Mailer
grep -i '^\[[A-Za-z].*\(in\|out\).*boundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' <logfilename> | tail -10 ;
--Note: All Mailer log files starts with name FNDCPGSC prefix

--Linux Shell script Command to get inbound processing error in Mailer 
grep -i '^\[[A-Za-z].*.*inboundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' <logfilename> | tail -10 ;


---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 The Workflow notification has ben sent or not
select mail_status, status from wf_notifications 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 loggin in application + click on preference + the notification preference 


--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;
References:
http://www.apps2fusion.com/at/gt/tc/328-workflow-mailer-debugging-script-for-debugging-emails-issues

1 comment: