委外核销导致明细账与结存对应错误案例问题描述1.委外加工收料单成本计算时提示“明细表中存在没有单价的发料行”，还有的提示“WrongArguments”。2.执行海南橡胶的SQL语句查询出问题数据160多行，即委外明细账和结存表数据对应不上，希望老师给查一下是什么问题造成的。问题分析1.经过对委外加工收料单成本计算的后台跟踪，发现这些单子的来源表头ID和来源表体ID，不是对应的同一张单子(委外加工入库单)。初步判定有可能是生成委外加工收料单时(即委外核销)，排序有误导致表头与表体ID对应不上。2.委外明细账与结存表数据对应不上，这应该是由第一点原因造成的，委外明细账是实际发生的每笔业务，结存则是一个最终结果，每次核销/反核销都会改写该结果，而排序乱，会导致核销回写也是错误的。这只能已明细账为准，来调整结存数据。测试结果问题界面排序前问题界面排序后注意看，排序前和排序后，都是同一张入库单，但是表体已经变了，说明表体已经不是原来的那种单子了。解决方案1.提供补丁，解决材料核销界面排序错乱。502版本参考支持网问题ID：201004191403347902的补丁。2.对已有的结存数据进行调整。调整脚本：--1创建临时表createtabletmp(cbalanceidchar(20),nnumDECIMAL(20),nmnyDECIMAL(20));--2把错误数据的id及对应的正确数量金额插入临时表中insertintotmp(cbalanceid,nnum,nmny)selectcc.cbalanceid,nvl(dd.nnum,0)+nvl(aa.nnum,0)-nvl(bb.nnum,0),nvl(dd.nmny,0)+nvl(aa.nmny,0)-nvl(bb.nmny,0)from(selecta.pk_corp,cvendorid,cprocessbaseid,cbalanceid,cmaterialbaseid,sum(nvl(nbalancenum,0))asnnum,sum(nvl(nbalancemny,0))asnmnyfromsc_balanceawherea.dr=0groupbya.pk_corp,cvendorid,cprocessbaseid,cmaterialbaseid,cbalanceid)ccleftouterjoin(selecta.pk_corp,a.cvendorid,a.cprocessbaseid,a.cmaterialbaseid,sum(nvl(a.nnum,0))asnnum,sum(nvl(a.nmny,0))asnmnyfromsc_materialledgerawherea.dr=0and(ltrim(rtrim(a.isourcetype))='0')groupbya.pk_corp,a.cvendorid,a.cprocessbaseid,a.cmaterialbaseid)aaon(aa.pk_corp=cc.pk_corpandaa.cvendorid=cc.cvendoridandaa.cprocessbaseid=cc.cprocessbaseidandaa.cmaterialbaseid=cc.cmaterialbaseid)leftouterjoin(selecta.pk_corp,a.cvendorid,a.cprocessbaseid,a.cmaterialbaseid,sum(nvl(a.nnum,0))asnnum,sum(nvl(a.nmny,0))asnmnyfromsc_materialledgerawherea.dr=0and(ltrim(rtrim(a.isourcetype))='1'orltrim(rtrim(a.isourcetype))='2')groupbya.pk_corp,a.cvendorid,a.cprocessbaseid,a.cmaterialbaseid)bbon(bb.pk_corp=cc.pk_corpandbb.cvendorid=cc.cvendoridandbb.cprocessbaseid=cc.cprocessbaseidandbb.cmaterialbaseid=cc.cmaterialbaseid)leftouterjoin(selecta.pk_corp,a.cvendorid,a.cprocessbaseid,a.cmaterialbaseid,sum(nvl(a.nnum,0))asnnum,sum(nvl(a.nmny,0))asnmnyfromsc_materialledgerawherea.dr=0and(ltrim(rtrim(a.isourcetype))='3')groupbya.pk_corp,a.cvendorid,a.cprocessbaseid,a.cmaterialbaseid)ddon(dd.pk_corp=cc.pk_corpanddd.cvendorid=cc.cvendoridanddd.cprocessbaseid=cc.cprocessbaseidanddd.cmaterialbaseid=cc.cmaterialbaseid)leftjoinbd_invbasdocbd_invbasdoc1oncc.cprocessbaseid=bd_invbasdoc1.pk_invbasdocleftjoinbd_invbasdocbd_invbasdoc2oncc.cmaterialbaseid=bd_invbasdoc2.pk_invbasdocleftjoinbd_measdoconbd_invbasdoc2.pk_measdoc=bd_measdoc.pk_measdocleftjoinbd_cubasdoconcc.cvendorid=bd_cubasdoc.pk_cubasdocwherenvl(dd.nnum,0)+nvl(aa.nnum,0)-nvl(bb.nnum,0)<>nvl(cc.nnum,0)ornvl(dd.nmny,0)+nvl(aa.nmny,0)-nvl(bb.nmny,0)<>nvl(cc.nmny,0)--3更新正确的数据updatesc_balanceset(nbalancenum,nbalancemny)=(selectnnum,nmnyfromtmpwheresc_balance.cbalanceid=tmp.cbalanceid)whereexists(select1fromtmpwheresc_balance.cbalanceid=tmp.cbalanceid)--4删除临时表droptabletmp;
