Average Delay (Average Payment Delay) in SAP Business One

I was looking for a way to use the Average Delay field in the Business Partner Master Data Payment Terms tab. I was wondering why this doesn’t automatically calculate through the system but I figured someone had written some code to make it work correctly. Knowing the average number of days to payment is an extremely useful piece of information, especially when setting credit limits or making decisions to set certain clients as inactive, etc.

This originally came as a request from a client but made perfect sense so I set about finding some sort of solution.

I came across the following code which seems to work very well. Credit goes to a consultant friend of mine Denis Doiron for writing the code.

To populate the average delay field automatically we will use the transaction notification storproc in your database of choice. If you are not familiar with storprocs or SQL this article will not help you and you should seek a certified SAP Consultant.

Here is the code to insert into the transaction notification:

–UPDATE AVERAGE DELAY ON INCOMING PAYMENT

IF @object_type = ’24’ AND @transaction_type = ‘A’
BEGIN
DECLARE @CardCode AS VARCHAR(15)
SELECT @CardCode = CardCode
FROM ORCT
WHERE DocEntry = @list_of_cols_val_tab_del
UPDATE OCRD SET [AvrageLate] =
(
SELECT AVG(DateDiff(dd, OINV.DocDueDate, ORCT.DocDate))
FROM RCT2
INNER JOIN ORCT ON ORCT.DocEntry = RCT2.DocNum
INNER JOIN OINV ON OINV.DocEntry = RCT2.DocEntry AND RCT2.InvType = 13
WHERE oinv.cardcode = OCRD.Cardcode
)
WHERE CardCode = @CardCode
END

As you can see it is triggered by an incoming payment and simply runs in the background and updates the average delay for that particular customer based on the entire history of payments.

You might also consider running this query once as you are installing the storproc code. The following code modified will update all of the average delays at once to get you started.

UPDATE OCRD SET [AvrageLate] =
(
SELECT AVG(DateDiff(dd, OINV.DocDueDate, ORCT.DocDate))
FROM RCT2
INNER JOIN ORCT ON ORCT.DocEntry = RCT2.DocNum
INNER JOIN OINV ON OINV.DocEntry = RCT2.DocEntry AND RCT2.InvType = 13
WHERE oinv.cardcode = OCRD.Cardcode
)
WHERE OCRD.CardType = ‘C’

You may want to filter some of the date ranges taking into consideration maybe only the last 365 days of time rather than the entire history, or perhaps filtering for $ amounts less than X amount because occasionally, even a good client will have an small amount outstanding between orders. But you can add those fairly easily.

If you have any questions, leave a comment!