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

 S-FS Average Lapsed Days FMS Set Up Screen Print.png

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(s):
--Version 1 Zal Parchem 2010 06 22

SELECT

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

WHERE

T4.CardCode = $[$5.0.0]

  • No labels