Stumbling Through SAP B1

B1 contains an integer field in the OCRD (Business Partner Master) table called “AvrageLate”. The field appears in the Payment Terms tab of Business Partners and is used to calculate delays in payment for SAP’s Cash Flow Forecasting.

For some reason, SAP decided to not have this field automatically calculate based on the customer’s previous applied payments. It must be manually entered, however, there is no way to calculate a genuine average if each time you enter a number into this field, you replace the previous value.

This original code comes from Battleship Cobra, who suggests adding this as a Transaction Notification storedproc, but best practice now would be to use PostTransactionNotice for such manipulations. I’ve tweaked it to convert the result to an integer (OCRD.AvrageLate is an INT data-type) and also to use DAYS_BETWEEN rather than DateDiff(), since HANA doesn’t support that function.

10/25/22 UPDATE: The difference between Transaction Notification Stored Procs and PostTransactionNotice Stored Procs is that Post Transaction Notice runs after the transaction has been fully committed on the DB layer (after all SAP validations have passed.) Transaction Notifications are good for validation before the data has been posted to the DB, PostTransactionNotice is for things you want to run after the data has been committed.

The code below is for HANA!

-–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] =
TO_INT(
(
SELECT AVG(DAYS_BETWEEN(TO_DATE(OINV."DocDueDate"), TO_DATE(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. Note that this uses the number of days between the DocDueDate, and the Receipt of Payment. So this number would be the number of days after a customer’s terms are over, that they are late. You could also just use the OINV.DocDate to calculate the number of days that it takes a customer to pay once the invoice is created, ignoring terms.

Also, this code will generate negative integers for customers who pay “early” (before their invoice Due Date.)

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] =
TO_INT((
SELECT AVG(DAYS_BETWEEN(TO_DATE(OINV."DocDueDate"), TO_DATE(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'

Of course, doing an UPDATE on “standard” SAP B1 fields will violate your SAP maintenance agreement, so we will create a UDF instead, and UPDATE the UDF.

I believe we can then use B1UP’s “Business Partner Master Data” manager, and set the “Value” to a SQL statement that pulls from that UDF, to update the field within B1, so it can be used for Cash Flow Forecasting. You can “Schedule” B1UP’s Master Data Manager to run nightly and copy data from your “swap” UDF to the standard SAP field OCRD.”AvrageLate”

SQL:SELECT OCRD."OurUDF" FROM OCRD WHERE OCRD."CardCode" = 'DocKey@'