CREATE GENERATOR GEN_EMCNOTES_KEY;

CREATE TABLE EMCNOTES
(
 EMCNOTES_KEY	INTEGER		NOT NULL,
 ACCOUNT_KEY	VARCHAR(7)	NOT NULL,
 PATIENT_KEY	INTEGER		NOT NULL,
 CAPDATE	DATE		NOT NULL,
 NOTESHEADER	VARCHAR(50)	NOT NULL,
 NOTES		BLOB 	 SUB_TYPE 0 SEGMENT SIZE 80,
 TRACKINGNO	INTEGER		NOT NULL,
 PRIMARY KEY(EMCNOTES_KEY),
 FOREIGN KEY(ACCOUNT_KEY, PATIENT_KEY) REFERENCES PATIENT(ACCOUNT_KEY, PATIENT_KEY)
);

SET TERM ^;


CREATE TRIGGER TG_INSERT_EMCNOTES FOR EMCNOTES 
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  NEW.TRACKINGNO = GEN_ID(GEN_TRACKINGNO, 1);
END
^

CREATE TRIGGER TG_UPDATE_EMCNOTES FOR EMCNOTES
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
  IF (NEW.EMCNOTES_KEY <> OLD.EMCNOTES_KEY) THEN
  BEGIN
    EXCEPTION EXC_KEY_ALTERED;
  END

  IF (NEW.TRACKINGNO <> OLD.TRACKINGNO) THEN
  BEGIN
    EXCEPTION EXC_RECORD_ALTERED;
  END

  NEW.TRACKINGNO = GEN_ID (GEN_TRACKINGNO, 1);

END
 ^

CREATE PROCEDURE SP_EMCNOTES 
(
  PAR_STATUS 		VARCHAR(1),
  PAR_HFUSERNAME 	VARCHAR(15),
  PAR_HFUSERPASSWORD 	VARCHAR(10),
  PAR_EMCNOTES_KEY	INTEGER,
  PAR_ACCOUNT_KEY	VARCHAR(7),
  PAR_PATIENT_KEY 	INTEGER,
  PAR_CAPDATE		DATE,
  PAR_NOTESHEADER	VARCHAR(50),
  PAR_NOTES 		BLOB,
  PAR_TRACKINGNO	INTEGER
)
RETURNS
(
  RET_EMCNOTES_KEY 	INTEGER
)
AS
  DECLARE VARIABLE EMCNOTES_KEY	INTEGER;
  DECLARE VARIABLE TRACKINGNO 	INTEGER;
BEGIN
  
  IF (PAR_STATUS = 'I') THEN
  BEGIN
    EMCNOTES_KEY = GEN_ID(GEN_EMCNOTES_KEY, 1);

    INSERT INTO EMCNOTES
    (EMCNOTES_KEY, ACCOUNT_KEY, PATIENT_KEY, CAPDATE, NOTESHEADER, NOTES, TRACKINGNO)
    VALUES
    (:EMCNOTES_KEY, :PAR_ACCOUNT_KEY, :PAR_PATIENT_KEY, 'TODAY', :PAR_NOTESHEADER, :PAR_NOTES, -1);
 
    RET_EMCNOTES_KEY = :EMCNOTES_KEY;
  END
  ELSE
  IF (PAR_STATUS = 'U') THEN
  BEGIN
    UPDATE EMCNOTES SET
      CAPDATE     = 'TODAY', 
      NOTESHEADER = :PAR_NOTESHEADER, 
      NOTES	  = :PAR_NOTES,
      TRACKINGNO  = :PAR_TRACKINGNO
    WHERE EMCNOTES_KEY = :PAR_EMCNOTES_KEY;
    
    RET_EMCNOTES_KEY = :EMCNOTES_KEY;
  END
  ELSE
  IF (PAR_STATUS = 'D') THEN
  BEGIN
    RET_EMCNOTES_KEY = :EMCNOTES_KEY;
  END
  
  SUSPEND;

END
 ^

SET TERM ;^

GRANT SELECT ON EMCNOTES TO PUBLIC;
GRANT ALL ON EMCNOTES TO PROCEDURE SP_EMCNOTES;
GRANT EXECUTE ON PROCEDURE SP_EMCNOTES TO PUBLIC;