CREATE OR REPLACE FUNCTION apps.jg_amt_in_word_f (p_num NUMBER)
RETURN CHAR
IS
numberin FLOAT := ROUND (p_num, 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 jg_amt_in_word_f;
/