Skip to Content
author's profile photo Former Member
Former Member

Amount in words

Dear All

At the time of checking the print preview of A/R invoice, it prints amount in words Hundredths insted of paise

Explain with an example

My Invoice Value 1000.16 INR ( Indian Rupees)

Now it shows: One thousand and Sixteen Hundredths

We expect:One thousand and Sixteen paise

I am using SAP Business One 2005 B PL 39

Only one currency is using in the system ( that is INR)

My currency settings

Code Currency International Code Hundredth Name English English Hundredth Name

INR Indian Rupee INR Paise Indian Rupee Paise

Pls help me

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2008 at 12:50 PM

    Goto Administration -> Setup -> Financials -> Currencies

    In the INR Currency Code, set 'Paise Only' in 'English Hundredth Name'

    Set 'Indian Rupee' in 'English'

    If your amount is 'Rs 9466.20' and it would print as

    Nine thousand four hundred sixty six India Rupee and twenty Paise Only.

    Also check this thread for reference

    [money-words]

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 21, 2008 at 11:15 AM

    Hi,Muhammed Rashid

    you can do like this it will help full for you.

    First Create 2 funtions in SQL server: and apply trigger for system filed you can get data automatically amount in words where ever you can use this funtion.and create one UDF in Marketing documents ex:U_AmountInwords

    1.funtion

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    select dbo.AmountToWords (4980)

    ALTER 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 '

    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 + ' '

    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 + ''

    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 + ''

    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 + ''

    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 + ''

    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 + ' '

    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 + ''

    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 + ''

    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 + ''

    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 + ''

    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 + ''

    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 + ''

    Set @Return = @Return + dbo.GetTextValue(SubString(@Num,13,2))

    End

    If @Dec '.00'

    Set @Return = @Return + ' And ' + dbo.GetTextValue(SubString(@Dec,2,2)) + 'Paise'

    Return @return

    END

    2.This is for Get texl vavlue funtion:

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    ALTER function dbo.GetTextValue

    (

    @num varchar(20)

    )

    returns VARCHAR(2000)

    as

    BEGIN

    Declare @Return varchar(2000)

    Declare @temp char(1)

    Declare @tempZero char(1)

    Declare @Zero char(1)

    Declare @One char(4)

    Declare @Two char(4)

    Declare @Three char(6)

    Declare @Four char(5)

    Declare @Five char(5)

    Declare @Six char(4)

    Declare @Seven char(6)

    Declare @Eight char(6)

    Declare @Nine char(5)

    Declare @Eleven char(7)

    Declare @Twelve char(7)

    Declare @Thirteen char(9)

    Declare @Fourteen char(9)

    Declare @Fifteen char(8)

    Declare @Sixteen char(8)

    Declare @Seventeen char(10)

    Declare @Eighteen char(9)

    Declare @Nineteen char(9)

    Declare @Ten char(4)

    Declare @Twenty char(6)

    Declare @Thirty char(7)

    Declare @Forty char(6)

    Declare @Fifty char(6)

    Declare @Sixty char(6)

    Declare @Seventy char(8)

    Declare @Eighty char(7)

    Declare @Ninety char(7)

    set @tempZero = '0'

    set @Zero = ''

    set @One = 'One '

    set @Two = 'Two '

    set @Three = 'Three '

    set @Four = 'Four '

    set @Five = 'Five '

    set @Six = 'Six '

    set @Seven = 'Seven '

    set @Eight = 'Eight '

    set @Nine = 'Nine '

    set @Eleven = 'Eleven '

    set @Twelve = 'Twelve '

    set @Thirteen = 'Thirteen '

    set @Fourteen = 'Fourteen '

    set @Fifteen = 'Fifteen '

    set @Sixteen = 'Sixteen '

    set @Seventeen = 'Seventeen '

    set @Eighteen = 'Eighteen '

    set @Nineteen = 'Nineteen '

    set @Ten = 'Ten '

    set @Twenty = 'Twenty '

    set @Thirty = 'Thirty '

    set @Forty = 'Forty '

    set @Fifty = 'Fifty '

    set @Sixty = 'Sixty '

    set @Seventy = 'Seventy '

    set @Eighty = 'Eighty '

    set @Ninety = 'Ninety '

    IF Len(@num) = 2

    Begin

    If SubString(@num,2,1) '0'

    Begin

    Set @temp = SubString(@num,1,1)

    Set @Return = dbo.GetTextValue(@temp+@tempZero)

    Set @Return = @Return + dbo.GetTextValue(SubString(@num,2,1))

    End

    END

    If @num = 0

    Begin

    set @Return = @Zero

    End

    Else if @num = 1

    Begin

    set @Return = @One

    End

    Else if @num = 2

    Begin

    set @Return = @Two

    End

    Else if @num = 3

    Begin

    set @Return = @Three

    End

    Else if @num = 4

    Begin

    set @Return = @Four

    End

    Else if @num = 5

    Begin

    set @Return = @Five

    End

    Else if @num = 6

    Begin

    set @Return = @Six

    End

    Else if @num = 7

    Begin

    set @Return = @Seven

    End

    Else if @num = 8

    Begin

    set @Return = @Eight

    End

    Else if @num = 9

    Begin

    set @Return = @Nine

    End

    Else if @num = 10

    Begin

    set @Return = @Ten

    End

    Else if @num = 11

    Begin

    set @Return = @Eleven

    End

    Else if @num = 12

    Begin

    set @Return = @Twelve

    End

    Else if @num = 13

    Begin

    set @Return = @Thirteen

    End

    Else if @num = 14

    Begin

    set @Return = @Fourteen

    End

    Else if @num = 15

    Begin

    set @Return = @Fifteen

    End

    Else if @num = 16

    Begin

    set @Return = @Sixteen

    End

    Else if @num = 17

    Begin

    set @Return = @Seventeen

    End

    Else if @num = 18

    Begin

    set @Return = @Eighteen

    End

    Else if @num = 19

    Begin

    set @Return = @Nineteen

    End

    Else if @num = 20

    Begin

    set @Return = @Twenty

    End

    Else if @num = 30

    Begin

    set @Return = @Thirty

    End

    Else if @num = 40

    Begin

    set @Return = @Forty

    End

    Else if @num = 50

    Begin

    set @Return = @Fifty

    End

    Else if @num = 60

    Begin

    set @Return = @Sixty

    End

    Else if @num = 70

    Begin

    set @Return = @Seventy

    End

    Else if @num = 80

    Begin

    set @Return = @Eighty

    End

    Else if @num = 90

    Begin

    set @Return = @Ninety

    End

    Return @Return

    END

    3.for Triger purpose: it will update automatically in your UDF.

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    -- =============================================

    -- Author: <Author,,Name>

    -- Create date: <Create Date,,>

    -- Description: <Description,,>

    -- =============================================

    ALTER TRIGGER [AmountToWordsTrigger_OINV]

    ON [dbo].[OINV]

    AFTER INSERT,UPDATE

    AS

    BEGIN

    declare @name nvarchar(8)

    select top 1 @name= Project from inserted

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    UPDATE [dbo].[OINV]

    SET U_AmountInwords = [dbo].[AmountToWords] (DocTotal)+'Only'

    WHERE DocEntry IN ( SELECT DocEntry From Inserted)

    update dbo.JDT1 set Project= @name

    where baseref in (Select DocNum from Inserted)

    END

    Regds,

    sampath kumar devunuri.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.