sap lock box process

LockBox Process in

SAP This document depicts a Implementation, Automation of Incoming Account Receivable process in SAP, called as “LockBox Process”

Surya Padhi

[Friday, 24/May/2013]

www.SuryaPadhi.Com | [email protected]

of 25

Lock Box functionality in SAP ECC 6.0

OVERVIEW : Banks offer lockbox service to help companies process incoming check payments. A company with a lockbox account asks its customers to send check payments directly to the bank. The bank processes the payment and transits the payment data electronically to the company either in the BAI or the BAI2 format. The information in the Lockbox file is matched to the accounts receivable information in the R/3 system to clear the payments against the open items. BAI and BAI2 formates defined by the Bank Administration Institute. In practice, the standards published by this organization are not followed strictly. Banks usually offer several variations of each format, including the BAI and BAI2 formats that R/3 reads.

The BAI2 format is newer and contains more information than the BAI format. The main difference between the two formats is the invoice information associated with each check.

• BAI Check, amount, invoice, invoice,...

• BAI2 Check, amount, invoice_and_amount, invoice_and_amount, ... The extra information on the BAI2 format allows better automatic matching of payments to customer open items when the data is imported. If automatic matching fails, your accounts receivable (A/R) clerk must manually clear the payments against open items.

When importing a BAI format file, unless the check amount matches exactly the total open invoice amount in R/3 (or within configured payment difference tolerances), none of the invoices clear and the entire check enters R/3 either as an:

• “On account” posting (if the payment and invoice totals do not match)

• “Unprocessed” posting (if no customer account and no documents could be identified) With BAI2 format files there is also a “partially applied” status. A check is partially applied when the import program can match and clear some, but not all, of the invoices within the check total. As a result, the hit rate—the percentage of matched invoices in each transmission—is higher with BAI2 than with BAI.

Advantages

• Quicker check collection, thus reduces customer check flot time.

• Improves cash liquidity of payee.

• Payees need spend less time on processing payments. Thus maximize man power utilization and reduces cost related to cash collection application.

Let us take a business case to explore the lock box functionality of SAP ECC 6.0.

Case : XYZ Inc. in manufacturere of desisel engines, sales its products in the retail market. For better cash management and speed of cash realisation, XYZ Inc. opened a lock box account with


of 25

its banker say Bank. All sales invoices of XYZ Inc has an endrosement for customer remit all payment to Post Box. No. 12345, Lasalle Ave, Chicago, 60001.

So every day, bank collects remittences from the lock box and process these remittences by credit to XYZ Inc.

At the end of the day bank generates a BAI2 file and transmit to a designated place normally a web server. XYZ Inc. Daily collects this BAI2 file from the web server and posts customer collection.

As a business pratice, XYZ Inc has set up a tolerence 1% for overpayment and underpayment. If XYZ receives customer payment within 1% of tolerance, these difference will be posted to under payment or over payment account by clearing customer open items.

If the customer remittence is outside of the tolerence then the said collection will be posted to customer as a on account payment. Subsequently on account payment will be cleared by account receivable clark based on futher information.

If system didn’t find customer information or didn’t match customer information, then the remittence will be credited to unapplied cash account and that can be cleared subsequently based on available information.

In the following page, I will explain various steps involved in the lock box prcess. These steps may vary depends on bank you are dealing with, the file format used by bank, file handling process, middleware used for conversion of bank transmitted file and configuration done.


of 25

PROCESS:

StepsStepsStepsSteps ProcessesProcessesProcessesProcesses

StepStepStepStep----1111 Customer: When XYZ Inc. bills customer ABC Inc. XYZ Inc. directs customer to mail check(s) along

with remittance advice to a post box owned by bank.

StepStepStepStep----2222 Bank: Everyday bank collects the checks mailed by customer from the designated post box and process

the check by giving credit to XYZ Inc.

StepStepStepStep----3333 Bank: Once checks are processed by bank, bank creates a flat file generally either BAI or BAI2 file and

send over to XYZ Inc. How the BAI / BAI2 file reaches to customer depends on service agreement

between bank and XYZ Inc.

StepStepStepStep----4444 XYZ Inc.: Once file received by XYZ Inc., XYZ Inc. imports BAI / BAI2 file through T.Code : FBL2. In

this step, SAP imports data and map the imported data

StepStepStepStep----5555 System generates (1) GL posting and (2) payment advices AR.

StepStepStepStep----6666 Payment Advices are used to clear AR open Items, if system matches customer and customer open items.

StepStepStepStep----7777 a. If system finds customer but amount doesn't match AR open items then, it post to customer as on on on on

account paymentaccount paymentaccount paymentaccount payment.

b. If system does not find customer then amount remains un-applied. Then in subsequent step user

processed unapplied cash manually ( T.Code: FLB1)

StepStepStepStep----8888 System updates customer master data for new bank information.


of 25

RFEBLB00: Transaction code FLB2 calls import program RFEBLB00. This is a umbrella program, carries following activities in the lock box process.

• Imports the file into bank date storage or from the specified location, • Generates the payment advices for clearing of subsidiary ledgers and does GL postings,

and • Generates a log of the checks and postings.

This program internally calls RFEBLB20 & RFEBBU00

RFEBLB20: This program map the imported data to standard SAP data structure such as FLB01, FLB02 etc according to BAI or BAI2 format. It then reads the data and uses data to create payment advices as per check submitted via lock box.

Each payment advice contains important data about incoming check such as amount, invoice number etc. Payment advices are stored in table AVIP

This program internally calls program RFEBLB30.

RFEBLB30: RFEBLB20 triggers this program through a batch job. This job generates a batch input session that can change customer master receords to insert new MICR numbers, whenever the customer is identified and MICR number is new. This functionality can be activated or deactivated based on the configuration.

RFEBBU00: program RFEBLB00 triggers program RFEBBU00. It carries out what SAP calls area specific postings depending on the configuration. Based on configuration this program post GL posting and utilizes payment advices created through program RFEBLB20 to clear customer open items. This configuration is available in the following path IMG ► Financial Accounting ►Bank Accounting ► Business Transactions ► Payment Transactions ► Lockbox ►Define Control Parameters

While performing posting, this program generates following account transctions based on configuration.

• General Ledger Posting: Bank Incoming Clearing Account Dr.

Unapplied Cash Cr.

• Subsidiary Ledger Posting:

Unapplied Cash Dr.

Customer Cr.

While processing pament advice, system uses payment advice information and search cusromer open item in the following sequence.

• Document number

• Reference number


of 25

If the system don’t find customer based on MICR number or based on Document Number or Reference number system updates AVIK. Based on customer open item search and maching found system updates the payment advice status as follows.

- Applied: System comes up with applied status when system able to find customer and able to find customer open items and open item matches with incomming payment.

- Partially Applied: Checks that are partially applied may require further processing. For example Customer has paid a check for 5 invoices, but system able to match 4 invoice and one invoice did not match. In this case system clears 4 invoices. The payment amount for the 5th invoice would be put on-account and would have to be post processed to clear.

- On account: In this case system is able to find customer but not the the open items( None). If the bank keyed in the correct invoice number, the Lockbox Import Program posts the payment on account. In the post processing step, you access the payment advice and correct the document number and upon saving the changes, the post process function clears the open item, deletes the payment advice and sets the check status to applied.

- Unprocessed: Any payment that could not be identified either by customer MICR number (check) or the document number would remain Unprocessed. Once the payment is researched and the customer and invoice is identified, it would be applied during post processing.

Now let us see, how to configure lock box functionality in SAP.

CONFIGURATION : 1. Define House Bank:

a. Transcation Code:FI12 b. Menu Path: IMG ► Financial Accounting ►Bank Accounting ► Bank

Accounts ► Define House Banks

In SAP a bank account is represented through with the combination of bank ID and account ID. House bank represented by bank ID while bank account is represented by a account ID. Bank ID and account ID are used for various banking transactions for example automatic payment program etc. In this step (1) you will create a bank master record (2) under that bank master master record you will create a bank account and (3) assign bank cash concetration GL account to bank account.


of 25


of 25

2. Define Lockboxes for House Banks: a. Transcation Code:OB10 b. Menu Path: IMG ► Financial Accounting ►Bank Accounting ► Bank

Accounts ► Define Lockboxes for House Banks

In this step, you define your lockbox accounts at the house banks. Thus, on the outgoing invoice you can inform your customer of the lockbox to which payment is to be made. By specifying this, you can optimize the payment transactions.

3. Define Control Parameters: a. Transcation Code:OBAY b. Menu Path: IMG ► Financial Accounting ►Bank Accounting ► Business

Transactions ► Payment Transactions ► Lockbox ►Define Control Parameters

In this activity you maintain control parameters for the lockbox procedure. Currently, only LOCKBOX procedure is supported by SAP and at the same time SAP understands only BAI and BAI2 format. Figure XX given below depicts what are control parameters you are going to maintain in this configuration step

As shown in figure above, SAP comes with pre delivered lockbox procedure LOCKBOX and input format BAI and BAI2. As per business requirement you can choose either BAI or BAI2. In this paper, I have considered BAI2 format.


of 25

Figure 1 Lock box control parameter -2

• Record format section is not applicable to BAI2 format. However if you are using BAI format, you need to maintain this section. Incase of BAI specify the length of the document numbers (10 in the standard SAP System) and the number of document numbers in record types 6 and 4 of the BAI file. Your bank must agree on this format information.

• In the posting section, o Check the check box for G/L account posting, o Check the check box Incoming customer payments for AR posting for cash

application.

NOTE: It is recommended that both choices are selected if you are using the general ledger and accounts receivable modules.

o Check the check box insert bank details to change customer master record to update MICR and account no.

o Gl account posting type: you can choose any one of the three options listed below based on business process.

� 1 One posting per check to the bank account � 2 One posting per lockbox to the bank account (check total) � 3 One posting per batch number to the bank account

o Partial Payment: In case of active check box, if the customer payment is insufficient system will post partial payment.


of 25

o If you checked Insert bank details, then you need to give batch sessions name, with which system will create a batch session to update the customer master with new or missing bank information.

NOTE: while the BAI and BAI2 formats are supposedly standard, experience has shown that they can vary by bank and many SAP clients have contracted for a customized format. The format received will need to be mapped to reconcile with the SAP delivered data dictionary layout (Tables FLB01, FLB05, FLB06, etc.) to ensure proper processing. If the format does not reconcile and the user does not want to have the bank change the format, SAP recommends that a user-written ABAP be used to reformat the file or alternatively the SAP data dictionary can be modified (this is a repair that will need to be re-applied in future releases).

Important Note: The lockbox program first attempts to identify the customer with unique bank information in the customer master record matching to MICR information on the check, so maintenance of unique customer MICR information is important to ensure success. If there is more that one customer with the same MICR bank and account, then the program cannot apply the check to an account. This situation usually means that either the same customer has been set up twice, or maybe what should have been only Ship-to or Sold-to customers for the same Payer have actually been set up as separate Payers. Alternatively, it may be necessary to set up a relationship where one customer is identified as the main Payer and has MICR info, and the related customers do not have MICR info but instead have the main Payers customer number in the Alternate Payer field of the customer master record.

The payment information provided in the bank data file will create a payment advice per check. This payment advice will be used by the lockbox procedure to clear subledger open items. For more information on payment advices, please read accounts receivable/accounts payable Payment Advice Notes in the IMG or On-Line Help.

4. Define Posting Data a. Transcation Code:OBAX b. Menu Path: IMG ► Financial Accounting ►Bank Accounting ► Business

Transactions ► Payment Transactions ► Lockbox ► 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. For every unique destination/origin, posting information is needed to create the following postings:

• G/L posting - Debit bank account (incoming checks) and credit payment clearing account • A/R posting - Debit payment clearing account and credit customer account


of 25

In addition to above configuration, you may need few additional configuration based on business requirement, few are listed below

5. Define Tolerance Groups for Employees: a. Transcation Code:OBA4 b. Path: IMG ► Financial Accounting ► Accounts Receivable and Accounts

Payable ► Business Transactions ► Manual Payment Receipt ► Define Tolerance Groups for Employees

This transaction is used to create tolerance groups for users. The tolerances are defined by company code and at a minimum there must be one assigned to each company code otherwise no postings can occur in the company code.


of 25

Employees tolerenace group determines who is authorized for what and permitted payment difference.

6. Define Tolerances (Customers/Vendors): a. Transcation Code:OBA3 b. Path: IMG ► Financial Accounting ► Accounts Receivable and Accounts

Payable ► Business Transactions ► Open Item Clearing► Clearing Differences ► Define Tolerances for Customers/Vendors

In this activity you define the tolerances for customers/vendors. The tolerances are used for dealing with payment differences and residual items that may arise when payment clearing is carried out. You specify the tolerances in one or more tolerance groups and assign a tolerance group to each customer/vendor using the master record.


of 25

7. Define Accounts for Overpayments/Underpayments: a. Transcation Code:OBXL b. Path: IMG ► Financial Accounting ► Accounts Receivable and Accounts

Payable ► Business Transactions ► Outgoing Payments ► Outgoing Payments Global Settings ► Define Accounts for Overpayments/Underpayments

In this activity you define the numbers of revenue and expense accounts to which the system posts if the following requirements are fulfilled:


of 25

• There is a difference in payment. The difference can result from an underpayment or an overpayment.

• The difference is within the tolerance limits for an automatic adjustment posting. • The difference cannot be posted via cash discount adjustments.

After completion of configuration, you need to maintain customer master data. In order to lockbox program to identify the custoer you must populate MICR and customer bank account number in the General Data: Payment Transaction screen of costomer master data

After completion above configuration and master data set up, you are ready to upload BAI2 file into system. To upload BAI2 file following following steps.

USE: 1. Maintain Customer Master:

a. Transcation Code:FD02 b. Path: SAP ► Accounting ► Accounts Receivable ► Master Records ► FD02 –

Change. Inorder to identify customer, we need to maintain bank details in the customer master.


of 25

The above screen shows that customer master has all necessary bank details. We can have multiple bank account reference in the cistomer master record.

2. AP Open Items: a. Transcation Code:FB70 b. Path: SAP ► Accounting ► Accounts Receivable ► Document Entry ►

Invoice. Since though lockbox you are getting incoming payment against customer open items. It is expected that before lockbox posting atleast one open items.


of 25

In this step created a AR open item with document number as 1800000398. In our subsequest step we’ll clear the open item through lockbox process.

3. Import the Lockbox File c. Transcation Code:FLB2 d. Path: SAP ► Accounting ► Financial Accounting ► Banks ► Input ►

Lockbox ► Import

In BAI2 format the file uses “Record 6” as an identifier of the check dollar amount, the MICR number and the check number. No Invoices are contained in this record. Following the “6 Record” are a series of “ 4 Records” which contains one Invoice each. These provide the Invoice number, Invoice open item amounts ( gross), deduction amount and External reason code.

Once you process lockbox BAI2 file system generated either of following 4 statuses for each of 6 record types.

1. Applied 2. Partially Applied 3. On Account 4. Unprocessed.

Refer previous section reason of havong various status.


of 25

Above screen shows that we are importing a lockbox file from our desk top. Once the file is sucessfully imported system will come up with screen appedned below.

Below screen shows the result of lock box import. It gives all information about lockbox file, customer indification and application of lock box remittences.


of 25

The above screen shows system has created 2 financial accounting document i.e 100013806 and 1400000239. Financial Document 100013806 is for GL posting while financial document 1400000239 is for sub ledger posting. On the above scree we can see the status is applied that mean

• system found the customer;

• Systm was able to locate AR open items;

• Incoming payment matches with Open AR after taking into account employee and customer tolerances.

We can see additional information on lockbox processing by choosing F3 two times. Below lockbox log display main lockbox program log.

In below screen figure we can see system has selected the right customer by matching MICR number.

Based on document number information system has identified the relevant AR open item.


of 25

After sucessful lock box processing , we can see that customer open has been cleared as shown below by document number 1400000239.

Accounting document 1400000239 was posted to 113109 by crediting customer 300299.


of 25

Now further look into the lockbox information. To see more information execute tramsction code FLB1 with origin and destination selection. On Lockbox : Proess check screen we can see all al processed lockbox files arranged in date and time.

All sucessful processed file will appear as green indicator while unprocessd or partially processed files will appear to be processed icon.


of 25

Look into the sucessfully processed steps and expend it. Now we can see financial document and payment advice what we have discussed earlier.

Here is the Sample Lock Box File I loaded;

100LOCKBOX0011343290421305141210

2LOCKBOX001999123450

50010011234567130514LOCKBOX001134329042

6001002000012000002100008955566677 77990019

400100360171800000398

700100412345671305140010000120000

8001005123456713051400010000120000900001200000000120000

9000008

Now move to the previous one which is not processed and partially processed, expend it. In this case we can see that system has created two account document. In this case the subledger document is appearing in PmtOnAcct field. Do you know what is the reason? To know the reason docuble click on payment advice number.

In this case system found the customer and not the relevant opein item, hence posted on account, which is shown below.


of 25

I hope you enjoyed it. Now it is very imporant to understand how BAI2 file is orgnized and its layout.

Lockbox File Format: As I said earlier ECC 6.0 supports both BAI and BAI2 lockbox format. Now it is very important to understand BAI and BAI2 format before before implememation or support of lockbox functionalities. In this section I will try cover both lock box formats.

BAI format:

BAI is defined in the repository with the following record structures:

• FLB01: Header record

• FLB02: Service record

• FLB04: Overflow record

• FLB05: Detail header record

• FLB06: Detail record

• FLB07: Batch total record

• FLB08: Lockbox total record

• FLB09: Trailer Record.

You can set the number length and number of invoice numbers (or purchase numbers) in customizing. These settings can deviate from structures FLB06 and FLB04. As long as you use


of 25

FI document numbers in transmitting numbers, leading zeros are automatically added by the program. The invoice numbers should be transferred in left-justified form.

BAI2: The BAI2 format is a US national standard for bank reporting. Most of the US Bank supports the BAI2 format. Detailed information on the BAI2 report format can be downloaded by visiting the Bank Administration Institutes website at http://www.bai.org/operations/faq.asp.

BAI2

The only difference between BAI2 and BAI is that BAI2 has a check record and an overflow record. The SAP system contains the following table structures for these record types.

• FLB24: Overflow record

• FLB26: Detail record

BAI2 can transmit data about the invoice, such as the payment amount, deduction amount and the reason code. Note that either the payment amount or the deduction amount can be transmitted for an invoice. If both are transmitted, the deduction amount is ignored. Invoice numbers (or purchase order numbers) should be transmitted in left-justified form. As long as FI document numbers are used, the program will automatically add leading zeros.

Using BAI2, the system will usually be able to produce a greater number of clearing entries and thus reduce the amount of time you will need to spend in the manual postprocessing transaction. You can have your lockbox provider create the BAI2 format by poviding them with the record description, or you can convert your lockbox provider's format to BAI2 by using a preprocessor. Typical BAI2 lock file comes with below record layout.

BAI2 Lock Box Record Definitions:

• Type 1 - File Header Record

• Type 2 - Service Header Record


of 25

• Type 4 - Overflow Record: Provides a means of handling variable quantities of detailed information. When the remitter furnishes more remittance information than can be recorded in the type 6 Detail record, the type 4 Overflow record is used for this data.

• Type 5 - Lock Box Detail Header: Used to reduce the size of detail records by carrying the required information that is common in all the detail records that it precedes.

• Type 6 - Detail Record: Contains data belonging to 1 remittance (MICR account, check amount, check number). In addition, some companies that use a Type 4 record often utilize the blank portion of the Type 6 record for invoice information as continued in their Type 4 records.

• Type 7 - Batch Total Record

• Type 8 - Service Total Record

• Type 9 - File Trailer Record

OSS Notes:

OSS note 105231: The lockbox program can search by either invoice number or reference document number. It has performance issues, however, if it searches by reference document number and the reference document happens to have a null value document number or is a reversal document, which may inherit the same reference document number from the original invoice.

OSS note 561171: This note should be applied when customers submit partial payments. If not applied, whenever a check contains some invoices that are partially paid and all other invoices are invalid document numbers, the lockbox program will terminate with the message Error in Routine INPUT_PRUEFEN.

OSS note 451121,437490, 391311, 358959: These notes should be applied if customers make multiple partial payments for one invoice at different lockbox runs. Without the note applied, the second partial payment won’t be able to be applied to the original invoice. Instead, it will clear off the first partial payment item and post the total amount of two partial payments to account.

OSS notes 458841, 407799, and 160257: These notes fix a clearing issue that is triggered when a customer is referencing a credit memo, but only takes the credit amount partially. Error message Exchange rate diff.accts are not defined for account & currency & appears in the lockbox posting log. The error message makes no sense since there are no foreign currencies involved during the whole billing and clearing process.

References:

• Lockbox functionality in Oracle Application, by Anil R Patel.

• Historical Development of BAI format, Wikipedia.

• Concept of Lockbox, wiki.sap.sdn


of 25

Lockbox Table References:

FEBEP - Electronic Bank Statement Line Items

FEBKO - Electronic Bank Statement Header Records

AVIP - Payment Advice Line Item

AVIK - Payment Advice Header

Lockbox Transaction Codes:

FBE1 - Create Payment Advice

FBE2 - Change Payment Advice

FBE6 - Delete Payment Advice

FBE3 - Display Payment Advice

FBE7 - Add to Payment Advice

FLB2 - Import Lockbox File

FLB1. FEBA_LOCKBOX - Postprocessing Lockbox Data

FLBP - Post Lockbox Data.

If you are looking for sample BAI2 file and a tool to create your own BAI file visit www.suryapadhi.com.

sap lock box process

Economy & Finance