The problems requires the procedure PL SQL knowledge. Please use Oracle SQL Developer and provide some examples to test the code. Please see the details below. 

 

 

 

3. Create a procedure calledrevenue_report_procedurewhich takes the followingparameters as input:(1) RouteID (e.g., R101)(2) rmonth (month to run the report for, e.g., 2 for February)(3) rYear (year to run the report or, e.g., 2015)Note: the name of the procedure and the order of parameters is important (do not changethem).Create a procedure which generates data into the table REVENUE_REPORT (do not changethe name of the table)This procedure takes the parameters listed above and generates data for the table. When it isrun for say route R101 and parameters 02 (February) and 2015, the REVENUE_REPORT tableshould be populated with routeID, revMonth, revYear, revenue, and differencePrevPeriod.The procedure should obtain the last month the procedure was run for (e.g., let’s assume thisis 11-2014, i.e., November). Then it should compute the required data for each month up toand including Feb 2015 (i.e., for December 2014, January 2015, February 2015) and store thisin the REVENUE_REPORT table. If no previous month can be found for that route, thenassume the first month for which data should be populated is January of the supplied year(using the previous example, if no entry for R101 is found in REVENUE_REPORT, start withJan 2015; set differencePrevPeriod for January 2015 to 0).Revenue can be calculated based on the validation records.A user swiping their bus-card at the terminal is called validation and a record is generated inthe validation_details table. Whenever user validates his/her card, an amount should bededucted from the user balance. The BALANCE column in USERS should be updated(reduced) based on the dollar value for that trip.Different user types will have different discounts (see: discount column in theUSER_TYPES table)Different routes will have different fares (see: routeFare column in the ROUTES table)The net fare to be charged is calculated based on the standard fare for a route. From thisstandard fare, a discount is deducted to give you the net fare charged.You will want to aggregate this fare for the needed route and month. If no validation recordsexist for a month, the revenue is 0.To calculate “differencePrevPeriod” for the month: subtract last month’s revenue from thismonth’s revenue (e.g., if revenue for Feb-2015 is $10,000 and for Jan-2015 is $9750, thendifferencePrevPeriod for Feb-2015 is $250). The difference may be negative, zero or positivedepending on the data.Here are the codes to create tables and insert data into those tables.DROP TABLE ROUTES cascade constraints;

Attachments:

• 1496027492-question 1.docx