Document total in words

[Former Member]

Dear experts,

I need a query of delivery document total in words with currency.

So will assign this to my delivery document UDF for report if any alternative pls suggest.

Regards

Mangesh Pagdhare.

[Former Member]

Hi,

Below Step

Create Function :

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create FUNCTION [dbo].[Currency_ToWords] (
@Input Numeric (38, 3) -- Input number with as many as 18 digits
) RETURNS VARCHAR(8000) 

/* * Converts a integer number as large as 34 digits into the 
* equivalent words.  The first letter is capitalized.
* Example:
select dbo.udf_Num_ToWords (1234567890) + CHAR(10)
      +  dbo.udf_Num_ToWords (0) + CHAR(10)
      +  dbo.udf_Num_ToWords (123) + CHAR(10)
Select dbo.udf_Num_ToWords(76543)

DECLARE @i numeric (38,0)
SET @i = 0
WHILE @I <= 1000 BEGIN 
    PRINT convert (char(5), @i)  
            + convert(varchar(255), dbo.udf_Num_ToWords(@i)) 
    SET @I  = @i + 1 
END
****************************************************************/
AS BEGIN
Declare @Number Numeric(38,0)
set @Number = @Input
Declare @Cents as int
set @Cents = 100*Convert(money,(@Input - convert(Numeric(38,3),@Number)))
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) -- for chunks of 3 numbers
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)

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
-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, '  ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)
RETURN 'RUPEES '+ UPPER(@outputString) + ' AND ' + convert(Varchar(20),@Cents) + ' PAISE ONLY' -- return the result
END

Save below query in query manager (AR Invoices)

declare @Doc_total numeric (19,6)
set @Doc_total=$[OINV.DocTotal.0]
select  dbo.Currency_ToWords(@Doc_total)

Now set SHIFT + F2 in AR Invoices with your UDF

Hope u can get your need

Thanks

Kevin

[Former Member]

HI,

->> Create 1 UDF in Header on Requrie Documents (ex. Marketing Documents).

->> Create 3 Function in MSSQL Server Management.

->> Create 1 FMS in Query Generator and save as Query Manager then Assign to UDF for Amount in Words.

for example:

Create UDF in Header on Marketing Documents.

->> Choose Tools on Top menu.

->> User - Defined Fields. -> User Manage Fields.

->> Open the User Manage Fields Widnow.

->> Marketing Documents. -> Title.

->> Select Title and Click Add button in Bottom on User Manage Fields Window.

->> Create Amount in Words UDF(Code, Discription and Type - Character) and Add the UDF.

Create Function in MSSQL Server Management.

Check this Link, (have 3 Functions in Link).

http://techcreeze.blogspot.com/2008/11/convert-amount-into-words-according-to_15.html

1st Funciton - to Convert one Digit Number to words

2nd Funciton - to convert 2 digit number to words.

3rd Funciton - to convert amt in numbers to words.

->> Open the MSSQL Server Management Window.

->> Choose your Company database and Create NEW Query.

->> Create 3 Function Queries one by one.

->> Create 3 NEW Query Tab and 1st one put the 1st Function then Run the Function. and

2nd New Query tab put the 2nd Function then Run the Function.

3rd New Query tab put the 3rd Function then Run the Function.

Create FMS in Query Generator and Save as Query Manager.

->> Adminstration.

->> Reports. -> Query Generator.

->> Open the Query Generator and put the below FMS query.

for example : Purchase Order Doc. Toal(in wrods).

declare @Doc_total numeric (19,6)

set @Doc_total=$[OPOR.DocTotal]

select dbo.fNumToWords (@Doc_total)

->> Assign the FMS in UDF on Purchase Order.

->> Auto Refresh of Document Total.

Ex.

1. Goto the UDF and Clcik ShiftAltF2.

2. Select the SEARCH BY SAVED QUERY.

3. Assign the FMS Query.

4. Select the AUTO REFRESH WHEN FIELD CHENGES.

5. Select Document Total.

6. Check the Display Saved Values

Regards

Manoj S

[former_member196081]

Hi,

Check below link

there are many links at sdn...

Regards

Deepak Tyagi