CREATE OR REPLACE PACKAGE MNT_ORD_ITEM AS
    PROCEDURE ADD_ORDER(kundenid ord.custid%type,
                        kommplan ord.commplan%type DEFAULT NULL);
    PROCEDURE ADD_ITEM(auftragsnr ITEM.ordid%type, produktnr ITEM.prodid%type,
                       preis ITEM.actualprice%type, menge ITEM.qty%type);
    PROCEDURE ADD_PRICE(produktnr product.prodid%type,
                        standardpreis price.stdprice%type,
                        mindestpreis price.minprice%type);
    PROCEDURE CHECK_CREDITLIMIT;
END MNT_ORD_ITEM;
/

CREATE OR REPLACE PACKAGE BODY MNT_ORD_ITEM AS

    PROCEDURE ADD_ORDER
     (kundenid ord.custid%type, kommplan ord.commplan%type DEFAULT NULL)
    AS
        curdate ord.orderdate%type; 
    BEGIN
        curdate := SYSDATE;
        INSERT INTO ord(ordid, orderdate, commplan, custid)
        VALUES(ordid.nextval, curdate, kommplan, kundenid);
    END ADD_ORDER;
    
    PROCEDURE ADD_ITEM
      (auftragsnr ITEM.ordid%type, produktnr ITEM.prodid%type,
       preis ITEM.actualprice%type, menge ITEM.qty%type)
    AS
        maxitemid INTEGER;
        anz_produkte INTEGER;
        anz_bestellungen INTEGER;
        PRODID_NOT_FOUND EXCEPTION;
        ORDID_NOT_FOUND EXCEPTION;
    BEGIN
        SELECT COUNT(p.prodid) INTO anz_produkte FROM product p
        WHERE p.prodid=produktnr;
        SELECT COUNT(o.ordid) INTO anz_bestellungen FROM ord o
        WHERE o.ordid=auftragsnr;
        IF anz_produkte=0 THEN raise PRODID_NOT_FOUND;
        ELSIF anz_bestellungen=0 THEN raise ORDID_NOT_FOUND;
        END IF;
        SELECT max(itemid) INTO maxitemid FROM item WHERE ordid = auftragsnr;
        maxitemid := NVL(maxitemid, 0);
        maxitemid := maxitemid+1;
        INSERT INTO item(ordid, itemid, prodid, actualprice, qty, itemtot)
        VALUES(auftragsnr, maxitemid, produktnr, preis, menge, menge*preis);
    EXCEPTION
        WHEN PRODID_NOT_FOUND THEN
            INSERT INTO errors VALUES('Produktnummer nicht gefunden.',
                produktnr, auftragsnr);
        WHEN ORDID_NOT_FOUND THEN
            INSERT INTO errors VALUES('Auftragsnummer nicht gefunden.',
                produktnr, auftragsnr);
    END ADD_ITEM;

    PROCEDURE ADD_PRICE
      (produktnr product.prodid%type, standardpreis price.stdprice%type,
       mindestpreis price.minprice%type)
    AS
        PRODID_NOT_FOUND EXCEPTION;
        anz_produkte INTEGER;
    BEGIN
        SELECT COUNT(p.prodid) INTO anz_produkte FROM product p
        WHERE p.prodid=produktnr;
        IF anz_produkte=0 THEN raise PRODID_NOT_FOUND;
        END IF;
        UPDATE price SET enddate = SYSDATE-1 WHERE
        startdate = (SELECT MAX(startdate) FROM
                        (SELECT * FROM price p WHERE p.prodid=produktnr))
        AND prodid = produktnr;
        INSERT INTO price(prodid, stdprice, minprice, startdate)
        VALUES(produktnr, standardpreis, mindestpreis, SYSDATE);
    EXCEPTION
        WHEN PRODID_NOT_FOUND THEN
            INSERT INTO errors
            VALUES('Ungueltige Produktnummer.', produktnr, -1);
    END ADD_PRICE;

    PROCEDURE CHECK_CREDITLIMIT
    AS
        gesbetrag INTEGER;
        yesno customer.creditlimit_indicate%type;
    CURSOR c IS SELECT cust.custid, cust.creditlimit
                FROM customer cust, ord o
                WHERE o.custid=cust.custid;
    c_rec c%ROWTYPE;
    BEGIN
        FOR c_rec IN c LOOP
           SELECT SUM(o.total) INTO gesbetrag FROM ord o
           WHERE c_rec.custid=o.custid;
           IF gesbetrag>c_rec.creditlimit THEN
               yesno := 'YES';
           ELSE
               yesno := 'NO';
           END IF;
           UPDATE customer cust SET CREDITLIMIT_INDICATE=yesno
           WHERE cust.custid=c_rec.custid;        
        END LOOP;
    END CHECK_CREDITLIMIT;
END MNT_ORD_ITEM;
/    
