Tech Notes:

SQL Server Scripts:

OPSROOT

CREATE TABLE [dbo].[ANALYTECODES]([CODE] [nvarchar](10) NULL,[NAME] [nvarchar](255) NULL,[SCIENTIFIC_NAME] [nvarchar](255) NULL,[TYPE_CODE] [nvarchar](255) NULL,[CAS_REGISTRY_NUM] [nvarchar](255) NULL,[STATE_CLASS_CODE] [nvarchar](255) NULL) ON [PRIMARY]

AnalyteCodes is then populated with 768 standard AnalyteCodes from SDWIS/CMDP database.

FACILITY DATABASES

CREATE TABLE [dbo].[LC_LABS]([ID] [int] IDENTITY(1,1) NOT NULL,[AUDITUSER] [varchar](50) NULL,[AUDITTIMESTAMP] [datetime] NULL DEFAULT (getdate()),[LABID] [nvarchar](50) NULL,[NAME] [nvarchar](255) NULL,[ADDRESS1] [nvarchar](255) NULL,[ADDRESS2] [nvarchar](255) NULL,[CITY] [nvarchar](255) NULL,[STATE] [nvarchar](255) NULL,[ZIP] [nvarchar](255) NULL,[PHONE] [nvarchar](255) NULL,[EMAIL] [nvarchar](255) NULL,[NOTES] [nvarchar](2000) NULL) ON [PRIMARY]

ALTER TABLE EVENTS ADD COMMENTS NVARCHAR2(2000) DEFAULT NULL
ALTER TABLE LC_SAMPLEDEF ADD LABID NVARCHAR(50) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLE ADD LABID NVARCHAR(50) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLE ADD SAMPLEVOLUME NVARCHAR(25) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLETEST ADD VOLANALYZED NVARCHAR(25) NULL DEFAULT (NULL)
ALTER TABLE LC_PEOPLE ADD LABID NVARCHAR(50) NULL DEFAULT (NULL)
ALTER TABLE LOCATION ADD SAMP_POINT_ID NVARCHAR(50) NULL DEFAULT (NULL)
ALTER TABLE LC_TEST ADD ANALYTECODE NVARCHAR(10) NULL DEFAULT (NULL)
ALTER TABLE VARDESC ADD ANALYTECODE NVARCHAR(10) NULL DEFAULT (NULL)
SP_RENAME 'VARDESC.ComRequiredOnEdit','COMREQUIREDONEDIT','COLUMN'
ALTER TABLE FILES ADD DATESTAMP DATETIME NULL DEFAULT (NULL)

Oracle Scripts

OPSROOT

CREATE TABLE "ANALYTECODES" ("CODE" VARCHAR2(10) NULL, "NAME" NVARCHAR2 (255) NULL, "SCIENTIFIC_NAME" NVARCHAR2 (255) NULL, "TYPE_CODE" NVARCHAR2 (255) NULL,"CAS_REGISTRY_NUM" NVARCHAR2(255) DEFAULT NULL,"STATE_CLASS_CODE" NVARCHAR2(255) DEFAULT NULL)

AnalyteCodes is then populated with 768 standard AnalyteCodes from SDWIS/CMDP database.

FACILITY DATABASES

CREATE TABLE "LC_LABS" ("ID" NUMBER NOT NULL, "AUDITUSER" VARCHAR2(50) NULL, "AUDITTIMESTAMP" DATE DEFAULT NULL, "LABID" NVARCHAR2 (50) NULL, "NAME" NVARCHAR2 (255) NULL,"ADDRESS1" NVARCHAR2 (100) NULL,"ADDRESS2" NVARCHAR2 (100) NULL,"CITY" NVARCHAR2 (100) NULL,"STATE" NVARCHAR2 (25) NULL,"ZIP" NVARCHAR2 (25) NULL,"PHONE" NVARCHAR2 (100) NULL,"EMAIL" NVARCHAR2 (100) NULL,"NOTES" NVARCHAR2 (2000) NULL)
CREATE SEQUENCE "A_LC_LABS" INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE CACHE 20 NOORDER
CREATE TRIGGER "T_LC_LABS" BEFORE INSERT ON "LC_LABS" FOR EACH ROW BEGIN SELECT "A_LC_LABS".NEXTVAL INTO :NEW."ID" FROM DUAL; END T_LC_LABS;

ALTER TABLE EVENTS ADD COMMENTS NVARCHAR(2000) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLEDEF ADD LABID NVARCHAR2(50) DEFAULT NULL
ALTER TABLE LC_SAMPLE ADD LABID NVARCHAR2(50) DEFAULT NULL
ALTER TABLE LC_SAMPLE ADD SAMPLEVOLUME NVARCHAR2(25) DEFAULT NULL
ALTER TABLE LC_SAMPLETEST ADD VOLANALYZED NVARCHAR2(25) DEFAULT NULL
ALTER TABLE LC_PEOPLE ADD LABID NVARCHAR2(25) DEFAULT NULL
ALTER TABLE LOCATION ADD SAMP_POINT_ID NVARCHAR2(50) DEFAULT NULL
ALTER TABLE LC_TEST ADD ANALYTECODE NVARCHAR2(10) DEFAULT NULL
ALTER TABLE VARDESC ADD ANALYTECODE NVARCHAR2(10) DEFAULT NULL
ALTER TABLE VARDESC RENAME COLUMN ComRequiredOnEdit to CROE
ALTER TABLE VARDESC RENAME COLUMN CROE TO COMREQUIREDONEDIT