Oracle Analytics and AI

Hash total calculation for bank account number doesn't work as expected

Summary

Hash total calculation for bank account number doesn't work as expected

Content

Hi All,

I have a EFT payment template where one of the requirement is a field to display a hash total calculation of the bank account number. It has to be the 10 rightmost digits :

How to calculate:

- Repeat Steps 1-3 for each transaction:

- Step 1: Get Receiving Bank Account Number (P41.5) field value.

- Step 2: Remove characters to right of 15 leftmost characters.

- Step 3: Convert to number. Replace any non-numeric character with 1.

- Step 4: Add up all numbers from Step 3.

- Step 5: Remove characters to left of 10 rightmost digits.

If value is shorter than 10 digits, add leading zeros.

Here is my function which doesn't return the required value:

SUBSTR (          LPAD (             TO_CHAR (                SUM (                   TO_NUMBER (                      (TRANSLATE (                          LOWER (                                  substr(to_char(OutboundPayment/ PayeeBankAccount/ BankAccountNumber),1, 15)                                  ), 'abcdefghijklmnopqrstuvwxyz', '11111111111111111111111111'))))),             50,             '0'),            41)

Attaching the template and XML data for your information. It's the field P80_RECORD from position 49 to 58.

Any ideas where i'm wrong? Should be something obvious but i just can't get this working.

Thanks in advance,

Alex

Find more posts tagged with

Accepted answers

All comments

Thomas Dodds

SUM() won't do ... - Step 4: Add up all numbers from Step 3.

Why not just simply use the builtin ORA_HASH? https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions112.htm#SQLRF06313

then make it a consistent left zero padded string of what ever length you want?

Brajesh Shukla-95078

Thanks you Thomas,

But calculation is under OutboundPaymentInstruction, that confused me. If under OutboundPaymentInstruction we have multiple OutbondPayment under that multiple PayeeBankAccount will be there so which PayeeBankAccount digit we are going to SUM since the logic is at the OutboundPaymentInstruction level.

I think in case of Bank Account digit sum we need to move this to OutbondPayment level? then we can take individual digit and can do SUM?

Regards,

Brajesh

Brajesh Shukla-95078

in youe sample XML you have only one <PayeeBankAccount> which is 26900161931. and after calculation its returning ten digit 6900161931, which is correct as per your code. Since you have only one PayeeBankAccount then what sum you are expecting ?

Regards,

Brajesh

Thomas Dodds

Where to do it? Not sure ... I was just going after the 1111 into 1+1+1+1 = 4 ...

Thomas Dodds

He wants digit-sum ... 6900161931 into 6+9+0+0+1+6+1+9+3+1 = 36

Thomas Dodds

ok ... clearer now ... in any case ... SUM() won't sum the values in to_number(1111) to 4 ... but something like this will:

SELECT SUM (TO_NUMBER (SUBSTR (TO_CHAR (123456, 'TM9'), LEVEL, 1))) "RESULT"

FROM DUAL

CONNECT BY LEVEL <= LENGTH (TO_CHAR (123456, 'TM9');

want to check it:

SELECT 1+2+3+4+5+6 "PROOF" FROM DUAL;

So I would suggest wrapping the sql a bit to get the 'string of digits' then in an outer SQL perform the SUM() with the CONNECT BY LEVEL, then wrap once more to lead-pad zeros ... It's helpful to see it in steps for debugging ...

User_U66J8

Hi,

Thanks for your reply!

Forgot to specify that the output is etext and based on the documentation ORA_HASH and other complicated functions like REGEXP_LIKE are not supported.

Creating an eText Template

Regards,

Alex

Brajesh Shukla-95078

Hi Thomas,

As I understand this should be under OutboundPaymen because PayeeBankAccount comes under that XML tag.

There are two ways we can achieve 1111 into 1+1+1+1 = 4 ..

1. We can create custom tag using standard user hook package(IBY_FD_EXTRACT_EXT_PUB) that oracle provide for Payment format customization .

There we can create custom tag for Hash Sum using CONNECT BY LEVEL as you suggested

2. If this has to be calculated on e-Text then we can use SUBSTR to extract each individual digit then can add

Regards,

Brajesh