Notes

IF OBJECT_ID('CreateOCRLineForInvoicePayload') IS NOT NULL
DROP FUNCTION CreateOCRLineForInvoicePayload
GO

CREATE FUNCTION CreateOCRLineForInvoicePayload
(
@sequenceNumber NVARCHAR(40), -- Contracts.SequenceNumber
@splitByContract VARCHAR (1), -- ReceivableInvoices.SplitByContract
@invoiceNumber NVARCHAR (40), -- ReceivableInvoices.Number
@currentDue DECIMAL (16,2), -- I.InvoiceBalanceAmount + I.InvoiceTaxBalanceAmount
@totalDue DECIMAL (16,2), -- I.InvoiceBalanceAmount + I.InvoiceTaxBalanceAmount
@IsMasterInvoice BIT
) RETURNS @ocr TABLE (
ocrNo VARCHAR(55)
)
AS
BEGIN

DECLARE @i INT = 1
DECLARE @sumOfMultiplications INT = 0
DECLARE @char VARCHAR(1) = ''
DECLARE @ocrLine VARCHAR(55)
DECLARE @consolidationFlag VARCHAR(1) = '0'
DECLARE @constant VARCHAR(2) = '09'
DECLARE @lockbox VARCHAR(5) = '00000'
DECLARE @checkDigit INT = 0
DECLARE @rawSequenceNumber NVARCHAR(40) = @sequenceNumber

-- Adjust parameters

-- Remove non numeric characters from Sequence Number
WHILE PATINDEX('%[^0-9]%', @rawSequenceNumber) > 0
BEGIN
SET @rawSequenceNumber = STUFF(@rawSequenceNumber, PATINDEX('%[^0-9]%', @rawSequenceNumber), 1, '')
END

-- Adjust sequence number to 13 character long, if needed
SET @sequenceNumber = FORMAT(CONVERT(BIGINT, SUBSTRING(@rawSequenceNumber, 1, 18)), REPLICATE('0', 13))

-- Adjust due amounts if equal to zero or negative
IF (@currentDue <= 0 AND @totalDue <= 0)
BEGIN
SET @currentDue = 0
SET @totalDue = 0
END

-- Set Consolidation Flag and adjust Sequence Number if needed
IF (@splitByContract = '0' OR @IsMasterInvoice = 1)
BEGIN
SET @consolidationFlag = '1'
SET @sequenceNumber = REPLACE(@sequenceNumber, RIGHT(@sequenceNumber, 3), '000')
END

-- Create OCR Line
SET @ocrLine = RIGHT(REPLICATE('0', 13) + @sequenceNumber, 13)
SET @ocrLine += @consolidationFlag
SET @ocrLine += RIGHT(REPLICATE('0', 10) + @invoiceNumber, 10)
SET @ocrLine += CAST(FORMAT(CAST(REPLACE(CAST(@currentDue AS VARCHAR), '.', '') AS BIGINT), REPLICATE('0', 10)) AS VARCHAR)
SET @ocrLine += CAST(FORMAT(CAST(REPLACE(CAST(@totalDue AS VARCHAR), '.', '') AS BIGINT), REPLICATE('0', 13)) AS VARCHAR)
SET @ocrLine += @constant
SET @ocrLine += @lockbox

-- Calculate sum of multiplications based on OCR Line
WHILE (@i <= LEN(@ocrLine))
BEGIN
SET @char = SUBSTRING(@ocrLine, @i, 1)

IF (@i % 2 = 0)
SET @sumOfMultiplications += CAST(@char AS INT)
ELSE
SET @sumOfMultiplications += CAST(@char AS INT) * 2

SET @i += 1
END

-- Calculate check digit based on sum of multiplications
SET @checkDigit = 10 - (@sumOfMultiplications % 10)

IF (@checkDigit = 10)
SET @checkDigit = 0

-- Append check digit on OCR Line
SET @ocrLine += CAST(@checkDigit AS VARCHAR)

INSERT INTO @ocr (ocrNo)
VALUES (@ocrLine)

RETURN;
END
GO