(Back to D-SL Sales AR Module SQL Posted to Wiki Listing - D-SL Sales AR Module SQL Posted to Wiki )
TYPE: Frequently Requested SQL
AUTHOR NAME: Zal Parchem
PURPOSE AND BACKGROUND: This SQL was created in 2007A SP01 PL05. This SQL was created as a Sales Department tool in contacting the Customer and updating them on late shipments. The company decided that instead of a User-Defined Field (UDF), their Production Personnel will input their comments in the "Remarks" section of the Sales Oder. Sales Personnel subtract one week from today's date for the second "DocDueDate" and it provides important information for them to use which is not available on the SAP B1 "Open Items List" (telephone number, contact name, etc). This is a fairly straight and simple SQL which Sales Personnel appreciate. 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 Late Sales Order by Date Range Ver 2 ZP 2011 01 31
-- DESCRIPTION: SQL provides Customer contact and ship to information so the Customer can be contacted about possible late shipments being made. Remarks are also displayed in case the expected ship date has been entered by the Production Department as to when the Sales Order might ship.
--Version 1 Zal Parchem 2009 11 16
--Version 2 Zal Parchem 2011 01 31 - Included Remarks field used by Production Department for possible shipping dates.
T0.CardCode AS 'Cust Numb',
T0.CardName AS 'Customer Name',
WHEN T2.FrozenFor = 'Y' THEN 'On Fin Hold'
WHEN T2.FrozenFor = 'N' THEN 'Not on Fin Hold'
END AS 'Financial Stat',
T0.DocNum AS 'Sls Ord Numb',
T0.NumAtCard AS 'Cust PO Ref',
T0.DocDueDate AS 'Orig Ship Date',
T0.DocTotal AS 'Sls Ord Total',
T1.Name AS 'Contact Person',
T2.Phone1 AS 'Cust Phone',
T1.Tel1 AS 'Contact Phone',
T0.Comments AS 'SO Remarks'
FROM ORDR T0
LEFT OUTER JOIN OCPR T1
ON T0.CntctCode = T1.CntctCode
LEFT OUTER JOIN OCRD T2
ON T0.CardCode = T2.CardCode
T0.DocStatus = 'O'
AND T0.DocDueDate >= [%0]
AND T0.DocDueDate <= [%1]