LOCK BOX PROCESSING IN SAP - Include details for Cross- Company processing

CONCEPT:

Overview A company can create accounts called 'lockbox' at its bank (or banks) that act as payment collection accounts for customer payments. The company then informs their customers that all open item payments for their accounts must be submitted to one of the established bank lockbox accounts. The bank collects these payments along with the customers' remittance information that indicates what open items the customer payments intend to clear. Data entry clerks at the bank manually enter the information into an electronic file for transmission to the company to which the lockbox account belongs. These files are typically transferred nightly to the various lockbox owners (companies). The files adhere to one of two standard banking industry transmission formats: BAI, BAI2 (Bank Administration Institute).

Advantages of Lockbox Following are some of the advantages of using the lockbox: Manual Handling of checks can be avoided Checks can be processed in time. Easy reconciliation Clearing Errors can be avoided

Difference between BAI and BAI2 BAI and BAI2 formats differ in their level of information detail. BAI does not separate out the incoming check line items by invoice subtotal reference. Instead, one check total amount simply has all invoices listed underneath it. Thus, in BAI format files, the entire check amount must match perfectly (or within configured payment difference tolerances) the total amount for all invoices listed. Otherwise, the entire check will enter into SAP as: an "On account" posting (if the payment and invoice totals don't match), or An "Unprocessed" posting (if no customer account and documents could be identified from the transmission). In these scenarios, your Accounts Receivable cash application clerks will have to perform manual application to clear payments against open items on the proper accounts. Conversely, BAI2 splits the check total into separate invoice references and associated payment amounts. Thus, within a large batch, BAI2 format files will allow a "Partially applied" status in which some identifiable payments within the check total will be matched and cleared, others will land on account. As a result, your 'hit rate' percentage of payment-invoice matching from each transmission is likely to be higher when using BAI2 rather than BAI formats.

Process Flow

Steps for lock box setting

Lock Box setting:There are three lockbox settings:

Defining lockboxes Defining control parameters Defining posting data

Define Lockboxes:Financial Accounting -> General Ledger Accounting -> Bank-Related Accounting -> Bank Accounts -> Define Lockbox Accounts at House Banks. Transaction: OB10.

In this transaction, 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.

To configure the lockboxes first specify the lockbox links (company code; key of the lockbox to which the customer is to pay; house bank ID; lockbox number at your house bank). Second enter a remit to address that will appear on the customer invoice.

Define Control Parameters: SPRO -> Treasury -> Cash Management > Business Transactions -> Lockbox -> Define Control Parameters. Transaction: OBAY

The top "Record format' settings are unnecessary for BAI2 format, and should be left blank.

Procedure - LOCKBOX (currently the only supported procedure in standard system)

Record Format - BAI or BAI2

Record Format Parameters: (Required for BAI Only)Document # length - length of the document # used to locate the open itemNumber of document numbers in record type 6 - maximum # of documents in detail recordNumber of document numbers in record type 4 - maximum # of documents in overflow record

Posting Parameters:G/L Account Postings: Identifies whether G/L Posting is created.

G/L Account Posting Type: (1) = Posting / Check or (2) Posting / Lockbox Incoming Customer Payments: Identifies whether to post to customer accounts also.

BAI2 Format for Lockbox

Overview A wider knowledge about the file structure you are uploading to your SAP ERP allows you to avoid many problems those looks like to be application issues. In the following sections you will be able to understand better the BAI2 structure.

BAI2 Structure

Record 1 - Header Record 2 - Service Record 5 - Detail Header Record

Record 6 - Detail Record Record 4 - Overflow Record Record 7 - Batch Total Record (Some banks process groups of checks in batches) Record 8 - Lockbox Total Record Record 9 - Trailer Record

Detailed information regarding each record can be found through Transaction Code SE11: FLB01 - Header

FLB02 - Service

FLB05 - Detail Header Record

FLB26 - Detail Record

FLB24 - Overflow Record

FLB07 - Batch Total Record

FLB08 - Lockbox Total Record

FLB09 - Trailer Record

Execution of Lockbox ProgramThe lockbox program can be accessed using transaction FLB2 or program RFEBLB00.

In the PRD environment a variant exists for each lockbox file. For test purposes it is easier to copy the PRD variants, changing only the location of the lockbox file. However, before executing the lockbox program please confirm that each of the fields is completed as indicated.

Import into Bank Data Storage – this field should be selected so the lockbox file will be imported into the bank storage data. This ensures that the same lockbox file cannot be accidentally imported more than once (based on Origin, Destination, Date and Time values).

PC Upload – this field should be selected if the lockbox file resides on the users PC rather than in a UNIX directory.

Lockbox File – enter the location of the lockbox file in this field. The location may be either on the user’s PC or in the UNIX directory.

Procedure – this field should be populated with LOCKBOX.

Input Record Format – this field should be populated with BAI2.

Invoice Numbers – this field may be populated with a '4'. This field offers four standard open item identification algorithms. 1 – match on document number (BELNR) 2 – match on reference number (XBLNR) 3 – match on document number first, if not found then on reference number 4 – match on reference number first, if not found then on document number

Enhanced Invoice No. Check – this field should not be selected. If selected it allows postings to be made across customer worklists.

Algorithm: checks with advice and Algorithm: checks without advice – these fields should be populated with 001. These fields determine whether cash that does not find an appropriate open item should be posted as 'On Account cash', or be applied against oldest open items on the account.

Account assignment fields – these fields should be left blank. The values entered in the fields are defaulted on the postings created by the lockbox program.

Print Control – this field should be selected so the lockbox reports will be output.

Payment advice processing

Matching of customer open itemsThe lockbox program uses detailed information from the payment advice to automatically search and match customer open items. The document number on the payment advice is matched against the document number in the customer open item file. Therefore, accurate payment data is necessary for automatic clearing to take place.

Payment Advice StatusIf the checks were applied or partially applied, the advice is deleted from the system after processing. If

the check was unprocessed or placed on account of customer, the advice is kept on file for further processing.

Post Processing The post process function entails reviewing the status of the checks applied through the lock box function. User must manually clear any checks that were on-account of customer or not applied to customer account.The Lockbox overview screen details the number of checks in each category. Depending on the status of the check, the user determines what needs to occur to apply checks.

-On account: 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. -Partially Applied: Checks that are partially applied may require further processing. Ex: Check may have paid 5 invoices, but one was incorrectly keyed. The first 4 invoices would clear. The payment amount for the 5th invoice would be put on-account and would have to be post processed to clear. -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.

Cross-Company Code Lock Box Processing Standard SAP Lockbox functionality allows postings within one company code. To clear open items cross multiple company codes, a user exit must be written/inserted within program RFEBLB20.

The intercompany posting can only work with a User Exit which fills e.g. the field alternate company code (AVIP-ABWBU) in the payment advice. The User Exit '001' in function exit_rfeblb20_001 allows you to change the complete payment advice.The interface looks as follows:* call userexitcall customer-function '001'exportingi_avik = avikimportinge_avik = aviktablest_avip = xavipt_avir = xavir.

Within this user exit it is necessary to determine the alternate company code and the information needed to post such intercompany clearings.

Lock Box User Exit:

04/03/12 ERD Program ZXF01U03 Page 1 ------------------------------------------------------------------------------------------------------------------------------- 1 *&---------------------------------------------------------------------* 2 *& Include ZXF01U03 3 *&---------------------------------------------------------------------* 4 *&---------------------------------------------------------------------* 5 * Object ID : ENFFIN029 6 * Program Title : Lockbox file processing for Cross Company Code 10 *----------------------------------------------------------------------* 11 * Description: : This Enhancement will post Customer Accounting 12 * document for cross company codes 13 * Type: : Enhancement 14 * Run Frequency: : Daily 15 * Development Class: : ZFIN 16 * Requirement Ref. : Configuration/Development Objects/ 17 * Enhancement/ENFFIN029 (SOLMAN Path) 18 *----------------------------------------------------------------------* 19 20 *----------------------------------------------------------------------* 21 * DECLARATION REQUIRED FOR IMPORTING 22 *----------------------------------------------------------------------* 2324 DATA: febep LIKE febep, 25 i_swert LIKE avip-swert. 26 *----------------------------------------------------------------------* 27 *----------------------------------------------------------------------* 28 * TYPES DECLARATION 29 *----------------------------------------------------------------------* 30 TYPES: BEGIN OF ty_bsid, 31 bukrs TYPE bukrs, 32 kunnr TYPE kunnr, 33 belnr TYPE belnr_d, 34 END OF ty_bsid, 35 36 BEGIN OF ty_knbk, 37 kunnr TYPE kunnr, 38 bankn TYPE bankn, 39 END OF ty_knbk. 40 *----------------------------------------------------------------------* 41 * INTERNAL TABLES DECLARATION 42 *----------------------------------------------------------------------* 43 44 DATA: gt_bsid TYPE STANDARD TABLE OF ty_bsid, 45 gt_knbk TYPE STANDARD TABLE OF ty_knbk. 46 47 *----------------------------------------------------------------------* 48 * WORK AREA DECLARATION 49 *----------------------------------------------------------------------* 50 DATA: gs_bsid TYPE ty_bsid, 51 gs_knbk TYPE ty_knbk. 52 53 *----------------------------------------------------------------------* 54 * CONSTANTS DECLARATION

55 *----------------------------------------------------------------------* 56 CONSTANTS: gc_belnr(5) TYPE c VALUE 'BELNR', 57 gc_xblnr(5) TYPE c VALUE 'XBLNR'. 58 59 60 * Import from Memory ID's for Non-Availability 61 IMPORT febep FROM MEMORY ID 'LOCK'. ------------------------------------------------------------------------------------------------------------------------------- 04/03/12 ERD Program ZXF01U03 Page 2 ------------------------------------------------------------------------------------------------------------------------------- 62 IMPORT i_swert FROM MEMORY ID 'SWERT'. 63 64 * Get Accounting Details of Customer by passing Accounting document number 65 66 IF t_avip-sfeld = gc_belnr. 67 SELECT bukrs 68 kunnr 69 belnr 70 FROM bsid 71 INTO TABLE gt_bsid 72 WHERE belnr = i_swert+0(10). 73 74 * Get Accounting Details of Customer by passing Reference document number 75 ELSEIF t_avip-sfeld = gc_xblnr. 76 77 SELECT bukrs 78 kunnr 79 belnr 80 FROM bsid 81 INTO TABLE gt_bsid 82 WHERE xblnr = i_swert+0(16). 8384 ENDIF. 85 86 87 IF sy-subrc EQ 0. 88 SORT gt_bsid BY bukrs kunnr belnr. 89 ENDIF. 90 * Get Customer Bank details 91 IF gt_bsid[] IS NOT INITIAL. 92 SELECT kunnr 93 bankn 94 FROM knbk 95 INTO TABLE gt_knbk 96 FOR ALL ENTRIES IN gt_bsid 97 WHERE kunnr = gt_bsid-kunnr. 98 IF sy-subrc EQ 0. 99 SORT gt_knbk BY kunnr. 100 ENDIF. 101 ENDIF. 102 * Identify the Correct Customer by matching the bank details 103 sort gt_bsid by kunnr. 104 LOOP AT t_avip. 105 READ TABLE gt_bsid INTO gs_bsid WITH KEY kunnr = t_avip-konto binary search. 106 READ TABLE gt_knbk INTO gs_knbk WITH KEY kunnr = gs_bsid-kunnr BINARY SEARCH. 107 IF sy-subrc EQ 0. 108 * Check bank account number of customer

109 IF gs_knbk-bankn EQ febep-pakto. 110 t_avip-abwbu = gs_bsid-bukrs. 111 t_avip-waers = i_avik-waers. 112 * Modify if the record is found. 113 MODIFY t_avip. 114 ENDIF 115 ENDIF.116 ENDLOOP. 117 * Free Memory ID's 118 FREE MEMORY ID 'LOCK'. 119 * Move Import Structure to Export Structure 120 MOVE i_avik TO e_avik. -------------------------------------------------------------------------------- 04/03/12 ERD Program ZXF01U04 Page 1 -------------------------------------------------------------------------------- 1 *&---------------------------------------------------------------------* 2 *& Include ZXF01U04 3 *&---------------------------------------------------------------------* 4 *&---------------------------------------------------------------------* 10 *----------------------------------------------------------------------* 11 * Description: : This Enhancement will post Customer Accounting 12 * document for cross company codes 13 * Type: : Enhancement 14 * Run Frequency: : Daily 15 * Development Class: : ZFIN 16 * Requirement Ref. : Configuration/Development Objects/ 17 * Enhancement/ENFFIN029 (SOLMAN Path) 18 *----------------------------------------------------------------------* 19 20 *********************Required for Execution******************** 21 DATA: febep LIKE febep. 22 * Import Structure from Memory ID for Non-Availability 23 IMPORT febep FROM MEMORY ID 'LOCK'. 24 * Export Structure to Memory ID25 EXPORT i_swert TO MEMORY ID 'SWERT'. 26 *Pass the Company code and Customer Number to Export parameters 27 e_bukrs = i_bukrs. 28 e_kunnr = febep-knrza. 29 e_swert = i_swert. 30 e_sfeld = i_sfeld. 31 32 *Free Memory Id 33 FREE MEMORY ID 'LOCK'.

Testing scenarios of Cross Company Lockbox

1) Single customer in multiple company codes with “same Reference with different amount”.

When the reference key is same in both the company codes for the same customer, document is posted in the 1000 company code (Receiver Company code) for both processed and unprocessed open item through lock box program. However cross company code posting happens only for the open item cleared through lockbox program. That means when the open item is processed then the lockbox program showing the status as Applied and it will clear the open item from the customer. For unprocessed status in lock box program generates payment advice and it will post entry in the Receiver company code 1000 only.

2) Single customer in multiple company codes with “same Reference with same amount”.

When the reference key is same for the customer in the different company codes with Same amount, then documents will be posted in Company code 1000. When amount is same, then the first line item in the file will be cleared and another line item will be posted on account in the company code 1000. When the amount is same then unclear item will be posted in “payment on Account” in the Receiver company code 1000. Note: If you go to FBL1- Lockbox process checks, there you can see the following like Unprocessed will show in Red color Processed on account will show in Blue with Square box

3) Single customer in multiple company codes with different reference

When the reference key is different for the same customer in different company codes, then the open items will be cleared in each company code for the same customer and clearing documents will be posted. When the reference keys is different for the same customer in different company codes then in 1000 Company code both GL and AR document will be posted as the lock box program shows the status as applied.

4) Partial Payment

The main lockbox program does not process the partial payments and shows the status as Unprocessed. Documents will be posted in 1000 company code and open items are un cleared.. It is a Standard Business process.

FAQ

What Bank will do?Bank Receives the payments, create a data file of the customer remittance information and payment amounts, and deposit the checks into client bank account. On regular basis, Client Company receives this data file for processing to update in their accounts.

What lockbox data file contain?Depending upon the choice of services with the Bank, the lock box file will contain information viz., Customer name, Customer Number, Customer MICR number ( Bank routing and Account Number), Check amount, Invoice number, Payment date, Payment amounts and other information.

What is the Lockbox Data Flow?Customers send their payments to a lockbox. Then bank collects the data and sends (either through EDI 820 and 823 formats) to R/3 users’ EDI server (standard Process). The server translates the message using as standard EDI interface into an IDOC (Intermediary documents) and sends it to the SAP Server.

What happens in SAP server?Once the message is received and stored in SAP table, a program is run to check the information stored in bank statement tables and create payment advices with Payment amount, invoice numbers and customer number.