(Back to S-FS Formatted Searches SQL Posted to Wiki Listing - S-FS Formatted Searches SQL Posted to Wiki )
TYPE: Monthly Special
AUTHOR NAME: Zal Parchem
PURPOSE AND BACKGROUND: This SQL was created in 2007A SP01 PL05. One important decision made by Financial Personnel on a Customer's Credit Rating is what is the average number of days a Customer takes to pay their invoices over the time they have been a Customer with the company. Currently in SAP B1 there is no functionality to show what the average lapsed days between the invoice dates and dates in which cash application is made on the Customer's Account in the Incoming Payments window. Here is an SQL which shows one way of how that can be accomplished. In order to use this SQL, you need to add a User-Defined Field to the Business Partner Master Data window; we titled the UDF "AvgLapseDays", with a Description of "Avg Lapse Days", and a type of "Numeric (10)". Make sure you read the proper SAP Manuals before using this SQL - see SAP B1 SQL Links and Additional Information . If you needed this on Vendors as well, I guess you could change the tables in the SQL to the proper ones for Vendor AP Invoices and AP Outgoing Payments. 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 in the User-Defined Field and how to set the Formatted Search with Shift+Alt+F2:
Copy and Paste this SQL:
--S-FS Average Lapsed Days Ver 1 ZP 2010 06 22
--DESCRIPTION: SQL shows AVERAGE days between the posting of an invoice and the application of the customers payment for an FMS on Business Partner.
--Version 1 Zal Parchem 2010 06 22
(SUM(DateDiff(DD, T4.DocDate, T1.DocDate))/COUNT(T4.DocNum))
FROM OCRD T0
INNER JOIN ORCT T1
ON T0.CardCode = T1.CardCode
INNER JOIN RCT2 T3
ON T3.DocNum = T1.DocNum
INNER JOIN OINV T4
ON T4.DocEntry = T3.DocENtry
AND T3.InvType = '13'
T4.CardCode = $[$5.0.0]