dd04f92fe58c2177498a6a573324a623352f5a45.svn-base 19 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591
  1. ---------------------------------------------------
  2. -- 기초코드관리
  3. ---------------------------------------------------
  4. ---- work_because_first_WobeFirst
  5. ---- 작업 관리/원인 분류/대분류
  6. -- WobeFirst, WobeSecond를 통합
  7. CREATE TABLE FmsWorkCodeCauseClass
  8. (
  9. SiteId int NOT NULL , -- 현장 고유번호
  10. CauseClassId int identity(1,1) NOT NULL , -- 원인분류 고유번호
  11. ParentId int NOT NULL , -- 부모 원인분류 고유번호
  12. Name nvarchar(50) NOT NULL , -- 작업유형 이름
  13. IsUse bit NULL , -- 사용 유무
  14. PRIMARY KEY (SiteId ASC , CauseClassId ASC)
  15. );
  16. -- work_code_emergency_WocoEmergency
  17. -- 긴급 정도 코드
  18. -- 작업 관리/기초 코드 관리/긴급 정도 코드
  19. --CREATE TABLE WocoEmergency
  20. CREATE TABLE FmsWorkCodeEmergency
  21. (
  22. SiteId int NOT NULL , -- 현장 고유번호
  23. EmergencyId int identity(1,1) NOT NULL , -- 긴급 작업 고유번호
  24. Name nvarchar(50) NULL , -- 긴급 작업 이름
  25. IsUse bit NULL , -- 사용 유무
  26. PRIMARY KEY (SiteId ASC , EmergencyId ASC)
  27. );
  28. -- work_code_kind_WocoKind
  29. -- 작업 구분 코드
  30. -- 작업 관리/기초 코드 관리/작업 구분 코드
  31. --CREATE TABLE WocoKind
  32. CREATE TABLE FmsWorkCodeType
  33. (
  34. WorkTypeId int NOT NULL , -- 작업구분코드 고유번호 (점검, 법정검사, 민원 등)
  35. Name nvarchar(50) NOT NULL , -- 작업구분코드 이름
  36. [Description] nvarchar(512) NULL , -- 설명
  37. PRIMARY KEY (WorkTypeId ASC)
  38. );
  39. INSERT INTO FmsWorkCodeType VALUES (1, '점검계획', '');
  40. INSERT INTO FmsWorkCodeType VALUES (2, '법정검사', '');
  41. INSERT INTO FmsWorkCodeType VALUES (3, '민원', '');
  42. -- work_code_progress_WocoProgress
  43. -- 진행 상태 코드
  44. -- 작업 관리/기초 코드 관리/진행 상태 코드
  45. --CREATE TABLE WocoProgress
  46. CREATE TABLE FmsWorkCodeProgress
  47. (
  48. WorkProgressId int NOT NULL , -- 진행상태 고유번호 (대기, 작업중, 승인 등)
  49. Name nvarchar(24) NOT NULL , -- 진행상태 이름
  50. [Description] nvarchar(512) NULL , -- 설명
  51. PRIMARY KEY (WorkProgressId ASC)
  52. );
  53. INSERT INTO FmsWorkCodeProgress VALUES (1, '작업계획', '');
  54. INSERT INTO FmsWorkCodeProgress VALUES (2, '작업보류', '');
  55. INSERT INTO FmsWorkCodeProgress VALUES (3, '작업대기', '');
  56. INSERT INTO FmsWorkCodeProgress VALUES (4, '작업진행중', '');
  57. INSERT INTO FmsWorkCodeProgress VALUES (5, '작업완료', '');
  58. INSERT INTO FmsWorkCodeProgress VALUES (6, '완료승인', '');
  59. ---------------------------------------------------
  60. -- 실행 계획 및 작업지시
  61. ---------------------------------------------------
  62. -- 실행 계획(=작업요청 =민원)
  63. -- CREATE TABLE WowoRequest
  64. CREATE TABLE FmsWorkRequest
  65. (
  66. SiteId int NOT NULL , -- 현장 고유번호
  67. WorkRequestId int identity(1,1) NOT NULL , -- 작업요청 고유번호
  68. WorkTypeId int NOT NULL , -- 작업요청유형 고유번호 (점검계획, 법정감사, 민원)
  69. BusinessFieldId int NOT NULL , -- 작업장소 고유번호
  70. WorkScheduleId int NULL , -- 기초계획 고유번호 (민원이면 NULL)
  71. WorkProgressId int NOT NULL , -- 진행상태 고유번호
  72. Title nvarchar(200) NOT NULL , -- 작업요청 제목
  73. Content nvarchar(1024) NULL , -- 작업요청자 상세내용
  74. RequestUserId nvarchar(24) NULL , -- 작업요청자 아이디 (데이터게이트웨이에서 올라오면 요청자가 없을 수 있다.)
  75. RequestDate datetime NOT NULL , -- 작업요청 날짜
  76. StartWorkDate datetime NOT NULL , -- 작업시작일
  77. InspectionAgencyId int NULL , -- 법정검사기관 고유번호(법정검사인 경우만 사용)
  78. RejectUserId nvarchar(24) NULL, -- 반려한 사용자 ID
  79. RejectDate datetime NULL, -- 반려한 일시
  80. RejectReason nvarchar(512) NULL , -- 반려한 이유
  81. PRIMARY KEY (SiteId ASC , WorkRequestId ASC)
  82. );
  83. DBCC CHECKIDENT( FmsWorkRequest, RESEED, 1 )
  84. --CREATE TABLE WowoWorkTargetFacility
  85. CREATE TABLE FmsWorkRequestToFacility
  86. (
  87. SiteId int NOT NULL , -- 현장 고유번호
  88. WorkRequestId int NOT NULL , -- 작업요청 고유번호
  89. FacilityCode int NOT NULL , -- 설비코드
  90. PRIMARY KEY (SiteId ASC , WorkRequestId ASC , FacilityCode ASC)
  91. );
  92. -- 작업지시
  93. --CREATE TABLE WowoOrder
  94. CREATE TABLE FmsWorkOrder
  95. (
  96. SiteId int NOT NULL , -- 현장 고유번호
  97. WorkRequestId int NOT NULL , -- 작업요청 고유번호
  98. BusinessFieldId int NOT NULL , -- 작업영역 고유번호
  99. EmergencyId int NULL, -- 긴급정보코드(자동지시인 경우 없을 수도 있다. 있다면 하드코딩으로 변경해야 함)
  100. OrderUserId nvarchar(24) NOT NULL , -- 작업지시한 사용자 고유번호
  101. OrderDate datetime NOT NULL , -- 작업지시한 날짜
  102. Title nvarchar(200) NOT NULL , -- 작업지시한 제목
  103. Content nvarchar(2000) NULL , -- 작업지시한 내용
  104. StartWorkDate datetime NOT NULL , -- 작업시작일
  105. PRIMARY KEY (SiteId ASC , WorkRequestId ASC)
  106. );
  107. DBCC CHECKIDENT( FmsWorkOrder, RESEED, 1 )
  108. --CREATE TABLE WowoWorkTargetFacility
  109. CREATE TABLE FmsWorkOrderToFacility
  110. (
  111. SiteId int NOT NULL , -- 현장 고유번호
  112. WorkRequestId int NOT NULL , -- 작업지시 고유번호
  113. FacilityCode int NOT NULL , -- 설비코드
  114. PRIMARY KEY (SiteId ASC , WorkRequestId ASC , FacilityCode ASC)
  115. );
  116. ---------------------------------------------------
  117. -- 작업결과등록
  118. ---------------------------------------------------
  119. --CREATE TABLE WowoResult
  120. CREATE TABLE FmsWorkResult
  121. (
  122. SiteId int NOT NULL , -- 현장 고유번호
  123. WorkRequestId int NOT NULL , -- 실행계획 고유번호
  124. WorkerUserId nvarchar(24) NOT NULL , -- 작업자 고유번호
  125. StartDate datetime NOT NULL , -- 작업 시작일
  126. EndDate datetime NOT NULL , -- 작업 완료일
  127. CauseClassId int NULL , -- 작업유형 고유번호
  128. Content nvarchar(1024) NULL , -- 작업 상세내용
  129. ConfirmedDate datetime NULL , -- 작업결과 승인날짜
  130. ConfirmedUserId nvarchar(24) NULL , -- 작업결과 승인 사용자 고유번호
  131. IsConfirmed bit NOT NULL , -- 작업승인확인
  132. PRIMARY KEY (SiteId ASC , WorkRequestId ASC)
  133. );
  134. -- 작업 결과 용 대상시설 관계 테이블
  135. --CREATE TABLE WowoWorkTargetFacility
  136. CREATE TABLE FmsWorkResultToFacility
  137. (
  138. SiteId int NOT NULL , -- 현장 고유번호
  139. WorkRequestId int NOT NULL , -- 작업결과의 작업지시 고유번호
  140. FacilityCode int NOT NULL , -- 설비코드
  141. PRIMARY KEY (SiteId ASC , WorkRequestId ASC , FacilityCode ASC)
  142. );
  143. -- 작업 결과 점검 항목
  144. --CREATE TABLE WowoResultCheckItem
  145. CREATE TABLE FmsWorkResultCheckItem
  146. (
  147. SiteId int NOT NULL , -- 현장 고유번호
  148. WorkRequestId int NOT NULL , -- 작업지시 고유번호
  149. FacilityCode int NOT NULL , -- 설비코드
  150. CheckItemId int NOT NULL , -- 작업항목 고유번호
  151. -- IsYesNoResult bit NOT NULL , -- 예,아니오 결과
  152. BoolResult bit NULL , -- 작업 결과 (조치여부)
  153. TextResult nvarchar(1024) NULL , -- 작업 결과 수기 항목
  154. -- CheckItemName nvarchar(100) NOT NULL , -- 작업 항목 이름
  155. -- SortOrderNo int NULL , -- 정렬 번호
  156. PRIMARY KEY (SiteId ASC , WorkRequestId ASC , FacilityCode ASC , CheckItemId ASC)
  157. );
  158. -- 작업결과 첨부파일
  159. --CREATE TABLE WowoResultFile
  160. CREATE TABLE FmsWorkResultFile
  161. (
  162. SiteId int NOT NULL , -- 현장 고유번호
  163. WorkRequestId int NOT NULL , -- 작업지시 고유번호
  164. FileId int NOT NULL , -- 파일 고유번호
  165. PRIMARY KEY (SiteId ASC , WorkRequestId ASC)
  166. );
  167. -- 작업결과 법정검사 정보
  168. --CREATE TABLE WowoResultLaw
  169. CREATE TABLE FmsWorkResultLaw
  170. (
  171. SiteId int NOT NULL , -- 현장 고유번호
  172. WorkRequestId int NOT NULL , -- 작업지시 고유번호
  173. CertificationNo nvarchar(50) NULL , -- 검사증 번호
  174. CheckCommission numeric(10,0) NULL , -- 검사 수수료
  175. CheckResult bit NULL , -- 검사 결과
  176. PRIMARY KEY (SiteId ASC , WorkRequestId ASC)
  177. );
  178. ---------------------------------------------------
  179. -- 작업지시
  180. ---------------------------------------------------
  181. ALTER TABLE FmsWorkCodeCauseClass
  182. ADD CONSTRAINT FK__FmsWorkCodeCauseClass__CmSite
  183. FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
  184. ;
  185. ALTER TABLE FmsWorkCodeEmergency
  186. ADD CONSTRAINT FK__FmsWorkCodeEmergency__CmSite
  187. FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
  188. ;
  189. -- 실행계획(작업요청) FK
  190. ALTER TABLE FmsWorkRequest
  191. ADD CONSTRAINT FK__FmsWorkRequest__CmSite
  192. FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
  193. ;
  194. ALTER TABLE FmsWorkRequest
  195. ADD CONSTRAINT FK__FmsWorkRequest__FmsWorkCodeWorkType
  196. FOREIGN KEY (WorkTypeId) REFERENCES FmsWorkCodeType(WorkTypeId)
  197. ;
  198. ALTER TABLE FmsWorkRequest
  199. ADD CONSTRAINT FK__FmsWorkRequest__FmsWorkSchedule
  200. FOREIGN KEY (SiteId, WorkScheduleId) REFERENCES FmsWorkSchedule(SiteId, WorkScheduleId)
  201. ;
  202. ALTER TABLE FmsWorkRequest
  203. ADD CONSTRAINT FK__FmsWorkRequest__FmsWorkCodeProgress
  204. FOREIGN KEY (WorkProgressId) REFERENCES FmsWorkCodeProgress(WorkProgressId)
  205. ;
  206. ALTER TABLE FmsWorkRequest
  207. ADD CONSTRAINT FK__FmsWorkRequest__CmBusinessField
  208. FOREIGN KEY (SiteId , BusinessFieldId) REFERENCES CmBusinessField(SiteId , BusinessFieldId)
  209. ;
  210. ALTER TABLE FmsWorkRequest
  211. ADD CONSTRAINT FK__FmsWorkRequest__CmUser_AsRequestUserId
  212. FOREIGN KEY (SiteId , RequestUserId) REFERENCES CmUser(SiteId , UserId)
  213. ;
  214. ALTER TABLE FmsWorkRequest
  215. ADD CONSTRAINT FK__FmsWorkRequest__CmUser1_AsRejectUserId
  216. FOREIGN KEY (SiteId , RejectUserId) REFERENCES CmUser(SiteId , UserId)
  217. ;
  218. ALTER TABLE FmsWorkRequest
  219. ADD CONSTRAINT FK__FmsWorkRequest__FmsWorkCodeLegalInspectionAgency
  220. FOREIGN KEY (SiteId , InspectionAgencyId) REFERENCES FmsWorkCodeLegalInspectionAgency(SiteId , InspectionAgencyId)
  221. ;
  222. ALTER TABLE FmsWorkRequestToFacility
  223. ADD CONSTRAINT FK__FmsWorkRequestToFacility__FmsWorkRequest
  224. FOREIGN KEY (SiteId , WorkRequestId) REFERENCES FmsWorkRequest(SiteId , WorkRequestId)
  225. ON DELETE CASCADE
  226. ;
  227. ALTER TABLE FmsWorkRequestToFacility
  228. ADD CONSTRAINT FK__FmsWorkRequestToFacility__CmFacility
  229. FOREIGN KEY (SiteId , FacilityCode) REFERENCES CmFacility(SiteId , FacilityCode)
  230. ;
  231. -- 작업 지시 FK
  232. ALTER TABLE FmsWorkOrder
  233. ADD CONSTRAINT FK__FmsWorkOrder__CmSite
  234. FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
  235. ;
  236. ALTER TABLE FmsWorkOrder
  237. ADD CONSTRAINT FK__FmsWorkOrder__FmsWorkRequest
  238. FOREIGN KEY (SiteId , WorkRequestId) REFERENCES FmsWorkRequest(SiteId , WorkRequestId)
  239. ;
  240. ALTER TABLE FmsWorkOrder
  241. ADD CONSTRAINT FK__FmsWorkOrder__FmsWorkCodeEmergency
  242. FOREIGN KEY (SiteId , EmergencyId) REFERENCES FmsWorkCodeEmergency(SiteId , EmergencyId)
  243. ;
  244. ALTER TABLE FmsWorkOrder
  245. ADD CONSTRAINT FK__FmsWorkOrder__CmUser
  246. FOREIGN KEY (SiteId , OrderUserId) REFERENCES CmUser(SiteId , UserId)
  247. ;
  248. ALTER TABLE FmsWorkOrder
  249. ADD CONSTRAINT FK__FmsWorkOrder__CmBusinessField
  250. FOREIGN KEY (SiteId , BusinessFieldId) REFERENCES CmBusinessField(SiteId , BusinessFieldId)
  251. ;
  252. ALTER TABLE FmsWorkOrderToFacility
  253. ADD CONSTRAINT FK__FmsWorkOrderToFacility__FmsWorkOrder
  254. FOREIGN KEY (SiteId , WorkRequestId) REFERENCES FmsWorkOrder(SiteId , WorkRequestId)
  255. ON DELETE CASCADE
  256. ;
  257. ALTER TABLE FmsWorkOrderToFacility
  258. ADD CONSTRAINT FK__FmsWorkOrderToFacility__CmFacility
  259. FOREIGN KEY (SiteId , FacilityCode) REFERENCES CmFacility(SiteId , FacilityCode)
  260. ;
  261. ---------------------------------------------------
  262. -- 작업결과등록
  263. ---------------------------------------------------
  264. ALTER TABLE FmsWorkResult
  265. ADD CONSTRAINT FK__FmsWorkResult__FmsWorkOrder
  266. FOREIGN KEY (SiteId , WorkRequestId) REFERENCES FmsWorkOrder(SiteId , WorkRequestId)
  267. ;
  268. ALTER TABLE FmsWorkResult
  269. ADD CONSTRAINT FK__FmsWorkResult__FmsWorkCodeCauseClass
  270. FOREIGN KEY (SiteId , CauseClassId) REFERENCES FmsWorkCodeCauseClass(SiteId , CauseClassId)
  271. ;
  272. ALTER TABLE FmsWorkResult
  273. ADD CONSTRAINT FK__FmsWorkResult__CmUser_AsWorkerUserId
  274. FOREIGN KEY (SiteId , WorkerUserId) REFERENCES CmUser(SiteId , UserId)
  275. ;
  276. ALTER TABLE FmsWorkResult
  277. ADD CONSTRAINT FK__FmsWorkResult__CmUser_AsConfirmedUserId
  278. FOREIGN KEY (SiteId , ConfirmedUserId) REFERENCES CmUser(SiteId , UserId)
  279. ;
  280. ALTER TABLE FmsWorkResultCheckItem
  281. ADD CONSTRAINT FK__FmsWorkResultCheckItem__FmsWorkResult
  282. FOREIGN KEY (SiteId , WorkRequestId) REFERENCES FmsWorkResult(SiteId , WorkRequestId)
  283. ;
  284. ALTER TABLE FmsWorkResultCheckItem
  285. ADD CONSTRAINT FK__FmsWorkResultCheckItem__FmsFacilityCheckItem
  286. FOREIGN KEY (SiteId , FacilityCode, CheckItemId) REFERENCES FmsFacilityCheckItem(SiteId, FacilityCode, CheckItemId)
  287. ;
  288. ALTER TABLE FmsWorkResultCheckItem
  289. ADD CONSTRAINT FK__FmsWorkResultCheckItem__CmFacility
  290. FOREIGN KEY (SiteId , FacilityCode) REFERENCES CmFacility(SiteId, FacilityCode)
  291. ;
  292. ALTER TABLE FmsWorkResultToFacility
  293. ADD CONSTRAINT FK__FmsWorkResultToFacility__FmsWorkOrder
  294. FOREIGN KEY (SiteId , WorkRequestId) REFERENCES FmsWorkResult(SiteId , WorkRequestId)
  295. ON DELETE CASCADE
  296. ;
  297. ALTER TABLE FmsWorkResultToFacility
  298. ADD CONSTRAINT FK__FmsWorkResultToFacility__CmFacility
  299. FOREIGN KEY (SiteId , FacilityCode) REFERENCES CmFacility(SiteId , FacilityCode)
  300. ;
  301. ALTER TABLE FmsWorkResultFile
  302. ADD CONSTRAINT FK__FmsWorkResultFile__FmsWorkResult
  303. FOREIGN KEY (SiteId , WorkRequestId) REFERENCES FmsWorkResult(SiteId , WorkRequestId)
  304. ;
  305. ALTER TABLE FmsWorkResultFile
  306. ADD CONSTRAINT FK__FmsWorkResultFile__CmFile
  307. FOREIGN KEY (SiteId , FileId) REFERENCES CmFile(SiteId , FileId)
  308. ;
  309. ALTER TABLE FmsWorkResultLaw
  310. ADD CONSTRAINT FK__FmsWorkResultLaw__FmsWorkResult
  311. FOREIGN KEY (SiteId , WorkRequestId) REFERENCES FmsWorkResult(SiteId , WorkRequestId)
  312. ;
  313. ALTER TABLE FmsWorkSchedule
  314. ADD CONSTRAINT FK__FmsWorkSchedule__FmsWorkCodeType
  315. FOREIGN KEY (WorkTypeId) REFERENCES FmsWorkCodeType(WorkTypeId)
  316. ;
  317. ALTER TABLE FmsFacilityCheckItem
  318. ADD CONSTRAINT FK__FmsFacilityCheckItem__FmsWorkCodeType
  319. FOREIGN KEY (WorkTypeId) REFERENCES FmsWorkCodeType(WorkTypeId)
  320. ;
  321. ---- work_because_first_WobeFirst
  322. ---- 작업 관리/원인 분류/대분류
  323. ----CREATE TABLE WobeFirst
  324. --CREATE TABLE FmsWorkCodeCauseClassFirst
  325. --(
  326. -- FirstId int NOT NULL ,
  327. -- FirstName nvarchar(50) NOT NULL ,
  328. -- SortOrderNo int NULL ,
  329. -- IsUse bit NULL ,
  330. -- InsertedDate datetime NULL ,
  331. -- InsertedUserId nvarchar(24) NULL ,
  332. -- UpdatedDate datetime NULL ,
  333. -- UpdatedUserId nvarchar(24) NULL ,
  334. -- PRIMARY KEY (FirstId ASC)
  335. --);
  336. ----CREATE TABLE WobeSecond
  337. --CREATE TABLE FmsWorkCodeCauseClassSecond
  338. --(
  339. -- FirstId int NOT NULL ,
  340. -- SecondId int NOT NULL ,
  341. -- SecondName nvarchar(50) NOT NULL ,
  342. -- SortOrderNo int NULL ,
  343. -- IsUse bit NULL ,
  344. -- InsertedDate datetime NULL ,
  345. -- InsertedUserId nvarchar(24) NULL ,
  346. -- UpdatedDate datetime NULL ,
  347. -- UpdatedUserId nvarchar(24) NULL ,
  348. -- PRIMARY KEY (SecondId ASC)
  349. --);
  350. -- work_work_orderMaterial_WowoOrderMaterial
  351. -- 지시 자재 리스트
  352. -- work/work/orderMaterial
  353. --CREATE TABLE WowoOrderMaterial
  354. --CREATE TABLE FmsWowoOrderMaterial
  355. --(
  356. -- WorkOrderId numeric(8,0) NOT NULL ,
  357. -- MaterialId nvarchar(30) NOT NULL ,
  358. -- OrderCount int NOT NULL ,
  359. -- DeliveryCount int NULL ,
  360. -- ReturnCount int NULL ,
  361. -- UseCount int NULL ,
  362. -- IsDelivery bit NOT NULL ,
  363. -- InoutDate datetime NULL ,
  364. -- PRIMARY KEY (WorkOrderId ASC,MaterialId ASC)
  365. --);
  366. ---------------------------------------------------
  367. -- 작업결과등록
  368. ---------------------------------------------------
  369. --CREATE TABLE WowoMaterial
  370. --(
  371. -- WorkOrderId numeric(8,0) NOT NULL ,
  372. -- ResultId int NOT NULL ,
  373. -- MaterialId nvarchar(30) NOT NULL ,
  374. -- DeliveryNo nvarchar(30) NOT NULL ,
  375. -- OrderCount int NOT NULL ,
  376. -- OrderNote nvarchar(2000) NULL ,
  377. -- DeliveryId int NOT NULL ,
  378. -- PRIMARY KEY (WorkOrderId ASC,ResultId ASC,MaterialId ASC,DeliveryNo ASC)
  379. --)
  380. --;
  381. ---------------------------------------------------
  382. -- 기초코드관리
  383. ---------------------------------------------------
  384. --ALTER TABLE WobeSecond
  385. -- ADD FOREIGN KEY (firstSeq) REFERENCES WobeFirst(firstSeq)
  386. --;
  387. ---------------------------------------------------
  388. -- 작업지시
  389. ---------------------------------------------------
  390. --ALTER TABLE FmsWorkOrderMaterial
  391. -- ADD FOREIGN KEY (WorkOrderId) REFERENCES FmsWorkOrder(WorkOrderId)
  392. --;
  393. --ALTER TABLE FmsWorkOrderMaterial
  394. -- ADD FOREIGN KEY (materialSeq) REFERENCES MamaMaterial(materialSeq)
  395. --;
  396. -- SuteTeam : 근무편성관리
  397. --ALTER TABLE FmsWorkOrder
  398. -- ADD FOREIGN KEY (CompanyId,TeamId) REFERENCES SuteTeam(CompanyId,TeamId)
  399. --;
  400. --ALTER TABLE FmsWorkOrderMaterial
  401. -- ADD FOREIGN KEY (warehouseSeq) REFERENCES MaplWarehouse(warehouseSeq)
  402. --;
  403. --ALTER TABLE FmsWorkRequest
  404. -- ADD FOREIGN KEY (alarmDatetime,systemSeq,pointId) REFERENCES BaalAlarm(alarmDatetime,systemSeq,pointId)
  405. --;
  406. --ALTER TABLE FmsWorkRequestSmsTeam
  407. -- ADD FOREIGN KEY (CompanyId,TeamId) REFERENCES SuteTeam(CompanyId,TeamId)
  408. --;
  409. ---------------------------------------------------
  410. -- 작업결과등록
  411. ---------------------------------------------------
  412. --ALTER TABLE WowoMaterial
  413. -- ADD FOREIGN KEY (WorkOrderId,ResultId) REFERENCES FmsWorkResult(WorkOrderId,ResultId)
  414. --;
  415. --ALTER TABLE WowoMaterial
  416. -- ADD FOREIGN KEY (materialSeq) REFERENCES MamaMaterial(materialSeq)
  417. --;
  418. --ALTER TABLE WowoMaterial
  419. -- ADD FOREIGN KEY (warehouseSeq) REFERENCES MaplWarehouse(warehouseSeq)
  420. --;
  421. --ALTER TABLE WowoMaterial
  422. -- ADD FOREIGN KEY (enterNo,deliveryNo) REFERENCES MamsLink(enterNo,deliveryNo)
  423. --;
  424. --ALTER TABLE WowoMaterial
  425. -- ADD FOREIGN KEY (deliverySeq) REFERENCES MacoDelivery(deliverySeq)
  426. --;
  427. --ALTER TABLE FmsWorkRequest
  428. -- ADD CONSTRAINT FK__FmsWorkRequest__BemsMonitoringPoint
  429. -- FOREIGN KEY (SiteId , AlarmFacilityCode , AlarmPropertyId)
  430. -- REFERENCES BemsMonitoringPoint(SiteId , FacilityCode , PropertyId)
  431. --;
  432. -- WocoAccept는 연동된 테이블을 찾지 못했고, 일지 작성주기 코드는 인수인계관리에서 사용하기 때문에 일단 사용하지 않는다.
  433. ---- work_code_acpt_WocoAcpt
  434. ---- 작업 관리/기초 코드 관리/작업 구분 코드
  435. ----CREATE TABLE WocoAccept
  436. --CREATE TABLE FmsWorkCodeAccept
  437. --(
  438. -- AcceptId int NOT NULL ,
  439. -- Name nvarchar(50) NOT NULL ,
  440. -- IsUse bit NULL ,
  441. -- SortOrderNo int NULL ,
  442. -- InsertedDate datetime NULL ,
  443. -- InsertedUserId nvarchar(24) NULL ,
  444. -- UpdatedDate datetime NULL ,
  445. -- UpdatedUserId nvarchar(24) NULL ,
  446. -- PRIMARY KEY (AcceptId ASC)
  447. --);
  448. ---- 일지작성주기코드
  449. ---- 작업 관리/기초 코드 관리/일지작성주기코드
  450. ----CREATE TABLE WocoCycle
  451. --CREATE TABLE FmsWorkCodeCycle
  452. --(
  453. -- CycleId int NOT NULL ,
  454. -- Name nvarchar(50) NOT NULL ,
  455. -- IsUse bit NULL ,
  456. -- SortOrderNo int NULL ,
  457. -- InsertedDate datetime NULL ,
  458. -- InsertedUserId nvarchar(24) NULL ,
  459. -- UpdatedDate datetime NULL ,
  460. -- UpdatedUserId nvarchar(24) NULL ,
  461. -- PRIMARY KEY (CycleId ASC)
  462. --);