赠品毛利为负
直接处理但是7月已有结转
并且直接改毛利会出现红帐页(主要是因为改splsk结存金额)
需要先更新明细再对明细进行合计（防止同一单据开出其它品种）
update spls_ck set chkje=0,ml=0,chbdj=0,jcje=85.47,hwjcer=85.47 where spid='SPH00005569' and rq>='2015-08-01'
update pf_ckmx set chbje=0,ml=0,chbdj=0 where spid='SPH00005569' and rq>='2015-08-01'
update b set b.chbje=a.chbje,b.ml=a.ml from
(select djbh,sum(chbje) chbje,sum(ml) ml from pf_ckmx where spid='SPH00005569' and rq>='2015-08-01' group by djbh) a,pf_ckhz b where a.djbh=b.djbh
update b set b.chbje=a.chbje,b.ml=a.ml from pf_ckhz a,cwk b where a.djbh=b.djbh and a.djbh in (select djbh from pf_ckmx where spid='SPH00005569')
发现红帐页
update spls_rk set jcje=0,hwjcer=0,chbdj=0 where djbh='JHBYMA00000044' and spid='SPH00005569'
后选择退货再销售
退货
先整理出原来的赠品销售数据和原来退货并对应的销售票数据
select a.djbh,e.spbh,e.spmch,e.shpgg,e.dw,e.shengccj,b.dwmch,a.pihao,a.shl,a.hsje,a.chbdj,a.rq,d.ontime,d.bm,d.ywy,c.username,a.ml,'销' as beizhu from pf_ckmx a,mchk b,pf_djhz c,pf_ckhz d,spkfk e where a.spid=e.spid and d.dwbh=b.dwbh and d.xgdjbh=c.djbh and a.djbh=d.djbh and a.spid in (select spid from spkfk where left(spbh,2)='ZP') and a.djbh like 'xsa%' --and a.chbdj<>0
union all
select a.duiydjbh,e.spbh,e.spmch,e.shpgg,e.dw,e.shengccj,b.dwmch,a.pihao,a.shl,a.hsje,a.chbdj,a.rq,d.bm,'','',a.ml,'退' as beizhu from pf_ckmx a,mchk b,pf_ckhz d,spkfk e where a.spid=e.spid and d.dwbh=b.dwbh and a.duiydjbh=d.djbh and a.spid in (select spid from spkfk where left(spbh,2)='ZP') and a.djbh like 'xsc%'
分开终端和分销集中退货退完货后对赠品所有数据进行核对
检查赠品成本金额是否都为0
select b.spbh,a.* from spkfjc a,spkfk b where a.spid=b.spid and a.spid in (select spid from spkfk where left(spbh,2)='ZP') and a.kcje<>0
select a.spid,sum(hwje) from hwsp a,spkfk b where a.spid=b.spid and a.spid in (select spid from spkfk where left(spbh,2)='ZP') and a.hwje<>0 group by a.spid
发现有一品种的成本金额为0.01，直接更新为0，对不为0的3个品种检查，发现一个为没有进货退回，一个品种无补差价，一个品种没有退完。
检查赠品的sphwph表中的hshj与pckhdj是否为0
select * from spkfk where spid in
(select distinct spid from sphwph where spid in (select spid from spkfk where left(spbh,2)='ZP') and (hshj<>0 or pckhdj<>0) )
其中发现有非赠品货位不为0统一更新为0并对赠品限制不能入到非赠品货位
update sphwph set hshj=0,pckhdj=0 where spid in (select spid from spkfk where left(spbh,2)='ZP') and (hshj<>0 or pckhdj<>0) and hw not in ('HWI00000012','HWI00000013')
检查无误后进行更新成本单价和个别计价
update spkfjc set chbdj=0,kcje=0 where spid in (select spid from spkfk where left(spbh,2)='ZP') and spid<>'SPH00005588'
update hwsp set chbdj=0,hwje=0 where spid in (select spid from spkfk where left(spbh,2)='ZP') and spid<>'SPH00005588'
update sphwph set gebjj=0 where spid in (select spid from spkfk where left(spbh,2)='ZP') and spid<>'SPH00005588'
检查是否更新完
select * from spkfjc where spid in (select spid from spkfk where left(spbh,2)='ZP') and (chbdj<>0 or kcje<>0)
select * from hwsp where spid in (select spid from spkfk where left(spbh,2)='ZP') and (chbdj<>0 or hwje<>0)
select * from sphwph where spid in (select spid from spkfk where left(spbh,2)='ZP') and gebjj<>0
销售
分别对终端(总数量835)和分销(16978)销售开票数进行监控
发现有人修改时间无法监控
select sum(shl) from pf_djmx where djbh in (select a.djbh from pf_djmx a,pf_djhz b where a.djbh=b.djbh and a.spid in (select spid from spkfk where left(spbh,2)='ZP')and a.djbh like 'xsg%' and bm='终端部' and b.rq='2015-08-29' and ontime> '18:00:00')
select sum(shl) from pf_djmx where djbh in (select a.djbh from pf_djmx a,pf_djhz b where a.djbh=b.djbh and a.spid in (select spid from spkfk where left(spbh,2)='ZP')and a.djbh like 'xsg%' and bm='分销部' and b.rq='2015-08-29' and ontime> '18:00:00')
审完票核对发现当天还有开其他赠品只能对时间也进行限定
select sum(shl) from pf_ckmx where djbh in (select a.djbh from pf_ckmx a,pf_ckhz b where a.djbh=b.djbh and a.spid in (select spid from spkfk where left(spbh,2)='ZP')and a.djbh like 'xsa%' and bm='终端部' and b.rq='2015-08-29' and ontime> '18:00:00'
)
select sum(shl) from pf_ckmx where djbh in (select a.djbh from pf_ckmx a,pf_ckhz b where a.djbh=b.djbh and a.spid in (select spid from spkfk where left(spbh,2)='ZP')and a.djbh like 'xsa%' and bm='分销部' and b.rq='2015-08-29' and ontime> '18:00:00'
)
发现终端有多开下的
后来对具体品种单一核对，发现有数量和批号开错现象。并对其进行重新修正。
如赠品需要调拨还需将赠品成本单价调为0.01
并对赠品开票进行限制不能开的高于0.01而且hshj和pckhdj只能为0
本文转自 qvodnet 51CTO博客，原文链接:http://blog.51cto.com/bks2015/1690240
