Useful SAP Business One Queries: Sales Orders with Multiple Deliveries

Richard Duffy
Richard Duffy in SAP Business One, SQL Queries January 08, 2018

In my role as SAP Business One Subject Matter Expert and Community Evangelist for ONE.Source (long title, I know), I help moderate our community forum. Each month, one of the oldest posts ranks as our most popular, both for ASUG members as well as those who stumble upon our community via search engines: Useful SAP B1 Queries from August of 2009. 

There are a number of queries shared throughout the thread, but the first query shared fits nicely with our inaugural Logistics month as we kick off our year-round curriculum for 2018. With that lucky tie-in to our January theme, and given the post's popularity, I thought it appropriate to highlight with a blog. (Over time, perhaps we can continue to explore more of these queries in an ongoing series.) 

The Business Case

The original author's organization wanted to take a look at sales orders that generate multiple deliveries, to see if there might be some inventory adjustments that might have let those orders go out the door complete (and sooner).

As Matt Roberts wrote in his original post, if you wish to use this query,  cut and paste the query text below into the Query Generator window and save the query to your SBO query manager.

The SQL Query code

SELECT DISTINCT INV1.DocEntry AS 'Invoice',

DLN1.DocEntry AS 'Delivery',

RDR1.DocEntry AS 'Sales Order', RDR1.DocDate AS 'SalesOrderDate', YEAR(RDR1.DocDate) AS 'SODateYear', MONTH(RDR1.DocDate) AS 'SODateMonth'

FROM INV1

    INNER JOIN DLN1 ON INV1.BaseEntry = DLN1.DocEntry

                        AND INV1.BaseLine = DLN1.LineNum

    INNER JOIN RDR1 ON DLN1.BaseEntry = RDR1.DocEntry

                        AND DLN1.BaseLine = RDR1.LineNum

WHERE INV1.BaseEntry IN (

    SELECT DISTINCT DocEntry FROM DLN1 WHERE BaseEntry IN (

            SELECT BaseEntry

            FROM (SELECT DISTINCT ODLN.DocEntry, DLN1.BaseEntry

    FROM ODLN INNER JOIN DLN1

        ON ODLN.DocEntry = DLN1.DocEntry

    WHERE DLN1.BaseEntry IS NOT NULL) AS BaseDoc

    GROUP BY BaseEntry

    HAVING COUNT(DocEntry) > 1)

) AND INV1.BaseType = 15

  AND DLN1.BaseType = 17

ORDER BY RDR1.DocEntry

Refine the Data with Filters

As intended, this query pulls all sales orders, deliveries, or invoices where a sales order has multiple deliveries, and the results are sorted by the sales order number.

If you want to further narrow down the documents to a particular month or year, it's easy to use the filter tool on the query (the funnel-shaped icon in the top menu bar in SAP). Use the 'SODateYear' and 'SODateMonth' fields to narrow the results down or add WHERE statements to your querey to achieve your desired result.

Useful Queries for SAP Business One

As I mentioned, this discussion of useful queries for your SAP Business One solution is our most popular post, and there are more queries to review throughout the thread.

Over the years, however, the thread has becomed a bit bogged down with many comments and questions about other queries. While the original author appears to have moved on to other priorities, you have ONE.Source (and me) as a resource for both queries and other topics.  So I encourage you to ask your questions via the Q&A in our community forum or share your favorite query by starting a new discussion thread. Who knows, perhaps your post will be our most popular in a few years' time. 

Ask a Question or Start a New Discussion