-- add_order.sql

DECLARE
  inv_num order_header.invoice_number%TYPE;

  FUNCTION get_next_invoice RETURN NUMBER
  AS
    invoice_num NUMBER;
  BEGIN
    SELECT MAX(invoice_number) + 1
      INTO invoice_num
      FROM order_header;

    RETURN invoice_num;
  END get_next_invoice;

BEGIN
  inv_num := get_next_invoice;

  INSERT INTO order_header
    (invoice_number, store_number, customer_id, order_date)
    VALUES (inv_num, 7, 9994, SYSDATE);

  INSERT INTO order_item
    (invoice_number, product_id, quantity, discount)
    VALUES (inv_num, 'CHYE0000003', 2, .1);
END;
/
