hv_labgen_tests ddl

  CREATE TABLE "HV_LABGEN_TESTS"
   (    "SOURCE_CLINIC" VARCHAR(15) NOT NULL,
    "DECPOS" BIGINT,
    "WORKSHEET" BIGINT,
    "WSHSEQ" BIGINT,
    "VOLUME" DECIMAL(6,1),
    "RECNUM" BIGINT,
    "CODE" VARCHAR(4),
    "NAME" VARCHAR(25),
    "ABBR" VARCHAR(5),
    "RESULTTYPE" VARCHAR(1),
    "TYPE_" VARCHAR(1),
    "MALELOW" VARCHAR(8),
    "MALEHIGH" VARCHAR(8),
    "FEMLOW" VARCHAR(8),
    "FEMHIGH" VARCHAR(8),
    "GENLOW" VARCHAR(8),
    "GENHIGH" VARCHAR(8),
    "EXTRANGE" VARCHAR(1),
    "PANICVALUE" VARCHAR(1),
    "CALCTEST" VARCHAR(1),
    "CALCFORMULA" VARCHAR(60),
    "SIGLTSTWSH" VARCHAR(1),
    "TECHID" VARCHAR(4),
    "TIMEDONE" VARCHAR(4),
    "MSG" VARCHAR(1),
    "SEPARATEORD" VARCHAR(1),
    "ONLINEORD" VARCHAR(1),
    "UNITS" VARCHAR(10),
    "REFLAB" VARCHAR(1),
    "DEFAULTS" VARCHAR(8),
    "ALPHANOR" VARCHAR(20),
    "ALPHABNOR" VARCHAR(20),
    "PANICLOW" VARCHAR(8),
    "PANICHIGH" VARCHAR(8),
    "DEPT" VARCHAR(2),
    "TUBETYPE" VARCHAR(2),
    "LABEL" VARCHAR(1),
    "RFLXLOW" VARCHAR(8),
    "RFLXHI" VARCHAR(8),
    "RFLXRES" VARCHAR(8),
    "RFLXOTHER" VARCHAR(8),
    "RFLXTEST" VARCHAR(4),
    "REFTSTCODE" VARCHAR(12),
    "TIMELIMIT" VARCHAR(4),
    "RFLXTEST2" VARCHAR(4),
    "INACTIVE_" VARCHAR(1),
    "ELECORDER" VARCHAR(1),
    "SPECIESSPEC" VARCHAR(4),
    "RFLXINSIDE" VARCHAR(1),
    "PREFIX" VARCHAR(1),
    "BACTISRC" VARCHAR(8),
    "DELTEST" VARCHAR(4),
    "METSCONSISTENT" VARCHAR(1),
    "METHODOLOGY" VARCHAR(4),
    "EXCLUDEFIXEDCOST" VARCHAR(1),
    "TEMPERATURE" VARCHAR(2),
    "MISSINGINFO" VARCHAR(1),
    "LONGNAME" VARCHAR(50),
    "REQUIREMEDS" VARCHAR(1),
    "FILLER2" VARCHAR(186),
    "LASTORDERDATE" DATETIME,
    "FIRSTORDERDATE" DATETIME,
    "DSYS_INSTANCE_ID" BIGINT,
    "DSYS_PROCESS_ID" BIGINT,
    "DSYS_BATCH_ID" DECIMAL(30,0),
    "DEPARTMENT_NAME" VARCHAR(50),
    "LABNAME" VARCHAR(30),
    "CPT_CODE" VARCHAR(8),
    "CODE_W_PREFIX" VARCHAR(10)
   );

  CREATE UNIQUE INDEX "HV_LABGEN_TESTS_AU1" ON "HV_LABGEN_TESTS" ("RECNUM", "SOURCE_CLINIC");

  CREATE INDEX "HV_LABGEN_TESTS_N1" ON "HV_LABGEN_TESTS" ("CODE", "SOURCE_CLINIC");

  CREATE INDEX "HV_LABGEN_TESTS_SYS" ON "HV_LABGEN_TESTS" ("DSYS_INSTANCE_ID", "DSYS_PROCESS_ID", "DSYS_BATCH_ID");

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_TESTS"
INSTEAD OF INSERT OR AS UPDATE ON hv_labgen_tests
FOR EACH ROW
BEGIN

   SET @:NEW.code_w_prefix = hv_labgen_pkg.get_accession_with_prefix
                                (:NEW.source_clinic,
                                 :NEW.code);

END;

/
ALTER TRIGGER "BIU_HV_LABGEN_TESTS" ENABLE;