Amount in words problem

[Former Member]

Hi All,

Please help me to get Amount in words in Indian standards

i.e

if amount is 125000 then it should print

"One Lakh Twenty-five thousand rupees only"

All these I need in a Check Print layout

here i can see amount but how will i get Amount in words there.

I have made a query CheckPrint

which contains follwing fields from OPCH (A/P Invoice table)

DocNum , DocDate, CardName, DocTotal.

I just want DocTotal in words so that i can display it on the layout and get the print on Cheque.

I am using SAP 2005 B version.

Please help I am not a trained professional in SAP Business One

Kindly do the needful.

Thanks and Regards,

Murtaza Piyersoap

[Former Member · Accepted solution]

hI,Murtaza Piyersoap.

Can you do this steps in the LAyout.

Amount in Words in Indian Format

Objective: If Document Total is INR 352,940.60 then Amount in Words should be as u201CThree hundred fifty-two thousand nine hundred and forty Indian rupee and Sixty Paisau201D

Here we need

1) One System Variable Named u201CField_092u201D

2) Three Formula fields named u201CField_093u201D,u201DField_094u201D and u201CField_098u201D

3) Three Database Fields named u201CField_095u201D,u201Dfield_096u201D and u201CField_097u201Detc.

4) Field_098 is the Out Last Formula to print amount in words in Indian Format except this hide all the fields.

Hope this will help you.

Regds,

Sampath Kuamr Devunuri.

[Former Member · Accepted solution]

Hi,

Thanks will try and let you know.

Regards,

Murtaza

[Former Member · Accepted solution]

Hi Murtaza,

I had a similar issue, and I addressed it by:

1. Creating a UDF in the Payments>Title called AmountInWord of Type AlphaNumeric Structure Text (i.e. in OVPM Table)

2. Created a Function called I_ConvertN2W in the database, which converts any number into Words as per Indian Style i.e. Lakh, Crore

3. In the SBO_SP_TransactionNotification, I am filling the Value in the UDF on every Add of Outgoing Payment

4. In the PLD for the printing of the cheque, I am using the UDF, instead of the SAPB1 Amount (which is non Indian Style)

Step 1:

Create the UDF as mentioned above, I guess no further explanation required

Step 2:

Creating the Function I_ConvertN2W, but first you will have to create all the support functions, I am listing the function in the order of creation

CREATE FUNCTION I_Modulus (@INPUT INT, @DIVIDER INT)

RETURNS INT AS

BEGIN

DECLARE @RETVAL AS INT, @MODULUS AS INT

IF @INPUT = @DIVIDER

BEGIN

SET @RETVAL = 0

END

IF @INPUT < @DIVIDER

BEGIN

SET @RETVAL = @INPUT

END

SET @MODULUS = @INPUT

WHILE @MODULUS >= @DIVIDER

BEGIN

SET @MODULUS = @MODULUS - @DIVIDER

END

SET @RETVAL = @MODULUS

RETURN (@RETVAL)

END

CREATE FUNCTION I_Mid (@sInput VARCHAR(8000), @iStart INT, @iLen INT)

RETURNS VARCHAR(8000) AS

BEGIN

--Adapted from the Mid function of Visual Basic

DECLARE @sRetVal AS VARCHAR(8000)

IF @iLen = 0

BEGIN

SET @iLen = LEN(@sInput)-(@iStart-1)

END

SET @sRetVal = RIGHT(@sInput,LEN(@sInput) - (@iStart-1))

SET @sRetVal = LEFT(@sRetVal,@iLen)

RETURN (@sRetVal)

END

CREATE FUNCTION I_CN2W_GetWord (@cValue INT)

RETURNS nvarchar(10)

AS

BEGIN

DECLARE @sRetVal AS NVARCHAR(10)

SET @sRetVal =

CASE

WHEN @cValue = 0 THEN ''

WHEN @cValue = 1 THEN 'One'

WHEN @cValue = 2 THEN 'Two'

WHEN @cValue = 3 THEN 'Three'

WHEN @cValue = 4 THEN 'Four'

WHEN @cValue = 5 THEN 'Five'

WHEN @cValue = 6 THEN 'Six'

WHEN @cValue = 7 THEN 'Seven'

WHEN @cValue = 8 THEN 'Eight'

WHEN @cValue = 9 THEN 'Nine'

WHEN @cValue = 10 THEN 'Ten'

WHEN @cValue = 11 THEN 'Eleven'

WHEN @cValue = 12 THEN 'Twelve'

WHEN @cValue = 13 THEN 'Thirteen'

WHEN @cValue = 14 THEN 'Fourteen'

WHEN @cValue = 15 THEN 'Fifteen'

WHEN @cValue = 16 THEN 'Sixteen'

WHEN @cValue = 17 THEN 'Seventeen'

WHEN @cValue = 18 THEN 'Eighteen'

WHEN @cValue = 19 THEN 'Ninteen'

WHEN @cValue = 20 THEN 'Twenty'

WHEN @cValue = 30 THEN 'Thirty'

WHEN @cValue = 40 THEN 'Forty'

WHEN @cValue = 50 THEN 'Fifty'

WHEN @cValue = 60 THEN 'Sixty'

WHEN @cValue = 70 THEN 'Seventy'

WHEN @cValue = 80 THEN 'Eighty'

WHEN @cValue = 90 THEN 'Ninty'

END

RETURN (@sRetVal)

END

CREATE FUNCTION I_CN2W_GetTens (@sValue as NVARCHAR(100))

RETURNS NVARCHAR(100) AS

BEGIN

DECLARE @sTens AS NVARCHAR(100), @iNum AS INT, @cnt AS INT, @iDigit AS INT, @sRetVal AS NVARCHAR(100)

SET @iNum = CAST(@sValue AS INT)

SET @sTens = ''

IF @iNum <= 20

BEGIN

SET @sRetVal = dbo.I_CN2W_GetWord(@iNum)

END

ELSE

BEGIN

SET @cnt = 1

WHILE @iNum > 0

BEGIN

SET @iDigit = dbo.I_Modulus(@iNum, 10)

IF @cnt = 1

SET @sTens = dbo.I_CN2W_GetWord(@iDigit)

ELSE

BEGIN

SET @iDigit = @iDigit * 10

SET @sTens = dbo.I_CN2W_GetWord(@iDigit) + ' ' + @sTens

END

SET @cnt = (@cnt + 1)

SET @iNum = (@iNum / 10)

END

SET @sRetVal = @sTens

END

RETURN (@sRetVal)

END

CREATE FUNCTION I_CN2W_GetHundreds (@sValue NVARCHAR(100))

RETURNS NVARCHAR(100) AS

BEGIN

DECLARE @rstHun as NVARCHAR(100), @sHun as NVARCHAR(100), @sTens AS NVARCHAR(100), @sRetVal as NVARCHAR(100)

IF RTRIM(LTRIM(@sValue)) = '000'

BEGIN

SET @sRetVal = ''

END

ELSE

BEGIN

SET @sHun = SUBSTRING(RTRIM(LTRIM(@sValue)),1,1)

SET @sTens = dbo.I_CN2W_GetTens(SUBSTRING(RTRIM(LTRIM(@sValue)),2,2))

IF (LEN(RTRIM(LTRIM(@sTens))) > 0)

BEGIN

SET @rstHun = dbo.I_CN2W_GetWord(CONVERT(INT, @sHun))

IF (LEN(RTRIM(LTRIM(@rstHun))) > 0)

BEGIN

SET @rstHun = @rstHun + ' Hundred and ' + @sTens

END

ELSE

BEGIN

SET @rstHun = @sTens

END

END

ELSE

BEGIN

SET @rstHun = dbo.I_CN2W_GetWord(CONVERT(INT, @sHun)) + ' Hundred'

END

SET @sRetVal = @rstHun

END

RETURN (@sRetVal)

END

CREATE FUNCTION I_CN2W_GetThousands (@sValue NVARCHAR(100))

RETURNS NVARCHAR(100) AS

BEGIN

DECLARE @rstHun as NVARCHAR(100), @rstThou AS NVARCHAR(100), @strHun as NVARCHAR(100), @strNum AS NVARCHAR(100), @strThou AS NVARCHAR(100)

DECLARE @sRetVal as NVARCHAR(100)

IF @sValue = '00000'

BEGIN

SET @sRetVal = ''

END

ELSE

BEGIN

SET @strNum = RTRIM(LTRIM(@sValue))

SET @strHun =

CASE

WHEN LEN(@strNum) = 4 THEN SUBSTRING(@strNum,2,3)

ELSE SUBSTRING(@strNum,3,3)

END

SET @strThou = SUBSTRING(@strNum,1,LEN(@strNum)-3)

SET @rstHun = dbo.I_CN2W_GetHundreds(@strHun)

SET @rstThou =

CASE

WHEN CAST(@strThou AS INT) <= 20 THEN dbo.I_CN2W_GetWord(CONVERT(INT, @strThou))

ELSE dbo.I_CN2W_GetTens(@strThou)

END

IF (LEN(@rstThou)>0)

BEGIN

SET @rstThou = @rstThou + ' Thousand ' + @rstHun

END

ELSE

BEGIN

SET @rstThou = @rstHun

END

SET @sRetVal = @rstThou

END

RETURN (@sRetVal)

END

CREATE FUNCTION I_CN2W_GetLakhs (@sValue NVARCHAR(100))

RETURNS NVARCHAR(100) AS

BEGIN

DECLARE @rstThou as NVARCHAR(100), @rstLakhs AS NVARCHAR(100), @strLakhs as NVARCHAR(100), @strNum AS NVARCHAR(100), @strThou AS NVARCHAR(100)

DECLARE @sRetVal as NVARCHAR(100)

IF @sValue = '0000000'

BEGIN

SET @sRetVal = ''

END

ELSE

BEGIN

SET @strNum = RTRIM(LTRIM(@sValue))

SET @strThou =

CASE

WHEN LEN(@strNum) = 6 THEN SUBSTRING(@strNum,2,5)

ELSE SUBSTRING(@strNum,3,5)

END

SET @strLakhs = SUBSTRING(@strNum,1,LEN(@strNum)-5)

SET @rstThou = dbo.I_CN2W_GetThousands(@strThou)

SET @rstLakhs =

CASE

WHEN CAST(@strLakhs AS INT) <= 20 THEN dbo.I_CN2W_GetWord(CONVERT(INT, @strLakhs))

ELSE dbo.I_CN2W_GetTens(@strLakhs)

END

IF (LEN(@rstLakhs)>0)

BEGIN

SET @rstLakhs = @rstLakhs + ' Lakh ' + @rstThou

END

ELSE

BEGIN

SET @rstLakhs = @rstThou

END

SET @sRetVal = @rstLakhs

END

RETURN (@sRetVal)

END

CREATE FUNCTION I_CN2W_GetCrores (@sValue NVARCHAR(255))

RETURNS NVARCHAR(255) AS

BEGIN

DECLARE @rstCro as NVARCHAR(255), @rstLakhs AS NVARCHAR(255), @strNum as NVARCHAR(100), @strLakhs AS NVARCHAR(255), @strCro AS NVARCHAR(255)

DECLARE @sRetVal as NVARCHAR(255)

IF @sValue = '000000000'

BEGIN

SET @sRetVal = ''

END

ELSE

BEGIN

SET @strNum = RTRIM(LTRIM(@sValue))

SET @strLakhs =

CASE

WHEN LEN(@strNum) = 8 THEN SUBSTRING(@strNum,2,7)

ELSE SUBSTRING(@strNum,3,7)

END

SET @strCro = SUBSTRING(@strNum,1,LEN(@strNum)-7)

SET @rstLakhs = dbo.I_CN2W_GetLakhs(@strLakhs)

SET @rstCro =

CASE

WHEN CAST(@strCro AS INT) <= 20 THEN dbo.I_CN2W_GetWord(CONVERT(INT, @strCro))

ELSE dbo.I_CN2W_GetTens(@strCro)

END

IF LEN(@rstCro)>0

BEGIN

SET @rstCro = @rstCro + ' Crore ' + @rstLakhs

END

ELSE

BEGIN

SET @rstCro = @rstLakhs

END

SET @sRetVal = @rstCro

END

RETURN (@sRetVal)

END

CREATE FUNCTION I_CN2W_GetArabs (@sValue NVARCHAR(255))

RETURNS NVARCHAR(255) AS

BEGIN

DECLARE @rstArab as NVARCHAR(255), @rstCro AS NVARCHAR(255), @strNum as NVARCHAR(255), @strCro AS NVARCHAR(255), @strArab AS NVARCHAR(255)

DECLARE @sRetVal as NVARCHAR(255)

IF @sValue = '00000000000'

BEGIN

SET @sRetVal = ''

END

ELSE

BEGIN

SET @strNum = RTRIM(LTRIM(@sValue))

SET @strCro =

CASE

WHEN LEN(@strNum) = 10 THEN SUBSTRING(@strNum,2,9)

ELSE SUBSTRING(@strNum,3,9)

END

SET @strArab = SUBSTRING(@strNum,1,LEN(@strNum)-9)

SET @rstCro = dbo.I_CN2W_GetCrores(@strCro)

SET @rstArab =

CASE

WHEN CAST(@strArab AS INT) <= 20 THEN dbo.I_CN2W_GetWord(CONVERT(INT, @strArab))

ELSE dbo.I_CN2W_GetTens(@strArab)

END

IF LEN(@rstArab)>0

BEGIN

SET @rstArab = @rstArab + ' Arab ' + @rstCro

END

ELSE

BEGIN

SET @rstArab = @rstCro

END

SET @sRetVal = @rstArab

END

RETURN (@sRetVal)

END

CREATE FUNCTION I_CN2W_GetKharabs (@sValue NVARCHAR(255))

RETURNS NVARCHAR(255) AS

BEGIN

DECLARE @rstArab as NVARCHAR(255), @rstKharab AS NVARCHAR(255), @strNum as NVARCHAR(255), @strKharab AS NVARCHAR(255), @strArab AS NVARCHAR(255)

DECLARE @sRetVal as NVARCHAR(255)

IF @sValue = '10000000000000'

BEGIN

SET @sRetVal = 'Hundred Kharab '

END

ELSE

BEGIN

SET @strNum = RTRIM(LTRIM(@sValue))

SET @strArab =

CASE

WHEN LEN(@strNum) = 12 THEN SUBSTRING(@strNum,2,11)

ELSE SUBSTRING(@strNum,3,11)

END

SET @strKharab = SUBSTRING(@strNum,1,LEN(@strNum)-11)

SET @rstArab = dbo.I_CN2W_GetArabs(@strArab)

SET @rstKharab =

CASE

WHEN CAST(@strKharab AS INT) <= 20 THEN dbo.I_CN2W_GetWord(CONVERT(INT, @strKharab))

ELSE dbo.I_CN2W_GetTens(@strKharab)

END

IF @rstKharab IS NOT NULL

BEGIN

SET @rstKharab = @rstKharab + ' Kharab ' + @rstArab

END

ELSE

BEGIN

SET @rstKharab = @rstArab

END

SET @sRetVal = @rstKharab

END

RETURN (@sRetVal)

END

CREATE FUNCTION I_CN2W_GetAbvKharab (@sValue NVARCHAR(255))

RETURNS NVARCHAR(255) AS

BEGIN

DECLARE @rstArab as NVARCHAR(255), @rstAbvKharab AS NVARCHAR(255), @strNum as NVARCHAR(255), @strArab AS NVARCHAR(255), @strAbvKharab AS NVARCHAR(255)

DECLARE @sRetVal as NVARCHAR(255), @ONLY AS NVARCHAR(10)

SET @ONLY = 'Only'

SET @strNum = RTRIM(LTRIM(@sValue))

SET @strArab = SUBSTRING(@strNum, ((LEN(@strNum)-11)+1),11)

SET @strAbvKharab = SUBSTRING(@strNum,1,LEN(@strNum)-11)

SET @rstArab = dbo.I_CN2W_GetArabs(@strArab)

SET @rstAbvKharab = REPLACE(dbo.I_ConvertN2W(CONVERT(MONEY, @strAbvKharab),0),@ONLY,'')

SET @rstAbvKharab = @rstAbvKharab + ' Kharab ' + @rstArab

SET @sRetVal = @rstAbvKharab

RETURN (@sRetVal)

END

CREATE FUNCTION I_ConvertN2W (@curNumber MONEY, @bPrefixCurrencyName BIT)

RETURNS VARCHAR(400) AS

BEGIN

-

---

---This Function is an Adapted (Scaled Down) version of ConvertN2W from the ITCCF32.dll Custom Functions Library by Infinite Technologies

---Original and Adapted (SQL) both Versions authored by Murtaza R.E. This function is provided on AS IS WHERE IS BASIS

---This peice of code can be freely used for all puposes, however please Do Not Forget to mention the source

---Suggestions and Bugs can be send to me at murtaza1972 at gmail dot com, I do not assure an immediate fix, but will try as time permits

---If you find this peice of code helpful, please do drop me a mail, this will encourage me to do something more creative

-

---

DECLARE @sRetVal AS VARCHAR(400), @RUP AS NVARCHAR(10), @PAISE AS NVARCHAR(10), @ONLY AS NVARCHAR(10)

DECLARE @curr AS MONEY, @strFirst AS VARCHAR(100), @lngDeciLen AS INT, @strDeci AS VARCHAR(100)

DECLARE @i AS INT, @rstDeci AS VARCHAR(100), @strNum as VARCHAR(400), @lngNum AS INT, @rstNum AS VARCHAR(400)

DECLARE @rstWord AS VARCHAR(400)

SET @RUP =

CASE

WHEN (@bPrefixCurrencyName = 1) THEN 'Rupees'

ELSE ''

END

SET @PAISE = 'Paise'

SET @ONLY = 'Only'

SET @curr = @curNumber

IF @curr < 0

BEGIN

SET @sRetVal = NULL

END

IF @curr = 0

BEGIN

SET @sRetVal = @RUP + ' Zero ' + @ONLY

END

SET @strFirst = LTRIM(STR(@curNumber,30,2))

SET @lngDeciLen =

CASE

WHEN PATINDEX('%.%',@strFirst) > 0 THEN (LEN(@strFirst)-PATINDEX('%.%',@strFirst))

ELSE 0

END

IF @lngDeciLen <> 0

BEGIN

SET @i = CASE

WHEN PATINDEX('%.%',@strFirst) > 0 THEN (PATINDEX('%.%',@strFirst) + 1)

ELSE 0

END

SET @strDeci = dbo.I_Mid(@strFirst, @i, @lngDeciLen)

END

ELSE

BEGIN

SET @strDeci = '0'

END

SET @strDeci =

CASE

WHEN LEN(@strDeci) = 1 THEN (@strDeci + '0')

ELSE @strDeci

END

SET @rstDeci = dbo.I_CN2W_GetTens(@strDeci)

SET @i =

CASE

WHEN PATINDEX('%.%', @strFirst)>0 THEN (PATINDEX('%.%',@strFirst)-1)

ELSE LEN(@strFirst)

END

SET @strNum = dbo.I_Mid(@strFirst,1,@i)

IF (@strNum IS NULL) OR (LEN(@strNum)=0)

BEGIN

SET @strNum = '0'

END

IF CONVERT(MONEY,@strNum) <= 99999

BEGIN

SET @lngNum = CONVERT(INT,@strNum)

SET @rstNum =

CASE

WHEN (@lngNum <= 20) THEN dbo.I_CN2W_GetWord(@lngNum)

WHEN ((@lngNum > 21) AND (@lngNum <= 99)) THEN dbo.I_CN2W_GetTens(@strNum)

WHEN ((@lngNum > 99) AND (@lngNum <= 999)) THEN dbo.I_CN2W_GetHundreds(@strNum)

WHEN ((@lngNum > 999) AND (@lngNum <= 99999)) THEN dbo.I_CN2W_GetThousands(@strNum)

END

END

IF CONVERT(MONEY,@strNum) > 99999

BEGIN

SET @rstNum =

CASE

WHEN ((CONVERT(MONEY,@strNum) > 99999) AND (CONVERT(MONEY,@strNum) <= 9999999)) THEN dbo.I_CN2W_GetLakhs(@strNum)

WHEN ((CONVERT(MONEY,@strNum) > 9999999) AND (CONVERT(MONEY,@strNum) <= 999999999)) THEN dbo.I_CN2W_GetCrores(@strNum)

WHEN ((CONVERT(MONEY,@strNum) > 999999999) AND (CONVERT(MONEY,@strNum) <= 99999999999)) THEN dbo.I_CN2W_GetArabs(@strNum)

WHEN (CONVERT(MONEY,@strNum) <= 10000000000000) THEN dbo.I_CN2W_GetKharabs(@strNum)

WHEN (CONVERT(MONEY,@strNum) > 10000000000000) THEN dbo.I_CN2W_GetAbvKharab(@strNum)

END

END

IF (LEN(RTRIM(LTRIM(@rstDeci))) = 0) OR (@rstDeci IS NULL)

BEGIN

SET @rstWord = @RUP + ' ' + @rstNum + ' ' + @ONLY

END

ELSE

BEGIN

IF (LEN(RTRIM(LTRIM(@rstNum))) > 0) OR (@rstNum IS NOT NULL)

BEGIN

SET @rstWord = @RUP + ' ' + @rstNum + ' and ' + @rstDeci + ' ' + @PAISE + ' ' + @ONLY

END

ELSE

BEGIN

SET @rstWord = @rstDeci + ' ' + @PAISE + ' ' + @ONLY

END

END

SET @sRetVal =

CASE

WHEN ((LEN(RTRIM(LTRIM(@rstWord)))=0) OR (@rstWord IS NULL)) THEN 'Zero'

ELSE REPLACE(RTRIM(LTRIM(@rstWord)),' ',' ')

END

RETURN (@sRetVal)

END

Step 3:

Code in SBO_SP_TransactionNotification

IF ((@object_type = '46') AND (@transaction_type = 'A')) -- Outgoing PAYMENT

BEGIN

DECLARE @sWords AS VARCHAR(400), @iValue AS MONEY

SET @error = 46

SET @iValue = (SELECT [CheckSum] FROM dbo.OVPM WHERE DocEntry = @list_of_cols_val_tab_del)

SET @sWords =

CASE

WHEN @iValue > 0 THEN dbo.I_ConvertN2W(@iValue,0) --call the function only when the value is more than 0

END

UPDATE dbo.OVPM

SET U_AmountInWord = @sWords

WHERE ((DocEntry = @list_of_cols_val_tab_del) AND ([CheckSum]>0)) --we want to update only when the means of payment is by cheque

SET @error = 0 --success

END

-

---

xxxxxxxxxxxxxxxENDxxxxxxxxxxxxxxxxxx---

This is being currently used by me, and works fine for me, I hope this will be useful to you and others

Regards,

Murtaza R.E.

[Former Member · Accepted solution]

Hi

I am sorry my earlier post looks all garbage, I am wondering how to format it properly, I will be back soon, with a more understandable reply

[Former Member · Accepted solution]

Hi Murtaza,

Give me your email ID I will forward the a more readable version of my reply to you.

Regards,

Murtaza R.E.

[Former Member · Accepted solution]

Hi Murtaza,

I had a similar issue, and I addressed it by:

1. Creating a UDF in the Payments>Title called AmountInWord of Type AlphaNumeric Structure Text (i.e. in OVPM Table)

2. Created a Function called I_ConvertN2W in the database, which converts any number into Words as per Indian Style i.e. Lakh, Crore

3. In the SBO_SP_TransactionNotification, I am filling the Value in the UDF on every Add of Outgoing Payment

4. In the PLD for the printing of the cheque, I am using the UDF, instead of the SAPB1 Amount (which is non Indian Style)

Step 1:

Create the UDF as mentioned above, I guess no further explanation required

Step 2:

Creating the Function I_ConvertN2W, but first you will have to create all the support functions, I am listing the function in the order of creation

CREATE FUNCTION I_Modulus (@INPUT INT, @DIVIDER INT)  
RETURNS INT AS  
BEGIN 

DECLARE @RETVAL AS INT, @MODULUS AS INT

IF @INPUT = @DIVIDER
BEGIN
	SET @RETVAL = 0
END

IF @INPUT < @DIVIDER
BEGIN
	SET @RETVAL = @INPUT
END

SET @MODULUS = @INPUT

WHILE @MODULUS >= @DIVIDER
BEGIN
	SET @MODULUS = @MODULUS - @DIVIDER
END
SET @RETVAL = @MODULUS

RETURN (@RETVAL)

END

CREATE FUNCTION I_Mid (@sInput VARCHAR(8000), @iStart INT, @iLen INT)  
RETURNS VARCHAR(8000) AS  
BEGIN 

--Adapted from the Mid function of Visual Basic

DECLARE @sRetVal AS VARCHAR(8000)
IF @iLen = 0
BEGIN
	SET @iLen = LEN(@sInput)-(@iStart-1)
END
SET @sRetVal = RIGHT(@sInput,LEN(@sInput) - (@iStart-1))
SET @sRetVal = LEFT(@sRetVal,@iLen)
RETURN (@sRetVal)

END

--NOW See Part 2 of the Reply

[Former Member · Accepted solution]

--PART 3

CREATE FUNCTION I_CN2W_GetHundreds (@sValue NVARCHAR(100))  
RETURNS NVARCHAR(100) AS  
BEGIN 

DECLARE @rstHun as NVARCHAR(100), @sHun as NVARCHAR(100), @sTens AS NVARCHAR(100), @sRetVal as NVARCHAR(100)

IF RTRIM(LTRIM(@sValue)) = '000'
BEGIN
	SET @sRetVal = ''
END
ELSE
BEGIN
	SET @sHun = SUBSTRING(RTRIM(LTRIM(@sValue)),1,1)
	SET @sTens = dbo.I_CN2W_GetTens(SUBSTRING(RTRIM(LTRIM(@sValue)),2,2))
	IF (LEN(RTRIM(LTRIM(@sTens))) > 0)
	BEGIN
		SET @rstHun = dbo.I_CN2W_GetWord(CONVERT(INT, @sHun))
		IF (LEN(RTRIM(LTRIM(@rstHun))) > 0)
		BEGIN
			SET @rstHun = @rstHun + ' Hundred and ' + @sTens
		END
		ELSE
		BEGIN
			SET @rstHun = @sTens
		END
	END
	ELSE
	BEGIN
		SET @rstHun = dbo.I_CN2W_GetWord(CONVERT(INT, @sHun)) + ' Hundred'
	END
	SET @sRetVal = @rstHun
END
RETURN (@sRetVal)

END

CREATE FUNCTION I_CN2W_GetThousands (@sValue NVARCHAR(100))  
RETURNS NVARCHAR(100) AS  
BEGIN 

DECLARE @rstHun as NVARCHAR(100), @rstThou AS NVARCHAR(100), @strHun as NVARCHAR(100), @strNum AS NVARCHAR(100), @strThou AS NVARCHAR(100)
DECLARE @sRetVal as NVARCHAR(100)

IF @sValue = '00000'
BEGIN
	SET @sRetVal = ''
END
ELSE
BEGIN
	SET @strNum = RTRIM(LTRIM(@sValue))
	SET @strHun =
		CASE 
			WHEN LEN(@strNum) = 4 THEN SUBSTRING(@strNum,2,3)
			ELSE SUBSTRING(@strNum,3,3)
		END
	SET @strThou = SUBSTRING(@strNum,1,LEN(@strNum)-3)
	SET @rstHun = dbo.I_CN2W_GetHundreds(@strHun)
	SET @rstThou =
		CASE 
			WHEN CAST(@strThou AS INT) <= 20 THEN dbo.I_CN2W_GetWord(CONVERT(INT, @strThou))
			ELSE dbo.I_CN2W_GetTens(@strThou)
		END
	IF (LEN(@rstThou)>0)
	BEGIN
		SET @rstThou = @rstThou + ' Thousand ' + @rstHun
	END
	ELSE
	BEGIN
		SET @rstThou = @rstHun
	END
	SET @sRetVal = @rstThou
END
RETURN (@sRetVal)

END

--NOW See Part 4

[Former Member · Accepted solution]

--PART 4

CREATE FUNCTION I_CN2W_GetLakhs (@sValue NVARCHAR(100))  
RETURNS NVARCHAR(100) AS  
BEGIN 

DECLARE @rstThou as NVARCHAR(100), @rstLakhs AS NVARCHAR(100), @strLakhs as NVARCHAR(100), @strNum AS NVARCHAR(100), @strThou AS NVARCHAR(100)
DECLARE @sRetVal as NVARCHAR(100)

IF @sValue = '0000000'
BEGIN
	SET @sRetVal = ''
END
ELSE
BEGIN
	SET @strNum = RTRIM(LTRIM(@sValue))
	SET @strThou =
		CASE 
			WHEN LEN(@strNum) = 6 THEN SUBSTRING(@strNum,2,5)
			ELSE SUBSTRING(@strNum,3,5)
		END
	SET @strLakhs = SUBSTRING(@strNum,1,LEN(@strNum)-5)
	SET @rstThou = dbo.I_CN2W_GetThousands(@strThou)
	SET @rstLakhs =
		CASE 
			WHEN CAST(@strLakhs AS INT) <= 20 THEN dbo.I_CN2W_GetWord(CONVERT(INT, @strLakhs))
			ELSE dbo.I_CN2W_GetTens(@strLakhs)
		END
	IF (LEN(@rstLakhs)>0) 
	BEGIN
		SET @rstLakhs = @rstLakhs + ' Lakh ' + @rstThou
	END
	ELSE
	BEGIN
		SET @rstLakhs = @rstThou
	END
	SET @sRetVal = @rstLakhs
END
RETURN (@sRetVal)

END

CREATE FUNCTION I_CN2W_GetCrores (@sValue NVARCHAR(255))  
RETURNS NVARCHAR(255) AS  
BEGIN 

DECLARE @rstCro as NVARCHAR(255), @rstLakhs AS NVARCHAR(255), @strNum as NVARCHAR(100), @strLakhs AS NVARCHAR(255), @strCro AS NVARCHAR(255)
DECLARE @sRetVal as NVARCHAR(255)

IF @sValue = '000000000'
BEGIN
	SET @sRetVal = ''
END
ELSE
BEGIN
	SET @strNum = RTRIM(LTRIM(@sValue))
	SET @strLakhs =
		CASE 
			WHEN LEN(@strNum) = 8 THEN SUBSTRING(@strNum,2,7)
			ELSE SUBSTRING(@strNum,3,7)
		END
	SET @strCro = SUBSTRING(@strNum,1,LEN(@strNum)-7)
	SET @rstLakhs = dbo.I_CN2W_GetLakhs(@strLakhs)
	SET @rstCro =
		CASE 
			WHEN CAST(@strCro AS INT) <= 20 THEN dbo.I_CN2W_GetWord(CONVERT(INT, @strCro))
			ELSE dbo.I_CN2W_GetTens(@strCro)
		END
	IF LEN(@rstCro)>0
	BEGIN
		SET @rstCro = @rstCro + ' Crore ' + @rstLakhs
	END
	ELSE
	BEGIN
		SET @rstCro = @rstLakhs
	END
	SET @sRetVal = @rstCro
END
RETURN (@sRetVal)

END

--NOW See Part 5

[Former Member · Accepted solution]

--PART 5

CREATE FUNCTION I_CN2W_GetArabs (@sValue NVARCHAR(255))  
RETURNS NVARCHAR(255) AS  
BEGIN 

DECLARE @rstArab as NVARCHAR(255), @rstCro AS NVARCHAR(255), @strNum as NVARCHAR(255), @strCro AS NVARCHAR(255), @strArab AS NVARCHAR(255)
DECLARE @sRetVal as NVARCHAR(255)

IF @sValue = '00000000000'
BEGIN
	SET @sRetVal = ''
END
ELSE
BEGIN
	SET @strNum = RTRIM(LTRIM(@sValue))
	SET @strCro =
		CASE 
			WHEN LEN(@strNum) = 10 THEN SUBSTRING(@strNum,2,9)
			ELSE SUBSTRING(@strNum,3,9)
		END
	SET @strArab = SUBSTRING(@strNum,1,LEN(@strNum)-9)
	SET @rstCro = dbo.I_CN2W_GetCrores(@strCro)
	SET @rstArab =
		CASE 
			WHEN CAST(@strArab AS INT) <= 20 THEN dbo.I_CN2W_GetWord(CONVERT(INT, @strArab))
			ELSE dbo.I_CN2W_GetTens(@strArab)
		END
	IF LEN(@rstArab)>0
	BEGIN
		SET @rstArab = @rstArab + ' Arab ' + @rstCro
	END
	ELSE
	BEGIN
		SET @rstArab = @rstCro
	END
	SET @sRetVal = @rstArab
END
RETURN (@sRetVal)

END

CREATE FUNCTION I_CN2W_GetKharabs (@sValue NVARCHAR(255))  
RETURNS NVARCHAR(255) AS  
BEGIN 

DECLARE @rstArab as NVARCHAR(255), @rstKharab AS NVARCHAR(255), @strNum as NVARCHAR(255), @strKharab AS NVARCHAR(255), @strArab AS NVARCHAR(255)
DECLARE @sRetVal as NVARCHAR(255)

IF @sValue = '10000000000000'
BEGIN
	SET @sRetVal = 'Hundred Kharab '
END
ELSE
BEGIN
	SET @strNum = RTRIM(LTRIM(@sValue))
	SET @strArab =
		CASE 
			WHEN LEN(@strNum) = 12 THEN SUBSTRING(@strNum,2,11)
			ELSE SUBSTRING(@strNum,3,11)
		END
	SET @strKharab = SUBSTRING(@strNum,1,LEN(@strNum)-11)
	SET @rstArab = dbo.I_CN2W_GetArabs(@strArab)
	SET @rstKharab =
		CASE 
			WHEN CAST(@strKharab AS INT) <= 20 THEN dbo.I_CN2W_GetWord(CONVERT(INT, @strKharab))
			ELSE dbo.I_CN2W_GetTens(@strKharab)
		END
	IF @rstKharab IS NOT NULL
	BEGIN
		SET @rstKharab = @rstKharab + ' Kharab ' + @rstArab
	END
	ELSE
	BEGIN
		SET @rstKharab = @rstArab
	END
	SET @sRetVal = @rstKharab
END
RETURN (@sRetVal) 

END

--NOW See Part 6

[Former Member · Accepted solution]

--PART 6

CREATE FUNCTION I_CN2W_GetAbvKharab (@sValue NVARCHAR(255))  
RETURNS NVARCHAR(255) AS  
BEGIN 

DECLARE @rstArab as NVARCHAR(255), @rstAbvKharab AS NVARCHAR(255), @strNum as NVARCHAR(255), @strArab AS NVARCHAR(255), @strAbvKharab AS NVARCHAR(255)
DECLARE @sRetVal as NVARCHAR(255), @ONLY AS NVARCHAR(10)

SET @ONLY = 'Only'
SET @strNum = RTRIM(LTRIM(@sValue))
SET @strArab = SUBSTRING(@strNum, ((LEN(@strNum)-11)+1),11)
SET @strAbvKharab = SUBSTRING(@strNum,1,LEN(@strNum)-11)
SET @rstArab = dbo.I_CN2W_GetArabs(@strArab)
SET @rstAbvKharab = REPLACE(dbo.I_ConvertN2W(CONVERT(MONEY, @strAbvKharab),0),@ONLY,'')
SET @rstAbvKharab = @rstAbvKharab + ' Kharab ' + @rstArab
SET @sRetVal = @rstAbvKharab
RETURN (@sRetVal) 

END

--NOW See Part 7

[Former Member · Accepted solution]

--PART 7

This is a big Function, and the forum settings does not allow it to be displayed in 1 post, thus it is split over multiple posts

CREATE FUNCTION I_ConvertN2W (@curNumber MONEY, @bPrefixCurrencyName BIT)
RETURNS VARCHAR(400) AS  
BEGIN 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---This Function is an Adapted (Scaled Down) version of ConvertN2W from the ITCCF32.dll Custom Functions Library by Infinite Technologies
---Original and Adapted (SQL) both Versions authored by Murtaza R.E. This function is provided on AS IS WHERE IS BASIS
---This peice of code can be freely used for all puposes, however please Do Not Forget to mention the source
---Suggestions and Bugs can be send to me at murtaza1972 at gmail dot com, I do not assure an immediate fix, but will try as time permits
---If you find this peice of code helpful, please do drop me a mail, this will encourage me to do something more creative
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE @sRetVal AS VARCHAR(400), @RUP AS NVARCHAR(10), @PAISE AS NVARCHAR(10), @ONLY AS NVARCHAR(10)
DECLARE @curr AS MONEY, @strFirst AS VARCHAR(100), @lngDeciLen AS INT, @strDeci AS VARCHAR(100)
DECLARE @i AS INT, @rstDeci AS VARCHAR(100), @strNum as VARCHAR(400), @lngNum AS INT, @rstNum AS VARCHAR(400)
DECLARE @rstWord AS VARCHAR(400)

SET @RUP = 
	CASE 
		WHEN (@bPrefixCurrencyName = 1) THEN 'Rupees'
		ELSE ''
	END
SET @PAISE = 'Paise'
SET @ONLY = 'Only'
SET @curr = @curNumber

--NOW See Part 7.1

[Former Member · Accepted solution]

--PART 7.1

IF @curr < 0
BEGIN	
	SET @sRetVal = NULL
END
IF @curr = 0
BEGIN
	SET @sRetVal = @RUP + ' Zero ' + @ONLY
END
SET @strFirst = LTRIM(STR(@curNumber,30,2))
SET @lngDeciLen =
	CASE
		WHEN PATINDEX('%.%',@strFirst) > 0 THEN (LEN(@strFirst)-PATINDEX('%.%',@strFirst))
		ELSE 0
	END
IF @lngDeciLen <> 0
BEGIN
	SET @i = CASE
			WHEN PATINDEX('%.%',@strFirst) > 0 THEN (PATINDEX('%.%',@strFirst) + 1)
			ELSE 0
		END
	SET @strDeci = dbo.I_Mid(@strFirst, @i, @lngDeciLen)
END
ELSE
BEGIN
	SET @strDeci = '0'
END
SET @strDeci = 
	CASE
		WHEN LEN(@strDeci) = 1 THEN (@strDeci + '0')
		ELSE @strDeci
	END
SET @rstDeci = dbo.I_CN2W_GetTens(@strDeci)

--NOW See Part 7.2

[Former Member · Accepted solution]

--PART 7.2

SET @i = 
	CASE
		WHEN PATINDEX('%.%', @strFirst)>0 THEN (PATINDEX('%.%',@strFirst)-1)
		ELSE LEN(@strFirst)
	END
SET @strNum = dbo.I_Mid(@strFirst,1,@i)
IF (@strNum IS NULL) OR (LEN(@strNum)=0)
BEGIN
	SET @strNum = '0'
END
IF CONVERT(MONEY,@strNum) <= 99999
BEGIN
	SET @lngNum = CONVERT(INT,@strNum)
	SET @rstNum =
		CASE
			WHEN (@lngNum <= 20) THEN dbo.I_CN2W_GetWord(@lngNum)
			WHEN ((@lngNum > 21) AND (@lngNum <= 99)) THEN dbo.I_CN2W_GetTens(@strNum)
			WHEN ((@lngNum > 99) AND (@lngNum <= 999)) THEN dbo.I_CN2W_GetHundreds(@strNum)
			WHEN ((@lngNum > 999) AND (@lngNum <= 99999)) THEN dbo.I_CN2W_GetThousands(@strNum)
		END
END
IF CONVERT(MONEY,@strNum) > 99999
BEGIN
	SET @rstNum =
		CASE
			WHEN ((CONVERT(MONEY,@strNum) > 99999) AND (CONVERT(MONEY,@strNum) <= 9999999)) THEN dbo.I_CN2W_GetLakhs(@strNum)
			WHEN ((CONVERT(MONEY,@strNum) > 9999999) AND (CONVERT(MONEY,@strNum) <= 999999999)) THEN dbo.I_CN2W_GetCrores(@strNum)
			WHEN ((CONVERT(MONEY,@strNum) > 999999999) AND (CONVERT(MONEY,@strNum) <= 99999999999)) THEN dbo.I_CN2W_GetArabs(@strNum)
			WHEN (CONVERT(MONEY,@strNum) <= 10000000000000) THEN dbo.I_CN2W_GetKharabs(@strNum)
			WHEN (CONVERT(MONEY,@strNum) > 10000000000000) THEN dbo.I_CN2W_GetAbvKharab(@strNum)
		END
END
IF (LEN(RTRIM(LTRIM(@rstDeci))) = 0) OR (@rstDeci IS NULL)
BEGIN
	SET @rstWord = @RUP + ' ' + @rstNum + ' ' + @ONLY
END
ELSE
BEGIN
	IF (LEN(RTRIM(LTRIM(@rstNum))) > 0) OR (@rstNum IS NOT NULL)
	BEGIN
		SET @rstWord = @RUP + ' ' + @rstNum + ' and ' + @rstDeci + ' ' + @PAISE + ' ' + @ONLY
	END
	ELSE
	BEGIN
		SET @rstWord = @rstDeci + ' ' + @PAISE + ' ' + @ONLY
	END
END
SET @sRetVal =
	CASE
		WHEN ((LEN(RTRIM(LTRIM(@rstWord)))=0) OR (@rstWord IS NULL)) THEN 'Zero'
		ELSE REPLACE(RTRIM(LTRIM(@rstWord)),'  ',' ')
	END	
RETURN (@sRetVal) 

END

--NOW See Part 8

[Former Member · Accepted solution]

--PART 8 (final)

Step 3:

Code in SBO_SP_TransactionNotification

IF ((@object_type = '46') AND (@transaction_type = 'A')) -- Outgoing PAYMENT
BEGIN
	DECLARE @sWords AS VARCHAR(400), @iValue AS MONEY

	SET @error = 46
	SET @iValue = (SELECT [CheckSum] FROM dbo.OVPM WHERE DocEntry = @list_of_cols_val_tab_del)
	SET @sWords = 
		CASE 
			WHEN @iValue > 0 THEN dbo.I_ConvertN2W(@iValue,0) --call the function only when the value is more than 0
		END

	UPDATE dbo.OVPM
		SET U_AmountInWord = @sWords
		WHERE ((DocEntry = @list_of_cols_val_tab_del) AND ([CheckSum]>0)) --we want to update only when the means of payment is by cheque

	SET @error = 0 --success
END

This is being currently used by me, and works fine for me, I hope this will be useful to you and others

Regards,

Murtaza R.E.

[Former Member · Accepted solution]

Hi Murtaza R.E.

Thanks for your Functional query...

Working fine.

By

Karth

Edited by: Karthi keyan on Sep 23, 2010 10:13 AM

Edited by: Karthi keyan on Sep 23, 2010 10:21 AM

[Former Member]

Hello

As stated by another contributor, the theme here is that within the layout (PLD), you can change the field to show the total in words ...

Now, the issue is that the field being referenced is a system variable field to bring the coin, so to make the text includes the same

If this is not possible should be removed with another formula field to associate with the currency showing the description fields of the same

I hope you will be helpful,

PS: Do not know if this is a problem big enough to make a mega validation as noted here.

Best regards,

[Former Member]

The stored procedure is working fine but for the users are not able to update Business Master Data and the production Order. I tested on 3 different production systems and it is positive. Anyone facing the same issue?

Bharath S

[Former Member]

Hi,

Plz Check -

Edited by: somi mishra on Dec 10, 2008 5:47 PM

[Former Member]

To get the amount in words, Drag the Amount Field to the PLD.

In the Properties Window, Choose the Format Tab and tick, 'Sum In Words' box.

When you preview the report, the amount would be shown in Words.

However, 125000 would be shown as One Hundred and Twenty Five Thousand.

This is a system behaviuor.

[Former Member]

Hello Sridharan T,

Actually i dont want 125000 would be shown as One Hundred and Twenty Five Thousand.

I want it should display One Lakh Twenty-Five Thousand Rupees Only.

Regards,

Murtaza

[Former Member]

--PART 2

CREATE FUNCTION I_CN2W_GetWord (@cValue INT)  
RETURNS nvarchar(10)
 AS  
BEGIN 

DECLARE @sRetVal AS NVARCHAR(10)

SET @sRetVal =
	CASE 
		WHEN @cValue = 0 THEN ''
		WHEN @cValue = 1 THEN 'One' 
		WHEN @cValue = 2 THEN 'Two' 
		WHEN @cValue = 3 THEN 'Three' 
		WHEN @cValue = 4 THEN 'Four' 
		WHEN @cValue = 5 THEN 'Five' 
		WHEN @cValue = 6 THEN 'Six' 
		WHEN @cValue = 7 THEN 'Seven' 
		WHEN @cValue = 8 THEN 'Eight' 
		WHEN @cValue = 9 THEN 'Nine' 
		WHEN @cValue = 10 THEN 'Ten' 
		WHEN @cValue = 11 THEN 'Eleven' 
		WHEN @cValue = 12 THEN 'Twelve' 
		WHEN @cValue = 13 THEN 'Thirteen' 
		WHEN @cValue = 14 THEN 'Fourteen' 
		WHEN @cValue = 15 THEN 'Fifteen' 
		WHEN @cValue = 16 THEN 'Sixteen' 
		WHEN @cValue = 17 THEN 'Seventeen' 
		WHEN @cValue = 18 THEN 'Eighteen' 
		WHEN @cValue = 19 THEN 'Ninteen' 
		WHEN @cValue = 20 THEN 'Twenty' 
		WHEN @cValue = 30 THEN 'Thirty' 
		WHEN @cValue = 40 THEN 'Forty' 
		WHEN @cValue = 50 THEN 'Fifty' 
		WHEN @cValue = 60 THEN 'Sixty' 
		WHEN @cValue = 70 THEN 'Seventy' 
		WHEN @cValue = 80 THEN 'Eighty' 
		WHEN @cValue = 90 THEN 'Ninty'
	END

RETURN (@sRetVal) 

END

CREATE FUNCTION I_CN2W_GetTens (@sValue as NVARCHAR(100))  
RETURNS NVARCHAR(100) AS  
BEGIN 

DECLARE @sTens AS NVARCHAR(100), @iNum AS INT, @cnt AS INT, @iDigit AS INT, @sRetVal AS NVARCHAR(100)

SET @iNum = CAST(@sValue AS INT)
SET @sTens = ''

IF @iNum <= 20
BEGIN
	SET @sRetVal =  dbo.I_CN2W_GetWord(@iNum)
END
ELSE
BEGIN
	SET @cnt = 1
	WHILE @iNum > 0
		BEGIN
			SET @iDigit = dbo.I_Modulus(@iNum, 10)
			IF @cnt = 1
				SET @sTens = dbo.I_CN2W_GetWord(@iDigit)
			ELSE
			BEGIN
				SET @iDigit = @iDigit * 10
				SET @sTens = dbo.I_CN2W_GetWord(@iDigit) + ' ' + @sTens
			END
			SET @cnt = (@cnt + 1)
			SET @iNum = (@iNum / 10)
		END

	SET @sRetVal = @sTens
END
RETURN (@sRetVal)

END

--NOW See Part 3

[former_member186095]

Hi,

I always create udf in banking and then using store procedure transaction notification (SP_TN), the amount in words generated. using query FMS, the amount in word displayed in the UDF. The udf then is defined in the check form in the print layout designer. The amount in word can be displayed in the preview and printed paper.

Let me know if you have used SP_TN amount in word or not.

Rgds,

[Former Member]

HI Jimmy!

Can you tell something about store procedure transaction notification (SP_TN). Because my country is Vietnam. I need amount in word very much but SAP don't support for us.

Thank you!