Oracle EBS 12.2: Diagnosing Workflow Mailer Email Storms and Delayed PO Email Approvals — A Read-Only Production RCA Runbook
A common production scenario in Oracle E-Business Suite 12.2: the Workflow Notification Mailer starts bouncing under a flood of notification emails, and users complain that Purchase Orders approved via email take a long time to reflect in EBS. This post is a complete, production-safe, READ-ONLY investigation runbook — every query and OS check needed to find exactly where the email approval is spending its time, before touching anything.
Ground rules: no purges, no updates to Workflow tables, no mailer bounces, no queue cleanup — diagnostics only. Baseline is EBS 12.2.x on Oracle 19c with APPS access and OS access to the Concurrent Manager node.
Architecture recap — why an outbound email storm delays inbound PO approvals
The Notification Mailer is one Java service container (WFMLRSVC / FNDCPGSC) that runs both the
outbound processors (dequeue WF_NOTIFICATION_OUT → SMTP) and the inbound processors
(poll IMAP → enqueue WF_NOTIFICATION_IN). Therefore:
- An outbound storm can saturate the mailer's threads/heap → OutOfMemory / hang → GSM restarts the container ("bouncing").
- Every bounce interrupts IMAP polling → user approval replies sit unread in the INBOX.
- Even after the reply is enqueued to
WF_NOTIFICATION_IN, a separate component — the Workflow Inbound
Notifications Agent Listener — must dequeue it and call the respond API.
- The post-response PO workflow activities may be deferred, i.e. parked on
WF_DEFERRED for the
Workflow Deferred Agent Listener (and, for timed-out/stuck activities, the Workflow Background Process).
So the end-to-end delay can accumulate in four independent places: mailer-outbound, mailer-inbound (IMAP),
WF_NOTIFICATION_IN listener, WF_DEFERRED engine processing. The phases below measure each one.
PHASE 1 — Immediate 5-minute production health check
1.1 Service component status
Purpose: Confirm the four critical components are RUNNING right now.
SELECT fsc.component_id,
fsc.component_name,
fsc.component_status,
fsc.component_type,
fsc.startup_mode,
fcq.concurrent_queue_name AS container
FROM fnd_svc_components fsc,
fnd_concurrent_queues fcq
WHERE fsc.concurrent_queue_id = fcq.concurrent_queue_id (+)
AND fsc.component_type LIKE 'WF_%'
ORDER BY fsc.component_type, fsc.component_name;
Expected normal: Workflow Notification Mailer, Workflow Agent Listener components
(Workflow Deferred Agent Listener, Workflow Deferred Notification Agent Listener,
Workflow Inbound Notifications Agent Listener, Workflow Error Agent Listener, Java listeners)
all RUNNING with startup_mode = AUTOMATIC.
Problem indicators:
| component_status |
Meaning |
Next step |
| STOPPED / DEACTIVATED_USER |
Someone stopped it |
Find who/when in mailer log; do NOT restart yet |
| STOPPED_ERROR |
Crashed after max auto-restarts |
Phase 7 log analysis — this is your smoking gun |
| SUSPENDED |
Manually or schedule-suspended |
Check OAM schedule |
| STARTING (persistently) |
Container thrashing / can't initialize |
Phase 7 — IMAP/SMTP connect errors, OOM |
1.2 Are the service containers themselves alive, and are they bouncing?
Purpose: WFMLRSVC (Mailer container) and WFALSNRSVC (Agent Listener container) restarts show up as
multiple recent process rows. Frequent restarts = "mailer bouncing" confirmed with timestamps.
SELECT q.concurrent_queue_name,
p.concurrent_process_id,
p.process_status_code,
TO_CHAR(p.process_start_date,'DD-MON HH24:MI:SS') AS started,
p.node_name,
p.logfile_name
FROM fnd_concurrent_queues q,
fnd_concurrent_processes p
WHERE q.concurrent_queue_id = p.concurrent_queue_id
AND q.concurrent_queue_name IN ('WFMLRSVC','WFALSNRSVC')
AND p.process_start_date > SYSDATE - 2
ORDER BY q.concurrent_queue_name, p.process_start_date DESC;
Expected normal: exactly one A (Active) row per container, started days/weeks ago.
Problem: several rows in the last 24 h (statuses K/S/T followed by new A rows) = container is
being killed/restarted repeatedly. Save the logfile_name values — they are the Phase 7 inputs.
1.3 30-second queue snapshot
Purpose: Instant view of the two queues that gate email approvals.
SELECT 'WF_NOTIFICATION_OUT' q, msg_state, COUNT(*) cnt,
TO_CHAR(CAST(MIN(enq_time) AS DATE),'DD-MON HH24:MI') oldest
FROM applsys.aq$wf_notification_out GROUP BY msg_state
UNION ALL
SELECT 'WF_NOTIFICATION_IN', msg_state, COUNT(*),
TO_CHAR(CAST(MIN(enq_time) AS DATE),'DD-MON HH24:MI')
FROM applsys.aq$wf_notification_in GROUP BY msg_state
UNION ALL
SELECT 'WF_DEFERRED', msg_state, COUNT(*),
TO_CHAR(CAST(MIN(enq_time) AS DATE),'DD-MON HH24:MI')
FROM applsys.aq$wf_deferred GROUP BY msg_state
UNION ALL
SELECT 'WF_ERROR', msg_state, COUNT(*),
TO_CHAR(CAST(MIN(enq_time) AS DATE),'DD-MON HH24:MI')
FROM applsys.aq$wf_error GROUP BY msg_state;
Expected normal: READY counts low (tens–low hundreds) with oldest READY only minutes old.
Problem: thousands of READY and/or oldest READY hours old. A big count with a young oldest-READY is
throughput pressure, not a stall; a stall is proven by AGE, not count — Phase 2 measures both.
1.4 New-notification arrival rate (storm confirmation, 10 seconds)
SELECT COUNT(*) notifications_last_hour
FROM wf_notifications
WHERE begin_date > SYSDATE - 1/24;
Compare against your known baseline (pull the same hour yesterday/last week in Phase 3). 5–10× baseline = storm.
PHASE 2 — Workflow AQ queue backlog analysis
AQ view column notes (applies to all AQ$<queue_table> views):
msg_state: READY (waiting to be dequeued), WAIT (delay not yet elapsed), PROCESSED (dequeued, retained
until retention time expires), EXPIRED (exceeded max retries → moved to the exception queue).
enq_time/deq_time are TIMESTAMP WITH TIME ZONE — CAST(... AS DATE) before date arithmetic.
PROCESSED rows exist only while queue retention keeps them; treat their absence as "retention=0", not "no throughput".
2.1 WF_NOTIFICATION_IN — inbound approval emails
What it does: the Mailer enqueues every valid inbound email response here; the Workflow Inbound
Notifications Agent Listener dequeues it and executes the response (calls the Respond API → Workflow Engine).
2.1a State summary, oldest/newest, age
SELECT msg_state,
COUNT(*) cnt,
TO_CHAR(CAST(MIN(enq_time) AS DATE),'DD-MON-YYYY HH24:MI:SS') oldest,
TO_CHAR(CAST(MAX(enq_time) AS DATE),'DD-MON-YYYY HH24:MI:SS') newest,
ROUND((SYSDATE - CAST(MIN(enq_time) AS DATE))*24*60,1) oldest_age_min
FROM applsys.aq$wf_notification_in
GROUP BY msg_state
ORDER BY msg_state;
2.1b Expired / retried messages (poison messages)
SELECT msg_state, retry_count, COUNT(*) cnt
FROM applsys.aq$wf_notification_in
GROUP BY msg_state, retry_count
ORDER BY retry_count DESC;
2.1c Arrival vs processing rate per hour (this is the "faster in than out?" answer)
-- Arrivals per hour (enqueue by mailer)
SELECT TO_CHAR(CAST(enq_time AS DATE),'DD-MON HH24') hr, COUNT(*) enqueued
FROM applsys.aq$wf_notification_in
WHERE CAST(enq_time AS DATE) > SYSDATE - 1
GROUP BY TO_CHAR(CAST(enq_time AS DATE),'DD-MON HH24')
ORDER BY 1;
-- Dequeues per hour (inbound listener throughput) — needs retention > 0
SELECT TO_CHAR(CAST(deq_time AS DATE),'DD-MON HH24') hr, COUNT(*) dequeued
FROM applsys.aq$wf_notification_in
WHERE deq_time IS NOT NULL
AND CAST(deq_time AS DATE) > SYSDATE - 1
GROUP BY TO_CHAR(CAST(deq_time AS DATE),'DD-MON HH24')
ORDER BY 1;
| Result pattern |
Interpretation |
| READY ≈ 0, oldest READY < 5 min |
Inbound listener healthy — delay is upstream (IMAP/mailer/mail server) |
| READY large, oldest READY old, dequeues/hr ≈ 0 |
Cause E — Inbound Agent Listener down/stuck (check 1.1, Phase 6 sessions, Phase 7 listener log) |
| READY large but dequeues/hr healthy and enq/hr larger |
Cause D-as-symptom — listener keeping up but flooded; look at what's flooding it |
| enq/hr near zero while users report replying |
Emails never reaching WF_NOTIFICATION_IN — Cause B/C/J (mailer down, IMAP backlog, mail-server delay) → Phase 4/7/10 |
2.2 WF_DEFERRED — deferred Workflow Engine work
What it does: activities the engine defers (cost above threshold) plus most Business Events, including
oracle.apps.wf.notification.send (which stages outbound notifications). Processed by the
Workflow Deferred Agent Listener (and Java counterpart on WF_JAVA_DEFERRED).
SELECT msg_state,
COUNT(*) cnt,
TO_CHAR(CAST(MIN(enq_time) AS DATE),'DD-MON-YYYY HH24:MI:SS') oldest,
TO_CHAR(CAST(MAX(enq_time) AS DATE),'DD-MON-YYYY HH24:MI:SS') newest,
ROUND((SYSDATE - CAST(MIN(enq_time) AS DATE))*24*60,1) oldest_age_min
FROM applsys.aq$wf_deferred
GROUP BY msg_state;
Which events are backlogged (corr_id = event name):
SELECT corr_id, msg_state, COUNT(*) cnt,
TO_CHAR(CAST(MIN(enq_time) AS DATE),'DD-MON HH24:MI') oldest
FROM applsys.aq$wf_deferred
WHERE msg_state IN ('READY','WAIT')
GROUP BY corr_id, msg_state
ORDER BY cnt DESC
FETCH FIRST 25 ROWS ONLY;
Expected normal: READY drains within minutes.
Problem: oldest READY > 30–60 min = Cause F (deferred backlog). If the dominant corr_id is
APPS:oracle.apps.wf.notification.send, the storm is choking notification staging — outbound and
engine progress both suffer. If it's an application event, that names your storm source.
2.3 WF_NOTIFICATION_OUT — outbound emails awaiting SMTP send
What it does: the Mailer's outbound feed. One message per email to be sent. This is where an email
storm physically piles up.
SELECT msg_state,
COUNT(*) cnt,
TO_CHAR(CAST(MIN(enq_time) AS DATE),'DD-MON-YYYY HH24:MI:SS') oldest,
ROUND((SYSDATE - CAST(MIN(enq_time) AS DATE))*24*60,1) oldest_age_min
FROM applsys.aq$wf_notification_out
GROUP BY msg_state;
-- Mailer send throughput per hour (needs retention > 0)
SELECT TO_CHAR(CAST(deq_time AS DATE),'DD-MON HH24') hr, COUNT(*) sent
FROM applsys.aq$wf_notification_out
WHERE deq_time IS NOT NULL
AND CAST(deq_time AS DATE) > SYSDATE - 1
GROUP BY TO_CHAR(CAST(deq_time AS DATE),'DD-MON HH24')
ORDER BY 1;
Problem: tens of thousands READY + mailer restarts in 1.2 = Causes A + B + K interacting.
A WAIT state spike here usually means failed sends waiting for retry (SMTP trouble).
2.4 Other relevant queues
| Queue |
Role |
Why it matters here |
WF_JAVA_DEFERRED |
Java-subscription deferred events |
Java listeners stuck → some notifications never stage |
WF_ERROR / WF_JAVA_ERROR |
Errored activities/events → WFERROR notifications |
An error loop generates emails — classic storm engine |
WF_IN / WF_OUT |
Legacy external agent queues |
Usually idle; backlog = old integrations misbehaving |
WF_CONTROL |
Container control messages (GSM ↔ components) |
Don't judge by counts; messages expire by design |
SELECT 'WF_JAVA_DEFERRED' q, msg_state, COUNT(*) cnt,
ROUND((SYSDATE - CAST(MIN(enq_time) AS DATE))*24*60,1) oldest_age_min
FROM applsys.aq$wf_java_deferred GROUP BY msg_state
UNION ALL
SELECT 'WF_JAVA_ERROR', msg_state, COUNT(*),
ROUND((SYSDATE - CAST(MIN(enq_time) AS DATE))*24*60,1)
FROM applsys.aq$wf_java_error GROUP BY msg_state
UNION ALL
SELECT 'WF_ERROR', msg_state, COUNT(*),
ROUND((SYSDATE - CAST(MIN(enq_time) AS DATE))*24*60,1)
FROM applsys.aq$wf_error GROUP BY msg_state;
Interpretation: large/growing WF_ERROR READY → find the failing item type (Phase 3.4) — an ERROR
retry loop mailing SYSADMIN is one of the most common EBS email storms.
PHASE 3 — Email-volume / notification-storm analysis
All queries here are on WF_NOTIFICATIONS (one row per notification, begin_date = creation time).
Change the window (SYSDATE - 1/24, - 6/24, - 1) to get the 1 h / 6 h / 24 h views.
3.1 STATUS and MAIL_STATUS distribution (last 24 h)
SELECT status, mail_status, COUNT(*) cnt
FROM wf_notifications
WHERE begin_date > SYSDATE - 1
GROUP BY status, mail_status
ORDER BY cnt DESC;
Status meanings — read these before concluding anything:
| Column |
Value |
Meaning |
Caution |
| STATUS |
OPEN |
Awaiting response/close |
Normal for FYI + pending approvals |
| STATUS |
CLOSED |
Responded/closed |
|
| STATUS |
CANCELED |
Canceled (e.g., re-approval reset) |
Mass CANCELED bursts can themselves send "canceled" mails |
| MAIL_STATUS |
MAIL |
Queued for the mailer, email not yet confirmed sent |
Big MAIL count = outbound backlog, says nothing about inbound |
| MAIL_STATUS |
SENT |
Mailer sent the email |
No timestamp of send stored here |
| MAIL_STATUS |
ERROR |
Send failed |
Check mailer log for the SMTP error |
| MAIL_STATUS |
WAIT |
Awaiting retry/more info |
|
| MAIL_STATUS |
INVALID |
Bad/unresolvable address |
Spikes after HR/user changes |
| MAIL_STATUS |
NULL |
No email required (recipient preference, or closed before send) |
NULL/SENT on an approved-but-stuck PO does NOT mean the inbound response arrived — inbound progress is proven only by WF_COMMENTS / activity end_date (Phase 5) |
3.2 Notifications per hour (find the storm start time)
SELECT TO_CHAR(begin_date,'DD-MON HH24') hr, COUNT(*) cnt
FROM wf_notifications
WHERE begin_date > SYSDATE - 2
GROUP BY TO_CHAR(begin_date,'DD-MON HH24')
ORDER BY 1;
Problem indicator: a step-change hour. That timestamp is your correlation anchor for mailer restarts
(1.2), queue growth (Phase 2) and log errors (Phase 7).
3.3 Top generators — by type, message, recipient
-- Top 30 by item type + message (change window: 1/24, 6/24, 1)
SELECT message_type, message_name, COUNT(*) cnt,
MIN(begin_date) first_seen, MAX(begin_date) last_seen
FROM wf_notifications
WHERE begin_date > SYSDATE - 1/24
GROUP BY message_type, message_name
ORDER BY cnt DESC
FETCH FIRST 30 ROWS ONLY;
-- Top 30 recipients (a single role/user drowning in mail = loop or bad routing rule)
SELECT recipient_role, COUNT(*) cnt
FROM wf_notifications
WHERE begin_date > SYSDATE - 1/24
GROUP BY recipient_role
ORDER BY cnt DESC
FETCH FIRST 30 ROWS ONLY;
-- Hour x type matrix for the top offenders
SELECT TO_CHAR(begin_date,'DD-MON HH24') hr, message_type, COUNT(*) cnt
FROM wf_notifications
WHERE begin_date > SYSDATE - 1
GROUP BY TO_CHAR(begin_date,'DD-MON HH24'), message_type
HAVING COUNT(*) > 100
ORDER BY 1, 3 DESC;
Typical storm signatures:
| Signature |
Likely source |
| message_type = WFERROR, recipient SYSADMIN |
Error retry loop (check WF_ERROR queue + the erroring item type) |
| ALR-prefixed / Alert messages |
Oracle Alert firing per-row instead of per-batch |
| Same message_name + same recipient_role every few seconds |
Looping workflow / bad activity transition |
| POAPPRV surge |
PO mass interface/requisition import or approval-hierarchy misconfig |
| Huge counts with STATUS=OPEN forever on FYI messages |
Notifications not being closed → re-send/reminder logic piling up |
3.4 Workflow item creation rate (catches loops even before notifications)
SELECT item_type, COUNT(*) cnt, MIN(begin_date) first_seen
FROM wf_items
WHERE begin_date > SYSDATE - 1
GROUP BY item_type
ORDER BY cnt DESC
FETCH FIRST 20 ROWS ONLY;
3.5 Open notifications and aging
-- Open > 30 minutes, overall and PO-only
SELECT message_type, COUNT(*) cnt,
ROUND((SYSDATE - MIN(begin_date))*24,1) oldest_open_hrs
FROM wf_notifications
WHERE status = 'OPEN'
AND begin_date < SYSDATE - 30/1440
GROUP BY message_type
ORDER BY cnt DESC;
SELECT notification_id, recipient_role, subject, begin_date, mail_status
FROM wf_notifications
WHERE status = 'OPEN'
AND message_type = 'POAPPRV'
AND begin_date < SYSDATE - 30/1440
ORDER BY begin_date
FETCH FIRST 50 ROWS ONLY;
Caution: OPEN + old is normal for approvals humans haven't answered. It only indicates a system problem
when the user says "I already replied" — then trace that specific NID in Phase 5.
PHASE 4 — Workflow Mailer and Agent Listener analysis
4.1 Mailer configuration snapshot (READ-ONLY)
Purpose: capture thread counts, polling frequency, IMAP/SMTP hosts before touching anything.
SELECT c.component_name, p.parameter_name, v.parameter_value
FROM fnd_svc_components c,
fnd_svc_comp_param_vals v,
fnd_svc_comp_params_b p
WHERE c.component_id = v.component_id
AND v.parameter_id = p.parameter_id
AND c.component_type = 'WF_MAILER'
AND p.parameter_name NOT LIKE '%PASSWORD%'
ORDER BY p.parameter_name;
Key parameters to note down:
| Parameter |
Meaning |
Storm relevance |
| PROCESSOR_OUT_THREAD_COUNT |
Outbound sender threads |
1 thread vs storm volume = Cause K |
| PROCESSOR_IN_THREAD_COUNT |
Inbound IMAP processing threads |
0/low = inbound starvation |
| PROCESSOR_READ_TIMEOUT / PROCESSOR_MAX_LOOP_SLEEP |
Polling cadence |
Long sleeps add fixed latency |
| INBOUND_SERVER / ACCOUNT / OUTBOUND_SERVER |
IMAP/SMTP endpoints |
For Phase 10 checks |
| MAX_INVALID_ADDR_LIST_SIZE, EXPUNGE_ON_CLOSE |
Inbox hygiene |
Giant unexpunged INBOX slows IMAP polls |
4.2 Listener/mailer processing evidence in the DB
Throughput per hour was already measured in 2.1c / 2.3 (deq_time). Combine:
| Evidence |
Healthy |
Unhealthy |
| WF_NOTIFICATION_OUT deq/hr vs WF_NOTIFICATIONS created/hr |
Roughly matching |
Created ≫ sent → outbound falling behind (B/K) |
| WF_NOTIFICATION_IN enq/hr vs user replies expected |
Matching |
Near zero → replies not reaching EBS (C/J or mailer down) |
| WF_NOTIFICATION_IN deq lag (deq_time − enq_time) |
Seconds |
See 4.3 |
4.3 Inbound listener latency distribution (the single most useful inbound metric)
SELECT ROUND(AVG((CAST(deq_time AS DATE) - CAST(enq_time AS DATE))*24*60),1) avg_min,
ROUND(MAX((CAST(deq_time AS DATE) - CAST(enq_time AS DATE))*24*60),1) max_min,
COUNT(*) sample
FROM applsys.aq$wf_notification_in
WHERE deq_time IS NOT NULL
AND CAST(enq_time AS DATE) > SYSDATE - 1;
Expected normal: avg well under a minute.
Problem: minutes/hours → the listener (E) or its downstream engine work (F/G/H) is the bottleneck —
Phase 6 tells you which (waiting session vs blocked session vs no session).
PHASE 5 — Trace one delayed PO approval end-to-end
Inputs: PO number (+ org), NID if known, user, approx time of email approval.
⚠ Joins marked [impl-varies] can differ if the PO approval workflow is customized
(custom item type, custom notification, AME). Verify item_type before trusting results.
5.1 PO → workflow item key
SELECT poh.po_header_id, poh.segment1 po_number, poh.org_id,
poh.authorization_status, poh.approved_flag, poh.approved_date,
poh.wf_item_type, poh.wf_item_key,
poh.last_update_date, poh.last_updated_by
FROM po_headers_all poh
WHERE poh.segment1 = '&po_number'
AND poh.org_id = &org_id; -- omit org_id only if segment1 is globally unique
5.2 Full activity history for that item (current + history)
SELECT ias.item_type, ias.item_key,
pa.instance_label activity,
ias.activity_status,
ias.activity_result_code,
ias.notification_id group_id, -- joins wf_notifications.group_id
TO_CHAR(ias.begin_date,'DD-MON HH24:MI:SS') act_begin,
TO_CHAR(ias.end_date, 'DD-MON HH24:MI:SS') act_end,
ias.error_name
FROM wf_item_activity_statuses ias,
wf_process_activities pa
WHERE ias.item_type = 'POAPPRV' -- [impl-varies] custom item types exist
AND ias.item_key = '&wf_item_key'
AND ias.process_activity = pa.instance_id
UNION ALL
SELECT h.item_type, h.item_key, pa.instance_label, h.activity_status,
h.activity_result_code, h.notification_id,
TO_CHAR(h.begin_date,'DD-MON HH24:MI:SS'),
TO_CHAR(h.end_date, 'DD-MON HH24:MI:SS'),
h.error_name
FROM wf_item_activity_statuses_h h,
wf_process_activities pa
WHERE h.item_type = 'POAPPRV'
AND h.item_key = '&wf_item_key'
AND h.process_activity = pa.instance_id
ORDER BY act_begin;
What to look for: the notification activity should show NOTIFIED while waiting, then COMPLETE
with result (e.g., APPROVED) once the response is processed. The gap between the user's email-send time
and act_end of the notification activity IS the system delay you're hunting. A row stuck in
DEFERRED afterwards points to F; ERROR points to I (check error_name + WF_ERROR queue).
5.3 The notification itself + the recorded response
-- Notification (join by GROUP_ID — an activity NID is the group id, not always the row NID)
SELECT n.notification_id, n.group_id, n.recipient_role, n.status, n.mail_status,
TO_CHAR(n.begin_date,'DD-MON HH24:MI:SS') created,
TO_CHAR(n.end_date, 'DD-MON HH24:MI:SS') closed,
n.original_recipient, n.responder, n.subject
FROM wf_notifications n
WHERE n.group_id = &group_id_from_5_2
ORDER BY n.notification_id;
-- Response values captured on the notification
SELECT na.name, na.text_value, na.number_value, na.date_value
FROM wf_notification_attributes na
WHERE na.notification_id = ¬ification_id
AND na.name IN ('RESULT','RESPONDER','#FROM_ROLE'); -- RESULT = APPROVED/REJECTED
-- Response arrival record (12.2 stores responses/actions in WF_COMMENTS)
SELECT wc.notification_id, wc.from_role, wc.to_role, wc.action, wc.action_type,
TO_CHAR(wc.comment_date,'DD-MON HH24:MI:SS') comment_time,
SUBSTR(wc.user_comment,1,200) user_comment
FROM wf_comments wc
WHERE wc.notification_id = ¬ification_id
ORDER BY wc.comment_date;
wf_comments.comment_date for the RESPOND/email action is your best DB-side approximation of "when EBS
processed the reply". Comparing it with the user's mail-client send time isolates the
mail-server + IMAP + mailer-inbound legs (which the DB cannot see) from the DB-side legs (which it can).
5.4 PO approval action record [impl-varies]
SELECT pah.sequence_num, pah.action_code,
TO_CHAR(pah.action_date,'DD-MON HH24:MI:SS') action_time,
pah.employee_id, pah.note
FROM po_action_history pah
WHERE pah.object_id = &po_header_id
AND pah.object_type_code IN ('PO','PA')
ORDER BY pah.sequence_num;
PHASE 6 — Notification response timing model
For one NID, assemble this table. Bold rows are stored in the database; the rest need logs.
| # |
Stage |
Timestamp source |
Stored in DB? |
| 1 |
Notification created |
wf_notifications.begin_date |
Yes |
| 2 |
Staged for mailer |
aq$wf_notification_out.enq_time (while retained) |
Partly |
| 3 |
Email sent (SMTP) |
Mailer log only (mail_status=SENT has no timestamp) |
No |
| 4 |
User clicked Approve / sent reply |
User's mail client / mail-server logs |
No |
| 5 |
Reply landed in WF IMAP inbox |
Mail-server logs / message Received: headers |
No |
| 6 |
Mailer enqueued reply |
aq$wf_notification_in.enq_time |
Yes |
| 7 |
Listener dequeued reply |
aq$wf_notification_in.deq_time (retention>0) |
Yes |
| 8 |
Response recorded |
wf_comments.comment_date (RESPOND) |
Yes |
| 9 |
Notification closed |
wf_notifications.end_date |
Yes |
| 10 |
Notification activity completed |
wf_item_activity_statuses(.._h).end_date |
Yes |
| 11 |
PO approved/updated |
po_action_history.action_date, po_headers_all.approved_date |
Yes |
Delay attribution:
| Large gap between |
Root-cause bucket |
| 4 → 6 |
B / C / J (mailer down or slow IMAP polling, mailbox backlog, corporate mail routing) |
| 6 → 7 |
D / E (WF_NOTIFICATION_IN backlog / inbound listener) |
| 7 → 9/10 |
F / G / H / I (deferred backlog, engine, DB blocking, PO workflow logic) |
| 10 → 11 |
I (post-approval PO activities, doc manager, custom code) |
PHASE 7 — Database session / blocking / performance analysis
7.1 Workflow-related sessions
SELECT s.inst_id, s.sid, s.serial#, s.username, s.status,
s.module, s.action, s.program, s.sql_id, s.event, s.wait_class,
s.seconds_in_wait, s.blocking_session, s.last_call_et,
TO_CHAR(s.logon_time,'DD-MON HH24:MI') logon
FROM gv$session s
WHERE s.username = 'APPS'
AND ( UPPER(s.module) LIKE '%WF%'
OR UPPER(s.module) LIKE '%WORKFLOW%'
OR UPPER(s.action) LIKE '%WF%'
OR UPPER(s.program) LIKE '%FNDSM%'
OR s.module LIKE 'e:FND:cp:%' )
ORDER BY s.blocking_session NULLS LAST, s.last_call_et DESC;
(Single instance: use v$session and drop inst_id.) Mailer/listener JDBC sessions typically show
program = JDBC Thin Client with WF modules/actions.
Expected normal: mostly INACTIVE (idle between polls) or short ACTIVE bursts;
waits like AQ: ... idle waits are fine.
Problem: ACTIVE with high last_call_et, non-idle waits (enq: TX - row lock contention,
buffer busy waits, db file sequential read storms), or a populated blocking_session.
7.2 Blocking tree and locked WF/PO objects
-- Who blocks whom
SELECT LPAD(' ',2*(LEVEL-1)) || s.sid blocked_tree, s.serial#, s.username,
s.event, s.sql_id, s.seconds_in_wait, s.module
FROM gv$session s
WHERE LEVEL > 1 OR EXISTS
(SELECT 1 FROM gv$session x WHERE x.blocking_session = s.sid)
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;
-- Locks held on WF_/PO_ tables
SELECT o.owner, o.object_name, lo.session_id, lo.oracle_username,
lo.locked_mode, s.module, s.event
FROM v$locked_object lo, dba_objects o, v$session s
WHERE lo.object_id = o.object_id
AND s.sid = lo.session_id
AND (o.object_name LIKE 'WF\_%' ESCAPE '\' OR o.object_name LIKE 'PO\_%' ESCAPE '\');
Problem: row-lock contention on WF_NOTIFICATIONS / WF_ITEM_ACTIVITY_STATUSES /
PO_HEADERS_ALL = Cause H. Note the blocker's module — a stuck form/user session or a batch job
holding a PO row will serialize every approval behind it. Do not kill anything; record SID/SQL_ID.
7.3 What SQL the listener/engine is grinding on
SELECT sql_id, executions, ROUND(elapsed_time/1e6/NULLIF(executions,0),3) sec_per_exec,
buffer_gets, disk_reads, SUBSTR(sql_text,1,120) sql_text
FROM v$sqlarea
WHERE (UPPER(sql_text) LIKE '%WF_NOTIFICATION%' OR UPPER(sql_text) LIKE '%WF_ITEM_ACTIVITY%')
AND parsing_schema_name = 'APPS'
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;
High sec_per_exec on WF queries often points at bloated WF tables/queues (millions of never-purged
rows) degrading every dequeue/update — a capacity finding for the post-RCA remediation list, not for now.
PHASE 8 — Concurrent processing checks
SELECT r.request_id, t.user_concurrent_program_name prog, r.phase_code, r.status_code,
TO_CHAR(r.actual_start_date,'DD-MON HH24:MI') started,
TO_CHAR(r.actual_completion_date,'DD-MON HH24:MI') ended,
ROUND((NVL(r.actual_completion_date,SYSDATE)-r.actual_start_date)*24*60) run_min,
r.argument_text
FROM fnd_concurrent_requests r,
fnd_concurrent_programs_tl t
WHERE r.concurrent_program_id = t.concurrent_program_id
AND r.program_application_id = t.application_id
AND t.language = 'US'
AND t.user_concurrent_program_name LIKE 'Workflow%'
AND r.requested_start_date > SYSDATE - 1
ORDER BY r.actual_start_date DESC;
Role clarification — Workflow Background Process (FNDWFBG):
It is NOT in the email-response path. Inbound responses are processed online by the Inbound
Notifications Agent Listener → Workflow Engine. FNDWFBG matters only indirectly:
- it processes deferred activities (if scheduled with deferred=Y) — so if the PO workflow defers
activities after the response, a missing/slow FNDWFBG (or Deferred Agent Listener) delays the final PO
status update;
- it processes timed-out and stuck items — relevant to cleanup, not to response latency.
Problem indicators: FNDWFBG not scheduled at all, erroring, or running for hours (a symptom of
WF_DEFERRED bloat / storm volume, i.e., evidence for A/F, not a cause to fix by itself).
PHASE 9 — Workflow Mailer / Agent Listener log analysis
9.1 Which logs
| Component |
Log |
Location |
| Notification Mailer (WFMLRSVC container) |
FNDCPGSC<pid>.txt |
$APPLCSF/$APPLLOG on the CM node (exact path = logfile_name from query 1.2) |
| Agent Listener service (WFALSNRSVC container) |
FNDCPGSC<pid>.txt (separate pid) |
same |
| GSM / Service Manager |
FNDSM* / ICM log |
$APPLCSF/$APPLLOG |
| DB-side AQ/WF errors |
alert log + FND_LOG_MESSAGES (if AFLOG enabled) |
DB node / query below |
-- If FND logging was on for WF (module 'wf.%'):
SELECT TO_CHAR(timestamp,'DD-MON HH24:MI:SS') ts, module, SUBSTR(message_text,1,200) msg
FROM fnd_log_messages
WHERE module LIKE 'wf%'
AND timestamp > SYSDATE - 1
ORDER BY timestamp DESC FETCH FIRST 200 ROWS ONLY;
9.2 OS-level search (READ-ONLY)
cd $APPLCSF/$APPLLOG # CM node from 1.2
# Errors and exceptions in mailer/listener container logs, last-modified first
ls -lt FNDCPGSC*.txt | head
egrep -in 'ORA-|WFMAIL|SQLException|OutOfMemory|Exception' FNDCPGSC<mailer_pid>.txt | tail -100
egrep -in 'imap|smtp|socket|timeout|authentication|unable to connect|connection re(set|fused)' \
FNDCPGSC<mailer_pid>.txt | tail -100
# Inbound processing + a specific notification id (NIDs appear as e.g. 2846290/1)
egrep -in 'inbound|processing message|moved message|discard|unsolicited' FNDCPGSC<mailer_pid>.txt | tail -100
grep -in '&NID' FNDCPGSC<mailer_pid>.txt
# Restart/bounce evidence
egrep -in 'shutting down|shutdown|starting|started|deactivat|restart' FNDCPGSC<mailer_pid>.txt
egrep -in 'WFMLRSVC|Workflow Mailer' $APPLCSF/$APPLLOG/FNDSM*.txt | tail -50
9.3 Correlating a delayed approval with the logs
- From Phase 5/6 take: user reply time (T4),
enq_time into WF_NOTIFICATION_IN (T6).
- In the mailer log between T4 and T6, find the IMAP poll cycles: long silent gaps = mailer down or sleeping;
repeated
unable to connect/auth errors = IMAP problem; "processing message"/NID lines show exactly
when the reply was read.
- If the log shows the reply read at T4+minutes but
enq_time is much later → DB-side enqueue stall
(rare; check Phase 7 waits at that time).
- If the log shows nothing until long after T4 and the container restarted repeatedly (9.2 restart grep)
→ the bounce loop is the inbound delay: every restart re-initializes IMAP and re-scans the inbox —
with a storm-bloated inbox each scan is slow, compounding the loop.
If log level is too low to see message-level lines, the increase (Log Level = STATEMENT via OAM) is a
config change — park it for the remediation phase, don't do it mid-RCA unless approved.
PHASE 10 — Mailbox / IMAP stage isolation
| Leg |
How to test (read-only) |
Delay here means |
| User → corporate mail server |
Received: headers of the reply message (mail team pulls one sample) |
J — client/relay delay, not EBS |
| Corporate server → WF IMAP inbox |
Mail-server delivery logs for the WF account; header timestamps |
J/C |
| WF INBOX → Mailer read |
Mailbox counts (below) + mailer log poll cycle |
B/C/K |
| Mailer → WF_NOTIFICATION_IN |
mailer log "processing" line vs enq_time |
rare; DB waits |
| WF_NOTIFICATION_IN → Listener |
4.3 latency query |
D/E |
| Listener → Engine → PO |
Phase 5/6 gaps 7→11 |
F/G/H/I |
Mailbox counts (ask mail team, or view the WF account mailbox read-only):
message counts and oldest-message age in INBOX, PROCESS, and DISCARD folders.
| Observation |
Interpretation |
| INBOX piling up, PROCESS moving |
Mailer reading slower than arrival (threads/capacity — K) |
| INBOX piling up, PROCESS static |
Mailer not polling at all (down/bouncing — B) |
| Thousands of old messages never expunged |
Every poll rescans them → slow polls; also check DISCARD growth from auto-replies/bounces |
| Auto-reply/out-of-office storm to the WF account |
Mail loop: outbound storm → OOO replies → inbound flood — A feeding C/D |
PHASE 11 — Root-cause decision matrix
Correlate — never classify from one number:
| # |
Evidence combination |
Classification |
| 1 |
3.2 step-change + one dominant generator in 3.3 |
A — email storm (name the item/message) |
| 2 |
NOTIFICATION_OUT huge/old + mailer OOM/restarts (1.2, 9.2) |
B (+ K if threads minimal) |
| 3 |
INBOX old messages + PROCESS static + mailer log gaps |
C (driven by B) |
| 4 |
NOTIFICATION_IN READY old + listener RUNNING + slow deq |
D/E — listener capacity or stuck session (7.1) |
| 5 |
4.3 latency high + listener STOPPED_ERROR |
E |
| 6 |
WF_DEFERRED oldest READY hours + 5.2 rows DEFERRED after respond |
F |
| 7 |
Sessions ACTIVE, non-idle waits, no blockers, all queues aging |
G (engine/DB throughput) |
| 8 |
blocking_session populated, TX locks on WF/PO tables |
H |
| 9 |
5.2 shows response processed fast but PO activities slow/ERROR |
I |
| 10 |
Header timestamps show delay before WF inbox |
J |
| 11 |
Thread counts=1, inbox never expunged, undersized JVM in log |
K |
Most common storyline matching your symptoms: A → B (+K) → C → intermittent D. The storm floods
WF_NOTIFICATION_OUT, the mailer JVM thrashes and GSM bounces it, IMAP polling stops during every bounce,
approval replies age in the INBOX, and each restart's inbox rescan is slower because of the storm's own
bounce-backs. The inbound listener and engine are often healthy — verify with 4.3 before blaming them.
Final summary table (fill during execution)
| Check |
Current Evidence |
Normal |
Problem Indicator |
Likely Cause |
Next Action |
| Component status (1.1) |
|
All RUNNING |
STOPPED_ERROR / thrashing |
B/E/K |
Phase 9 logs |
| Container restarts (1.2) |
|
1 old Active row |
Multiple starts <24 h |
B |
Phase 9 restart grep |
| WF_NOTIFICATION_OUT (2.3) |
|
READY low, young |
Huge + old |
A/B/K |
3.3 top generators |
| WF_NOTIFICATION_IN age (2.1) |
|
oldest READY < 5 min |
Hours old |
D/E |
4.3 + 7.1 |
| IN latency deq−enq (4.3) |
|
< 1 min avg |
Minutes+ |
E/G/H |
7.1/7.2 |
| WF_DEFERRED age (2.2) |
|
Drains in minutes |
Hours old |
F |
corr_id breakdown |
| WF_ERROR growth (2.4) |
|
Stable |
Growing fast |
A (error loop) |
Failing item type |
| Notifications/hr (3.2) |
|
Baseline |
Step change |
A |
Storm start anchor |
| Top generator (3.3) |
|
Spread |
One dominant |
A |
Owner of that workflow |
| Traced PO gap 4→6 (Ph.6) |
|
< 2–3 min |
Large |
B/C/J |
Mail logs / Phase 10 |
| Traced PO gap 6→7 |
|
Seconds |
Large |
D/E |
Listener |
| Traced PO gap 7→11 |
|
Seconds–min |
Large |
F/G/H/I |
5.2 statuses + 7.2 |
| Blocking (7.2) |
|
None |
TX locks on WF/PO |
H |
Record blocker |
| FNDWFBG (Ph.8) |
|
Scheduled, minutes |
Missing/erroring/hours |
F symptom |
After storm fixed |
End of runbook. Corrective actions (mailer tuning, queue drain strategy, storm-source fix, inbox hygiene)
to be designed only after the matrix above is filled in and the cause classified.
Disclaimer: run all queries with a read-only mindset on production; validate object names against your patch level. Corrective actions (mailer tuning, storm-source fixes, queue drain strategy, inbox hygiene) belong to a separate remediation phase — only after the root cause is classified.
