oracle pl/sql The Program Create a new version of the Babbage's Cabbage's program from Assignment #8 (script is provided

[answerhappygod]

oracle pl/sql
The Program
Create a new version of the Babbage's Cabbage's program from
Assignment #8 (script is provided below) 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 below 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;