123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139 |
- -- insert into temDict_costcenter
- -- (CostCenter_code,CostCenter_name,factory_name)
- -- values
- -- ('9099010501', '品控处', '马鞍山牧场'),
- -- ('9099010101', '财务处', '马鞍山牧场'),
- -- ('9099010102', '信息处', '马鞍山牧场'),
- -- ('9099010103', '办公室', '马鞍山牧场'),
- -- ('9099010502', '饲养处', '马鞍山牧场'),
- -- ('9099010301', '挤奶处', '马鞍山牧场'),
- -- ('9099010503', '技术处', '马鞍山牧场'),
- -- ('9099010504', '犊牛处', '马鞍山牧场'),
- -- ('9099010505', '饲喂处', '马鞍山牧场'),
- -- ('9099010506', '设备处', '马鞍山牧场'),
- -- ('9099010507', '安环处', '马鞍山牧场'),
- -- ('9099010104', '餐厅', '马鞍山牧场'),
- -- ('9099010508', '沼液车间', '马鞍山牧场'),
- -- ('9099010401', '沼渣车间', '马鞍山牧场'),
- -- ('9099010509', '污水车间', '马鞍山牧场'),
- -- ('9099010402', '水处理车间', '马鞍山牧场'),
- -- ('9099010105', '总裁办', '马鞍山牧场')
- -- ;
- -- insert into warehouse (factory_name,warehoseCode,warehosetName,enable)
- -- values
- -- ('察北牧场','1001', '原材料仓',1),
- -- ('察北牧场','1002', '药品仓',1),
- -- ('察北牧场','1003', '药品二级库',1),
- -- ('察北牧场','1004', '备件仓',1),
- -- ('察北牧场','1005', '综合仓',1),
- -- ('察北牧场','1101', '青贮仓-1号窖',1),
- -- ('察北牧场','1102', '青贮仓-2号窖',1),
- -- ('察北牧场','1103', '青贮仓-3号窖',1),
- -- ('察北牧场','1104', '青贮仓-4号窖',1),
- -- ('察北牧场','1105', '青贮仓-5号窖',1),
- -- ('察北牧场','1106', '青贮仓-6号窖',1),
- -- ('察北牧场','1107', '青贮仓-7号窖',1),
- -- ('察北牧场','1108', '青贮仓-8号窖',1),
- -- ('察北牧场','1109', '青贮仓-9号窖',1),
- -- ('察北牧场','1110', '青贮仓-10号窖',1),
- -- ('察北牧场','1111', '青贮仓-11号窖',1),
- -- ('察北牧场','1112', '青贮仓-12号窖',1),
- -- ('察北牧场','1113', '青贮仓-13号窖',1),
- -- ('察北牧场','1114', '青贮仓-14号窖',1),
- -- ('察北牧场','1115', '青贮仓-15号窖',1),
- -- ('察北牧场','1116', '青贮仓-16号窖',1),
- -- ('察北牧场','1117', '青贮仓-17号窖',1),
- -- ('察北牧场','1118', '青贮仓-18号窖',1),
- -- ('察北牧场','1119', '青贮仓-19号窖',1),
- -- ('察北牧场','1120', '青贮仓-20号窖',1),
- -- ('察北牧场','1201', '原奶仓',1);
- -- insert into dept_Data values
- -- ('1005170501', '二期品控处'),
- -- ('1005170101', '二期财务处'),
- -- ('1005170102', '二期信息处'),
- -- ('1005170103', '二期办公室'),
- -- ('1005170502', '二期饲养处'),
- -- ('1005170301', '二期挤奶处'),
- -- ('1005170503', '二期技术处'),
- -- ('1005170504', '二期犊牛处'),
- -- ('1005170505', '二期饲喂处'),
- -- ('1005170506', '二期设备处'),
- -- ('1005170507', '二期沼液车间'),
- -- ('1005170401', '二期沼渣车间'),
- -- ('1005170508', '二期污水车间'),
- -- ('1005170402', '二期水处理车间')
- -- insert into cost_center_temp values
- -- ('1005050501', '品控处', 'F'),
- -- ('1005050101', '财务处', 'M'),
- -- ('1005050102', '信息处', 'M'),
- -- ('1005050103', '办公室', 'M'),
- -- ('1005050502', '饲养处', 'F'),
- -- ('1005050301', '挤奶处', 'P'),
- -- ('1005050503', '技术处', 'F'),
- -- ('1005050504', '犊牛处', 'F'),
- -- ('1005050505', '饲喂处', 'F'),
- -- ('1005050506', '设备处', 'F'),
- -- ('1005050507', '安环处', 'F'),
- -- ('1005050508', '沼液车间', 'F'),
- -- ('1005050401', '沼渣车间', 'A'),
- -- ('1005050509', '污水车间', 'F'),
- -- ('1005050402', '水处理车间', 'A'),
- -- ('1005050104', '餐厅', 'M'),
- -- ('1005170501', '二期品控处', 'F'),
- -- ('1005170101', '二期财务处', 'M'),
- -- ('1005170102', '二期信息处', 'M'),
- -- ('1005170103', '二期办公室', 'M'),
- -- ('1005170502', '二期饲养处', 'F'),
- -- ('1005170301', '二期挤奶处', 'P'),
- -- ('1005170503', '二期技术处', 'F'),
- -- ('1005170504', '二期犊牛处', 'F'),
- -- ('1005170505', '二期饲喂处', 'F'),
- -- ('1005170506', '二期设备处', 'F'),
- -- ('1005170507', '二期沼液车间', 'F'),
- -- ('1005170401', '二期沼渣车间', 'A'),
- -- ('1005170508', '二期污水车间', 'F'),
- -- ('1005170402', '二期水处理车间', 'A'),
- -- ('1005170509', '二期安环处', 'F')
- create temporary table save_temp (
- code varchar(100),
- providername varchar(100),
- brand varchar(100),
- price decimal(15,5)
- )
- num integer
- select pr.contractid from
- part_repertory pr
- left join warehouse w on w.id =pr.locationId
- left join pasture p on (p.id=pr.pastureId or p.id=w.pastureId )
- where p.name ='衡水二牧'
- and pr.partCode ='14.04.02.01.000335'
- select * from partlaid where contractid =598726 and locationid =8855
- select * from brand
- select * from pasture where name='衡水二牧'
- select code from save_temp where code not in (
- select pr.partcode
- from
- part_repertory pr
- join save_temp s on pr.partcode =s.code and pr.price =s.price and pr.brand =s.brand
- join provider pv on pv.id =pr.providerId and pv.providerName =s.providername
- where pr.pastureid =631 or (select pastureId from warehouse w where w.id =pr.locationid) =631
- )t
- update
- part_repertory pr
- join save_temp s on pr.partcode =s.code and pr.price =s.price and pr.brand =s.brand
- join provider pv on pv.id =pr.providerId and pv.providerName =s.providername
- set pr.reportery = s.num
- where pr.pastureid =631 or (select pastureId from warehouse w where w.id =pr.locationid) =631
|