Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

(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:

 D-SL Late Sales Order by Date Range.png

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(s):
--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.

SELECT

T0.CardCode AS 'Cust Numb',
T0.CardName AS 'Customer Name',

CASE
WHEN T2.FrozenFor = 'Y' THEN 'On Fin Hold'
WHEN T2.FrozenFor = 'N' THEN 'Not on Fin Hold'
ELSE 'Error'
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

WHERE

T0.DocStatus = 'O'
AND  T0.DocDueDate >= [%0]
AND  T0.DocDueDate <= [%1]

ORDER BY

T0.CardName,
T0.DocDueDate

FOR BROWSE

  • No labels