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 email@example.com and I'll see what I can do. Hope this is of some help to someone!
-Matt Roberts, MCTS
Prestwick House, Inc.