123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291 |
- CREATE TABLE FmsMaterialCodeClass
- (
- SiteId int NOT NULL ,
- MaterialClassId int identity(1,1) NOT NULL ,
- ParentMaterialClassId int NULL ,
- Depth int NOT NULL ,
- Name nvarchar(32) NOT NULL ,
- Abbreviation nvarchar(16) NULL ,
- IsUse bit NULL ,
-
- PRIMARY KEY (SiteId ASC , MaterialClassId ASC)
- );
- CREATE TABLE FmsMaterialCodeType
- (
- SiteId int NOT NULL,
- MaterialTypeId smallint identity(1,1) NOT NULL,
- Name nvarchar(24) NOT NULL,
- Nickname nvarchar(24) NOT NULL,
- IsUse bit NOT NULL,
- PRIMARY KEY (SiteId ASC , MaterialTypeId ASC)
- );
- CREATE TABLE FmsMaterialCodePurchaseType
- (
- SiteId int NOT NULL,
- PurchaseTypeId smallint identity(1,1) NOT NULL,
- Name nvarchar(24) NOT NULL,
- IsUse bit NOT NULL,
- PRIMARY KEY (SiteId ASC , PurchaseTypeId ASC)
- );
-
- CREATE TABLE FmsMaterialCodeReleaseType
- (
- ReleaseTypeId smallint NOT NULL,
-
- Name nvarchar(24) NOT NULL,
- [Description] nvarchar(100) NULL,
- PRIMARY KEY (ReleaseTypeId ASC)
- );
- INSERT INTO FmsMaterialCodeReleaseType VALUES (1, '작업출고', '작업에 사용한 자재의 출고');
- INSERT INTO FmsMaterialCodeReleaseType VALUES (2, '조정출고', '재고 정산에 의한 출고');
- CREATE TABLE FmsMaterialTradeCompany
- (
- SiteId int NOT NULL,
- TradeCompanyId int identity(1,1) NOT NULL,
- Name nvarchar(48) NOT NULL,
- RegistrationNo nvarchar(24) NOT NULL,
- RepresentativeName nvarchar(24) null,
- PhoneNo nvarchar(20) null,
- FaxNo nvarchar(20) null,
- SalseName nvarchar(20) null,
- SalsePhoneNo nvarchar(20) null,
- AddressZip1 nchar(3) NULL ,
- AddressZip2 nchar(3) NULL ,
- Address1 nvarchar(100) NULL ,
- Adderss2 nvarchar(100) NULL ,
- HomepageUri nvarchar(200) NULL ,
- Note nvarchar(1024) NULL ,
- IsUse bit NOT NULL,
- PRIMARY KEY (SiteId ASC, TradeCompanyId ASC)
- );
- CREATE TABLE FmsMaterialCodeProgress
- (
- ProgressId smallint NOT NULL,
- Name nvarchar(24) NOT NULL,
- [Description] nvarchar(100) null,
- PRIMARY KEY (ProgressId ASC)
- );
- INSERT INTO FmsMaterialCodeProgress VALUES (1, '자재신청', '');
- INSERT INTO FmsMaterialCodeProgress VALUES (2, '신청승인', '');
- INSERT INTO FmsMaterialCodeProgress VALUES (3, '발주요청', '');
- INSERT INTO FmsMaterialCodeProgress VALUES (4, '자재입고', '');
- INSERT INTO FmsMaterialCodeProgress VALUES (5, '입고승인', '');
- CREATE TABLE FmsMaterialCodeLocation
- (
- SiteId int NOT NULL,
- LocationId int identity(1,1) NOT NULL,
- BusinessFieldId int NOT NULL,
- Name nvarchar(48) NOT NULL,
- IsUse bit NOT NULL,
- PRIMARY KEY (SiteId ASC, LocationId ASC)
- );
- CREATE TABLE FmsMaterialWarehouse
- (
- SiteId int NOT NULL,
- BusinessFieldId int NOT NULL,
- WarehouseId int identity(1,1) NOT NULL,
- Name nvarchar(48) NOT NULL,
- IsUse bit NOT NULL,
- PRIMARY KEY (SiteId ASC, BusinessFieldId ASC, WarehouseId ASC)
- );
- CREATE TABLE FmsMaterial
- (
- SiteId int NOT NULL,
- BusinessFieldId int NOT NULL,
- MaterialId int identity(1,1) NOT NULL,
- MaterialCode nvarchar(48) NOT NULL,
- Name nvarchar(48) NOT NULL,
-
- FirstClassId int null,
- SecondClassId int null,
- ThirdClassId int null,
- LocationId int null,
- MaterialTypeId smallint null,
- TradeCompanyId int NULL,
- WarehouseId int NULL,
- Rfid nvarchar(52) null,
- Unit nvarchar(12) null,
- DurableYears smallint null,
- ReasonableStockCount smallint null,
- Manufacturer nvarchar(40) null,
- [Standard] nvarchar(20) null,
-
- FinalPrice int null,
- ImageFileId int null,
- Note nvarchar(512) null,
- IsDiscontinued bit null,
- IsUse bit NOT NULL,
- PRIMARY KEY (SiteId ASC, BusinessFieldId ASC, MaterialId ASC)
- );
- ALTER TABLE FmsMaterialCodeClass
- ADD CONSTRAINT FK__FmsMaterialCodeClass__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsMaterialCodeClass
- ADD CONSTRAINT FK__FmsMaterialCodeClass__FmsMaterialCodeClass
- FOREIGN KEY (SiteId , ParentMaterialClassId) REFERENCES FmsMaterialCodeClass(SiteId , MaterialClassId)
- ;
- ALTER TABLE FmsMaterialTradeCompany
- ADD CONSTRAINT FK__FmsMaterialTradeCompany__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsMaterialCodeType
- ADD CONSTRAINT FK__FmsMaterialCodeType__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsMaterialCodePurchaseType
- ADD CONSTRAINT FK__FmsMaterialCodePurchaseType__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsMaterialCodeLocation
- ADD CONSTRAINT FK__FmsMaterialCodeLocation__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsMaterialCodeLocation
- ADD CONSTRAINT FK__FmsMaterialCodeLocation__CmBusinessField
- FOREIGN KEY (SiteId, BusinessFieldId) REFERENCES CmBusinessField(SiteId, BusinessFieldId)
- ;
- ALTER TABLE FmsMaterialWarehouse
- ADD CONSTRAINT FK__FmsMaterialWarehouse__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsMaterialWarehouse
- ADD CONSTRAINT FK__FmsMaterialWarehouse__CmBusinessField
- FOREIGN KEY (SiteId, BusinessFieldId) REFERENCES CmBusinessField(SiteId, BusinessFieldId)
- ;
- ALTER TABLE FmsMaterial
- ADD CONSTRAINT FK__FmsMaterial__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsMaterial
- ADD CONSTRAINT FK__FmsMaterial__CmBusinessField
- FOREIGN KEY (SiteId, BusinessFieldId) REFERENCES CmBusinessField(SiteId, BusinessFieldId)
- ;
- ALTER TABLE FmsMaterial
- ADD CONSTRAINT FK__FmsMaterial__FmsMaterialCodeClass_AsFirstClassId
- FOREIGN KEY (SiteId, FirstClassId) REFERENCES FmsMaterialCodeClass(SiteId, MaterialClassId)
- ;
- ALTER TABLE FmsMaterial
- ADD CONSTRAINT FK__FmsMaterial__FmsMaterialCodeClass_AsSecondClassId
- FOREIGN KEY (SiteId, SecondClassId) REFERENCES FmsMaterialCodeClass(SiteId, MaterialClassId)
- ;
- ALTER TABLE FmsMaterial
- ADD CONSTRAINT FK__FmsMaterial__FmsMaterialCodeClass_AsThirdClassId
- FOREIGN KEY (SiteId, ThirdClassId) REFERENCES FmsMaterialCodeClass(SiteId, MaterialClassId)
- ;
- ALTER TABLE FmsMaterial
- ADD CONSTRAINT FK__FmsMaterial__FmsMaterialWarehouse
- FOREIGN KEY (SiteId, BusinessFieldId, WarehouseId) REFERENCES FmsMaterialWarehouse(SiteId, BusinessFieldId, WarehouseId)
- ;
- ALTER TABLE FmsMaterial
- ADD CONSTRAINT FK__FmsMaterial__FmsMaterialTradeCompany
- FOREIGN KEY (SiteId, TradeCompanyId) REFERENCES FmsMaterialTradeCompany(SiteId, TradeCompanyId)
- ;
- ALTER TABLE FmsMaterial
- ADD CONSTRAINT FK__FmsMaterial__FmsMaterialCodeLocation
- FOREIGN KEY (SiteId, LocationId) REFERENCES FmsMaterialCodeLocation(SiteId, LocationId)
- ;
- ALTER TABLE FmsMaterial
- ADD CONSTRAINT FK__FmsMaterial__FmsMaterialCodeType
- FOREIGN KEY (SiteId, MaterialTypeId) REFERENCES FmsMaterialCodeType(SiteId, MaterialTypeId)
- ;
- ALTER TABLE FmsMaterial
- ADD CONSTRAINT FK__FmsMaterial__CmFile
- FOREIGN KEY (SiteId, ImageFileId) REFERENCES CmFile(SiteId, FileId)
- ;
|