1755f200b42692b5dfb35e6e72d493d77cb0dab8.svn-base 7.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192
  1. ---------------------------------------------------
  2. -- 자재구매관리
  3. ---------------------------------------------------
  4. -- 자재 구매 신청
  5. -- MapuRequest
  6. CREATE TABLE FmsMaterialPurchaseRequest
  7. (
  8. SiteId int NOT NULL , -- 현장 고유번호
  9. BusinessFieldId int NOT NULL , -- 업무분야 고유번호
  10. PurchaseRequestId int identity(1,1) NOT NULL , -- 구매신청 고유번호
  11. PurchaseTypeId smallint NOT NULL , -- 구매구분 고유번호
  12. RequestDate datetime NOT NULL , -- 구매신청일시
  13. RequestUserId nvarchar(24) NOT NULL , -- 구매요청자 사용자 고유번호
  14. PurchaseHopeDate datetime NULL, -- 구매희망일
  15. Title nvarchar(256) NOT NULL , -- 구매신청 제목
  16. Reason nvarchar(2000) NOT NULL , -- 구매신청 사유
  17. ProgressId smallint NOT NULL , -- 구매 신청 진행 고유번호
  18. ApprovalDate datetime NULL , -- 승인일시
  19. ApprovalUserId nvarchar(24) NULL , -- 승인자 고유번호
  20. PRIMARY KEY (SiteId ASC , BusinessFieldId ASC , PurchaseRequestId ASC)
  21. );
  22. -- 자재 구매 신청 자재들
  23. CREATE TABLE FmsMaterialPurchaseRequestMaterial
  24. (
  25. SiteId int NOT NULL , -- 현장 고유번호
  26. BusinessFieldId int NOT NULL , -- 업무분야 고유번호
  27. PurchaseRequestId int NOT NULL , -- 구매신청 고유번호
  28. MaterialId int NOT NULL , -- 자재 고유번호
  29. MaterialCount int NOT NULL , -- 자재 요청 수량
  30. PRIMARY KEY (SiteId ASC , BusinessFieldId ASC , PurchaseRequestId ASC , MaterialId ASC)
  31. );
  32. -- 자재 발주 정보
  33. -- MapuRequest
  34. CREATE TABLE FmsMaterialPurchaseOrder
  35. (
  36. SiteId int NOT NULL , -- 현장 고유번호
  37. BusinessFieldId int NOT NULL , -- 업무분야 고유번호
  38. PurchaseOrderId int identity(1,1) NOT NULL , -- 자재발주 고유번호
  39. PurchaseRequestId int NOT NULL , -- 구매신청 고유번호
  40. TradeCompanyId int NOT NULL, -- 거래처 고유번호
  41. OrderDate datetime NOT NULL , -- 발주신청일시
  42. OrderUserId nvarchar(24) NOT NULL , -- 발주 사용자 고유번호
  43. Title nvarchar(256) NOT NULL , -- 발주 제목
  44. Reason nvarchar(2000) NOT NULL , -- 발주 사유
  45. StoredDueDate datetime NULL , -- 입고 예정일
  46. FileId int NULL , -- 발주서 파일
  47. PRIMARY KEY (SiteId ASC , BusinessFieldId ASC , PurchaseOrderId ASC)
  48. );
  49. -- 발주서 자재들
  50. CREATE TABLE FmsMaterialPurchaseOrderMaterial
  51. (
  52. SiteId int NOT NULL , -- 현장 고유번호
  53. BusinessFieldId int NOT NULL , -- 업무분야 고유번호
  54. PurchaseOrderId int NOT NULL , -- 자재발주 고유번호
  55. MaterialId int NOT NULL , -- 자재 고유번호
  56. MaterialCount int NOT NULL , -- 자재 요청 수량
  57. UnitCost int NOT NULL , -- 자재 단가
  58. PRIMARY KEY (SiteId ASC , BusinessFieldId ASC , PurchaseOrderId ASC , MaterialId ASC)
  59. );
  60. ---- 자재 발주서 파일들
  61. --CREATE TABLE FmsMaterialPurchaseOrderFile
  62. --(
  63. -- SiteId int NOT NULL , -- 현장 고유번호
  64. -- BusinessFieldId int NOT NULL , -- 업무분야 고유번호
  65. -- PurchaseOrderId int NOT NULL , -- 자재발주 고유번호
  66. -- FileId int NOT NULL , -- 파일 고유번호
  67. -- PRIMARY KEY (SiteId ASC , BusinessFieldId ASC , PurchaseOrderId ASC , FileId ASC)
  68. --);
  69. -- 구매 요청 FK
  70. ALTER TABLE FmsMaterialPurchaseRequest
  71. ADD CONSTRAINT FK__FmsMaterialPurchaseRequest__CmSite
  72. FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
  73. ;
  74. ALTER TABLE FmsMaterialPurchaseRequest
  75. ADD CONSTRAINT FK__FmsMaterialPurchaseRequest__CmBusinessField
  76. FOREIGN KEY (SiteId, BusinessFieldId) REFERENCES CmBusinessField(SiteId, BusinessFieldId)
  77. ;
  78. ALTER TABLE FmsMaterialPurchaseRequest
  79. ADD CONSTRAINT FK__FmsMaterialPurchaseRequest__FmsMaterialCodePurchaseType
  80. FOREIGN KEY (SiteId, PurchaseTypeId) REFERENCES FmsMaterialCodePurchaseType(SiteId, PurchaseTypeId)
  81. ;
  82. -- 주의!!!
  83. -- FK 이름 생성 규칙 : 같은 테이블에 여러개의 필드가 하나의 테이블에 FK를 가질 경우,
  84. -- 네이밍 순서가 넘버링으로 결정된다. 아래와 같은 경우 그것을 보장받기 위해 CmUser1로 명시했다.
  85. ALTER TABLE FmsMaterialPurchaseRequest
  86. ADD CONSTRAINT FK__FmsMaterialPurchaseRequest__CmUser_AsRequestUserId
  87. FOREIGN KEY (SiteId, RequestUserId) REFERENCES CmUser(SiteId, UserId)
  88. ;
  89. ALTER TABLE FmsMaterialPurchaseRequest
  90. ADD CONSTRAINT FK__FmsMaterialPurchaseRequest__CmUser1_AsApprovalUserId
  91. FOREIGN KEY (SiteId, ApprovalUserId) REFERENCES CmUser(SiteId, UserId)
  92. ;
  93. ALTER TABLE FmsMaterialPurchaseRequest
  94. ADD CONSTRAINT FK__FmsMaterialPurchaseRequest__FmsMaterialCodeProgress
  95. FOREIGN KEY (ProgressId) REFERENCES FmsMaterialCodeProgress(ProgressId)
  96. ;
  97. -- 구매 신청 자재들 FK
  98. ALTER TABLE FmsMaterialPurchaseRequestMaterial
  99. ADD CONSTRAINT FK__FmsMaterialPurchaseRequestMaterial__FmsMaterialPurchaseRequest
  100. FOREIGN KEY (SiteId, BusinessFieldId, PurchaseRequestId) REFERENCES FmsMaterialPurchaseRequest(SiteId, BusinessFieldId, PurchaseRequestId)
  101. ;
  102. ALTER TABLE FmsMaterialPurchaseRequestMaterial
  103. ADD CONSTRAINT FK__FmsMaterialPurchaseRequestMaterial__FmsMaterial
  104. FOREIGN KEY (SiteId, BusinessFieldId, MaterialId) REFERENCES FmsMaterial(SiteId, BusinessFieldId, MaterialId)
  105. ;
  106. -- 발주 FK
  107. ALTER TABLE FmsMaterialPurchaseOrder
  108. ADD CONSTRAINT FK__FmsMaterialPurchaseOrder__CmSite
  109. FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
  110. ;
  111. ALTER TABLE FmsMaterialPurchaseOrder
  112. ADD CONSTRAINT FK__FmsMaterialPurchaseOrder__FmsMaterialPurchaseRequest
  113. FOREIGN KEY (SiteId, BusinessFieldId, PurchaseRequestId) REFERENCES FmsMaterialPurchaseRequest(SiteId, BusinessFieldId, PurchaseRequestId)
  114. ;
  115. ALTER TABLE FmsMaterialPurchaseOrder
  116. ADD CONSTRAINT FK__FmsMaterialPurchaseOrder__CmBusinessField
  117. FOREIGN KEY (SiteId, BusinessFieldId) REFERENCES CmBusinessField(SiteId, BusinessFieldId)
  118. ;
  119. ALTER TABLE FmsMaterialPurchaseOrder
  120. ADD CONSTRAINT FK__FmsMaterialPurchaseOrder__CmUser
  121. FOREIGN KEY (SiteId, OrderUserId) REFERENCES CmUser(SiteId, UserId)
  122. ;
  123. ALTER TABLE FmsMaterialPurchaseOrder
  124. ADD CONSTRAINT FK__FmsMaterialPurchaseOrder__FmsMaterialTradeCompany
  125. FOREIGN KEY (SiteId, TradeCompanyId) REFERENCES FmsMaterialTradeCompany(SiteId, TradeCompanyId)
  126. ;
  127. ALTER TABLE FmsMaterialPurchaseOrder
  128. ADD CONSTRAINT FK__FmsMaterialPurchaseOrder__CmFile
  129. FOREIGN KEY (SiteId, FileId) REFERENCES CmFile(SiteId, FileId)
  130. ;
  131. -- 발주 자재들 FK
  132. ALTER TABLE FmsMaterialPurchaseOrderMaterial
  133. ADD CONSTRAINT FK__FmsMaterialPurchaseOrderMaterial__FmsMaterialPurchaseOrder
  134. FOREIGN KEY (SiteId, BusinessFieldId, PurchaseOrderId) REFERENCES FmsMaterialPurchaseOrder(SiteId, BusinessFieldId, PurchaseOrderId)
  135. ;
  136. ALTER TABLE FmsMaterialPurchaseOrderMaterial
  137. ADD CONSTRAINT FK__FmsMaterialPurchaseOrderMaterial__FmsMaterial
  138. FOREIGN KEY (SiteId, BusinessFieldId, MaterialId) REFERENCES FmsMaterial(SiteId, BusinessFieldId, MaterialId)
  139. ;
  140. -- 발주 파일 FK
  141. --ALTER TABLE FmsMaterialPurchaseOrderFile
  142. -- ADD CONSTRAINT FK__FmsMaterialPurchaseOrderFile__FmsMaterialPurchaseOrder
  143. -- FOREIGN KEY (SiteId, BusinessFieldId, PurchaseOrderId) REFERENCES FmsMaterialPurchaseOrder(SiteId, BusinessFieldId, PurchaseOrderId)
  144. --;
  145. --ALTER TABLE FmsMaterialPurchaseOrderFile
  146. -- ADD CONSTRAINT FK__FmsMaterialPurchaseOrderFile__CmFile
  147. -- FOREIGN KEY (SiteId, FileId) REFERENCES CmFile(SiteId, FileId)
  148. --;