Workflow-scripts

############ To know the node where WF is working

SELECT node_name FROM fnd_concurrent_queues WHERE concurrent_queue_name LIKE 'WFMLRSVC%';

############ To know the Notification mailer parameters

SELECT b.component_name,

c.parameter_name,

a.parameter_value

FROM fnd_svc_comp_param_vals a,

fnd_svc_components b,

fnd_svc_comp_params_b c

WHERE b.component_id = a.component_id

AND b.component_type = c.component_type

AND c.parameter_id = a.parameter_id

AND c.encrypted_flag = 'N'

AND b.component_name LIKE '%Mailer%'

--AND c.parameter_name IN ('OUTBOUND_SERVER', 'REPLYTO')

ORDER BY c.parameter_name;

############ To know the workflow Components status whether are they running or stopped

col COMPONENT_TYPE for a24

col COMPONENT_NAME for a45

col COMPONENT_STATUS for a10

col ERROR for a10

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;

############ To know 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) "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;

***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 ;

############ Query to 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>%';

############ Query to check The Workflow notification has been sent or not

Ø --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

select mail_status, status

from wf_notifications

where notification_id=<notification_id>

############ Query to check whether workflow background Engine is working fine 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

############ Query to check whether 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