Developer ToolsFree generatorPDF, Excel, Word exportSAP Business OneAll SAP Business One tools

SAP Business One Approval Query Generator: Pending Approvals from OWDD and WDD1

This generator writes a SQL Server or HANA query that lists SAP Business One approval requests by approver decision, document type and age, joined to the draft, the template and the stage. A second query documents the templates, stages and authorizers so you can see why a document is waiting on someone.

Fill in

Approver decision status

W pending, Y approved, N not approved.

Document object type

17 sales order, 22 purchase order, 13 A/R invoice, 18 A/P invoice, 1470000113 purchase request.

Older than (days)

Generated code

Download:

1. SQL Server: approval requests by approver

SELECT
  T0.WddCode,
  T0.ObjType,
  T0.DraftEntry,
  T0.CreateDate,
  DATEDIFF(day, T0.CreateDate, GETDATE()) AS DaysWaiting,
  U0.U_NAME AS Originator,
  T2.Name AS Template,
  S0.Name AS Stage,
  U1.U_NAME AS Approver,
  T1.Status AS ApproverStatus,
  T0.Status AS RequestStatus,
  T1.UpdateDate,
  T1.Remarks,
  D0.CardCode,
  D0.CardName,
  D0.DocTotal
FROM OWDD T0
INNER JOIN WDD1 T1 ON T0.WddCode = T1.WddCode
LEFT JOIN OWTM T2 ON T0.WtmCode = T2.WtmCode
LEFT JOIN OWST S0 ON T1.StepCode = S0.WstCode
LEFT JOIN OUSR U0 ON T0.OwnerID = U0.USERID
LEFT JOIN OUSR U1 ON T1.UserID = U1.USERID
LEFT JOIN ODRF D0 ON T0.DraftEntry = D0.DocEntry
WHERE T1.Status = 'W'
  AND T0.ObjType = '22'
  AND DATEDIFF(day, T0.CreateDate, GETDATE()) >= 3
ORDER BY T0.CreateDate, T0.WddCode

2. HANA: approval requests by approver

SELECT
  T0."WddCode",
  T0."ObjType",
  T0."DraftEntry",
  T0."CreateDate",
  DAYS_BETWEEN(T0."CreateDate", CURRENT_DATE) AS "DaysWaiting",
  U0."U_NAME" AS "Originator",
  T2."Name" AS "Template",
  S0."Name" AS "Stage",
  U1."U_NAME" AS "Approver",
  T1."Status" AS "ApproverStatus",
  T0."Status" AS "RequestStatus",
  T1."UpdateDate",
  T1."Remarks",
  D0."CardCode",
  D0."CardName",
  D0."DocTotal"
FROM "OWDD" T0
INNER JOIN "WDD1" T1 ON T0."WddCode" = T1."WddCode"
LEFT JOIN "OWTM" T2 ON T0."WtmCode" = T2."WtmCode"
LEFT JOIN "OWST" S0 ON T1."StepCode" = S0."WstCode"
LEFT JOIN "OUSR" U0 ON T0."OwnerID" = U0."USERID"
LEFT JOIN "OUSR" U1 ON T1."UserID" = U1."USERID"
LEFT JOIN "ODRF" D0 ON T0."DraftEntry" = D0."DocEntry"
WHERE T1."Status" = 'W'
  AND T0."ObjType" = '22'
  AND DAYS_BETWEEN(T0."CreateDate", CURRENT_DATE) >= 3
ORDER BY T0."CreateDate", T0."WddCode"

3. SQL Server: templates, documents, stages and authorizers

SELECT
  T0.WtmCode,
  T0.Name AS Template,
  T0.Active,
  W3.TransType AS ObjType,
  S0.WstCode,
  S0.Name AS Stage,
  S0.MaxReqr AS ApprovalsRequired,
  S0.MaxRejReqr AS RejectionsRequired,
  U0.USER_CODE AS Authorizer
FROM OWTM T0
INNER JOIN WTM3 W3 ON T0.WtmCode = W3.WtmCode
INNER JOIN WTM2 W2 ON T0.WtmCode = W2.WtmCode
INNER JOIN OWST S0 ON W2.WstCode = S0.WstCode
INNER JOIN WST1 S1 ON S0.WstCode = S1.WstCode
INNER JOIN OUSR U0 ON S1.UserID = U0.USERID
WHERE W3.TransType = '22'
ORDER BY T0.WtmCode, S0.WstCode

/* Originators of each template */
SELECT T0.WtmCode, T0.Name AS Template, U0.USER_CODE AS Originator
FROM OWTM T0
INNER JOIN WTM1 W1 ON T0.WtmCode = W1.WtmCode
INNER JOIN OUSR U0 ON W1.UserID = U0.USERID
ORDER BY T0.WtmCode

Status values in OWDD.Status and WDD1.Status

Value	Meaning
W	Pending
Y	Approved
N	Not approved (rejected)
P	Generated (document created from the approved draft)
A	Generated by authorizer
C	Canceled

Read-only reporting queries. Column names follow the SAP Business One 10.0 table structure; confirm OWDD.DraftEntry, OwnerID and the status values in the SDK table reference for your patch level, and check the object type codes in OWDD.ObjType. Never update OWDD, WDD1 or the template tables with SQL: it is unsupported and breaks the approval history.

Get your approval templates reviewed

Send us the output of the template query and we will point out overlapping templates and stages that cause stuck approvals.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Choose the approver status (W for pending is the usual one), the document object type and the minimum age in days. Paste block 1 or 2 into Query Manager, run it and save it; a manager can then open it daily to chase approvals that are stuck. Each row is one approver on one request, so a two-approver stage gives two rows, which is exactly what you want when chasing people. Block 3 lists, for the same document type, which templates apply, their stages, how many approvals and rejections each stage needs, and which users can authorize.

Sort by DaysWaiting to find the oldest requests.
Filter on Approver to build a personal to-do list.
Compare ApproverStatus with RequestStatus to see stages already decided by others.

How the approval tables fit together

Templates (Administration > Approval Process > Approval Templates) are stored in OWTM, with originators in WTM1, stages in WTM2, document types in WTM3 and terms in WTM4. Stages are defined in OWST, with their authorizers in WST1. When an originator adds a document that meets the terms, Business One saves a draft in ODRF and creates an approval request in OWDD (one row per request, with template, object type, draft key, originator and overall status) plus WDD1 rows, one per authorizer per stage, holding that person's decision, date and remarks. Once all stages approve, the originator adds the document from the draft and the request moves to generated.

Cannot update approval stage: what it usually means

The message appears when a user tries to approve or reject a request in a stage where they are not, or no longer, an authorizer, or when the request is no longer pending because it was canceled, already decided or the draft was changed. Run block 1 for the request and check the WDD1 row for that user, then block 3 for the current stage authorizers. If the template or stage was changed after the request was created, the request keeps its original stages. The fix is done in the application: cancel the request and resubmit the draft, or adjust the stage and create a new request.

Gotchas

Requests whose draft was deleted can remain in OWDD with status W, so cross-check results with the Approval Status Report and the Approval Decision Report in the application before chasing anyone. ObjType is stored as text, so keep the quotes around the code. The draft totals in ODRF are document totals including tax. On HANA every identifier must be double quoted with its exact case. Finally, resist the temptation to fix stuck approvals with UPDATE statements: SAP does not support direct writes to Business One tables, and support may refuse help on a company that has them.

Frequently Asked Questions

Which tables store SAP Business One approvals?

OWDD holds approval requests and WDD1 the decision of each authorizer per stage. Templates are in OWTM with WTM1 (originators), WTM2 (stages), WTM3 (documents) and WTM4 (terms). Stages are in OWST with authorizers in WST1. The pending document itself is a draft in ODRF.

How do I see all pending approvals in SAP B1?

Use the Approval Status Report in the application, or query WDD1 with Status 'W' joined to OWDD, OUSR and ODRF as in the generated query, which also shows the age of each request and the approver it waits on.

Why do I get cannot update approval stage in SAP B1?

The user is not an authorizer of the current stage, the request is no longer pending, or the draft changed after the request was made. Check the user's WDD1 row and the stage authorizers in WST1, then cancel and resubmit the request in the application if needed.

How does the SAP Business One approval process work?

An approval template names originators, documents, stages and terms. When an originator adds a matching document, a draft and an approval request are created; each stage's authorizers approve or reject, and when the required approvals are reached the originator adds the document from the draft.

If your managers want to ask Business One which approvals are waiting on them in plain English, on your own server, we can set that up read-only.

Talk to an Expert Explore More Free Tools