Blank in invoice number in Electronic Bank Statement (MT940)

[former_member242326]

Hello,

We have the following issue I would like to solve:

We receive Eletronic Bank Statement form our bank.

In the MT940 file, the data is like this:

:61:141110C543,21NTRFNONREF//43140900123333

/TRCD/00100/

:86:/CNTP/NL92ABNA0000000001/ABNANL2A/MANEGE HORSES V///REMI/USTD//3R

4508 bet.kenmerk 2001185466/

Our invoice number is splitted over two lines, in the example above, the invoice number is 3R4508 (see red value).

In SAP, after reading the file, the note to payee is like this:

/CNTP/NL92ABNA0000000001/ABNANL2A/MANEGE HORSES V///REMI/USTD//3R 4508 BET.KENMERK 2001100066/ 43140900000009 /TRCD/00100/

In the invoice number, a blank is added, so it is 3R 4508 instead of 3R4508.

Now the search string doesn't find the invoice number.

It is not always the third position that is blank, in fact, it could be any position: 3 R4508 or 3R4 508 or 3R45 08 or 3R450 8

Is there an easy way to create a search string that ignores the blank(s) and finds the invoice number?

The invoice number always contains 6 characters, numeric of alpha numeric.

Thanks in advance!

[Former Member]

Hi,

If blank space is coming in you statement MT940 format. Kindly check with you bank people,They will correct it.IT is a encoding issue.

Thanks&Regards

M.Mohammed

[former_member242326]

Hello,

Since we switched to structured MT940 file instead of unstructured, the problem is solved!

Although there is still a space visible in the note to payee, SAP does now find the invoice(s).

We now (still) have another issue.

We have some suppliers that use small numbers for invoice numbers, for example 10, 25 or 40.

Now we experiencing less automatic matches, because SAP finds a match on both those supplier invoices as the customer invoices.

It is mostly caused by the IBAN of the customer, because we search the whole string (86 tag).

For example:

:86:/EREF/521750331//CNTP/NL06RABO112233445/RABONL2U/SPORTCENTRA AAA

AAAAA BV///REMI/USTD//INVOICE 4R1234/

In the part after USTD, the invoice number(s) should be searched.

This can be any string, depending on how the customer described this.

Is there a possibility to start the search of the invoice number(s) after the USTD// tag?

Our invoice numbers always are 6 positions, but can be a combination of numbers and characters.

We defined the invoice search strings as follows:

Any help is appreciated!

Thanks.

[Former Member]

Hi

I don't know if your line always includes the description ' Invoice'  as well, but as a start you could try the following:

(^| )USTD//??????/( |$) with the mapping ??????

This will then search for the part starting with USTD// and find the following 6 characters:

You could of course then change it to (^| )USTD//Invoice ??????/( |$) with the mapping ?????? to include the option of 'invoice' in the description. But this could be a starting point for you to work from.

Regards,

Thijs

[former_member242326]

Hello Thijs,

Thanks for the reply.

The customer is free to fill the note to payee, we don't have or make rules for that.

Your solution sounds promising, but I forgot to mention that is is also possible that the customer states more than one invoice in one entry. The customer is free to enter whatever he likes.

For example:

:86:/EREF/521750331//CNTP/NL06RABO112233445/RABONL2U/SPORTCENTRA AAA

AAAAA BV///REMI/USTD//4R1234, 4R5678 4R8765 / 4R2277 and 4R4455/

All 5 invoices should be recognized.

In our current solution, SAP will recognize all 5 invoices, but if there is also an invoice of a supplier with, for example, invoice number 33 (as part of the IBAN), it will result in a mismatch.

Can I force that a match of EXACTLY 6 positions should be used?

If that can be arranged, then the match of 33 could/should not be made?!

Thanks!

[Former Member]

By creating search strings that start with "(^| )USTD// " you can force the program to only look at that part of the :86: line, but you would need to create different search strings for the different situations, so for just one invoice, two invoices, etc. The question becomes to how far you want to manage the situation with search strings.

Your current solution is the basic search algorithm offered by SAP and no search string is involved? In that situation I don't know of a solution to force SAP to only look at 6 characters.

Solution would be in using search strings, and maybe there is something possible with the + or * (repetition) functions to not have to manage every exception in a search string. But I haven't experienced this situation yet, so you would have to test it.

Regards,

[former_member242326]

Hello,

Is it possible to retrieve only the value after the USTD// tag as note to payee?

For example the following :86 tag value:

:86:/EREF/CC000162-151-CC000162//CNTP/NL99RBOS0999999999/RBOSNL2A/XXX

XXXX . XXXX BV///REMI/USTD//Customer 12345.0 invoice 4Z5116/

I would like SAP to search for invoice number(s) only in the part after USTD//, in the above example that is Customer 12345.0 invoice 4Z5116/

The problem here is that the number of characters that a customer enters in not known.

It could be 1 invoice only, invoice and customer number, more than 1 invoice or....

I would like to prevent that a match is being made on the part before USTD//.

If we can exclude that part, a lot of errors will not occur.

In the example above SAP finds invoice 151 (as part of the EREF tag) of a vendor (this should NOT be found) and invoice 4Z5116 of the customer (which is correct).

For every mismatch a payment advice is being made, which manually must be processed.

We have lots of those "mismatches" per day, that is not meant to be, it is a lot of work.

Only the part after USTD// should be used as note to payee.

Any suggestions?

Thanks!

Ed

[Former Member]

Hi,

A question, does your invoice number always start with a 3 or 3R? Or can all positions be different?

We have a similar situation in our system, but our solution, I believe, is dependent on the first 2 positions always being the same.

Regards,

Thijs

[former_member242326]

Hello,

We don't have always the same starting values.

Can be anything.

[Former Member]

We have the same problem and we have recently fixed this by creating a number of search strings.

Our invoice convention is that our invoice numbers are always 10 characters long and they always start with '96' and we ran into the problem that there would be a space in the bank statement somewhere in that invoice number.

We defined a search string

(IMG -> Financial Accounting -> Bank Accounting -> Business Transactions -> Payment Transactions -> Electronic Bank Statement -> Define Search String for Electronic Bank Statement)

The mapping in the search string is then as follows:

Nr.     Source     Is...      Target

1            9           ->          9

2            6           ->          6

3            #           ->          #

4                         ->              

5            #           ->          #

6            #           ->          #

7            #           ->          #

8            #           ->          #

9            #           ->          #

10          #           ->          #

11          #           ->          #

What this then means is that when an invoice number on the bank statement has a space on the 4th position, SAP will convert it and remove the space.

So:  961 2345678 becomes 9612345678

This means that for everything position we created a search string as the space could occur in any position of the statement.

You can try applying this, the only added difficulty becomes the letter, if the letter is always in the same place of the invoice number (ie. second position) you can fix that by using the ? (wild card), if however it could be in any position (ie. inv # could be 3R4508 but also 26G489) it can become more difficult.

The only doubt I have is whether it will recognize the correct invoice number if you cannot pinpoint 1 specific position which always has the same value.

Sorry I couldn't be more of help!

[former_member242326]

Hello,

Thanks for your answer.

I was trying to do something similar as you described.

Indeed the problem is: where can I find the (start position of the) invoice number in the text and how will I recognize it.

The customer fills in the description in the bank statement, so I cannot search at a specific position. The invoice number can be any combination of numbers and characters.

Any ideas are welcome!