Modify the program provided with this assignment to produce a report containing the names, income, federal income tax, provincial income tax, total tax, and effective tax rate of the following employees.

Name Salary

Liam Smith .......... $278,000

Emma Brown ........... 105,000

Sophia Lee ............ 79,000

Jacob Wilson .......... 47,000

Use the following simplified procedure for calculating the taxes: The federal government does not tax the first $11,474 of income, and the province exempts the first $11,550. You may assume that everyone in the company has a salary less than $1 million.

Federal rates

15% on taxable income of $45,916 or less

20.5% on taxable income between $45,916 and $91,831

26% on taxable income between $91,831 and $142,353

29% on taxable income between $142,353 and $202,800

33% on taxable income over $202,800

Provincial rates

16% of taxable income of $42,390 or less

20% on taxable income between $42,390 and $84,780

24% on taxable income between $84,780 and $103,150

25.75% on taxable income over $103,150

An example calculation is shown on the next page.

Only submit the code for the 3 functions that were changed.

Bonus: Modify the write_table procedure to produce a report for all employees in the Human Resources database. Consider only their salaries and ignore any commissions.

For example, Sophias federal taxes are calculated as follows:

Sophias taxable income = salary basic personal amount

= 79,000 11,474 = 67,526

Sophias tax in first bracket = 15% of 45,916

= 6,887.40

Sophias tax in second bracket = 20.5% of (67,526 45,916)

= 4,430.05

None of Sophias income falls in the top 2 brackets.

Her total federal tax is the sum of the amounts in all of the brackets:

6,887.40 + 4,430.05 = 11,317.45

Liams provincial taxes are $61,718.95.

CODE PROVIDED:

CREATE OR REPLACE PROCEDURE write_header IS BEGIN dbms_output.put_line('-------- Name -------- -------------- Tax --------------- Effective'); dbms_output.put_line('First Last Federal Provincial Total Rate'); dbms_output.put_line('---------- ---------- ---------- ---------- ---------- ---------'); END; /

CREATE OR REPLACE FUNCTION tax_in_bracket_on (rate IN NUMBER, bottom IN NUMBER, top IN NUMBER, taxable_income IN NUMBER) RETURN NUMBER IS -- TODO: FixMe amt_in_bracket NUMBER(10, 2); tax_in_bracket NUMBER(10, 2); BEGIN amt_in_bracket := taxable_income; tax_in_bracket := rate * amt_in_bracket; RETURN tax_in_bracket; END; /

CREATE OR REPLACE FUNCTION federal_tax_on (income IN NUMBER) RETURN NUMBER IS -- income: FixMe taxable_income NUMBER(10, 2) := income / 10; federal_tax NUMBER(10, 2); BEGIN federal_tax := tax_in_bracket_on(0.15, 0, 45916, taxable_income) + tax_in_bracket_on(0.205, 45916, 91831, taxable_income) + tax_in_bracket_on(0.26, 91831, 142353, taxable_income) + tax_in_bracket_on(0.29, 142353, 202800, taxable_income) + tax_in_bracket_on(0.29, 202800, 999999, taxable_income);

RETURN federal_tax; END; /

CREATE OR REPLACE FUNCTION provincial_tax_on (income IN NUMBER) RETURN NUMBER IS -- TODO: FixMe BEGIN RETURN income * 0.1; END; /

CREATE OR REPLACE PROCEDURE write_detail (first_name IN VARCHAR2, last_name IN VARCHAR2, income IN NUMBER) IS fed_tax NUMBER(10, 2) := federal_tax_on(income); prov_tax NUMBER(10, 2) := provincial_tax_on(income); total_tax NUMBER(10, 2) := fed_tax + prov_tax; rate NUMBER( 4, 2) := 100 * total_tax / income; BEGIN dbms_output.put_line(RPAD(first_name, 12) || RPAD(last_name, 12) || TO_CHAR(fed_tax, '999,999.99') || ' ' || TO_CHAR(prov_tax, '999,999.99') || ' ' || TO_CHAR(total_tax, '999,999.99') || ' ' || TO_CHAR(rate, '99.99') || '%'); END; /

CREATE OR REPLACE PROCEDURE write_table IS BEGIN write_header;

write_detail('Liam', 'Smith', 278000); write_detail('Emma', 'Brown', 105000); write_detail('Sophia', 'Lee', 79000); write_detail('Jacob', 'Wilson', 47000); END; /

DECLARE BEGIN write_table; END;