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