A quick note about this discussion thread:
This thread from 2009 is our most popular post and we are happy you found it. However, as it has been many years since this was posted (and many of you may have found your way here via search results), I want to let you know you are unlikely to receive a response from the original author.
In October 2017, ASUG re-launched our community for SAP Business One as ONE.Source with a renewed commitment to year-round education and online community (more information). As a membership-based organization, some of our content (webcasts, videos, training documents, etc) is exclusive to ASUG members; however, all are welcome to read our blogs and access our community forum.
If you have a question about SAP Business One queries (or anything else B1 related), I would encourage you to ask your question in the new Q&A area of our community forum. Our dedicated subject matter expert, Richard Duffy, is helping moderate this forum and we promise your question won't go unanswered.
Recent blogs on SAP Business One Queries:
- Useful SAP Business One Queries: Sales Orders with Multiple Deliveries
- Useful SAP Business One Queries: When Did Your Customers Last Purchase from You?
- SQL Queries in SAP Business One - Make them even more useful with these tips
Thank you for visiting ONE.Source, you can find the original post below.
Danny Pancratz, Director of Education, Americas' SAP Users' Group
(Aug 24, 2009)
I wrote a query the other day, and thought that, rather than using this forum just to post Q&A in response to problems, I might actually post on a regular basis some of the queries that I've developed for our company that others might find useful. I'll also post the business need or scenario that prompted us to write the query (and get the information out of the database).
This first query answers the question: how many sales orders have multiple deliveries / invoices? In my company, our sales orders are 99% item marketing documents (as opposed to service) - we receive orders for items, we then ship them (Delivery), and then invoice them (Invoice) at the end of the day. We do not combine multiple sales orders to a single delivery. The only situations where we might have multiple deliveries for one sales order are when an item is out-of-stock and has to be backordered for an extended period of time (longer than a week). This results in a lower first hit rate, or the percentage of orders which can ship fully the first time.
In an effort to improve our customer service, we wanted to take a look at those sales orders that generate multiple deliveries, to see if there might be some inventory adjustments that might have let this order go out the door complete (and sooner). If you wish to use this query, simply cut and paste the query text into the Query Generator window and save the query to your SBO query manager. The query is as follows:
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'
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 (
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
There are a few nested subqueries in this query, but it does run fairly quickly on our system, and we have > 100,000 each of sales orders / deliveries / invoices. This will select the Invoice number, the Delivery number, and the Sales Order number for orders that have gone from Sales Order -> Delivery -> Invoice. Orders that have gone from Sales Order directly to Invoice are excluded.
As it stands, this pulls all sales orders / deliveries / invoices where a sales order has multiple deliveries, and the results are ordered by the sales order number. Should one wish to see only the documents from a particular month and / or year, it's easy to use the filter tool on the query (the funnel-shaped icon in the top menu bar in SAP) on the 'SODateYear' and 'SODateMonth' fields to narrow the results down.
I'll see what other useful queries we might have lying around that are slightly above the average difficulty level for query-users. Of course, if you have a particular query that you might like to have written, I'd find it pretty fun (and fairly easy) to do so - as long as you don't mind me posting it here. Shoot me an email at firstname.lastname@example.org and I'll see what I can do. Hope this is of some help to someone!
-Matt Roberts, MCTS
Prestwick House, Inc.