Display Numbers in Figures useing AP Module AP_Convert_number Function

In AP Module there is a builtin Function (ap_amount_utilities_pkg.ap_convert_number )to convert number value into Figures.

Example :-

SELECT Upper(ap_amount_utilities_pkg.ap_convert_number (123456789)) AS Value_in_words  FROM DUAL  -- For Upper Case Letters

SELECT InitCap(ap_amount_utilities_pkg.ap_convert_number (123456789)) AS Value_in_words  FROM DUAL -- For Mixed Case Letters

SELECT Lower(ap_amount_utilities_pkg.ap_convert_number (123456789)) AS Value_in_words  FROM DUAL -- For Lower Case Letters

SQL Query to Display Numbers in Figures

Example 1:-

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(12345678, 9, '0'), 'FF9'),'FFSP') AS Value_in_words  FROM DUAL -- For Upper Case Letters

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(12345678, 9, '0'), 'FF9'),'Ffsp') AS Value_in_words  FROM DUAL -- For Mixed Case Letters

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(12345678, 9, '0'), 'FF9'),'ffsp') AS Value_in_words  FROM DUAL -- For Lower Case Letters

Example 2:-

select to_char(to_date(1122187,'J'),'JSP') Value_in_words from dual   -- For upper-case letters

select to_char(to_date(1122187,'J'),'Jsp') Value_in_words from dual  -- For mixed-case letters

select to_char(to_date(1122187,'J'),'jsp') Value_in_words from dual  -- For lower-case letters

SQL Query to split values seperated by comma

SELECT TRIM( SUBSTR ( txt , INSTR (txt, ',', 1, level ) + 1,
INSTR (txt, ',', 1, level+1) - INSTR (txt, ',', 1, level) -1) )         AS token  
FROM ( SELECT ','||:in_string||',' AS txt  FROM dual )
CONNECT BY level <= LENGTH(txt)-LENGTH(REPLACE(txt,',',''))-1

--Example
-------
--If value of in_string is entered as 1234,2,3,45,6,7,7,88,9,346

PL/SQL Function to conver amount in number value to words

FUNCTION test_amt_in_word (in_val NUMBER, curr VARCHAR2, subunit VARCHAR2)
RETURN VARCHAR2
IS
billion NUMBER (15);
million NUMBER (12);
thousand NUMBER (9);
paisas NUMBER (6);
spell_amt VARCHAR2 (160);
positive_val NUMBER (15, 2);
BEGIN
IF in_val < 0
THEN
positive_val := in_val * -1;
ELSE
positive_val := in_val;
END IF;
SELECT TO_NUMBER (SUBSTR (LPAD ((positive_val - MOD (positive_val, 1)),
15,
'0'
),
-12,
3
)
),
TO_NUMBER (SUBSTR (LPAD ((positive_val - MOD (positive_val, 1)),
15,
'0'
),
-9,
3
)
),
TO_NUMBER (SUBSTR (LPAD ((positive_val - MOD (positive_val, 1)),
15,
'0'
),
-6,
6
)
),
TO_NUMBER (RPAD (SUBSTR (MOD (positive_val, 1), 2, 2), 2, '0'))
INTO billion,
million,
thousand,
paisas
FROM DUAL;
IF NVL (billion, 0) != 0
THEN
SELECT INITCAP (TO_CHAR (TO_DATE (billion, 'j'), 'jsp')) || ' Billion'
INTO spell_amt
FROM DUAL;
END IF;
IF NVL (million, 0) != 0
THEN
SELECT RTRIM (spell_amt)
|| ' '
|| INITCAP (TO_CHAR (TO_DATE (million, 'j'), 'jsp'))
|| ' Million'
INTO spell_amt
FROM DUAL;
END IF;
IF NVL(thousand, 0) != 0
THEN
SELECT RTRIM (spell_amt)
|| ' '
|| INITCAP (TO_CHAR (TO_DATE (thousand, 'j'), 'jsp'))
|| ' '
|| curr
INTO spell_amt
FROM DUAL;
END IF;
IF NVL(paisas, 0) != 0
THEN
SELECT RTRIM (spell_amt)
|| '  '
||'and '
|| INITCAP (TO_CHAR (TO_DATE (paisas, 'j'), 'jsp'))||' '||subunit
INTO spell_amt
FROM DUAL;
END IF;
RETURN spell_amt;
END  test_amt_in_word ;

SQL Query for List of API's in APPS

select DS.OWNER
, DS.NAME DS.TYPE
, DO.status
, DO.last_ddl_time
, DS.text Description
from dba_source DS, dba_objects DO
WHERE DO.object_name = DS.name
and DS.text like '%Header%'
and Ds.type = DO.object_type
and DS.name like UPPER(:P_API_NAME) orderby DS.name