ee

CREATE OR REPLACE
PACKAGE P5_summit_pkg AS
  e_no_fk_constraint EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_no_fk_constraint,-2291);
 
  TYPE type_coll_onaf_orders IS
  TABLE OF s_ord%ROWTYPE INDEX BY PLS_INTEGER;
  t_onafgewerkte_orders type_coll_onaf_orders;
 
  CURSOR C_Overzicht_P_WH IS
  SELECT PRODUCT_ID, 
    (SELECT NAME FROM S_PRODUCT WHERE ID= PRODUCT_ID) NAME, WAREHOUSE_ID, 
    (SELECT CITY FROM S_WAREHOUSE WHERE ID=WAREHOUSE_ID)
  FROM S_INVENTORY
  ORDER BY WAREHOUSE_ID, NAME;
 
 
  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
);
 
  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  
  );
 
    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 
  );
 
  PROCEDURE P2_2_HOOGSTE_VOORRAADWAARDE;
  PROCEDURE P4_4_VERWIJDER_MEDEWERKER(
  MED_ID IN S_EMP.ID%TYPE);
  PROCEDURE P4_4_VERWIJDER_MEDEWERKER(
  P_LAST_NAME IN S_EMP.LAST_NAME%TYPE,
  P_FIRST_NAME IN S_EMP.FIRST_NAME%TYPE);
 
 
 
  PROCEDURE VERWIJDER_ORDER
  (p_ordid IN s_ord.id%TYPE);
 
  FUNCTION P1_5_TOTAALBEDRAG_ORDERS 
  (P_PERSONEELSNUMMER IN PLS_INTEGER)RETURN NUMBER;
 
  FUNCTION P1_7_KLEINSTE_VOORRAAD RETURN NUMBER;
 
END P5_summit_pkg;