Configuring Lockbox for Cash Application in SAP FICO

The United States, unlike much of the world, still relies heavily on paper checks for payments. In order to facilitate internal control and to streamline processing, many companies utilize a lockbox service at a bank. In this article, we’ll examine the process flow for cash application via lockbox, discuss a few keys to success, and then examine the configuration and technical details in SAP.

Lockbox Process
  1. A customer orders products from a company. The company sends the products to the customer.
  2. The company invoices the customer. On the invoice, the vendor includes a voucher. The voucher includes the invoice number and customer number.
  3. The customer mails their check with the voucher to the bank that the lockbox is setup with.
  4. The bank deposits the cash. The bank’s staff will capture the information off the customer’s check, possibly the information on the voucher, and possibly an image of the check.
  5. The bank sends the company a file with all of the captured payment information.
  6. The company uses the payment file from the bank in order to apply payments against the customer invoices.
  7. Any errors during step 6 must be manually researched and resolved.
BAI2 Overview for SAP Lockbox

Setting up lockbox processing starts with getting a correctly formatted file. SAP requires the file to be formatted in what it calls the BAI2 format. This is not the BAI2 layout that the rest of the world considers the BAI2 format. It is the BAI2 format for lockboxes. SAP thankfully gives us the layout for the file, but not a lot of detail about what is going on. From the help documentation in FLB2, we see that the layout of the file is contained in tables FLB*

SAP FICO Lockbox record layout

You can view the fields in each of those tables to see what fields should be contained in the file. The lockbox file if a fixed width text file. Even if the field is not being used, spaces should be added to the field. All of the meat of the file is in record types 5, 6, and 4. Record type 5 contains the lockbox information, record type 6 contains the check information as well as any invoice numbers that are being cleared. Record type 4 is an overflow of record type 6 and is used for adding additional invoices if they do not all fit in record type 6.

An example of the layout from the SCN wiki is below. You can see that the record type is the first digit of each line.

BAI2 Lockbox Format Example

This workbook details what each field does in each record type. Note that I had to work this out by reading the ABAP of RFEBLB20, so be aware that it may not be exactly perfect, but it’s a very good start.

Some banks are able to actually send the file in the correct format. Other banks are not able to do so and instead a custom program will need to translate the file out of the bank’s format and into the BAI2 lockbox format that SAP expects.

SAP Lockbox Configuration

The first step in the configuration is to set the control parameters for lockbox processing. This step is usually already done by default in most ECC 6.0 installations, but you should double check the settings.

Lockbox control parameters for SAP FICO(1)SPRO->Financial Accounting->Bank Accounting->Business Transactions->Payment Transactions->Lockbox->Define Control Parameters

The most important setting here control the postings in the lockbox process. The standard set of journal entries are:

Dr Bank Account

Cr Incoming Payments

Dr Incoming Payments

Cr Customer Account

The settings in this config screen will determine how and if the postings are made. Have a look at the help text for each for more in depth discussion. The next screen has the setup for each individual lockbox.

SAP FICO Lockbox Posting Parameters(2)SPRO->Financial Accounting->Bank Accounting->Business Transactions->Payment Transactions->Lockbox->Define Posting Data

Here, the origin and destination that is coded in the lockbox file is entered. Additional parameters such as GL accounts to use for the bank and clearing accounts, document types, and posting keys can be selected. If you choose to code this activity to a house bank account that you setup in FI12, then it can be connected here.

That should give us enough configuration to process a lockbox file. There are other configuration items such as reason codes in AR, default profit centers in the GL, house banks in cash accounting, and disputes in FSCM Dispute Management that integrate here as well, but these are a separate topic and will make a long post even longer.  Let’s look at the lockbox processing screen:

SAP Lockbox Posting FLB2(3)FLB2

A couple of key items here that impact processing in a big way. The procedure and format should match what is configured in our first config screen. The field “Invoice Numbers” is incredibly important as it determines how the invoice numbers on the lockbox file will be matched to the FI document numbers.

Any items that do not match an invoice after FLB2 is run will show up in FEBA_LOCKBOX. This screen is used for post processing and fixing errors.

Keys to Success in SAP Lockbox Processing

There are a few critical points in the process to ensure that lockbox cash application has a high hit rate.

  1. Bank Integration: The bank who is providing the lockbox file must be on board with providing enough information. Similarly, the treasury department must be willing to pay the fees to capture the information
  2. Cross CoCd Payments: If the lockbox belongs to one company code and the payments are being received in another, then enhancing a user exit will be necessary.
  3. Updates during post processing: Each time that a payment is applied manually, the customer’s bank information should be updated. This approach requires enhancement.
  4. Routine error handling: All lockbox issues and unapplied cash should be examined on a daily basis. If these problems back up, it can make for an ugly close at month end.

Hopefully this information gives you a decent sense of how to configure lockbox for SAP. Feel free to leave comments or questions.

References   [ + ]

1. SPRO->Financial Accounting->Bank Accounting->Business Transactions->Payment Transactions->Lockbox->Define Control Parameters
2. SPRO->Financial Accounting->Bank Accounting->Business Transactions->Payment Transactions->Lockbox->Define Posting Data
3. FLB2

By |August 3rd, 2017|SAP|0 Comments

Read More

The dangerous transaction: SE16

I was once asked an astute question in an interview about transaction SE16:

“David, how do you feel about giving end users access to SE16 in SAP ECC?”

My answer to the interviewer was three fold:

  1. End users should not have access to SE16 (or its variants SE16N, SE17, etc) in SAP ECC because the necessary authorizations are not present to limit access to sensitive data. That means end users could have unfettered access to sensitive financials, payroll, human resources, and project information. Depending on the authorizations given,  end users could even change tables and configuration directly. Thus it’s not appropriate to give SE16 or SE16N to end users.
  2. Giving table access to end users usually implies that there is a deficiency in the reporting solution. I usually see this scenario where a robust ad-hoc reporting package was not in scope or not delivered. In recent times, it’s much better to deliver ad-hoc reporting through SAP Business Objects on top of SAP BW or ECC on HANA than to rely on only ECC ABAP reports.
  3. If all else fails and a reporting solution is not in place in the short term, then there are the old school reporting tools in SAP:
    • A standard transaction can often be manipulated into a ‘good enough’ solution. That might mean making a new standard layout in FAGLL03 or S_PL0_86000030 in FICO or ME2L or ME2N in procurement.
    • Report painter or :shiver: report writer can be used with either a standard or custom library to build a custom report. Often, there are predefined key figures that can save a lot of trouble. Report writer usually leads to tears of frustration though.
    • SAP Query through SQ01 and SQ02 can be used to join tables together and present a useful layout. SAP Query is very handy for reports without complex requirements.
    • FinallyABAP programs can serve in a pinch for more complex reporting requirements when the above options all fail.

With all of the above options, there is no reason to give end users access to SE16 or SE16n and risk the disclosure of sensitive data!

By |August 2nd, 2017|SAP|0 Comments

Read More

Currency types in SAP FI

Understanding currency types in SAP FI is critical for both users and consultants. If a user does not understand the currency types, then they will book entries wrong. If the consultant does not understand them, then serious valuation problems can result.

Consider the following:

We have a Mexico affiliate of a US corporation that is procuring supplies from Germany. The contract is denominated for 500 EUR, but the company reports tax locally in MXN, and operating results from all countries are consolidated into USD. 

What currency should the contract be tracked in? The answer, of course, is EUR, MXN, and USD. SAP gives us the flexibility to account in different currencies for different purposes.

Currency Types in SAP FI

Document Currency

The document currency is the currency of the transaction which depends on the structure of the transaction. In our example, we’re legally obligated to pay 500 EUR. Thus our document currency is EUR. This currency is actually set on the document at the time of the transaction. When we start FB60 to book our vendor invoice, we select the document currency of EUR.

Document Currency on FB60 (1)FB60

Local Currency

The local currency is the currency of the company code which represents the legal entity in a ‘standard’ SAP configuration. This currency is used to comply with local tax reporting requirements as well as representing the functional currency as seen in FAS 52 or IAS 21.  In our example above, the functional currency for a Mexico entity is most likely MXN. That said, according to FAS 52 or IAS 21, if we suppose the primary business to be exporting ot the USA, then the functional currency might be USD. In any case, the currency is set on the company code as you can see below configuration screen.

Company Code Currency (2)Defined when you create the company code in SPRO->Enterprise Structure->Definition->Financial Accounting->Edit, Copy, etc Company Code

Group Currency

To enable reporting across all entities in an SAP environment – for doing comparative metrics and quick estimations – you can use the group currency. Since our example entity is based out of the USA, then the USD would almost certainly be the group currency. The group currency is set at the client and is consistent across all company codes. You can see the configuration below.

Currency for entire client (3)Basis can modify the client in SCC4

Tying it all together

In the configuration, you specify which currencies are relevant for which company codes. Notice that you can have up to three currencies for the company code in addition to the document currency. That allows for inflation accounting and other currency types that we haven’t covered here. In this very standard setup, we have the first currency set as the company code currency (the local currency) and the second currency set as the group currency. That means, for any transaction, we’ll have a document currency, the currency of the legal entity, and the currency of the entire enterprise.

 (4)SPRO->Financial Accounting New->Financial Accounting Global Settings New->Ledgers->Ledger->Define Currencies of Leading Ledger

Now, when we post a document, we can see that each currency type is being calculated and tracked – even if we don’t explicitly enter it!

JE with multiple currencies (5)FB60

References   [ + ]

1, 5. FB60
2. Defined when you create the company code in SPRO->Enterprise Structure->Definition->Financial Accounting->Edit, Copy, etc Company Code
3. Basis can modify the client in SCC4
4. SPRO->Financial Accounting New->Financial Accounting Global Settings New->Ledgers->Ledger->Define Currencies of Leading Ledger

By |July 13th, 2017|SAP, SAP FI|0 Comments

Read More