Friday, 13 October 2017

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;

 

1 comment:

  1. eToro is the #1 forex broker for new and established traders.

    ReplyDelete