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

How to convet Amount To Words in PLD

Hi All,

I am working in PLD for SAP B1 2007B. I have requirement where I have to print the amount in words in PLD.I want this amount in Indian format (Five Lakhs Ten thosusand and sixty Five ) .Is this possible in PLD?

Thanks & Regards,

Sheetal

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 10, 2008 at 07:01 AM

    Hi,

    According to you condition

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

    And in FMS- Refresh Regularly on Document Total

    Thanks & Regards,

    Somi Mishra

    Edited by: somi mishra on Dec 10, 2008 12:31 PM

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2008 at 07:46 AM

    Hi Sheetal,

    Click on amount field (in figure) got to Properties for that field in Format tab you will find "sum in words check box", just click on the check box. Now amount will come in words.

    As far as Indian format is concened it won't come in Indian format, for this you will have to create a UDF.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2008 at 07:53 AM

    It is the SAP System Behaviour, that it prints the sum in words as per European Format.

    Say you have a value 'INR 5,200,000.00', system prints as 'Five Million Two Hundred Thousand' instead of 'Fifty Two Lakhs'.

    As a workaround, declare a UDF in Title level of Marketing documents and insist the users to enter the Sum in words in required format and show the UDF in PLD.

    Add a comment
    10|10000 characters needed characters exceeded

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

    Hi ,

    Try -

    *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**************************************

    select dbo.AmountToWords (doctotal) ,doctotal from OPOR

    select dbo.AmountToWords (208000.23)

    Edited by: somi mishra on Dec 8, 2008 5:53 PM

    Add a comment
    10|10000 characters needed characters exceeded

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

    Hello,

    the Format Option 'Sum in Words' displays works in the following way:

    If you set the Option for

    a) a Datafield, which contains of 1742.02,

    it prints as "One thousand seven hundred forty-two and Two

    hundredths"

    b) a Variablefield, which contains £1742.02

    it prints as "One thousand seven hundred forty-two Pounds

    and two cents."

    If the Value that you want to print in words is only available as a

    Datafield, you can create a concat of the Datafield with the Datafield

    that displays the Currency.

    The Setting to control the Currency description can be found under

    Administration->Setup->Financials->Currencies

    => the Value that you set for International Description/English

    Description and Hundredth Name and English Hundredth Name

    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.