function CF_11Formula return Char is
NumberIN float:=round(:CF_1,2);
InvalidNumberFormatModel EXCEPTION;
PRAGMA EXCEPTION_INIT(InvalidNumberFormatModel,-1481);
InvalidNumber EXCEPTION;
PRAGMA EXCEPTION_INIT(InvalidNumber,-1722);
TYPE GroupTableType IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
ConversionType CHAR(6) := '';
GroupTable GroupTableType;
GroupIndex NUMBER;
Words VARCHAR2(2000);
WholePart NUMBER;
FractionalPart NUMBER;
FractionalDigits NUMBER;
Remainder NUMBER;
Remainder1 NUMBER;
Remainder2 NUMBER;
Suffix VARCHAR2(50);
BEGIN
GroupTable(0) := '';
GroupTable(1) := ' ten';
GroupTable(2) := ' hundred';
GroupTable(3) := ' thousand';
GroupTable(4) := ' ten thousand';
GroupTable(5) := ' lakh ';
GroupTable(6) := ' ten lakh ';
GroupTable(7) := ' crore ';
GroupTable(8) := ' ten crore ';
GroupTable(9) := ' hundred crore ';
GroupTable(10) := ' thousand crore ';
GroupTable(11) := ' ten thousand crore ';
GroupTable(12) := ' lakh crore ';
GroupTable(13) := ' ten lakh crore ';
GroupTable(14) := ' hundred lakh crore ';
GroupTable(15) := ' thousand lakh crore ';
GroupTable(16) := ' ten thousand lakh crore ';
GroupTable(17) := ' lakh lakh crore ';
GroupTable(18) := ' ten lakh lakh crore ';
GroupTable(19) := ' crore crore ';
WholePart := ABS(TRUNC(NumberIN)); -- Calculate whole and fractional parts
FractionalPart := ABS(NumberIN) - WholePart;
IF FractionalPart = 0 THEN -- Check if fractional part is 0
Words := 'zero paise';
Suffix := ' and ';
ELSE
IF ConversionType = 'N' THEN
FractionalDigits := LENGTH(TO_CHAR(FractionalPart)) - 1;
IF FractionalDigits > 15 THEN
RAISE InvalidNumber;
END IF;
Suffix := GroupTable(FractionalDigits) || 'th';
FractionalPart := FractionalPart * POWER(10,FractionalDigits);
ELSE
IF LENGTH(TO_CHAR(FractionalPart)) > 3 THEN
RAISE InvalidNumber;
END IF;
FractionalPart := FractionalPart * 100;
IF FractionalPart = 1 THEN
Suffix := ' Paise';
ELSE
Suffix := ' Paise';
END IF;
END IF;
IF FractionalPart <= 99999 THEN
Words := TO_CHAR(TO_DATE(FractionalPart,'j'),'Jsp') ||Suffix;
ELSE
GroupIndex := 0;
WHILE FractionalPart != 0
LOOP
Remainder := MOD(FractionalPart,1000);
IF Remainder != 0 THEN
Words := TO_CHAR(TO_DATE(Remainder,'j'),'Jsp') || GroupTable(GroupIndex) || Words;
END IF;
GroupIndex := GroupIndex + 3;
FractionalPart:= TRUNC(FractionalPart / 1000);
END LOOP;
Words := Words || Suffix;
END IF;
Suffix := ' and ';
END IF;
IF WholePart = 0 THEN
IF ConversionType = '' THEN
Words := 'zero ' || Suffix || Words;
ELSE
Words := 'zero' || Suffix || Words;
END IF;
ELSE
IF WholePart = 1 THEN
Suffix := ' Rupee' || Suffix;
ELSE
Suffix := '' || Suffix;
END IF;
IF WholePart <= 99999 THEN
Words := TO_CHAR(TO_DATE(WholePart,'j'),'Jsp') ||Suffix || Words;
ELSE
IF LENGTH(TO_CHAR(WholePart)) > 15 THEN
RAISE InvalidNumber;
END IF;
GroupIndex := 0;
Words := Suffix || Words;
WHILE WholePart != 0
LOOP
IF WholePart < 10000000 THEN
Remainder := MOD(WholePart,100000);
IF Remainder != 0 THEN
Words := TO_CHAR(TO_DATE(Remainder,'j'),'Jsp') ||GroupTable(GroupIndex) || Words;
END IF;
GroupIndex := GroupIndex + 5;
WholePart := TRUNC(WholePart / 100000);
ELSE
Remainder := MOD(WholePart,10000000);
IF Remainder != 0 THEN
IF Remainder >= 100000 THEN
Remainder2 := MOD(Remainder,100000);
Words := TO_CHAR(TO_DATE(Remainder2,'j'),'Jsp') || words;
Remainder1 := trunc(Remainder/100000);
Words := TO_CHAR(TO_DATE(Remainder1,'j'),'Jsp') ||' lakh '|| Words;
ELSE
Words := TO_CHAR(TO_DATE(Remainder,'j'),'Jsp') ||GroupTable(GroupIndex) || Words;
END IF;
END IF;
GroupIndex := GroupIndex + 7;
WholePart := TRUNC(WholePart / 10000000);
END IF;
END LOOP;
END IF;
END IF;
IF Words IS NULL THEN
Words := 'zero';
END IF;
IF SIGN(NumberIN) = -1 THEN
Words := 'minus ' || Words;
END IF;
return Words||' Only';
EXCEPTION
WHEN OTHERS THEN
RETURN ' ';
END;
Function CF_1Formula return number is
Number1IN float := :CS_1;-- Sum
Number2IN float := :CS_2;
Number2IN float := :CS_3;
Number2IN float := :CS_4;
Number2IN float := :CS_5;
TOT number;
v_po_num varchar2(30);
v_tx_tot number;
v_curr varchar2(30);
begin
if :CURRENCY_CODE = 'INR' then
TOT := nvl(:CS_1,0)+nvl(:CS_2,0)+nvl(:CS_3,0)+nvl(:CS_4,0)+ nvl(:CS_5,0);
--return TOT;
SRW.MESSAGE(1000, tot);
else
begin
select DISTINCT (PHA.SEGMENT1),
sum(JPT.TAX_AMOUNT),jpt.CURRENCY
into v_po_num,
v_tx_tot,
v_curr
from PO_HEADERS_ALL PHA,
JAI_PO_TAXES JPT,
JAI_CMN_TAXES_ALL JCTA
where PHA.PO_HEADER_ID=JPT.PO_HEADER_ID
AND PHA.TYPE_LOOKUP_CODE in ('STANDARD','BLANKET')
and JPT.TAX_ID=JCTA.TAX_ID
And PHA.po_header_id=:po_header_id
group by PHA.SEGMENT1,jpt.CURRENCY;
exception
when others then
v_tx_tot := 0;
end;
if v_curr = 'INR' then
TOT := nvl(:CS_1,0)+nvl((v_tx_tot/:E_RATE),0);
--return TOT;
else
TOT := nvl(:CS_1,0)+nvl(:CS_2,0)+nvl(:CS_3,0)+nvl(:CS_4,0)+ nvl(:CS_5,0);
--return TOT;
end if;
end if;
return TOT;
exception
when others then
return 0;
end;
OR you can use below oracle Package -
SELECT AP_AMOUNT_UTILITIES_PKG.AP_CONVERT_NUMBER (123456789) AMOUNT_IN_WORD FROM DUAL;
