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