1/9用友股份-LE支持服务业务本部产品方案--《委外核销导致明细账与结存对应错误案例》建立日期：2013-12-03修改日期：xxxx-xx-xx文档属性：客户文控编号：LE-DY-CP-2013-09462/9文档控制创建记录适用范围审阅人日期审阅签字所属部门发布范围内部员工伙伴客户发布人姓名所属部门发布时间日期作者所属部门邮件地址版本2013-12-03用友NC产品支持部V1。0版本领域模块备注3/9目录委外核销导致明细账与结存对应错误案例............................................................4一、问题描述............................................................................................................4二、问题分析............................................................................................................4三、测试结果............................................................................................................5四、解决方案............................................................................................................64/9委外核销导致明细账与结存对应错误案例一、问题描述1.委外加工收料单成本计算时提示“明细表中存在没有单价的发料行”，还有的提示“WrongArguments”。2.执行海南橡胶的SQL语句查询出问题数据160多行，即委外明细账和结存表数据对应不上，希望老师给查一下是什么问题造成的。二、问题分析1.经过对委外加工收料单成本计算的后台跟踪，发现这些单子的来源表头ID和来源表体ID，不是对应的同一张单子(委外加工入库单)。初步判定有可能是生成委外加工收料单时(即委外核销)，排序有误导致表头与表体ID对应不上。2.委外明细账与结存表数据对应不上，这应该是由第一点原因造成的，委外明细账是实际发生的每笔业务，结存则是一个最终结果，每次核销/反核销都会改写该结果，而排序乱，会导致核销回写也是错误的。这只能已明细账为准，来调整结存数据。5/9三、测试结果问题界面排序前6/9问题界面排序后注意看，排序前和排序后，都是同一张入库单，但是表体已经变了，说明表体已经不是原来的那种单子了。四、解决方案1.提供补丁，解决材料核销界面排序错乱。502版本参考支持网问题ID：201004191403347902的补丁。2.对已有的结存数据进行调整。（注：请非专业人士慎重使用，使用前一定做好备份）调整脚本：--1创建临时表createtabletmp(cbalanceidchar(20),nnumDECIMAL(20),nmnyDECIMAL(20));--2把错误数据的id及对应的正确数量金额插入临时表中insertintotmp7/9(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_materialledgera8/9wherea.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_cubasdoc9/9wherenvl(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;
