Open Source ERP Academy

How to Convert Numbers into Words (Payment or Check Printing)

The purpose of this page is to help you convert a numeric to a text. In the United States, we still write quite a few checks. The conversion from number to words is required for checks. The below will greatly help you format your check accordingly. Example:

The magic happens with this PostgreSQL function. Here is a discussion from the iDempiere community.

Copied below for your convenience:

CREATE OR REPLACE FUNCTION fnNumberToWords(n BIGINT) RETURNS TEXT AS
$
DECLARE
  e TEXT;
BEGIN


  WITH Below20(Word, Id) AS
  (
    VALUES
      ('Zero', 0), ('One', 1),( 'Two', 2 ), ( 'Three', 3),
      ( 'Four', 4 ), ( 'Five', 5 ), ( 'Six', 6 ), ( 'Seven', 7 ),
      ( 'Eight', 8), ( 'Nine', 9), ( 'Ten', 10), ( 'Eleven', 11 ),
      ( 'Twelve', 12 ), ( 'Thirteen', 13 ), ( 'Fourteen', 14),
      ( 'Fifteen', 15 ), ('Sixteen', 16 ), ( 'Seventeen', 17),
      ('Eighteen', 18 ), ( 'Nineteen', 19 )
   ),
   Below100(Word, Id) AS
   (
      VALUES
       ('Twenty', 2), ('Thirty', 3),('Forty', 4), ('Fifty', 5),
       ('Sixty', 6), ('Seventy', 7), ('Eighty', 8), ('Ninety', 9)
   )
   SELECT
     CASE
      WHEN n = 0 THEN  ''
      WHEN n BETWEEN 1 AND 19
        THEN (SELECT Word FROM Below20 WHERE ID=n)
     WHEN n BETWEEN 20 AND 99
       THEN  (SELECT Word FROM Below100 WHERE ID=n/10) ||  '-'  ||
             fnNumberToWords( n % 10)
     WHEN n BETWEEN 100 AND 999
       THEN  (fnNumberToWords( n / 100)) || ' Hundred ' ||
           fnNumberToWords( n % 100)
     WHEN n BETWEEN 1000 AND 999999
       THEN  (fnNumberToWords( n / 1000)) || ' Thousand ' ||
           fnNumberToWords( n % 1000)
     WHEN n BETWEEN 1000000 AND 999999999
       THEN  (fnNumberToWords( n / 1000000)) || ' Million ' ||
           fnNumberToWords( n % 1000000)
     WHEN n BETWEEN 1000000000 AND 999999999999
       THEN  (fnNumberToWords( n / 1000000000)) || ' Billion ' ||
           fnNumberToWords( n % 1000000000)
     WHEN n BETWEEN 1000000000000 AND 999999999999999
       THEN  (fnNumberToWords( n / 1000000000000)) || ' Trillion ' ||
           fnNumberToWords( n % 1000000000000)
    WHEN n BETWEEN 1000000000000000 AND 999999999999999999
       THEN  (fnNumberToWords( n / 1000000000000000)) || ' Quadrillion ' ||
           fnNumberToWords( n % 1000000000000000)
    WHEN n BETWEEN 1000000000000000000 AND 999999999999999999999
       THEN  (fnNumberToWords( n / 1000000000000000000)) || ' Quintillion ' ||
           fnNumberToWords( n % 1000000000000000000)
          ELSE ' INVALID INPUT' END INTO e;


  e := RTRIM(e);


  IF RIGHT(e,1)='-' THEN
    e := RTRIM(LEFT(e,length(e)-1));
  END IF;


  RETURN e;
END;
$ LANGUAGE PLPGSQL;

Here is an example where you can modify the c_payselection_check_v view to include the following column:

  ((fnnumbertowords(trunc(psc.payamt)::bigint) || ' and '::text) ||
        CASE
            WHEN (psc.payamt - trunc(psc.payamt)) <> 0.0 
            THEN fnnumbertowords(abs(round((psc.payamt - trunc(psc.payamt)) * 100::numeric))::bigint)
            ELSE 'No'::text
        END) || ' cents'::text AS payamt_words

Reference