用友U8 U821存货核算，此帐套只启用存货核算而未启用库存管理，在存货核算中做出库单时看到的现存量数不对，已做整理现存量脚本补丁和重现升级数据库和启用库存模块整理现存量都不行，请帮忙解决2004年的现存量数与结存数相等用友U8 U821存货核算，此帐套只启用存货核算而未启用库存管理，在存货核算中做出库单时看到的现存量数不对，已做整理现存量脚本补丁和重现升级数据库和启用库存模块整理现存量都不行，请帮忙解决2004年的现存量数与结存数相等
问题原因:821只启用存货核算如何整理现存量问题咨询。 解决方法:/*整理现存量脚本 只使用存货系统的帐套用此方法整理现存量：for 8.21在查询分析器执行即可。*/Delete From CurrentStock if exists(select * from sysobjects where name=‘tempcurrentstock‘) --Delete From CurrentStockdrop table TempCurrentStockSELECT cWhCode,cInvCode,(CASE WHEN cFree1=‘‘THEN NULL ELSE cFree1 END) AS cFree1 ,(CASE WHEN cFree2=‘‘THEN NULL ELSE cFree2 END) AS cFree2 ,iainquantity AS iQuantity, CAST(0 AS FLOAT) AS fOutQuantity,CAST(0 AS FLOAT) AS fInQuantityINTO TempCurrentStock From ia_subsidiary Where cvoutype=‘34‘group by ia_subsidiary.cWhCode,ia_subsidiary.cInvCode,ia_subsidiary.cFree1,ia_subsidiary.cFree2,ia_subsidiary.iainquantityINSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,iQuantity) SELECT cWhCode, cInvCode,(CASE WHEN cFree1=‘‘THEN NULL ELSE cFree1 END) AS cFree1 ,(CASE WHEN cFree2=‘‘THEN NULL ELSE cFree2 END) AS cFree2 , (CASE WHEN bRdFlag<>0 THEN iQuantity ELSE -iQuantity END) AS iQuantityFROM RdRecord INNER JOIN RdRecords ON RdRecord.ID = RdRecords.ID WHERE cVouchType<>‘33‘AND cVouchType<>‘34‘ AND RdRecord.dDate>=‘2001-1-01‘ --(该日期为模块启用日期)INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fOutQuantity) SELECT EnDispatchs.cWhCode, EnDispatchs.cInvCode,(CASE WHEN EnDispatchs.cFree1=‘‘ THEN NULL ELSE EnDispatchs.cFree1 END) AS cFree1 ,(CASE WHEN EnDispatchs.cFree2=‘‘ THEN NULL ELSE EnDispatchs.cFree2 END) AS cFree2 , (ISNULL(iQuantity,0)-ISNULL(fOutQuantity,0)) AS fOutQuantityFROM EnDispatch INNER JOIN EnDispatchs ON EnDispatch.EDID = EnDispatchs.EDID WHERE ISNULL(EnDispatchs.cWhCode,‘‘)<>‘‘ AND ISNULL(EnDispatch.cSTCode,‘‘)<>‘‘ AND EnDispatchs.iQuantity>0 AND bReturnFlag=0INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fOutQuantity) SELECT DispatchLists.cWhCode as cWhCode, DispatchLists.cInvCode as cInvCode,(CASE WHEN DispatchLists.cFree1=‘‘ THEN NULL ELSE DispatchLists.cFree1 END) AS cFree1 , (CASE WHEN DispatchLists.cFree2=‘‘ THEN NULL ELSE DispatchLists.cFree2 END) AS cFree2 , (ISNULL(iQuantity,0)-ISNULL(fOutQuantity,0)) AS fOutQuantity FROM DispatchLists INNER JOIN DispatchList ON DispatchLists.DLID = DispatchList.DLID WHERE DispatchList.cVouchType=‘05‘ AND ISNULL(DispatchLists.cWhCode,‘‘)<>‘‘ AND ISNULL(DispatchList.cSTCode,‘‘)<>‘‘ AND (DispatchList.iSale=0 or DispatchList.iSale IS NULL) AND DispatchLists.iQuantity>0 INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fOutQuantity) SELECT cWhCode, cInvCode,(CASE WHEN cFree1=‘‘THEN NULL ELSE cFree1 END) AS cFree1 , (CASE WHEN cFree2=‘‘THEN NULL ELSE cFree2 END) AS cFree2 , (ISNULL(iQuantity,0)-ISNULL(fOutQuantity,0)) AS fOutQuantityFROM SaleBillVouchs INNER JOIN SaleBillVouch ON SaleBillVouch.SBVID=SaleBillVouchs.SBVID WHERE ISNULL(SaleBillVouchs.cWhCode,‘‘)<>‘‘ AND (SaleBillVouch.iDisp=0 OR SaleBillVouch.iDisp IS NULL) AND ISNULL(cBusType,‘‘)<>‘委托‘ AND ISNULL(cSTCode,‘‘)<>‘‘ AND ISNULL(cInvalider,‘‘)=‘‘ AND SaleBillVouchs.iQuantity>0 INSERT INTO CurrentStock(cWhCode,cInvCode,cFree1,cFree2,iQuantity,fOutQuantity,fInQuantity) SELECT cWhCode, cInvCode,cFree1,cFree2 , sum(iQuantity) AS iQuantity,sum(ISNULL(fOutQuantity,0)) AS fOutQuantity, sum(ISNULL(fInQuantity,0)) AS fInQuantityFROM TempCurrentStock GROUP BY cWhCode,cInvCode,cFree1,cFree2Delete From CurrentStockwhere iquantity is null and foutquantity=0 and finquantity=0
解决方案：
问题原因:821只启用存货核算如何整理现存量问题咨询。 解决方法:/*整理现存量脚本 只使用存货系统的帐套用此方法整理现存量：for 8.21在查询分析器执行即可。*/Delete From CurrentStock if exists(select * from sysobjects where name=‘tempcurrentstock‘) --Delete From CurrentStockdrop table TempCurrentStockSELECT cWhCode,cInvCode,(CASE WHEN cFree1=‘‘THEN NULL ELSE cFree1 END) AS cFree1 ,(CASE WHEN cFree2=‘‘THEN NULL ELSE cFree2 END) AS cFree2 ,iainquantity AS iQuantity, CAST(0 AS FLOAT) AS fOutQuantity,CAST(0 AS FLOAT) AS fInQuantityINTO TempCurrentStock From ia_subsidiary Where cvoutype=‘34‘group by ia_subsidiary.cWhCode,ia_subsidiary.cInvCode,ia_subsidiary.cFree1,ia_subsidiary.cFree2,ia_subsidiary.iainquantityINSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,iQuantity) SELECT cWhCode, cInvCode,(CASE WHEN cFree1=‘‘THEN NULL ELSE cFree1 END) AS cFree1 ,(CASE WHEN cFree2=‘‘THEN NULL ELSE cFree2 END) AS cFree2 , (CASE WHEN bRdFlag<>0 THEN iQuantity ELSE -iQuantity END) AS iQuantityFROM RdRecord INNER JOIN RdRecords ON RdRecord.ID = RdRecords.ID WHERE cVouchType<>‘33‘AND cVouchType<>‘34‘ AND RdRecord.dDate>=‘2001-1-01‘ --(该日期为模块启用日期)INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fOutQuantity) SELECT EnDispatchs.cWhCode, EnDispatchs.cInvCode,(CASE WHEN EnDispatchs.cFree1=‘‘ THEN NULL ELSE EnDispatchs.cFree1 END) AS cFree1 ,(CASE WHEN EnDispatchs.cFree2=‘‘ THEN NULL ELSE EnDispatchs.cFree2 END) AS cFree2 , (ISNULL(iQuantity,0)-ISNULL(fOutQuantity,0)) AS fOutQuantityFROM EnDispatch INNER JOIN EnDispatchs ON EnDispatch.EDID = EnDispatchs.EDID WHERE ISNULL(EnDispatchs.cWhCode,‘‘)<>‘‘ AND ISNULL(EnDispatch.cSTCode,‘‘)<>‘‘ AND EnDispatchs.iQuantity>0 AND bReturnFlag=0INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fOutQuantity) SELECT DispatchLists.cWhCode as cWhCode, DispatchLists.cInvCode as cInvCode,(CASE WHEN DispatchLists.cFree1=‘‘ THEN NULL ELSE DispatchLists.cFree1 END) AS cFree1 , (CASE WHEN DispatchLists.cFree2=‘‘ THEN NULL ELSE DispatchLists.cFree2 END) AS cFree2 , (ISNULL(iQuantity,0)-ISNULL(fOutQuantity,0)) AS fOutQuantity FROM DispatchLists INNER JOIN DispatchList ON DispatchLists.DLID = DispatchList.DLID WHERE DispatchList.cVouchType=‘05‘ AND ISNULL(DispatchLists.cWhCode,‘‘)<>‘‘ AND ISNULL(DispatchList.cSTCode,‘‘)<>‘‘ AND (DispatchList.iSale=0 or DispatchList.iSale IS NULL) AND DispatchLists.iQuantity>0 INSERT INTO TempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fOutQuantity) SELECT cWhCode, cInvCode,(CASE WHEN cFree1=‘‘THEN NULL ELSE cFree1 END) AS cFree1 , (CASE WHEN cFree2=‘‘THEN NULL ELSE cFree2 END) AS cFree2 , (ISNULL(iQuantity,0)-ISNULL(fOutQuantity,0)) AS fOutQuantityFROM SaleBillVouchs INNER JOIN SaleBillVouch ON SaleBillVouch.SBVID=SaleBillVouchs.SBVID WHERE ISNULL(SaleBillVouchs.cWhCode,‘‘)<>‘‘ AND (SaleBillVouch.iDisp=0 OR SaleBillVouch.iDisp IS NULL) AND ISNULL(cBusType,‘‘)<>‘委托‘ AND ISNULL(cSTCode,‘‘)<>‘‘ AND ISNULL(cInvalider,‘‘)=‘‘ AND SaleBillVouchs.iQuantity>0 INSERT INTO CurrentStock(cWhCode,cInvCode,cFree1,cFree2,iQuantity,fOutQuantity,fInQuantity) SELECT cWhCode, cInvCode,cFree1,cFree2 , sum(iQuantity) AS iQuantity,sum(ISNULL(fOutQuantity,0)) AS fOutQuantity, sum(ISNULL(fInQuantity,0)) AS fInQuantityFROM TempCurrentStock GROUP BY cWhCode,cInvCode,cFree1,cFree2Delete From CurrentStockwhere iquantity is null and foutquantity=0 and finquantity=0
www.kuaiji66.com 天龙瑞德
