--------------------------------------------------- -- 자재입출고 관리 --------------------------------------------------- -- 자재 입고 정보 -- MaenEnter 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 , -- 입고한 것 자체에 대한 재고 개수, 선입선출 할 때 이 값이 감소한다. 0이되면 출고 불가능, 재고관리 테이블에 계산하여 넣는것도 방법 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) ); -- 자재 출고 정보 -- MadeDelivery 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 , -- 마감 연월 (일: 1) 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) ); -- 입고 FK 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) ; -- 출고 FK 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) ; -- 자재 이월 FK 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) ;