Lockbox deep-dive in SAP S/4HANA

Lockbox is the most popular way to handle the receipt and processing of incoming payments in the United States. This service is offered by all major banks. A company can create n number of such accounts at its bank (or banks), which will work as its payment collection partner for customer payments. The customers, then, send their payments to these bank (lockbox) accounts, along with that they provide remittance information for what open items these customer payments intend to clear. Now once the bank has received all these payments for a day, it creates an electronic data file from the payment advice data and the customer payment amounts, for transmission to the Lockbox owner (company). Once the company receives the file (or fetches it from Bank sFTP), it uploads these file(s) in SAP which in turn automatically clears customer open items and posts accounting entries based on the configuration setup.

This sounds familiar to most of us, right? But before starting the blog post it was necessary to give an overview of what the lockbox functionality is all about. So now as we understand it correctly, let’s deep dive into this functionality. Firstly, the file structure which the bank sends – BAI2 file. Now I am not going to discuss about BAI file, which is a cheaper option than BAI2, but with this file you may not have a suitable ‘hit rate’ for automatically matching payments to customer open items. Therefore, BAI2 is the recommended one for processing large volume of Lockbox payment transmissions each day. It considers common scenarios such as multiple items are paid per single check and deductions occur commonly etc.

Please Note: banks charge per keystroke, therefore the BAI2 files will cost more to produce than BAI files.

You can get the BAI2 file sample from the SAP program = RFEBLBT2

An extract from my system:

(All the images shown in this blog are not any customer specific and are for public use)

Put your cursor somewhere on the data –

1. Select by CTRL+Y (it will highlight the data);
2. Copy via CTRL+C and paste it into a new notepad;
3. Change the check number(s) in the second or third check (because by default it comes same for all 3);
4. Save the notepad file

BAI2 File Structure

Now in case you are new to the BAI2 file structure, you can always check these field structures in the system in following SAP structures (T-code SE11 > View):

• Record 1: FLB01 – Header Record

This record type This record type marks the beginning of the data and identifies both the source and destination of all data contained therein. It comes only once per lockbox within the file, unless your transmission contains four lockboxes, for instance, then you will have four of this record ‘1’ in your file.

• Record 2: FLB02 – Service Record

This record type identifies the purpose, content and physical characteristics of the data. It is not a mandatory record for lockbox processing. Some banks utilize a Record 2, and others don’t. Similar to record 1, there can be only one record 2 per lockbox within the file, unless there are many lockbox transmissions within a single file.

• Record 5: FLB05 – Detail Header Record

This record is used to reduce the size of ensuing detail records by carrying the required information that is common to all the detail records it precedes for a given deposit site. Like record 1, there can be only one record 5 per lockbox within the file, unless there are many lockbox transmissions within a single file.

• Record 6: FLB26 – Detail Record (New Check)

This record type contains remitter’s bank information (Transit Routing No. and Account Number), commonly known as the “MICR” data (Magnetic Ink Character Recognition). This is the data that appears at the bottom of bank checks. This record also contains remittance amount and check information, on which the frequency of this record type is dependent, i.e. the record will appear multiple times in the file, if there is more than one check, or if there is a new customer’s information within the same batch.

• Record 4: FLB24 – Overflow Record

This record provides a means of handling multi-invoice transactions. If a transaction contains one check multiple invoices, this record is used to accommodate this additional data. It contains the invoice numbers, payment amounts per invoice, and external reason code for the payment. Please note that only one invoice is placed into each record 4.

Tip: In the overflow record, a free text may be appended after the reason code. This text is not transferred to the payment advice position. To do so, kindly refer to note 565284, which can assist you in filling the payment advice item text (field AVIP-SGTXT) with the record 4 free text.

• Record 7: FLB07 – Batch Total Record

This record marks the end of a batch of detail records and is used for balance and control purposes. Note that each batch has a unique, identifying set of sequential batch and item numbers.

• Record 8: FLB08 – Lockbox Total Record

Record Type 8 appears only once in the Lockbox file.  Record 8 is the “Service Total Record” which signifies the end of all Lockbox bundles.

• Record 9: FLB09 – Trailer Records

This record marks the end of the data and includes the total number of records. There can only be one Record 9 in a file.

Tip: Please note that the total length for each record is fixed. Therefore, spacing is critical because the Lockbox program reads the file based on character position. So, use spaces to make up the excess space not used by an item within the record.  For example, if your Origin of Transmission only uses 6 characters, then it should be preceded by four blank spaces.

To build different scenarios, you can recycle and edit the file. Two very important things to remember:

• Update the date and time on the first row of the data file. If not, the program will tell you that you are uploading a duplicate file

100LOCKBOXDESLOCKBOXORI    2009090526

                                                                                                         YYMMDDHHMM

• Update the check number. Remember, the payment advice is a function of the check number. If you duplicate the check number, the payment advice will already exist, and the system will not allow file processing.

6001001000060000088888887 87654321 010142260

Lockbox Configuration

Lockbox Configuration is not a lengthy process, if all of the following information is handy –

1. Total No. of lockbox account(s) along with their House bank & Account IDs
2. Which Company code has which lockbox account(s)
3. Lockbox account(s) – Destination & Origin keys. [Destination is generally Bank account no. and Origin is generally routing/bank key]
4. Type of G/L posting(s) i.e. One posting per check, or per lockbox, or per batch;
5. G/L account number for bank / bank sub-account (Lockbox Clearing);
6. G/L account number for Bank clearing (incoming payment remains in this account as an open item until it is cleared or posted on account to a customer);

Once you have all this information, following are the 3 steps process to configure Lockbox –

1. T-code OB10 : Define Lockboxes for House Banks

Here you must specify your lockbox links (company code; key of the lockbox to which the customer is to pay; house bank ID; lockbox number at your house bank).

Sample data –

Path: Financial Accounting >> Bank Accounting >> Bank Accounts >> Define Lockboxes for House Banks

2. T-code OBAY : Define Control Parameters

In this activity you store control data for the lockbox procedure. Currently, only the procedure LOCKBOX is supported.  This data is needed for importing lockbox files sent by banks.

Sample data –

Path: Financial Accounting >> Bank Accounting >> Business Transactions >> Payment Transactions >> Lockbox >> Define Control Parameters

Please note that the partial payments checkbox would mean that will be posted as partial payments in that case where the payment is insufficient for full clearing.  The default behavior is to generate residual items with such payment receipt.

3. T-code OBAX : Define Posting Data

In this activity you store information needed to process particular lockbox data and generate postings.  The Destination and Origin are routing information and defined by your bank.

Sample data –

Path: Financial Accounting >> Bank Accounting >> Business Transactions >> Payment Transactions >> Lockbox >> Define Posting Data

That’s all about the Lockbox configuration. Yes, that’s true. Simple, isn’t it?

Lockbox File Processing

Image credits: Rahul Girdhar

Now, let’s test the Lockbox, through processing this BAI2 file. In order to do so –

Run the Lockbox Program through T-code FBL2 (Main Lockbox Program)

This is normally executed overnight in background mode, although it can be run online. The file, which is processed, can be transmitted by your bank or some banks also allow to dial in and download the files.

Now, once you run this lockbox program it performs following tasks –

1. It checks for duplicate bank transmission (date & time on the first record);
2. It adds bank file to SAP bank buffer and payment advice tables;
3. It identifies customer using record type 6 info. (MICR details);
4. It generates the Payment advices for the Clearing of the Sub ledgers (A/P, A/R) and conducts G/L postings related to the Bank accounts;
5. It updates payment advice tables;
6. Generates a Log of the Check(s) and Postings.

While processing the file, there could be any of the following statuses –

If a customer is matched via MICR or invoice number, then the open items get cleared, and the check gets an “Applied” status. No further action required on this.

If the customer is matched, but the open items are not cleared, the check gets an “On-Account” status. User will have to manually clear the open items via standard SAP processing.

If customer(s) and invoice(s) are matched, but there is an under-payment / short pay, then the check will have “Partially Applied” status. i.e. residual items.

If customer(s) not matched, then check will have “Unprocessed” status. User will have to investigate to establish the customer and manually clear the open items via standard SAP processing.

Residual Payments

Suppose the lockbox file contains a $10 short payment on one or two invoices. And the customer has sent reason code on the remittance to the bank for the short pay. This reason code must be pre-defined and mapped in the system.

Path: Financial Accounting >> Accounts Receivable and Accounts Payable >> Business Transactions >> Incoming Payments >> Incoming Payments Global Settings >> Overpayment/Underpayment

To configure this, you must have a reason code conversion version in the customer master record, and a conversion defined in configuration. The reason code conversion allows text to populate the charge-back line item automatically, providing additional information for those viewing the account.  No post-processing would be necessary because the payment advice will get cleared with payment. Yes! The lockbox program will generate the payment advice and will match the open invoices against the payment. Now, the open items on the customer record, for example if $1480 was received against 2 invoices of $750 each, will get reduced by $1480.

Automation of Incoming File Transmission

It is generally recommended to automate the Upload and Processing of the Lockbox, as an overnight in background job.

So, what happens? The Bank prepares lockbox check remittances in BAI2 Flat file. The file gets picked up from Bank sFTP server by SAP’s PI/CPI middleware. This means to automate the file transfer between banks and SAP sFTP servers there requires an interface to be developed for seamless end-to-end processing of the file. And it is advisable that all the files should be PGP encrypted and sFTP to be used for transmission/retrieval. The PI/CPI places the file in a designated folder e.g. /INTERFACE/CPI/INBOUND/….

Then after daily SAP job will run and pick the file from folder and execute Program = RFEBLB00 (T-code FLB2) to import the file into bank data table. Program RFEBLB00 internally calls program RFEBLB20 which maps the imported data to the standard SAP data structure i.e. FLB01, FLB02 etc. Program RFEBBU00 utilizes the payment advices created through program RFEBLB20 to match customer using MICR or invoice number. No further programmatic execution is required.

After preprocessing, the original file should be archived, and output file should be stored in Lockbox Inbound folder for further processing. Further actions can be handled manually by users in “Post Processing”.

Please note that I have written this based on the assumption that MBC (Multi-bank connectivity) is not there in scope. If MBC is in scope, kindly refer to this SAP Help page.

Lockbox Post-processing

To understand this post processing (T-code FLB1), which comes into picture only in case lockbox program is not able to automatically clear/apply all available checks in the file, you may refer to this blog post I found, which details it very clearly. Also you can refer to SAP help content.

An example where the post processing would be needed can be – suppose a check was posted on-account due to no invoice reference details could be found. Or let’s say in the transmission file, a customer payment of $1000, for example, references 2 line-items in the payment advice without referencing an invoice number. The lockbox program generates the payment advice, but since there are no invoice number to match against the payment, nothing can be cleared.

Alternate Company Code based Payment Advice

One more aspect of Lockbox which is very common across all companies, is that – generally multiple company codes utilize the same lockbox account for customer collections. Whereas standard SAP Lockbox functionality allows posting within a single company code per lockbox. To facilitate such intercompany cash application, a user exit must be added to lockbox program RFEBLB20. This ensures an alternate company code is added in the payment advice. This allows matching customers and invoices in alternate company codes.

User Exit = EXIT_RFEBLB20_001 (it allows to change the complete payment advice)

User Exit = EXIT_RFEBLB20_002 (it is called up for every single invoice number that is transferred in the lockbox file). If five invoices are to be paid with one check, for example, and the invoice number is contained in the lockbox file, the exit is called five times. This user-exit gets called prior to lockbox processing, i.e. before the system checks whether the invoice number is valid or not. Therefore, there will not be any immediate or on-account postings before the system identifies the alternate (actual) company codes.

The user-exit is called only to populate the alternate (actual) Company code and not to map any algorithm to search and clear invoices.

Customer Remittance with more than 999 line-items

As we understand that Lockbox Program also generates a financial accounting entry, and there can be an error (FS 734), in case 999 line-items are exceeded. For this, SAP has created an OSS note 681901 to take care of this requirement, applicable to BAI2 file format.

This can also be handled in the user-exits I mentioned earlier – EXIT_RFEBLB20_001 & EXIT_RFEBLB20_002. The trigger ‘SPLT’ canmake the program split the payment advice every 200 positions. Should you like any other number, set E_AVIK-VORGC accordingly.

So, what happens is that – a check can pay a lot of invoices, but these invoices are only partially paid. As a result, e.g. residual items are created and stored as document line items. If the number of line items exceeds 999, the document cannot be saved. Here the suggestion is to modify the standard program, i.e. splitting the original check into two or more sub-checks with the same no. which pay the invoices. Each single check does not pay more than 200 invoices – thus, it is unlikely that the resulting payment document will have more than 999 line-items. Each sub-cheque has its own payment document – debiting clearing and crediting the customer, clearing invoices and setting up deductions, as necessary.

In Lockbox post-processing (transaction FLB1), you will see these sub-checks, each with an associated payment advice which now has an internal ID (FEBEP-KUKEY and FEBEP-ESNUM) to guarantee uniqueness.

I hope you would find this information useful and relevant for your next assignment / learning.

Your feedback & comments are welcome.