oracle pl/sql The Program Create a new version of the Babbage's Cabbage's program from Assignment #8 using the modular s

[answerhappygod]

oracle pl/sql
The Program
Create a new version of the Babbage's Cabbage's program from
Assignment #8 using the modular structures offered by PL/SQL:
procedures, functions, and packages.
One significant change from the previous version is that
transport fees will now be looked up by code in a table
named bc_transport_codes.
Use the script provided (Links to an external
site.) to create the necessary tables and populate them with
sample data.
Requirements
The program code is contained entirely within modules. All of
the modules listed below must be in a single package
named bc_lastname that incorporates your last name
(e.g., bc_krofchok). Use only the lowercase letters a–z when
spelling your last name; please omit any accent marks, spaces or
hyphens.
You will need to supply an appropriate data type for each of the
parameters, but do not change the module's name or parameter list —
the number of parameters, their order, or the in/out
directionality. You are allowed to change the
names of the parameters, if desired (to conform to the textbook's
style, for example), but each parameter must keep its same
conceptual meaning.
split_hours( in hours, out reg_hours,
out ovt_hours )
A procedure that splits the given number of hours into regular
hours and overtime, per Assignment #8.
compute_gross_pay( in hours,
in hourly_rate )
A function that computes and returns an employee's gross pay
given their hours and hourly rate, per Assignment #8. This module
must call the split_hours module to determine the regular
and overtime hours, rather than duplicating that module's
logic.
compute_taxes( in gross_pay )
A function that computes and returns an employee's taxes given
their gross pay amount, per Assignment #8.
get_transport_amount( in code )
A function that returns the transportation deduction amount
corresponding to the given single-letter code. The amount is looked
up in the bc_transport_codes table. If the code supplied
is NULL, then zero is returned. If the code supplied is not NULL
and not found in the lookup table, an application error with error
number -20007 and message “Transportation code not found” is
raised.
compute_net_pay( in gross_pay, in taxes,
in transport_code )
A function that computes and returns an employee's net pay given
their gross pay, taxes, and transportation deduction code, per
Assignment #8. The amount of the transportation deduction is
determined using the get_transport_amount function. If
the transportation deduction code is determined to be invalid due
to an error being raised, then NULL is returned for the net pay
amount.
process_payroll( )
A procedure that processes the weekly payroll for all employees
in the bc_employees table. A complete and correct row is
inserted into the bc_payroll table for each employee; any
existing rows in the table are deleted by this module before
processing begins. If the net pay amount for an employee is NULL,
it is an indication that their transportation deduction code is
invalid (and their transportation deduction will also be listed as
NULL).
Create BC Tables
BEGIN
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE
bc_payroll';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE
bc_employees';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP SEQUENCE
bc_employee_id_seq';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE
bc_transport_codes';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END;
/
CREATE TABLE bc_transport_codes (
code CHAR(1)
PRIMARY KEY,
description VARCHAR2(30) NOT NULL
UNIQUE,
amount NUMBER(5,2) NOT
NULL CHECK (amount >= 0)
);
CREATE SEQUENCE bc_employee_id_seq
START WITH 1;
CREATE TABLE bc_employees (
employee_id NUMBER
DEFAULT bc_employee_id_seq.NEXTVAL PRIMARY KEY,
last_name VARCHAR2(30) NOT
NULL,
first_name VARCHAR2(30) NOT
NULL,
hours NUMBER(4,2)
NOT NULL CHECK (hours >= 0),
hourly_rate NUMBER(4,2) NOT NULL
CHECK (hourly_rate >= 0),
transport_code VARCHAR(1)
);
CREATE TABLE bc_payroll (
employee_id NUMBER
NOT NULL REFERENCES bc_employees(employee_id),
reg_hours NUMBER(4,2) NOT NULL
CHECK (reg_hours >= 0),
ovt_hours NUMBER(4,2) NOT NULL
CHECK (ovt_hours >= 0),
gross_pay NUMBER(6,2) NOT NULL
CHECK (gross_pay >= 0),
taxes NUMBER(5,2)
NOT NULL CHECK (taxes >= 0),
transport_fee NUMBER(4,2) CHECK (transport_fee
>= 0),
net_pay NUMBER(6,2)
);
INSERT INTO bc_transport_codes VALUES ('P', 'Parking Garage',
7.50);
INSERT INTO bc_transport_codes VALUES ('T', 'Transit Pass' ,
5.00);
INSERT INTO bc_transport_codes VALUES ('L', 'Bike Locker' ,
1.00);
INSERT INTO bc_employees (
last_name, first_name, hours, hourly_rate,
transport_code
) VALUES (
'Horsecollar', 'Horace', 38.00, 12.50, 'P'
);
INSERT INTO bc_employees (
last_name, first_name, hours, hourly_rate,
transport_code
) VALUES (
'Reins', 'Rachel', 46.50, 14.40, 'T'
);
INSERT INTO bc_employees (
last_name, first_name, hours, hourly_rate,
transport_code
) VALUES (
'Saddle', 'Samuel', 51.00, 40.00, NULL
);
Assignment #8 Code
BEGIN
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE
bc_payroll';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE
bc_employees';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP SEQUENCE
bc_employee_id_seq';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END;
/
CREATE SEQUENCE bc_employee_id_seq
START WITH 1;
CREATE TABLE bc_employees (
employee_id NUMBER
DEFAULT bc_employee_id_seq.NEXTVAL PRIMARY KEY,
last_name VARCHAR2(30) NOT
NULL,
first_name VARCHAR2(30) NOT
NULL,
hours NUMBER(4,2)
NOT NULL CHECK (hours >= 0),
hourly_rate NUMBER(4,2) NOT NULL
CHECK (hourly_rate >= 0),
transport_code VARCHAR(1) NOT NULL
CHECK (transport_code IN ('P', 'T',


'L',
'N'))
);
CREATE TABLE bc_payroll (
employee_id NUMBER
NOT NULL REFERENCES bc_employees(employee_id),
reg_hours NUMBER(4,2) NOT NULL
CHECK (reg_hours >= 0),
ovt_hours NUMBER(4,2) NOT NULL
CHECK (ovt_hours >= 0),
gross_pay NUMBER(6,2) NOT NULL
CHECK (gross_pay >= 0),
taxes NUMBER(5,2)
NOT NULL CHECK (taxes >= 0),
transport_fee NUMBER(4,2) NOT NULL CHECK
(transport_fee >= 0),
net_pay NUMBER(6,2) NOT
NULL
);
INSERT INTO bc_employees (
last_name, first_name, hours, hourly_rate,
transport_code
) VALUES (
'Horsecollar', 'Horace', 38.00, 12.50, 'P'
);
INSERT INTO bc_employees (
last_name, first_name, hours, hourly_rate,
transport_code
) VALUES (
'Reins', 'Rachel', 46.50, 14.40, 'T'
);
INSERT INTO bc_employees (
last_name, first_name, hours, hourly_rate,
transport_code
) VALUES (
'Saddle', 'Samuel', 51.00, 40.00, 'N'
);
DECLARE
CURSOR employees_cursor IS
SELECT employee_id, hours, hourly_rate,
transport_code
FROM bc_employees
ORDER BY hours DESC;
employee_row bc_employees%ROWTYPE;
reg_hours bc_payroll.reg_hours%TYPE;
ovt_hours bc_payroll.ovt_hours%TYPE;
gross_pay bc_payroll.gross_pay%TYPE;
taxes bc_payroll.taxes%TYPE;
transport_fee bc_payroll.transport_fee%TYPE;
net_pay bc_payroll.net_pay%TYPE;
BEGIN
DELETE FROM bc_payroll;
FOR employee_row IN employees_cursor LOOP
-- Split hours into regualar and
overtime
IF employee_row.hours > 40
THEN
reg_hours := 40;
ovt_hours :=
employee_row.hours - 40;
ELSE
reg_hours :=
employee_row.hours;
ovt_hours := 0;
END IF;
CASE employee_row.transport_code
WHEN 'P' THEN

transport_fee := 7.50;
WHEN 'T' THEN

transport_fee := 5.00;
WHEN 'L' THEN

transport_fee := 1.00;
ELSE

transport_fee := 0.00;
END CASE;
gross_pay := (reg_hours *
employee_row.hourly_rate) +
(ovt_hours * 1.5 *
employee_row.hourly_rate);
taxes := gross_pay * 0.28;
net_pay := gross_pay - taxes -
transport_fee;
-- Insert results into payroll table
using Dynamic SQL
EXECUTE IMMEDIATE
'INSERT INTO bc_payroll
VALUES (' ||

employee_row.employee_id || ', ' ||
reg_hours
|| ', ' ||
ovt_hours
|| ', ' ||
gross_pay
|| ', ' ||
taxes || ',
' ||

transport_fee || ', ' ||
net_pay ||
')';
END LOOP;
END;
/
SELECT last_name, first_name,
TO_CHAR(reg_hours, '90.99') AS reg_hours,
TO_CHAR(ovt_hours, '90.99') AS ovt_hours,
TO_CHAR(hourly_rate, '90.99') AS hourly_rate,
TO_CHAR(gross_pay, '9990.99') AS gross_pay,
TO_CHAR(taxes, '9990.99') AS taxes,
TO_CHAR(transport_fee, '90.99') AS
transport_fee,
TO_CHAR(net_pay, '9990.99') AS net_pay
FROM bc_payroll
JOIN bc_employees
ON bc_payroll.employee_id =
bc_employees.employee_id
ORDER BY bc_employees.employee_id;