|
|
问题现象：在用友U8erp系统中用友U8版本号：U8软件的供应链产品线存货核算模块出现如下问题：u821存货核算，此帐套只启用存货核算而未启用库存管理，在存货核算中做出库单时看到的现存量数不对，已做整理现存量脚本补丁和重现升级数据库和启用库存模块整理现存量都不行，请帮忙解决2004年的现存量数与结存数相等请帮忙解答！
原因分析：821只启用存货核算如何整理现存量问题咨询。
解决方案：在用友erp 系统版本U8软件中/*整理现存量脚本只使用存货系统的帐套用此方法整理现存量：for8.21在查询分析器执行即可。*/DeleteFromCurrentStockifexists(select*fromsysobjectswherename=‘tempcurrentstock‘)--DeleteFromCurrentStockdroptableTempCurrentStockSELECTcWhCode,cInvCode,(CASEWHENcFree1=‘‘THENNULLELSEcFree1END)AScFree1,(CASEWHENcFree2=‘‘THENNULLELSEcFree2END)AScFree2,iainquantityASiQuantity,CAST(0ASFLOAT)ASfOutQuantity,CAST(0ASFLOAT)ASfInQuantityINTOTempCurrentStockFromia_subsidiaryWherecvoutype=‘34‘groupbyia_subsidiary.cWhCode,ia_subsidiary.cInvCode,ia_subsidiary.cFree1,ia_subsidiary.cFree2,ia_subsidiary.iainquantityINSERTINTOTempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,iQuantity)SELECTcWhCode,cInvCode,(CASEWHENcFree1=‘‘THENNULLELSEcFree1END)AScFree1,(CASEWHENcFree2=‘‘THENNULLELSEcFree2END)AScFree2,(CASEWHENbRdFlag0THENiQuantityELSE-iQuantityEND)ASiQuantityFROMRdRecordINNERJOINRdRecordsONRdRecord.ID=RdRecords.IDWHEREcVouchType‘33‘ANDcVouchType‘34‘ANDRdRecord.dDate>=‘2001-1-01‘--(该日期为模块启用日期)INSERTINTOTempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fOutQuantity)SELECTEnDispatchs.cWhCode,EnDispatchs.cInvCode,(CASEWHENEnDispatchs.cFree1=‘‘THENNULLELSEEnDispatchs.cFree1END)AScFree1,(CASEWHENEnDispatchs.cFree2=‘‘THENNULLELSEEnDispatchs.cFree2END)AScFree2,(ISNULL(iQuantity,0)-ISNULL(fOutQuantity,0))ASfOutQuantityFROMEnDispatchINNERJOINEnDispatchsONEnDispatch.EDID=EnDispatchs.EDIDWHEREISNULL(EnDispatchs.cWhCode,‘‘)‘‘ANDISNULL(EnDispatch.cSTCode,‘‘)‘‘ANDEnDispatchs.iQuantity>0ANDbReturnFlag=0INSERTINTOTempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fOutQuantity)SELECTDispatchLists.cWhCodeascWhCode,DispatchLists.cInvCodeascInvCode,(CASEWHENDispatchLists.cFree1=‘‘THENNULLELSEDispatchLists.cFree1END)AScFree1,(CASEWHENDispatchLists.cFree2=‘‘THENNULLELSEDispatchLists.cFree2END)AScFree2,(ISNULL(iQuantity,0)-ISNULL(fOutQuantity,0))ASfOutQuantityFROMDispatchListsINNERJOINDispatchListONDispatchLists.DLID=DispatchList.DLIDWHEREDispatchList.cVouchType=‘05‘ANDISNULL(DispatchLists.cWhCode,‘‘)‘‘ANDISNULL(DispatchList.cSTCode,‘‘)‘‘AND(DispatchList.iSale=0orDispatchList.iSaleISNULL)ANDDispatchLists.iQuantity>0INSERTINTOTempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fOutQuantity)SELECTcWhCode,cInvCode,(CASEWHENcFree1=‘‘THENNULLELSEcFree1END)AScFree1,(CASEWHENcFree2=‘‘THENNULLELSEcFree2END)AScFree2,(ISNULL(iQuantity,0)-ISNULL(fOutQuantity,0))ASfOutQuantityFROMSaleBillVouchsINNERJOINSaleBillVouchONSaleBillVouch.SBVID=SaleBillVouchs.SBVIDWHEREISNULL(SaleBillVouchs.cWhCode,‘‘)‘‘AND(SaleBillVouch.iDisp=0ORSaleBillVouch.iDispISNULL)ANDISNULL(cBusType,‘‘)‘委托‘ANDISNULL(cSTCode,‘‘)‘‘ANDISNULL(cInvalider,‘‘)=‘‘ANDSaleBillVouchs.iQuantity>0INSERTINTOCurrentStock(cWhCode,cInvCode,cFree1,cFree2,iQuantity,fOutQuantity,fInQuantity)SELECTcWhCode,cInvCode,cFree1,cFree2,sum(iQuantity)ASiQuantity,sum(ISNULL(fOutQuantity,0))ASfOutQuantity,sum(ISNULL(fInQuantity,0))ASfInQuantityFROMTempCurrentStockGROUPBYcWhCode,cInvCode,cFree1,cFree2DeleteFromCurrentStockwhereiquantityisnullandfoutquantity=0andfinquantity=0。
|
|
|手机版|小黑屋|用友十万个为什么
( 赣ICP备13006939号 )
发表于 2016-9-25 15:34:38