8de5471b13a74be5774608d078da782e5217cea3.svn-base 5.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164
  1. ---------------------------------------------------
  2. -- 자재입출고 관리
  3. ---------------------------------------------------
  4. -- 자재 입고 정보
  5. -- MaenEnter
  6. CREATE TABLE FmsMaterialStored
  7. (
  8. SiteId int NOT NULL , -- 현장 고유번호
  9. BusinessFieldId int NOT NULL , -- 업무분야 고유번호
  10. StoredDate datetime NOT NULL , -- 입고일시
  11. MaterialStoredId int identity(1,1) NOT NULL , -- 입고 고유번호
  12. PurchaseRequestId int NULL , -- 구매신청 고유번호
  13. WarehouseId int NOT NULL , -- 창고 고유번호
  14. MaterialId int NOT NULL , -- 자재 고유번호
  15. StoredCount int NOT NULL , -- 자재 요청 수량
  16. UnitCost int NOT NULL , -- 자재 단가
  17. StockCount int NOT NULL , -- 입고한 것 자체에 대한 재고 개수, 선입선출 할 때 이 값이 감소한다. 0이되면 출고 불가능, 재고관리 테이블에 계산하여 넣는것도 방법
  18. Note nvarchar(1024) NULL , -- 비고
  19. StoredUserId nvarchar(24) NOT NULL , -- 입고한 사용자 고유번호
  20. ApprovalDate datetime NULL , -- 승인일시
  21. ApprovalUserId nvarchar(24) NULL , -- 승인자 고유번호
  22. IsApproval bit NOT NULL , -- 승인여부
  23. PRIMARY KEY (SiteId ASC , BusinessFieldId ASC, StoredDate ASC , MaterialStoredId ASC)
  24. );
  25. -- 자재 출고 정보
  26. -- MadeDelivery
  27. CREATE TABLE FmsMaterialRelease
  28. (
  29. SiteId int NOT NULL , -- 현장 고유번호
  30. BusinessFieldId int NOT NULL , -- 업무분야 고유번호
  31. ReleaseDate datetime NOT NULL , -- 출고일시
  32. MaterialReleaseId int identity(1,1) NOT NULL , -- 출고 고유번호
  33. ReleaseTypeId smallint NOT NULl , -- 출고구분 고유번호
  34. WorkOrderId int NOT NULL , -- 작업지시 고유번호
  35. WarehouseId int NOT NULL , -- 창고 고유번호
  36. MaterialId int NOT NULL , -- 자재 고유번호
  37. ReleaseCount int NOT NULL , -- 자재 출고 수량
  38. ReleaseTotalCost int NOT NULL , -- 출고 된 자재 총 비용(각 단가 별로의 합산)
  39. PRIMARY KEY (SiteId ASC , BusinessFieldId ASC , ReleaseDate ASC , MaterialReleaseId ASC)
  40. );
  41. -- 자재 이월 정보
  42. CREATE TABLE FmsMaterialCarriedForward
  43. (
  44. SiteId int NOT NULL , -- 현장 고유번호
  45. ClosedDate datetime NOT NULL , -- 마감 연월 (일: 1)
  46. BusinessFieldId int NOT NULL , -- 업무분야 고유번호
  47. WarehouseId int NOT NULL , -- 창고 고유번호
  48. MaterialId int NOT NULL , -- 자재 고유번호
  49. StockCount int NOT NULL , -- 자재 재고 수량
  50. StockTotalCost int NOT NULL , -- 자재 총 비용(각 단가 별로의 합산)
  51. PRIMARY KEY (SiteId ASC , ClosedDate ASC , BusinessFieldId ASC , WarehouseId ASC , MaterialId ASC)
  52. );
  53. -- 입고 FK
  54. ALTER TABLE FmsMaterialStored
  55. ADD CONSTRAINT FK__FmsMaterialStored__CmSite
  56. FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
  57. ;
  58. ALTER TABLE FmsMaterialStored
  59. ADD CONSTRAINT FK__FmsMaterialStored__FmsMaterialPurchaseRequest
  60. FOREIGN KEY (SiteId, BusinessFieldId, PurchaseRequestId) REFERENCES FmsMaterialPurchaseRequest(SiteId, BusinessFieldId, PurchaseRequestId)
  61. ;
  62. ALTER TABLE FmsMaterialStored
  63. ADD CONSTRAINT FK__FmsMaterialStored__CmBusinessField
  64. FOREIGN KEY (SiteId, BusinessFieldId) REFERENCES CmBusinessField(SiteId, BusinessFieldId)
  65. ;
  66. ALTER TABLE FmsMaterialStored
  67. ADD CONSTRAINT FK__FmsMaterialStored__FmsMaterialWarehouse
  68. FOREIGN KEY (SiteId, BusinessFieldId, WarehouseId) REFERENCES FmsMaterialWarehouse(SiteId, BusinessFieldId,WarehouseId)
  69. ;
  70. ALTER TABLE FmsMaterialStored
  71. ADD CONSTRAINT FK__FmsMaterialStored__FmsMaterial
  72. FOREIGN KEY (SiteId, BusinessFieldId, MaterialId) REFERENCES FmsMaterial(SiteId, BusinessFieldId, MaterialId)
  73. ;
  74. ALTER TABLE FmsMaterialStored
  75. ADD CONSTRAINT FK__FmsMaterialStored__CmUser_AsStoredUserId
  76. FOREIGN KEY (SiteId, StoredUserId) REFERENCES CmUser(SiteId, UserId)
  77. ;
  78. ALTER TABLE FmsMaterialStored
  79. ADD CONSTRAINT FK__FmsMaterialStored__CmUser1_AsApprovalUserId
  80. FOREIGN KEY (SiteId, ApprovalUserId) REFERENCES CmUser(SiteId, UserId)
  81. ;
  82. -- 출고 FK
  83. ALTER TABLE FmsMaterialRelease
  84. ADD CONSTRAINT FK__FmsMaterialRelease__CmSite
  85. FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
  86. ;
  87. ALTER TABLE FmsMaterialRelease
  88. ADD CONSTRAINT FK__FmsMaterialRelease__FmsWorkOrder
  89. FOREIGN KEY (SiteId, WorkOrderId) REFERENCES FmsWorkOrder(SiteId, WorkOrderId)
  90. ;
  91. ALTER TABLE FmsMaterialRelease
  92. ADD CONSTRAINT FK__FmsMaterialRelease__CmBusinessField
  93. FOREIGN KEY (SiteId, BusinessFieldId) REFERENCES CmBusinessField(SiteId, BusinessFieldId)
  94. ;
  95. ALTER TABLE FmsMaterialRelease
  96. ADD CONSTRAINT FK__FmsMaterialRelease__FmsMaterialCodeReleaseType
  97. FOREIGN KEY (ReleaseTypeId) REFERENCES FmsMaterialCodeReleaseType(ReleaseTypeId)
  98. ;
  99. ALTER TABLE FmsMaterialRelease
  100. ADD CONSTRAINT FK__FmsMaterialRelease__FmsMaterialWarehouse
  101. FOREIGN KEY (SiteId, WarehouseId) REFERENCES FmsMaterialWarehouse(SiteId, WarehouseId)
  102. ;
  103. ALTER TABLE FmsMaterialRelease
  104. ADD CONSTRAINT FK__FmsMaterialRelease__FmsMaterial
  105. FOREIGN KEY (SiteId, BusinessFieldId, MaterialId) REFERENCES FmsMaterial(SiteId, BusinessFieldId, MaterialId)
  106. ;
  107. -- 자재 이월 FK
  108. ALTER TABLE FmsMaterialCarriedForward
  109. ADD CONSTRAINT FK__FmsMaterialCarriedForward__CmSite
  110. FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
  111. ;
  112. ALTER TABLE FmsMaterialCarriedForward
  113. ADD CONSTRAINT FK__FmsMaterialCarriedForward__CmBusinessField
  114. FOREIGN KEY (SiteId, BusinessFieldId) REFERENCES CmBusinessField(SiteId, BusinessFieldId)
  115. ;
  116. ALTER TABLE FmsMaterialCarriedForward
  117. ADD CONSTRAINT FK__FmsMaterialCarriedForward__FmsMaterialWarehouse
  118. FOREIGN KEY (SiteId, BusinessFieldId, WarehouseId) REFERENCES FmsMaterialWarehouse(SiteId, BusinessFieldId, WarehouseId)
  119. ;
  120. ALTER TABLE FmsMaterialCarriedForward
  121. ADD CONSTRAINT FK__FmsMaterialCarriedForward__FmsMaterial
  122. FOREIGN KEY (SiteId, BusinessFieldId, MaterialId) REFERENCES FmsMaterial(SiteId, BusinessFieldId, MaterialId)
  123. ;