Skip Headers

Oracle Reports Building Reports 10g (9.0.4) Part Number B10602-01 |
Home | Contents | Index

# 30
Building a Check Printing Report with Spelled-Out Cash Amounts

#### Figure 30-1 Check printing report output

Text description of the illustration orbrspellcash_fin.gif

In this chapter, you will build a check printing report with a stub and spelled-out cash amounts. The steps described in this chapter will help you create a PL/SQL function that returns spelled-out numerical values. You will also learn how to import an image of a pre-printed form (in this case, a blank check image) and use the tools in the Paper Layout and Paper Design views to print your report on such a form. Although we use a check as the example in this report, you can use the steps to use any pre-printed form with Oracle Reports.

Note: Many of the same concepts are used in the following three 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 31, "Building a Report Using a Pre-Printed Form", where you learn formatting techniques for printing reports on pre-printed 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 32, "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.

#### Concepts

- To spell out the value of a check, you will 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.

##### 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'll 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

This chapter will show you how to 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 will format the dollar amounts on your checks.

You will use the tools in the Paper Layout view to import a blank check image, which is provided to you in the example report directory. You will then 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.

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.

##### Table 30-1 Features demonstrated in this example

Feature | Location
--- | ---
Use the Report Wizard to create a simple form report. | Section 30.2, "Create a report using the Report Wizard"
Use the PL/SQL Editor to create a PL/SQL function and the Data Model view to create a formula column. | Section 30.3, "Create a formula column that returns the spelled-out cash amounts"
Use the Data Model view to create a second query and link the two queries together. | Section 30.4, "Create a query that will return the items in the order"
Use 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. | Section 30.5, "Import a check image and arrange fields for printing"
Use the Paper Design view to create a check stub and the Report Block Wizard to create a simple tabular report that displays order details. | Section 30.6, "Create a check stub with payment information and order details"

## 30.1 Prerequisites for this example

To build the example in this chapter, you must have access to the Order Entry schema, which is provided by default with the Oracle9i database. If you don't know if you have access to the sample schema provided with the Oracle9i database, contact your database administrator.

## 30.2 Create a report using the Report Wizard

The steps in this section will show you how to use the Report Wizard to create a simple report definition.

#### To create a report definition:

1. Launch Reports Builder (or, if already open, choose File > New > Report)

2. In the Welcome or New Report dialog box, select Use the Report Wizard, then click OK.

3. If the Welcome page displays, click Next.

4. On the Report Type page, select Create Paper Layout Only, then click Next.

5. On the Style page, type a Title for your report, select Form, then click Next.

6. On the Data Source page, click SQL Query, then click Next.

7. On the Data page, enter the following SELECT statement in the Data Source definition field:

       SELECT ALL ORDERS.ORDER_ID, TO_CHAR(ORDERS.ORDER_DATE, 'DD-MON-YYYY')
       ORDER_DATE, ORDERS.CUSTOMER_ID, ORDERS.ORDER_TOTAL,
       CUSTOMERS.CUST_FIRST_NAME || ' ' || CUSTOMERS.CUST_LAST_NAME CUSTOMER_NAME,
       CUSTOMERS.CUST_ADDRESS, (ROWNUM + 1000) AS CHECK_NO
       FROM ORDERS, CUSTOMERS
       WHERE CUSTOMERS.CUSTOMER_ID = ORDERS.CUSTOMER_ID
       ORDER BY ORDERS.ORDER_ID ASC

   Note: You can enter this query in any of the following ways: Copy and paste the code from the provided text file called `spellcash_code.txt` into the Data Source definition field. Click Query Builder to build the query without entering any code manually. Type the code in the Data Source definition field.

1. Click Next.

   Note: If you are not already connected to a database, you will be prompted to connect to the database when you click Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 30.1, "Prerequisites for this example" describes the sample schema requirements for this example.

1. On the Fields page, click the double right arrows (>>) to move all of the fields to the Displayed Fields list, then click Next.

2. On the Totals page, click Next.

3. On the Labels page, change the labels and field widths as follows, then click Next:

   ##### Table 30-2 Field description of Labels page

   Fields | Labels | Width
   --- | --- | ---
   ORDER_ID | `Order No.` | `5`
   ORDER_DATE | `Order Date` | `12`
   CUSTOMER_ID | `Customer No.` | `3`
   ORDER_TOTAL | `Order Total` | `15`
   CUSTOMER_NAME | `Customer` | `40`
   C_STREET_ADDRESS | `[blank]` | `40`
   C_POSTAL_CODE | `[blank]` | `5`
   C_CITY | `[blank]` | `20`
   C_STATE_PROVINCE | `[blank]` | `3`
   C_COUNTRY_ID | `[blank]` | `3`
   CHECK_NO | `Check No.` | `5`

1. On the Template page, select No Template, then click Finish to preview your report output in the Paper Design view.

Since you've only created the initial report definition, the formatting will not display like a check. It should look something like this:

#### Figure 30-2 Paper Design view of the simple report

Text description of the illustration orbrspellcash_init.gif

1. Save your report as `spellcash_<your initials>.rdf`.

## 30.3 Create a formula column that returns the spelled-out cash amounts

The steps in this section will show you how to create a formula column that is based on a function. The function you create will return verbal or word value for the numerical value of the check. You will then create a formula column that will spell out the numerical value in the designated currency. In this case, we will use dollars and cents.

### 30.3.1 Create a PL/SQL function

In this section, you will create a function that simply returns the check amount in word format, such as "twenty-four sixty-five." The formula column you create in the next section will use the information retrieved by this function to spell out the cash amounts on your checks.

#### To create a PL/SQL function:

1. In the Object Navigator, under your report name, double-click Program Units.

2. In the New Program Unit dialog box, type "`Spell`" in the Name box.

3. Select Function, then click OK.

4. In the PL/SQL Editor, type the following code:

       FUNCTION Spell (val number) RETURN CHAR IS
         sp varchar2(100);
       BEGIN
         if val > 0 then
         	 return(initcap(to_char(to_date(val, 'SSSSS'), 'SSSSSSP')));
         else
         	 return('');
         end if;
       END;

   Note: You can enter this code by copying and pasting it from the provided text file called `spellcash_code.txt` into the PL/SQL Editor.

1. Click Compile. If you see any errors, compare your code against the code we've provided, which is shown in the image below:

#### Figure 30-3 PL/SQL Editor displaying the SPELL function

Text description of the illustration orbrspellcash_pue.gif

1. When your code successfully compiles, click Close.

Your new function now displays in the Object Navigator:

#### Figure 30-4 Object Navigator with SPELL PL/SQL function

Text description of the illustration orbrspellcash_on.gif

1. Save your report.

### 30.3.2 Create a formula column in your data model

In this section, you will create a formula column that uses the information retrieved by the Spell function you created in Section 30.3.1, "Create a PL/SQL function". This formula column will use the verbal values of the check amounts and combine the words with the correct currency. For example, the "twenty-four sixty-five" returned by the Spell function will be turned into "twenty-four dollars and sixty-five cents".

#### To create a formula column:

1. In the Object Navigator, under your report name, double-click the view icon next to the Data Model node to display the Data Model view.

   Your data model should look like this:

#### Figure 30-5 Data Model view

Text description of the illustration orbrspellcash_dm.gif

1. Resize the G_ORDER_ID box by clicking at the top, then dragging the bottom center resize handle downwards. Your data model should now look something like this:

#### Figure 30-6 Resized Data Model view

Text description of the illustration orbrspellcash_dm1.gif

1. Click the Formula Column tool in the tool palette.

2. Click in the G_ORDER_ID group, in the space you just created, to create a new formula column.

3. Double-click the new formula column object (CF_1) to display the Property Inspector, and set properties:

   - Under General Information, set the Name property to SPELLED_AMOUNT.

   - Under Column, set the Datatype property to CHARACTER, and the Width property to 100.

   - Under Placeholder/Formula, click the PL/SQL Formula property field to display the PL/SQL Editor.

4. In the PL/SQL Editor, use the template to enter the following PL/SQL code:

        function SPELLED_AMOUNTFormula return Char is
       cents number;
       c_str varchar2(80);
       val number;
       begin
         val := :order_total;
         cents := (val mod 1) * 100;
         if cents > 0 then --creates string for cents
         c_str := ' and ' || TO_CHAR(cents) || '/100 Dollars******';
          else
         c_str := ' Dollars******';
         end if;
         if val < 1000 and val > 1 then
         return (initcap(spell(floor(val))) || c_str);
         elsif val > 1000 then
          return(initcap(spell(floor(val/1000))) || ' Thousand ' ||
          spell(floor(val mod 1000)) || c_str);
         else
          return('Zero'||c_str);
         end if;
       end;

   Note: You can enter this code by copying and pasting it from the provided text file called `spellcash_code.txt` into the PL/SQL Editor.

1. Click Compile. If you see any errors, compare your code against the code we've provided, which is shown in the image below:

#### Figure 30-7 PL/SQL Code for SPELLED_AMOUNT formula column

Text description of the illustration orbrspellcash_fc.gif

1. When your code successfully compiles, click Close.

Your new formula column, called SPELLED_AMOUNT, now displays in the data model.

#### Figure 30-8 Data Model with SPELLED_AMOUNTS formula column

Text description of the illustration orbrspellcash_fcdm.gif

1. Save your report.

## 30.4 Create a query that will return the items in the order

The steps in this section will show you how to manually create a query in the Data Model view that will return the items in the customer's order. This data retrieved will be used to display the order details in the check stub.

#### To manually create a query:

1. In the Data Model view, click the SQL Query tool in the tool palette.

2. Click an open area in the Data Model view to display the SQL Query Statement dialog box.

3. In the SQL Query Statement field, type the following code:

       SELECT ALL
           ORDER_ITEMS.LINE_ITEM_ID,
           ORDER_ITEMS.ORDER_ID,
           ORDER_ITEMS.PRODUCT_ID,
           ORDER_ITEMS.UNIT_PRICE,
           ORDER_ITEMS.QUANTITY,
           PRODUCT_INFORMATION.PRODUCT_NAME
       FROM ORDER_ITEMS, PRODUCT_INFORMATION
       WHERE PRODUCT_INFORMATION.PRODUCT_ID=ORDER_ITEMS.PRODUCT_ID
       ORDER BY ORDER_ITEMS.LINE_ITEM_ID ASC

   Note: You can enter this query in any of the following ways: Copy and paste the code from the provided text file called `spellcash_code.txt` into the SQL Query Statement field. Click Query Builder to build the query without entering any code manually. Type the code in the SQL Query Statement field.

1. Click OK. Your query displays in the Data Model view, and should look something like this:

#### Figure 30-9 Data Model with two queries

Text description of the illustration orbrspellcash_dmq2.gif

1. In the Data Model view, click the Data Link tool in the tool palette.

2. In the first query, Q_1, under G_ORDER_ID, click ORDER_ID, then drag the line to ORDER_ID1 in Q_2.

3. Your data model should now look like this:

#### Figure 30-10 Data Link between two queries

Text description of the illustration orbrspellcash_dmdl.gif

1. Save your report.

## 30.5 Import a check image and arrange fields for printing

The steps in this section will show you how to adjust the margins of your check printing report and align the fields with an image of a check. You can scan any check and use its image to lay out the objects of your check report. In this section, we use the image we've provided to you, called `blankcheck.jpg`. This image is located in the `spellcash` example folder.

### 30.5.1 Rearrange the layout objects

Before you can insert the check image, you must first rearrange the layout objects.

#### To rearrange the layout objects:

1. Click the Paper Layout button in the toolbar to display the Paper Layout view.

   The layout currently looks like this:

#### Figure 30-11 Paper Layout view of your report

Text description of the illustration orbrspellcash_lay.gif

1. Click the Edit Margin button in the toolbar.

2. In the Paper Layout view, click the margin frame (the heavy black line) to select it.

3. At the top of the frame, click the center black resizing square and drag it up, so that the margin is 0.25 inches.

   Tip: When you click the center square, notice that a dotted guideline displays while your mouse button is depressed. You can use these guidelines to help place objects exactly where you want them in the Paper Layout view.

When you're done, it should look something like this:

#### Figure 30-12 Paper Layout view with resized margin

Text description of the illustration orbrspellcash_lay1.gif

1. Save your report.

### 30.5.2 Import the blank check image

In this section, you will import an image of a blank check. You can use any check you'd like. For this example, we've provided an image called `blankcheck.jpg` in the spellcash example directory.

#### To import the blank check image:

1. While the frame is still selected, choose Insert > Image.

2. In the Import Image dialog box, make sure that File is selected.

3. In the text box next to File, type or browse for the location of the image, `blankcheck.jpg`, then click OK to display the blank check image in the Paper Layout view.

4. Click the Edit Margin tool in the toolbar to return to edit mode. Notice that the image no longer displays.

### 30.5.3 Set up the check printing fields

The steps in this section will show you how to use the various tools in the Paper Layout view to modify the look and feel of your check report. Here, you will learn how to add and modify layout objects and fields according to how you want them to display on the resulting checks. You will create copies of certain fields that you will then use in Section 30.6, "Create a check stub with payment information and order details" to create the check stub.

#### To set up the check printing fields:

1. In the Paper Layout view, click the Order No. boilerplate text.

2. Click the Select Parent Frame button in the toolbar

   The repeating frame called R_G_ORDER_ID should now be selected in the Paper Layout view. You can also look in the Object Navigator, under Paper Layout, to make sure R_G_ORDER_ID is selected.

3. While R_G_ORDER_ID is selected, click and drag the bottom center resize handle to 8.75 inches (the bottom of the page).

   Tip: When you click and drag a resize handle in the Paper Layout view, guidelines display along the ruler to help you place your objects. In this case, drag the bottom center resize handle down the page until you the horizontal guideline reaches 8.75.

1. Click the Flex Off button in the toolbar to set Flex mode off.

2. In the Paper Layout view, click F_ORDER_TOTAL, then press CTRL+C on your keyboard.

3. Click in the area below your layout objects and press CTRL+V.

   You should see a new field called F_ORDER_TOTAL1. If this field displays on top of the other layout objects, click and drag it down below the other layout objects so that your Paper Layout view now looks something like this:

#### Figure 30-13 Partial Paper Layout view with F_ORDER_TOTAL1

Text description of the illustration orbrspellcash_ptlay.gif

1. Copy the F_ORDER_DATE field and paste it below the other fields, so that your layout now looks like this:

#### Figure 30-14 Partial Paper Layout view with F_ORDER_DATE1

Text description of the illustration orbrspellcash_ptlay1.gif

Note: When you copy and paste a field, Reports Builder maintains the size of the field. So, when you copy and paste F_ORDER_DATE, you may not see the full name of the field. While the field is selected, you can click and drag the right border of the field to the right so that you can see the full name of the field, F_ORDER_DATE1.

1. Copy F_CUSTOMER_NAME and paste the field below the other layout objects, so that your layout looks like this:

#### Figure 30-15 Partial Paper Layout view with F_CUSTOMER_NAME1

Text description of the illustration orbrspellcash_ptlay2.gif

1. Make two copies of F_CHECK_NO by pressing CTRL+C on your keyboard once, then CTRL+V twice.

   Note: Since this field is small, it may be difficult to find. You can use the Object Navigator to find and select the field. When you click a field in the Object Navigator, under Paper Layout, the corresponding field is also selected in the Paper Layout view.

1. Position these two new fields below the other layout objects, so that your layout looks like this:

#### Figure 30-16 Partial Paper Layout view with F_CHECK_NO1 and F_CHECK_NO2

Text description of the illustration orbrspellcash_ptlay3.gif

Note: We've expanded the size of the two new fields (F_CHECK_NO1 and F_CHECK_NO2) to make them easier to see, but you do not need to resize these fields:

1. Click the Field tool in the tool palette.

2. Draw a field below the other layout objects about 5 inches long, so that your layout now looks like this:

#### Figure 30-17 Partial Paper Layout view with F_1 field

Text description of the illustration orbrspellcash_ptlay4.gif

1. While the new field, F_1, is still selected, click the Fill Color tool in the tool palette, and choose No Fill.

2. Click the Line Color tool in the tool palette, and choose No Line.

3. Click the field F_1, then press F4 on your keyboard to display the Property Inspector.

4. Set the Name property to F_SPELLED_AMOUNT.

5. Under Field, set the Source property to SPELLED_AMOUNT (the formula column you created in Section 30.3.2, "Create a formula column in your data model").

6. Save your report.

### 30.5.4 Rearrange the new fields according to the blank check image

The steps in this section will show you how to use the Paper Design view to rearrange the fields you just created. You will use the blank check image we've provided as a guide.

1. Click the Paper Design button in the toolbar to display your report in the Paper Design view.

   Note: When you click the Paper Design button while you are in another view (i.e., Paper Layout view or Data Model view), Reports Builder runs your query and your layout. If you receive any error messages when you click the Paper Design button, go back to your original view to verify your changes. You can always compare your report against the sample report we've provided, called `spellcash.rdf`.

   You will see your layout objects, as well as the blank check image:

#### Figure 30-18 Partial Paper Design view of the check report

Text description of the illustration orbrspellcash_prev.gif

Note: You'll see that the preview doesn't quite look like a check report. The steps in this section will show you how to move your fields so that your report looks like a proper check.

1. In the Paper Design view, position the F_ORDER_TOTAL field in the "Amount" box in the blank check image.

   Note: If you closed Reports Builder for any reason and are returning to building this report, make sure you click the Flex Off button in the toolbar so that you can move the fields around in the Paper Design view.

1. Click the Align Right button in the toolbar, so that this portion of the check looks like the following:

#### Figure 30-19 Amount area on blank check

Text description of the illustration orbrspellcash_amt.gif

1. Position the F_CHECK_NO field in the "Check No." box on the blank check, and center-align the text.

2. Position the F_ORDER_DATE field in the "Date" box on the blank check, and center-align the text.

3. Position the F_CHECK_NO2 field in the upper right-hand corner of the check image, and right-align the text.

4. Position the F_SPELLED_AMOUNT field next to the "Pay Exactly:" text on the blank check image.

5. Position the F_CUSTOMER_NAME field next to the "Pay To The Order Of:" text on the blank check image.

   The check should now look like this:

#### Figure 30-20 Check image with fields positioned

Text description of the illustration orbrspellcash_chk.gif

1. Save your report.

### 30.5.5 Modify the look and feel of the check

The steps in this section will show you how to change the font, alignment, and formatting of the data on your check.

#### To modify the look and feel of the check:

1. In the Paper Design view, choose Edit > Select All (or press CTRL+A).

2. Choose Format > Font.

3. In the Font dialog box, choose Arial font, Regular style, Size 10, then click OK.

4. In the Paper Design view, click anywhere to deselect all the objects.

   Tip: If you have trouble deselecting the objects, go to the Object Navigator and click on any item. The objects that were selected in the Object Navigator should display as deselected.

1. In the Paper Design view, click F_CHECK_NO2 (the check number in the upper right-hand corner of the check) and change the font to Arial, Bold, 12 point.

2. Click the F_ORDER_TOTAL field (the number in the Amount box on the check).

3. Click the Currency button in the toolbar, then click the Commas button.

4. While F_ORDER_TOTAL is still selected, click the Add Decimal Place button in the toolbar twice, so that the amount now displays like this: `$46,257.00`.

5. Press F4 to display the Property Inspector for F_ORDER_TOTAL.

6. Under Field, next to the Format Mask property, add to the beginning of the existing format mask "`****`", then close the Property Inspector.

   The amount should now display like this: `****$46,257.00`.

7. Select the F_CUSTOMER_NAME and F_SPELLED_AMOUNT fields by Shift-clicking them, then change the font to Times New Roman, 12 point.

   Your report should now look something like this:

#### Figure 30-21 Formatted Check

Text description of the illustration orbrspellcash_ckfm1.gif

Note: You'll notice that the other boilerplate text is still overlaying the image. We will rearrange this text later in the chapter.

1. Save your report.

## 30.6 Create a check stub with payment information and order details

When you create a check printing report, you will sometimes want to create a check "stub" that contains the payment information on the check, as well as the details of the order. The steps in this section will show you how to create a check stub that contains the payee, amount, date, check number, and other payment information.

You will then use the information retrieved by the second query in your data model to print a simple tabular report on the check stub that displays the order information associated with the check.

### 30.6.1 Create a check stub in the Paper Design view

The steps in this section will show you how to use the tools in the Paper Design view to create a check stub for your check report. You will rearrange the fields you created in the previous section so that every time you run the report, a corresponding check stub will display for every check.

You will also create guidelines in the Paper Design view to help you align the check stub information with the data in the check report.

#### To create a check stub:

1. Click the Flex Off button in the toolbar.

2. Click in the left, vertical ruler and drag your mouse to the right, so that the line is flush with the text "Pay Exactly."

3. When you release your mouse button, you'll notice that a guideline displays, like the one in the following image:

#### Figure 30-22 Partial Paper Design view of the check report with horizontal guideline

Text description of the illustration orbrspellcash_gl.gif

Note: The guideline is a broken black line spanning across this image, to the left of the "P" in "Pay Exactly."

1. Click the Order No. boilerplate object (located in the upper left-hand corner of the check) and position it along the new guideline, and below the check image at 3.25 inches, like this:

#### Figure 30-23 Partial Paper Design view of the check with Order No. boilerplate text

Text description of the illustration orbrspellcash_bp.gif

1. Click the F_ORDER_ID field (in the above image, it is the field with "2354") and position it next to the Order No. field, like this:

#### Figure 30-24 Order No. and F_ORDER_ID fields in the Paper Design view

Text description of the illustration orbrspellcash_bp1.gif

Note: You can always select your fields in the Object Navigator, under Paper Layout > Main Section > Body, and these fields will appear selected in the Paper Design view.

1. Now, click the Order Date boilerplate text and position it directly below "Order No.", like this:

#### Figure 30-25 Order Date field in the Paper Design view

Text description of the illustration orbrspellcash_bp2.gif

1. Click F_ORDER_DATE1 (the copy you made of the F_ORDER_DATE field), which you can locate using the Object Navigator, and position the field next to the Order Date boilerplate text, like this:

#### Figure 30-26 F_ORDER_DATE1 field next to Order Date text

Text description of the illustration orbrspellcash_bp3.gif

1. Position the Order Total boilerplate text directly below the Order Date boilerplate text.

2. Position the F_ORDER_TOTAL1 field next to the Order Total boilerplate text.

3. Position the Check No. boilerplate text directly below the Order Total boilerplate text.

4. Position F_CHECK_NO1 next to the Check No. boilerplate text.

   The check stub should now look like this:

#### Figure 30-27 Partial check stub in Paper Design view

Text description of the illustration orbrspellcash_bp4.gif

1. Create another vertical guideline that is aligned with the box containing the data, amount, and check number. The guideline should appear here:

#### Figure 30-28 Second vertical guideline in the Paper Design view

Text description of the illustration orbrspellcash_gl1.gif

Note: In this image, the second vertical guideline is at the 4.25 inch mark on the top ruler. To create this guideline, click in the left-hand ruler, then drag your mouse until the line is aligned with the 4.25 inch mark.

1. Click the Customer boilerplate text, and position it so that it is vertically aligned with the second guideline, and horizontally aligned with the Order No. text (at 3.25 inches), like this:

#### Figure 30-29 Customer field in the Paper Design view

Text description of the illustration orbrspellcash_bp5.gif

1. Position the F_CUSTOMER_NAME1 field next to the Customer boilerplate text so that these layout objects look like this:

#### Figure 30-30 Customer and F_CUSTOMER_NAME1 fields

Text description of the illustration orbrspellcash_bp6.gif

Tip: If you have difficulty fitting the F_CUSTOMER_NAME1 field into the provided space, you may need to resize the field. To do so, select the field, then drag the right middle resize handle to the left, making sure that there is still enough space for the customer name (Harrison Sutherland).

1. Position the F_C_STREET_ADDRESS field directly below F_CUSTOMER_NAME1.

2. Position F_C_CITY directly below F_C_STREET_ADDRESS.

3. Position the F_C_STATE_PROVINCE field next to F_C_CITY.

4. Position the F_C_POSTAL_CODE field next to F_C_STATE_PROVINCE.

5. Position the F_C_COUNTRY_ID field next to F_C_POSTAL_CODE.

6. Position the F_CUSTOMER_ID field directly below the F_C_CITY field.

7. Position the Customer No. boilerplate text (B_CUSTOMER_ID) directly to the left of the F_CUSTOMER_ID field, so that this portion of the check stub should now look like this:

#### Figure 30-31 Partial check stub in the Paper Design view

Text description of the illustration orbrspellcash_cs.gif

1. Now, let's format the information you've just added. Select the following boilerplate text objects in the check stub:

   - Order No.

   - Order Date

   - Order Total

   - Check No.

   - Customer

   - Customer No.

2. Change the font to Bold, then right-align the text.

3. Click F_ORDER_TOTAL1, then click the Currency button and the Commas button in the toolbar.

4. Click the Add Decimals button in the toolbar twice.

   Your check stub should now look like this:

#### Figure 30-32 Formatted check stub

Text description of the illustration orbrspellcash_stfm.gif

1. Save your report.

### 30.6.2 Add order details to the check stub

In the previous section, you created a check stub that displays the payment information, that is a copy of the information on the check itself. The steps in this section will show you how to add the details of the order to the check stub by creating a simple tabular report. You will use the Report Block Wizard to create the simple tabular report, then format the data in the Paper Design view.

#### To add order details:

1. In the Paper Layout view, click the Report Block tool in the tool palette.

2. Drag a rectangular area for the new layout to display the Report Block Wizard.

3. In the Report Block Wizard, on the Style page, select Tabular, then click Next.

4. On the Groups page, click G_LINE_ITEM_ID and click Down in the Available Groups list to specify the Print Direction and move this group to the Displayed Groups list, then click Next.

5. On the Fields page, click each of the following fields in the Available Fields list, then click the right arrow (>) to move them to the Displayed Fields list, then click Next:

   - LINE_ITEM_ID

   - PRODUCT_ID

   - PRODUCT_NAME

   - UNIT_PRICE

   - QUANTITY

6. On the Labels page, change the labels and field widths as follows, then click Next:

   Fields | Labels | Width
   --- | --- | ---
   LINE_ITEM_ID | `Line` | `2`
   PRODUCT_ID | `Product ID` | `4`
   QUANTITY | Quantity | 4
   PRODUCT_NAME | Product Name | 40
   UNIT_PRICE | Unit Price | 4

7. On the Template page, select No template, then click Finish to display your report layout in the Paper Layout view, with the new fields and layout objects displaying below the original layout objects.

8. In the Paper Layout view, make sure the new fields and layout objects are located within the R_G_ORDER_ID repeating frame, and that all the layout objects fit on a single page (within 8.75 inches).

9. Click the Paper Design button in the toolbar to run and display your report in the Paper Design view. It should look something like this:

#### Figure 30-33 Check printing report with non-formatted order details

Text description of the illustration orbrspellcash_ckod.gif

Since the new report block is not formatted, let's format the data to look like the rest of the report.

1. Shift-click the following new labels, then click the Bold button in the toolbar and change the font to Arial, 10 point:

   - Line

   - Product ID

   - Product Name

   - Unit Price

   - Quantity

2. Click the data under Line (F_LINE_ITEM_ID), then click the Align Center button in the toolbar.

3. Click the data under Unit Price (F_UNIT_PRICE), then click the Align Right button in the toolbar.

4. While the data is still selected, click the Currency button, Commas button, then click the Add Decimal button twice in the toolbar.

5. Click on the right resize handle and align the right edge of the data field with the Unit Price label.

6. Click the data under Quantity (F_QUANTITY), then click the Align Right button in the toolbar, then resize the field so that the right edge of the data field is aligned with the Quantity label.

7. Save your report.

Your report should now look like this:

#### Figure 30-34 Final check report with spelled-out cash amounts and stub

Text description of the illustration orbrspellcash_fin.gif

Note: You can also compare your results with the example report we've provided, called `spellcash.rdf`.

## 30.7 Summary

Congratulations! You have successfully built a report with spelled-out cash amounts on the check, as well as the payment and order details on the check stub. You now know how to:

- use the Report Wizard to create a simple report definition.

- use the tools in the Paper Layout view to import a blank check image and add fields to the report.

- create a PL/SQL function that returns spelled-out numbers.

- create a formula column based on a PL/SQL function.

- use the tools in the Data Model view to link two queries.

- use the tools in the Paper Design view to format your check printing report to print on a desired check.

- create a check stub that displays a duplicate of the information on the check.

- use the Report Block Wizard to create a simple tabular report that displays order details on a check stub.

For more information on any of the wizards, views, or properties used in this example, refer to the Reports Builder Online Help, which you can access in two ways:

- From the Oracle Technology Network (`http://otn.oracle.com/products/reports/`), click Documentation and navigate to the Reports Builder Online Help for the most recent, hosted online help.

- From Reports Builder, choose Help > Help Contents.

| Copyright © 2002, 2003 Oracle Corporation. All Rights Reserved. |
Home | Contents | Index