hv_d_npi_list ddl

  CREATE TABLE "HV_D_NPI_LIST"
   (    "FILE_ID" FLOAT NOT NULL,
    "FILE_NAME" VARCHAR(100),
    "MIME_TYPE" VARCHAR(100),
    "LAST_UPDATED" DATETIME,
    "CHARACTER_SET" VARCHAR(100),
    "FILE_DOCUMENT" VARBINARY(max),
    "REFERRING_PROVIDER_NAME" VARCHAR(100),
    "REFERRING_PROVIDER_NPI" DECIMAL(38,5),
    "CLINIC_NAME" VARCHAR(100),
    "REFERRING_PROVIDER_PHONE" VARCHAR(50),
    "REFERRING_PROVIDER_ADDRESS_1" VARCHAR(100),
    "REFERRING_PROVIDER_ADDRESS_2" VARCHAR(50),
    "REFERRING_PROVIDER_CITY" VARCHAR(50),
    "REFERRING_PROVIDER_STATE" VARCHAR(50),
    "REFERRING_PROVIDER_ZIP" VARCHAR(50),
    "CORDURO_PROVIDER_NAME" VARCHAR(50),
    "FROM_DATE" DATETIME,
    "TO_DATE" DATETIME,
    "GROUP" VARCHAR(50),
    "DSYS_INSTANCE_ID" BIGINT,
    "DSYS_PROCESS_ID" BIGINT,
    "DSYS_BATCH_ID" DECIMAL(30,0),
    "PRACTICE" VARCHAR(50),
    "PROVIDER_NOTES" VARCHAR(2000),
    "COMPANY_IDENTIFIER" FLOAT,
    "CREATION_DATE" DATETIME2 (6),
    "CREATED_BY" VARCHAR(100),
    "LAST_UPDATE_DATE" DATETIME2 (6),
    "LAST_UPDATED_BY" VARCHAR(100),
    "PROVIDER_ID" FLOAT,
    "CLINIC_ID" FLOAT,
    "ACCOUNT_REP" VARCHAR(50),
    "PROVIDER_FIRST_NAME" VARCHAR(50),
    "PROVIDER_LAST_NAME" VARCHAR(50),
    "PROVIDER_MIDDLE_NAME" VARCHAR(50),
    "PROVIDER_SUFFIX" VARCHAR(50),
    "PROVIDER_TITLE" VARCHAR(50),
    "LABGEN_USERID" VARCHAR(50),
    "LABGEN_PASSWORD" VARCHAR(50),
    "SPECIALITY" VARCHAR(50),
    "AGREEMENT_BLOOD" VARCHAR(1),
    "AGREEMENT_TOX" VARCHAR(1),
    "AGREEMENT_PGX" VARCHAR(1),
    "AGREEMENT_PHARMACY" VARCHAR(1),
    "COMMENTS" VARCHAR(300),
    "FILE_NAME1" VARCHAR(100),
    "MIME_TYPE1" VARCHAR(100),
    "LAST_UPDATED1" DATETIME,
    "CHARACTER_SET1" VARCHAR(100),
    "FILE_DOCUMENT1" VARBINARY(max),
    "BLOOD" VARCHAR(1),
    "TOX" VARCHAR(1),
    "PGX" VARCHAR(1),
    "PHARMACY" VARCHAR(1),
    "CANCER" VARCHAR(1),
    "CARRIER" VARCHAR(1),
    "RPP" VARCHAR(1),
    "GI" VARCHAR(1),
    "NIPT" VARCHAR(1),
    "BLOOD_Volume" FLOAT,
    "TOX_Volume" FLOAT,
    "PGX_Volume" FLOAT,
    "PHARMACY_Volume" FLOAT,
    "CANCER_VOLUME" FLOAT,
    "CARRIER_VOLUME" FLOAT,
    "RPP_VOLUME" FLOAT,
    "GI_VOLUME" FLOAT,
    "NIPT_VOLUME" FLOAT,
    "ACCOUNT_REP_EMAIL" VARCHAR(150),
    "START_DATE" DATETIME,
    "LATITUDE" FLOAT,
    "LONGITUDE" FLOAT
   )
    KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
 LOB ("FILE_DOCUMENT1") STORE AS SECUREFILE (
  TABLESPACE "HV_DATA" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

  CREATE INDEX "HV_D_NPI_LIST_PK_1" ON "HV_D_NPI_LIST" ("REFERRING_PROVIDER_NPI", "FROM_DATE", "TO_DATE");

  CREATE UNIQUE INDEX "NPI_DOCUMENTS_TEMP_INDEX1" ON "HV_D_NPI_LIST" ("FILE_ID");

  CREATE OR REPLACE TRIGGER "BU_NPI_Encrypt" on "HV_D_NPI_LIST"
    instead of update
       as
    begin
            SET @:new.REFERRING_PROVIDER_NAME = Isnull(:new.provider_last_name, '') + ' , ' + Isnull(:new.provider_first_name, '');
            IF :new.labgen_password  <> ' '  AND :new.labgen_password <> isnull(:old.labgen_password,' ')    BEGIN
            Set @:new.labgen_password = get_enc_val(:new.labgen_password);
            END
END
END;

GO
ALTER TRIGGER "BU_NPI_Encrypt" ENABLE;

  CREATE OR REPLACE TRIGGER "TRG_FILE_ID" on "HV_D_NPI_LIST"
    instead of insert
       as
    begin
      select file_id_seq.nextval
        into :new.file_id
        from dual;
If :new.REFERRING_PROVIDER_NAME <> '' BEGIN
    SET @:new.REFERRING_PROVIDER_NAME = Isnull(:new.provider_last_name, '') + ' , ' + Isnull(:new.provider_first_name, '');
END
      Set @:new.labgen_password = get_enc_val(:new.labgen_password);
    end
    end;

GO
ALTER TRIGGER "TRG_FILE_ID" ENABLE;

  CREATE OR REPLACE TRIGGER "BU_HV_D_NPI_LIST" on "HV_D_NPI_LIST"
  instead of update
   as


    DECLARE @v_user     VARCHAR(100)    = ISNULL(v('APP_USER'),'DATASYNC');
WHILE 1=0 BEGIN
IF @@FETCH_STATUS <> 0 BREAK;

begin
  SET @:NEW.last_updated_by = v_user;
  SET @:NEW.last_update_date = GETDATE();
end;
/
ALTER TRIGGER "BU_HV_D_NPI_LIST" ENABLE;

  CREATE OR REPLACE TRIGGER "BI_HV_D_NPI_LIST" on "HV_D_NPI_LIST"
  instead of insert
   as


    DECLARE @v_user     VARCHAR(100)    = ISNULL(v('APP_USER'),'DATASYNC');
WHILE 1=0 BEGIN
IF @@FETCH_STATUS <> 0 BREAK;

begin
  SET @:NEW.created_by = v_user;
  SET @:NEW.creation_date = GETDATE();
  SET @:NEW.last_updated_by = v_user;
  SET @:NEW.last_update_date = GETDATE();
end;
/
ALTER TRIGGER "BI_HV_D_NPI_LIST" ENABLE;