Previous | Table of Contents | Next

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.

Installment 4—Designing and Building the User Interface

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 Department Update Form (Deptupd.fmb)

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 Employee Update Form (Empupd.fmb)

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.

Previous | Table of Contents | Next