| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192 | ----------------------------------------------------- 자재구매관리 ----------------------------------------------------- 자재 구매 신청-- MapuRequestCREATE 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));-- 자재 발주 정보-- MapuRequestCREATE 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));---- 자재 발주서 파일들--CREATE TABLE FmsMaterialPurchaseOrderFile--(--	SiteId					int	NOT NULL ,	-- 현장 고유번호--	BusinessFieldId			int	NOT NULL ,  -- 업무분야 고유번호--	PurchaseOrderId			int	NOT NULL ,	-- 자재발주 고유번호--	FileId					int NOT NULL ,	-- 파일 고유번호--	PRIMARY KEY (SiteId ASC , BusinessFieldId ASC , PurchaseOrderId ASC , FileId ASC)--);-- 구매 요청 FKALTER 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);-- 주의!!! -- FK 이름 생성 규칙 : 같은 테이블에 여러개의 필드가 하나의 테이블에 FK를 가질 경우, -- 네이밍 순서가 넘버링으로 결정된다. 아래와 같은 경우 그것을 보장받기 위해 CmUser1로 명시했다.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);-- 구매 신청 자재들 FKALTER 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);-- 발주 FKALTER 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);-- 발주 자재들 FKALTER 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);-- 발주 파일 FK--ALTER TABLE FmsMaterialPurchaseOrderFile--	ADD CONSTRAINT FK__FmsMaterialPurchaseOrderFile__FmsMaterialPurchaseOrder--		FOREIGN KEY (SiteId, BusinessFieldId, PurchaseOrderId) REFERENCES FmsMaterialPurchaseOrder(SiteId, BusinessFieldId, PurchaseOrderId)--;--ALTER TABLE FmsMaterialPurchaseOrderFile--	ADD CONSTRAINT FK__FmsMaterialPurchaseOrderFile__CmFile--		FOREIGN KEY (SiteId, FileId) REFERENCES CmFile(SiteId, FileId)--;
 |