CREATE GENERATOR GEN_NATURALFORMTYPE_KEY;
CREATE GENERATOR GEN_NATURALFORMS_KEY;
CREATE GENERATOR GEN_NATURALFORMSPATIENT_KEY;
 
CREATE TABLE NATURALFORMTYPE
(
  NATURALFORMTYPE_KEY	INTEGER		NOT NULL,
  NATURALFORMTYPE_DESC	VARCHAR(50)	NOT NULL,
  NATURALFORMTYPE_ID	INTEGER		NOT NULL,
  PATIENTCAPTURE	TRUEFALSEENTRY DEFAULT 'F' NOT NULL,
  TRACKINGNO		INTEGER		NOT NULL,
  PRIMARY KEY(NATURALFORMTYPE_KEY)
);

CREATE TABLE NATURALFORMS
(
  NATURALFORMS_KEY	INTEGER		NOT NULL,
  ACCOUNT_KEY		VARCHAR(7)	NOT NULL,
  PATIENT_KEY		INTEGER		NOT NULL,
  NATURALFORMTYPE_KEY	INTEGER		NOT NULL,
  CAPDATE		DATE,
  CAPTIME		TIME,	
  TRACKINGNO		INTEGER		NOT NULL,
  PRIMARY KEY(NATURALFORMS_KEY)
);

SET TERM ^;

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

CREATE TRIGGER TG_UPDATE_NATURALFORMTYPE FOR NATURALFORMTYPE
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
  IF (NEW.NATURALFORMTYPE_KEY <> OLD.NATURALFORMTYPE_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 TRIGGER TG_INSERT_NATURALFORMS FOR NATURALFORMS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  NEW.TRACKINGNO = GEN_ID(GEN_TRACKINGNO, 1);
END
^

CREATE TRIGGER TG_UPDATE_NATURALFORMS FOR NATURALFORMS
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
  IF (NEW.NATURALFORMS_KEY <> OLD.NATURALFORMS_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_NATURALFORMS
(
  PAR_STATUS 			VARCHAR(1),
  PAR_HFUSERNAME 		VARCHAR(15),
  PAR_HFUSERPASSWORD 		VARCHAR(10),
  PAR_NATURALFORMS_KEY		INTEGER,
  PAR_ACCOUNT_KEY		VARCHAR(7),
  PAR_PATIENT_KEY		INTEGER,
  PAR_NATURALFORMTYPE_KEY	INTEGER,
  PAR_CAPDATE			DATE,
  PAR_CAPTIME			TIME,	
  PAR_TRACKINGNO		INTEGER,
  PAR_FORMNAME 			VARCHAR(50)
)
RETURNS
(
  RET_NATURALFORMS_KEY 		INTEGER
)
AS
  DECLARE VARIABLE RECORDCOUNT NUMERIC(15);

BEGIN

  RET_NATURALFORMS_KEY = NULL;      

  IF (PAR_STATUS = 'I') THEN
  BEGIN
    SELECT COUNT(*) FROM NATURALFORMS WHERE NATURALFORMS_KEY = :PAR_NATURALFORMS_KEY INTO :RECORDCOUNT;
    
    IF (RECORDCOUNT = 0) THEN
    BEGIN
      PAR_NATURALFORMS_KEY = GEN_ID(GEN_NATURALFORMS_KEY, 1);

      INSERT INTO NATURALFORMS
      (NATURALFORMS_KEY, ACCOUNT_KEY, PATIENT_KEY, NATURALFORMTYPE_KEY, CAPDATE, CAPTIME, TRACKINGNO)
      VALUES
      (:PAR_NATURALFORMS_KEY, :PAR_ACCOUNT_KEY, :PAR_PATIENT_KEY, :PAR_NATURALFORMTYPE_KEY, 'TODAY', 'NOW', -1);

      RET_NATURALFORMS_KEY = :PAR_NATURALFORMS_KEY;
    END
    ELSE
    BEGIN
      EXCEPTION EXC_RECORD_EXISTS;
    END
  END
  ELSE
  BEGIN
    EXCEPTION EXC_INVALID_STATUS; 
  END

END
 ^