R12: CE: Bank Statement Format Masking for Mapping BAI2, SWIFT940: BANK_ACCOUNT_NUM, BANK_TRX_NUMBER, BANK_ACCOUNT_TEXT, Agent Bank Account Number And Others (Doc ID 752532.1)

Last updated on FEBRUARY 05, 2023

Applies to:

Goal

(1). BAI2 BANK STATEMENT MAPPING:

Q1: Why following BAI2 datafile needs bank statement mapping as: 

BANK_TRX_NUMBER 16 -3 and not -2?

16,164,10674584,Z,902354001896874,000000000000/
88,<xxxxxxxxx>:VENDOR PMT ID:HU00010710006
88,INDN:<yyyyyyyy>:3041036004 CTX
88,ADDITIONAL INFORMATION IS AVAILABLE FOR THIS PMT.
88,CONTACT A TREASURY SALES OFFICER FOR ASSISTANCE.

Q2: Why following BAI2 datafile needs bank statement mapping as:

BANK_TRX_NUMBER 16 -2 and not -1?

16,581,156500,Z,813009392186700,000000002004/

Q3. You have BAI2 Bank Statement where BANK_TRX_NUMBERis of 15 digits and first 8 digits are always constant but not same hence following was tested:

Bank Transaction number: 16 -3 ~(nnnnnnn) or nnnnnnn(nnnnnnn).  This doesn't work
Bank Transaction number: 16 -3 902354xx(nnnnnnn).  This works. 

What characters and how format masking to be used to insure correct mapping?  While we tried the constant: 902354xx as>>902354xx(nnnnnnn).This works, however why the  ~(nnnnnnn) or nnnnnnn(nnnnnnn) doesn't work. Since preceding constant can be different and mapping can not be constrained of this.

How do we insure that only last seven digits only populated and first eight digits are truncated?

Q4: You have following Bank account Number data for the SWEEP IN SWEEP OUT statement line and this needs to populate the AGENT column:
ZXC000000683xxxx
ZBD100005639yyyy
To populate the correct BANK_ACCOUNT_TEXT (AGENT) with values as:

000000683xxxx and 100005639yyyy

Q5: When you have scenario where BANK_TRX_NUMBER field in the bank statement may have six or seven leading zeros for the Number.  For example:

Payment Number: 0000000123456 or 000000012345

What format masking to be used in bank statement mapping?

Q6: What Setups you need to load:

1). Bank Account as: 00942772xxxx>> 942772xxxx

2). Number as: 0094277yyyy >> 94277yyyy

03,00942772xxxx,USD/
..
16,275,000000000003789,Z,081304012000000,009427729328/
88,ZBA TRANSFER FROM 009427729328
16,475,000000000003789,Z,813006292539777,000000100221,/
..

Q7: How to insure You load appropriate values for:
CURRENCY_CODE
EXCHANGE_RATE
TRX_TEXT
INVOICE_TEXT
ORIGINAL_AMOUNT
CHARGES_AMOUNT
CUSTOMER_TEXT
BANK_ACCOUNT_TEXT

From the following BAI2 data file:

01,<xxxxxxxx.,4873012,110428,0600,06005100,,,2/
02,4873012,XXXXGB22,1,110427,1410,SEK,2/
03,123-123-123,USD,010,612340866,,,015,612337783,,,030,612337783,,,/
88,040,612361291,,,045,612361291,,,060,612361291,,,072,000,,,/
88,074,000,,,100,000,0,,400,3083,1,,470,000,0,/
16,495,3083,V,110428,,3118285563,1161000828064397/
88,FR:ISSUE CCY TT
88,ENDT:20110427
88,TRID:1161000828064397
88,PY:JAN-MAR 11 ROYALTY
88,BI:IE23BOFI90139437309002
88,BN:RUGBY WORLD CUP
88,AB:<QQQQQQQQQQQQQQQQQQQQQQ>:TALBOT S/
88,TREET/
88,OA:GBP3083
88,BO:<ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ>
49,3674109554,15/
98,3674109554,1,17/
99,3674109554,1,19/

Q8: Agent Bank account number:
For Bank Statement Loader BAI2 bank file how to remove a space within the bank account number in record 16 for the Agent Bank account number?
Release 12.1.1.
Bank file is from the bank is Bank of America and they will not change their format. The bank sends the account number in the following format: "9999 9999999". We stored the account number as "999999999999". Unfortunately we cannot change the configuration of the bank accounts, since the bank also sends transactions with the reference "999999999999". It is only the sweep in/out transactions that have the embedded space.

BAI2 file:
16,275,000000001528530692,V,110426,0000,6019XBS0001382500,6008 48471015 /
88,<XXXXXXXXXXXXXXXXXXXXXX>
The bank file has Agent Bank Account number in the following format:
xxxx nnnnnnnn ex: 6008 <XXXXXXXX>
where x=branch number and n=bank account number
However, the bank accounts themselves are configured in the following format:
xxxxnnnnnnnn ex: 6008XXXXXXXX

How can you remove the space in “xxxx nnnnnnnn” with the Bank account mapping or with other solution?
All the following Bank Account Mappings do not work:
• (nnnn),(nnnnnnnn) Concatenated Format = Yes
• 6008(nnnnnnnn) Include Indicator=Yes
• (xxxx),(xxxxxxxx) Concatenated Format = Yes
• 6008 (nnnnnnnn) Include Indicator=Yes

Q9: Bank Statement Load and Import, Format Masking Fails:

BANK STATEMENT LODER:  AP check and AR receipt numbers that are 000010000030 is removing the leading as well as Other zeros and 1.
Bank Statement Mapping Used:
BANK_TRX_NUMBER 16 -2 00000000000(~),00000000(~),000000(~),00000(~),0000(~)
 
DATAFILE DATA:
 
16,165,000000000002500,Z,902526004998724,000001000029,/
16,165,000000000002731,Z,902526004998724,000010000030,/
16,165,000000000002500,Z,902526004998724,000000100058,/
16,165,000000000002500,Z,902526004998724,000010000066,/
16,165,000000000002500,Z,902526004998724,000010000069,/

When Loaded and seen in Interface Form:
 
BANK STATEMENT LOADER:  AP check and AR receipt numbers that are 000010000030  is removing the leading as well as Other zeros and 1. Bank Statement Mapping:
BANK_TRX_NUMBER 16 -2 00000000000(~),00000000(~),000000(~),00000(~),0000(~)
 
Line Number Code Number
 
17 165 1000029
18 165 30   >>>> Instead of:10000030
19 165 100058
20 165 66 >>>> Instead of:10000066
21 165 69 >>>> Instead of:10000069

<Bug: 14072265> - BANK_TRX_NUMBER MASKING FAILS TO MASK CORRECTLY

Application is working as expected. Please find below justification.

The bank statement mapping by customer is,Bank Statement Mapping:
BANK_TRX_NUMBER 16 -2 00000000000(~),00000000(~),000000(~),00000(~),0000(~)

Considering two lines from problem statement.
1=>  16,165,000000000002500,Z,902526004998724,000000100058,/
2=>  16,165,000000000002500,Z,902526004998724,000010000066,/

Line 1=>

the Bank Trx Number value is 000000100058. Please note that user has provided multiple formats. Hence program takes each
format one by one and will search for the match.

Format 1:00000000000(~)
This format do not match as there are no continuous 11 zero's in
"000000100058".

Format 2:00000000(~)
This format do not match as there are no continuous 8 zero's in
"000000100058".

Format 3:000000(~)
The format 000000(~) will match in this case, as the string "000000100058".
holds continuous 6 zero's, and bank trx number populates as 100058.

Line 2=>

the Bank Trx Number value is "000010000066". Please note that user has provided multiple formats. hence program takes each format one by one and will search for the match.

Format 1:00000000000(~)
This format do not match as there are no continuous 11 zero's in  
"000010000066"

Format 2:00000000(~)
This format do not match as there are no continuous 8 zero's in  
"000010000066"

Format 3:000000(~)
This format do not match as there are no continuous 6 zero's in  
"000010000066"

Format 3:00000(~)
The format 000000(~) will match in this case, as the string  "000010000066"
holds continuous 5 zero's, and bank trx number populates as 66.

Hence the bank trx number populates as 66. Please note that once applications finds a successful match, it will not search further.

Guide lines while defining format for the bank trx number:

The bank trx number is most likely to be numeric value. Hence proving numeric formats to extract numeric values may not be accurate there are high chances of matching the format with value. Please refer the
line=> 2 case explained above, Bank trx number extracted as 66 instead of 10000066. This happened because the format "00000" found in actual value "10000066".
The formats are meant for clear instructions to program by user to extract certain value from bank file. So user must ensure that the format is unique in the file.

Q10: EXCHANGE_RATE 16 -1 ER:(~)

CEBSLDR module: Load Bank Statement Data program ends in error
Errors:
Cause: FDPSTP failed due to ORA-06502: PL/SQL: numeric or value error:
character to number conversion error
ORA-06512: at "APPS.CE_BANK_STATEMENT_LOADER", line 3355
ORA-06512: at line 1

(2). SWIFT BANK STATEMENT MAPPING:
Q1: How to map BANK_TRX_NUMBER for the following data file?

:20:9400124557581854
:25:123-123-123
:28:134/1
:60F:C110715ZAR80562552,13
:61:1107150715DR1223,00NTRF19610ECDT5T//1196022090
/CTC/076/OUTGOING CLEARING TRANSFE
:86:/PT/FT/BN/SM NAIDOO/AB/632005 <XXXX> ELECTRONIC ,ABS/AB3/<ADDRESS>/AB4/AC4060321464
:61:1107150715DR7019,65NTRF19610ECDT5V//1196022117
/CTC/076/OUTGOING CLEARING TRANSFE
:86:/PT/FT/PY/ATC SA/BN/NEIL SEARLS/AB/632005 <XXXX> ELECTRONIC ,AB
S/AB3/<ADDRESS>/AB4/AC4077758052
:61:1107150715DR1671402,03NTRF19210EAPBT7//1196022084
/CTC/076/OUTGOING CLEARING TRANSFE
:86:/PT/FT/PY/ATC ST03/2011/BN/<YYYY> TRADING/AB/250655 CUSTOME
R COMPUTE,FNB/AB3/<ADDRESS>/AB4/AC62045612
969
:62F:C110715ZAR78882907,45
:64:C110715ZAR78882907,45

Q2: SWIFT BANK STATEMENT MAPPING WITH IBAN CODE AT RECORD/TAG :25: IBAN code: IT45X0558401631000000015894 and when try to Upload it errors as:

Error     The loading program was not able to locate BANK_ACCOUNT_NUM information which is required in the Bank Statement Interface.

Error    Bank account IT45X05584016310000000<XXXX> is not defined.  Set up the bank account first.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.