As a new ABC Consultant assigned to the XYZ Company, you have been asked to enhance the current system to include payroll processing.  Although the current employee table has monthly salary and commission columns, it does not provide any means for storing employee deductions. 

1. You will add the tables      necessary to store employee deductions. Next you will create a payroll pre-calculation      program that will calculate the net pay for all the employees via a batch      process (a stored procedure in a package, which will call other stored      procedures within the package). Although this is not a complete payroll      system, the unit test results must be accurate. 
2. Next you will create two      PL/SQL blocks for inserting and deleting rows from the employee deduction      table.  These PL/SQL blocks will be      passed information from host or bind variables and a third PL/SQL block      which will assign the variables defined in SQL*Plus (e.g. employee number,      dollar amount and deduction name). Since the XYZ Company wants to track      changes to the employee and employee deduction tables, you will create two      database triggers that will update audit tables when rows are changed or      deleted. 
3. The XYZ Company also      requires a view that will display specific employee information, plus the      number of deductions for an employee.       This will be accomplished by creating a stored function that will      count the number for deductions for an employee.  This function will be invoked in the      select statement for the employee view. 

Task List:

Setup:

1. Create the tables in the existing system. 
2. Create a table for deductions with three columns for deduction name (PK), Salary grade minimum, salary grade maximum. You need to populate the deduction table any way you wish. Populate the table with as many deductions as you think would be appropriate to thoroughly test your application.  The salgrade min and max information will come from the salgrade table. 
3. Create a table for employee deductions with a foreign key (name) to the deduction table, a flag that indicates if the deduction is a before tax or after tax deduction, deduction (dollar) amount and another foreign key (empno) to the employee table.   This table is an intersection table and the two foreign keys concatenated together will be the primary key of this table. 
4. Create two audit tables, one for the employee table and one for the employee deduction table.  These audit tables should contain a unique numeric ID (assigned via a sequence), the current date, and the user id of the person that has made the change to he employee or employee deduction table, and all the columns from the source table. 
5. Create two sequences that will be used to populate the audit tables with a unique identifier.
6. Create a table to keep tax rates, these can be real or bogus, as long as there are at least 7 different rates ( For example, if the annual salary is 0 -10000 then a 5% tax rate, 10001 – 20000 then a 7% tax rate, etc)

Note: Data for these tables may be found in the Appendix of this Final Exam document. If you wish, you may add addition rows to these tables. 

CREATE TABLE dept (

deptno         Number(2) Primary Key,

dname         VARCHAR2(14),

loc               VARCHAR2(13));

Create table emp (

empno       NUMBER(4) Primary Key,

ename       VARCHAR2(10),

job            VARCHAR2(9),

mgr           NUMBER(4),

hiredate    DATE,

sal             NUMBER(7,2),

comm        NUMBER(7,2),

deptno       NUMBER(2));

Create table salgrade (

grade       NUMBER,

losal       NUMBER,

hisal       NUMBER);

CREATE TABLE deductions  (

      name     VARCHAR2(30),

      salary_grade_min NUMBER(2),

      salary_grade_max NUMBER(2));

CREATE TABLE emp_deductions     (

     fk_deduction                     VARCHAR2(30),

     fk_empno                          NUMBER(4),

     before_or_after_flag         CHAR(1),

      deduction_amount           NUMBER(6,2));

                       CREATE TABLE emp_audit   (

                             audit_uid               NUMBER(15),

                             change_date          DATE,

                             change_user          VARCHAR2(30),

                              action                   CHAR(1),

                              empno                  NUMBER(4),

                              ename                   VARCHAR2(10),

      job                        VARCHAR2(9),

      mgr                       NUMBER(4),

      hiredate                DATE,

      sal                         NUMBER(7,2),

     comm                    NUMBER(7,2),

    deptno                    NUMBER(2));

CREATE TABLE emp_deductions_audit    (

                              audit_uid                          NUMBER(15),

                             change_date                      DATE,

                             change_user                      VARCHAR2(30),

                              action                               CHAR(1),

                              fk_deduction                    VARCHAR2(30),

                              fk_empno                         NUMBER(4),

                              before_or_after_flag        CHAR(1),

                              deduction_amount           NUMBER(6,2));

CREATE TABLE tax_rates ( 

    percent       NUMBER(3,2),

    salary_min NUMBER(8,2),

    salary_max NUMBER(8,2));

Program Description 

• Create      two PL/SQL blocks that will insert or delete rows from the employee      deduction table.  Include exception      processing for bad input data and verify that an employee is eligible for a      deduction based upon their salary grade.       Salary grade is determined by checking the salary grade table to      derive the employee’s salary grade.       Once you know an employee’s grade, then verify if the employee is      eligible for the deduction (e.g. 401K) via comparing it to the minimum and      maximum salary grade that is stored in the deduction table ( see      definition above).
• Create      two database triggers that will update the employee audit table and      employee deduction audit table when rows are either changed or deleted.  Use a sequence number to assign a unique      identifier to each row as it is created in the audit table.  Capture the user ID, date of the change      or delete, and action (update or delete), plus all the fields in the      before image of the row (e.g. before it is changed or deleted).
• Create      a function that will count the number of deductions for an employee.  Input to the function is the employee ID      and it returns the count of employee deductions.  If there aren’t any deductions for the      employee, the function returns zero.       Within the view, include the employee name, number, hire date, job,      dept number and the umber of deductions for the employee. Use the naming      standard <name>_v.  

CREATE or REPLACE VIEW show_deduction_v  AS

SELECT empno, 

   ename, 

    hiredate, 

    deptno, 

     job, 

     count_deductions(empno) deduction_cnt

FROM emp;

• Create      a package that contains multiple stored procedures.  Only one procedure will be available to      external calls ( in the package specifications).  The other procedure will be subroutines      that are called by the main procedure in the package body.  It is okay to include stored functions      in your package that support the procedure.  The procedure will accomplish the      following steps for each employee:
• Subtract       the before tax deductions from the monthly salary.
• Calculate       and subtract Federal Tax and State Income using the tax table that you       created (see above).  For more of a       challenge, initially load the tax table into a PL/SQL table that resides       in memory (optional).   Use the       same rate for Federal Income Tax (FIT) and State Income Tax (SIT).
  • Exclude        other taxes  (e.g. SS)
  • Include        commission as part of the salary
• Subtract       after tax deductions from the remaining monthly salary to       determine the net pay.
• Use       DBMS_OUTPUT to display all deductions/calculations and net pay.  This will enable you to turn spool on       and capture all the calculations for all employees when you execute the procedure       via SQL*Plus.  

Deliverables

• Listing      of code for every block (function, trigger, a package with procedures,      etc.) defined above.
• Unit      test data for every block defined above (use the spool command).