# Oracle EBS Workflow Notifications and Errored Activity Query Generator

> Source: https://netray.co/tools/oracle-ebs-workflow-error-and-notification-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generate the five queries and the PL/SQL retry block a workflow administrator needs: errored activities, open notifications, mailer backlog, deferred queue and retry.

## Category

developer

## Erp

Oracle E-Business Suite

## Audience

- Oracle EBS developer
- Apps DBA
- Workflow administrator
- ERP support analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Item type (internal name)

**Key:** item_type

**Type:** text

**Default:** OEOL

**Help:** For example POAPPRV (PO approval), REQAPPRV (requisition approval), OEOH (order header), OEOL (order line), WFERROR (error process).

#### Item key (optional for queries, required for retry)

**Key:** item_key

**Type:** text

**Placeholder:** e.g. 1234567

#### Recipient role (user name)

**Key:** role

**Type:** text

**Default:** SYSADMIN

#### Days back

**Key:** days

**Type:** number

**Default:** 7

#### Activity to retry (PROCESS:LABEL)

**Key:** activity

**Type:** text

**Default:** PROCESS_NAME:INSTANCE_LABEL

**Help:** Copy RETRY_ACTIVITY from query 1.

**Blocks:**

#### 1. Errored activities for the item type

**Body:** SELECT ias.item_key,
       pa.process_name,
       pa.instance_label,
       pa.activity_name,
       ias.activity_status,
       ias.error_name,
       SUBSTR(ias.error_message, 1, 400) AS error_message,
       ias.begin_date,
       pa.process_name || ':' || pa.instance_label AS retry_activity
FROM   wf_item_activity_statuses ias
JOIN   wf_process_activities pa
       ON pa.instance_id = ias.process_activity
WHERE  ias.item_type = '{{item_type}}'
AND    ias.activity_status = 'ERROR'
AND    ias.item_key = NVL('{{item_key}}', ias.item_key)
AND    ias.begin_date >= SYSDATE - {{days}}
ORDER  BY ias.begin_date DESC;

#### 2. Open notifications for a user or role

**Body:** SELECT n.notification_id,
       n.message_type,
       n.message_name,
       n.subject,
       n.begin_date,
       n.mail_status,
       n.original_recipient
FROM   wf_notifications n
WHERE  n.recipient_role = '{{role}}'
AND    n.status = 'OPEN'
AND    n.begin_date >= SYSDATE - {{days}}
ORDER  BY n.begin_date DESC;

#### 3. Notification Mailer backlog

**Body:** SELECT n.mail_status,
       COUNT(*) AS notifications
FROM   wf_notifications n
WHERE  n.status = 'OPEN'
AND    n.mail_status IN ('MAIL', 'FAILED', 'ERROR')
GROUP  BY n.mail_status
ORDER  BY 2 DESC;

#### 4. Deferred activity backlog (Workflow Background Process)

**Body:** -- WF_DEFERRED is an Advanced Queuing queue table; STATE 0 = ready, 1 = waiting (delayed), 2 = processed, 3 = expired
SELECT q.corrid,
       q.state,
       COUNT(*) AS messages,
       MIN(q.enq_time) AS oldest_enqueued
FROM   applsys.wf_deferred q
GROUP  BY q.corrid, q.state
ORDER  BY 3 DESC;

#### 5. Check status and retry one errored activity

**Body:** SET SERVEROUTPUT ON
DECLARE
  l_status VARCHAR2(30);
  l_result VARCHAR2(240);
BEGIN
  wf_engine.itemstatus(itemtype => '{{item_type}}',
                       itemkey  => '{{item_key}}',
                       status   => l_status,
                       result   => l_result);
  DBMS_OUTPUT.PUT_LINE('Before retry: ' || l_status || ' / ' || l_result);

  wf_engine.handleerror(itemtype => '{{item_type}}',
                        itemkey  => '{{item_key}}',
                        activity => '{{activity}}',
                        command  => 'RETRY',
                        result   => NULL);
  COMMIT;

  wf_engine.itemstatus(itemtype => '{{item_type}}',
                       itemkey  => '{{item_key}}',
                       status   => l_status,
                       result   => l_result);
  DBMS_OUTPUT.PUT_LINE('After retry: ' || l_status || ' / ' || l_result);
END;
/

**Notes:** Queries are read-only; block 5 changes workflow state, so run it in a test instance first and only after fixing the root cause of the error. Verify the WF_ENGINE signatures in the Oracle Workflow API Reference for your release, and that your item type uses the internal names shown in Workflow Builder. For order lines, prefer the Progress Order action or the Retry Activities in Error flow where your process allows it.

## Content

**Intro:** This generator produces the queries an Oracle EBS 12.1 or 12.2 workflow administrator runs every week: errored activities for an item type, open notifications for a user, the Notification Mailer backlog and the deferred queue. It also writes a PL/SQL block that checks an item's status and retries one errored activity through WF_ENGINE.

**Sections:**

#### How to use it

**Body:** Set the item type internal name, an optional item key, the recipient role and how many days back to look. Run query 1 first: it lists every activity in ERROR status with the error name and message, and builds the RETRY_ACTIVITY string in the PROCESS_NAME:INSTANCE_LABEL form that WF_ENGINE.HANDLEERROR expects. Paste that value and the item key into the fields, review the root cause, then run block 5. Queries 2 to 4 are health checks: a growing MAIL count means the mailer is not sending, FAILED or ERROR rows point to bad addresses or mailer errors, and a large ready count in WF_DEFERRED means the Workflow Background Process is not scheduled for that item type.

#### Where this lives in Oracle EBS

**Body:** Runtime state is in WF_ITEMS (one row per item type and key), WF_ITEM_ACTIVITY_STATUSES (current status of each activity instance, with history in WF_ITEM_ACTIVITY_STATUSES_H) and WF_PROCESS_ACTIVITIES (the design-time map from INSTANCE_ID to process name, activity name and instance label). Notifications are in WF_NOTIFICATIONS, keyed by NOTIFICATION_ID, with RECIPIENT_ROLE, STATUS (OPEN, CLOSED, CANCELED) and MAIL_STATUS. Deferred activities are messages in the APPLSYS.WF_DEFERRED queue table, picked up by the Workflow Background Process concurrent program. In the application, the same data is visible through Workflow Administrator Web Applications responsibility > Status Monitor, which also lets you retry, skip or rewind an activity.

#### Label versus name, and other gotchas

**Body:** HANDLEERROR takes the activity label, not the activity name. Most activities have a label equal to the name, but a process that uses the same function twice gets labels such as APPROVE_PO and APPROVE_PO-1, so always take INSTANCE_LABEL from WF_PROCESS_ACTIVITIES. Prefix it with the process name when the label is not unique across subprocesses. RETRY re-executes the activity, so if the error came from data (a closed period, a missing account, an invalid approver) fix the data first or the activity errors again. SKIP with a result is the alternative when you have already completed the step manually. Always COMMIT after the call; the engine does not commit for you.

**Bullets:**

- Deferred steps only move when the Workflow Background Process runs with Process Deferred = Yes for that item type.
- Process Timeout and Process Stuck are separate parameters of the same program.
- Purge Obsolete Workflow Runtime Data keeps these tables small; run it for closed items.

#### How to check the result

**Body:** After the retry, block 5 prints the item status again. ACTIVE means the engine moved on; COMPLETE means the process ended; ERROR means the same or a new activity failed, so rerun query 1 for that item key. For notifications, a retried approval usually creates a new WF_NOTIFICATIONS row for the next approver; query 2 with that approver's user name confirms it was sent, and MAIL_STATUS moves from MAIL to SENT once the mailer delivers it. If the item belongs to an order line, Sales Orders > Actions > Progress Order should show the next eligible activity.

**Cta:** If your support team wants to ask which approvals are stuck and why in plain English, answered from your EBS workflow tables on your own servers, we can set that up.

**Faq:**

#### Which API retries an errored workflow activity in Oracle EBS?

**Answer:** WF_ENGINE.HANDLEERROR(itemtype, itemkey, activity, command, result) with command 'RETRY' re-executes the activity; 'SKIP' with a result code skips it. The activity argument is the activity label, optionally prefixed with the process name as PROCESS:LABEL. Commit after the call. For bulk retries, the standard Retry Errored Workflow Activities concurrent program does the same for an item type.

#### How do I find the workflow for a notification ID?

**Answer:** Query WF_NOTIFICATIONS by NOTIFICATION_ID to get MESSAGE_TYPE, RECIPIENT_ROLE and STATUS, then join WF_ITEM_ACTIVITY_STATUSES on NOTIFICATION_ID to find the ITEM_TYPE and ITEM_KEY that sent it. With the item key you can open the item in Status Monitor or run query 1 of this tool for the full activity history.

#### Why are workflow notifications not being emailed?

**Answer:** Check query 3. A rising count with MAIL_STATUS = 'MAIL' means the Workflow Notification Mailer is down, not started or cannot reach the SMTP or IMAP server; check it in Oracle Applications Manager > Workflow Manager. FAILED or ERROR rows usually point to invalid email addresses or an excluded notification preference for that role.

#### What does the Workflow Background Process do?

**Answer:** It processes activities that were deferred (cost above the engine threshold or deferred by design), timed-out notifications and, optionally, stuck processes for a given item type. Order Management, Purchasing and many other products rely on it, so it is usually scheduled every few minutes for OEOL and OEOH. Query 4 shows the deferred queue it works from.

## Search terms

oracle ebs workflow notifications query, oracle ebs workflow query, workflow notification id in oracle ebs 12.2, api to retry activity oracle ebs, wf_engine.handleerror retry, wf_item_activity_statuses error query, wf_deferred backlog query, oracle ebs workflow interview questions

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json