123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164 |
- CREATE TABLE FmsMaterialStored
- (
- SiteId int NOT NULL ,
- BusinessFieldId int NOT NULL ,
- StoredDate datetime NOT NULL ,
- MaterialStoredId int identity(1,1) NOT NULL ,
- PurchaseRequestId int NULL ,
- WarehouseId int NOT NULL ,
- MaterialId int NOT NULL ,
- StoredCount int NOT NULL ,
- UnitCost int NOT NULL ,
- StockCount int NOT NULL ,
-
- Note nvarchar(1024) NULL ,
- StoredUserId nvarchar(24) NOT NULL ,
- ApprovalDate datetime NULL ,
- ApprovalUserId nvarchar(24) NULL ,
-
- IsApproval bit NOT NULL ,
- PRIMARY KEY (SiteId ASC , BusinessFieldId ASC, StoredDate ASC , MaterialStoredId ASC)
- );
- CREATE TABLE FmsMaterialRelease
- (
- SiteId int NOT NULL ,
- BusinessFieldId int NOT NULL ,
- ReleaseDate datetime NOT NULL ,
- MaterialReleaseId int identity(1,1) NOT NULL ,
- ReleaseTypeId smallint NOT NULl ,
- WorkOrderId int NOT NULL ,
- WarehouseId int NOT NULL ,
- MaterialId int NOT NULL ,
- ReleaseCount int NOT NULL ,
- ReleaseTotalCost int NOT NULL ,
-
- PRIMARY KEY (SiteId ASC , BusinessFieldId ASC , ReleaseDate ASC , MaterialReleaseId ASC)
- );
- CREATE TABLE FmsMaterialCarriedForward
- (
- SiteId int NOT NULL ,
- ClosedDate datetime NOT NULL ,
-
- BusinessFieldId int NOT NULL ,
- WarehouseId int NOT NULL ,
- MaterialId int NOT NULL ,
- StockCount int NOT NULL ,
- StockTotalCost int NOT NULL ,
-
- PRIMARY KEY (SiteId ASC , ClosedDate ASC , BusinessFieldId ASC , WarehouseId ASC , MaterialId ASC)
- );
- ALTER TABLE FmsMaterialStored
- ADD CONSTRAINT FK__FmsMaterialStored__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsMaterialStored
- ADD CONSTRAINT FK__FmsMaterialStored__FmsMaterialPurchaseRequest
- FOREIGN KEY (SiteId, BusinessFieldId, PurchaseRequestId) REFERENCES FmsMaterialPurchaseRequest(SiteId, BusinessFieldId, PurchaseRequestId)
- ;
- ALTER TABLE FmsMaterialStored
- ADD CONSTRAINT FK__FmsMaterialStored__CmBusinessField
- FOREIGN KEY (SiteId, BusinessFieldId) REFERENCES CmBusinessField(SiteId, BusinessFieldId)
- ;
- ALTER TABLE FmsMaterialStored
- ADD CONSTRAINT FK__FmsMaterialStored__FmsMaterialWarehouse
- FOREIGN KEY (SiteId, BusinessFieldId, WarehouseId) REFERENCES FmsMaterialWarehouse(SiteId, BusinessFieldId,WarehouseId)
- ;
- ALTER TABLE FmsMaterialStored
- ADD CONSTRAINT FK__FmsMaterialStored__FmsMaterial
- FOREIGN KEY (SiteId, BusinessFieldId, MaterialId) REFERENCES FmsMaterial(SiteId, BusinessFieldId, MaterialId)
- ;
- ALTER TABLE FmsMaterialStored
- ADD CONSTRAINT FK__FmsMaterialStored__CmUser_AsStoredUserId
- FOREIGN KEY (SiteId, StoredUserId) REFERENCES CmUser(SiteId, UserId)
- ;
- ALTER TABLE FmsMaterialStored
- ADD CONSTRAINT FK__FmsMaterialStored__CmUser1_AsApprovalUserId
- FOREIGN KEY (SiteId, ApprovalUserId) REFERENCES CmUser(SiteId, UserId)
- ;
- ALTER TABLE FmsMaterialRelease
- ADD CONSTRAINT FK__FmsMaterialRelease__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsMaterialRelease
- ADD CONSTRAINT FK__FmsMaterialRelease__FmsWorkOrder
- FOREIGN KEY (SiteId, WorkOrderId) REFERENCES FmsWorkOrder(SiteId, WorkOrderId)
- ;
- ALTER TABLE FmsMaterialRelease
- ADD CONSTRAINT FK__FmsMaterialRelease__CmBusinessField
- FOREIGN KEY (SiteId, BusinessFieldId) REFERENCES CmBusinessField(SiteId, BusinessFieldId)
- ;
- ALTER TABLE FmsMaterialRelease
- ADD CONSTRAINT FK__FmsMaterialRelease__FmsMaterialCodeReleaseType
- FOREIGN KEY (ReleaseTypeId) REFERENCES FmsMaterialCodeReleaseType(ReleaseTypeId)
- ;
- ALTER TABLE FmsMaterialRelease
- ADD CONSTRAINT FK__FmsMaterialRelease__FmsMaterialWarehouse
- FOREIGN KEY (SiteId, WarehouseId) REFERENCES FmsMaterialWarehouse(SiteId, WarehouseId)
- ;
- ALTER TABLE FmsMaterialRelease
- ADD CONSTRAINT FK__FmsMaterialRelease__FmsMaterial
- FOREIGN KEY (SiteId, BusinessFieldId, MaterialId) REFERENCES FmsMaterial(SiteId, BusinessFieldId, MaterialId)
- ;
- ALTER TABLE FmsMaterialCarriedForward
- ADD CONSTRAINT FK__FmsMaterialCarriedForward__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsMaterialCarriedForward
- ADD CONSTRAINT FK__FmsMaterialCarriedForward__CmBusinessField
- FOREIGN KEY (SiteId, BusinessFieldId) REFERENCES CmBusinessField(SiteId, BusinessFieldId)
- ;
- ALTER TABLE FmsMaterialCarriedForward
- ADD CONSTRAINT FK__FmsMaterialCarriedForward__FmsMaterialWarehouse
- FOREIGN KEY (SiteId, BusinessFieldId, WarehouseId) REFERENCES FmsMaterialWarehouse(SiteId, BusinessFieldId, WarehouseId)
- ;
- ALTER TABLE FmsMaterialCarriedForward
- ADD CONSTRAINT FK__FmsMaterialCarriedForward__FmsMaterial
- FOREIGN KEY (SiteId, BusinessFieldId, MaterialId) REFERENCES FmsMaterial(SiteId, BusinessFieldId, MaterialId)
- ;
|