i.sql 5.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141
  1. -- insert into temDict_costcenter
  2. -- (CostCenter_code,CostCenter_name,factory_name)
  3. -- values
  4. -- ('9099010501', '品控处', '马鞍山牧场'),
  5. -- ('9099010101', '财务处', '马鞍山牧场'),
  6. -- ('9099010102', '信息处', '马鞍山牧场'),
  7. -- ('9099010103', '办公室', '马鞍山牧场'),
  8. -- ('9099010502', '饲养处', '马鞍山牧场'),
  9. -- ('9099010301', '挤奶处', '马鞍山牧场'),
  10. -- ('9099010503', '技术处', '马鞍山牧场'),
  11. -- ('9099010504', '犊牛处', '马鞍山牧场'),
  12. -- ('9099010505', '饲喂处', '马鞍山牧场'),
  13. -- ('9099010506', '设备处', '马鞍山牧场'),
  14. -- ('9099010507', '安环处', '马鞍山牧场'),
  15. -- ('9099010104', '餐厅', '马鞍山牧场'),
  16. -- ('9099010508', '沼液车间', '马鞍山牧场'),
  17. -- ('9099010401', '沼渣车间', '马鞍山牧场'),
  18. -- ('9099010509', '污水车间', '马鞍山牧场'),
  19. -- ('9099010402', '水处理车间', '马鞍山牧场'),
  20. -- ('9099010105', '总裁办', '马鞍山牧场')
  21. -- ;
  22. -- insert into warehouse (factory_name,warehoseCode,warehosetName,enable)
  23. -- values
  24. -- ('察北牧场','1001', '原材料仓',1),
  25. -- ('察北牧场','1002', '药品仓',1),
  26. -- ('察北牧场','1003', '药品二级库',1),
  27. -- ('察北牧场','1004', '备件仓',1),
  28. -- ('察北牧场','1005', '综合仓',1),
  29. -- ('察北牧场','1101', '青贮仓-1号窖',1),
  30. -- ('察北牧场','1102', '青贮仓-2号窖',1),
  31. -- ('察北牧场','1103', '青贮仓-3号窖',1),
  32. -- ('察北牧场','1104', '青贮仓-4号窖',1),
  33. -- ('察北牧场','1105', '青贮仓-5号窖',1),
  34. -- ('察北牧场','1106', '青贮仓-6号窖',1),
  35. -- ('察北牧场','1107', '青贮仓-7号窖',1),
  36. -- ('察北牧场','1108', '青贮仓-8号窖',1),
  37. -- ('察北牧场','1109', '青贮仓-9号窖',1),
  38. -- ('察北牧场','1110', '青贮仓-10号窖',1),
  39. -- ('察北牧场','1111', '青贮仓-11号窖',1),
  40. -- ('察北牧场','1112', '青贮仓-12号窖',1),
  41. -- ('察北牧场','1113', '青贮仓-13号窖',1),
  42. -- ('察北牧场','1114', '青贮仓-14号窖',1),
  43. -- ('察北牧场','1115', '青贮仓-15号窖',1),
  44. -- ('察北牧场','1116', '青贮仓-16号窖',1),
  45. -- ('察北牧场','1117', '青贮仓-17号窖',1),
  46. -- ('察北牧场','1118', '青贮仓-18号窖',1),
  47. -- ('察北牧场','1119', '青贮仓-19号窖',1),
  48. -- ('察北牧场','1120', '青贮仓-20号窖',1),
  49. -- ('察北牧场','1201', '原奶仓',1);
  50. -- insert into dept_Data values
  51. -- ('1005170501', '二期品控处'),
  52. -- ('1005170101', '二期财务处'),
  53. -- ('1005170102', '二期信息处'),
  54. -- ('1005170103', '二期办公室'),
  55. -- ('1005170502', '二期饲养处'),
  56. -- ('1005170301', '二期挤奶处'),
  57. -- ('1005170503', '二期技术处'),
  58. -- ('1005170504', '二期犊牛处'),
  59. -- ('1005170505', '二期饲喂处'),
  60. -- ('1005170506', '二期设备处'),
  61. -- ('1005170507', '二期沼液车间'),
  62. -- ('1005170401', '二期沼渣车间'),
  63. -- ('1005170508', '二期污水车间'),
  64. -- ('1005170402', '二期水处理车间')
  65. -- insert into cost_center_temp values
  66. -- ('1005050501', '品控处', 'F'),
  67. -- ('1005050101', '财务处', 'M'),
  68. -- ('1005050102', '信息处', 'M'),
  69. -- ('1005050103', '办公室', 'M'),
  70. -- ('1005050502', '饲养处', 'F'),
  71. -- ('1005050301', '挤奶处', 'P'),
  72. -- ('1005050503', '技术处', 'F'),
  73. -- ('1005050504', '犊牛处', 'F'),
  74. -- ('1005050505', '饲喂处', 'F'),
  75. -- ('1005050506', '设备处', 'F'),
  76. -- ('1005050507', '安环处', 'F'),
  77. -- ('1005050508', '沼液车间', 'F'),
  78. -- ('1005050401', '沼渣车间', 'A'),
  79. -- ('1005050509', '污水车间', 'F'),
  80. -- ('1005050402', '水处理车间', 'A'),
  81. -- ('1005050104', '餐厅', 'M'),
  82. -- ('1005170501', '二期品控处', 'F'),
  83. -- ('1005170101', '二期财务处', 'M'),
  84. -- ('1005170102', '二期信息处', 'M'),
  85. -- ('1005170103', '二期办公室', 'M'),
  86. -- ('1005170502', '二期饲养处', 'F'),
  87. -- ('1005170301', '二期挤奶处', 'P'),
  88. -- ('1005170503', '二期技术处', 'F'),
  89. -- ('1005170504', '二期犊牛处', 'F'),
  90. -- ('1005170505', '二期饲喂处', 'F'),
  91. -- ('1005170506', '二期设备处', 'F'),
  92. -- ('1005170507', '二期沼液车间', 'F'),
  93. -- ('1005170401', '二期沼渣车间', 'A'),
  94. -- ('1005170508', '二期污水车间', 'F'),
  95. -- ('1005170402', '二期水处理车间', 'A'),
  96. -- ('1005170509', '二期安环处', 'F')
  97. create temporary table save_temp (
  98. code varchar(100),
  99. providername varchar(100),
  100. brand varchar(100),
  101. price decimal(15,5),
  102. num decimal(15,5)
  103. )
  104. num integer
  105. select pr.contractid from
  106. part_repertory pr
  107. left join warehouse w on w.id =pr.locationId
  108. left join pasture p on (p.id=pr.pastureId or p.id=w.pastureId )
  109. where p.name ='衡水二牧'
  110. and pr.partCode ='14.04.02.01.000335'
  111. select * from partlaid where contractid =598726 and locationid =8855
  112. select * from brand
  113. select * from pasture where name='衡水二牧'
  114. select code from save_temp where code not in (
  115. select pr.partcode
  116. from
  117. part_repertory pr
  118. join save_temp s on pr.partcode =s.code and pr.price =s.price and pr.brand =s.brand
  119. join provider pv on pv.id =pr.providerId and pv.providerName =s.providername
  120. where pr.pastureid =619 or (select pastureId from warehouse w where w.id =pr.locationid) =619
  121. )t
  122. where t.part
  123. update
  124. part_repertory pr
  125. join save_temp s on pr.partcode =s.code and pr.price =s.price and pr.brand =s.brand
  126. join provider pv on pv.id =pr.providerId and pv.providerName =s.providername
  127. set pr.reportery = s.num
  128. where pr.pastureid =619 or (select pastureId from warehouse w where w.id =pr.locationid) =619