Image

SAP Business One B1i SSL Integration Installation (Details)

brain quiz

See my video on the B1i installation process including my AutoSSL tool by clicking here.

Main SAP Note 2019275 (click for link)
NOTE: This article has been adapted from the main SAP note and expanded upon based on several installations.

Prerequisites

  1. You need to have B1i installed
  2. You need the current .keystore password located at: C:\Program Files (x86)\SAP\SAP Business One Integration\IntegrationServer\Tomcat\conf\server.xml
  3. Search the XML document for the keystorePass attribute. Default that I have observed has been sapB1iP
  4. You need a domain name associated with your raw IP address. IE, mobile.lhimports.com as an example. This must resolve to their static IP address with an A record in the subdomains DNS.

Initialize OpenSSL

  1. Go to: http://slproweb.com/products/Win32OpenSSL.html
  2. Download and install Visual C++ 2008 Redistributables (x64)
  3. Download and install Win64 OpenSSL v1.0.2d Light
  4. Install everything with default settings
  5. Continue reading

brain quiz

SAP Business One: EFT Bank File Format (Electronic Funds Transfer)

Since SAP Business One 9.0 I had been hearing about new EFT capabilities built right into the system and I had a client request this feature so I decided to really dig into the subject. So after a LOT of investigation and testing I will summarize in plain English what I found.

EFT_02

(NOTE: my focus here is on EFT files, NOT bank reconciliation files but SBO can do this as well. I will attempt to note this whenever possible.)

SAP Business One Has a Really Well Thought Out EFT System

It’s hard to find and understand but it is well thought out once you get the hang of it. It can do EFT files for payment, import files for bank reconciliation and generate CSV files in specific format for any other file type you would want to work with. I was using it specifically for an EFT outgoing payment file to a bank for payments.
Continue reading

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!