[Oracle] function number to english characters

create or replace
FUNCTION FUNC_NUM2ENG (iAmount NUMBER)
RETURN VARCHAR2 AS
RtnValue VARCHAR2(300) := '';
vFloatVal NUMBER(15,2) := 0;
vIntVal NUMBER(25) := 0;
vDigit NUMBER(1) := 0;
vLetter1 VARCHAR2(50) := '';
vLetter2 VARCHAR2(50) := '';
vLength NUMBER(25) := 0;
vLength2 NUMBER(25) := 0;
vSubRtnVal1 VARCHAR2(300) := '';
vSubRtnVal2 VARCHAR2(300) := '';
bLock_Digit Char(1) :='N';
iAmount2 NUMBER(15,2) :=0;
BEGIN

iAmount2 := iAmount;
-- check negative number --
IF iAmount2 < 0 THEN
iAmount2 := iAmount2*(-1);

END IF;

vFloatVal := REPLACE(iAmount2,',','')*100;
vIntVal := trunc(REPLACE(iAmount2,',',''))*100;
vFloatVal := vFloatVal - vIntVal;

IF vIntVal = 0 AND vFloatVal = 0 THEN
RETURN 'Zero Bath.-';
END IF;

vLength := LENGTH(vIntVal/100);
FOR i IN 1..vLength LOOP
vLetter1 := '';
vDigit := SUBSTR(vIntVal/100,vLength-i+1,1);

IF vDigit = '1' AND (i = 2 OR i = 5 OR i = 8 ) THEN
bLock_Digit :='Y' ;
-- IF vDigit = '1' AND (i-1 = 1 OR i-1 = 4 OR i-1 = 7 ) THEN
-- vLetter2 := 'Ten';
-- ELSIF vDigit = '1' AND (i = 1 OR i = 4 OR i = 7 ) THEN
IF SUBSTR(vIntVal/100,-i+1,1) = '0' AND vDigit = '1' AND (i = 2 OR i = 5 OR i = 8 ) THEN
vLetter1 := 'Ten';
ELSIF SUBSTR(vIntVal/100,-i+1,1) = '1' AND vDigit = '1' AND (i = 2 OR i = 5 OR i = 8 ) THEN
vLetter1 := 'Eleven';
ELSIF SUBSTR(vIntVal/100,-i+1,1) = '2' AND vDigit = '1'AND (i = 2 OR i = 5 OR i = 8 ) THEN
vLetter1 := 'Twelve';
ELSIF SUBSTR(vIntVal/100,-i+1,1) = '3' AND vDigit = '1' AND (i = 2 OR i = 5 OR i = 8 ) THEN
vLetter1 := 'Thirteen';
ELSIF SUBSTR(vIntVal/100,-i+1,1) = '4' AND vDigit = '1' AND (i = 2 OR i = 5 OR i = 8 ) THEN
vLetter1 := 'Fourteen';
ELSIF SUBSTR(vIntVal/100,-i+1,1) = '5' AND vDigit = '1' AND (i = 2 OR i = 5 OR i = 8 ) THEN
vLetter1 := 'Fifteen';
ELSIF SUBSTR(vIntVal/100,-i+1,1) = '6' AND vDigit = '1' AND (i = 2 OR i = 5 OR i = 8 ) THEN
vLetter1 := 'Sixteen';
ELSIF SUBSTR(vIntVal/100,-i+1,1) = '7' AND vDigit = '1' AND (i = 2 OR i = 5 OR i = 8 ) THEN
vLetter1 := 'Seventeen';
ELSIF SUBSTR(vIntVal/100,-i+1,1) = '8' AND vDigit = '1' AND (i = 2 OR i = 5 OR i = 8 ) THEN
vLetter1 := 'Eighteen';
ELSIF SUBSTR(vIntVal/100,-i+1,1) = '9' AND vDigit = '1' AND (i = 2 OR i = 5 OR i = 8 ) THEN
vLetter1 := 'Nineteen';
END IF;
-- ELSIF vDigit = '1' THEN
-- vLetter1 := 'One';
-- END IF;
ELSIF vDigit = '1' AND SUBSTR(vIntVal/100,-i-1,1) = '1' AND (i = 1 OR i = 3 OR i = 4 OR i = 6 OR i = 7 OR i = 9) THEN
vLetter1 := 'One' ;
ELSIF vDigit = '1' AND SUBSTR(vIntVal/100,-i-1,1) is null THEN
vLetter1 := 'One' ||(SUBSTR(vIntVal/100,-i-1,1)) ;
ELSIF vDigit = '1' THEN
vLetter1 := 'One' ;
END IF;


IF vDigit = '2' AND (i = 2 OR i = 5 OR i = 8 ) THEN
vLetter1 := 'Twenty ';

ELSIF vDigit = '2' AND (SUBSTR(vIntVal/100,-i-1,1) <> '1' or SUBSTR(vIntVal/100,-i-1,1) is null) THEN
vLetter1 := 'Two';
ELSIF vDigit = '2' THEN
vLetter1 := 'Two';
END IF;

IF vDigit = '3' AND (i = 2 OR i = 5 OR i = 8 ) THEN
vLetter1 := 'Therty ';
ELSIF vDigit = '3' AND (SUBSTR(vIntVal/100,-i-1,1) <> '1' or SUBSTR(vIntVal/100,-i-1,1) is null) THEN
vLetter1 := 'Three';
ELSIF vDigit = '3' THEN
vLetter1 := 'Three';
END IF;

IF vDigit = '4' AND (i = 2 OR i = 5 OR i = 8 ) THEN
vLetter1 := 'Forty ';
ELSIF vDigit = '4' AND (SUBSTR(vIntVal/100,-i-1,1) <> '1' or SUBSTR(vIntVal/100,-i-1,1) is null) THEN
vLetter1 := 'Four';
ELSIF vDigit = '4' THEN
vLetter1 := 'Four';
END IF;

IF vDigit = '5' AND (i = 2 OR i = 5 OR i = 8 ) THEN
vLetter1 := 'Fifty ';
ELSIF vDigit = '5' AND (SUBSTR(vIntVal/100,-i-1,1) <> '1' or SUBSTR(vIntVal/100,-i-1,1) is null) THEN
vLetter1 := 'Five';
ELSIF vDigit = '5' THEN
vLetter1 := 'Five';
END IF;

IF vDigit = '6' AND (i = 2 OR i = 5 OR i = 8 ) THEN
vLetter1 := 'Sixty ';
ELSIF vDigit = '6' AND (SUBSTR(vIntVal/100,-i-1,1) <> '1' or SUBSTR(vIntVal/100,-i-1,1) is null) THEN
vLetter1 := 'Six';
ELSIF vDigit = '6' THEN
vLetter1 := 'Six';
END IF;

IF vDigit = '7' AND (i = 2 OR i = 5 OR i = 8 ) THEN
vLetter1 := 'Seventy ';
ELSIF vDigit = '7' AND (SUBSTR(vIntVal/100,-i-1,1) <> '1' or SUBSTR(vIntVal/100,-i-1,1) is null) THEN
vLetter1 := 'Seven ';
ELSIF vDigit = '7' THEN
vLetter1 := 'Seven ';
END IF;

IF vDigit = '8' AND (i = 2 OR i = 5 OR i = 8 ) THEN
vLetter1 := 'Eighty ';
ELSIF vDigit = '8' AND (SUBSTR(vIntVal/100,-i-1,1) <> '1' or SUBSTR(vIntVal/100,-i-1,1) is null) THEN
vLetter1 := 'Eight';
ELSIF vDigit = '8' THEN
vLetter1 := 'Eight';
END IF;

IF vDigit = '9' AND (i = 2 OR i = 5 OR i = 8 ) THEN
vLetter1 := 'Ninty ';
ELSIF vDigit = '9' AND SUBSTR(vIntVal/100,-i-1,1) <> '1' THEN
vLetter1 := 'Nine';
ELSIF vDigit = '9' THEN
vLetter1 := 'Nine';
END IF;

IF vDigit = '0' THEN vLetter1 := NULL; END IF;


IF i = 3 AND vDigit <> '0' THEN
vLetter1 := vLetter1 || ' Hundred ';

-- IF i = 3 THEN
-- IF vDigit <> '0' THEN
-- IF MOD((vIntVal/100),100) >'0' THEN
-- vLetter1 := vLetter1 || ' Hundred And ';
-- ELSE
-- vLetter1 := vLetter1 || ' Hundred ';
-- END IF;
-- ELSIF MOD((vIntVal/100),100) >'0' THEN
-- vLetter1 := vLetter1 || ' And ';
-- END IF;


ELSIF i = 4 THEN
IF vDigit <> '0' THEN
vLetter1 := vLetter1 || ' Thousand ';
ELSIF (vIntVal/1000000) >= 1 AND (vIntVal/100000000) < 100 THEN
vLetter1 := vLetter1 || ' Thousand ';
END IF;

ELSIF i = 6 AND vDigit <> '0' THEN
vLetter1 := vLetter1 || ' Hundred ';


ELSIF i = 7 THEN
IF vDigit <> '0' THEN
vLetter1 := vLetter1 || ' Million ';
ELSIF (vIntVal/1000000000) >= 1 AND (vIntVal/100000000000) < 100 THEN
vLetter1 := vLetter1 || ' Million ';
END IF;


ELSIF i = 9 AND vDigit <> '0' THEN
vLetter1 := vLetter1 || ' Hundred ';
END IF;

vSubRtnVal1 := vLetter1 || vSubRtnVal1 ;
END LOOP;

-- section for decimal point vFloatVal
vLength2 := LENGTH(vFloatVal);
FOR i IN 1..vLength2 LOOP
vLetter2 := '';
vDigit := SUBSTR(vFloatVal,vLength2-i+1,1);

IF vDigit = '1' AND i = 2 THEN
IF SUBSTR(vFloatVal,-1,1) =0 THEN
vLetter2 := 'Ten' ;
ELSIF SUBSTR(vFloatVal,-1,1) = '1' THEN
vLetter2 := 'Eleven';
ELSIF SUBSTR(vFloatVal,-1,1) = '2' THEN
vLetter2 := 'Twelve';
ELSIF SUBSTR(vFloatVal,-1,1) = '3' THEN
vLetter2 := 'Thirteen';
ELSIF SUBSTR(vFloatVal,-1,1) = '4' THEN
vLetter2 := 'Fourteen';
ELSIF SUBSTR(vFloatVal,-1,1) = '5' THEN
vLetter2 := 'Fifteen';
ELSIF SUBSTR(vFloatVal,-1,1) = '6' THEN
vLetter2 := 'Sixteen';
ELSIF SUBSTR(vFloatVal,-1,1) = '7' THEN
vLetter2 := 'Seventeen';
ELSIF SUBSTR(vFloatVal,-1,1) = '8' THEN
vLetter2 := 'Eighteen';
ELSIF SUBSTR(vFloatVal,-1,1) = '9' THEN
vLetter2 := 'Nineteen';
END IF;
ELSIF vDigit = '1' And i = 1 AND SUBSTR(vFloatVal,-2,1) = 1 THEN
vLetter2 := '';
ELSIF vDigit = '1' THEN
vLetter2 := 'One';
END IF;


IF vDigit = '2' AND i = 2 THEN
vLetter2 := ' Twenty ';
ELSIF vDigit = '2' AND i = 2 AND SUBSTR(vFloatVal,-2,1) = 1 THEN
vLetter2 := '';
ELSIF vDigit = '2' THEN
vLetter2 := 'Two';
END IF;

IF vDigit = '3' AND i = 2 THEN
vLetter2 := ' Thirty ';
ELSIF vDigit = '3' AND SUBSTR(vFloatVal,-2,1) = 1 THEN
vLetter2 := '';
ELSIF vDigit = '3' THEN
vLetter2 := 'Three';
END IF;

IF vDigit = '4' AND i = 2 THEN
vLetter2 := ' Forty ';
ELSIF vDigit = '4' AND SUBSTR(vFloatVal,-2,1) = 1 THEN
vLetter2 := '';
ELSIF vDigit = '4' THEN
vLetter2 := 'Four';
END IF;

IF vDigit = '5' AND i = 2 THEN
vLetter2 := ' Fifty ';
ELSIF vDigit = '5' AND SUBSTR(vFloatVal,-2,1) = 1 THEN
vLetter2 := '';
ELSIF vDigit = '5' THEN
vLetter2 := 'Five';
END IF;

IF vDigit = '6' AND i = 2 THEN
vLetter2 := ' Sixty ';
ELSIF vDigit = '6' AND SUBSTR(vFloatVal,-2,1) = 1 THEN
vLetter2 := '';
ELSIF vDigit = '6' THEN
vLetter2 := 'Six';
END IF;

IF vDigit = '7' AND i = 2 THEN
vLetter2 := ' Seventy ';
ELSIF vDigit = '7' AND SUBSTR(vFloatVal,-2,1) = 1 THEN
vLetter2 := '';
ELSIF vDigit = '7' THEN
vLetter2 := 'Seven';
END IF;


IF vDigit = '8' AND i = 2 THEN
vLetter2 := ' Eighty ';
ELSIF vDigit = '8' AND SUBSTR(vFloatVal,-2,1) = 1 THEN
vLetter2 := '';
ELSIF vDigit = '8' THEN
vLetter2 := 'Eight';
END IF;


IF vDigit = '9' AND i = 2 THEN
vLetter2 := ' Ninety ';
ELSIF vDigit = '9' AND SUBSTR(vFloatVal,-2,1) = 1 THEN
vLetter2 := '';
ELSIF vDigit = '9' THEN
vLetter2 := 'Nine';
END IF;


IF vDigit = '0' THEN vLetter2 := NULL; END IF;

vSubRtnVal2 := vLetter2 || vSubRtnVal2;
END LOOP;

IF vFloatVal = 0 THEN
RtnValue := vSubRtnVal1 || ' Bath Only';
ELSIF vIntVal = 0 THEN
RtnValue := '-' || vSubRtnVal2 || ' Satang-';
ELSE
RtnValue := '-' || vSubRtnVal1 || ' Bath ' || vSubRtnVal2 || ' Satang-';
END IF;

RETURN RtnValue;
END FUNC_NUM2ENG;