31 Building a Check Printing Report with Spelled-Out Cash Amounts

In this chapter, you will learn about reports that format output as checks with cash amounts spelled out in words. By following the steps in this chapter, you can generate the report output shown in Figure 31-1.

Figure 31-1 Check printing report output

Description of "Figure 31-1 Check printing report output"

Concepts

To spell out the value of a check, you use a PL/SQL function to split the number into its constituent numerals, then use a formula column to combine the words into the spelled-out cash amount.

Note:

Many of the concepts in this example are used in the following example reports:

This example, where you import an image of a check and use it as a guide to position fields in the Paper Layout view. In addition, you learn how to create a PL/SQL function that returns spelled-out numerical values.
Chapter 32, "Building a Report Using a Preprinted Form", where you learn formatting techniques for printing reports on preprinted forms when you do not have access to a computer readable version of the forms. Such reports must be designed so that the data prints in exact positions on the form.
Chapter 33, "Building an Invoice Report", where you import an image of an invoice and use it as a guide to position fields in the Paper Layout view.

Data Relationships

This report uses two linked queries. The first query retrieves the information necessary for the check, and the second query retrieves the order details that will be printed on the check stub.
One of the queries you will create assumes that the numbers to be spelled are stored in the TOTAL column of the ORDERS table. The text of the spelled-out number will be fetched from the table called Lookup, and returned in the Thousands_text, Thousands_Symbol, Ones_text, and Decimal_Text columns

Layout

This report uses a default Form style.

Example Scenario

In this example, you will use the Report Wizard to create your basic report definition. You will create a PL/SQL function that spells out the numerical value of the check amount. You will also create a formula column that formats the dollar amounts on your checks. You will import an image of a blank check image, and use the tools in the Paper Layout and Paper Design views to rearrange the fields in your report according to how you want them to display on the resulting check printing report. You will also use these tools to create a stub for every check.

As you build this example report, you will:

Create a Report Using the Report Wizard to create a simple form report.
Create a Formula Column That returns the Spelled-Out Cash Amounts using the PL/SQL Editor and the Data Model view.
Create a Query That Returns the Items in the Order using the Data Model view to create a second query and link the two queries together.
Import a Check Image and Arrange Fields for Printing using the Paper Layout view to import a blank check image and add fields necessary for printing the check and check stub. Use the Paper Design view to arrange the new fields on the blank check image.
Create a Check Stub With Payment Information and Order Details using the Paper Design view to create a check stub and the Report Block Wizard to create a simple tabular report that displays order details.

To see a sample report where the cash amounts are spelled out, open the examples folder named spellcash, then open the Oracle Reports example named spellcash.rdf. For details on how to open it, see "Accessing the Example Reports" in the Preface.