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)
- --;
|