Page 549
FIG. 20.13
The Security Form
menu option command
used to validate
security.
The goal of this installment is to develop several reports by using SQL*Reports. These reports will be linked to the menu created in installment 4. This will allow the operator to launch the form from the system's menu. The first report (empewp1.rdf) is a listing of employees who need an appraisal performed within the next 30 days. The query for this report identifies the employees who have not had an appraisal in more than 335 days. Figure 20.14 shows the query used to retrieve the records for the report.
After the report is properly formatted and an executable file generated, a menu option to launch the report is placed on the Employee system menu. The command to launch the report is shown in Figure 20.15. This command launches Reports Runtime and passes it the name of the report file, database logon information, and disables the default parameter list displayed before the report is executed. This command initiates the report immediately. The report will appear in the Reports Previewer upon completion.
Page 550
FIG. 20.14
The query used to
identify employees that
require an appraisal.
FIG. 20.15
The command to launch
the Appraisal Due report
from the Employee
System menu.
The second system report compiles a list of employees who require a tool payroll deduction for the specified pay date. The report is initiated from a menu option on the system's menu. Before the report is executed, a parameter form is displayed that requests the operator to specify a pay date. This date is used to evaluate dates in the query. The query searches for employees who
Page 551
have purchased a tool on payroll deduction. The specified pay date must be greater- than-or-equal-to the value in the first_payment_date and less-than-or-equal to the last_payment_date to be included on the list. If the pay date is equal to the last_payment_date, the payroll deduction amount is equal to the last_payment_amount rather than the payment. Figure 20.16 shows the query that will produce this report.
FIG. 20.16
The query to produce
the Payroll Deduction
for Tools report.
The command to execute the report is displayed in Figure 20.17. Because this report requires a parameter list to be displayed, the paramlist option is yes.
FIG. 20.17
The command to
launch the Payroll
Deduction report.
Page 552
Figure 20.18 shows the two report options as they appear to the operator on menu.
FIG. 20.18
The report options
as they appear on the
system menu.
The purpose of this chapter is to offer you an approach to developing an actual application by using Oracle Developer 2000 on the front end and the Oracle database on the back end. The book presented a variety of tasks and tools used in one manner or another. This chapter is an attempt to place the tools and tasks in their logical order and show how the tools are used to develop a system.
This chapter is also an attempt to give you a place to answer your development questions. When I have a problem with a system, I generally ask myself whether I had performed the procedure before. If I have, I look at the code and try to emulate it. Sometimes when I try to use commands and procedures described in the Help section or a reference manual I have problems. It is always nice to be able to look at a working application. This chapter is an attempt to give you a working application that can be used by you to solve development products.
Finally, I find Oracle an excellent product for developing systems. I hope that you find Oracle a good product also, and that this book helps in your development work.l
Page 553
A Built-In Subprograms555
B Triggers579
C System Variables597
D Practice Database Installation Instructions601
E Answers to Practice Problems607
Page 554