Mastering SQL Queries for SAP Business One

Examples of user queries for approval

These examples will familiarize you with how to apply a user query to the approval procedure.

Case 1—Approval for adding delivery document

The first case is relatively simple. This approval in need is triggered when a user tries to add a delivery document for a customer with A/R Invoices that have overdue DocDueDates.

The solution to this simple request is shown here:

SELECT DISTINCT 'True'

FROM dbo.OINV T0

WHERE DateDiff(DD,GetDate(),T0.DocDueDate) > 0
AND T0.CardCode = $[$4.0.0]

This is a very simple query and is explained in the following points:

• SELECT DISTINCT 'TRUE' will be used for all approval queries. DISTINCT keyword can be omitted if you can make sure the query result will only return one line. When in ...