Tech Notes:

SQL Server Scripts:

OPSROOT

CREATE TABLE [dbo].[ZIPCODES]([ZIPCODE] [nvarchar](10) NOT NULL,[CITY] [nvarchar](100) NULL,[STATE] [nvarchar](10) NULL,[COUNTY] [nvarchar](100) NULL,[TIMEZONE] [nvarchar](100) NULL,) ON [PRIMARY]

The Zipcodes table is then populated with the US Zip codes.

FACILITY DATABASES

Record added to EventType: Late Sample, SystemClass=4

ALTER TABLE CHART ADD YAXISTYPE [numeric](18, 0) NOT NULL DEFAULT (0)
ALTER TABLE CHART ADD LINESON2NDY [numeric](18, 0) NOT NULL DEFAULT (0)
ALTER TABLE CHART ADD YAXISLABEL NVARCHAR(100) NULL DEFAULT (NULL)
ALTER TABLE CHART ADD RYAXISLABEL NVARCHAR(100) NULL DEFAULT (NULL)

CREATE TABLE [dbo].[NOTIFICATIONLIST]([ID] [int] IDENTITY(1,1) NOT NULL,[AUDITUSER] [varchar](50) NULL,[AUDITTIMESTAMP] [datetime] NULL DEFAULT (getdate()),[NAME] [nvarchar](100) NULL,[LIST] NVARCHAR (max) NULL,[NOTES] [nvarchar](500) NULL) ON [PRIMARY]

CREATE TABLE [dbo].[LC_TESTQC]([AUDITUSER] [varchar](50) NULL,[AUDITTIMESTAMP] [datetime] NULL DEFAULT (getdate()),[TESTID] [numeric](18, 0) NOT NULL DEFAULT ((0)),[VARID] [numeric](18, 0) NULL DEFAULT ((0)),[NAME] [nvarchar](100) NULL,[QCTYPE] [nvarchar](100) NULL,[SETVAL] [float] NULL,[NOTES] [nvarchar](500) NULL) ON [PRIMARY]

ALTER TABLE VARDESC ADD TextVarFormat NVARCHAR(30) NULL DEFAULT (NULL)
UPDATE VARDESC SET TextVarFormat='TEXT ENTRY' WHERE VARTYPE IN ('T','X','Q','B','E','R','A') AND LIMIT_TO_LIST = 0 AND (LIST IS NULL OR LEN(CAST(LIST AS nvarchar(max))) = 0
UPDATE VARDESC SET TextVarFormat='DROP DOWN LIST' WHERE VARTYPE IN ('T','X','Q','B','E','R','A') AND LIMIT_TO_LIST = 0 AND (LIST IS NOT NULL AND LEN(CAST(LIST AS nvarchar(max))) > 0
UPDATE VARDESC SET TextVarFormat='DROP DOWN LIMIT TO LIST' WHERE VARTYPE IN ('T','X','Q','B','E','R','A') AND LIMIT_TO_LIST = -1 AND (LIST IS NOT NULL AND LEN(CAST(LIST AS nvarchar(max))) > 0

ALTER TABLE VARDESC ADD ENTRYNOTIFICATIONLISTID [numeric](18, 0) NULL DEFAULT (NULL)
ALTER TABLE VARDESC ADD REGNOTIFICATIONLISTID [numeric](18, 0) NULL DEFAULT (NULL)
ALTER TABLE VARDESC ADD QCNOTIFICATIONLISTID [numeric](18, 0) NULL DEFAULT (NULL)

ALTER TABLE LC_SAMPLEDEF ADD SCHDSTARTDATE NVARCHAR(10) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLEDEF ADD SCHDENDDATE NVARCHAR(10) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLEDEF ALTER COLUMN NAME NVARCHAR(200)
ALTER TABLE LC_SAMPLEDEF ADD ADHOC [numeric](18, 0) NOT NULL DEFAULT (0)
ALTER TABLE LC_SAMPLEDEF ADD DUENOTIFICATIONLISTID [numeric](18, 0) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLEDEF ADD LATENOTIFICATIONLISTID [numeric](18, 0) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLEDEF ADD LATENOTIFYAFTER [numeric](18, 0) NULL DEFAULT (NULL)

ALTER TABLE LOCATION ADD ADHOC [numeric](18, 0) NOT NULL DEFAULT (0)

ALTER TABLE LC_SAMPLEDEF ALTER COLUMN NAME NVARCHAR(200)
ALTER TABLE LC_SAMPLEDEF ADD ADHOC [numeric](18, 0) NOT NULL DEFAULT (0)

ALTER TABLE LC_SAMPLE ADD SAMPLENAME NVARCHAR(200) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLE ADD SAMPLEDBYNAME NVARCHAR(120) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLE ADD RCVDBYNAME NVARCHAR(120) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLE ADD SAMPLELOCATION NVARCHAR(250) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLE ADD ADHOC [numeric](18, 0) NOT NULL DEFAULT (0)
ALTER TABLE LC_SAMPLE ADD LATEEMAILSENT DATETIME NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLE ADD DUEEMAILSENT DATETIME NULL DEFAULT (NULL)

UPDATE LC_SAMPLE SET RCVDBYNAME = LC_PEOPLE.NAME FROM LC_SAMPLE INNER JOIN LC_PEOPLE ON LC_SAMPLE.RECEIVEDBY=LC_PEOPLE.ID  
UPDATE LC_SAMPLE SET SAMPLEDBYNAME = LC_PEOPLE.NAME FROM LC_SAMPLE INNER JOIN LC_PEOPLE ON LC_SAMPLE.SAMPLEDBY=LC_PEOPLE.ID
UPDATE LC_SAMPLE SET SAMPLELOCATION = LOCATION.PATH FROM LC_SAMPLE INNER JOIN LOCATION ON LC_SAMPLE.LOCATIONID=LOCATION.LOCID

ORACLE:

OPSROOT

CREATE TABLE "ZIPCODES" ("ZIPCODE" VARCHAR2(10) NOT NULL, "CITY" VARCHAR2(100) NULL, "STATE" NVARCHAR2 (10) NULL,"COUNTY" NVARCHAR2 (100) NULL,"TIMEZONE" NVARCHAR2 (100) NULL)

The Zipcodes table is then populated with the US Zip codes.

FACILITY DATABASES

Record added to EventType: Late Sample, SystemClass=4

ALTER TABLE CHART ADD YAXISTYPE NUMBER DEFAULT 0 NOT NULL
ALTER TABLE CHART ADD LINESON2NDY NUMBER DEFAULT 0 NOT NULL
ALTER TABLE CHART ADD YAXISLABEL NVARCHAR2(100) NULL
ALTER TABLE CHART ADD RYAXISLABEL NVARCHAR2(100) NULL

CREATE TABLE "NOTIFICATIONLIST"("ID" NUMBER NOT NULL, "AUDITUSER" VARCHAR2(50) NULL, "AUDITTIMESTAMP" DATE DEFAULT NULL, "NAME" nvarchar2 (100) NULL,"LIST" nvarchar2 (1000) NULL,"NOTES" nvarchar2 (500) NULL)
CREATE SEQUENCE "A_LC_NOTIFICATIONLIST" INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE CACHE 20 NOORDER
CREATE TRIGGER "T_LC_NOTIFICATIONLIST" BEFORE INSERT ON "NOTIFICATIONLIST" FOR EACH ROW BEGIN SELECT "A_LC_NOTIFICATIONLIST".NEXTVAL INTO :NEW."ID" FROM DUAL; END T_LC_NOTIFICATIONLIST;

CREATE TABLE "LC_TESTQC"("AUDITUSER" VARCHAR2(50) NULL, "AUDITTIMESTAMP" DATE DEFAULT NULL, "TESTID" NUMBER NOT NULL, "VARID" NUMBER NOT NULL, "NAME" nvarchar2 (100) NULL,"QCTYPE" nvarchar2 (100) NULL,"SETVAL" NUMBER NULL, "NOTES" nvarchar2 (500) NULL)
CREATE SEQUENCE "A_LC_TESTQC" INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE CACHE 20 NOORDER

CREATE TRIGGER "T_LC_TESTQC" BEFORE INSERT ON "LC_TESTQC" FOR EACH ROW BEGIN SELECT "A_LC_TESTQC".NEXTVAL INTO :NEW."ID" FROM DUAL; END T_LC_TESTQC;

ALTER TABLE VARDESC ADD TextVarFormat NVARCHAR2(30) NULL
UPDATE VARDESC SET TextVarFormat='TEXT ENTRY' WHERE VARTYPE IN ('T','X','Q','B','E','R','A') AND LIMIT_TO_LIST = 0 AND (LIST IS NULL) UPDATE VARDESC SET TextVarFormat='DROP DOWN LIST' WHERE VARTYPE IN ('T','X','Q','B','E','R','A') AND LIMIT_TO_LIST = 0 AND (LIST IS NOT NULL)
UPDATE VARDESC SET TextVarFormat='DROP DOWN LIMIT TO LIST' WHERE VARTYPE IN ('T','X','Q','B','E','R','A') AND LIMIT_TO_LIST = -1 AND (LIST IS NOT NULL)

ALTER TABLE VARDESC ADD ENTRYNOTIFICATIONLISTID NUMBER NULL
ALTER TABLE VARDESC ADD REGNOTIFICATIONLISTID NUMBER NULL
ALTER TABLE VARDESC ADD QCNOTIFICATIONLISTID NUMBER NULL

ALTER TABLE LC_SAMPLEDEF ADD SCHDSTARTDATE NVARCHAR2(10) NULL
ALTER TABLE LC_SAMPLEDEF ADD SCHDENDDATE NVARCHAR2(10) NULL
ALTER TABLE LC_SAMPLEDEF ADD ADHOC NUMBER DEFAULT 0 NOT NULL
ALTER TABLE LC_SAMPLEDEF MODIFY(NAME NVARCHAR2(200)
ALTER TABLE LC_SAMPLEDEF ADD DUENOTIFICATIONLISTID NUMBER NULL
ALTER TABLE LC_SAMPLEDEF ADD LATENOTIFICATIONLISTID NUMBER NULL
ALTER TABLE LC_SAMPLEDEF ADD LATENOTIFYAFTER NUMBER NULL

ALTER TABLE LOCATION ADD ADHOC NUMBER DEFAULT 0 NOT NULL

ALTER TABLE LC_SAMPLEDEF ADD ADHOC NUMBER DEFAULT 0 NOT NULL
ALTER TABLE LC_SAMPLEDEF MODIFY(NAME NVARCHAR2(200))

ALTER TABLE LC_SAMPLE ADD SAMPLENAME NVARCHAR2(200) NULL
ALTER TABLE LC_SAMPLE ADD SAMPLEDBYNAME NVARCHAR2(120) NULL
ALTER TABLE LC_SAMPLE ADD RCVDBYNAME NVARCHAR2(120) NULL
ALTER TABLE LC_SAMPLE ADD SAMPLELOCATION NVARCHAR2(250) NULL
ALTER TABLE LC_SAMPLE ADD ADHOC NUMBER DEFAULT 0 NOT NULL
ALTER TABLE LC_SAMPLE ADD LATEEMAILSENT DATE NULL
ALTER TABLE LC_SAMPLE ADD DUEEMAILSENT DATE NULL
  
UPDATE LC_SAMPLE SET RCVDBYNAME = LC_PEOPLE.NAME FROM LC_SAMPLE INNER JOIN LC_PEOPLE ON LC_SAMPLE.RECEIVEDBY=LC_PEOPLE.ID  
UPDATE LC_SAMPLE SET SAMPLEDBYNAME = LC_PEOPLE.NAME FROM LC_SAMPLE INNER JOIN LC_PEOPLE ON LC_SAMPLE.SAMPLEDBY=LC_PEOPLE.ID
UPDATE LC_SAMPLE SET SAMPLELOCATION = LOCATION.PATH FROM LC_SAMPLE INNER JOIN LOCATION ON LC_SAMPLE.LOCATIONID=LOCATION.LOCID