| 解决方案: | 执行如下脚本: SELECT * FROM sysobjects WHERE name = 'tempcurrentstock' DROP TABLE TempCurrentStock SELECT 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 fInQuantity INTO TempCurrentStock FROM ia_subsidiary WHERE cvoutype = '34' GROUP BY ia_subsidiary.cWhCode, ia_subsidiary.cInvCode, ia_subsidiary.cFree1, ia_subsidiary.cFree2, ia_subsidiary.iainquantity INSERT 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 iQuantity FROM RdRecord INNER JOIN RdRecords ON RdRecord.ID = RdRecords.ID WHERE cVouchType <> '33' AND cVouchType <> '34' AND RdRecord.dDate >= '2007-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 fOutQuantity FROM EnDispatch INNER JOIN EnDispatchs ON EnDispatch.EDID = EnDispatchs.EDID WHERE ISNULL(EnDispatchs.cWhCode, '') <> '' AND ISNULL(EnDispatch.cSTCode, '') <> '' AND EnDispatchs.iQuantity > 0 AND bReturnFlag = 0 INSERT 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 fOutQuantity FROM 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 fInQuantity FROM TempCurrentStock GROUP BY cWhCode, cInvCode, cFree1, cFree2 DELETE FROM CurrentStock WHERE (iQuantity IS NULL) AND (fOutQuantity = 0) AND (fInQuantity = 0) |
