(Back to D-SL Sales AR Module SQL Posted to Wiki Listing - D-SL Sales AR Module SQL Posted to Wiki )
TYPE: Monthly Special
AUTHOR NAME: Zal Parchem
PURPOSE AND BACKGROUND: This SQL was created in Version 8.8 PL 15. This SQL went through several versions - at first the End User wanted it to show specifically by item code and then it eventually morphed into specific orders connected with specific shipments (as you see here). Some might say this should be called "Customer Fill Rate" but it does not actually have the Customer information, so maybe it would be best to call it "Sales Order Fill Rate". But notice how the End User requested the SQL results to be sorted by the ORDER BY section. The End User ended up utilizing the "Filter" icon in SAP B1 to look at the information in different ways. Whatever you would like to call it, the SQL does have a rather good example of using the DATEDIFF in combination of CASE to show you how you can determine various statuses with the CASE statements which is highlighted in red. You might be able to use it "as is" or add more data fields to this SQL, depending upon your own requirements. If you find this SQL helpful, then use “copy and paste” to place and save it using the Query Generator of your SAP B1 installation.
Here is a screen print of the SQL results:
Copy and Paste this SQL:
--D-SL Lapsed Days Order to Deliveries Order Detail Ver 3 ZP 2011 04 16
--DESCRIPTION: SQL displays the days it takes for a Product to be entered into a Sales Order and then shipped from the Warehouse.
--Version 1 Zal Parchem 21 March 2011
--Version 2 Zal Parchem 15 April 2011 Added different classes for the shipment days and took out canceled orders
--Version 3 Zal Parchem 16 April 2011 Reduced SQL down to individual order shipment days
DATEDIFF(DD, T2.DocDueDate, T3.DocDueDate) AS 'Lapsed Days',
WHEN DATEDIFF(DD, T2.DocDueDate, T3.DocDueDate) >= 5 THEN 'Late by 5 Days or More'
WHEN DATEDIFF(DD, T2.DocDueDate, T3.DocDueDate) BETWEEN 1 AND 4 THEN 'Late by 1 to 4 Days'
WHEN DATEDIFF(DD, T2.DocDueDate, T3.DocDueDate) = 0 THEN 'On Time'
WHEN DATEDIFF(DD, T2.DocDueDate, T3.DocDueDate) < 0 THEN 'Early Shipment'
ELSE 'Not Shipped'
END AS 'Ship Stat Results',
T2.DocNum AS 'Sales Ord',
T2.DocDueDate AS 'Exp Del Date',
T3.DocNum AS 'Del Num',
T3.DocDueDate AS 'Del Date'
FROM RDR1 T0
LEFT OUTER JOIN DLN1 T1
ON T1.BaseType = 17
AND T0.DocEntry = T1.BaseEntry
AND T0.LineNum = T1.BaseLine
AND T0.ItemCode = T1.ItemCode
LEFT OUTER JOIN ORDR T2
ON T0.DocEntry = T2.DocEntry
AND T2.Canceled = 'N'
LEFT OUTER JOIN ODLN T3
ON T1.DocEntry = T3.DocEntry
T2.DocType = 'I'
DATEDIFF(DD, T2.DocDueDate, T3.DocDueDate),