on 01-06-2011 7:13 AM
Hi Experts,
in A/R Invoice the grand total is displayed in "Rupees in words". like if grand total = 386326.62, it is written
THREE LAC EIGHTY THOUSAND THREE HUNDRED TWENTY SIX & SIXTY TWO PAISE ONLY
I need to show it in PLD after Rounding off the Total value (As in my screen amount value is 3, so it shows like INR 386,326.617)
Please guide
Regards,
Rupa Sarkar
Hi Rupa,
As JimM, referred you can solve this through Stored Procedure or FMS for to show in PLD.
If you are using Crystal reports, then just a formula field is enough to convert the amount in Indian Style.
Here is the formula.
numbervar RmVal:=0;
numbervar Amt:=0;
numbervar pAmt:=0;
stringvar InWords :="Rupees ";
Amt := "Amount to convert";
if Amt > 10000000 then RmVal := truncate(Amt/10000000);
if Amt = 10000000 then RmVal := 1;
if RmVal = 1 then
InWords := InWords + " " + towords(RmVal,0) + " crore"
else
if RmVal > 1 then InWords := InWords + " " + towords(RmVal,0) + " crores";
Amt := Amt - Rmval * 10000000;
if Amt > 100000 then RmVal := truncate(Amt/100000);
if Amt = 100000 then RmVal := 1;
if RmVal >=1 then
InWords := InWords + " " + towords(RmVal,0) + " lakhs";
Amt := Amt - Rmval * 100000;
if Amt > 0 then InWords := InWords + " " + towords(truncate(Amt),0);
pAmt := (Amt - truncate(Amt)) * 100;
if pAmt > 0 then
InWords := InWords + " and " + towords(pAmt,0) + " paisa only"
else
InWords := InWords + " only";
UPPERCASE(InWords)
Note: Just replace the "Amount to convert" with the DocTotal field or other field that you need to convert.
Regards,
Bala
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The Bala's formula is for Crystal report and not UDF.
You must develop query for FMS. In the query, you call the amount in word function. It's simple query. If you want the amount in words in the UDF of a document in the SBO, first of all, you must create a function for amount in words in the db of SBO. The function can be searched through google.com.
Using the query FMS, you can display the amount in words function in the SBO. Here is the query FMS amount in words sample in invoice:
DECLARE @DocCur VARCHAR(3)
DECLARE @PrjCust VARCHAR(20)
SET @DocCur = $[OINV.DocCur]
SELECT @PrjCust = T0.ProjectCod FROM OCRD T0 WHERE T0.CardCode = $[OINV.CardCode]
IF @DocCur = 'INR'
BEGIN
IF @PrjCust = '150'
BEGIN
SELECT dbo.inwords('INR',$[OINV.DocTotal])
END
ELSE
BEGIN
SELECT dbo.inwords('INR',ROUND($[OINV.DocTotal],0))
END
END
END
Assign the above query FMS in the udf and refresh by doctotal. The udf value will be amount in words.
JimM
Hi,
create these 2 fucntions in sql
CREATE function [dbo].[AmountToWords]
(
@InNumber Numeric(18,2)
)
--Returns the number as words.
returns VARCHAR(2000)
as BEGIN
--SEt NoCount ON
Declare @Num Varchar(20)
Declare @Dec Varchar(3)
Declare @Return Varchar(2000)
Set @Dec = SubString(Convert(Varchar(20),@Innumber),Len(Convert(Varchar(20),@Innumber))-2,3)
Set @Num = SubString(Convert(Varchar(20),@Innumber),1, Len(Convert(Varchar(20),@Innumber))-3)
Declare @Hundred Char(8)
Declare @HundredAnd Char(12)
Declare @Thousand Char(9)
Declare @Lakh Char(5)
Declare @Lakhs Char(6)
Declare @Crore Char(6)
Declare @Crores Char(7)
Set @Hundred = 'Hundred '
Set @Thousand = 'Thousand ' Set @Lakh = 'Lakh ' Set @Lakhs = 'Lakhs ' Set @Crore = 'Crore ' Set @Crores = 'Crores '
Set @HundredAnd = 'Hundred and '
if Len(@Num) = 1 -- One
Begin Set @Return = dbo.GetTextValue(@Num) End Else
if Len(@Num) = 2 -- Ten
Begin Set @Return = dbo.GetTextValue(@Num) End Else
if Len(@Num) = 3 -- Hundred
Begin Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Hundred
IF SubString(@num,2,2)= '00'
Set @Return = @Return + 'And '
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2))
End
Else if Len(@Num) = 4 -- thousand
Begin Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Thousand
If SubString(@Num,2,1)= '0'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,1)) + @Hundred
IF SubString(@num,3,2)= '00'
Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2))
End
Else if Len(@Num) = 5 -- Ten Thousand
Begin Set @Return = dbo.GetTextValue(SubString(@Num,1,2)) + @Thousand
If SubString(@Num,3,1)= '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,1)) + @Hundred
IF SubString(@num,4,2) ='00'
Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2))
End
Else if Len(@Num) = 6 -- Lakh
Begin
If SubString(@Num,1,1) = '1'
Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Lakh
Else
Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Lakhs
If SubString(@Num,2,2)= '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2)) + @Thousand
If SubString(@Num,4,1) ='0'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,1)) + @Hundred
IF SubString(@num,5,2) ='00'
Set @Return = @Return + 'And '
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2)) End
Else if Len(@Num) = 7 -- Ten Lakhs
Begin Set @Return = dbo.GetTextValue(SubString(@Num,1,2)) + @Lakhs
If SubString(@Num,3,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2)) + @Thousand
If SubString(@Num,6,1)= '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,1)) + @Hundred
IF SubString(@num,6,2)= '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2)) End
Else if Len(@Num) = 8 -- Crore
Begin Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Crore
If SubString(@Num,2,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2)) + @Lakhs
If SubString(@Num,4,2) ='00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2)) + @Thousand
If SubString(@Num,6,1) ='0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,1)) + @Hundred
IF SubString(@num,7,2) ='00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,2))
End Else if Len(@Num) = 9 -- Ten Crore
Begin Set @Return = dbo.GetTextValue(SubString(@Num,1,2)) + @Crores
If SubString(@Num,3,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2)) + @Lakhs
If SubString(@Num,5,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2)) + @Thousand
If SubString(@Num,7,1)= '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,1)) + @Hundred
IF SubString(@num,8,2) ='00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,2)) End
Else if Len(@Num) = 10 -- Hundred Crore
Begin Set @Return = dbo.GetTextValue(Substring(@Num,1,1)) + @Hundred
IF Substring(@Num,2,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2)) Set @Return = @Return + @Crores
If SubString(@Num,4,2) ='00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2)) + @Lakhs
If SubString(@Num,6,2) ='00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2)) + @Thousand
If SubString(@Num,8,1)= '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,1)) + @Hundred
IF SubString(@num,9,2)= '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,9,2)) End
Else if Len(@Num) = 11 -- Thousand Crore
Begin Set @Return = dbo.GetTextValue(Substring(@Num,1,1)) + @Thousand
IF SubString(@Num,2,1) ='0' Set @Return = @Return + dbo.GetTextValue(Substring(@Num,2,1)) + @Hundred
IF Substring(@Num,3,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2)) Set @Return = @Return + @Crores
If SubString(@Num,5,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2)) + @Lakhs
If SubString(@Num,7,2) ='00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,2)) + @Thousand
If SubString(@Num,9,1) ='0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,9,1)) + @Hundred
IF SubString(@num,10,2)= '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,10,2)) End
Else if Len(@Num) = 12 -- Ten thousand Crore
Begin Set @Return = dbo.GetTextValue(Substring(@Num,1,2)) + @Thousand
IF SubString(@Num,3,1)= '0' Set @Return = @Return + dbo.GetTextValue(Substring(@Num,3,1)) + @Hundred
IF Substring(@Num,4,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2)) Set @Return = @Return + @Crores
If SubString(@Num,6,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2)) + @Lakhs
If SubString(@Num,8,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,2)) + @Thousand
If SubString(@Num,10,1)= '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,10,1)) + @Hundred
IF SubString(@num,11,2)= '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,11,2)) End
Else if Len(@Num) = 13 -- Lakh Crore
Begin Set @Return = dbo.GetTextValue(Substring(@Num,1,1)) + @Lakh
If Substring(@Num,2,2) ='00' Set @Return = @Return + dbo.GetTextValue(Substring(@Num,2,2)) + @Thousand
IF SubString(@Num,4,1)= '0' Set @Return = @Return + dbo.GetTextValue(Substring(@Num,4,1)) + @Hundred
IF Substring(@Num,5,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2)) Set @Return = @Return + @Crores
If SubString(@Num,7,2) ='00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,2)) + @Lakhs
If SubString(@Num,9,2) ='00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,9,2)) + @Thousand
If SubString(@Num,11,1)= '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,11,1)) + @Hundred
IF SubString(@num,12,2)= '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,12,2)) End
Else if Len(@Num) = 14 -- Ten Lakh Crore
Begin Set @Return = dbo.GetTextValue(Substring(@Num,1,2)) + @Lakhs
If Substring(@Num,3,2) ='00' Set @Return = @Return + dbo.GetTextValue(Substring(@Num,3,2)) + @Thousand
IF SubString(@Num,5,1)= '0' Set @Return = @Return + dbo.GetTextValue(Substring(@Num,5,1)) + @Hundred
IF Substring(@Num,6,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2)) Set @Return = @Return + @Crores
If SubString(@Num,8,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,2)) + @Lakhs
If SubString(@Num,10,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,10,2)) + @Thousand
If SubString(@Num,12,1) ='0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,12,1)) + @Hundred
IF SubString(@num,13,2)= '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,13,2)) End
If @Dec= '.00' Set @Return = @Return + 'Rupees And ' + dbo.GetTextValue(SubString(@Dec,2,2)) + 'Paise Only'
ELSE Set @Return =@Return+'Zero Rupees Only'
Return @return
End
and
CREATE Function [dbo].[GetTextValue]
(
@dblNumber Numeric
)
Returns Varchar(1000)
As
Begin
Declare @StrWord Varchar(400)
SEt @strWord = Case @dblNumber When 1 Then 'One ' When 2 Then 'Two ' When 3 Then 'Three ' When 4 Then 'Four ' When 5 Then 'Five ' When 6 Then 'Six ' When 7 Then 'Seven ' When 8 Then 'Eight ' When 9 Then 'Nine ' When 10 Then 'Ten ' When 11 Then 'Eleven ' When 12 Then 'Twelve ' When 13 Then 'Thirteen ' When 14 Then 'Fourteen ' When 15 Then 'Fifteen ' When 16 Then 'Sixteen ' When 17 Then 'Seventeen ' When 18 Then 'Eighteen ' When 19 Then 'Nineteen ' When 20 Then 'Twenty ' When 21 Then 'Twenty One ' When 22 Then 'Twenty Two ' When 23 Then 'Twenty Three ' When 24 Then 'Twenty Four ' When 25 Then 'Twenty Five ' When 26 Then 'Twenty Six ' When 27 Then 'Twenty Seven ' When 28 Then 'Twenty Eight ' When 29 Then 'Twenty Nine ' When 30 Then 'Thirty ' When 31 Then 'Thirty One ' When 32 Then 'Thirty Two ' When 33 Then 'Thirty Three ' When 34 Then 'Thirty Four ' When 35 Then 'Thirty Five ' When 36 Then 'Thirty Six ' When 37 Then 'Thirty Seven ' When 38 Then 'Thirty Eight ' When 39 Then 'Thirty Nine ' When 40 Then 'Fourty ' When 41 Then 'Fourty One ' When 42 Then 'Fourty Two ' When 43 Then 'Fourty Three ' When 44 Then 'Fourty Four ' When 45 Then 'Fourty Five ' When 46 Then 'Fourty Six ' When 47 Then 'Fourty Seven ' When 48 Then 'Fourty Eight ' When 49 Then 'Fourty Nine ' When 50 Then 'Fifty ' When 51 Then 'Fifty One ' When 52 Then 'Fifty Two ' When 53 Then 'Fifty Three ' When 54 Then 'Fifty Four ' When 55 Then 'Fifty Five ' When 56 Then 'Fifty Six ' When 57 Then 'Fifty Seven ' When 58 Then 'Fifty Eight ' When 59 Then 'Fifty Nine ' When 60 Then 'Sixty ' When 61 Then 'Sixty One ' When 62 Then 'Sixty Two ' When 63 Then 'Sixty Three ' When 64 Then 'Sixty Four ' When 65 Then 'Sixty Five ' When 66 Then 'Sixty Six ' When 67 Then 'Sixty Seven ' When 68 Then 'Sixty Eight ' When 69 Then 'Sixty Nine ' When 70 Then 'Seventy ' When 71 Then 'Seventy One ' When 72 Then 'Seventy Two ' When 73 Then 'Seventy Three ' When 74 Then 'Seventy Four ' When 75 Then 'Seventy Five ' When 76 Then 'Seventy Six ' When 77 Then 'Seventy Seven ' When 78 Then 'Seventy Eight ' When 79 Then 'Seventy Nine ' When 80 Then 'Eighty ' When 81 Then 'Eighty One ' When 82 Then 'Eighty Two ' When 83 Then 'Eighty Three ' When 84 Then 'Eighty Four ' When 85 Then 'Eighty Five ' When 86 Then 'Eighty Six ' When 87 Then 'Eighty Seven ' When 88 Then 'Eighty Eight ' When 89 Then 'Eighty Nine ' When 90 Then 'Ninety ' When 91 Then 'Ninety One ' When 92 Then 'Ninety Two ' When 93 Then 'Ninety Three ' When 94 Then 'Ninety Four ' When 95 Then 'Ninety Five ' When 96 Then 'Ninety Six ' When 97 Then 'Ninety Seven ' When 98 Then 'Ninety Eight ' When 99 Then 'Ninety Nine ' When 100 Then 'One hundred ' else ' '
End
Return @strWord
End
-
Run the query generator of SAP,paste below query. Apply FMS on field(Create Userdefined field on header level in marketing documents and apply fms on that udf).
declare @Doc_total numeric (19,0)
set @Doc_total=$[OINV.DocTotal]
select dbo.AmountToWords (@Doc_total)
use this udf in pld to get amount in words
Thanks,
Neetu
Hi Rupa,
That formula is only for Crystal Reports.
If you need in PLD through FMS, then try this,
->> 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|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,
Bala
Hi,
I have used this function and attached a FMS as
"declare @Doc_total numeric (19,6)
set @Doc_total=$[$88.33.number]
select dbo.fNumToWords (@Doc_total)"
It shows wrong value and if i try to click the button at the UDF, internal error ...."
Please help
ALTER function [dbo].[fNumToWords] (@decNumber decimal(12, 2))
returns varchar(300)
As
Begin
Declare
@strNumber varchar(100),
@strRupees varchar(200),
@strPaise varchar(100),
@strWords varchar(300),
@intIndex integer,
@intAndFlag integer
Select @strNumber = Cast(@decNumber as varchar(100))
Select @intIndex = CharIndex('.', @strNumber)
if(@decNumber>99999999.99)
BEGIN
RETURN ''
END
If @intIndex > 0
begin
Select @strPaise = dbo.fConvertTens(Right(@strNumber, Len(@strNumber) - @intIndex))
Select @strNumber = SubString(@strNumber, 1, Len(@strNumber) - 3)
If Len(@strPaise) > 0 Select @strPaise = @strPaise + ' paise'
end
Select @strRupees = ''
Select @intIndex=len(@strNumber)
Select @intAndFlag=2
while(@intIndex>0)
begin
if(@intIndex=8)
begin
Select @strRupees=@strRupeesdbo.fConvertDigit(left(@decNumber,1))' Crore '
Select @strNumber=substring(@strNumber,2,len(@strNumber))
Select @intIndex=@intIndex-1
end
else if(@intIndex=7)
begin
if(substring(@strNumber,1,1)='0')
begin
if substring(@strNumber,2,1)<>'0'
begin
if (@strRupees<>NULL and substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and substring(@strNumber,6,1)='0' and substring(@strNumber,7,1)='0' and @intAndFlag=2 and @strPaise=NULL)
begin
Select @strRupees=@strRupees+' and ' dbo.fConvertDigit(substring(@strNumber,2,1))' Lakh '
Select @intAndFlag=1
end
else
begin
Select @strRupees=@strRupeesdbo.fConvertDigit(substring(@strNumber,2,1))' Lakh '
end
Select @strNumber=substring(@strNumber,3,len(@strNumber))
Select @intIndex=@intIndex-2
end
else
begin
Select @strNumber=substring(@strNumber,3,len(@strNumber))
Select @intIndex=@intIndex-2
end
end
else
begin
if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and substring(@strNumber,6,1)='0' and substring(@strNumber,7,1)='0' and @intAndFlag=2 and @strPaise='')
begin
Select @strRupees=@strRupees+' and ' + dbo.fConvertTens(substring(@strNumber,1,2))+' Lakhs '
Select @intAndFlag=1
end
else
begin
Select @strRupees=@strRupeesdbo.fConvertTens(substring(@strNumber,1,2))' Lakhs '
end
Select @strNumber=substring(@strNumber,3,len(@strNumber))
Select @intIndex=@intIndex-2
end
end
else if(@intIndex=6)
begin
if(substring(@strNumber,2,1)<>'0' or substring(@strNumber,3,1)<>'0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and substring(@strNumber,6,1)='0' and @intAndFlag=2 and @strPaise='')
begin
if len(@strRupees) <= 0
begin
if convert(int,substring(@strNumber,1,1)) = 1
begin
Select @strRupees=@strRupees+'' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
Select @intAndFlag=2
end
else
begin
Select @strRupees=@strRupees+'' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
Select @intAndFlag=2
end
end
else
begin
if convert(int,substring(@strNumber,1,1)) = 1
begin
Select @strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
Select @intAndFlag=1
end
else
begin
Select @strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
Select @intAndFlag=1
end
end
end
else
begin
if convert(int,substring(@strNumber,1,1)) = 1
begin
Select @strRupees=@strRupeesdbo.fConvertDigit(substring(@strNumber,1,1))' Lakh '
end
else
begin
Select @strRupees=@strRupeesdbo.fConvertDigit(substring(@strNumber,1,1))' Lakhs '
end
end
Select @strNumber=substring(@strNumber,2,len(@strNumber))
Select @intIndex=@intIndex-1
end
else if(@intIndex=5)
begin
if(substring(@strNumber,1,1)='0')
begin
if substring(@strNumber,2,1)<>'0'
begin
if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and @intAndFlag=2 and @strPaise='')
begin
Select @strRupees=@strRupees+' and ' dbo.fConvertDigit(substring(@strNumber,2,1))' Thousand '
Select @intAndFlag=1
end
else
begin
Select @strRupees=@strRupeesdbo.fConvertDigit(substring(@strNumber,2,1))' Thousand '
end
Select @strNumber=substring(@strNumber,3,len(@strNumber))
Select @intIndex=@intIndex-2
end
else
begin
Select @strNumber=substring(@strNumber,3,len(@strNumber))
Select @intIndex=@intIndex-2
end
end
else
begin
if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and @intAndFlag=2 and @strPaise='')
begin
Select @strRupees=@strRupees' and 'dbo.fConvertTens(substring(@strNumber,1,2))+' Thousand '
Select @intAndFlag=1
end
else
begin
Select @strRupees=@strRupeesdbo.fConvertTens(substring(@strNumber,1,2))' Thousand '
end
Select @strNumber=substring(@strNumber,3,len(@strNumber))
Select @intIndex=@intIndex-2
end
end
else if(@intIndex=4)
begin
if ( (substring(@strNumber,3,1)<>'0' or substring(@strNumber,4,1)<>'0') and substring(@strNumber,2,1)='0' and @intAndFlag=2 and @strPaise='')
begin
Select @strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Thousand '
Select @intAndFlag=1
end
else
begin
Select @strRupees=@strRupeesdbo.fConvertDigit(substring(@strNumber,1,1))' Thousand '
end
Select @strNumber=substring(@strNumber,2,len(@strNumber))
Select @intIndex=@intIndex-1
end
else if(@intIndex=3)
begin
if substring(@strNumber,1,1)<>'0'
begin
Select @strRupees=@strRupeesdbo.fConvertDigit(substring(@strNumber,1,1))' Hundred '
Select @strNumber=substring(@strNumber,2,len(@strNumber))
if( (substring(@strNumber,1,1)<>'0' or substring(@strNumber,2,1)<>'0') and @intAndFlag=2 )
begin
Select @strRupees=@strRupees+' and '
Select @intAndFlag=1
end
Select @intIndex=@intIndex-1
end
else
begin
Select @strNumber=substring(@strNumber,2,len(@strNumber))
Select @intIndex=@intIndex-1
end
end
else if(@intIndex=2)
begin
if substring(@strNumber,1,1)<>'0'
begin
Select @strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))
Select @intIndex=@intIndex-2
end
else
begin
Select @intIndex=@intIndex-1
end
end
else if(@intIndex=1)
begin
if(@strNumber<>'0')
begin
Select @strRupees=@strRupees+dbo.fConvertDigit(@strNumber)
end
Select @intIndex=@intIndex-1
end
continue
end
if len(@strRupees)>0 Select @strRupees=@strRupees+ ' rupees '
IF(len(@strPaise)<>0)
BEGIN
if len(@strRupees)>0 Select @strRupees=@strRupees + ' and '
END
Select @strWords = IsNull(@strRupees, '') + IsNull(@strPaise, '')
select @strWords = @strWords + ' only'
Return @strWords
End
Hi Rupa,
What wrong value you are getting?
Try by updating your functions with the functions below.
1. Function to Convert one Digit Number to words.
Alter Function dbo.fConvertDigit(@decNumber decimal)
returns varchar(6)
as
Begin
declare
@strWords varchar(6)
Select @strWords = Case @decNumber
When '1' then 'One'
When '2' then 'Two'
When '3' then 'Three'
When '4' then 'Four'
When '5' then 'Five'
When '6' then 'Six'
When '7' then 'Seven'
When '8' then 'Eight'
When '9' then 'Nine'
Else ''
end
return @strWords
end
2. Function to convert 2 digit number to words.
Alter Function dbo.fConvertTens(@decNumber varchar(2))
returns varchar(30)
as
Begin
declare @strWords varchar(30)
--Is value between 10 and 19?
If Left(@decNumber, 1) = 1
begin
Select @strWords = Case @decNumber
When '10' then 'Ten'
When '11' then 'Eleven'
When '12' then 'Twelve'
When '13' then 'Thirteen'
When '14' then 'Fourteen'
When '15' then 'Fifteen'
When '16' then 'Sixteen'
When '17' then 'Seventeen'
When '18' then 'Eighteen'
When '19' then 'Nineteen'
end
end
else -- otherwise it's between 20 and 99.
begin
Select @strWords = Case Left(@decNumber, 1)
When '0' then ''
When '2' then 'Twenty '
When '3' then 'Thirty '
When '4' then 'Forty '
When '5' then 'Fifty '
When '6' then 'Sixty '
When '7' then 'Seventy '
When '8' then 'Eighty '
When '9' then 'Ninety '
end
Select @strWords = @strWords + dbo.fConvertDigit(Right(@decNumber, 1))
end
--Convert ones place digit.
return @strWords
end
Hi Neetu,
Thank you for the code. Unfortunately, the first code shows following error
Msg 156, Level 15, State 1, Procedure AmountToWords, Line 26
Incorrect syntax near the keyword 'Else'.
Msg 156, Level 15, State 1, Procedure AmountToWords, Line 29
Incorrect syntax near the keyword 'Else'.
Msg 156, Level 15, State 1, Procedure AmountToWords, Line 35
Incorrect syntax near the keyword 'Else'.
Msg 156, Level 15, State 1, Procedure AmountToWords, Line 43
Incorrect syntax near the keyword 'Else'.
Msg 102, Level 15, State 1, Procedure AmountToWords, Line 194
Incorrect syntax near 'End'
Regards,
Rupa Sarkar
Hi Rupa,
What document that was?
Did u tried by Refreshingly regularly?
Since that FMS is giving the same Internal Error for me also, if i execute it by Magnifier button or Shift+F2.
But its showing exact results by refresh regularly.
Also try the FMS query as
declare @Doc_total numeric (19,6)
set @Doc_total=$[$OPOR.DocTotal]
select dbo.fNumToWords (@Doc_total)
Change the Document table accordingly instead of OPOR.
Regards,
Bala
Edited by: Balakumar Viswanathan on Jan 6, 2011 4:45 PM
Hi Bala,
I am in A/R invoice. I had selected the button "refresh regularly". my UDF is of "TEXT" type at Title level. The amount or the TOTAL amount with tax needs to be get printed. So I gave
declare @Doc_total numeric (19,6)
set @Doc_total=$[$91.29.number]
select dbo.fNumToWords (@Doc_total)
in the FMS query.
91.29 for "TOTAL" or i can give it like "Balance Due" (88.33). But all the cases it is "One rupees only" with the "internal error" issue as of urs
Regards,
Rupa
Hi rupa,
Try 1st funtion in sql as below:
Create function [dbo].[AmountToWords]
(
@InNumber Numeric(18,2)
)
returns VARCHAR(2000)
as BEGIN
Declare @Num Varchar(20)
Declare @Dec Varchar(3)
Declare @Return Varchar(2000)
Set @Dec = SubString(Convert(Varchar(20),@Innumber),Len(Convert(Varchar(20),@Innumber))-2,3)
Set @Num = SubString(Convert(Varchar(20),@Innumber),1, Len(Convert(Varchar(20),@Innumber))-3)
Declare @Hundred Char(8)
Declare @HundredAnd Char(12)
Declare @Thousand Char(9)
Declare @Lakh Char(5)
Declare @Lakhs Char(6)
Declare @Crore Char(6)
Declare @Crores Char(7)
Set @Hundred = 'Hundred '
Set @Thousand = 'Thousand ' Set @Lakh = 'Lakh ' Set @Lakhs = 'Lakhs ' Set @Crore = 'Crore ' Set @Crores = 'Crores '
Set @HundredAnd = 'Hundred and '
if Len(@Num) = 1
Begin Set @Return = dbo.GetTextValue(@Num) End Else
if Len(@Num) = 2
Begin Set @Return = dbo.GetTextValue(@Num) End Else
if Len(@Num) = 3
Begin Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Hundred
IF SubString(@num,2,2)= '00'
Set @Return = @Return + 'And '
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2))
End
Else if Len(@Num) = 4
Begin Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Thousand
If SubString(@Num,2,1)= '0'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,1)) + @Hundred
IF SubString(@num,3,2)= '00'
Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2))
End
Else if Len(@Num) = 5
Begin Set @Return = dbo.GetTextValue(SubString(@Num,1,2)) + @Thousand
If SubString(@Num,3,1)= '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,1)) + @Hundred
IF SubString(@num,4,2) ='00'
Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2))
End
Else if Len(@Num) = 6
Begin
If SubString(@Num,1,1) = '1'
Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Lakh
Else
Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Lakhs
If SubString(@Num,2,2)= '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2)) + @Thousand
If SubString(@Num,4,1) ='0'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,1)) + @Hundred
IF SubString(@num,5,2) ='00'
Set @Return = @Return + 'And '
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2)) End
Else if Len(@Num) = 7
Begin Set @Return = dbo.GetTextValue(SubString(@Num,1,2)) + @Lakhs
If SubString(@Num,3,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2)) + @Thousand
If SubString(@Num,6,1)= '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,1)) + @Hundred
IF SubString(@num,6,2)= '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2)) End
Else if Len(@Num) = 8
Begin Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Crore
If SubString(@Num,2,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2)) + @Lakhs
If SubString(@Num,4,2) ='00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2)) + @Thousand
If SubString(@Num,6,1) ='0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,1)) + @Hundred
IF SubString(@num,7,2) ='00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,2))
End Else if Len(@Num) = 9
Begin Set @Return = dbo.GetTextValue(SubString(@Num,1,2)) + @Crores
If SubString(@Num,3,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2)) + @Lakhs
If SubString(@Num,5,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2)) + @Thousand
If SubString(@Num,7,1)= '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,1)) + @Hundred
IF SubString(@num,8,2) ='00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,2)) End
Else if Len(@Num) = 10
Begin Set @Return = dbo.GetTextValue(Substring(@Num,1,1)) + @Hundred
IF Substring(@Num,2,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2)) Set @Return = @Return + @Crores
If SubString(@Num,4,2) ='00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2)) + @Lakhs
If SubString(@Num,6,2) ='00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2)) + @Thousand
If SubString(@Num,8,1)= '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,1)) + @Hundred
IF SubString(@num,9,2)= '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,9,2)) End
Else if Len(@Num) = 11
Begin Set @Return = dbo.GetTextValue(Substring(@Num,1,1)) + @Thousand
IF SubString(@Num,2,1) ='0' Set @Return = @Return + dbo.GetTextValue(Substring(@Num,2,1)) + @Hundred
IF Substring(@Num,3,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2)) Set @Return = @Return + @Crores
If SubString(@Num,5,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2)) + @Lakhs
If SubString(@Num,7,2) ='00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,2)) + @Thousand
If SubString(@Num,9,1) ='0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,9,1)) + @Hundred
IF SubString(@num,10,2)= '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,10,2)) End
Else if Len(@Num) = 12
Begin Set @Return = dbo.GetTextValue(Substring(@Num,1,2)) + @Thousand
IF SubString(@Num,3,1)= '0' Set @Return = @Return + dbo.GetTextValue(Substring(@Num,3,1)) + @Hundred
IF Substring(@Num,4,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2)) Set @Return = @Return + @Crores
If SubString(@Num,6,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2)) + @Lakhs
If SubString(@Num,8,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,2)) + @Thousand
If SubString(@Num,10,1)= '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,10,1)) + @Hundred
IF SubString(@num,11,2)= '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,11,2)) End
Else if Len(@Num) = 13
Begin Set @Return = dbo.GetTextValue(Substring(@Num,1,1)) + @Lakh
If Substring(@Num,2,2) ='00' Set @Return = @Return + dbo.GetTextValue(Substring(@Num,2,2)) + @Thousand
IF SubString(@Num,4,1)= '0' Set @Return = @Return + dbo.GetTextValue(Substring(@Num,4,1)) + @Hundred
IF Substring(@Num,5,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2)) Set @Return = @Return + @Crores
If SubString(@Num,7,2) ='00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,2)) + @Lakhs
If SubString(@Num,9,2) ='00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,9,2)) + @Thousand
If SubString(@Num,11,1)= '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,11,1)) + @Hundred
IF SubString(@num,12,2)= '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,12,2)) End
Else if Len(@Num) = 14
Begin Set @Return = dbo.GetTextValue(Substring(@Num,1,2)) + @Lakhs
If Substring(@Num,3,2) ='00' Set @Return = @Return + dbo.GetTextValue(Substring(@Num,3,2)) + @Thousand
IF SubString(@Num,5,1)= '0' Set @Return = @Return + dbo.GetTextValue(Substring(@Num,5,1)) + @Hundred
IF Substring(@Num,6,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2)) Set @Return = @Return + @Crores
If SubString(@Num,8,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,2)) + @Lakhs
If SubString(@Num,10,2)= '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,10,2)) + @Thousand
If SubString(@Num,12,1) ='0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,12,1)) + @Hundred
IF SubString(@num,13,2)= '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,13,2)) End
If @Dec= '.00' Set @Return = @Return + 'Rupees And ' + dbo.GetTextValue(SubString(@Dec,2,2)) + 'Paise Only'
ELSE Set @Return =@Return+'Zero Rupees Only'
Return @return
End
thanks,
Neetu
create 2nd sql function:
create Function [dbo].[GetTextValue]
(
@dblNumber Numeric
)
Returns Varchar(1000)
As
Begin
Declare @StrWord Varchar(400)
SEt @strWord = Case @dblNumber When 1 Then 'One ' When 2 Then 'Two ' When 3 Then 'Three ' When 4 Then 'Four ' When 5 Then 'Five ' When 6 Then 'Six ' When 7 Then 'Seven ' When 8 Then 'Eight ' When 9 Then 'Nine ' When 10 Then 'Ten ' When 11 Then 'Eleven ' When 12 Then 'Twelve ' When 13 Then 'Thirteen ' When 14 Then 'Fourteen ' When 15 Then 'Fifteen ' When 16 Then 'Sixteen ' When 17 Then 'Seventeen ' When 18 Then 'Eighteen ' When 19 Then 'Nineteen ' When 20 Then 'Twenty ' When 21 Then 'Twenty One ' When 22 Then 'Twenty Two ' When 23 Then 'Twenty Three ' When 24 Then 'Twenty Four ' When 25 Then 'Twenty Five ' When 26 Then 'Twenty Six ' When 27 Then 'Twenty Seven ' When 28 Then 'Twenty Eight ' When 29 Then 'Twenty Nine ' When 30 Then 'Thirty ' When 31 Then 'Thirty One ' When 32 Then 'Thirty Two ' When 33 Then 'Thirty Three ' When 34 Then 'Thirty Four ' When 35 Then 'Thirty Five ' When 36 Then 'Thirty Six ' When 37 Then 'Thirty Seven ' When 38 Then 'Thirty Eight ' When 39 Then 'Thirty Nine ' When 40 Then 'Fourty ' When 41 Then 'Fourty One ' When 42 Then 'Fourty Two ' When 43 Then 'Fourty Three ' When 44 Then 'Fourty Four ' When 45 Then 'Fourty Five ' When 46 Then 'Fourty Six ' When 47 Then 'Fourty Seven ' When 48 Then 'Fourty Eight ' When 49 Then 'Fourty Nine ' When 50 Then 'Fifty ' When 51 Then 'Fifty One ' When 52 Then 'Fifty Two ' When 53 Then 'Fifty Three ' When 54 Then 'Fifty Four ' When 55 Then 'Fifty Five ' When 56 Then 'Fifty Six ' When 57 Then 'Fifty Seven ' When 58 Then 'Fifty Eight ' When 59 Then 'Fifty Nine ' When 60 Then 'Sixty ' When 61 Then 'Sixty One ' When 62 Then 'Sixty Two ' When 63 Then 'Sixty Three ' When 64 Then 'Sixty Four ' When 65 Then 'Sixty Five ' When 66 Then 'Sixty Six ' When 67 Then 'Sixty Seven ' When 68 Then 'Sixty Eight ' When 69 Then 'Sixty Nine ' When 70 Then 'Seventy ' When 71 Then 'Seventy One ' When 72 Then 'Seventy Two ' When 73 Then 'Seventy Three ' When 74 Then 'Seventy Four ' When 75 Then 'Seventy Five ' When 76 Then 'Seventy Six ' When 77 Then 'Seventy Seven ' When 78 Then 'Seventy Eight ' When 79 Then 'Seventy Nine ' When 80 Then 'Eighty ' When 81 Then 'Eighty One ' When 82 Then 'Eighty Two ' When 83 Then 'Eighty Three ' When 84 Then 'Eighty Four ' When 85 Then 'Eighty Five ' When 86 Then 'Eighty Six ' When 87 Then 'Eighty Seven ' When 88 Then 'Eighty Eight ' When 89 Then 'Eighty Nine ' When 90 Then 'Ninety ' When 91 Then 'Ninety One ' When 92 Then 'Ninety Two ' When 93 Then 'Ninety Three ' When 94 Then 'Ninety Four ' When 95 Then 'Ninety Five ' When 96 Then 'Ninety Six ' When 97 Then 'Ninety Seven ' When 98 Then 'Ninety Eight ' When 99 Then 'Ninety Nine ' When 100 Then 'One hundred ' else ' '
End
Return @strWord
End
then apply fms as suggest below:
declare @Doc_total numeric (19,0) set @Doc_total=$[OINV.DocTotal] select dbo.AmountToWords (@Doc_total){
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
10 | |
10 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.