ADD_COMPLAINT.sql

create or replace PROCEDURE ADD_COMPLAINT(PROP_ID IN NUMBER,
COMPL_TYPE_ID IN NUMBER,
COMP_DATE DATE,
COMP_STATUS VARCHAR,
C_DESC VARCHAR,
C_SEVERITY VARCHAR,
C_PRIORITY IN NUMBER)

IS 

MAX_COMP_ID NUMBER;
NEW_COMP_ID NUMBER;
PROPERTY_ID NUMBER;
COMPLAINT_TYPE_ID NUMBER;

PROPERTY_ID_EXCEPTION EXCEPTION;
COMPLAINT_TYPE_ID_EXCEPTION EXCEPTION;
--COMP_DATE_EXCEPTION EXCEPTION;
COMP_STATUS_EXCEPTION EXCEPTION;
COMP_SEVERITY_EXCEPTION EXCEPTION;
COMP_PRIORITY_EXCEPTION EXCEPTION;
COMP_PRIORITY_NUM_EXCEPTION EXCEPTION;

BEGIN
Select count(*) INTO PROPERTY_ID from PROPERTY where PROP_ID=PROPERTY.P_ID;
Select count(*) into COMPLAINT_TYPE_ID from COMPLAINT_TYPE where COMPL_TYPE_ID=COMPLAINT_TYPE.COMP_TYPE_ID;

SELECT NVL(MAX(COMP_ID),0) INTO MAX_COMP_ID FROM COMPLAINT; 
NEW_COMP_ID := MAX_COMP_ID +1; 

IF (PROPERTY_ID=0) THEN
    RAISE PROPERTY_ID_EXCEPTION;
ELSIF (COMPLAINT_TYPE_ID=0) THEN
    RAISE COMPLAINT_TYPE_ID_EXCEPTION;
--ELSIF COMP_DATE BETWEEN LEASE.L_STRTDATE AND LEASE.L_ENDDATE THEN
 --   RAISE COMP_DATE_EXCEPTION;
ELSIF COMP_STATUS NOT IN ('OPEN') THEN
    RAISE COMP_STATUS_EXCEPTION;  
ELSIF C_SEVERITY NOT IN ('LOW','MEDIUM','HIGH') THEN
    RAISE COMP_SEVERITY_EXCEPTION;
ELSIF C_PRIORITY<1 OR C_PRIORITY>11 THEN
    RAISE COMP_PRIORITY_EXCEPTION;
ELSIF is_number(C_PRIORITY)=0  THEN
    RAISE COMP_PRIORITY_NUM_EXCEPTION;
ELSE
INSERT INTO COMPLAINT(COMP_ID,FK_COMPLAINT_P_ID,FK_COMP_TYPE_ID,C_DATE,C_STATUS,COMP_DESC,COMP_SEVERITY,COMP_PRIORITY)
VALUES(NEW_COMP_ID,PROP_ID,COMPL_TYPE_ID,COMP_DATE,COMP_STATUS,C_DESC,C_SEVERITY,C_PRIORITY);
COMMIT;
END IF;

EXCEPTION
    WHEN PROPERTY_ID_EXCEPTION THEN
        DBMS_OUTPUT.PUT_LINE('PROPERTY ID NOT PRESENT IN PROPERTY TABLE');
    WHEN COMPLAINT_TYPE_ID_EXCEPTION THEN
        DBMS_OUTPUT.PUT_LINE('COMPLAINT TYPE ID NOT PRESENT IN COMPLAINT TYPE TABLE');
 --   WHEN COMP_DATE_EXCEPTION THEN
  --      DBMS_OUTPUT.PUT_LINE('COMPLAINT DATE SHOULD BE BETWEEN LEASE START DATE AND LEASE END DATE');
    WHEN COMP_STATUS_EXCEPTION THEN
        DBMS_OUTPUT.PUT_LINE('COMPLAINT STATUS SHOULD BE OPEN');
    WHEN COMP_SEVERITY_EXCEPTION THEN
        DBMS_OUTPUT.PUT_LINE('COMPLAINT SEVERITY SHOULD BE LOW,MEDIUM OR HIGH');
    WHEN COMP_PRIORITY_EXCEPTION THEN
        DBMS_OUTPUT.PUT_LINE('COMPLAINT PRIORITY SHOULD BE BETWEEN 1 TO 10'); 
    WHEN COMP_PRIORITY_NUM_EXCEPTION THEN
        DBMS_OUTPUT.PUT_LINE('COMPLAINT PRIORITY SHOULD BE NUMBER');
END;