/* Function to receive an order_id value as an input parameter, and 
return the shipping & handling amount */

FUNCTION calc_sh (current_order_id NUMBER) RETURN NUMBER
IS
  total_order_amount NUMBER;
  sh_amount NUMBER;
BEGIN
  --determine total order amount
  SELECT SUM(price * order_quantity)
  INTO total_order_amount
  FROM cust_order, order_line, inventory
  WHERE cust_order.order_id = order_line.order_id
  AND order_line.inv_id = inventory.inv_id
  AND cust_order.order_id = current_order_id;
  
  --determine shipping & hanlding amount based on total order amount
  IF total_order_amount <= 25 THEN
    sh_amount := 5;
  ELSIF total_order_amount > 25 AND total_order_amount <= 50 THEN
    sh_amount := 8;
  ELSIF total_order_amount > 50 AND total_order_amount <= 75 THEN
    sh_amount := 10;
  ELSE
  	sh_amount := 15;
  END IF;
  
  --return function value
  RETURN sh_amount;
END;