Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member

For anyone that has ever worked on a financial application, the chances are pretty good that you’ve been asked to develop a facsimile of a “check” or other type of disbursement – including the field where you “spell out” the dollar and cents value of the check.  This is a Frequently Asked Question over on the PowerBuilder forums, but the solutions provided are usually PowerScript code in a client-side NVO method.   Now, this is not a poor solution in and of itself, but this approach locks that functionality away in the client-side PowerScript, and doesn’t afford much reuse.

Here’s what I’m talking about:

This posting outlines a server-side approach, and implements this feature as a stored function in a SQL Anywhere 12 database.  I tried to write the code as “generic” as possible, using ANSI-standard built-in functions so that it could be ported to other DBMS platforms.

CREATE or replace FUNCTION uf_verbalize_group( IN sGroup varchar(3) )
RETURNS varchar(60)
DETERMINISTIC
BEGIN
    DECLARE iGroup  integer;
    DECLARE iHundreds integer;
    DECLARE iTens integer;
    DECLARE iDigits integer;
    DECLARE iUnits integer;
    DECLARE sWords varchar(60);
    declare local temporary table units(
        id        integer     not null default autoincrement,
        unit_name varchar(10) not null,
        primary key (id)
       ) not transactional;
    declare local temporary table tens(
        id integer not null default autoincrement,
        tens_name varchar(10) not null,
        primary key (id)
       ) not transactional;
    insert into units (unit_name )
          select name from OpenString( value string(
              'ONE|TWO|THREE|FOUR|FIVE|SIX|SEVEN|EIGHT|NINE|TEN|',
              'ELEVEN|TWELVE|THIRTEEN|FOURTEEN|FIFTEEN|',
              'SIXTEEN|SEVENTEEN|EIGHTEEN|NINETEEN' ) )
              with (name varchar(10))
              option( row delimited by '|') w1;
    insert into tens (tens_name )
          select name from OpenString( value
              'TEN|TWENTY|THIRTY|FORTY|FIFTY|SIXTY|SEVENTY|EIGHTY|NINETY' )
              with (name varchar(10))
              option( row delimited by '|') w10;
  
    SET iGroup = CAST( sGroup as integer);
    IF iGroup > 99 THEN
       SET iHundreds = CAST(Left(sGroup, 1) as Integer);
       SET sWords =
        (SELECT unit_name || ' HUNDRED '
           from UNITS
          where id = iHundreds ) ;
    END IF ;
    set iDigits = CAST( Right(sGroup, 2) as Integer);
    CASE 
     WHEN iDigits = 0 THEN
      RETURN sWords ;
     WHEN iDigits > 0 and iDigits < 20 THEN
      set sWords =
        (SELECT sWords || unit_name || ' '
           FROM units
          WHERE id = iDigits) ;
     ELSE
      SET iTens = CAST( left( right( sGroup, 2), 1) as Integer);
      set sWords =
        (SELECT sWords || tens_name
           from tens
          where id = iTens);
      SET iUnits = CAST(Right(sGroup, 1) as Integer);
      IF iUnits > 0 THEN
         set sWords =
           (SELECT sWords || '-' || unit_name || ' '
              FROM units
             where id = iUnits);
      ELSE
         SET sWords = sWords || ' ' ;
      END IF ;
     END CASE;
    RETURN sWords;
END
go
CREATE or replace FUNCTION uf_verbalize_amount( IN aDecAmount numeric )
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE sWords   varchar(255);
    DECLARE iDollars integer;
    DECLARE iCents   integer;
    DECLARE sDollars varchar(255);
    DECLARE sGroup   varchar(3);
    SET aDecAmount = ROUND( aDecAmount, 2 );
    SET iDollars   = TRUNCNUM( aDecAmount, 0 );
    SET iCents     = CAST(((aDecAmount - iDollars) * 100) as INTEGER);
    IF iDollars >= 1000000000 THEN
       SET sWords = CAST(iDollars as varchar(60)) || ' DOLLARS AND ' || CAST(iCents as char(2)) || ' CENTS' ;
       RETURN sWords ;
    END IF;
    // Make the dollars string 9 digits long
    SET sDollars = right( REPEAT('0',9) || CAST(iDollars as varchar(12)), 9 );
    IF iDollars > 999999 THEN
       // Verbalize the millions
       SET sGroup = LEFT(sDollars, 3);
       IF CAST( sGroup as integer) > 0 THEN
          SET sWords = uf_verbalize_group(sGroup) || ' MILLION ';
       END IF ;
    END IF ;
    IF iDollars > 999 THEN
       // Verbalize the thousands
       SET sGroup = SUBSTR(sDollars, 4, 3);
       IF CAST (sGroup as Integer) > 0 THEN
          SET sWords = sWords || uf_verbalize_group(sGroup) || ' THOUSAND ' ;
       END IF
    END IF ;
    IF iDollars > 0 THEN
       SET sGroup = RIGHT(sDollars, 3);
       SET sWords = sWords || uf_verbalize_group(sGroup);
    ELSE
       SET sWords = 'ZERO '
    END IF ;
    IF iCents > 0 THEN
       SET sWords = sWords || 'AND ' || CAST(iCents as char(2)) || '/100 DOLLARS' ;
    ELSE
       SET sWords = sWords + 'DOLLARS' ;
    END IF ;
    RETURN sWords;
END
go

-Paul Horan-

1 Comment