DBS311 Assignment 2 function that determines the price markup

$30.00

Category: You will Instantly receive a download link for .zip solution file upon Payment

Description

5/5 - (3 votes)

 

  1. Create a user defined function that determines the price markup in the products file. Be prepared to show it being used in a select statement in the select list, the where clause, and the order by clause. When demonstrating this just include the product name, the two different types of prices and the markup.
  2. PATIENTA and INSURANCEA tables have been provided for you. Use those tables for your assignment Adapt your lab stored procedure to reject any updates where a company tries to pay 25% or less.

 

 

 

  1. Adapt your lab procedure to use a CURSOR that goes through all the updated rows after the update was applied and reports on the ones where the percentage payout by the company for all the patients exceeds the company’s maximum payout amount.

For this assignment, we will not adjust the insurance payment to the maximum payout they allow, we will just report on it.

 

 

  1. Create an update trigger program for PATIENTA. If the company pays < 40% there may be substantial debt remaining for the Patients. If the amount owing is greater than 10,000 and the percentage paid is less than 40%, use a trigger to store a row in a new table called DCOLLECTION (difficult collections). The hospital will go through these entries and try to get the insurance company to pay a higher percentage.

This is the DCOLLECTIONS table.

When the insurance company pays 80% for all the patients, there will be no row added to DCOLLECTIONS. The patient may still owe more thatn $10,000, but the hospital will not try to get the company to raise the rate.

In this case the rate was below 40% and a few patients still owe more than $10,000 – so rows are added to the DCOLLECTIONS table.

  1. Create a stored procedure that uses a cursor for a join of any two of ORDERS, PRODUCTS, ORDERDETAILS and CUSTOMERS. In your WHERE condition, have a minimum of five rows returned. Allow one or two parameters to be passed to your stored procedure that limits the amount of rows returned. The cursor should accept these parameter and be capable of producing different results each time it is used. Show your output with dbms_output.put_line.

 

Related products