Summary Table
Categories |
Total Count |
PII |
0 |
URL |
0 |
DNS |
0 |
EKL |
0 |
IP |
0 |
PORT |
0 |
VsID |
0 |
CF |
0 |
AI |
0 |
VPD |
0 |
PL |
0 |
Other |
0 |
File Content
CREATE TABLE Status (
Id INT NOT NULL,
description VARCHAR(50) NOT NULL
);
ALTER TABLE Status ADD CONSTRAINT PK_status PRIMARY KEY (Id) ;
CREATE TABLE ServiceLine (
Id INT NOT NULL,
description VARCHAR(250) NOT NULL,
serviceLineAbbr VARCHAR(3) NOT NULL,
discontinued bit NULL
) ;
ALTER TABLE ServiceLine ADD CONSTRAINT PK_serviceLine PRIMARY KEY (Id) ;
CREATE TABLE CategoryOfCare (
Id INT NOT NULL,
description VARCHAR(75) NOT NULL,
serviceLineId INT NOT NULL,
discontinued bit NULL
) ;
ALTER TABLE CategoryOfCare ADD CONSTRAINT PK_categoryOfCare PRIMARY KEY (Id) ;
ALTER TABLE CategoryOfCare ADD CONSTRAINT FK_COC_SL FOREIGN KEY (serviceLineId) REFERENCES ServiceLine(Id);
CREATE TABLE ClinicalService (
Id INT NOT NULL,
description VARCHAR(250) NOT NULL,
discontinued bit NULL
) ;
ALTER TABLE ClinicalService ADD CONSTRAINT PK_clinicalService PRIMARY KEY (Id) ;
CREATE TABLE Qasp (
Id INT NOT NULL,
description VARCHAR(50) NOT NULL,
discontinued bit NULL
);
ALTER TABLE Qasp ADD CONSTRAINT PK_qasp PRIMARY KEY (Id) ;
CREATE TABLE Seoc (
Id INT NOT NULL AUTO_INCREMENT,
seocKey INT NOT NULL,
name VARCHAR(150) NOT NULL,
versionNumber VARCHAR(25) ,
effectiveDate datetime2,
endDate datetime2,
disclaimer VARCHAR(2000) ,
duration VARCHAR(50),
description VARCHAR(2000),
REV bit,
categoryOfCareId INT ,
serviceLineId INT ,
statusId INT,
qaspId INT,
proceduralOverview VARCHAR(5000),
codeRequired bit,
maxAllowableVisits INT,
activatedTimestamp datetime2,
activatedBy VARCHAR(80),
discontinuedTimestamp datetime2,
discontinuedBy VARCHAR(80),
comment VARCHAR(10000)
) ;
-- Constraints for table SEOC
ALTER TABLE Seoc ADD CONSTRAINT PK_SEOC PRIMARY KEY (Id);
ALTER TABLE Seoc ADD CONSTRAINT FK_SEOC_SL FOREIGN KEY (serviceLineId) REFERENCES ServiceLine(Id);
ALTER TABLE Seoc ADD CONSTRAINT FK_SEOC_CC FOREIGN KEY (categoryOfCareId) REFERENCES CategoryOfCare(Id);
ALTER TABLE Seoc ADD CONSTRAINT FK_SEOC_ST FOREIGN KEY (statusId) REFERENCES Status(Id);
ALTER TABLE Seoc ADD CONSTRAINT FK_SEOC_qasp FOREIGN KEY (qaspId) REFERENCES Qasp(Id);
CREATE TABLE BillingCode (
Id INT NOT NULL AUTO_INCREMENT,
billingCode VARCHAR(25) NOT NULL,
codeType VARCHAR(25) NOT NULL,
description VARCHAR(2000),
precertRqd bit NULL,
deactivated bit NULL DEFAULT 0
) ;
-- Constraints for table CPTCODES
ALTER TABLE BillingCode ADD CONSTRAINT PK_CPTCODES PRIMARY KEY (Id) ;
CREATE TABLE Service (
Id INT NOT NULL AUTO_INCREMENT,
seocId INT NOT NULL,
description VARCHAR(2000),
frequency INT,
frequencyType VARCHAR(25),
visits INT,
codeRequired VARCHAR(3) NULL,
clinicalServiceId INT,
codedBy VARCHAR(80),
codedTimestamp DATE
) ;
-- Constraints for table SERVICE
ALTER TABLE Service ADD CONSTRAINT PK_SEOCSERVICE PRIMARY KEY (Id) ;
ALTER TABLE Service ADD CONSTRAINT FK_SEOCSERVICE_SEOC FOREIGN KEY (seocId) REFERENCES Seoc(Id);
ALTER TABLE Service ADD CONSTRAINT FK_SERVICE_CS FOREIGN KEY (clinicalServiceId) REFERENCES ClinicalService(Id);
CREATE TABLE ServiceBillingCode(
serviceId INT NOT NULL,
billingCodeId INT NOT NULL
);
ALTER TABLE ServiceBillingCode ADD CONSTRAINT PK_SERVICEBILLINGCODE PRIMARY KEY (serviceId,billingCodeId) ;
ALTER TABLE ServiceBillingCode ADD CONSTRAINT FK_BILLINGCODE FOREIGN KEY (billingCodeId) REFERENCES BillingCode(Id);
ALTER TABLE ServiceBillingCode ADD CONSTRAINT FK_SERVICESBC FOREIGN KEY (serviceId) REFERENCES Service(Id);
CREATE TABLE ServiceClinicalService(
serviceId INT NOT NULL,
clinicalServiceId INT NOT NULL
);
ALTER TABLE ServiceClinicalService ADD CONSTRAINT PK_SERVICECLINICALSERVICE PRIMARY KEY (serviceId,clinicalServiceId) ;
ALTER TABLE ServiceClinicalService ADD CONSTRAINT FK_CLINICALSERVICE FOREIGN KEY (clinicalServiceId) REFERENCES ClinicalService(Id);
ALTER TABLE ServiceClinicalService ADD CONSTRAINT FK_SERVICESCS FOREIGN KEY (serviceId) REFERENCES Service(Id);
CREATE TABLE SeocConfig (
currProgram VARCHAR(25) NOT NULL,
currProgramVersion INT NOT NULL,
inMaintenanceBy varchar(80) NOT NULL,
) ;
ALTER TABLE SeocConfig ADD CONSTRAINT PK_SEOCONFIG PRIMARY KEY (currProgram) ;
create table SeocUser
(Id int IDENTITY(1,1) NOT NULL,
userName varchar(50) NOT NULL,
vaUserId varchar(80) NOT NULL,
role varchar(25) NULL,
deactivatedDate datetime2(7) NULL,
createDate datetime2(7) NOT NULL,
modifiedDate datetime2(7) NULL);
ALTER TABLE SeocUser ALTER COLUMN createDate SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE SeocUser ADD CONSTRAINT PK_SEOCUSER_USERNAME_CONSTRAINT UNIQUE(userName,deactivatedDate) ;
ALTER TABLE SeocUser ADD CONSTRAINT PK_SEOCUSER_VAUSERID_CONSTRAINT UNIQUE(vaUserId,deactivatedDate) ;
CREATE TABLE Event(
Id INT IDENTITY(1,1) NOT NULL,
action varchar(50) NOT NULL,
description varchar(250) NULL
);
CREATE TABLE ChangeLog(
Id INT IDENTITY(1,1) NOT NULL,
userId INT NOT NULL,
seocId INT NOT NULL,
seocKey INT NOT NULL,
eventId INT NOT NULL,
comments varchar(500) NULL,
eventDate datetime2(7) NOT NULL);
ALTER TABLE ChangeLog ALTER COLUMN eventDate SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE ChangeLog ALTER COLUMN userId SET DEFAULT 1;
ALTER TABLE ChangeLog ADD CONSTRAINT FK_CL_EId FOREIGN KEY (eventId) REFERENCES Event(Id);
ALTER TABLE ChangeLog ADD CONSTRAINT FK_CL_UId FOREIGN KEY (userId) REFERENCES SeocUser(Id);
ALTER TABLE ChangeLog ADD CONSTRAINT FK_EVENTID FOREIGN KEY (EventId) REFERENCES Event(Id);
CREATE TABLE Hptc (
hptc VARCHAR(25) NOT NULL,
classification VARCHAR(25) NOT NULL,
specialization VARCHAR(25) NOT NULL,
grouping VARCHAR(25) NOT NULL
) ;
ALTER TABLE Hptc ADD CONSTRAINT PK_HPTC PRIMARY KEY (hptc) ;
CREATE TABLE SeocHptc(
seocId INT NOT NULL,
hptc VARCHAR(25) NOT NULL
);
ALTER TABLE SeocHptc ADD CONSTRAINT PK_SECOHPTC PRIMARY KEY (seocId,hptc) ;
ALTER TABLE SeocHptc ADD CONSTRAINT FK_SECOHPTC_SEOC FOREIGN KEY (seocId) REFERENCES Seoc(Id);
ALTER TABLE SeocHptc ADD CONSTRAINT FK_SECOHPTC_HPTC FOREIGN KEY (hptc) REFERENCES Hptc(hptc);
CREATE TABLE ServiceHptc(
hptc VARCHAR(10) NOT NULL,
medicareCode VARCHAR(10) NOT NULL
);
ALTER TABLE ServiceHptc ADD CONSTRAINT PK_SERVICEHPTC PRIMARY KEY (hptc, medicareCode) ;