Performing GL Conversions Correctly Part I

It amazes me that GL conversions go so wrong so often. The recipe is straightforward and if the legacy data is good enough, there is rarely an excuse to have trouble during GL conversions. Yet, I’ve seen quite a few go off track. So in this series of posts, I’d like to talk about the critical points to doing them correctly.

What we’re converting

In general, most GL conversions are done on a period by period basis to ensure that the trial balance, P&L, and balance sheet are correct for the periods in question. Usually line item detail is left behind in the legacy system. So at the end of the conversion process, I should have worksheets that tie out each new account balance drilled down to the relevant cost objects and profit center for each period.

Let’s suppose that we have a GL conversion from a legacy system to our SAP GL that is supposed to span three years of data – 2011, 2012, and 2013 (YTD). We cannot just start with 2011 though. The end balance sheet for 2010 must be loaded to ensure that the starting position is correct. The P&L for 2010 does not necessarily need to be loaded as long as retained earnings is loaded correctly.

The GL conversion process

GL Conversion Process (ETL)

The GL conversion process is usually done in three steps that follows the acronym ETL. First, the existing GL balances are extracted from the legacy system. The balances data is placed into a staging location. This location could be a simple as a flat text file on a file server or as complex as complete database system with a web front end.

Next, the data in the staging table is transformed from the legacy GL characteristics to the new SAP GL characteristics. This step is accomplished with mapping tables that take the legacy value onto the SAP value.  These mapping tables can become exceedingly complex if the logic is ugly.

Third, the transformed data is loaded into the new SAP system. This step is usually done with an LSMW that injects the standard GL posting program, but I’ve also seen other methods such as Idocs and recordings. At this point, all of the GL data should be loaded into the SAP system.

Key checks in a GL conversion

Throughout the GL conversion process, it’s important to ensure that each step succeeds. At the absolute bare minimum, when the data is loaded into the new SAP system, it’s critical to ensure that legacy balances match the new SAP balances per each costing object and period and in each relevant currency. It’s much better though to make checks throughout the process. Often times, a few heuristics will detect problems much earlier. A couple suggestions:

  1. Debits=Credits. If your trial balance doesn’t balance at each step, something is obviously wrong.
  2. P&L matches. The P&L should always match throughout the process. If it doesn’t, something went wrong.
  3. Assets, Liabilities, and Equities match. Each major class should
  4. All values mapped. Once the transformation step is complete, all accounts, company codes, cost objects, etc should be mapped. If any aren’t, the mapping table isn’t complete or there is a bug in the transform logic.

Finally, it’s incredibly important to ensure that the roll-up of the legacy accounts and cost objects matches that of the new SAP system. That means ensuring that an account that is mapped as an asset in the legacy system is still mapped as an asset in the new SAP system. Even more complicated, it means ensuring that a profit center in a management entity is mapped to the same management entity in the new SAP system. Usually the consolidations team will have a lot to say here.

Each mapping of GL accounts should preserve the account group

In our next installment of Performing GL Conversions Correctly, we’ll explore a standard journal entry flow and how to handle sub-ledger accounts.

By |September 29th, 2017|GL|0 Comments

Read More

SAP GL Document Splitting Part I

As alluded to in my last post, the document splitter plays a crucial part in the derivation of profit center and consequently in the preparation of management financial statements. Now we’ll start to take a deep dive into document splitting.

There are two prerequisites for using the splitter. First, the NewGL must be active in the box. That should be the case of any recent implementation. Second, there should be an understanding of the desired coding block and the business interpretation of the coding block elements.

Document Splitting Characteristics

The first step to configuring document splitting in SAP is to determine which coding block elements that you will want to have as document splitting characteristics. A document splitting characteristic will be acted on by the splitter to derive more detailed postings.

Document Splitting Characteristics  (1) SPRO->Financial Accounting (New) -> General Ledger Accounting (New) -> Business Transactions -> Document Splitting -> Define Document Splitting Characteristics for General Ledger Accounting

The partner field is used for cross-characteristic postings. For instance, in a posting spanning two profit centers A and B, the debit balance on one profit center A has partner profit center B, and the credit balance on profit center B has partner profit center A. It’s very similar to trading partner for those familiar with that concept.

There are two more options for each characteristic. First, whether the field is mandatory – that is it must be populated on all GL line items. Second, whether the field is zero balanced – whether the sum of the postings must equal zero (debits=credits).

For instance (and most common), if the profit center is set as a mandatory zero balancing characteristic, each line item on a posting must have a profit center and the total (debits-credits) of each profit center must be zero.

Configuration Model

The second key to understanding how the splitter is configured is to understand the theoretical model of the configuration. Once these areas are understood, it becomes much easier to guess what the configuration is doing. In the tradition of, I’ve created the informative diagram below (Ha! Ha! Ha!) .

Configuration for the Splitter

The critical information to glean at this step is that each GL account is assigned to an Item Category and each Document Type is assigned to a Business Transaction Variant. Finally, in the Splitting Method, we establish rules for each Business Transaction Variant on how each item category will be derived. This information is confusing, but it’s key to understanding the nature of the splitter. Memorize it if you must.

Next, we’ll walk through different splitting scenarios.

Active Document Split

The pièce de résistance of the NewGL, the active document split is the standard example used by most FICO consultants. In this scenario, an AR item is offset by two revenue items. Each revenue line item has a different profit center. In the GL View, the splitter breaks the AR line item into two line items – one for each profit center. Furthermore, the amounts on each posting is based on the weighted average of the offset line items. Read that twice – it’s tricky.

When we get to the active document splitting configuration, the key to this example is that the AR item category is split by the revenue category.

Active Split


Once you have active split down, inheritance is much easier. The key here is that if a posting has one blank line item and all other line items have the same profit center, then the blank line items will receive the same profit center. In the below example, we have a cash item without a profit center that has two expense items with PC 101. In the GL view, the cash item will receive the same profit center.

Inheritance in the SAP Document SplitterInheritance is turned on by default for all business transactions. You do have the option of turning it off at the rule level, but I’ve never seen a reason to do so.

Passive Split

The third type of splitting, is the passive split. The passive split is simple and it’s used whenever you reverse or clear a document. For reversing a document, the splitter will reverse the posting with the exact same profit center derivation as the document being reversed. This makes intuitive sense. The reversal should reverse out the original posting exactly, regardless of the configuration.

Second, for lines that are being cleared, the clearing line items will receive the same profit center split. This approach also makes intuitive sense. If we have an AR line item with $500 on profit center 101 and $300 on profit center 102, then we would expect the clearing document to wipe away $500 on 101 and $300 on 102.

In the next part of the series, we’ll start looking at the basic configuration.

References   [ + ]

1. SPRO->Financial Accounting (New) -> General Ledger Accounting (New) -> Business Transactions -> Document Splitting -> Define Document Splitting Characteristics for General Ledger Accounting

By |August 31st, 2017|SAP|0 Comments

Read More

House Bank Accounts in SAP FICO

House Bank Accounts (HBAs) in SAP are often configured incorrectly during an implementation. This improper configuration causes problems later when additional sub modules such as Electronic Bank Statement (EBS), Cash Forecasting, and Check Reconciliation are brought into scope. In this article, we explore how House Bank Accounts connect to physical bank accounts, some key settings, and common pitfalls to avoid.

House Bank Account

A house bank account represents a bank account that the company has opened with a bank. There should not be a house bank account for customer, vendor, or competitor bank accounts. HBAs should only be setup for a company’s own bank accounts. Second, each HBA should represent one single bank account and one bank account should only be represented by one HBA. If there are multiple house bank accounts with the same account number, check clearing and EBS operations will be negatively affected.

It’s also important to note that when a bank account is opened, it is opened by a single legal entity. So if a company with fifty legal entities opens a bank account, the account is still owned by one entity inside that company. We see this relationship in SAP in that a single company code is assigned to the HBA.

House Banks

If a house bank account represents a company’s bank account, then the House Bank (HB) represents the bank that the account is maintained at. So suppose a company has account 523552 at Bank of America with routing number 52389593, then the house bank account will be tied to account 523552 and the house bank will be tied to routing number 52389593. Let’s suppose that you have multiple Bank of America accounts with different routing numbers. Should you have a different HB for each routing number? Yes – SAP only enables one routing number per house bank.

Naming House Bank Accounts

SAP gives us four characters to name each House Bank and each House Bank Account. That of course means that we must be pithy and smart about our naming convention. Most companies only have one or small number of banks that they have accounts with. At the same time, there may be a few routing numbers for the same bank. Thus, multiple house banks may be required for the same bank. With that information, a sensible naming scheme for house banks seems to be AAA# where AAA is an abbreviation for the bank name – such as BOA for Bank of America or FTB for Fifth Third Bank – and # is a sequential number for each routing number at that bank.

Similarly, we must be careful about naming house bank accounts. Best treasury practices suggest that accounts should be segregated to only have one type of activity. So disbursements should be executed out of one account and collections should be in a separate account. Thus, a reasonable practice would be to use an abbreviation for the type of activity  and a sequential number. I would do AA## with AA being the short hand for the activity type and ## being a sequential number.

GL Accounts for House Bank Accounts

Most flows in bank accounting revolve around recording a companies cash activity and then reconciling against how the bank recorded that activity. As part of a monthly close operation, bank statements and internal accounting records should be reconciled against one another.

The standard recipe in SAP for cash accounting flows is that say when a payment is issued, a clearing account tied to the house bank account is credited and the vendor account is debited. Then, when the payment shows up on the bank statement, the clearing account is debited and the G/L tied to the HBA is debited credited. That means that the account tied to the HBA should always represent the reconciled activity (and match the balance per the last statement date), while the clearing accounts represent unreconciled activity.

Usually, there are more than one clearing accounts used depending on how much activity is flowing through the account. If the HBA GL account is 444440, then clearing accounts should be numbered 444441,444442,…,444449. If the account has low volumes, then only a clearing account for incomings and another for outgoings may be required. If the account has high volumes, then a separate account should be used for each type of activity (lockbox, ACH payments, check disbursements, etc).

Configuring House Bank Accounts

First, the house bank must be setup as a bank in FI01. The bank really is just used so that SAP is aware that the bank’s routing number is valid and to have a consistent address for the bank. Often times, a file is prepared and mass loaded with all of the known banks in a country to avoid having do to this step every time.


Next, the house bank is setup in FI12. First, we’re prompted for the company code of the house bank. Next, a code is assigned to the house bank as previously detailed. The country should be denoted as well. If there is a need for additional payment settings, these can be entered under the DME section.


Once the house bank is created, the house bank account should be configured. Key information here is the Account id, the description, the account number, the GL account, and the currency.


Once all of this information is saved, then the house bank account is ready for use in other cash flows such as lockbox, EBS, cash forecasting, and so on.

References   [ + ]

1. FI01
2, 3. FI12

By |August 30th, 2017|Bank, SAP|0 Comments

Read More

ERP Suites Wins Oracle JD Edwards Distinguished Partner Award

As a premier provider of products and solutions around the Enterprise Resource Planning (ERP) industry and Gold level member of Oracle PartnerNetwork (OPN), ERP Suites was awarded the Oracle JD Edwards Distinguished Partner Award for 2017. The recognition comes as ERP Suites continue to deliver innovative products and solutions that simplify the engagement with ERP systems while leveraging technologies that embrace the digital transformation era.

“We’re excited to be recognized again by Oracle for our leadership in the industry,” said Mike Moorman, CEO and partner of ERP Suites. “We continue to invest in technologies and partnerships with shared visions of customer enablement and increased ROI.”

ERP Suites, which recently celebrated its 11th anniversary, focuses on disrupting the traditional mindsets around ERP systems. “ERP systems used to be thought of as bloated, back-office systems with outdated interfaces and the need for constant care and feeding. Over the last 11 years, we have helped lead the adoption of ERP in the cloud, simplified management and fresh, new interfaces for easier interaction with your ERP system,” said Matt Batchler, Executive Director of Product Development for ERP Suites.

Included in these interfaces are the ERP Suites Clarity and ERP Suites Mobility products. Both products, offered in a simple cloud-based Software-as-a-Service (SaaS) model, provide options for customers that want to achieve immediate benefit from their investment, lower overall cost of management and provide several mobility options to their end-users.

Focused on the operational side of ERP systems, the ERP Suites Clarity product provides a complete view into the health of a customers’ ERP application through intuitive web and mobile interfaces. With features like SuitesScore™, microhelp and custom alerting, Clarity decreases customers’ application management costs while increasing focus on their core business.

With the Mobility application, ERP Suites has built a powerful and dynamic cloud-based framework called Ocelli to provide a simple, zero-code development of cross-ERP mobile applications. The framework architecture cuts the time to deploy mobile applications from weeks or months down to minutes. Customers realize significant savings as there is no need for native app or back end developers, “Simply connect to your ERP system, identify the interactive programs and define your process,” according to Matt Batchler. “We’re not just enabling mobile interaction. With our flexible framework, we can quickly link any number of channels, like voice, wearables or AR/VR technologies.”

ERP Suites was also recognized for their innovative leadership in 2015 for the integration of voice-enabled devices, like the Amazon Echo, with Oracle JD Edwards.

“We recognize the future of ERP interaction is not just mobile, our investments reflect the adoption of any number of technologies that enable a more dynamic and fluid workforce in the future,” said Mike Moorman.

Newswire Link

By |August 18th, 2017|News|1 Comment

Read More

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