PACKAGE Pkg_number2word is
Function Num2Word_million (pCompCode varchar2, pAmount Number) return varchar2 ;
Function Num2Word_crore (pCompCode varchar2, pAmount Number) return Varchar2 ;
Function Interprt (pAmount Number) return Varchar2 ;
Function Numbertext (pAmount Number) return Varchar2 ;
End;
/
PACKAGE BODY pkg_number2word is
Function Num2Word_million(pCompCode varchar2, pAmount Number) return Varchar2 is
vWord Varchar2(200) ;
vAmount Number(15,2) ;
vBillionHundred Number(15) Default 0 ;
vBillion Number(15) Default 0 ;
vMillionHundred Number(15) Default 0 ;
vMillion Number(10) Default 0 ;
vHundredThousand Number(12) Default 0 ;
vThousand Number(10) Default 0 ;
vHundred Number(10) Default 0 ;
vOnes Number(2) Default 0 ;
vDecimal Number(2) Default 0 ;
vCurrCode Cm004_comp_mst.curr_code%type ;
vCurrSubUnit Cm001_currency_mst.subunit%type ;
-- vCurrMainUnit Cm001_currency_mst.mainunit%type ;
vCurrMainUnit varchar2(500);
BEGIN
vAmount := Abs(pAmount) ;
Select Floor(vAmount/100000000000) Into vBillionHundred From Dual ;
If vBillionHundred >= 1 then
vAmount := vAmount-(vBillionHundred*100000000000) ;
End if;
Select Floor(vAmount/1000000000) Into vBillion From Dual ;
If vBillion >= 1 then
vAmount := vAmount-(vBillion*1000000000) ;
End if;
Select Floor(vAmount/100000000) Into vMillionHundred From Dual ;
If vMillionHundred >= 1 then
vAmount := vAmount-(vMillionHundred*100000000) ;
End if;
Select Floor(vAmount/1000000) Into vMillion From Dual ;
If vMillion >= 1 then
vAmount := vAmount-(vMillion*1000000) ;
End if;
Select Floor(vAmount/100000) Into vHundredThousand From Dual ;
If vHundredThousand >= 1 then
vAmount := vAmount-(vHundredThousand*100000) ;
End if;
Select Floor(vAmount/1000) Into vThousand From Dual ;
If vThousand >= 1 then
vAmount := vAmount-(vThousand*1000) ;
End if;
Select Floor(vAmount/100) Into vHundred From Dual ;
If vHundred >= 1 then
vAmount := vAmount-(vHundred*100) ;
End if;
Select (pAmount - trunc(pAmount,0)) * 100 Into vDecimal From Dual ;
vOnes := vAmount - (vDecimal/100) ;
vWord := '' ;
If vBillionHundred >= 1 then
vWord := vWord || Interprt(vBillionHundred)||' HUNDRED ' ;
If vBillion = 0 then
vWord := vWord || Interprt(vBillion)||' BILLION ' ;
End if ;
End if ;
If vBillion >= 1 then
vWord := vWord || Interprt(vBillion)||' BILLION ' ;
End if ;
If vMillionHundred >= 1 then
vWord := vWord || Interprt(vMillionHundred)||' HUNDRED ' ;
-----if million is 0 then appends the million
If vMillion = 0 then
vWord := vWord || Interprt(vMillion)||' MILLION ' ;
End if ;
End if ;
If vMillion >= 1 then
vWord := vWord || Interprt(vMillion)||' MILLION ' ;
End if ;
---------prints the hundred thousand
If vHundredThousand >= 1 then
vWord := vWord || Interprt(vHundredThousand)||' HUNDRED ' ;
-----if thousand is 0 then appends the thousand
If vThousand = 0 then
vWord := vWord || Interprt(vThousand)||' THOUSAND ' ;
End if ;
---------
End if ;
If vThousand >= 1 then
vWord := vWord || Interprt(vThousand)||' THOUSAND ' ;
End if ;
If vHundred >= 1 then
vWord := vWord || Interprt(vHundred)||' HUNDRED ' ;
End if ;
Begin
SELECT a.curr_code INTO vCurrCode
FROM Cm004_comp_mst a
WHERE a.comp_code = pCompCode ;
Exception
When Others Then
vCurrCode := null;
End;
If vCurrCode is not null Then
Begin
SELECT a.subunit, a.mainunit INTO vCurrSubUnit, vCurrMainUnit
FROM Cm001_currency_mst a
WHERE a.curr_code = vCurrCode ;
Exception
When Others Then
vCurrMainUnit := null ;
vCurrSubUnit := null ;
End ;
End If;
vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'DOLLARS') ;
If vDecimal > 0 then
If vWord is not null then
-- vWord := vWord ||' AND '|| Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS') || ' ONLY' ;
If trim(vWord) ='DOLLARS' then -- Naren added on 20/06/2022
dbms_output.put_line('entered in if dollars');
vWord :=pkg_number2word.Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS') || ' ONLY' ;
Else
vWord := vWord ||' AND '|| pkg_number2word.Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS') || ' ONLY' ;
End If;
Else
vWord := Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS')||' ONLY' ;
End if;
Else
If vWord is not null Then
vWord := vWord || ' ONLY' ;
End If;
End if ;
Return (vWord);
Exception
When Value_Error then
return null ;
End;
Function Num2Word_crore (pCompCode varchar2, pAmount Number) return Varchar2 IS
vWord Varchar2(200) ;
vAmount Number(15,2) ;
vBillionHundred Number(15) Default 0 ;
vBillion Number(15) Default 0 ;
vMillionHundred Number(15) Default 0 ;
vMillion Number(10) Default 0 ;
vHundredThousand Number(12) Default 0 ;
vThousand Number(10) Default 0 ;
vHundred Number(10) Default 0 ;
vOnes Number(2) Default 0 ;
vDecimal Number(2) Default 0 ;
vCurrCode Cm004_comp_mst.curr_code%type ;
vCurrSubUnit Cm001_currency_mst.subunit%type ;
-- vCurrMainUnit Cm001_currency_mst.mainunit%type ;
vCurrMainUnit varchar2(500);
BEGIN
vAmount := Abs(pAmount) ;
Select Floor(vAmount/100000000000) Into vBillionHundred From Dual ;
If vBillionHundred >= 1 then
vAmount := vAmount-(vBillionHundred*100000000000) ;
End if;
Select Floor(vAmount/1000000000) Into vBillion From Dual ;
If vBillion >= 1 then
vAmount := vAmount-(vBillion*1000000000) ;
End if;
Select Floor(vAmount/100000000) Into vMillionHundred From Dual ;
If vMillionHundred >= 1 then
vAmount := vAmount-(vMillionHundred*100000000) ;
End if;
Select Floor(vAmount/1000000) Into vMillion From Dual ;
If vMillion >= 1 then
vAmount := vAmount-(vMillion*1000000) ;
End if;
Select Floor(vAmount/100000) Into vHundredThousand From Dual ;
If vHundredThousand >= 1 then
vAmount := vAmount-(vHundredThousand*100000) ;
End if;
Select Floor(vAmount/1000) Into vThousand From Dual ;
If vThousand >= 1 then
vAmount := vAmount-(vThousand*1000) ;
End if;
Select Floor(vAmount/100) Into vHundred From Dual ;
If vHundred >= 1 then
vAmount := vAmount-(vHundred*100) ;
End if;
Select (pAmount - trunc(pAmount,0)) * 100 Into vDecimal From Dual ;
vOnes := vAmount - (vDecimal/100) ;
vWord := '' ;
If vBillionHundred >= 1 then
vWord := vWord || Interprt(vBillionHundred)||' HUNDRED ' ;
If vBillion = 0 then
vWord := vWord || Interprt(vBillion)||' BILLION ' ;
End if ;
End if ;
If vBillion >= 1 then
vWord := vWord || Interprt(vBillion)||' BILLION ' ;
End if ;
If vMillionHundred >= 1 then
vWord := vWord || Interprt(vMillionHundred)||' HUNDRED ' ;
-----if million is 0 then appends the million
If vMillion = 0 then
vWord := vWord || Interprt(vMillion)||' MILLION ' ;
End if ;
End if ;
If vMillion >= 1 then
vWord := vWord || Interprt(vMillion)||' MILLION ' ;
End if ;
---------prints the hundred thousand
If vHundredThousand >= 1 then
vWord := vWord || Interprt(vHundredThousand)||' HUNDRED ' ;
-----if thousand is 0 then appends the thousand
If vThousand = 0 then
vWord := vWord || Interprt(vThousand)||' THOUSAND ' ;
End if ;
---------
End if ;
If vThousand >= 1 then
vWord := vWord || Interprt(vThousand)||' THOUSAND ' ;
End if ;
If vHundred >= 1 then
vWord := vWord || Interprt(vHundred)||' HUNDRED ' ;
End if ;
Begin
SELECT a.curr_code INTO vCurrCode
FROM Cm004_comp_mst a
WHERE a.comp_code = pCompCode ;
Exception
When Others Then
vCurrCode := null;
End;
If vCurrCode is not null Then
Begin
SELECT a.subunit, a.mainunit INTO vCurrSubUnit, vCurrMainUnit
FROM Cm001_currency_mst a
WHERE a.curr_code = vCurrCode ;
Exception
When Others Then
vCurrMainUnit := null ;
vCurrSubUnit := null ;
End ;
End If;
vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'DOLLARS') ;
If vDecimal > 0 then
If vWord is not null then
vWord := vWord ||' AND '|| Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS') || ' ONLY' ;
Else
vWord := Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS')||' ONLY' ;
End if;
Else
If vWord is not null Then
vWord := vWord || ' ONLY' ;
End If;
End if ;
Return (vWord);
Exception
When Value_Error then
return null ;
/* vWord Varchar2(100) ;
vAmount Number(13,2) ;
vCrore Number(10) Default 0 ;
vLakhs Number(10) Default 0 ;
vThousand Number(10) Default 0 ;
vHundred Number(10) Default 0 ;
vOnes Number(2) Default 0 ;
vDecimal Number(2) Default 0 ;
vCurrCode Cm004_comp_mst.curr_code%type ;
vCurrSubUnit Cm001_currency_mst.subunit%type ;
vCurrMainUnit Cm001_currency_mst.mainunit%type ;
BEGIN
vAmount := Abs(pAmount) ;
Select Floor(vAmount/10000000) Into vCrore From Dual ;
If vCrore >= 1 then
vAmount := vAmount-(vCrore*10000000) ;
End if;
Select Floor(vAmount/100000) Into vLakhs From Dual ;
If vLakhs >= 1 then
vAmount := vAmount-(vLakhs*100000) ;
End if;
Select Floor(vAmount/1000) Into vThousand From Dual ;
If vThousand >= 1 then
vAmount := vAmount-(vThousand*1000) ;
End if;
Select Floor(vAmount/100) Into vHundred From Dual ;
If vHundred >= 1 then
vAmount := vAmount-(vHundred*100) ;
End if;
Select (pAmount - trunc(pAmount,0)) * 100 Into vDecimal From Dual ;
vOnes := vAmount - (vDecimal/100) ;
vWord := '' ;
If vCrore >= 1 then
vWord := vWord || Interprt(vCrore)||' Crores ' ;
End if ;
If vLakhs >= 1 then
vWord := vWord || Interprt(vLakhs)||' Lakhs ' ;
End if ;
If vThousand >= 1 then
vWord := vWord || Interprt(vThousand)||' Thousand ' ;
End if ;
If vHundred >= 1 then
vWord := vWord || Interprt(vHundred)||' Hundred ' ;
End if ;
Begin
SELECT a.curr_code INTO vCurrCode
FROM Cm004_comp_mst a
WHERE a.comp_code = pCompCode ;
Exception
When Others Then
vCurrCode := null;
End;
If vCurrCode is not null Then
Begin
SELECT a.subunit, a.mainunit INTO vCurrSubUnit, vCurrMainUnit
FROM Cm001_currency_mst a
WHERE a.curr_code = vCurrCode ;
Exception
When Others Then
vCurrMainUnit := null ;
vCurrSubUnit := null ;
End ;
End If;
vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'Rupees') ;
If vDecimal > 0 then
vWord := vWord || ' And ' || Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'Paise') || ' Only' ;
Else
If vWord is not null Then
vWord := vWord || ' ONLY' ;
End If;
End if ;
Return(vWord);
Exception
When Value_Error then
return null ;*/
END;
Function Interprt(pAmount Number) return Varchar2 is
vText Varchar2(1000);
BEGIN
vText := '' ;
If pAmount > 20 then
vText := NumberText(trunc(pAmount,-1)) ;
vText := vText || ' ' || NumberText(pAmount - trunc(pAmount,-1)) ;
Else
vText := NumberText(pAmount) ;
End if ;
Return vText ;
END;
Function Numbertext(pAmount Number) return Varchar2 IS
vText Varchar2(100) ;
BEGIN
Select Decode(pAmount,
1, 'ONE',
2, 'TWO',
3, 'THREE',
4, 'FOUR',
5, 'FIVE',
6, 'SIX',
7, 'SEVEN',
8, 'EIGHT',
9, 'NINE',
10,'TEN',
11,'ELEVEN',
12,'TWELVE',
13,'THIRTEEN',
14,'FOURTEEN',
15,'FIFTEEN',
16,'SIXTEEN',
17,'SEVENTEEN',
18,'EIGHTEEN',
19,'NINETEEN',
20,'TWENTY',
30,'THIRTY',
40,'FORTY',
50,'FIFTY',
60,'SIXTY',
70,'SEVENTY',
80,'EIGHTY',
90,'NINETY',100,'HUNDRED' ) Into vText From Dual ;
Return vText ;
END;
End;
