- The source information/data needed to calculate the severance package lies in anoracle database table.
- See Appendix 1 (Excel spreadsheet) for sample data/structure of the source table.
- Create two tables for this assignment.
- The first table will be the source table - containing the employees' information.
- Populate the first table with test data (Cater for all possible test cases).
- The second table should store the following details of the severance package:
- Employee number, First Name, Last Name, Years of service, salary, pension (companyportion),housing allowance, medical allowance(company), entertainment allowance,car allowance, bonus(monthly portion),value of leave days, week pay *years of service, monthly package, final severance pay.
- Write PL/SQL program/s (Procedures, functions, triggers etc) that will extract the informationfrom the source table, do the necessary calculations and populate thetarget table with the required information.
- The management also want a summary which should look like the one below:
Summary Report:
Total number of employees: 2
Number of employees 31 yrs old and above: 1(50% )
Total salary all employees: 17800
Total pension all employees: 2492
Total housing all employees: 2670
Total medical all employees: 2789.8
Total car allowance all employees: 0
Total bonus all employees: 1483.34
Total leave value all employees: 14857.92
Total severance cost all employees: 158755
- Write the necessary PL/SQL block to produce the required summary report.
- For the number of employees above a certain age, the program/code must promptyou to enter the age limit or alternatively it can be passed as a parameter to theprocedure. If you enter 40 or pass it as a parameter for the age limit, then the programmust give the number of employees who are 40 years and above includingthe percentage between the brackets as shown in the sample summary reportabove.
- Consider only the company contribution for all allowances.