zerfz

CREATE OR REPLACE PROCEDURE P4_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;
 
  E_NOPARENT EXCEPTION;
  PRAGMA EXCEPTION_INIT(E_NOPARENT,-2291);
  e_check_constraint EXCEPTION;
  PRAGMA EXCEPTION_INIT(E_CHECK_CONSTRAINT,-2290);
 
  E_SALES_REP_ERROR EXCEPTION;
 
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
    RAISE E_SALES_REP_ERROR;
  END IF;
  COMMIT;
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE('ID van dit order bestaat al');
    WHEN E_NOPARENT THEN
      DBMS_OUTPUT.PUT_LINE('FK Is foutief');
    WHEN E_CHECK_CONSTRAINT THEN
      DBMS_OUTPUT.PUT_LINE('Het type van betaling moet CASH OF CREDIT ZIJN');
    WHEN E_SALES_REP_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('CUSTUMERID Moet een sales rep zijn.');
    WHEN OTHERS THEN
      raise_application_error( -20000, 'Onbekende fout opgetreden');
 
END P4_2_VOEG_ORDER_TOE;