Page 537
/* The Departments table is the parent table to Employees table. The valid departments must reside on this table before the Employee table is populated. Cursor a select the distinct department, department name combinations from the load data. These values are inserted as records in the Departments table*/ for loc in a loop insert into departments (department, department_name) values (rtrim(upper(loc.department)), upper(loc.department_name)); end loop; /* This section populates the Employee, Classifications, Tools, and Eyeglasses tables. A payroll number was needed as part of the primary key for each of these tables. Since it did not exist in the original data, the number needed to be generated. Cursor b is used to select the entire record from the Temptable table.*/ for loc1 in b loop /* The following line generates the payroll number for each employee*/ select next_payroll_number.NextVal into pay_number from dual; /* The following insert statement populates the Employees table*/ insert into employees (payroll_number, last_name, first_name, absences, wages, street, city, state, phone, social_security_number, employment_date, birth_date, classification, classification_date, fk_department) values (pay_number, upper(loc1.last_name), upper(loc1.first_name), loc1.absences, loc1.wages, upper(loc1.street), upper(loc1.city), upper(loc1.state), loc1.phone, loc1.social_security_number, upper(loc1.employment_date), upper(loc1.birth_date), upper(loc1.current_position), upper(loc1.position_date), rtrim(upper(loc1.department))); /* The following section populates the Classifications table. The original data consists of six sets of fields. The following six if-then-else statements evaluate the fields. A record will be created for each set that contains values */ if (loc1.position_date_1 is not null) then dept := rtrim(upper(loc1.department_1)); open c; fetch c into dept_name; close c; insert into classifications (fk_payroll_number, department, classification, classification_date, wages, comments, department_name) values (pay_number, rtrim(upper(loc1.department_1)), upper(loc1.historic_position_1), upper(loc1.position_date_1), loc1.wages_1, upper(loc1.comments_1), dept_name); end if; if (loc1.position_date_2 is not null) then dept := rtrim(upper(loc1.department_2));
continues
Page 538
Listing 20.14 Continued
open c; fetch c into dept_name; close c; insert into classifications (fk_payroll_number, department, classification, classification_date, wages, comments, department_name) values (pay_number, rtrim(upper(loc1.department_2)), upper(loc1.historic_position_2), upper(loc1.position_date_2), loc1.wages_2, upper(loc1.comments_2), dept_name); end if; if (loc1.position_date_3 is not null) then dept := rtrim(upper(loc1.department_3)); open c; fetch c into dept_name; close c; insert into classifications (fk_payroll_number, department, classification, classification_date, wages, comments, department_name) values (pay_number, rtrim(upper(loc1.department_3)), upper(loc1.historic_position_3), upper(loc1.position_date_3), loc1.wages_3, upper(loc1.comments_3), dept_name); end if; if (loc1.position_date_4 is not null) then dept := rtrim(upper(loc1.department_4)); open c; fetch c into dept_name; close c; insert into classifications (fk_payroll_number, department, classification, classification_date, wages, comments, department_name) values (pay_number, rtrim(upper(loc1.department_4)), upper(loc1.historic_position_4), upper(loc1.position_date_4), loc1.wages_4, upper(loc1.comments_4), dept_name); end if; if (loc1.position_date_5 is not null) then dept := rtrim(upper(loc1.department_5)); open c; fetch c into dept_name; close c; insert into classifications (fk_payroll_number, department, classification, classification_date, wages, comments, department_name) values (pay_number, rtrim(upper(loc1.department_5)), upper(loc1.historic_position_5), upper(loc1.position_date_5), loc1.wages_5, upper(loc1.comments_5), dept_name); end if; if (loc1.position_date_6 is not null) then dept := rtrim(upper(loc1.department_6)); open c; fetch c into dept_name; close c; insert into classifications (fk_payroll_number, department, classification, classification_date, wages, comments, department_name) values (pay_number, rtrim(upper(loc1.department_6)), upper(loc1.historic_position_6), upper(loc1.position_date_6), loc1.wages_6, upper(loc1.comments_6), dept_name); end if; /* This section populates the Eyeglasses fields. It evaluates three sets of records*/
Page 539
if (loc1.purchase_date_1 is not null) then insert into eyeglasses (fk_payroll_number, purchase_date, optician, cost, check_number) values (pay_number, upper(loc1.purchase_date_1), upper(loc1.optician_1), loc1.cost_1, upper(loc1.check_number_1)); end if; if (loc1.purchase_date_2 is not null) then insert into eyeglasses (fk_payroll_number, purchase_date, optician, cost, check_number) values (pay_number, upper(loc1.purchase_date_2), upper(loc1.optician_2), loc1.cost_2, upper(loc1.check_number_2)); end if; if (loc1.purchase_date_3 is not null) then insert into eyeglasses (fk_payroll_number, purchase_date, optician, cost, check_number) values (pay_number, upper(loc1.purchase_date_3), upper(loc1.optician_3), loc1.cost_3, upper(loc1.check_number_3)); end if; /*This section of the program populates the Tools table*/ if (loc1.tool_purchase_date_1 is not null) then insert into tools (fk_payroll_number, purchase_date, payroll_deduct, tool_name, tool_cost, payment, last_payment_amount, first_payment_date, last_payment_date) values (pay_number, upper(loc1.tool_purchase_date_1), upper(loc1.payroll_deduct_1), upper(loc1.tool_name_1), loc1.tool_cost_1, loc1.payment_amount_1, loc1.last_payment_amount_1, upper(loc1.first_payment_date_1), upper(loc1.last_payment_date_1)); end if; if (loc1.tool_purchase_date_2 is not null) then insert into tools (fk_payroll_number, purchase_date, payroll_deduct, tool_name, tool_cost, payment, last_payment_amount, first_payment_date, last_payment_date) values (pay_number, upper(loc1.tool_purchase_date_2), upper(loc1.payroll_deduct_2), upper(loc1.tool_name_2), loc1.tool_cost_2, loc1.payment_amount_2, loc1.last_payment_amount_2, upper(loc1.first_payment_date_2), upper(loc1.last_payment_date_2)); end if; if (loc1.tool_purchase_date_3 is not null) then insert into tools (fk_payroll_number, purchase_date, payroll_deduct, tool_name, tool_cost, payment, last_payment_amount, first_payment_date, last_payment_date) values (pay_number, upper(loc1.tool_purchase_date_3), upper(loc1.payroll_deduct_3), upper(loc1.tool_name_3), loc1.tool_cost_3, loc1.payment_amount_3, loc1.last_payment_amount_3, upper(loc1.first_payment_date_3), upper(loc1.last_payment_date_3)); end if; end loop; end; /
Page 540
After this program has been completed, the tables used in the system are populated. The system is ready for forms to be developed.
This installment is to develop the menu and forms that will be used to maintain the Employee system data. These forms consist of a Department Update form, an Employee Update form, an Employee Directory, and a Security Administration form. The system will also have a main menu that is used to call any form in the system.
NOTE |
This section will not describe each step in the creation of the forms and menus. This was covered in Chapters 11 to 16. The forms and menus are similar to those covered in these chapters. This chapter only discusses the design and special features of the applications. The steps to achieve the design are covered in the indicated chapters.n |
The first application to create is the Department Update application, which will be used to maintain the Departments table. This form only contains two columns. It will be designed with a partial screen-size canvas. This means the form will not entirely overlay other applications. Table 20.3 contains some of the key triggers and form attributes used in the Department Update form.
Table 20.3 Department Update Form Triggers and Key Attributes
Item
|
Purpose/Setting
|
Canvas Height | 287 |
Canvas Width | 336 |
Security triggers | The form should have form-level preinsert, preupdate, and predelete triggers that prevent unauthorized users from modifying the records. |
When-new-form-instance | This trigger will contain the execute-query built-in. This will enable the form to display the table's records as it is displayed. |
Window Height | 287 |
Window Width | 336 |
Figure 20.2 shows the executed Department Update form. The source code for this application is in file Deptupd.fmb.
Page 541
FIG. 20.2
The Department Update
screen used in the
Employee system.
The next form to create is the Employee Update form. This form will be the most sophisticated in the system. It is similar to the Employee Update form discussed in Chapters 11 to 16 except that it will have added functionality.
One of the new features is the ability to generate payroll numbers for new employees. The Next_payroll_number sequence is added to the form in a block "one" pre-insert trigger. The trigger is fired before a record is inserted into the Employees table. The trigger populates the Payroll_number item with the next value generated by the sequence. The Payroll_number item's Insert Allowed, Update Allowed, and Navigable properties will also be changed to value of false. This will preclude the operator from changing the item's value. Figure 20.3 illustrates the trigger used to generate the payroll number.
This form differs from the other Employee Update form in that it must have blocks for two additional tables. This form displays historical classification records contained in the Classification table. It will also display historical appraisal records contained in the Appraisal record. The form does not contain enough room to display the four child blocks that will display the records for the Appraisals, Classifications, Tools, and Eyeglasses tables. This will require the form to have stacked canvases. These canvases will overlay the original content canvas. The Tools and Eyeglasses table records will be displayed on one stacked canvas. The Appraisals and Classifications tables will be displayed on another stacked canvas.
The records for the Employees table is displayed in block one. The canvas for this block is "canvas2." Canvas2 and the block "one" layout are shown in Figure 20.4. The block uses a post-query trigger to populate the name of the nonbase table Department item. The State item was changed to a picklist and the Gender item to a radio group button. A LOV for the fk_department item is also created. A button adjacent to the item initiates the LOV.