123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203 |
- CREATE TABLE FmsWorkCodeLegalInspectionAgency
- (
- SiteId int NOT NULL ,
- InspectionAgencyId int identity(1,1) NOT NULL ,
- Name nvarchar(24) NOT NULL ,
-
- IsUse bit NULL ,
- PRIMARY KEY (SiteId ASC , InspectionAgencyId ASC)
- );
- DBCC CHECKIDENT( FmsWorkCodeLegalInspectionAgency, RESEED, 1 )
- CREATE TABLE FmsWorkCodeHolidayWorkType
- (
- SiteId int NOT NULL ,
- HolidayWorkTypeId int NOT NULL ,
- Name nvarchar(50) NULL ,
- IsUse bit NULL ,
-
- PRIMARY KEY (SiteId ASC , HolidayWorkTypeId ASC)
- );
- INSERT INTO FmsWorkCodeHolidayWorkType VALUES(1, 0, '사용 안 함', 1);
- INSERT INTO FmsWorkCodeHolidayWorkType VALUES(1, 1, '휴일작업', 1);
- INSERT INTO FmsWorkCodeHolidayWorkType VALUES(1, 2, '익일작업', 1);
- CREATE TABLE FmsWorkCodeCycleUnit
- (
- SiteId int NOT NULL ,
- CycleUnitId int NOT NULL ,
- Name nvarchar(24) NOT NULL ,
- [Description] nvarchar(200) NULL ,
- PRIMARY KEY (SiteId ASC , CycleUnitId ASC)
- );
- CREATE TABLE FmsWorkSchedule
- (
- SiteId int NOT NULL ,
- WorkScheduleId int identity(1,1) NOT NULL ,
- WorkTypeId int NOT NULL ,
- BusinessFieldId int NOT NULL ,
- Name nvarchar(100) NOT NULL ,
- CycleSize int NOT NULL ,
- CycleUnitId int NOT NULL ,
- HolidayWorkTypeId int NOT NULL ,
-
- InspectionAgencyId int NULL ,
-
- WorkDueDate datetime NOT NULL ,
-
- UpdateUserId nvarchar(24) NOT NULL ,
- IsUse bit NOT NULL ,
- IsAutoOrder bit NOT NULL ,
- PRIMARY KEY (SiteId ASC , WorkScheduleId ASC)
- );
- DBCC CHECKIDENT( FmsWorkSchedule, RESEED, 1 )
- CREATE TABLE FmsWorkScheduleToFacility
- (
- SiteId int NOT NULL ,
- WorkScheduleId int NOT NULL ,
- FacilityCode int NOT NULL ,
-
- PRIMARY KEY (SiteId ASC , WorkScheduleId ASC , FacilityCode ASC)
- );
- ALTER TABLE FmsWorkCodeLegalInspectionAgency
- ADD CONSTRAINT FK__FmsWorkCodeLegalInspectionAgency__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsWorkCodeHolidayWorkType
- ADD CONSTRAINT FK__FmsWorkCodeHolidayWorkType__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsWorkCodeCycleUnit
- ADD CONSTRAINT FK__FmsWorkCodeCycleUnit__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsFacilityCheckItem
- ADD CONSTRAINT FK__FmsFacilityCheckItem__FmsWorkCodeCycleUnit
- FOREIGN KEY (SiteId , CycleUnitId) REFERENCES FmsWorkCodeCycleUnit(SiteId , CycleUnitId)
- ;
- ALTER TABLE FmsWorkSchedule
- ADD CONSTRAINT FK__FmsWorkSchedule__FmsWorkCodeCycleUnit
- FOREIGN KEY (SiteId , CycleUnitId) REFERENCES FmsWorkCodeCycleUnit(SiteId , CycleUnitId)
- ;
- ALTER TABLE FmsWorkSchedule
- ADD CONSTRAINT FK__FmsWorkSchedule__FmsWorkCodeHolidayWorkType
- FOREIGN KEY (SiteId , HolidayWorkTypeId) REFERENCES FmsWorkCodeHolidayWorkType(SiteId , HolidayWorkTypeId)
- ;
- ALTER TABLE FmsWorkSchedule
- ADD CONSTRAINT FK__FmsWorkSchedule__CmBusinessField
- FOREIGN KEY (SiteId , BusinessFieldId) REFERENCES CmBusinessField(SiteId , BusinessFieldId)
- ;
- ALTER TABLE FmsWorkSchedule
- ADD CONSTRAINT FK__FmsWorkSchedule__CmUser
- FOREIGN KEY (SiteId , UpdateUserId) REFERENCES CmUser(SiteId , UserId)
- ;
- ALTER TABLE FmsWorkSchedule
- ADD CONSTRAINT FK__FmsWorkSchedule__FmsWorkCodeLegalInspectionAgency
- FOREIGN KEY (SiteId , InspectionAgencyId) REFERENCES FmsWorkCodeLegalInspectionAgency(SiteId , InspectionAgencyId)
- ;
- ALTER TABLE FmsWorkScheduleToFacility
- ADD CONSTRAINT FK__FmsWorkScheduleToFacility__FmsWorkSchedule
- FOREIGN KEY (SiteId , WorkScheduleId) REFERENCES FmsWorkSchedule(SiteId , WorkScheduleId)
- ;
- ALTER TABLE FmsWorkScheduleToFacility
- ADD CONSTRAINT FK__FmsWorkScheduleToFacility__CmFacility
- FOREIGN KEY (SiteId , FacilityCode) REFERENCES CmFacility(SiteId , FacilityCode)
- ;
-
|