Practical use case of SE16H with a quick Query to understand Material account determination (FI-MM)

INTRO

The days of using SE16N (or even the old SE16) should be over now and you should now embrace the new evolution of it. The new SE16H !!! "H" for the HANA database era and capabilities.

This new version (SE16H) offers grouping capabilities, sorting, aggregation (like COUNT for counting records) and one of the most important one, JOIN capabilities. Now you can JOIN other tables to retrieve the content or descriptions that you can find in other satellite tables or Header to Line items relationships in one big Query / Result instead of jumping from one to another.

In the past we used to execute several Queries with SE16N (or SE16 for the old folks), download to Excel and then VLOOKUP those values to join them and or get descriptions. Now you can do all of that directly in SAP with SE16H in one step.

QUERY

This Query will allow you to quickly understand and give you more information about all your FI-MM account determination from your system by providing GL account descriptions and Valuation Classes names,

FI-MM Account determination configuration can be found in Table T030.

Once in SE16H, go to "Outer Join Definition" and create a new one. Provide a Name and a Name / Description. 

Then add Table SKAT - GL Account Master record descriptions by clicking the green add icon with the "+" on the 1st table for "Definition of Secondary Tables". 

Once added click on the "Output" icon (same as the Multiple selection icon for reports). This will allow you to select which fields you want to output from your "joined" SKAT table in your final query results. Accept and you will go back to the prior screen.

Then on the 2nd part of the screen (Selected Secondary Table) you have to enter the different fields that you need to join both tables. In our case, I am looking to display the GL accounts descriptions / names that I have in Table T030 as there is no way that I would know them by hard by just looking at T030 directly, and there are quite a few of them.

Enter SAKNR on Table field, select Method "Reference" (as you will reference / join it with the value of table T030), Reference field KONTS (GL account) from Table T030 then Table T030.

Add 2 other lines for KTOPL (Chart of accounts code), but in this case it will be as a "Constant" and value 0001 as my Chart of Accounts is 0001. Note: don't put this value as '0001' or you will get an error in your query, you do not need the colons for a constant as you would do while writing code.

Then add SPRAS (Language) as you are bring Text descriptions from a Table (SKAT) that is language dependent. In this case E for English and not EN. E for the technical unconverted value for English language. If you are not sure of your language technical value, display SKAT first in a separate SE16H window to know yours.

Finally, I want to add a 2nd table that will show me the Valuation Classes descriptions, as there are several and you do not always know them all by hard. Follow the same process but in this case for Table T025T (T for Text table for original table T025 Valuation classes).

Save your "Join Conditions" and then go back to SE16H main screen and select your "Outer Join Definition" name that you just created and Execute.

You can also select which fields you want to output from T030 in this main screen too. Then you can save your "layout" and re-use it next time in conjunction with your Outer Join definition.

At the end, after adding the 2 tables for GL account and Valuation Classes descriptions, your end result will show you all the T030 records with their corresponding descriptions for GL and Valuation classes. This will help you understand your FI-MM Account determination in a better way.

PS.: It is a shame that we do not have a table to get the descriptions for the field "Transactions" / KTOSL that would tell us what BSX, GBB, PRD, WRX, etc mean that we could add here too. But as an SAP Consultant, we need to know them or have proper notes / documentation with their meaning and usage.

If your Company and/or Project needs to implement this, or any of the functionalities described in my Blog, or advise about them, do not hesitate to reach out to me and I will be happy to provide you my services.

www.dnabusinessconsulting.com

Some banks can't deal with characters with accents and special characters, how to remove them with no-code ...

Recently, while doing a banking implementation with a US bank, I run into an issue that never had before. It caught my attention and thought it was worth sharing it because it is quite specific that I am sure it is going to benefit someone.

I was doing payment files testing with a US Bank (a really big one) for my client's US and Canadian operations and because my client also operates in the province of Quebec, Canada (where we also speak French); we run into some particular issue because we have data with French accented characters. 

It is really common in the French language to have accents on people's name, streets and companies too. But this bank's systems where not able to handle and read our files because of this "special characters / strange characters". Many times bank's systems are so antiquated that they are still running mainframe servers that were conceived in the 80's easily. Many of this servers and operating systems do not have the ability to handle some of this characters and even had a complete different code page too. In some of this cases, since we were passing them ASCII files (as per their request), their systems were not interpreting these characters and adding an extra space to the line record. This was making the record longer and therefore failing.

These are some of these "special characters" (Ex, À Á Ã È É Ê Ì Í Ò Ó Ô Ù Ú Û à á â è é ê ì í î ò ó ô ù ú û ).

Now there are 2 different scenarios that I had where I needed to remove this special characters, one without code and another one with.

# 1 - DME file creation (Ex. Electronic Payments, Positive Pay or any type of Banking file)

#2 - Check printing

# 1 - DME file creation

In this scenario we were creating 2 different types of files for this bank, an ASCII Positive Pay file and an XML ISO 20022 payment file. The ASCII one was the one that the bank was not able to process properly. 

In order to remove the 'special characters' on the DME file, you just need to flag one option on the corresponding field and the DME engine will take care of the rest.

Ex. I want to remove the 'special characters' from the Vendor Name (PayeeName1 field in my DME) that is part of the output file.

Select the Field name (Element or Atom) and click on the "Conv.Function" icon. You will get a pop-up like this one (see below).

Here you have a series of options to right justify or left justify your characters. Then on the 2nd part, you have the option "Replace National Chars". This option will allow you to replace these series of "accented" characters by no accented ones by executing a standard FM behind the scenes. (which opens the door to talk about my option #2).

#2 - Check printing

I mentioned that the output file was related to Positive Pay, so in this case, we needed to have a exact match file vs. printed check otherwise the bank was going to reject our checks. So, we also needed to adjust our check forms so that the printed names do not have these accented characters either. 

To do this, we will be using the exact same Function Module that is available in the DME engine, but in this case calling it via code.

FM SCP_REPLACE_STRANGE_CHARS

You can test it here to see the results before incorporating it in your code.

Here you will see how in the Input text I put all sort of accented characters and the FM returned all the same characters but without accents. The same can be executed in your forms or any other Abap program to remove these characters.

Bonus track ...

You can also use program RSCP0155 that has a selection screen where you can play with more sophisticated options like input and output code page and range of Unicode characters. Then this selection screen will end up executing this same FM and returning all the input characters and converted characters for the whole ASCII code. So you can see upfront what your characters will look like after conversion.

Part of this Post is based on OSS Note 

1062237 - Transliteration of characters with SCP_REPLACE_STRANGE_CHARS

https://launchpad.support.sap.com/#/notes/1062237

(requires a valid SAP Marketplace ID to display the Note) 

If your Company and/or Project needs to implement this, or any of the functionalities described in my Blog, or advise about them, do not hesitate to reach out to me and I will be happy to provide you my services.

www.dnabusinessconsulting.com

SAP Bank keys for North America - Implementing banking & electronic payments

I decided to write this blog post because over the last 2 years or more, I have been working on a number of projects / clients where I had to take over or use North America SAP Banking configuration of prior SAP consultants (in many cases "just configurators"). Unfortunately, I have seen a series of recurring mistakes being done by the majority of them because of the lack of knowledge of the North American banking system and common practices (for USA and Canada). Many of these consultants were either local or offshore and from different Big 5 consulting / implementation partners and some smaller SI's as well.

These series of recurring mistakes are due to the lack of knowledge of what it is a "Bank Key" in SAP or what it should be and what it is used for.

Bank Keys in SAP are used for two different things and places:

• Identify our own House banks where our own (Company's) bank accounts are registered / created
• Pre-requisite piece of Master Data information to be able to register Banking information for either Vendors and/or Customers to be used for Electronic Payments.

Bank Keys in SAP are created by country and their number will depend on certain rules and conventions that are specific to that particular country that you are creating the bank key for. One country will not use the same Bank Key and naming convention than another country. It will all depend on that country's Financial System. The Bank Keys are then going to be used to be able to perform and route electronic payments, if you do not create your bank keys properly, your electronic payments will not be routed properly or will be rejected by your bank because you are not providing the right and expected information to your bank.

Canada Bank Keys

Canadian Bank keys, look similar to US bank keys, but are completely different one to another. Canadian Bank keys are made of 2 different pieces of information, the first 4 digits represent the Financial Institution number assigned in the Canadian Banking / Financial system with a leading zero.

Ex.

• 0003 - Royal Bank of Canada
• 0004 - TD Canada Trust
• 0001 - Bank of Montreal
• 0010 - CICB - Canadian Imperial Bank of Commerce
• 0002 - Scotia Bank
• 0006 - National Bank of Canada

What is a Routing Number?

A routing number identifies the financial institution and the branch to which a payment item is directed. Along with the account number, it is essential for delivering payments through the clearing system.

EFT Routing Number

An Electronic Fund Transactions (EFT) routing number is comprised of a three-digit financial institution number and a five-digit branch number, preceded by a "leading zero".

Example : 0XXXYYYYY

•     0 : Leading zero
•     YYY : Institution Number
•     XXXXX : Transit / Branch Number

The electronic routing number is used for routing electronic payment items, such as direct deposits and wire transfers. 

Example of an SAP Bank Key record for Canada

As you can see, they are created by Country Code (Ex. CA, US or MX). Then the Bank Key is numeric for Canada as described above.

Then you have the Bank Name and address information, SWIFT Code (8 or 11 long) and the Bank Number for Canada is equal to the Bank key number entered.

The Bank Key should never be defined as the "SWIFT Code", there is another field to define the SWIFT code.

Below is a Void cheque image for a Canadian type of cheque where from there you will be able to pick the right routing information for a given Bank Account or Vendor information for you to properly create in SAP to ultimately execute electronic payments.

USA Bank Keys

The routing number (a.k.a. RTN or routing transit number) is a 9-digit number that serves to identify the specific financial institution responsible for the payment of a negotiable instrument. 

In same cases in the US it is also called ABA routing number (American Bankers Association #).

Where is the routing number on my check?

The sample graphic below shows where the routing number can be found in the bottom left corner of your checks.

Routing number format

The routing number consists of 9 digits:

The ABA Routing Number is of the form XXXXYYYYC

•     XXXX is Federal Reserve Routing Symbol
•     YYYY is ABA Institution Identifier
•     C is the Check Digit

Read more about the Routing number at Wikipedia website.    

Example of an SAP Bank Key record for USA

What is a SWIFT Code?

SWIFT code (also known as ISO 9362, SWIFT-BIC, BIC code, SWIFT ID or SWIFT code) is a standard format of Business Identifier Codes approved by the International Organization for Standardization (ISO). It is a unique identification code for both financial and non-financial institutions. (When assigned to a non-financial institution, a code may also be known as a Business Entity Identifier or BEI.) These codes are used when transferring money between banks, particularly for international wire transfers, and also for the exchange of other messages between banks. The codes can sometimes be found on account statements.

The SWIFT code is 8 or 11 characters, made up of:

•     4 letters: Institution Code or bank code.
•     2 letters: ISO 3166-1 alpha-2 country code
•     2 letters or digits: location code
•         if the second character is "0", then it is typically a test BIC as opposed to a BIC used on the live network.
•         if the second character is "1", then it denotes a passive participant in the SWIFT network
•         if the second character is "2", then it typically indicates a reverse billing BIC, where the recipient pays for the message as opposed to the more usual mode whereby the sender pays for the message.
•     3 letters or digits: branch code, optional ('XXX' for primary office)

Where an 8-digit code is given, it may be assumed that it refers to the primary office.

Business Partner

Once the Bank Keys have been created in Tcode FI01 (Create) 02 (Change) 03 (Display) or in Fiori App "Manage Banks", they can be used in the assignment and definition of our own House Banks and also for Business Partner (Vendor / Customer) Banking data under the "Payment Transactions" BP tab.

A given Business Partner can easily use the same Bank Key as our own House Bank if this BP also has a Bank Account at our own same Bank branch. In that case, you do not need to create a 2nd bank key, as we are talking about the same record that will be used in both scenarios.

As you can see in the image below, Bank Key CA - 000300002 needs to be previously created before being able to assign it to Business Partner 1000009. (In some cases, it can be created while doing the BP creation but you still need to provide all the same Data (Ex. Country, Key, address, SWIFT, Bank Number).

Common Mistakes done creating Bank Keys

When defining Bank Keys, I have seen several mistakes done by SAP resources that have all sort of downstream impacts in the system configuration and later on while interacting with the Banks:

1. Defining the Bank Key using a SWIFT code
2. Defining the Bank Key as consecutive numbers
3. Thinking that the Bank Key that is used by the House Bank is different from a Business Partner Bank Key
4. Modifying SAP standard country settings to force bank key of a different length or type (alfa or numeric)

• Defining the Bank Key using a SWIFT code - A NO, NO !!!

Not knowing what should be the Bank key or what a bank key looks like for a given country, has led to many SAP Finance resources to define them using the SWIFT codes as if it were the Bank Key. This in most, if not 100%, of the cases is never correct. The Bank Key is normally used as a key piece of routing information by the banks to execute payments and most of the standard DMEE's (Electronic Payment files) map and use them at a certain point inside the DMEE. 

If instead of providing a numeric Bank Key, we provide the SWIFT code, then the bank will reject our payments and we will not be able to pay vendors.

Below you will see a screenshot of a configuration done by a Consultant from a Big SI (really big) that because he/she did not know how to define properly a Bank Key for our own House Banks, he/she had to end up hardcoding in the DME config the Bank Key because the Bank was rejecting their payment files. Standard SAP DME config passes the Bank Key created in Tcode FI01 (Table BNKA), this was due to him/her using the SWIFT as Bank Key for the House Bank, which is absolutely wrong.

He/She ended up harcoding and putting a DME condition (IF) based on the country, to pass a constant. That should have been the Bank Key that is is "hardcoding here" if he/she should have done it properly from the get go.

• Defining the Bank Key as consecutive numbers

At another client, I have seen Bank Keys being defined as consecutive numbers (Ex. 1, 2, 3, 4 ...) without any logic and without respecting what it should be used for that Country as per the Bank Financial System. This will also generate an issue like in the above previous example when passing routing information to the bank in the DME files resulting in payments being rejected.

• Thinking that the Bank Key that is used by the House Bank is different from a Business Partner Bank Key

As mentioned before, Bank Keys for House Banks and Business Partners (Vendors / Customer) are and should not be different from one another. They are a piece of Master Data (Bank Master) that can be used by either of them. 

At this other customer, the consultants did not advise the client properly, and the client was defining a series of Bank Keys for own House Banks and another one completely differently for Business Partners. This is another big mistake, also done by the same Big SI.

• Modifying SAP standard country settings to force bank key of a different length or type (alfa or numeric)

I have also seen in some of these prior customers, that these SAP resources have modified standard SAP delivered country settings (Table T005) to force and allow them to entry Bank Keys that are different in length to the one that is supposed to be entered for a given country or to enter alphanumeric Bank keys when the Key should be numeric. 

Modifying SAP standard delivered country settings should be a "NO-NO !", unless you really have a compelling reason to do so. While working with some Banking Directory Services, you might have to do it, but in most cases it is part of the instructions that they provide you; so you have proper justification for it.

Data Migration for Bank Keys

Bank Keys are a pre-requisite Master Data object before being able to create Business Partners, in that you are planning to perform electronic payments to Vendors / Customers (BP's). You need to build and have this data prepared and loaded before starting to create Business Partners.

Also, our own House Banks will need to have these specific Bank Keys created previously so then you can configure the House Bank/s (in Tcode FI12_HBANK). So you will need them as part of your configuration. Normally these are just a few (3, 4 or 7, 8 records) that you can create them manually or you can build yourself a small LTMC file just for your configuration. 

S/4 HANA Data Migration Cockpit has a standard Data Migration object called "Bank Master" that can be used to migrate these Bank keys, either for your configuration or for your BP's. In most of the cases, you will end up with 2 different files. 1 small one for your config that you will also have to upload before you transport your House Bank and migrate (upload in S4) your Bank Accounts and then a 2nd file which should be bigger that will contain all the Bank Keys needed for your BPs, as they are a pre-requisite. 

Understanding this is really key to have a successful Data Migration approach for Business Partner Bank Keys that should be completely different for the Bank Keys associated to your House Banks. Unfortunately, I have also seen a lot of people confusing them. 

Banking Directory Data services 

Dealing with Bank Key Master Data and having clean Bank Keys data is key in case you want to implement Electronic Payments in SAP and avoid payments being rejected. Collecting these info from your Vendor (or Customers) should be part of your Business Partner creation process, but if you are doing it for the 1st time, collecting the data from them and also having the Bank Keys uploaded in the system could be quite an effort.

Having payments being rejected by your bank, will cost you money as banks charge you for rejected payments and also could create issues with your vendors because they will not receive your payments  on time and eventually they could cut services or goods delivery if you do not pay promptly.

There are certain companies and services out there in the market that can sell you Bank Directories that can be uploaded in SAP and will be ready to consume. Same as you would upload the Data with the Data Migration cockpit or maintain it manually.

Some of these services, are quite costly, but really convenient.

SWIFT Bank Directory: quite expensive but so far the best one and easy one that I have used. Comes with a file ready to load in SAP with standard SAP Bank directory upload program. They provide Worldwide financial institutions information, not just for 1 country.

Accuity Bank Directory: expensive as well but require the installation of custom Abap programs to upload in SAP (provided as transports by them). Their information is not that easy to deal and upload compared to SWIFT. They also provide Worldwide financial institutions information.

Canadian Payment Association (CPA): Their service is called "Financial Institution branch directory". I have not used it for a while, but they used to have a file ready to be uploaded in SAP with standard programs, same as SWIFT. They seem to only provide Canadian banks info, not worldwide as SWIFT or Accuity.

If you are looking to find some individual Bank Routing information for free out there on the Internet, there are some pages that give you (one at a time) information that you could use as Bank Keys info for Canada and US.

http://canada-banks-info.com/

https://us-banks-info.com/

Defining your Bank Keys properly is KEY for your House Bank configuration and your Electronic Payment setup in SAP and ultimately for a successful banking implementation. 

Consultants that have the right banking knowledge for the country and market that you are trying to implement is vital for this. Not all consultants know about this and end up taking the wrong decisions that have many ripple effects down the road. In the majority of those cases, those consultants never stick around long enough to see the unintended consequences of their lack of knowledge and experience in the subject.

If your Company and/or Project needs to implement this, or any of the functionalities described in my Blog, or advise about them, do not hesitate to reach out to me and I will be happy to provide you my services.

www.dnabusinessconsulting.com