CREATE GENERATOR GEN_EMCSCRIBBLE_KEY;

CREATE TABLE EMCSCRIBBLE 
(
  EMCSCRIBBLE_KEY	INTEGER 	NOT NULL,
  ACCOUNT_KEY		ACCOUNTNO 	NOT NULL,
  PATIENT_KEY		INTEGER 	NOT NULL,
  CAPDATE		DATE		NOT NULL,
  SCRIBBLEHEADER	VARCHAR(50)	NOT NULL,
  SCRIBBLE		BLOB SUB_TYPE 0 SEGMENT SIZE 80,
  TRACKINGNO		INTEGER NOT NULL,
  PRIMARY KEY(EMCSCRIBBLE_KEY)
);

SET TERM ^;

CREATE PROCEDURE SP_EMCSCRIBBLE
(
  PAR_STATUS 		VARCHAR(1),
  PAR_HFUSERNAME 	VARCHAR(15),
  PAR_HFUSERPASSWORD	VARCHAR(10),
  PAR_EMCSCRIBBLE_KEY 	INTEGER,
  PAR_ACCOUNT_KEY 	VARCHAR(7),
  PAR_PATIENT_KEY 	INTEGER,
  PAR_CAPDATE		DATE,
  PAR_SCRIBBLEHEADER	VARCHAR(50),
  PAR_SCRIBBLE 		BLOB,
  PAR_TRACKINGNO	INTEGER
)
RETURNS
(
  RET_EMCSCRIBBLE_KEY INTEGER
)
AS
 DECLARE VARIABLE TRACKINGNO 		INTEGER;
 DECLARE VARIABLE EMCSCRIBBLE_KEY       INTEGER;
BEGIN

  IF (PAR_STATUS = 'I') THEN
  BEGIN
    EMCSCRIBBLE_KEY = GEN_ID(GEN_EMCSCRIBBLE_KEY, 1);
    
    INSERT INTO EMCSCRIBBLE(EMCSCRIBBLE_KEY, ACCOUNT_KEY, PATIENT_KEY, CAPDATE, SCRIBBLEHEADER, SCRIBBLE, TRACKINGNO)
    VALUES (:EMCSCRIBBLE_KEY, :PAR_ACCOUNT_KEY, :PAR_PATIENT_KEY, 'TODAY', :PAR_SCRIBBLEHEADER, :PAR_SCRIBBLE, -1);

    RET_EMCSCRIBBLE_KEY = :EMCSCRIBBLE_KEY;
  END
  ELSE
  IF (PAR_STATUS = 'U') THEN
  BEGIN
    UPDATE EMCSCRIBBLE SET
      SCRIBBLEHEADER = :PAR_SCRIBBLEHEADER,
      SCRIBBLE       = :PAR_SCRIBBLE,
      TRACKINGNO     = :PAR_TRACKINGNO
    WHERE EMCSCRIBBLE_KEY = :PAR_EMCSCRIBBLE_KEY;

    RET_EMCSCRIBBLE_KEY = :EMCSCRIBBLE_KEY;
  END
  ELSE
  IF (PAR_STATUS = 'D') THEN
  BEGIN
    RET_EMCSCRIBBLE_KEY = :EMCSCRIBBLE_KEY;
  END
  
  SUSPEND;
END
 ^