Convert the Amount in to the Word using Function in Oracle Apps EBS R12

 

Convert the Amount in to the Word using Function in Oracle Apps EBS R12.

Function:

CREATE OR REPLACE FUNCTION APPS.Get_amount_to_word(P_LC_AMOUNT IN NUMBER,P_CURRENCY_CODE IN VARCHAR2)
RETURN VARCHAR2
IS

x_tot_amount1     NUMBER            := 0;
x_tot_amount      VARCHAR2(30)      := 0;
x_amount_in_word  VARCHAR2(2000)    := 0;

BEGIN

x_tot_amount1 := p_lc_amount;

    x_tot_amount := TO_CHAR(x_tot_amount1,'999999999999.99');
   
    BEGIN
        IF NVL(x_tot_amount1,0) >= 0 THEN
            IF p_currency_code ='INR' THEN -- ## g_const_currency_bdt = 'BDT' ##--
           
                BEGIN
                    SELECT  'Rupee '||REPLACE(AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 1))||
                            DECODE(AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 2)),NULL,' Only',
                            ' and Paisa'||AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 2))||' Only'),'-',' ')
                      INTO x_amount_in_word
                      FROM DUAL;
                END;
            ELSIF p_currency_code <>'INR' THEN
           
               BEGIN
                    SELECT  REPLACE(AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 1))||
                            DECODE(AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 2)),NULL,' Only',
                            ' and Cents '||AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 2))||' Only'),'-',' ')
                      INTO x_amount_in_word
                      FROM DUAL;
                END;
--            ELSIF p_currency_code ='USD' THEN   
--           
--               BEGIN
--                    SELECT  'Dollar '||REPLACE(AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 1))||
--                            DECODE(AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 2)),NULL,' Only',
--                            ' and Cents '||AP_AMOUNT_UTILITIES_PKG.ap_convert_number(REGEXP_SUBSTR (x_tot_amount, '[^.]+', 1, 2))||' Only'),'-',' ')
--                      INTO x_amount_in_word
--                      FROM DUAL;
--                END;
           
            END IF;
        END IF;
    END;
   
    RETURN (x_amount_in_word);
   
    EXCEPTION
    WHEN OTHERS THEN
    FND_FILE.put_line(fnd_file.log,'--##ERROR##--In PACKAGE.function = XXBEX_ASIA_LC_ATHRZTN_PRINT_PK.xxget_amount_to_word/'||SQLERRM);
    RETURN 0;   
END Get_amount_to_word;

====================================================================

CREATE OR REPLACE FUNCTION APPS.XX_CONVERT_AMOUNT_TO_WORDS (P_AMT       IN NUMBER )                                    
                                               RETURN VARCHAR2 IS
M_MAIN_AMT_TEXT      VARCHAR2(2000) ;
M_TOP_AMT_TEXT       VARCHAR2(2000) ;
M_BOTTOM_AMT_TEXT    VARCHAR2(2000) ;
M_DECIMAL_TEXT       VARCHAR2(2000) ;
M_TOP                NUMBER(20,5) ;
M_MAIN_AMT           NUMBER(20,5) ;
M_TOP_AMT            NUMBER(20,5) ;
M_BOTTOM_AMT         NUMBER(20,5) ;
M_DECIMAL            NUMBER(20,5) ;
M_AMT                NUMBER(20,5);
M_TEXT               VARCHAR2(2000) ;
BEGIN
   M_MAIN_AMT        := NULL ;
   M_TOP_AMT_TEXT    := NULL ;
   M_BOTTOM_AMT_TEXT := NULL ;
   M_DECIMAL_TEXT    := NULL ;
  
   -- To get paise part
   M_DECIMAL    := P_AMT - TRUNC(P_AMT) ;
  
   IF M_DECIMAL >0 THEN
   M_DECIMAL := ROUND(M_DECIMAL *100);
   END IF;
  
   M_AMT        := TRUNC(P_AMT) ;          


   M_TOP        := TRUNC(M_AMT / 100000) ;
   M_MAIN_AMT   := TRUNC(M_TOP / 100);
   M_TOP_AMT    := M_TOP - M_MAIN_AMT * 100 ;
   M_BOTTOM_AMT :=  M_AMT - (M_TOP * 100000) ;

  IF M_MAIN_AMT > 0 THEN
      M_MAIN_AMT_TEXT := TO_CHAR(TO_DATE(M_MAIN_AMT,'J'),'JSP') ;
      IF M_MAIN_AMT = 1 THEN
        M_MAIN_AMT_TEXT := M_MAIN_AMT_TEXT || ' CRORE ' ;
      ELSE
        M_MAIN_AMT_TEXT := M_MAIN_AMT_TEXT || ' CRORES ' ;
      END IF ;
   END IF ;

   IF M_TOP_AMT > 0 THEN
      M_TOP_AMT_TEXT := TO_CHAR(TO_DATE(M_TOP_AMT,'J'),'JSP') ;
      IF M_TOP_AMT = 1 THEN
        M_TOP_AMT_TEXT := M_TOP_AMT_TEXT || ' LAKH ' ;
      ELSE
        M_TOP_AMT_TEXT := M_TOP_AMT_TEXT || ' LAKHS ' ;
      END IF;
   END IF ;
   IF M_BOTTOM_AMT > 0 THEN
      M_BOTTOM_AMT_TEXT := TO_CHAR(TO_DATE(M_BOTTOM_AMT,'J'),'JSP') ;
   END IF ;
   IF M_DECIMAL > 0 THEN
      IF NVL(M_BOTTOM_AMT,0) + NVL(M_TOP_AMT,0) > 0 THEN
         M_DECIMAL_TEXT := ' AND ' || TO_CHAR(TO_DATE(M_DECIMAL,'J'),'JSP') || ' Paise ' ;
      ELSE
         M_DECIMAL_TEXT :=  TO_CHAR(TO_DATE(M_DECIMAL,'J'),'JSP') ||' Paise ';
      END IF ;
        END IF ;
   M_TEXT := LOWER(M_MAIN_AMT_TEXT || M_TOP_AMT_TEXT || M_BOTTOM_AMT_TEXT || ' Rupees' || M_DECIMAL_TEXT || ' ONLY') ;
   M_TEXT := UPPER(SUBSTR(M_TEXT,1,1))|| SUBSTR(M_TEXT,2);
   M_TEXT := 'Rupees'||' '|| M_TEXT;
   RETURN (M_TEXT);

END XX_CONVERT_AMOUNT_TO_WORDS;