| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192 | 
							- ---------------------------------------------------
 
- -- 자재구매관리 
 
- ---------------------------------------------------
 
- -- 자재 구매 신청
 
- -- MapuRequest
 
- CREATE 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)
 
- );
 
- -- 자재 발주 정보
 
- -- MapuRequest
 
- CREATE 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)
 
- --);
 
- -- 구매 요청 FK
 
- ALTER 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)
 
- ;
 
- -- 구매 신청 자재들 FK
 
- ALTER 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)
 
- ;
 
- -- 발주 FK
 
- ALTER 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)
 
- ;
 
- -- 발주 자재들 FK
 
- ALTER 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)
 
- --;
 
 
  |