FUNCTION TRANSLATION (
p_money IN VARCHAR2,
p_currency IN VARCHAR2 default '' --currency
)
RETURN varchar2
IS
j INT := 0;
i INT := 1;
l int := 0;
m int := 1;
len INT := 0;
len1 INT := 0;
len2 int :=0;
str VARCHAR2 (4000);
s_result varchar2(1000);
suffix varchar2(1000);
num number(1);
dw VARCHAR2(10);
x_num number(1);
tail varchar2(1000) := '';
t_str varchar2(1000);
p_delimiter varchar2(1) :=',';
BEGIN
len := LENGTH (p_money);
len1 := LENGTH (p_delimiter);
t_str :=p_money;
num :=lengthb(regexp_replace(p_money,'[^,]',null));
x_num := lengthb(regexp_replace(p_money,'[^.]',null));
s_result := REPLACE(s_result,'HUNDRED_T');
if x_num > 0 then
t_str := substr(p_money,1,instr(p_money,'.')-1) ;
if substr(p_money,instr(p_money,'.')+1) > 0 then
tail := to_char(to_date(substr(p_money,instr(p_money,'.')+1),'J'),'Jsp')||' CENTS';
end if;
END IF;
if num = 3 then
suffix :='BILLION,MILLION,THOUSAND,HUNDRED_T';
ELSIF num = 2 then
suffix :='MILLION,THOUSAND,HUNDRED_T';
ELSIF num = 1 then
suffix :='THOUSAND,HUNDRED_T';
else
suffix :='HUNDRED_T';
end if;
len2 := LENGTH (suffix);
if REPLACE(t_str,',','') > 0 then
WHILE j < len
LOOP
j := INSTR (t_str, p_delimiter, i);
l := INSTR (suffix, p_delimiter, m);
IF j = 0 and l = 0
THEN
j := len;
l :=len2;
str := SUBSTR (t_str, i);
dw := SUBSTR (suffix, m);
if str > 0 then
s_result :=s_result||' '||to_char(to_date(str,'J'),'Jsp')||' '||dw;
end if;
IF i >= len
THEN
EXIT;
END IF;
ELSE
str := SUBSTR(t_str, i, j - i);
dw := SUBSTR(suffix, m, l - m);
m := l + len1;
i := j + len1;
if str > 0 then
s_result := s_result||' '||to_char(to_date(str,'J'),'Jsp')||' '||dw;
end if;
END IF;
END LOOP;
else
s_result :=tail;
end if;
s_result := UPPER(replace(s_result,'HUNDRED_T')||' '||p_currency);
--s_result :=replace(s_result, 'HUNDRED','HUNDRED AND');
if x_num > 0 and substr(p_money,instr(p_money,'.')+1) > 0 and REPLACE(t_str,',','') > 0 then
s_result := s_result||' AND '||tail;
end if;
RETURN TRIM(UPPER(REPLACE(s_result,'-',' ')));
END TRANSLATION;