方俊霞2010年12月22日存货核算收发存汇总表查询小例存货核算收发存汇总表查询小例【问题描述】查询存货核算的收发存汇总表，发现存在数量为零金额不为零的记录，期末处理时勾选：数量为零金额不为零自动生成出库调整单，也没有调整单产生。为什么？【案例分析处理】1、首先，根据客户的现象，第一反应就是存货明细账不等于存货总账。故直接用‘存货总账重算工具’重算。2、重算后，以某存货为例子，重新查询存货总账和存货明细账，其结存是相等的，且都是数量为零金额也为零。3、再重新查询该存货的收发存汇总表，发现还是存在数量为零，金额不为零的现象。截图如下：仓库01，存货编码为01002的存货，其存货总账11月底的数量、金额均为0仓库01，存货编码为01002的存货，其存货总账11月底的数量、金额均为0其对应的存货明细账11月底的数量、金额也均为0其对应的存货明细账11月底的数量、金额也均为0再查询存货核算的收发存汇总表，再查询存货核算的收发存汇总表，奇怪！奇怪！(补充：主要参见语句如下，仅供参考：）我们知道收发存汇总表的数据都是来自于ia_subsidiary的我们知道收发存汇总表的数据都是来自于ia_subsidiary的insertintotempdb..[InOutSumTmpFANG0]select6,INVENTORY.cInvCCode,casewhen(ia_subsidiary.cWhCodeisnull)thenN''elseia_subsidiary.cWhCodeendascWhCode,casewhen(ia_subsidiary.cWhCodeisnull)thenN''elsecWhNameendascWhName,IA_Subsidiary.cInvCode,cInvAddCode,cInvName,cInvStd,ComputationUnit.CComUnitName,iInvWeight,0ASQcQuantity,0ASQcPrice,0asqcDif,(casebRdFlagwhen1then(casewhen(iAInQuantityisnull)then0elseiAInQuantityend)else0end)asInQuantity,(casebRdFlagwhen1then(casewhen(iAInPriceisnull)then0elsecast(iAInPriceasdecimal(20,2))end)else0end)asInPrice,(casebRdFlagwhen1then(isnull(iDebitDifCost,0)-isnull(iCreditDifCost,0))else0end)asInDif,(casebRdFlagwhen0then(casewhen(iAOutQuantityisnull)then0elseiAOutQuantityend)else0end)asOutQuantity,(casebRdFlagwhen0then(casewhen(iAOutPriceisnull)then0elsecast(iAOutPriceasdecimal(20,2))end)else0end)AsOutPrice,(casebRdFlagwhen0then(isnull(iCreditDifCost,0)-isnull(iDebitDifCost,0))else0end)asOutDiffromIA_Subsidiarywith(nolock)leftjoinInventorywith(nolock)onIA_Subsidiary.cInvCode=Inventory.cInvCodeleftjoinComputationUnitwith(nolock)ONinventory.cComunitCode=ComputationUnit.ccomunitcodeleftjoinwarehousewith(nolock)onia_subsidiary.cWhCode=warehouse.cwhcodeWherecVouType<>N'33'andiMonth<>0andIA_Subsidiary.cInvCode>=N'01002'AndIA_Subsidiary.cInvCode<=N'01002'AND(WareHouse.bInCost=1orWareHouse.cwhcodeisnull)And(dKeepDate>=N'2010-11-01'AnddKeepDate<=N'2010-11-30')Selecttempdb..[InOutSumTmpFANG0].cWhCodeasN'仓库编码',tempdb..[InOutSumTmpFANG0].cWhNameasN'仓库名称',tempdb..[InOutSumTmpFANG0].cInvCodeasN'存货编码',tempdb..[InOutSumTmpFANG0].cInvAddCodeasN'存货代码',tempdb..[InOutSumTmpFANG0].cInvNameasN'存货名称',tempdb..[InOutSumTmpFANG0].cInvStdasN'存货规格',tempdb..[InOutSumTmpFANG0].CComUnitNameasN'存货单位',tempdb..[InOutSumTmpFANG0].iInvWeightasN'存货重量',ltrim(sum(Cast(qcQuantityasdecimal(20,2))))asN'期初数量',ltrim(str(sum(cast(qcPriceasdecimal(20,2))),20,2))asN'期初金额',ltrim(str(sum(cast(qcDifasdecimal(20,2))),20,2))asN'期初差价',ltrim(str((casesum(cast(qcQuantityasdecimal(20,2)))when0thennullelsesum(qcPrice+qcDif)/sum(qcQuantity)end),20,2))asN'期初单价',ltrim(str(sum(cast(qcPrice+qcDifasdecimal(20,2))),20,2))asN'期初成本',ltrim(str(sum(InQuantity),20,2))asN'收入数量',ltrim(str(sum(cast(inPriceasdecimal(20,2))),20,2))asN'收入金额',ltrim(str(sum(cast(indifasdecimal(20,2))),20,2))asN'收入差价',ltrim(str((caseround(sum(InQuantity),8)when0thennullelsesum(cast(inPriceasdecimal(20,2))+indif)/sum(InQuantity)end),20,2))asN'收入单价',ltrim(str(sum(cast(inPriceasdecimal(20,2))+indif),20,2))asN'收入成本',ltrim(str(sum(OutQuantity),20,2))asN'发出数量',ltrim(str(sum(cast(OutPriceasdecimal(20,2))),20,2))asN'发出金额',ltrim(str(sum(OutDif),20,2))asN'发出差价',ltrim(str((casesum(OutQuantity)when0thennullelsesum(cast(OutPriceasdecimal(20,2))+OutDif)/sum(OutQuantity)end),20,2))asN'发出单价',ltrim(str(sum(cast(OutPriceasdecimal(20,2))+OutDif),20,2))asN'发出成本',ltrim(str(cast(sum(qcQuantity)+sum(InQuantity)-sum(OutQuantity)asdecimal(20,2)),20,2))asN'结存数量',ltrim(str(cast(sum(qcPrice)+sum(cast(inPriceasdecimal(20,2)))-sum(cast(OutPriceasdecimal(20,2)))asdecimal(20,2)),20,2))asN'结存金额',ltrim(str(cast(sum(qcDif)+sum(InDif)-sum(OutDif)asdecimal(20,2)),20,2))asN'结存差价',casewhencast(sum(qcQuantity)+sum(InQuantity)-sum(OutQuantity)asdecimal(20,2))=0thennullelseltrim(str(cast(cast(sum(qcPrice)+sum(cast(inPriceasdecimal(20,2)))-sum(cast(OutPriceasdecimal(20,2)))+(sum(qcDif)+sum(InDif)-sum(OutDif))asdecimal(20,2))/cast(sum(qcQuantity)+sum(InQuantity)-sum(OutQuantity)asdecimal(20,2))asdecimal(20,2)),20,2))endasN'结存单价',ltrim(str(cast(sum(qcPrice)+sum(cast(inPriceasdecimal(20,2)))-sum(cast(OutPriceasdecimal(20,2)))+(sum(qcDif)+sum(InDif)-sum(OutDif))asdecimal(20,2)),20,2))asN'结存成本'intotempdb..[InOutSumTmp0FANG0]fromtempdb..[InOutSumTmpFANG0]groupbygrade,i,tempdb..[InOutSumTmpFANG0].从上门的语句我们可以看出：1、收发存汇总表的数据主要来自于ia_subsidiary2、金额取的是四舍五入后的两位小数金额。那么正常情况下，金额都是两位小数。难道客户的金额有异常的？？查询下看看：发现客户期初数据金额都是3位的。发现客户期初数据金额都是3位的。经与客户沟通，期初数据时导入的。难怪！！因为正常情况下，在单据记账插入明细账的时候，金额是会自动四舍五入到两位小数写入的。【处理方案】updateia_subsidiarysetiainprice=round(iainprice,2),iaoutprice=round(iaoutprice,2)whereiainprice<>round(iainprice,2)oriaoutprice<>round(iaoutprice,2)按照客户的数据：再重算存货总账再期末处理，勾选：数量为零金额不为零自动生成调整单即可
