▼

Converting Financial Reports' Amounts to Words with Currencies

If ever you need to add currencies into Words and have to include their Decimals (i.e. Cents), this query below demonstrates that using to TO_CHAR(TO_DATE(X, 'J'), 'JSP') trick:

SELECT  AMOUNT_SIGN||AMOUNT_IN_WORDS COMPLETE_AMOUNT_IN_WORDS
FROM    (SELECT  AMOUNT
        ,   CASE WHEN SIGN_AMT is not null then SIGN_AMT||' ' END AMOUNT_SIGN
        ,   CASE WHEN DOLLAR_AMT IS NOT NULL AND CENT_AMT IS NOT NULL THEN
                DOLLAR_AMT||' AND '||CENT_AMT
                 WHEN DOLLAR_AMT IS NULL AND CENT_AMT IS NOT NULL THEN
                CENT_AMT
                 WHEN DOLLAR_AMT IS NOT NULL AND CENT_AMT IS NULL THEN
                DOLLAR_AMT
            END AMOUNT_IN_WORDS
    FROM    (select  AMOUNT
                ,   CASE WHEN INSTR(AMOUNT, '-') > 0 THEN
                        'NEGATIVE'
                    END SIGN_AMT
                ,   CASE WHEN ABS(amount) >= 1 THEN -- ONLY THE ONES WITH WHOLE NUMBERS
                        to_char( to_date(TRUNC(ABS(amount)), 'J'), 'JSP') || ' BAHT' 
                    END DOLLAR_AMT
                ,   CASE WHEN INSTR(AMOUNT, '.') > 0 THEN -- ONLY THE ONES WITH DECIMALS
                        TO_CHAR(to_date(RPAD(SUBSTR(AMOUNT, INSTR(AMOUNT, '.') + 1), 2, '0'), 'J'), 'JSP') || ' SATANG'
                    END CENT_AMT
            from    (select 30 amount from dual
                    union 
                    select 30.500 amount from dual
                    union 
                    select 0.501 amount from dual
                    union
                    select -30 amount from dual
                    union 
                    select -30.500 amount from dual
                    union 
                    select -0.501 amount from dual
                    )
                )
            );

This sample above shows six scenarios:
  1. Positive Whole Numbers only
  2. Positive Whole Numbers with Decimals
  3. Positive Decimals Only
  4. Negative Whole Numbers only
  5. Negative Whole Numbers with Decimals
  6. Negative Decimals Only
Just change the Currencies if needed.

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/
Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)
- 1 comment: