| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164 | ----------------------------------------------------- 자재입출고 관리----------------------------------------------------- 자재 입고 정보-- MaenEnterCREATE 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));-- 자재 출고 정보-- MadeDeliveryCREATE 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));-- 입고 FKALTER 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);-- 출고 FKALTER 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);-- 자재 이월 FKALTER 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);
 |