hv_labgen_patient ddl

  CREATE TABLE "HV_LABGEN_PATIENTS"
   (    "SOURCE_CLINIC" VARCHAR(15) NOT NULL,
    "RECNUM" BIGINT,
    "CLIENT" BIGINT,
    "SUBINS1" BIGINT,
    "SUBINS2" BIGINT,
    "BILLSCH" BIGINT,
    "ORDFLINK" BIGINT,
    "ORDBLINK" BIGINT,
    "LOCATION" BIGINT,
    "LASTPAYSRC" BIGINT,
    "PHYS" BIGINT,
    "CHART" VARCHAR(200),
    "LASTN" VARCHAR(15),
    "FIRSTN" VARCHAR(15),
    "MIDDLE" VARCHAR(200),
    "SEX" VARCHAR(1),
    "PHONE" VARCHAR(13),
    "ROOM" VARCHAR(6),
    "BED" VARCHAR(6),
    "AGE" VARCHAR(3),
    "RACE" VARCHAR(1),
    "ADDRESS1" VARCHAR(25),
    "ADDRESS2" VARCHAR(25),
    "CITY" VARCHAR(25),
    "STATE" VARCHAR(2),
    "ZIP" VARCHAR(9),
    "ILNAME" VARCHAR(15),
    "IFNAME" VARCHAR(15),
    "IADDR1" VARCHAR(35),
    "IADDR2" VARCHAR(35),
    "RELATION" VARCHAR(2),
    "INSID1" VARCHAR(15),
    "CAT1" VARCHAR(5),
    "GROUP1" VARCHAR(15),
    "INSID2" VARCHAR(200),
    "CAT2" VARCHAR(5),
    "GROUP2" VARCHAR(200),
    "DOCCHART" VARCHAR(15),
    "NEXTTIME" VARCHAR(6),
    "LASTDIAG" VARCHAR(6),
    "PERMANENT" VARCHAR(1),
    "ISTATE" VARCHAR(2),
    "IZIP" VARCHAR(9),
    "BILLCMT" VARCHAR(50),
    "INS1" VARCHAR(4),
    "INS2" VARCHAR(4),
    "STATUS" VARCHAR(1),
    "EMPSTATUS" VARCHAR(1),
    "EMPNAME" VARCHAR(20),
    "TYPE_" VARCHAR(1),
    "EXPIRED" VARCHAR(1),
    "IRELATION" VARCHAR(2),
    "ACCSTAT" VARCHAR(5),
    "HRACE" VARCHAR(1),
    "DOSAGE" VARCHAR(10),
    "DOCCHART2" VARCHAR(10),
    "PATROUTE" VARCHAR(5),
    "BREED" VARCHAR(20),
    "SPECIES" VARCHAR(4),
    "AGEMONTHS" VARCHAR(3),
    "SSNUM" VARCHAR(200),
    "EMAIL" VARCHAR(50),
    "IMIDDLE" VARCHAR(500),
    "UPDATETM" VARCHAR(6),
    "UPDATEDBY" VARCHAR(9),
    "DIAGHIST" VARCHAR(8),
    "DOB" DATETIME,
    "ADMITDATE" DATETIME,
    "DISCHGDT" DATETIME,
    "LASTVISIT" DATETIME,
    "LASTBILL" DATETIME,
    "LASTPAY" DATETIME,
    "NEXTVISIT" DATETIME,
    "INS1ISSUDATE" DATETIME,
    "INS2ISSUDATE" DATETIME,
    "UPDATEDT" DATETIME,
    "DSYS_INSTANCE_ID" BIGINT,
    "DSYS_PROCESS_ID" BIGINT,
    "DSYS_BATCH_ID" DECIMAL(30,0),
    "LASTN_NEW" VARCHAR(200),
    "FIRSTN_NEW" VARCHAR(200),
    "PHONE_NEW" VARCHAR(200),
    "ADDRESS1_NEW" VARCHAR(200),
    "ADDRESS2_NEW" VARCHAR(200),
    "CITY_NEW" VARCHAR(200),
    "STATE_NEW" VARCHAR(200),
    "ZIP_NEW" VARCHAR(200),
    "ILNAME_NEW" VARCHAR(200),
    "IFNAME_NEW" VARCHAR(200),
    "IADDR1_NEW" VARCHAR(200),
    "IADDR2_NEW" VARCHAR(200),
    "INSID1_NEW" VARCHAR(200),
    "GROUP1_NEW" VARCHAR(200),
    "ISTATE_NEW" VARCHAR(200),
    "IZIP_NEW" VARCHAR(200),
    "DOB_NEW" VARCHAR(200),
    "ISEX" VARCHAR(5),
    "IDOB" VARCHAR(200),
    "IPHONE" VARCHAR(200),
    "IDOB_NEW" DATETIME,
    "SECONDARY_LAST_NAME" VARCHAR(500),
    "SECONDARY_MIDDLE" VARCHAR(500),
    "SECONDARY_FIRST_NAME" VARCHAR(500),
    "SECONDARY_SSNUM" VARCHAR(500),
    "SECONDARY_SEX" VARCHAR(500),
    "SECONDARY_PHONE" VARCHAR(500),
    "SECONDARY_DOB" VARCHAR(500),
    "SECONDARY_DOB_NEW" DATETIME,
    "ISSNUM" VARCHAR(200),
    "ICITY" VARCHAR(200)
   );

  CREATE UNIQUE INDEX "HV_LABGEN_PATIENTS_AU1" ON "HV_LABGEN_PATIENTS" ("RECNUM", "SOURCE_CLINIC");

  CREATE INDEX "HV_LABGEN_PATIENTS_N1" ON "HV_LABGEN_PATIENTS" ("CHART", "SOURCE_CLINIC");

  CREATE INDEX "HV_LABGEN_PATIENTS_SYS" ON "HV_LABGEN_PATIENTS" ("DSYS_INSTANCE_ID", "DSYS_PROCESS_ID", "DSYS_BATCH_ID");

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_DOB2"
  INSTEAD OF INSERT OR AS UPDATE OF secondary_dob_new ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.secondary_dob        = get_enc_val(CONVERT(VARCHAR(8), :NEW.secondary_dob_new, 112));
   SET @:NEW.secondary_dob_new    = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_DOB2" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_IMIDDLE"
  INSTEAD OF INSERT OR AS UPDATE OF imiddle ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.imiddle        = get_enc_val(:NEW.imiddle);

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_IMIDDLE" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_ISSNUM2"
  INSTEAD OF INSERT OR AS UPDATE OF issnum ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.issnum        = get_enc_val(:NEW.issnum);

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_ISSNUM2" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_SEX2"
  INSTEAD OF INSERT OR AS UPDATE OF secondary_sex ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.secondary_sex        = get_enc_val(:NEW.secondary_sex);

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_SEX2" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_PHONE2"
  INSTEAD OF INSERT OR AS UPDATE OF secondary_phone ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.secondary_phone        = get_enc_val(:NEW.secondary_phone);

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_PHONE2" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_SSNUM2"
  INSTEAD OF INSERT OR AS UPDATE OF secondary_ssnum ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.secondary_ssnum        = get_enc_val(:NEW.secondary_ssnum);

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_SSNUM2" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_FNAME2"
  INSTEAD OF INSERT OR AS UPDATE OF secondary_first_name ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.secondary_first_name        = get_enc_val(:NEW.secondary_first_name);

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_FNAME2" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_MIDDLE2"
  INSTEAD OF INSERT OR AS UPDATE OF secondary_middle ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.secondary_middle        = get_enc_val(:NEW.secondary_middle);

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_MIDDLE2" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_LNAME2"
  INSTEAD OF INSERT OR AS UPDATE OF secondary_last_name ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.secondary_last_name        = get_enc_val(:NEW.secondary_last_name);

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_LNAME2" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_SSNUM"
  INSTEAD OF INSERT OR AS UPDATE OF ssnum ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.ssnum        = get_enc_val(:NEW.ssnum);

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_SSNUM" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_MIDDLE"
  INSTEAD OF INSERT OR AS UPDATE OF middle ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.middle        = get_enc_val(:NEW.middle);

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_MIDDLE" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_IPHONE"
  INSTEAD OF INSERT OR AS UPDATE OF iphone ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.iphone        = get_enc_val(:NEW.iphone);

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_IPHONE" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_INSID2"
  INSTEAD OF INSERT OR AS UPDATE OF insid2 ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.insid2        = get_enc_val(:NEW.insid2);

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_INSID2" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_GROUP2"
  INSTEAD OF INSERT OR AS UPDATE OF group2 ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.group2        = get_enc_val(:NEW.group2);

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_GROUP2" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_CHART"
  INSTEAD OF INSERT OR AS UPDATE OF chart ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.chart        = get_enc_val(:NEW.chart);

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_CHART" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_ZIP"
  INSTEAD OF INSERT OR AS UPDATE OF zip ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.zip_new        = get_enc_val(:NEW.zip);
   SET @:NEW.zip            = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_ZIP" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_STATE"
  INSTEAD OF INSERT OR AS UPDATE OF state ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.state_new        = get_enc_val(:NEW.state);
   SET @:NEW.state            = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_STATE" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_PHONE"
  INSTEAD OF INSERT OR AS UPDATE OF phone ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.phone_new        = get_enc_val(:NEW.phone);
   SET @:NEW.phone            = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_PHONE" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_LASTN"
  INSTEAD OF INSERT OR AS UPDATE OF lastn ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.lastn_new        = get_enc_val(:NEW.lastn);
   SET @:NEW.lastn            = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_LASTN" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_ISTATE"
  INSTEAD OF INSERT OR AS UPDATE OF istate ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.istate_new        = get_enc_val(:NEW.istate);
   SET @:NEW.istate            = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_ISTATE" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_INSID1"
  INSTEAD OF INSERT OR AS UPDATE OF insid1 ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.insid1_new        = get_enc_val(:NEW.insid1);
   SET @:NEW.insid1            = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_INSID1" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_ILNAME"
  INSTEAD OF INSERT OR AS UPDATE OF ilname ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.ilname_new        = get_enc_val(:NEW.ilname);
   SET @:NEW.ilname            = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_ILNAME" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_IFNAME"
  INSTEAD OF INSERT OR AS UPDATE OF ifname ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.ifname_new        = get_enc_val(:NEW.ifname);
   SET @:NEW.ifname            = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_IFNAME" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_IDOB"
  INSTEAD OF INSERT OR AS UPDATE OF idob ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.idob        = get_enc_val(CONVERT(VARCHAR(8), :NEW.idob_new, 112));
   SET @:NEW.idob_new    = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_IDOB" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_IADDR2"
  INSTEAD OF INSERT OR AS UPDATE OF iaddr2 ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.iaddr2_new        = get_enc_val(:NEW.iaddr2);
   SET @:NEW.iaddr2            = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_IADDR2" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_IZIP"
  INSTEAD OF INSERT OR AS UPDATE OF izip ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.izip_new        = get_enc_val(:NEW.izip);
   SET @:NEW.izip            = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_IZIP" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_IADDR1"
  INSTEAD OF INSERT OR AS UPDATE OF iaddr1 ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.iaddr1_new        = get_enc_val(:NEW.iaddr1);
   SET @:NEW.iaddr1            = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_IADDR1" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_CITY"
  INSTEAD OF INSERT OR AS UPDATE OF city ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.city_new        = get_enc_val(:NEW.city);
   SET @:NEW.city            = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_CITY" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_ADDR1"
  INSTEAD OF INSERT OR AS UPDATE OF address1 ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.address1_new        = get_enc_val(:NEW.address1);
   SET @:NEW.address1            = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_ADDR1" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_GROUP1"
  INSTEAD OF INSERT OR AS UPDATE OF group1 ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.group1_new        = get_enc_val(:NEW.group1);
   SET @:NEW.group1            = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_GROUP1" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_ADDR2"
  INSTEAD OF INSERT OR AS UPDATE OF address2 ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.address2_new        = get_enc_val(:NEW.address2);
   SET @:NEW.address2            = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_ADDR2" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_DOB"
  INSTEAD OF INSERT OR AS UPDATE OF dob ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.dob_new        = get_enc_val(TO_CHAR(:NEW.dob, 'DD-MON-YYYY'));
   SET @:NEW.dob            = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_DOB" ENABLE;

  CREATE OR REPLACE TRIGGER "BIU_HV_LABGEN_PATIENT_FIRSTN"
  INSTEAD OF INSERT OR AS UPDATE OF firstn ON hv_labgen_patients
  FOR EACH ROW

BEGIN

   SET @:NEW.firstn_new        = get_enc_val(:NEW.firstn);
   SET @:NEW.firstn            = NULL;

END;
/
ALTER TRIGGER "BIU_HV_LABGEN_PATIENT_FIRSTN" ENABLE;