[Oracle] function number to thai character

create or replace
FUNCTION FUNC_NUM2THAI (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) := '';
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 'ศูนย์บาท';
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 THEN
vLetter1 := 'สิบ';
ELSIF vDigit = '1' AND i = 1 THEN
IF SUBSTR(vIntVal/100,-2,1) <> '0' THEN
vLetter1 := 'เอ็ด';
ELSE
vLetter1 := 'หนึ่ง';
END IF;
ELSIF vDigit = '1' THEN
vLetter1 := 'หนึ่ง';
END IF;

IF vDigit = '2' AND i = 2 THEN
vLetter1 := 'ยี่สิบ';
-- ELSIF vDigit = '2' AND i = 1 THEN
-- IF SUBSTR(vIntVal/100,-2,1) <> '0' THEN
-- vLetter1 := 'เอ็ด';
-- ELSE
-- vLetter1 := 'สอง';
-- END IF;
ELSIF vDigit = '2' THEN
vLetter1 := 'สอง';
END IF;

IF vDigit = '3' AND i = 2 THEN
vLetter1 := 'สามสิบ';
ELSIF vDigit = '3' THEN
vLetter1 := 'สาม';
END IF;

IF vDigit = '4' AND i = 2 THEN
vLetter1 := 'สี่สิบ';
ELSIF vDigit = '4' THEN
vLetter1 := 'สี่';
END IF;

IF vDigit = '5' AND i = 2 THEN
vLetter1 := 'ห้าสิบ';
ELSIF vDigit = '5' THEN
vLetter1 := 'ห้า';
END IF;

IF vDigit = '6' AND i = 2 THEN
vLetter1 := 'หกสิบ';
ELSIF vDigit = '6' THEN
vLetter1 := 'หก';
END IF;

IF vDigit = '7' AND i = 2 THEN
vLetter1 := 'เจ็ดสิบ';
ELSIF vDigit = '7' THEN
vLetter1 := 'เจ็ด';
END IF;

IF vDigit = '8' AND i = 2 THEN
vLetter1 := 'แปดสิบ';
ELSIF vDigit = '8' THEN
vLetter1 := 'แปด';
END IF;

IF vDigit = '9' AND i = 2 THEN
vLetter1 := 'เก้าสิบ';
ELSIF vDigit = '9' THEN
vLetter1 := 'เก้า';
END IF;

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

IF i = 3 AND vDigit <> '0' THEN
vLetter1 := vLetter1 || 'ร้อย';
ELSIF i = 4 AND vDigit <> '0' THEN
vLetter1 := vLetter1 || 'พัน';
ELSIF i = 5 AND vDigit <> '0' THEN
vLetter1 := vLetter1 || 'หมื่น';
ELSIF i = 6 AND vDigit <> '0' THEN
vLetter1 := vLetter1 || 'แสน';
ELSIF i = 7 AND vDigit <> '0' THEN
vLetter1 := vLetter1 || 'ล้าน';
ELSIF i = 8 AND vDigit <> '0' THEN
vLetter1 := vLetter1 || 'ล้าน';
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
vLetter2 := 'สิบ';
ELSIF vDigit = '1' AND i = 1 THEN
IF SUBSTR(vFloatVal,-2,1) <> '0' THEN
vLetter2 := 'เอ็ด';
ELSE
vLetter2 := 'หนึ่ง';
END IF;
ELSIF vDigit = '1' THEN
vLetter2 := 'หนึ่ง';
END IF;

IF vDigit = '2' AND i = 2 THEN
vLetter2 := 'ยี่สิบ';
-- ELSIF vDigit = '2' AND i = 1 THEN
-- vLetter1 := 'สอง';
ELSIF vDigit = '2' THEN
vLetter2 := 'สอง';
END IF;

IF vDigit = '3' AND i = 2 THEN
vLetter2 := 'สามสิบ';
ELSIF vDigit = '3' THEN
vLetter2 := 'สาม';
END IF;

IF vDigit = '4' AND i = 2 THEN
vLetter2 := 'สี่สิบ';
ELSIF vDigit = '4' THEN
vLetter2 := 'สี่';
END IF;

IF vDigit = '5' AND i = 2 THEN
vLetter2 := 'ห้าสิบ';
ELSIF vDigit = '5' THEN
vLetter2 := 'ห้า';
END IF;

IF vDigit = '6' AND i = 2 THEN
vLetter2 := 'หกสิบ';
ELSIF vDigit = '6' THEN
vLetter2 := 'หก';
END IF;

IF vDigit = '7' AND i = 2 THEN
vLetter2 := 'เจ็ดสิบ';
ELSIF vDigit = '7' THEN
vLetter2 := 'เจ็ด';
END IF;

IF vDigit = '8' AND i = 2 THEN
vLetter2 := 'แปดสิบ';
ELSIF vDigit = '8' THEN
vLetter2 := 'แปด';
END IF;

IF vDigit = '9' AND i = 2 THEN
vLetter2 := 'เก้าสิบ';
ELSIF vDigit = '9' THEN
vLetter2 := 'เก้า';
END IF;

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

vSubRtnVal2 := vLetter2 || vSubRtnVal2;
END LOOP;

IF vFloatVal = 0 THEN
RtnValue := vSubRtnVal1 || 'บาทถ้วน';
ELSIF vIntVal = 0 THEN
RtnValue := '-' || vSubRtnVal2 || 'สตางค์-';
ELSE
RtnValue := '-' || vSubRtnVal1 || 'บาท' || vSubRtnVal2 || 'สตางค์-';
END IF;

RETURN RtnValue;
END FUNC_NUM2THAI;