Creating MOD10V01 BPay Reference Numbers Using T-SQL

Introduction

BPAY is Australia’s most widely used bill payment service which enables bill payers to transfer funds electronically from their bank accounts to billers. It is offered as a payment channel by over 170 financial institutions through their phone and Internet banking services.

Since BPAY’s launch in 1997 electronic bill payment in Australia has grown at a phenomenal rate. BPAY can be used to pay over 19,000 different bills Australia wide and each month BPAY processes over 18 million individual payments.

Over the last couple of years there has been a substantial increase in the number of people who have access to internet banking which has resulted in a large increase in the number of people using BPAY. Now 72% of the Australian bill paying population use BPAY* with 87% of BPAY payments being made via the internet.

MSDN Gallery: http://code.msdn.microsoft.com/Creates-a-Mod10-BPay-7ff5387a

*source: TNS Research Nov 2009

Code

DECLARE @TestID BIGINT = 1234567
DECLARE @TestIDLength TINYINT = 1
DECLARE @returnedVal INT = 0
DECLARE @inputVal VARCHAR(50) = REVERSE(@TestID)
DECLARE @odd BIT = 0
DECLARE @sum INT
DECLARE @numberLeft INT

WHILE @TestIDLength <= LEN(@inputVal)
    BEGIN
      IF @odd = 1
        BEGIN
          SET @sum = CAST(SUBSTRING(@inputval,@TestIDLength,1) AS TINYINT) * 1
          SET @odd = 0
        END
      ELSE
        BEGIN
          SET @sum = CAST(SUBSTRING(@inputval,@TestIDLength,1) AS TINYINT) * 2
          SET @odd = 1
        END
        
      IF LEN(CAST(@sum AS VARCHAR(10))) > 1
        SET @sum = CAST(SUBSTRING(CAST(@sum AS VARCHAR(10)),1,1) AS TINYINT) 
          + CAST(SUBSTRING(CAST(@sum AS VARCHAR(10)),2,1) AS TINYINT)
        
      SET @returnedVal = @returnedVal + @sum
      SET @TestIDLength = @TestIDLength + 1
    END

    IF @returnedVal % 10 = 0
      SET @numberLeft = 0
    ELSE
      SET @numberLeft = 10 - (@returnedVal % 10)

SELECT CAST(CAST(@TestID AS VARCHAR(10)) 
  + CAST(@numberLeft AS CHAR(1)) AS BIGINT) AS BPayRefNumber

 

 

Advertisements

About Mr. Wharty's Ramblings
Jeff holds a Master’s Degree in Systems Development (.NET Stream) and a Master’s Degree in Database Design and Management (SQL Server). Jeff also holds MCPD, MCITP, MCDBA, MCSD and MCSE certifications.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: