问题现象: | 期初红字回冲单比正常的多了一倍,造成结算后仍有单据在生成红蓝回冲单,业务数据已处理到8月,不能回退,数据已上传20110920.rar |
问题原因: | |
解决方案: | 您好, 此问题【经查看数据/测试】现做如下分析与答复: 【问题原因】 由于期初采购入库单的记录重复,导致红字回冲单记录重复,期初采购入库单重复可能是因为结转后,后台又做过调整导致; 【解决方案】 1、由于红字回冲单已经生成凭证,所以需要在存货核算中,将1月份的195号凭证冲销,然后针对2011年数据库执行下面语句,将重复的期初采购入库单、红字回冲单记录删除: delete from IA_Subsidiary where AutoID in (select MAX(autoid) from IA_Subsidiary where cVouType='33' group by id having COUNT(*)>1) delete from IA_Subsidiary where AutoID in (select MAX(autoid) from IA_Subsidiary where cVouType='24' and imonth=1 group by id having COUNT(*)>1) 2、由于1月份之后生成的蓝字回冲单数量金额重复,所以需要将所有月份的红蓝回冲单所生成的凭证都冲销或删除后,并且8月期末处理后月末结账前(如果业务已经处理到9月份,也可以在9月份期末处理后月末结账前),再针对2011年数据库执行下面语句更新红蓝回冲单的数量,例如:0008051号入库单的KR03-04-122存货,期初采购数量为5,1月份蓝字回冲单数量为10,以后月份的红蓝回冲单绝对值也为10: declare @imonth as int set @imonth=1 while @imonth<=(select MAX(iperiod)+1 from GL_mend where bflag_IA=1) begin update a set iainquantity=convert(decimal(20,4),-iq),iainprice=CONVERT(decimal(20,2),-iq*iincost),iprocessfee=CONVERT(decimal(20,2),-iq*iincost) from IA_Subsidiary a inner join (select SUM(iainquantity) as iq,id,iMonth from IA_Subsidiary where cVouType='24' or (cvoutype='30' and bflag=2) group by id,iMonth) b on a.id=b.ID and a.iMonth=b.iMonth where a.cVouType='30' and bFlag=1 and a.iMonth=@imonth update a set iainquantity=-b.iainquantity,iainprice=-b.iainprice,iprocessfee=-b.iprocessfee from IA_Subsidiary a inner join (select * from IA_Subsidiary where cVouType='30' and bFlag=1 and iMonth=@imonth) b on a.id=b.ID where a.cVouType='24' and a.iMonth=b.iMonth+1 set @imonth=@imonth+1 end go delete from IA_Subsidiary where cvoutype in ('24','30') and bFlag=1 and ABS(iainquantity)=0 处理完毕后,再对红蓝回冲单重新生成凭证即可。 【注意事项】修改前请务必做好数据备份;由于网站的关系,上述语句中的标点符号' % + 必需在输入法为英文的状态下重新录入后再执行; |
相关补丁: | |
版本: | 8.90 |
模块: | 存货管理 |
产品: | 供应链 |
问题名称: | 期初红字回冲单双倍 |
最后更新: | 2011-10-07 23:24:31 |