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.
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.
In the Object Navigator, under your report name, double-click Program Units.
In the New Program Unit dialog box, type Spell
in the Name box.
Select Function, then click OK.
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 calledspellcash_code.txt
into the PL/SQL Editor.Click Compile. If you see any errors, compare your code against the code shown in the image below:
Figure 31-3 PL/SQL Editor displaying the SPELL function
When your code successfully compiles, click Close.
Your new function now displays in the Object Navigator:
Figure 31-4 Object Navigator with SPELL PL/SQL function
Save your report.
In this section, you will create a formula column that uses the information retrieved by the Spell function you created in Section 31.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".
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:
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:
Click the Formula Column tool in the tool palette.
Click in the G_ORDER_ID group, in the space you just created, to create a new formula column.
Double-click the new formula column object (CF_1) to display the Property Inspector, and set the following 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.
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 calledspellcash_code.txt
into the PL/SQL Editor.Click Compile. If you see any errors, compare your code against the code we have provided.
When your code successfully compiles, click Close.
Your new formula column, called SPELLED_AMOUNT, now displays in the data model.
Figure 31-7 Data Model with SPELLED_AMOUNT formula column
Save your report.