Convert Amount in Words (ORACLE APPS)

Amount in Words

Convert Numbers To Arabic Words (Tafqeet)
Number in Arabic Currency
convert to amount in words
Numbers to words in any language
This function will convert a number to words, handy for ptinting cheques
Number To Words Conversion Function
How To Convert Number into Words using Oracle SQL Query
Spell number to words (in Arabic)
Convert money into words using PL SQL function,
Function To Convert Amount to Words
Convert amount in numbers to amount in words in Oracle
Oracle Number to Word Conversion

 ------------------------------------------------------------------------------------------------

Numbers to words in any language [AP_AMOUNT_UTILITIES_PKG.ap_convert_number, ap_lookup_codes]
 ------------------------------------------------------------------------------------------------

This tip describes how to convert numbers to words while logged into Oracle Applications as an English user, using a custom view and function. This should work for any language that is installed on the database.


This code snippet is primarily used for converting numbers to words in any language. We had an instance where we needed to print French checks while logged into Oracle Applications as an English user. Here's how we did it.

NOTE: This should work for any language that is installed on the database. We have English and Arabic. For the main language the user could use the Oracle built-in function


AP_AMOUNT_UTILITIES_PKG.ap_convert_number. I have manipulated and created a view and a function based on this function.

 

Created a custom view based on ap_lookup_codes, and forced it to use Arabic instead of userenv('LANG').

 

1. View Script

 

CREATE OR REPLACE VIEW xxedb_ap_lookup_codes_ar_v AS
SELECT lv.lookup_type, lv.lookup_code, lv.meaning "DISPLAYED_FIELD",
lv.description, lv.enabled_flag, lv.start_date_active,
lv.end_date_active,
CAST('فقط'  AS NVARCHAR2(100)) FAQT,
CAST ('لاغير' AS NVARCHAR2(100))LGAR,
CAST('درهم'  AS NVARCHAR2(100)) AED_CR   ,
CAST('و' as nvarchar2(100)) and_ar,
CAST('فلس' AS NVARCHAR2(100)) FILLS_AR
   FROM fnd_lookup_values lv
WHERE lv.LANGUAGE = 'AR'
AND lv.view_application_id =  200
AND lv.security_group_id = fnd_global.lookup_security_group(lv.lookup_type,        lv.view_application_id    );

 

2. Created a custom function to convert numbers to words. Used for AED amount.

 

Package Specification

CREATE OR REPLACE PACKAGE APPS.XXEDB_GET_AMT_INWARDS_PKG AUTHID CURRENT_USER
AS
--------------------------------------------------------------------------------
--  PACKAGE:      XXEDB_GET_AMT_INWARDS_PKG                                       --
--                                                                                --
--  DESCRIPTION:  Convert the Amount in Text as Arabic Using the English Session  --
--                                                                                --
--  MODIFICATION HISTORY                                                          --
--  Date            Username        Description                                   --
--  11 MAY, 2015    AURANGZAIB      Initial Creation                              --
--------------------------------------------------------------------------------
   FUNCTION get_amt_words_arabic (in_numeral IN NUMBER)
      RETURN VARCHAR2;
   FUNCTION translate_amt_words_arabic (in_numeral IN NUMBER)
      RETURN VARCHAR2;
END xxedb_get_amt_inwards_pkg;


Package Body

CREATE OR REPLACE PACKAGE BODY APPS.xxedb_get_amt_inwards_pkg
AS
--------------------------------------------------------------------------------
--  PACKAGE:      XXEDB_GET_AMT_INWARDS_PKG                                       --
--                                                                                --
--  DESCRIPTION:  Convert the Amount in Text as Arabic Using the English Session  --
--                                                                                --
--  MODIFICATION HISTORY                                                          --
--  Date            Username        Description                                   --
--  11 MAY, 2015    AURANGZAIB      Initial Creation                              --
--------------------------------------------------------------------------------
   FUNCTION get_amt_words_arabic (in_numeral IN NUMBER)
      RETURN VARCHAR2
   IS
      l_whole_amt              NUMBER;
      l_whole_amt_inwards      VARCHAR2 (32000);
      l_fraction_amt           NUMBER;
      l_fraction_amt_inwards   VARCHAR2 (32000);
      l_amount_inwards         VARCHAR2 (32000);
      --
      l_faqt                   NVARCHAR2 (1000);
      l_lgar                   NVARCHAR2 (1000);
      l_aed_cr                 NVARCHAR2 (1000);
      l_and_ar                 NVARCHAR2 (1000);
      l_fills_ar               VARCHAR2 (1000);
   BEGIN
      l_whole_amt := TRUNC (in_numeral);
      l_whole_amt_inwards := translate_amt_words_arabic (l_whole_amt);
      l_fraction_amt := (ROUND (in_numeral, 2) - TRUNC (in_numeral)) * 100;
      l_fraction_amt_inwards := translate_amt_words_arabic (l_fraction_amt);
      ---
      SELECT faqt, lgar, aed_cr, and_ar, fills_ar
        INTO l_faqt, l_lgar, l_aed_cr, l_and_ar, l_fills_ar
        FROM xxedb_ap_lookup_codes_ar_v
       WHERE ROWNUM = 1;
      ---
      IF l_fraction_amt > 0
      THEN
         l_amount_inwards :=
               l_faqt
            || ' '
            || l_whole_amt_inwards
            || ' '
            || l_aed_cr
            || ' '
            || l_and_ar
            || ' '
            || l_fraction_amt_inwards
            || ' '
            || l_fills_ar
            || ' '
            || l_lgar;
      ELSE
         l_amount_inwards :=
               l_faqt
            || ' '
            || l_whole_amt_inwards
            || ' '
            || l_aed_cr
            || ' '
            || l_lgar;
      END IF;
      RETURN (l_amount_inwards);
   END get_amt_words_arabic;
   FUNCTION translate_amt_words_arabic (in_numeral IN NUMBER)
      RETURN VARCHAR2
   IS
      c_zero             xxedb_ap_lookup_codes_ar_v.displayed_field%TYPE;
      c_thousand         xxedb_ap_lookup_codes_ar_v.displayed_field%TYPE;
      c_million          xxedb_ap_lookup_codes_ar_v.displayed_field%TYPE;
      c_billion          xxedb_ap_lookup_codes_ar_v.displayed_field%TYPE;
      number_too_large   EXCEPTION;
      numeral            INTEGER                          := ABS (in_numeral);
      max_digit          INTEGER                                        := 12;
      -- for numbers less than a trillion
      number_text        VARCHAR2 (240)                                 := '';
      billion_seg        VARCHAR2 (25);
      million_seg        VARCHAR2 (25);
      thousand_seg       VARCHAR2 (25);
      units_seg          VARCHAR2 (25);
      billion_lookup     VARCHAR2 (80);
      million_lookup     VARCHAR2 (80);
      thousand_lookup    VARCHAR2 (80);
      units_lookup       VARCHAR2 (80);
      session_language   fnd_languages.nls_language%TYPE;
      thousand           NUMBER                              := POWER (10, 3);
      million            NUMBER                              := POWER (10, 6);
      billion            NUMBER                              := POWER (10, 9);
      --
      faqt               NVARCHAR2 (1000);
      lgar               NVARCHAR2 (1000);
      aed_cr             NVARCHAR2 (1000);
   BEGIN
      IF numeral >= POWER (10, max_digit)
      THEN
         RAISE number_too_large;
      END IF;
--For Bug459665
      IF numeral = 0
      THEN
         SELECT ' ' || displayed_field || ' '
           INTO c_zero
           FROM ap_lookup_codes
          WHERE lookup_code = 'ZERO';
         RETURN (c_zero);
      END IF;
      billion_seg := TO_CHAR (TRUNC (numeral / billion));
      numeral := numeral - (TRUNC (numeral / billion) * billion);
      million_seg := TO_CHAR (TRUNC (numeral / million));
      numeral := numeral - (TRUNC (numeral / million) * million);
      thousand_seg := TO_CHAR (TRUNC (numeral / thousand));
      units_seg := TO_CHAR (MOD (numeral, thousand));
      SELECT ' ' || lc1.displayed_field || ' ',
             ' ' || lc2.displayed_field || ' ',
             ' ' || lc3.displayed_field || ' ', ' ' || lc4.displayed_field,
             lc5.description, lc6.description, lc7.description,
             lc8.description
                            --
      ,      lc8.faqt, lc8.lgar, lc8.aed_cr
        --
      INTO   c_billion,
             c_million,
             c_thousand, c_zero,
             billion_lookup, million_lookup, thousand_lookup,
             units_lookup,
                          --
                          faqt, lgar, aed_cr
        --
      FROM   xxedb_ap_lookup_codes_ar_v lc1,
             xxedb_ap_lookup_codes_ar_v lc2,
             xxedb_ap_lookup_codes_ar_v lc3,
             xxedb_ap_lookup_codes_ar_v lc4,
             xxedb_ap_lookup_codes_ar_v lc5,
             xxedb_ap_lookup_codes_ar_v lc6,
             xxedb_ap_lookup_codes_ar_v lc7,
             xxedb_ap_lookup_codes_ar_v lc8
       WHERE lc1.lookup_code = 'BILLION'
         AND lc1.lookup_type = 'NLS TRANSLATION'
         AND lc2.lookup_code = 'MILLION'
         AND lc2.lookup_type = 'NLS TRANSLATION'
         AND lc3.lookup_code = 'THOUSAND'
         AND lc3.lookup_type = 'NLS TRANSLATION'
         AND lc4.lookup_code = 'ZERO'
         AND lc4.lookup_type = 'NLS TRANSLATION'
         AND lc5.lookup_code = billion_seg
         AND lc5.lookup_type = 'NUMBERS'
         AND lc6.lookup_code = million_seg
         AND lc6.lookup_type = 'NUMBERS'
         AND lc7.lookup_code = thousand_seg
         AND lc7.lookup_type = 'NUMBERS'
         AND lc8.lookup_code = units_seg
         AND lc8.lookup_type = 'NUMBERS';
--Commented For Bug459665
/*
if numeral = 0 then
     return(c_zero);
  end if;
*/
      SELECT SUBSTR (USERENV ('LANGUAGE'),
                     1,
                     INSTR (USERENV ('LANGUAGE'), '_') - 1
                    )
        INTO session_language
        FROM DUAL;
--Bug 335063 fix.
      IF     (   session_language = 'FRENCH'
              OR session_language = 'CANADIAN FRENCH'
             )
         AND thousand_seg = '1'
      THEN
         thousand_lookup := NULL;
      END IF;
--
      IF billion_seg <> '0'
      THEN
         number_text := number_text || billion_lookup || c_billion;
      END IF;
      IF million_seg <> '0'
      THEN
         number_text := number_text || million_lookup || c_million;
      END IF;
      IF thousand_seg <> '0'
      THEN
         number_text := number_text || thousand_lookup || c_thousand;
      END IF;
      IF units_seg <> '0'
      THEN
         number_text := number_text || units_lookup;
      END IF;
      number_text := LTRIM (number_text);
      number_text :=
            UPPER (SUBSTR (number_text, 1, 1))
         || RTRIM (LOWER (SUBSTR (number_text, 2, LENGTH (number_text))));
        
    --Return the Amount as the Text in Arabic.
     
      --RETURN (faqt || ' ' || number_text || ' ' || aed_cr || ' ' || lgar);
     
      RETURN (number_text);
  
   EXCEPTION
      WHEN number_too_large
      THEN
         RETURN (NULL);
      WHEN OTHERS
      THEN
         RETURN (NULL);
   END translate_amt_words_arabic;
END xxedb_get_amt_inwards_pkg;
/


3. Test Script


Test Script for the Amount having no Fraction

SELECT xxedb_get_amt_inwards_pkg.get_amt_words_arabic (1234) FROM DUAL;

Test Script for the Amount having Fraction, Amount in Arabic will be in Round 2 Digits.


SELECT xxedb_get_amt_inwards_pkg.get_amt_words_arabic (1234.567) FROM DUAL;