ee

CREATE OR REPLACE
PACKAGE BODY P5_summit_pkg AS
 
  PROCEDURE P1_1_VOEG_KLANT_TOE 
  (
    p_id      IN S_CUSTOMER.ID%TYPE,
    p_name    IN S_CUSTOMER.NAME%TYPE,
    p_phone   IN S_CUSTOMER.PHONE%TYPE,
    p_address IN S_CUSTOMER.ADDRESS%TYPE,
    p_city    IN S_CUSTOMER.CITY%TYPE,
    p_state   IN S_CUSTOMER.STATE%TYPE,
    p_country IN S_CUSTOMER.COUNTRY%TYPE,
    p_zipcode IN S_CUSTOMER.ZIP_CODE%TYPE
  )
  AS
 
  BEGIN
    INSERT INTO S_CUSTOMER(ID, NAME, PHONE, address, city, state, country,ZIP_CODE)
    VALUES (p_id,p_name,P_PHONE,P_ADDRESS,P_CITY,P_STATE,P_COUNTRY,P_ZIPCODE);
    COMMIT;
  END P1_1_VOEG_KLANT_TOE;
 
 
  PROCEDURE P1_2_VOEG_ORDER_TOE 
  (
    P_ID           IN S_ORD.ID%TYPE  
  , P_CUSTOMERID   IN S_ORD.CUSTOMER_ID%TYPE  
  , P_SALES_REP_ID IN S_ORD.SALES_REP_ID%TYPE  
  , P_DATE_ORDERED IN S_ORD.DATE_ORDERED%TYPE    
  , P_PAYMENT_TYPE IN S_ORD.PAYMENT_TYPE%TYPE  
  ) AS 
    v_jobtitle     S_EMP.TITLE%TYPE;
  BEGIN
    SELECT UPPER(TITLE)
    INTO v_jobtitle
    FROM S_EMP
    WHERE ID = p_sales_rep_id;
 
    IF V_JOBTITLE = 'SALES REPRESENTATIVE' THEN
      INSERT INTO S_ORD(id, customer_id, SALES_REP_ID, DATE_ORDERED, PAYMENT_TYPE)
      VALUES(p_id, p_customerid, p_sales_rep_id, p_date_ordered, p_payment_type);
      DBMS_OUTPUT.PUT_LINE('Order toegevoegd');
      COMMIT;
    ELSE
      DBMS_OUTPUT.PUT_LINE('Order NIET toegevoegd');
      ROLLBACK;
    END IF;
    COMMIT;
  END P1_2_VOEG_ORDER_TOE;
 
    PROCEDURE P1_3_VOEG_ORDERLIJN_TOE 
  (
    P_ORD_ID IN S_ITEM.ORD_ID%TYPE  
  , P_PRODUCT_ID IN S_ITEM.PRODUCT_ID%TYPE   
  , P_PRICE IN S_ITEM.PRICE%TYPE   
  , P_QUANTITY IN S_ITEM.QUANTITY%TYPE 
  ) AS 
    V_ITEM_ID   S_ITEM.ITEM_ID%TYPE;
  BEGIN
    SELECT MAX(ITEM_ID)+1
    INTO V_ITEM_ID
    FROM S_ITEM
    WHERE ORD_ID = P_ORD_ID;
 
    IF V_ITEM_ID IS NULL THEN
      V_ITEM_ID := 1;
    END IF;
 
    INSERT INTO S_ITEM(ORD_ID,ITEM_ID,PRODUCT_ID,PRICE,QUANTITY)
    VALUES(P_ORD_ID, V_ITEM_ID, P_PRODUCT_ID, P_PRICE, P_QUANTITY);
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Orderlijn toegevoegd!');
  END P1_3_VOEG_ORDERLIJN_TOE;
 
    PROCEDURE P4_4_VERWIJDER_MEDEWERKER(
  MED_ID IN S_EMP.ID%TYPE)
  AS
    E_CHILDREN_FOUND EXCEPTION;
    PRAGMA EXCEPTION_INIT (E_CHILDREN_FOUND, - 2292);
  BEGIN
    DELETE FROM S_EMP
    WHERE ID = MED_ID;
 
  EXCEPTION
    WHEN E_CHILDREN_FOUND THEN 
    DBMS_OUTPUT.PUT_LINE ('Employee has children!');
    WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR( -20000, 'Onbekende fout opgetreden');
  END P4_4_VERWIJDER_MEDEWERKER;
 
  PROCEDURE P4_4_VERWIJDER_MEDEWERKER(
  P_LAST_NAME IN S_EMP.LAST_NAME%TYPE,
  P_FIRST_NAME IN S_EMP.FIRST_NAME%TYPE)
  AS
    E_CHILDREN_FOUND EXCEPTION;
    PRAGMA EXCEPTION_INIT (E_CHILDREN_FOUND, - 2292);
  BEGIN
    DELETE FROM S_EMP
    WHERE LAST_NAME = P_LAST_NAME AND FIRST_NAME = P_FIRST_NAME ;
 
  EXCEPTION
    WHEN E_CHILDREN_FOUND THEN 
    DBMS_OUTPUT.PUT_LINE ('Employee has children!');
    WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR( -20000, 'Onbekende fout opgetreden');
  END P4_4_VERWIJDER_MEDEWERKER;
 
    PROCEDURE P2_2_HOOGSTE_VOORRAADWAARDE AS 
   TYPE TYPE_REC_VOORRAADWAARDE IS RECORD(
        WHID    S_INVENTORY.WAREHOUSE_ID%TYPE,
        WHVALUE NUMBER
    );
 
      TYPE TYPE_COL_VOORRAAD
      IS TABLE OF TYPE_REC_VOORRAADWAARDE
      INDEX BY PLS_INTEGER;
 
      t_voorraad  TYPE_COL_VOORRAAD;
  BEGIN
      SELECT WAREHOUSE_ID, SUM(I.AMOUNT_IN_STOCK * P.SUGGESTED_WHLSL_PRICE) Waarde
      BULK COLLECT INTO t_voorraad
      FROM S_INVENTORY I, S_PRODUCT P
      WHERE I.PRODUCT_ID = P.ID
      GROUP BY WAREHOUSE_ID
      ORDER BY Waarde DESC;
 
      DBMS_OUTPUT.PUT_LINE('De 3 voorraadruimten met de hoogste voorraadwaarde :');
      FOR I IN 1..3 LOOP
        DBMS_OUTPUT.PUT_LINE('Voorraadruimte: ' || t_voorraad(I).WHID ||' Waarde: ' ||t_voorraad(I).WHVALUE);
      END LOOP;
 
  END P2_2_HOOGSTE_VOORRAADWAARDE;
 
  PROCEDURE VERWIJDER_ORDER
  (p_ordid IN s_ord.id%TYPE)
  IS
    v_ordid NUMBER := 0;
   BEGIN
      SELECT COUNT(ord_id) INTO v_ordid 
      FROM s_item 
      WHERE ord_id = p_ordid;
      IF v_ordid <> 0 
       THEN
        DELETE FROM s_item WHERE ord_id = p_ordid;
        DELETE FROM s_ord WHERE id = p_ordid;
        COMMIT;
       ELSE RAISE_APPLICATION_ERROR (-20006, 'Order bestaat niet ');
      END IF; 
   EXCEPTION
        WHEN NO_DATA_FOUND THEN 
          RAISE_APPLICATION_ERROR(-20005, 'Order bestaat niet ');
END VERWIJDER_ORDER;
 
PROCEDURE VERHOOG_TOTAL_IN_STOCK
  (p_product_id  IN s_product.id%TYPE,
  p_total_in_stock IN s_product.total_in_stock%TYPE)
  IS
  BEGIN
  UPDATE s_product
  SET total_in_stock=NVL(total_in_stock,0)+p_total_in_stock
  WHERE id=p_product_id;
END VERHOOG_TOTAL_IN_STOCK;
 
FUNCTION P1_5_TOTAALBEDRAG_ORDERS 
(
  P_PERSONEELSNUMMER IN PLS_INTEGER  
) RETURN NUMBER AS 
  v_TOTAAL_BEDRAG_ORDERS NUMBER;
BEGIN
  SELECT SUM(TOTAL)
  INTO v_TOTAAL_BEDRAG_ORDERS
  FROM S_ORD
  WHERE SALES_REP_ID = p_personeelsnummer;
  DBMS_OUTPUT.PUT_LINE('Het totaal bedrag van ' ||p_personeelsnummer || ' bedraagt ' || v_totaal_bedrag_orders);
  RETURN V_TOTAAL_BEDRAG_ORDERS;
END P1_5_TOTAALBEDRAG_ORDERS;
 
FUNCTION P1_7_KLEINSTE_VOORRAAD RETURN NUMBER AS 
  V_PRODUCTID S_PRODUCT.ID%TYPE;
BEGIN
 
  SELECT ID
  INTO V_PRODUCTID
  FROM S_PRODUCT
  WHERE TOTAL_IN_STOCK = (SELECT MIN(TOTAL_IN_STOCK) FROM S_product);
 
  RETURN V_PRODUCTID;
END P1_7_KLEINSTE_VOORRAAD;
 
BEGIN
  SELECT *
    BULK COLLECT INTO t_onafgewerkte_orders
    FROM s_ord
    WHERE order_filled = 'N';
 
    FOR i IN 1 .. t_onafgewerkte_orders.COUNT
    LOOP      
      DBMS_OUTPUT.put_line(t_onafgewerkte_orders(i).id || ' ' || t_onafgewerkte_orders(i).customer_id || ' ' || t_onafgewerkte_orders(i).date_ordered || ' ' || t_onafgewerkte_orders(i).date_ordered);
    END LOOP;
 
END P5_summit_pkg;