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.