修改语句
<p>1.修改测试语句
update s_shopid set shopname='测试'||shopname --店铺名前加测试 update s_company set companyname='测试'||companyname --组织名前加测试 update g_yongh set c_yonghmc='测试'||c_yonghmc --用户名前加测试 update g_yongh set c_password2='''SWRFFY''' --密码修改为123456 update g_dianpqx set c_quanxvalue='-1' where c_quanxmc='设置短信发送方式'
---取消短信发送方式
update g_dianpqx set c_quanxvalue='0' where c_quanxmc='是否启用posE发送短信功能'
--取消pose短信发送--</p>
<p>2.修改刷新价格段语句 ----刷新拉杆箱
UPDATE G_SPDM SET ITEM24 ='299及以下' WHERE C_XILBH='6' AND SALE <='299' AND ITEM24 <>'299及以下'; UPDATE G_SPDM SET ITEM24 ='309-399' WHERE C_XILBH='6' AND SALE <='399' AND SALE >='309' AND ITEM24 <>'309-399'; UPDATE G_SPDM SET ITEM24 ='409-499' WHERE C_XILBH='6' AND SALE <='499' AND SALE >='409' AND ITEM24 <>'409-499'; UPDATE G_SPDM SET ITEM24 ='509-599' WHERE C_XILBH='6' AND SALE <='599' AND SALE >='509' AND ITEM24 <>'509-599'; UPDATE G_SPDM SET ITEM24 ='609-699' WHERE C_XILBH='6' AND SALE <='699' AND SALE >='609' AND ITEM24 <>'609-699'; UPDATE G_SPDM SET ITEM24 ='709-799' WHERE C_XILBH='6' AND SALE <='799' AND SALE >='709' AND ITEM24 <>'709-799'; UPDATE G_SPDM SET ITEM24 ='809-899' WHERE C_XILBH='6' AND SALE <='899' AND SALE >='809' AND ITEM24 <>'809-899'; UPDATE G_SPDM SET ITEM24 ='909及以上' WHERE C_XILBH='6' AND SALE >='909' AND ITEM24 <>'909及以上'; ----刷新礼盒 UPDATE G_SPDM SET ITEM24 ='289及以下' WHERE C_XILBH='21' AND SALE <='299' AND ITEM24 <>'289及以下'; UPDATE G_SPDM SET ITEM24 ='299-499' WHERE C_XILBH='21' AND SALE <='499' AND SALE >='299' AND ITEM24 <>'299-499'; UPDATE G_SPDM SET ITEM24 ='509-709' WHERE C_XILBH='21' AND SALE <='709' AND SALE >='509' AND ITEM24 <>'509-709'; UPDATE G_SPDM SET ITEM24 ='719-919' WHERE C_XILBH='21' AND SALE <='919' AND SALE >='719' AND ITEM24 <>'719-919'; UPDATE G_SPDM SET ITEM24 ='919及以上' WHERE C_XILBH='21' AND SALE >='909' AND ITEM24 <>'919及以上'; ---刷新男包 UPDATE G_SPDM SET ITEM24 ='299及以下' WHERE C_XILBH='13' AND SALE <='299' AND ITEM24 <>'299及以下'; UPDATE G_SPDM SET ITEM24 ='309-399' WHERE C_XILBH='13' AND SALE <='399' AND SALE >='309' AND ITEM24 <>'309-399'; UPDATE G_SPDM SET ITEM24 ='409-499' WHERE C_XILBH='13' AND SALE <='499' AND SALE >='409' AND ITEM24 <>'409-499'; UPDATE G_SPDM SET ITEM24 ='509-599' WHERE C_XILBH='13' AND SALE <='599' AND SALE >='509' AND ITEM24 <>'509-599'; UPDATE G_SPDM SET ITEM24 ='609-699' WHERE C_XILBH='13' AND SALE <='699' AND SALE >='609' AND ITEM24 <>'609-699'; UPDATE G_SPDM SET ITEM24 ='709-799' WHERE C_XILBH='13' AND SALE <='799' AND SALE >='709' AND ITEM24 <>'709-799'; UPDATE G_SPDM SET ITEM24 ='809-899' WHERE C_XILBH='13' AND SALE <='899' AND SALE >='809' AND ITEM24 <>'809-899'; UPDATE G_SPDM SET ITEM24 ='909-999' WHERE C_XILBH='13' AND SALE <='999' AND SALE >='909' AND ITEM24 <>'909-999'; UPDATE G_SPDM SET ITEM24 ='1009及以上' WHERE C_XILBH='13' AND SALE >='1009' AND ITEM24 <>'1009及以上'; ---刷新男夹 UPDATE G_SPDM SET ITEM24 ='69及以下' WHERE C_XILBH='15' AND SALE <='69' AND ITEM24 <>'69及以下'; UPDATE G_SPDM SET ITEM24 ='79-129' WHERE C_XILBH='15' AND SALE <='129' AND SALE >='79' AND ITEM24 <>'79-129'; UPDATE G_SPDM SET ITEM24 ='139-189' WHERE C_XILBH='15' AND SALE <='189' AND SALE >='139' AND ITEM24 <>'139-189'; UPDATE G_SPDM SET ITEM24 ='199-249' WHERE C_XILBH='15' AND SALE <='249' AND SALE >='199' AND ITEM24 <>'199-249'; UPDATE G_SPDM SET ITEM24 ='259-299' WHERE C_XILBH='15' AND SALE <='299' AND SALE >='259' AND ITEM24 <>'259-299'; UPDATE G_SPDM SET ITEM24 ='309-359' WHERE C_XILBH='15' AND SALE <='359' AND SALE >='309' AND ITEM24 <>'309-359'; UPDATE G_SPDM SET ITEM24 ='369-419' WHERE C_XILBH='15' AND SALE <='419' AND SALE >='369' AND ITEM24 <>'369-419'; UPDATE G_SPDM SET ITEM24 ='429-479' WHERE C_XILBH='15' AND SALE <='479' AND SALE >='429' AND ITEM24 <>'429-479'; UPDATE G_SPDM SET ITEM24 ='489及以上' WHERE C_XILBH='15' AND SALE >='489' AND ITEM24 <>'489及以上'; --刷新女包 UPDATE G_SPDM SET ITEM24 ='109以下' WHERE C_XILBH='14' AND SALE <='109' AND ITEM24 <>'109以下'; UPDATE G_SPDM SET ITEM24 ='109-149' WHERE C_XILBH='14' AND SALE <='149' AND SALE >='109' AND ITEM24 <>'109-149'; UPDATE G_SPDM SET ITEM24 ='159-199' WHERE C_XILBH='14' AND SALE <='199' AND SALE >='159' AND ITEM24 <>'159-199'; UPDATE G_SPDM SET ITEM24 ='209-249' WHERE C_XILBH='14' AND SALE <='249' AND SALE >='209' AND ITEM24 <>'209-249'; UPDATE G_SPDM SET ITEM24 ='259-299' WHERE C_XILBH='14' AND SALE <='299' AND SALE >='259' AND ITEM24 <>'259-299'; UPDATE G_SPDM SET ITEM24 ='309-349' WHERE C_XILBH='14' AND SALE <='349' AND SALE >='309' AND ITEM24 <>'309-349'; UPDATE G_SPDM SET ITEM24 ='359-399' WHERE C_XILBH='14' AND SALE <='399' AND SALE >='359' AND ITEM24 <>'359-399'; UPDATE G_SPDM SET ITEM24 ='409-449' WHERE C_XILBH='14' AND SALE <='449' AND SALE >='409' AND ITEM24 <>'409-449'; UPDATE G_SPDM SET ITEM24 ='459-499' WHERE C_XILBH='14' AND SALE <='499' AND SALE >='459' AND ITEM24 <>'459-499'; UPDATE G_SPDM SET ITEM24 ='509-549' WHERE C_XILBH='14' AND SALE <='549' AND SALE >='509' AND ITEM24 <>'509-549'; UPDATE G_SPDM SET ITEM24 ='559-599' WHERE C_XILBH='14' AND SALE <='599' AND SALE >='559' AND ITEM24 <>'559-599'; UPDATE G_SPDM SET ITEM24 ='609-649' WHERE C_XILBH='14' AND SALE <='649' AND SALE >='609' AND ITEM24 <>'609-649'; UPDATE G_SPDM SET ITEM24 ='659-699' WHERE C_XILBH='14' AND SALE <='699' AND SALE >='659' AND ITEM24 <>'659-699'; UPDATE G_SPDM SET ITEM24 ='709-749' WHERE C_XILBH='14' AND SALE <='749' AND SALE >='709' AND ITEM24 <>'709-749'; UPDATE G_SPDM SET ITEM24 ='759-799' WHERE C_XILBH='14' AND SALE <='799' AND SALE >='759' AND ITEM24 <>'759-799'; UPDATE G_SPDM SET ITEM24 ='799及以上' WHERE C_XILBH='14' AND SALE >='799' AND ITEM24 <>'799及以上'; --刷新女手 UPDATE G_SPDM SET ITEM24 ='99及以下' WHERE C_XILBH='18' AND SALE <='99' AND ITEM24 <>'99及以下'; UPDATE G_SPDM SET ITEM24 ='109-159' WHERE C_XILBH='18' AND SALE <='159' AND SALE >='109' AND ITEM24 <>'109-159'; UPDATE G_SPDM SET ITEM24 ='169-219' WHERE C_XILBH='18' AND SALE <='219' AND SALE >='169' AND ITEM24 <>'169-219'; UPDATE G_SPDM SET ITEM24 ='229-279' WHERE C_XILBH='18' AND SALE <='279' AND SALE >='229' AND ITEM24 <>'229-279'; UPDATE G_SPDM SET ITEM24 ='289-339' WHERE C_XILBH='18' AND SALE <='339' AND SALE >='289' AND ITEM24 <>'289-339'; UPDATE G_SPDM SET ITEM24 ='349-399' WHERE C_XILBH='18' AND SALE <='399' AND SALE >='349' AND ITEM24 <>'349-399'; UPDATE G_SPDM SET ITEM24 ='409-459' WHERE C_XILBH='18' AND SALE <='459' AND SALE >='409' AND ITEM24 <>'409-459'; UPDATE G_SPDM SET ITEM24 ='469-519' WHERE C_XILBH='18' AND SALE <='519' AND SALE >='469' AND ITEM24 <>'469-519'; UPDATE G_SPDM SET ITEM24 ='529-579' WHERE C_XILBH='18' AND SALE <='579' AND SALE >='529' AND ITEM24 <>'529-579'; UPDATE G_SPDM SET ITEM24 ='589及以上' WHERE C_XILBH='18' AND SALE >='589' AND ITEM24 <>'589及以上'; --刷新小件 UPDATE G_SPDM SET ITEM24 ='69及以下' WHERE C_XILBH='4' AND SALE <='69' AND ITEM24 <>'69及以下'; UPDATE G_SPDM SET ITEM24 ='79-129' WHERE C_XILBH='4' AND SALE <='129' AND SALE >='79' AND ITEM24 <>'79-129'; UPDATE G_SPDM SET ITEM24 ='139-189' WHERE C_XILBH='4' AND SALE <='189' AND SALE >='139' AND ITEM24 <>'139-189'; UPDATE G_SPDM SET ITEM24 ='199-249' WHERE C_XILBH='4' AND SALE <='249' AND SALE >='199' AND ITEM24 <>'199-249'; UPDATE G_SPDM SET ITEM24 ='259-299' WHERE C_XILBH='4' AND SALE <='299' AND SALE >='259' AND ITEM24 <>'259-299'; UPDATE G_SPDM SET ITEM24 ='309-359' WHERE C_XILBH='4' AND SALE <='359' AND SALE >='309' AND ITEM24 <>'309-359'; UPDATE G_SPDM SET ITEM24 ='369-419' WHERE C_XILBH='4' AND SALE <='419' AND SALE >='369' AND ITEM24 <>'369-419'; UPDATE G_SPDM SET ITEM24 ='429-479' WHERE C_XILBH='4' AND SALE <='479' AND SALE >='429' AND ITEM24 <>'429-479'; UPDATE G_SPDM SET ITEM24 ='489及以上' WHERE C_XILBH='4' AND SALE >='489' AND ITEM24 <>'489及以上'; ---刷新男皮带 UPDATE G_SPDM SET ITEM24 ='49及以下' WHERE C_XILBH='19' AND SALE <='49' AND ITEM24 <>'49及以下'; UPDATE G_SPDM SET ITEM24 ='49-99' WHERE C_XILBH='19' AND SALE <='409' AND SALE >='49' AND ITEM24 <>'49-99'; UPDATE G_SPDM SET ITEM24 ='109-159' WHERE C_XILBH='19' AND SALE <='159' AND SALE >='109' AND ITEM24 <>'109-159'; UPDATE G_SPDM SET ITEM24 ='169-219' WHERE C_XILBH='19' AND SALE <='219' AND SALE >='169' AND ITEM24 <>'169-219'; UPDATE G_SPDM SET ITEM24 ='229-279' WHERE C_XILBH='19' AND SALE <='279' AND SALE >='229' AND ITEM24 <>'229-279'; UPDATE G_SPDM SET ITEM24 ='289-339' WHERE C_XILBH='19' AND SALE <='339' AND SALE >='289' AND ITEM24 <>'289-339'; UPDATE G_SPDM SET ITEM24 ='349-399' WHERE C_XILBH='19' AND SALE <='399' AND SALE >='349' AND ITEM24 <>'349-399'; UPDATE G_SPDM SET ITEM24 ='409-459' WHERE C_XILBH='19' AND SALE <='459' AND SALE >='409' AND ITEM24 <>'409-459'; UPDATE G_SPDM SET ITEM24 ='469及以上' WHERE C_XILBH='19' AND SALE >='469' AND ITEM24 <>'469及以上'; ---刷新女皮带 UPDATE G_SPDM SET ITEM24 ='49及以下' WHERE C_XILBH='20' AND SALE <='49' AND ITEM24 <>'49及以下'; UPDATE G_SPDM SET ITEM24 ='49-99' WHERE C_XILBH='20' AND SALE <='409' AND SALE >='49' AND ITEM24 <>'49-99'; UPDATE G_SPDM SET ITEM24 ='109-159' WHERE C_XILBH='20' AND SALE <='159' AND SALE >='109' AND ITEM24 <>'109-159'; UPDATE G_SPDM SET ITEM24 ='169-219' WHERE C_XILBH='20' AND SALE <='219' AND SALE >='169' AND ITEM24 <>'169-219'; UPDATE G_SPDM SET ITEM24 ='229-279' WHERE C_XILBH='20' AND SALE <='279' AND SALE >='229' AND ITEM24 <>'229-279'; UPDATE G_SPDM SET ITEM24 ='289-339' WHERE C_XILBH='20' AND SALE <='339' AND SALE >='289' AND ITEM24 <>'289-339'; UPDATE G_SPDM SET ITEM24 ='349-399' WHERE C_XILBH='20' AND SALE <='399' AND SALE >='349' AND ITEM24 <>'349-399'; UPDATE G_SPDM SET ITEM24 ='409-459' WHERE C_XILBH='20' AND SALE <='459' AND SALE >='409' AND ITEM24 <>'409-459'; UPDATE G_SPDM SET ITEM24 ='469及以上' WHERE C_XILBH='20' AND SALE >='469' AND ITEM24 <>'469及以上'; ---刷新销售辅料 UPDATE G_SPDM SET ITEM24 ='69及以下' WHERE C_XILBH='12' AND SALE <='69' AND ITEM24 <>'69及以下'; UPDATE G_SPDM SET ITEM24 ='79-129' WHERE C_XILBH='12' AND SALE <='129' AND SALE >='79' AND ITEM24 <>'79-129'; UPDATE G_SPDM SET ITEM24 ='139-189' WHERE C_XILBH='12' AND SALE <='189' AND SALE >='139' AND ITEM24 <>'139-189'; UPDATE G_SPDM SET ITEM24 ='199-249' WHERE C_XILBH='12' AND SALE <='249' AND SALE >='199' AND ITEM24 <>'199-249'; UPDATE G_SPDM SET ITEM24 ='259-299' WHERE C_XILBH='12' AND SALE <='299' AND SALE >='259' AND ITEM24 <>'259-299'; UPDATE G_SPDM SET ITEM24 ='309-359' WHERE C_XILBH='12' AND SALE <='359' AND SALE >='309' AND ITEM24 <>'309-359'; UPDATE G_SPDM SET ITEM24 ='369-419' WHERE C_XILBH='12' AND SALE <='419' AND SALE >='369' AND ITEM24 <>'369-419'; UPDATE G_SPDM SET ITEM24 ='429-479' WHERE C_XILBH='12' AND SALE <='479' AND SALE >='429' AND ITEM24 <>'429-479'; UPDATE G_SPDM SET ITEM24 ='489及以上' WHERE C_XILBH='12' AND SALE >='489' AND ITEM24 <>'489及以上'; --刷新手套 UPDATE G_SPDM SET ITEM24 ='69及以下' WHERE C_XILBH='11' AND SALE <='69' AND ITEM24 <>'69及以下'; UPDATE G_SPDM SET ITEM24 ='79-129' WHERE C_XILBH='11' AND SALE <='129' AND SALE >='79' AND ITEM24 <>'79-129'; UPDATE G_SPDM SET ITEM24 ='139-189' WHERE C_XILBH='11' AND SALE <='189' AND SALE >='139' AND ITEM24 <>'139-189'; UPDATE G_SPDM SET ITEM24 ='199-249' WHERE C_XILBH='11' AND SALE <='249' AND SALE >='199' AND ITEM24 <>'199-249'; UPDATE G_SPDM SET ITEM24 ='259-299' WHERE C_XILBH='11' AND SALE <='299' AND SALE >='259' AND ITEM24 <>'259-299'; UPDATE G_SPDM SET ITEM24 ='309-359' WHERE C_XILBH='11' AND SALE <='359' AND SALE >='309' AND ITEM24 <>'309-359'; UPDATE G_SPDM SET ITEM24 ='369-419' WHERE C_XILBH='11' AND SALE <='419' AND SALE >='369' AND ITEM24 <>'369-419'; UPDATE G_SPDM SET ITEM24 ='429-479' WHERE C_XILBH='11' AND SALE <='479' AND SALE >='429' AND ITEM24 <>'429-479'; UPDATE G_SPDM SET ITEM24 ='489及以上' WHERE C_XILBH='11' AND SALE >='489' AND ITEM24 <>'489及以上'; ---刷新男手 UPDATE G_SPDM SET ITEM24 ='99及以下' WHERE C_XILBH='17' AND SALE <='99' AND ITEM24 <>'99及以下'; UPDATE G_SPDM SET ITEM24 ='109-159' WHERE C_XILBH='17' AND SALE <='159' AND SALE >='109' AND ITEM24 <>'109-159'; UPDATE G_SPDM SET ITEM24 ='169-219' WHERE C_XILBH='17' AND SALE <='219' AND SALE >='169' AND ITEM24 <>'169-219'; UPDATE G_SPDM SET ITEM24 ='229-279' WHERE C_XILBH='17' AND SALE <='279' AND SALE >='229' AND ITEM24 <>'229-279'; UPDATE G_SPDM SET ITEM24 ='289-339' WHERE C_XILBH='17' AND SALE <='339' AND SALE >='289' AND ITEM24 <>'289-339'; UPDATE G_SPDM SET ITEM24 ='349-399' WHERE C_XILBH='17' AND SALE <='399' AND SALE >='349' AND ITEM24 <>'349-399'; UPDATE G_SPDM SET ITEM24 ='409-459' WHERE C_XILBH='17' AND SALE <='459' AND SALE >='409' AND ITEM24 <>'409-459'; UPDATE G_SPDM SET ITEM24 ='469-519' WHERE C_XILBH='17' AND SALE <='519' AND SALE >='469' AND ITEM24 <>'469-519'; UPDATE G_SPDM SET ITEM24 ='529-579' WHERE C_XILBH='17' AND SALE <='579' AND SALE >='529' AND ITEM24 <>'529-579'; UPDATE G_SPDM SET ITEM24 ='589及以上' WHERE C_XILBH='17' AND SALE >='589' AND ITEM24 <>'589及以上'; ---刷新女夹 UPDATE G_SPDM SET ITEM24 ='69及以下' WHERE C_XILBH='16' AND SALE <='69' AND ITEM24 <>'69及以下'; UPDATE G_SPDM SET ITEM24 ='79-129' WHERE C_XILBH='16' AND SALE <='129' AND SALE >='79' AND ITEM24 <>'79-129'; UPDATE G_SPDM SET ITEM24 ='139-189' WHERE C_XILBH='16' AND SALE <='189' AND SALE >='139' AND ITEM24 <>'139-189'; UPDATE G_SPDM SET ITEM24 ='199-249' WHERE C_XILBH='16' AND SALE <='249' AND SALE >='199' AND ITEM24 <>'199-249'; UPDATE G_SPDM SET ITEM24 ='259-299' WHERE C_XILBH='16' AND SALE <='299' AND SALE >='259' AND ITEM24 <>'259-299'; UPDATE G_SPDM SET ITEM24 ='309-359' WHERE C_XILBH='16' AND SALE <='359' AND SALE >='309' AND ITEM24 <>'309-359'; UPDATE G_SPDM SET ITEM24 ='369-419' WHERE C_XILBH='16' AND SALE <='419' AND SALE >='369' AND ITEM24 <>'369-419'; UPDATE G_SPDM SET ITEM24 ='429-479' WHERE C_XILBH='16' AND SALE <='479' AND SALE >='429' AND ITEM24 <>'429-479'; UPDATE G_SPDM SET ITEM24 ='489及以上' WHERE C_XILBH='16' AND SALE >='489' AND ITEM24 <>'489及以上';</p>
<p>3.重新计算可用数量
insert into g_endstoreph(shopid,code,colorid,xsize,c_buh,c_haox,c_pinpbh,amount) select u.shopid,u.code,u.colorid,u.xsize,u.c_buh,u.c_haox,u.c_pinpbh,u.amount from ( select m.ftcompanyid as shopid,d.code,d.colorid,trim(d.xsize) as xsize,d.c_buh,d.c_haox, coalesce(d.c_danwmc,'') as c_pinpbh,sum(d.amount) as amount from g_inouttblbill m, g_iodetailbill d where m.scripno=d.scripno and m.ioflag=5 and m.iotype=2 and m.companyid='44001' group by m.ftcompanyid,d.code,d.colorid,trim(d.xsize),d.c_buh,d.c_haox,coalesce(d.c_danwmc,'') ) u left join g_endstoreph g on u.shopid=g.shopid and u.code=g.code and u.colorid=g.colorid and u.xsize=g.xsize and u.c_buh=g.c_buh and u.c_haox=g.c_haox and u.c_pinpbh=g.c_pinpbh where g.shopid is null and g.code is null and g.colorid is null and g.xsize is null;
--插入配货数据(仓库方)
insert into g_endstoreph(shopid,code,colorid,xsize,c_buh,c_haox,c_pinpbh,amount)
select u.shopid,u.code,u.colorid,u.xsize,u.c_buh,u.c_haox,'',u.amount
from (
select m.gfrom as shopid,d.code,d.colorid,trim(d.xsize) as xsize,d.c_buh,d.c_haox,sum(d.amount) as amount
from g_inouttblbill m, g_iodetailbill d
where m.scripno=d.scripno and m.ioflag=5 and m.iotype=2 and m.companyid='44001'
group by m.gfrom,d.code,d.colorid,trim(d.xsize),d.c_buh,d.c_haox
) u
left join g_endstoreph g on u.shopid=g.shopid and u.code=g.code and u.colorid=g.colorid
and u.xsize=g.xsize and u.c_buh=g.c_buh and u.c_haox=g.c_haox
where g.shopid is null and g.code is null and g.colorid is null and g.xsize is null;
1.扣除库存 begin ; update g_endstore set amount= g_endstore.amount-d.amount from (select b.gfrom,a.code,a.colorid, sum (a.amount) as amount ,'0' as xsize from g_iodetail_zmd a ,g_inouttbl_zmd b where a.scripno =b.scripno and a.xsize ='-' GROUP BY b.gfrom,a.code,a.colorid ) d
where g_endstore.shopid=d.gfrom and g_endstore.code=d.code and g_endstore.colorid=d.colorid and g_endstore.xsize='0'; update g_iodetail_zmd set xsize='0' ,u_id ='nmy151126' where xsize='-' ; commit;
begin ; update g_endstore set amount= g_endstore.amount-d.amount from (select b.gfrom,a.code,a.colorid, sum (a.amount) as amount ,'0' as xsize from g_iodetail a ,g_inouttbl b where a.scripno =b.scripno and a.xsize ='-' and b.ioflag=2 and b.iotype= 7 GROUP BY b.gfrom,a.code,a.colorid ) d
where g_endstore.shopid=d.gfrom and g_endstore.code=d.code and g_endstore.colorid=d.colorid and g_endstore.xsize='0'; update g_iodetail set xsize='0' ,u_id ='nmy151126' where xsize='-' AND scripno IN (SELECT scripno FROM g_inouttbl WHERE ioflag=2 and iotype= 7 ); commit;
begin ; update g_endstore set amount= g_endstore.amount+d.amount from (select b.gto,a.code,a.colorid, sum (a.amount) as amount ,'0' as xsize from g_iodetail a ,g_inouttbl b where a.scripno =b.scripno and a.xsize ='-' and b.ioflag=1 and b.iotype= 7 GROUP BY b.gto,a.code,a.colorid ) d
where g_endstore.shopid=d.gto and g_endstore.code=d.code and g_endstore.colorid=d.colorid and g_endstore.xsize='0'; update g_iodetail set xsize='0' ,u_id ='nmy151126' where xsize='-' AND scripno IN (SELECT scripno FROM g_inouttbl WHERE ioflag=1 and iotype= 7 ); commit;
调拨 刷新
begin ; update g_endstore set amount= g_endstore.amount-d.amount from (select b.gfrom,a.code,a.colorid, sum (a.amount) as amount ,'0' as xsize from g_iodetail a ,g_inouttbl b where a.scripno =b.scripno and a.xsize ='-' and b.ioflag=2 and b.iotype= 7 GROUP BY b.gfrom,a.code,a.colorid ) d
where g_endstore.shopid=d.gfrom and g_endstore.code=d.code and g_endstore.colorid=d.colorid and g_endstore.xsize='0'; update g_iodetail set xsize='0' ,u_id ='nmy151126' where xsize='-' AND scripno IN (SELECT scripno FROM g_inouttbl WHERE ioflag=2 and iotype= 7 ); commit;
begin ; update g_endstore set amount= g_endstore.amount+d.amount from (select b.gto,a.code,a.colorid, sum (a.amount) as amount ,'0' as xsize from g_iodetail a ,g_inouttbl b where a.scripno =b.scripno and a.xsize ='-' and b.ioflag=1 and b.iotype= 7 GROUP BY b.gto,a.code,a.colorid ) d
where g_endstore.shopid=d.gto and g_endstore.code=d.code and g_endstore.colorid=d.colorid and g_endstore.xsize='0'; update g_iodetail set xsize='0' ,u_id ='nmy151129' where xsize='-' AND scripno IN (SELECT scripno FROM g_inouttbl WHERE ioflag=1 and iotype= 7 ); commit;</p>
<p>4.复盘数合计
update g_inouttblbill set totalcount_n = a.c_sangcbh from
(select scripno, sum (to_number(c_sangcbh,'999999999')) as c_sangcbh from g_iodetailbill where scripno in ( SELECT scripno FROM g_inouttblbill WHERE ioflag=9 AND iotype=6 ) GROUP BY scripno ) a
where g_inouttblbill.scripno=a .scripno and g_inouttblbill.ioflag=9 AND g_inouttblbill.iotype=6</p>
<p>5.批量启用储值卡功能
update s_shopid set c_hethm='1234',c_jiesfsbh='5678' where shopid in (select shopid from s_shopid where companyid in ('22882','22883','22884','22885','22886','22887','22888','','','') )
6.pose 评价修改空 update g_iodetail_zmd set boxamount=NULL ,c_beiz=NULL where scripno='AX3101140012015102000004'</p>