Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Received Response
328
Views
8
Comments
User_U66J8
User_U66J8 Rank 4 - Community Specialist
in Oracle Analytics Cloud and Server

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

Answers

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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
    Brajesh Shukla-95078 Rank 7 - Analytics Coach

    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
    Brajesh Shukla-95078 Rank 7 - Analytics Coach

    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
    Thomas Dodds Rank 8 - Analytics Strategist

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

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

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

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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
    User_U66J8 Rank 4 - Community Specialist

    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
    Brajesh Shukla-95078 Rank 7 - Analytics Coach

    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