0

1.Procedure named *ship_order* that will accept an order# parameter. It should verify that the ship date for the order# is null and each book ordered has a sufficient quantity on hand. If every book is available, the quantity on hand for each book should be decremented and the ship date for the order should be updated to the current date.

This is what i have so far but i don't think i'm anywhere close to right

create or replace
PROCEDURE SHIP_ORDER 
(
  p_ORDER# in orders.order#%type,
  p_quantity out orderitems.quantity%type,
  p_shipdate out orders.shipdate%type,
  p_on_hand out books.on_hand_quantity%type
) AS 
cursor cur_ship_order is
select i.quantity, o.shipdate, b.on_hand_quantity
into p_quantity, p_shipdate, p_on_hand
from orderitems i, orders o, books b
where b.isbn = i.isbn
  AND i.order# = p_order#  AND o.shipdate is NULL;
BEGIN
  for i in cur_ship_order loop
    if p_quantity > 0 then

Edited by Ezzaral: Added code tags. Please use them to format any code that you post.

3
Contributors
2
Replies
3
Views
5 Years
Discussion Span
Last Post by hfx642
0

1. The number of books you have on-hand, has got nothing to do with the order.
Your on-hand should be in your table which stores your inventory.
For your order, you may want to track how many books ordered, and how many books shipped.
The number of books shipped will be the least of how many ordered and how many you have on-hand.
On-hand should be deducted by how many shipped.
In your inventory table, you may also want a reorder count.
When your inventory reaches the reorder count, order more books from the supplier.
That way, you don't run out of books to sell to your client.

2. Where is the rest of your Ship_Order procedure?

ps. It's bad practice to use special characters in naming... anything.

Edited by hfx642: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.