123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192 |
- CREATE TABLE FmsMaterialPurchaseRequest
- (
- SiteId int NOT NULL ,
- BusinessFieldId int NOT NULL ,
- PurchaseRequestId int identity(1,1) NOT NULL ,
- PurchaseTypeId smallint NOT NULL ,
- RequestDate datetime NOT NULL ,
- RequestUserId nvarchar(24) NOT NULL ,
- PurchaseHopeDate datetime NULL,
- Title nvarchar(256) NOT NULL ,
- Reason nvarchar(2000) NOT NULL ,
- ProgressId smallint NOT NULL ,
- ApprovalDate datetime NULL ,
- ApprovalUserId nvarchar(24) NULL ,
-
- PRIMARY KEY (SiteId ASC , BusinessFieldId ASC , PurchaseRequestId ASC)
- );
- CREATE TABLE FmsMaterialPurchaseRequestMaterial
- (
- SiteId int NOT NULL ,
- BusinessFieldId int NOT NULL ,
- PurchaseRequestId int NOT NULL ,
- MaterialId int NOT NULL ,
- MaterialCount int NOT NULL ,
- PRIMARY KEY (SiteId ASC , BusinessFieldId ASC , PurchaseRequestId ASC , MaterialId ASC)
- );
- CREATE TABLE FmsMaterialPurchaseOrder
- (
- SiteId int NOT NULL ,
- BusinessFieldId int NOT NULL ,
- PurchaseOrderId int identity(1,1) NOT NULL ,
- PurchaseRequestId int NOT NULL ,
- TradeCompanyId int NOT NULL,
- OrderDate datetime NOT NULL ,
- OrderUserId nvarchar(24) NOT NULL ,
- Title nvarchar(256) NOT NULL ,
- Reason nvarchar(2000) NOT NULL ,
-
- StoredDueDate datetime NULL ,
- FileId int NULL ,
- PRIMARY KEY (SiteId ASC , BusinessFieldId ASC , PurchaseOrderId ASC)
- );
- CREATE TABLE FmsMaterialPurchaseOrderMaterial
- (
- SiteId int NOT NULL ,
- BusinessFieldId int NOT NULL ,
- PurchaseOrderId int NOT NULL ,
- MaterialId int NOT NULL ,
- MaterialCount int NOT NULL ,
- UnitCost int NOT NULL ,
- PRIMARY KEY (SiteId ASC , BusinessFieldId ASC , PurchaseOrderId ASC , MaterialId ASC)
- );
- ALTER TABLE FmsMaterialPurchaseRequest
- ADD CONSTRAINT FK__FmsMaterialPurchaseRequest__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsMaterialPurchaseRequest
- ADD CONSTRAINT FK__FmsMaterialPurchaseRequest__CmBusinessField
- FOREIGN KEY (SiteId, BusinessFieldId) REFERENCES CmBusinessField(SiteId, BusinessFieldId)
- ;
- ALTER TABLE FmsMaterialPurchaseRequest
- ADD CONSTRAINT FK__FmsMaterialPurchaseRequest__FmsMaterialCodePurchaseType
- FOREIGN KEY (SiteId, PurchaseTypeId) REFERENCES FmsMaterialCodePurchaseType(SiteId, PurchaseTypeId)
- ;
- ALTER TABLE FmsMaterialPurchaseRequest
- ADD CONSTRAINT FK__FmsMaterialPurchaseRequest__CmUser_AsRequestUserId
- FOREIGN KEY (SiteId, RequestUserId) REFERENCES CmUser(SiteId, UserId)
- ;
- ALTER TABLE FmsMaterialPurchaseRequest
- ADD CONSTRAINT FK__FmsMaterialPurchaseRequest__CmUser1_AsApprovalUserId
- FOREIGN KEY (SiteId, ApprovalUserId) REFERENCES CmUser(SiteId, UserId)
- ;
- ALTER TABLE FmsMaterialPurchaseRequest
- ADD CONSTRAINT FK__FmsMaterialPurchaseRequest__FmsMaterialCodeProgress
- FOREIGN KEY (ProgressId) REFERENCES FmsMaterialCodeProgress(ProgressId)
- ;
- ALTER TABLE FmsMaterialPurchaseRequestMaterial
- ADD CONSTRAINT FK__FmsMaterialPurchaseRequestMaterial__FmsMaterialPurchaseRequest
- FOREIGN KEY (SiteId, BusinessFieldId, PurchaseRequestId) REFERENCES FmsMaterialPurchaseRequest(SiteId, BusinessFieldId, PurchaseRequestId)
- ;
- ALTER TABLE FmsMaterialPurchaseRequestMaterial
- ADD CONSTRAINT FK__FmsMaterialPurchaseRequestMaterial__FmsMaterial
- FOREIGN KEY (SiteId, BusinessFieldId, MaterialId) REFERENCES FmsMaterial(SiteId, BusinessFieldId, MaterialId)
- ;
- ALTER TABLE FmsMaterialPurchaseOrder
- ADD CONSTRAINT FK__FmsMaterialPurchaseOrder__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsMaterialPurchaseOrder
- ADD CONSTRAINT FK__FmsMaterialPurchaseOrder__FmsMaterialPurchaseRequest
- FOREIGN KEY (SiteId, BusinessFieldId, PurchaseRequestId) REFERENCES FmsMaterialPurchaseRequest(SiteId, BusinessFieldId, PurchaseRequestId)
- ;
- ALTER TABLE FmsMaterialPurchaseOrder
- ADD CONSTRAINT FK__FmsMaterialPurchaseOrder__CmBusinessField
- FOREIGN KEY (SiteId, BusinessFieldId) REFERENCES CmBusinessField(SiteId, BusinessFieldId)
- ;
- ALTER TABLE FmsMaterialPurchaseOrder
- ADD CONSTRAINT FK__FmsMaterialPurchaseOrder__CmUser
- FOREIGN KEY (SiteId, OrderUserId) REFERENCES CmUser(SiteId, UserId)
- ;
- ALTER TABLE FmsMaterialPurchaseOrder
- ADD CONSTRAINT FK__FmsMaterialPurchaseOrder__FmsMaterialTradeCompany
- FOREIGN KEY (SiteId, TradeCompanyId) REFERENCES FmsMaterialTradeCompany(SiteId, TradeCompanyId)
- ;
- ALTER TABLE FmsMaterialPurchaseOrder
- ADD CONSTRAINT FK__FmsMaterialPurchaseOrder__CmFile
- FOREIGN KEY (SiteId, FileId) REFERENCES CmFile(SiteId, FileId)
- ;
- ALTER TABLE FmsMaterialPurchaseOrderMaterial
- ADD CONSTRAINT FK__FmsMaterialPurchaseOrderMaterial__FmsMaterialPurchaseOrder
- FOREIGN KEY (SiteId, BusinessFieldId, PurchaseOrderId) REFERENCES FmsMaterialPurchaseOrder(SiteId, BusinessFieldId, PurchaseOrderId)
- ;
- ALTER TABLE FmsMaterialPurchaseOrderMaterial
- ADD CONSTRAINT FK__FmsMaterialPurchaseOrderMaterial__FmsMaterial
- FOREIGN KEY (SiteId, BusinessFieldId, MaterialId) REFERENCES FmsMaterial(SiteId, BusinessFieldId, MaterialId)
- ;
|