iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Developers - REPORTS

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 |

 

Lesson 06

"For centuries, theologians have been explaining the unknowable in terms of the-not-worth-knowing." - Henry Louis Mencken (1880-1956)

 

Read first then play the video:

   RPT-VIDEO -(Hands-On 05) How to write a Form Letter

   

Form Letter Report

Introduction

The Form letter report will complement the mailing label report. You can pull several database columns plus your mailing message to produce form mailings.

 

Hand-on

Your client needs to write a letter to all its customers who own Oracle shares for good news of split of their shares. You are asked to write a “Form Letter” to send to each customer who own Oracle (orcl) shares. You are supposed to notify them of a stock split and tell them the number of shares the broker hold for them, their size of the split and the post-split amount and their current stock market values.

See Figure 5.

 

Your tasks are:

1- Create a “Form Letter” report.

2- Extract customer’s information from their database.

3- Generate the report only for Oracle’s share holders.

4- Apply template to the user report.

5- Run the report.

6- Test the repot.

 

FYou will learn how to: use the report style “form letter,” and the “query builder,” set table relationships, define column from the “query builder,” use sub-query, use of data model, layout model, use of boilerplate, write the letter and pass the column to the letter.

 

Figure 5

 

 

Create a new report

In the Object Navigator, highlight the Reports item, and click on the "create" icon (Green ‘+’) to create a new report.

 

Use the Report Wizard

In the New Report window, choose the ‘Use the Report Wizard’ option, and click ‘OK.’

 

Choose a report style

In the Report Wizard, choose the Form Letter style and click Next.

 

Form Letter

The Form letter report will complement the mailing label report. You can pull several database columns plus your mailing message to produce form mailings.

 

SQL Query Statement

In the SQL Query Statement, write a SQL statement to query customers’ information (Name and address), and their portfolio information (Number of shares, double_shares_owned, and current stock market value).

(SQL Query Statement)

SELECT ALL first_name, last_name,

address_line_1, address_line_2, address_line_3,

Shares_owned, shares_owned * 2 as double_shares_owned,

(shares_owned * 2) * current_price as stock_market_value

FROM customers, portfolio, stocks

WHERE (( id = customer_id)

and ( stock_symbol = symbol))

and trade_date = ( SELECT max(trade_date)

FROM stocks

Then click Next.

 

Create a Form Letter

Create your form letter by selecting fields and by typing directly into the Form Letter text.

(Form Letter Text)

&<FIRST_NAME> &<LAST_NAME>

&<ADDRESS_LINE_1>

&<ADDRESS_LINE_2>

&<ADDRESS_LINE_3>

Dear &< FIRST_NAME>,

Please ….

&<SHARES_OWNED>

&<DOUBLE_SHARES_OWNED>

&<STOCK_MARKET_VALUE>

We selected all the columns that will be used in the letter.

 

In the Report Wizard, select the template of your choice and click on “FINISH.” In this hands-on, use the default template.

 

Data Model

In the Live Pre-viewer, click on the “Data Model” icon. Right click on the “Q_1” and open its property palette and change its name to Q_CUSTOMER.

 

Report Editor

In the Data Model, click on the Report Editor icon to open the “Report Editor.”

Make the Object Navigator and the Report Editor side by side.

 

Resizing a object

In the “Object Navigator,” expand "Layout Model," “Main Section,” “Body,” and select the repeating group (R_G_FIRST_NAME).

 

The frame item can be selected either by using the “Object Navigator” or the “Layout Model” in the report editor.

 

Drag the frame to a size of letter page.

Click on the text boilerplate in the Body item. Notice the handlers; drag them to the "repeating group" frame.

 

Write a letter

In the Report Editor, write the company letter.

(Letter)

&<FIRST_NAME> &<LAST_NAME>

&<ADDRESS_LINE_1>

&<ADDRESS_LINE_2>

&<ADDRESS_LINE_3>

Dear &< FIRST_NAME>,

Please be advised that the Oracle Company (ORCL) has announced a stock split for shareholders of record.

The split will be 2 for 1.

Our records indicate that you now hold &<SHARES_OWNED> after the split that amount will be &<DOUBLE_SHARES_OWNED>. Please contact us if this amount does not agree with your record.

Your stock’s market value as of today is: &<STOCK_MARKET_VALUE>

Thank you,

Customer Service.

 

Run the report.

Navigate through the report.

 

You can also change the text on the “live pre-viewer.”

Notice that the number of shares got double.

 

Put a dollar sign ($) next to the stock market values.

 

Navigate through all the Oracle's stock's holders.

After testing the output report, highlight the report and save it as report number 5 in the iself folder (REPT05).

 

“Education's purpose is to replace an empty mind with an open one.” Malcolm Forbes (1919 - 1990), in Forbes Magazine

 

Questions:

Q: What is a Form Letter report?

Q: When do you use the query builder tool?

Q: How do you set a table relationship in the Layout Module?

Q: How do you define a column from the Query Builder?

Q: How do you use the Data Model tool?

Q: How do you use the Layout Model tool?

Q: What is the boilerplate in the Layout Editor?

Q: Your client needs to write a letter to all its customers who own Oracle shares for good news of split of their shares. You are asked to write a “Form Letter” to send to each customer who own Oracle (orcl) shares. You are supposed to notify them of a stock split and tell them the number of shares the broker hold for them, their size of the split and the post-split amount and their current stock market values.

See Figure 5.

 

Your tasks are:

1- Create a “Form Letter” report.

2- Extract customer’s information from their database.

3- Generate the report only for Oracle’s share holders.

4- Apply template to the user report.

5- Run the report.

6- Test the repot.