| 问题版本: |
1-通2005 |
问题模块: |
1-总账 |
| 所属行业: |
0-通用 |
问题状态: |
1-UU通注册用户 |
| 关 键 字: |
现存量整理有误 |
适用产品: |
|
| 补 丁 号: |
|
开放状态: |
0-UU通注册用户 |
| 原问题号: |
|
提交时间: |
2007-11-30 13:34:00 |
| 问题名称: |
现存量整理后还是不对怎么办? |
| 问题现象: |
现存量整理后还是不对怎么办? |
| 原因分析: |
见问题答案 |
| 解决方案: |
执行如下脚本: 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) | |
