|
|
问题现象：在用友8.61erp系统中用友U8版本号：U8.61软件的财务系统产品线应付模块出现如下问题：存货核算里，2005年年末存货明细账存货总帐结存数量单价，有一部分与2006年初存货明细账存货总帐结存数量单价不一致，但是金额是一致的。有差异的存货在文件EXCEL‘存货年初年末差异明细表’已列出。客户想在不影响以前会计月财务报表的基础上把差异数量金额调整掉，以保证以后会计月的成本正确。请帮忙解答！
原因分析：请先做好数据备份！问题原因是年度结转后ia_subsidiary表及ia_summary表中期初记录有错误。执行完附件中的语句后需执行重算存货总帐工具,先点‘修复存货总帐’，再点‘重算存货总帐’即可使上下年的存货期初完全一致。useufdata_001_2006selectcwhcode,cinvcode,cinvheadintokmfromia_subsidiarywhereimonth=0andcvoutype='34'groupbycwhcode,cinvcode,cinvheadhavingcinvheadisnotnulldeleteia_subsidiaryfromia_subsidiarywhereautoidnotin(selectmax(autoid)fromia_subsidiarywhereimonth=0andcvoutype='34'groupbycwhcode,cinvcode)andimonth=0andcvoutype='34'updateia_subsidiarysetiainquantity=(casewhena.inum=0thennullwhena.inumisnullthennullelsea.inumend),iincost=(casewhena.inum=0thennullwhena.inumisnullthennullelsea.imoney/a.inumend),iainprice=a.imoneyfromia_subsidiaryfullouterjoin(selectcwhcode,cinvcode,inum,imoneyfromUFDATA_001_2005..ia_summarywhereimonth=12)aonia_subsidiary.cwhcode=a.cwhcodeandia_subsidiary.cinvcode=a.cinvcodewhere(isnull(ia_subsidiary.iainquantity,0)isnull(a.inum,0)orisnull(ia_subsidiary.iainprice,0)isnull(a.imoney,0))andia_subsidiary.autoidisnotnullandia_subsidiary.imonth=0andcvoutype='34'deletefromia_subsidiarywherecvoutype='34'andimonth=0and(isnull(iainquantity,0)=0andisnull(iainprice,0)=0)updateia_subsidiarysetcinvhead=km.cinvheadFromkmfullouterjoinia_subsidiaryonia_subsidiary.cwhcode=km.cwhcodeandia_subsidiary.cinvcode=km.cinvcodewherekm.cwhcodeisnotnullandcvoutype='34'andimonth=0andisnull(ia_subsidiary.cinvhead,0)km.cinvheadSETNOCOUNTONdeclare@cVouCodeasvarchar(30),@cwhcodeasvarchar(10),@cinvcodeasvarchar(20),@iainquantityasfloat,@iincostasfloat,@iainpriceasmoneyset@cVouCode=(selectmax(cVouCode)-10000FROM[IA_Subsidiary]wherecvoutype='34'andimonth=0)DECLAREaaCURSORFORselectb.cwhcode,b.cinvcode,(casewheninum=0thennullwheninumisnullthennullelseinumend)inum,(casewheninum=0thennullwheninumisnullthennullelseimoney/inumend)cost,imoneyfrom(selectcwhcode,cinvcode,sum(isnull(iainquantity,0))q,sum(isnull(iainprice,0))pfromia_subsidiarywherecvoutype='34'andimonth=0groupbycwhcode,cinvcode)afullouterjoin(selectcwhcode,cinvcode,inum,imoneyfromUFDATA_001_2005..ia_summarywhereimonth=12and(inum0orimoney0))bona.cwhcode=b.cwhcodeanda.cinvcode=b.cinvcodewhereisnull(a.q,0)isnull(b.inum,0)orisnull(a.p,0)isnull(imoney,0)OPENaaFETCHNEXTFROMaaINTO@cwhcode,@cinvcode,@iainquantity,@iincost,@iainpriceWHILE(@@fetch_status=0)BEGINinsertintoia_subsidiary([bRdFlag],[cVouCode],[ID],[dVouDate],[dKeepDate],[iMonth],[cVouType],[cWhCode],[cInvCode],[cHandler],[iAInQuantity],[iInCost],[iAInPrice],[cAccounter],[cMaker],[bMoneyFlag],[bSale],[cPZID])values(1,@cVouCode,@cVouCode,'2005-12-31','2005-12-31',0,34,@cWhCode,@cInvCode,'朱虹',@iAInQuantity,@iInCost,@iAInPrice,'朱虹','朱虹',1,0,0)FETCHNEXTFROMaaINTO@cwhcode,@cinvcode,@iainquantity,@iincost,@iainpriceset@cVouCode=@cVouCode+1ENDCLOSEaaDEALLOCATEaaGOdroptablekm。
解决方案：在用友erp 系统版本U8.61软件中请先做好数据备份！问题原因是年度结转后ia_subsidiary表及ia_summary表中期初记录有错误。执行完附件中的语句后需执行重算存货总帐工具,先点‘修复存货总帐’，再点‘重算存货总帐’即可使上下年的存货期初完全一致。useufdata_001_2006selectcwhcode,cinvcode,cinvheadintokmfromia_subsidiarywhereimonth=0andcvoutype='34'groupbycwhcode,cinvcode,cinvheadhavingcinvheadisnotnulldeleteia_subsidiaryfromia_subsidiarywhereautoidnotin(selectmax(autoid)fromia_subsidiarywhereimonth=0andcvoutype='34'groupbycwhcode,cinvcode)andimonth=0andcvoutype='34'updateia_subsidiarysetiainquantity=(casewhena.inum=0thennullwhena.inumisnullthennullelsea.inumend),iincost=(casewhena.inum=0thennullwhena.inumisnullthennullelsea.imoney/a.inumend),iainprice=a.imoneyfromia_subsidiaryfullouterjoin(selectcwhcode,cinvcode,inum,imoneyfromUFDATA_001_2005..ia_summarywhereimonth=12)aonia_subsidiary.cwhcode=a.cwhcodeandia_subsidiary.cinvcode=a.cinvcodewhere(isnull(ia_subsidiary.iainquantity,0)isnull(a.inum,0)orisnull(ia_subsidiary.iainprice,0)isnull(a.imoney,0))andia_subsidiary.autoidisnotnullandia_subsidiary.imonth=0andcvoutype='34'deletefromia_subsidiarywherecvoutype='34'andimonth=0and(isnull(iainquantity,0)=0andisnull(iainprice,0)=0)updateia_subsidiarysetcinvhead=km.cinvheadFromkmfullouterjoinia_subsidiaryonia_subsidiary.cwhcode=km.cwhcodeandia_subsidiary.cinvcode=km.cinvcodewherekm.cwhcodeisnotnullandcvoutype='34'andimonth=0andisnull(ia_subsidiary.cinvhead,0)km.cinvheadSETNOCOUNTONdeclare@cVouCodeasvarchar(30),@cwhcodeasvarchar(10),@cinvcodeasvarchar(20),@iainquantityasfloat,@iincostasfloat,@iainpriceasmoneyset@cVouCode=(selectmax(cVouCode)-10000FROM[IA_Subsidiary]wherecvoutype='34'andimonth=0)DECLAREaaCURSORFORselectb.cwhcode,b.cinvcode,(casewheninum=0thennullwheninumisnullthennullelseinumend)inum,(casewheninum=0thennullwheninumisnullthennullelseimoney/inumend)cost,imoneyfrom(selectcwhcode,cinvcode,sum(isnull(iainquantity,0))q,sum(isnull(iainprice,0))pfromia_subsidiarywherecvoutype='34'andimonth=0groupbycwhcode,cinvcode)afullouterjoin(selectcwhcode,cinvcode,inum,imoneyfromUFDATA_001_2005..ia_summarywhereimonth=12and(inum0orimoney0))bona.cwhcode=b.cwhcodeanda.cinvcode=b.cinvcodewhereisnull(a.q,0)isnull(b.inum,0)orisnull(a.p,0)isnull(imoney,0)OPENaaFETCHNEXTFROMaaINTO@cwhcode,@cinvcode,@iainquantity,@iincost,@iainpriceWHILE(@@fetch_status=0)BEGINinsertintoia_subsidiary([bRdFlag],[cVouCode],[ID],[dVouDate],[dKeepDate],[iMonth],[cVouType],[cWhCode],[cInvCode],[cHandler],[iAInQuantity],[iInCost],[iAInPrice],[cAccounter],[cMaker],[bMoneyFlag],[bSale],[cPZID])values(1,@cVouCode,@cVouCode,'2005-12-31','2005-12-31',0,34,@cWhCode,@cInvCode,'朱虹',@iAInQuantity,@iInCost,@iAInPrice,'朱虹','朱虹',1,0,0)FETCHNEXTFROMaaINTO@cwhcode,@cinvcode,@iainquantity,@iincost,@iainpriceset@cVouCode=@cVouCode+1ENDCLOSEaaDEALLOCATEaaGOdroptablekm。
|
|
|手机版|小黑屋|用友十万个为什么
( 赣ICP备13006939号 )
发表于 2016-9-25 16:25:15