We have requirements to produce Payment Requests through Oracle E-Business suite custom interface, where the users are allowed to select different currencies based on the payment.
Different currencies means different precisions, US $ 2, Indian Rupees 2, while Kuwait, Bahrain have 3 precisions to maintain…
So the amounts displayed on reports needed to be formatted properly and we were doing some hardcoded formatting like
Now we have more currencies to deal with, hence came up with following simple function, which accepts the amount, precisions as inputs and returns a VARCHAR2 string as formatted number!
CREATE OR REPLACE FUNCTION xx_format_number (P_AMOUNT IN NUMBER, P_DEC_PREC IN NUMBER) RETURN VARCHAR2 IS frm_1 VARCHAR2 (30) := 'fm999999999990.'; new_num VARCHAR2 (30); BEGIN SELECT TO_CHAR (P_AMOUNT, RPAD (frm_1, 15 + P_DEC_PREC, '90000000000000000')) INTO new_num FROM DUAL; RETURN (new_num); END;
You can check the function like following
SET SERVEROUTPUT ON; DECLARE l_new_num VARCHAR2 (30); BEGIN l_new_num := xx_format_number(.009,3); DBMS_OUTPUT.PUT_LINE (l_new_num); END;
Check it out, and let us know whether it worked for you!