Dynamics GP -SQL View - WORK AP Voucher with words to numbers

/****** Object:  View [dbo].[BI_PMVoucher]    Script Date: 13/01/2015 06:23:26 PM ******/
DROP VIEW [dbo].[BI_PMVoucher]
GO

/****** Object:  View [dbo].[BI_PMVoucher]    Script Date: 13/01/2015 06:23:26 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_PMVoucher]
AS
SELECT     dbo.SY00500.BACHNUMB, dbo.SY00500.SERIES, dbo.SY03900.TXTFIELD, dbo.PM10000.VCHNUMWK, dbo.PM10000.VENDORID, dbo.PM10000.DOCNUMBR, dbo.PM10000.DOCTYPE, dbo.PM00200.VENDNAME, dbo.PM10000.SOURCDOC, dbo.PM10000.DOCAMNT,
                  dbo.PM10000.DOCDATE, dbo.PM10000.PSTGDATE, dbo.PM10000.VADDCDPR, dbo.PM10000.VADCDTRO, dbo.PM10000.PYMTRMID, dbo.PM10000.TAXSCHID, dbo.PM10000.DUEDATE, dbo.PM10000.DISCDATE, dbo.PM10000.PRCHAMNT, dbo.PM10000.CHRGAMNT,
                  dbo.PM10000.CASHAMNT, dbo.PM10000.CDOCNMBR, dbo.PM10000.CHEKAMNT, dbo.PM10000.CHEKNMBR, dbo.PM10000.CURNCYID, dbo.PM10000.CHEKBKID, dbo.PM10000.TRXDSCRN, dbo.PM10000.TAXAMNT, dbo.PM10000.FRTAMNT,
                  dbo.PM10000.TRDISAMT, dbo.PM10000.PORDNMBR, dbo.PM10000.SHIPMTHD, dbo.PM10000.CURTRXAM, dbo.PM10000.POSTED, dbo.PM10000.PSTGSTUS, dbo.PM10000.APPLDAMT, dbo.PM10000.VCHRNMBR, dbo.PM10000.POSTEDDT,
                  dbo.PM10000.PTDUSRID, dbo.PM10000.PCHSCHID, dbo.PM10000.FRTSCHID, dbo.PM10000.MSCSCHID, dbo.PM10000.PRINTED, RTRIM(dbo.PM10000.VCHRNMBR) + ' | ' + RTRIM(dbo.PM10000.DOCNUMBR) + ' | ' + RTRIM(dbo.PM00200.VENDNAME) AS VchLabel,
                  dbo.PM10100.DSTSQNUM, dbo.PM10100.CNTRLTYP, dbo.PM10100.CRDTAMNT, dbo.PM10100.DEBITAMT, dbo.PM10100.DSTINDX, dbo.PM10100.DISTTYPE, dbo.GL00100.ACTDESCR, dbo.GL00105.ACTNUMST,
                  CASE WHEN CRDTAMNT > 0 THEN CRDTAMNT ELSE DEBITAMT END AS DistAmt, CASE WHEN CRDTAMNT > 0 THEN 'C' ELSE 'D' END AS Distsign, dbo.PM10100.DistRef, dbo.NumberToWords(dbo.PM10100.DEBITAMT) AS NumWords
FROM        dbo.GL00100 INNER JOIN
                  dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX RIGHT OUTER JOIN
                  dbo.PM10000 INNER JOIN
                  dbo.SY00500 ON dbo.PM10000.BACHNUMB = dbo.SY00500.BACHNUMB INNER JOIN
                  dbo.PM00200 ON dbo.PM10000.VENDORID = dbo.PM00200.VENDORID INNER JOIN
                  dbo.PM10100 ON dbo.PM10000.VCHRNMBR = dbo.PM10100.VCHRNMBR ON dbo.GL00100.ACTINDX = dbo.PM10100.DSTINDX LEFT OUTER JOIN
                  dbo.SY03900 ON dbo.PM10000.NOTEINDX = dbo.SY03900.NOTEINDX

GO


/****** Object:  UserDefinedFunction [dbo].[BI_NumberToWords]    Script Date: 13/01/2015 06:34:16 PM ******/
DROP FUNCTION [dbo].[BI_NumberToWords]
GO

/****** Object:  UserDefinedFunction [dbo].[BI_NumberToWords]    Script Date: 13/01/2015 06:34:16 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[BI_NumberToWords]
(
    @NumberString nvarchar(max)
) RETURNS VARCHAR(8000)
AS BEGIN

DECLARE @Number Numeric (38, 0)
DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3)
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)

set @Number = cast( @NumberString as Numeric (38, 0) )

IF @Number = 0 Return 'Zero'

-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @Number)
     , @outputString = ''
     , @counter = 1
SELECT @length   = LEN(@inputNumber)
     , @position = LEN(@inputNumber) - 2
     , @loops    = LEN(@inputNumber)/3

-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1

-- insert data for the numbers and words
INSERT INTO @NumbersTable   SELECT '00', ''
    UNION ALL SELECT '01', 'one'      UNION ALL SELECT '02', 'two'
    UNION ALL SELECT '03', 'three'    UNION ALL SELECT '04', 'four'
    UNION ALL SELECT '05', 'five'     UNION ALL SELECT '06', 'six'
    UNION ALL SELECT '07', 'seven'    UNION ALL SELECT '08', 'eight'
    UNION ALL SELECT '09', 'nine'     UNION ALL SELECT '10', 'ten'
    UNION ALL SELECT '11', 'eleven'   UNION ALL SELECT '12', 'twelve'
    UNION ALL SELECT '13', 'thirteen' UNION ALL SELECT '14', 'fourteen'
    UNION ALL SELECT '15', 'fifteen'  UNION ALL SELECT '16', 'sixteen'
    UNION ALL SELECT '17', 'seventeen' UNION ALL SELECT '18', 'eighteen'
    UNION ALL SELECT '19', 'nineteen' UNION ALL SELECT '20', 'twenty'
    UNION ALL SELECT '30', 'thirty'   UNION ALL SELECT '40', 'forty'
    UNION ALL SELECT '50', 'fifty'    UNION ALL SELECT '60', 'sixty'
    UNION ALL SELECT '70', 'seventy'  UNION ALL SELECT '80', 'eighty'
    UNION ALL SELECT '90', 'ninety'

WHILE @counter <= @loops BEGIN

    -- get chunks of 3 numbers at a time, padded with leading zeros
    SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)

    IF @chunk <> '000' BEGIN
        SELECT @tensones = SUBSTRING(@chunk, 2, 2)
             , @hundreds = SUBSTRING(@chunk, 1, 1)
             , @tens = SUBSTRING(@chunk, 2, 1)
             , @ones = SUBSTRING(@chunk, 3, 1)

        -- If twenty or less, use the word directly from @NumbersTable
        IF CONVERT(INT, @tensones) <= 20 OR @Ones='0'
        BEGIN
            SET @outputString = (SELECT word
                                      FROM @NumbersTable
                                      WHERE @tensones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '  WHEN 13 THEN ' undecillion '
                       ELSE '' END
                               + @outputString
            END
         ELSE BEGIN -- break down the ones and the tens separately

             SET @outputString = ' '
                            + (SELECT word
                                    FROM @NumbersTable
                                    WHERE @tens + '0' = number)
                             + '-'
                             + (SELECT word
                                    FROM @NumbersTable
                                    WHERE '0'+ @ones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '   WHEN 13 THEN ' undecillion '
                       ELSE '' END
                            + @outputString
        END

        -- now get the hundreds
        IF @hundreds <> '0' BEGIN
            SET @outputString  = (SELECT word
                                      FROM @NumbersTable
                                      WHERE '0' + @hundreds = number)
                                + ' hundred '
                                + @outputString
        END
    END

    SELECT @counter = @counter + 1
         , @position = @position - 3

END

SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, '  ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000) + 'dollars and ' + right(@numberstring,2) + ' cents'


RETURN @outputString
END
GO