cancel
Showing results for 
Search instead for 
Did you mean: 

Indian Currency Settings

Former Member
0 Kudos

Hi Experts,

Can anyone help me, how to get Indian currency format in SAP Business One. For ex: 1,00,000 I just want to display as One Lakh Rupees Only.

Thanks in advance

Regards

Neslin

Accepted Solutions (0)

Answers (3)

Answers (3)

kothandaraman_nagarajan
Active Contributor
0 Kudos
KennedyT21
Active Contributor
0 Kudos

Hi Neslin Francis ....

*1.    First you have to create UDF.*

*2.   Then Run this function*    [AmountToWords]  in SQL

   3.    After Run this Function  You write   

Query for formate search-

select  dbo.AmountToWords (doctotal) from OPOR where OPOR.Docnum=$[OPOR.Docnum]

   4.    Take FMS on Your UDF.(Refresh Vendor Code)

   

****************************************Start  Function AmountToWords**************************************

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AmountToWords]') and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[AmountToWords]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTextValue]') and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[GetTextValue]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

CREATE function 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'    

ELSE

   Set @Return =@Return +'Rupees'

Return @return

End

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

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

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

****************************************End   Function AmountToWords**************************************

declare @Doc_total numeric (19,6) set @Doc_total=$[OPOR.DocTotal] 
select  dbo.AmountToWords (@Doc_total)

And in FMS- Refresh Regularly  on Document Total

Hope Helpful

Regards

Kennedy

Former Member
0 Kudos

Hi Neslin,

There have quite a few discussions regarding your issue on the forum before. Search it harder.

Thanks,

Gordon