用有年度结转不平问题.ppt

年度结转,U8产品支持部 26号工程师 李晨,对帐不平是年度结转后遇到最多的问题 包括： 购销链结转后期初余额和上年期末余额不平 新年度中库存和存货期初不平 应收应付和总帐不平 总账明细帐和总帐不平 今天主要讲解购销链在年度结转中后出现对帐不平现象的处理方法。,主要内容,对帐不平的问题在日常维护工作中属于一类常见的问题 ,年度结转后的对帐不平问题属于其中的一个分支。 下面我们看一个对帐不平问题数据的解决过程，来总结一下解决这类问题的思路。 用这个思路我们再套用到年度结转造成的对帐不平问题中。,思 路,案例一,U852版本存货暂估材料余额表和采购暂估入库余额表的余额不相等。 采购暂估入库余额表的余额：228617844.77 存货暂估材料余额表的余额：174522322.53,采购暂估入库余额表,存货暂估材料余额表,问题分析,从这两张表中可以看出它们的期初余额就是不平的。 利用输出功能将这两张表输出到EXCEL表中，对每个存货进行比较。,问题分析,以存货020084为例，采购期初数量是27500,存货核算期初数量17500,这一定会导致结存不一致。 查询入库单列表，入库日期小于2005-01-01的入库单都应该作为期初暂估入库单。,问题分析,问题分析,存货020084的期初暂估入库数量应该是17500,那麽采购暂估入库余额表的期初数量是错误的。,问题分析,问题分析,跟踪暂估入库余额表的取数过程,得到下面这条语句：SELECT T4.cVenCode,T4.cInvCode,SUM(iOriQuantity) as iOriQuantity,SUM(iOriMoney) as iOriMoney From (SELECT T1.cVenCode,T1.cInvCode,(ISNULL(T1.iQuantity,0)-ISNULL(T2.iSQuantity,0)+ISNULL(T3.iSVQuantity,0) AS iOriQuantity,(ISNULL(T1.iAPrice,0)-ISNULL(T2.iMoney,0)+ISNULL(T3.iSVAPrice,0) AS iOriMoney From (SELECT RdRecord.cVenCode,RdRecords.cInvCode,RdRecords.AutoID,RdRecords.iQuantity,RdRecords.fACost,RdRecords.iAPrice FROM RdRecord INNER JOIN RdRecords ON RdRecord.ID=RdRecords.ID LEFT JOIN Inventory ON RdRecords.cInvCode=Inventory.cInvCode LEFT JOIN Vendor ON RdRecord.cVenCode=Vendor.cVenCode WHERE 1=1 AND cVouchType=01 AND cBusType=普通采购 AND RdRecord.dDate=1999-01-01 AND RdRecord.dDate=2005-01-01 AND cBusType=普通采购 GROUP BY PurSettleVouchs.iRdsID) as T3 ON T1.AutoID=T3.iRdsID) AS T4 WHERE 1=1 AND cInvCode=020084 GROUP BY T4.cVenCode,T4.cInvCode Order By cVenCode,cInvCode,把这条语句拆开分析。 (ISNULL(T1.iQuantity,0)- ISNULL(T2.iSQuantity,0)+ ISNULL(T3.iSVQuantity,0) AS iOriQuantity 可以看出期初数量是有三部分组成，T1表的iQuantity减去T2表的iSQuantity再加上T3表的iSVQuantity。,问题分析,从语句中看出T1表的iQuantity是rdrecords表中的入库数量， T2表的iSQuantity是rdrecords表的结算数量， T3表的iSVQuantity是PurSettleVouchs表的结算数量 把存货020084带入到这三张表中分别查询就可以看出。,问题分析,问题分析,rdrecords表的 结算数量,PurSettleVouchs表 的结算数量,很明显，RdRecords表中结算数量全是0，而PurSettleVouchs表中确有结算数量，这就导致该表期初数量的错误。,问题分析,首先按PurSettleVouchs.iRdsID汇总生成结算单子表的结算结果 SELECT PurSettleVouchs.iRdsID,SUM(PurSettleVouchs.iSVQuantity) AS iSVQuantity,SUM(PurSettleVouchs.iSVAPrice) AS iSVAPrice into lichen FROM PurSettleVouch INNER JOIN PurSettleVouchs ON PurSettleVouch.PSVID=PurSettleVouchs.PSVID WHERE PurSettleVouch.dSVDate=2005-01-01 AND cBusType=普通采购 GROUP BY PurSettleVouchs.iRdsID,修改方法,根据此结果回写到Rdrecords表的累计结算数量和金额。 update rdrecords set isquantity=isvquantity,imoney=isvaprice from rdrecords join lichen on AutoID=iRdsID where iSQuantityiSVQuantity or iMoneyiSVAPrice,修改方法,小结,把对帐问题具体化，把一个总的错误分解成若干部分，细分到最小的错误结点。 抓住其中一个结点找出原因 修改同样错误的其他结点 如果还有错误，重复2，3步骤,年度结转后的对帐问题,分为两种情况： 1.结转后的年度和上年比较，相同的报表新年度的期初数和上年度的期末数不相等。 2.结转后的新年度不同模块之间比较，比如存货和库存期初对帐不平，应收应付和总帐期初对帐不平等,案例二,问题说明：年度结转后存货核算模块收发存汇总表2005年期初数量和2004年期末数量不等。 选择仓库01进行比较 2004年期末数量： 171070.07 2005年期初数量：2409956.68,2004年收发存汇总表,2005年收发存汇总表,问题分析,错误细分：由于很容易就可以发现01仓库第一个存货30101001两年的数量就不一致 2004年结存数量和金额都为0，2005年期初数量为21345，金额为0。 由于我们知道存货模块的收发存汇总表是从ia_subsidiary 表取数，这部分内容省去了跟踪。,问题分析,select sum(iainquantity),sum(iaoutquantity)from ia_subsidiary where cwhcode=01 and cinvcode=30101001 可以查出2004年数据库中30101001在01仓库结存为数量0 select sum(iainquantity) from ia_subsidiary where cwhcode=01 and cinvcode=30101001 and cVouType33 And imonth=0 结转到2005年后期初数量为21345,问题分析,可以看出存货30101001在结转后结存数量从0变为21345。 我们只能跟踪结转过程，看一看数量是如何结转的。 下面是跟踪出的语句：,SELECT distinct IA_valuationass.autoid, IA_SubTab.cWhCode,IA_SubTab.cInvCode, cast(str(case when IA_valuationass.iiQuantity is null then IA_valuationass.ioQuantity else IA_valuationass.iiQuantity end ),20,5) as float) AS SumQ, (case when IA_valuationass.iiPrice is null then IA_valuationass.ioPrice else IA_valuationass.iiPrice end ) AS SumM FROM (IA_valuationass left join (select * from ia_subsidiary where (isnull(iainquantity,0)-isnull(iaoutquantity,0)0 ) IA_SubTab on IA_SubTab.id=IA_valuationass.inid left join warehouse on IA_SubTab.cwhcode=warehouse.cwhcode) WHERE IA_valuationass.dikeepdate=IA_SubTab.dkeepdate and (Warehouse.cWhvaluestyle=先进先出法 or Warehouse.cWhvaluestyle=后进先出法) And (IA_SubTab.iMonth0 or (IA_SubTab.iMonth=0 and IA_SubTab.cVouType=34) and IA_valuationass.cinvcode=IA_SubTab.cinvcode and (IA_valuationass.dIKeepDate is null Or IA_valuationass.dOKeepDate is null),问题分析,问题分析,从语句中看出仓库计价方式是先进先出或后进先出的存货，明细账数据的来源是根据计价辅助数据表的结存结转的。 把上面的语句添加条件： IA_valuationass.cwhcode=01 and IA_valuationass.cinvcode=30101001 得出下面的结果。,问题分析,这是辅助计价表中数据,问题分析,这是2005年存货期初余额,问题原因,原因找到了：2004年存货明细账和计价辅助数据表数据不一致导致的年度结转错误。 解决办法：修改2004年计价辅助数据表。,修改方法,-备份fifo filo 辅助表 -select * into wjg_ia_valuationass_887_2004_bak from ia_valuationass -valuationass 和 summary 对比查看 -select s.cwhcode,s.cinvcode, s.imonth,s.inum ,b.total from ia_summary s -inner join (select cwhcode,cinvcode ,imonth=max(imonth) from ia_summary group by cwhcode,cinvcode ) a on a.cwhcode=s.cwhcode and a.cinvcode=s.cinvcode and s.imonth=a.imonth -left join -(select cwhcode,cinvcode ,sum(isnull(iiquantity,0)-isnull(ioquantity,0) as total -from ia_valuationass -group by cwhcode,cinvcode) b on b.cwhcode=s.cwhcode and b.cinvcode=s.cinvcode -where not s.inum=b.total and inum=0,-根据summary中的0库存调valuationass update ia_valuationass set ioquantity=iiquantity,dokeepdate=dikeepdate,iocost=iicost,ioprice=iiprice,covoucode=600010008 from ia_valuationass right join( select s.cwhcode,s.cinvcode, s.imonth,s.inum ,b.total from ia_summary s inner join (select cwhcode,cinvcode ,imonth=max(imonth) from ia_summary group by cwhcode,cinvcode ) a on a.cwhcode=s.cwhcode and a.cinvcode=s.cinvcode and s.imonth=a.imonth left join (select cwhcode,cinvcode ,sum(isnull(iiquantity,0)-isnull(ioquantity,0) as total from ia_valuationass group by cwhcode,cinvcode) b on b.cwhcode=s.cwhcode and b.cinvcode=s.cinvcode where s.inum=0 and (not s.inum=b.total) ) c on ia_valuationass.cwhcode=c.cwhcode and ia_valuationass.cinvcode=c.cinvcode where not isnull(iiquantity,0)=isnull(ioquantity,0) and isnull(iiquantity,0)0,修改方法,-将辅助库中可以一条补差后余零的调整 update ia_valuationass set ioquantity=iiquantity,dokeepdate=dikeepdate,iocost=iicost,ioprice=iiprice,covoucode=600010009 from ia_valuationass right join ( select v.cwhcode,v.cinvcode,autoid=max(autoid) from ia_valuationass v right join ( select s.cwhcode,s.cinvcode, s.imonth,s.inum ,b.total,cwhvaluestyle from ia_summary s left join warehouse on warehouse.cwhcode=s.cwhcode and cwhvaluestyle=先进先出法 inner join (select cwhcode,cinvcode ,imonth=max(imonth) from ia_summary group by cwhcode,cinvcode ) a on a.cwhcode=s.cwhcode and a.cinvcode=s.cinvcode and s.imonth=a.imonth left join (select cwhcode,cinvcode ,sum(isnull(iiquantity,0)-isnull(ioquantity,0) as total from ia_valuationass group by cwhcode,cinvcode) b on b.cwhcode=s.cwhcode and b.cinvcode=s.cinvcode where not s.inum=b.total and not s.inum=0 ) d on v.cwhcode=d.cwhcode and v.cinvcode=d.cinvcode and ioquantity is null where iiquantity=d.total-d.inum group by v.cwhcode,v.cinvcode ) e on ia_valuationass.autoid=e.autoid left join (select s.cwhcode,s.cinvcode, s.imonth,s.inum ,b.total,cwhvaluestyle ,total-inum as mod from ia_summary s left join warehouse on warehouse.cwhcode=s.cwhcode and cwhvaluestyle=先进先出法 inner join (select cwhcode,cinvcode ,imonth=max(imonth) from ia_summary group by cwhcode,cinvcode ) a on a.cwhcode=s.cwhcode and a.cinvcode=s.cinvcode and s.imonth=a.imonth left join (select cwhcode,cinvcode ,sum(isnull(iiquantity,0)-isnull(ioquantity,0) as total from ia_valuationass group by cwhcode,cinvcode) b on b.cwhcode=s.cwhcode and b.cinvcode=s.cinvcode where not s.inum=b.total and not s.inum=0 ) f on e.cwhcode=f.cwhcode and e.cinvcode=f.cinvcode,修改方法,-调节需完全出库的入库数据 update ia_valuationass set ioquantity=iiquantity,dokeepdate=dikeepdate,iocost=iicost,ioprice=iiprice,covoucode=600010009 from ia_valuationass right join ( select v.cwhcode,v.cinvcode,autoid=max(autoid) from ia_valuationass v right join ( select s.cwhcode,s.cinvcode, s.imonth,s.inum ,b.total,cwhvaluestyle from ia_summary s left join warehouse on warehouse.cwhcode=s.cwhcode and cwhvaluestyle=先进先出法 inner join (select cwhcode,cinvcode ,imonth=max(imonth) from ia_summary group by cwhcode,cinvcode ) a on a.cwhcode=s.cwhcode and a.cinvcode=s.cinvcode and s.imonth=a.imonth left join (select cwhcode,cinvcode ,sum(isnull(iiquantity,0)-isnull(ioquantity,0) as total from ia_valuationass group by cwhcode,cinvcode) b on b.cwhcode=s.cwhcode and b.cinvcode=s.cinvcode where not s.inum=b.total and not s.inum=0 ) d on v.cwhcode=d.cwhcode and v.cinvcode=d.cinvcode and ioquantity is null where iiquantityd.total-d.inum group by v.cwhcode,v.cinvcode ) e on ia_valuationass.autoid=e.autoid left join ( select s.cwhcode,s.cinvcode, s.imonth,s.inum ,b.total,cwhvaluestyle ,total-inum as mod from ia_summary s left join warehouse on warehouse.cwhcode=s.cwhcode and cwhvaluestyle=先进先出法 inner join (select cwhcode,cinvcode ,imonth=max(imonth) from ia_summary group by cwhcode,cinvcode ) a on a.cwhcode=s.cwhcode and a.cinvcode=s.cinvcode and s.imonth=a.imonth left join (select cwhcode,cinvcode ,sum(isnull(iiquantity,0)-isnull(ioquantity,0) as total from ia_valuationass group by cwhcode,cinvcode) b on b.cwhcode=s.cwhcode and b.cinvcode=s.cinvcode where not s.inum=b.total and not s.inum=0 ) f on e.cwhcode=f.cwhcode and e.cinvcode=f.cinvcode,修改方法,-没有全部出库的分录，为避免拆单，调整入库数量 update ia_valuationass set iIquantity=iiquantity-MOD,ioprice=iiprice-MOD*iicost,covoucode=600010010 from ia_valuationass right join ( select v.cwhcode,v.cinvcode,autoid=max(autoid) from ia_valuationass v right join ( select s.cwhcode,s.cinvcode, s.imonth,s.inum ,b.total,cwhvaluestyle from ia_summary s left join warehouse on warehouse.cwhcode=s.cwhcode and cwhvaluestyle=先进先出法 inner join (select cwhcode,cinvcode ,imonth=max(imonth) from ia_summary group by cwhcode,cinvcode ) a on a.cwhcode=s.cwhcode and a.cinvcode=s.cinvcode and s.imonth=a.imonth left join (select cwhcode,cinvcode ,sum(isnull(iiquantity,0)-isnull(ioquantity,0) as total from ia_valuationass group by cwhcode,cinvcode) b on b.cwhcode=s.cwhcode and b.cinvcode=s.cinvcode where not s.inum=b.total and not s.inum=0 ) d on v.cwhcode=d.cwhcode and v.cinvcode=d.cinvcode and ioquantity is null where iiquantityd.total-d.inum group by v.cwhcode,v.cinvcode ) e on ia_valuationass.autoid=e.autoid left join ( select s.cwhcode,s.cinvcode, s.imonth,s.inum ,b.total,cwhvaluestyle ,total-inum as mod from ia_summary s left join warehouse on warehouse.cwhcode=s.cwhcode and cwhvaluestyle=先进先出法 inner join (select cwhcode,cinvcode ,imonth=max(imonth) from ia_summary group by cwhcode,cinvcode ) a on a.cwhcode=s.cwhcode and a.cinvcode=s.cinvcode and s.imonth=a.imonth left join (select cwhcode,cinvcode ,sum(isnull(iiquantity,0)-isnull(ioquantity,0) as total from ia_valuationass group by cwhcode,cinvcode) b on b.cwhcode=s.cwhcode and b.cinvcode=s.cinvcode where not s.inum=b.total and not s.inum=0 ) f on e.cwhcode=f.cwhcode and e.cinvcode=f.cinvcode,修改方法,重点内容,下面针对这个数据中遇到的两个难点分别介绍。 如何从跟踪出的语句中发现数据的错误。 如何从跟踪的结果中找到我们需要的语句。,计价辅助数据表,采用先进先出，后进先出计价方式需要用到的数据表。 下面举一个简单的例子：录一张采购入库单，选择先进先出的仓库，数量100，单价1，存货记账后我们查看计价辅助数据表中数据。,关联ia_subsidiary 表id字段,录入一张出库单，相同仓库，相同存货，数量20，单价不填。记账后，我们再查看计价辅助数据表。,计价辅助数据表,再录入一张相同的出库单，数量为80，这样对应开始那张数量为100的入库单将被完全出库。记账后，查询计价辅助数据表。,计价辅助数据表,Inid:ia_subsidiary.id(入库单记帐的记录id按记帐时间排序),outid:对应的出库单在ia_subsidiary的id Ia_valuationass.inid相同记录的sum(iiquantity),应等于ia_subsidiary.iainquantity Ia_valuationass.outid相同记录的sum(ioquantity)应等于ia_subsidiary.iaoutquantity,计价辅助数据表,我们重新看一下刚才跟踪出来的语句中的条件部分 WHERE IA_valuationass.dikeepdate=IA_SubTab.dkeepdate and (Warehouse.cWhvaluestyle=先进先出法 or Warehouse.cWhvaluestyle=后进先出法) And (IA_SubTab.iMonth0 or (IA_SubTab.iMonth=0 and IA_SubTab.cVouType=34) and IA_valuationass.cinvcode=IA_SubTab.cinvcode and (IA_valuationass.dIKeepDate is null Or IA_valuationass.dOKeepDate is null),计价辅助数据表,关键内容,关键点： 如何跟踪出结转年度结转时结转存货明细账的语句？,跟踪,大体范围找到了！这些语句开始写入存货明细账，组织数据源的语句应该在这一片语句上面,在这些语句的上面果然发现了三条查询语句： 第一条： SELECT IA_Subsidiary.cWhCode,34,IA_Subsidiary.cInvCode,IA_Subsidiary.cInvHead,(case when Isnull(IA_Subsidiary.cPzID,0) = 0 then 0 else 1 end ) as PZID,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10, cast(str(Sum(IsNull(IA_Subsidiary.iAInQuantity,0)-IsNull(IA_Subsidiary.iAOutQuantity,0),20,5) as float) AS SumQ, Sum(ISNULL(IA_Subsidiary.iAInPrice,0)-ISNULL(IA_Subsidiary.iAOutPrice,0) AS SumM FROM (IA_Subsidiary inner join warehouse on IA_Subsidiary.cwhcode=warehouse.cwhcode) WHERE Warehouse.cWhvaluestyle个别计价法 and Warehouse.cWhvaluestyle先进先出法 and Warehouse.cWhvaluestyle后进先出法 And (IA_Subsidiary.iMonth0 or (IA_Subsidiary.iMonth=0 and IA_Subsidiary.cVouType=34) GROUP BY IA_Subsidiary.cWhCode,IA_Subsidiary.cInvCode,IA_Subsidiary.cInvHead,(case when Isnull(IA_Subsidiary.cPzID,0) = 0 then 0 else 1 end),cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10 having Sum(IsNull(IA_Subsidiary.iAInQuantity,0)-IsNull(IA_Subsidiary.iAOutQuantity,0)0 or Sum(ISNULL(IA_Subsidiary.iAInPrice,0)-ISNULL(IA_Subsidiary.iAOutPrice,0)0,跟踪,第二条： Select distinct ia_subsidiary.autoid,ia_subsidiary.cdepcode from (ia_subsidiary inner join warehouse on ia_subsidiary.cwhcode= warehouse.cwhcode) left join ia_individual on ia_subsidiary.brdflag=0 and ia_subsidiary.id=ia_individual.coutid and cissale=case when ia_subsidiary.cvoutype in(25,26,27,28) then 1 else case when ia_subsidiary.cvoutype in(5,6,05,06) then 2 else 0 end end where isnull(ia_subsidiary.cbustype,)假退料 And warehouse.cwhvaluestyle =个别计价法 and (ia_subsidiary.iMonth0 or (ia_subsidiary.imonth=0 and ia_subsidiary.cvoutype=34) and ia_subsidiary.cVoutype24 and ia_subsidiary.cVoutype30 and ia_subsidiary.cVoutype21 and (ia_subsidiary.cBatchCode is null) and (isnull(ia_subsidiary.iainquantity,0) - isnull(ia_subsidiary.iaoutquantity,0)0) and (ia_subsidiary.brdflag=1 or ia_individual.autoid is null),跟踪,第三条： SELECT distinct IA_valuationass.autoid, IA_SubTab.cWhCode,34,Null As cDepCode,IA_SubTab.cInvCode,IA_SubTab.cInvHead,(case when IsNull(IA_SubTab.cPzID,0)=0 then 0 else 1 end ) as PZID,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10, cast(str(case when IA_valuationass.iiQuantity is null then IA_valuationass.ioQuantity else IA_valuationass.iiQuantity end ),20,5) as float) AS SumQ, (case when IA_valuationass.iiPrice is null then IA_valuationass.ioPrice else IA_valuationass.iiPrice end ) AS SumM FROM (IA_valuationass left join (select * from ia_subsidiary where (isnull(iainquantity,0)-isnull(iaoutquantity,0)0 ) IA_SubTab on IA_SubTab.id=IA_valuationass.inid left join warehouse on IA_SubTab.cwhcode=warehouse.cwhcode) WHERE IA_valuationass.dikeepdate=IA_SubTab.dkeepdate and (Warehouse.cWhvaluestyle=先进先出法 or Warehouse.cWhvaluestyle=后进先出法) And (IA_SubTab.iMonth0 or (IA_SubTab.iMonth=0 and IA_SubTab.cVouType=34) and IA_valuationass.cinvcode=IA_SubTab.cinvcode and (IA_valuationass.dIKeepDate is null Or IA_valuationass.dOKeepDate is null),跟踪,小结,跟踪可以看出存货明细账是按照仓库计价方式结转。 这套数据中01仓库是先进先出计价方式，所以第三条语句是我们需要的。,计划价,计划价法对入库单记帐时，按存货计划价记帐，入库实际价与计划有差异部分记入ia_subsidiary的借方差异IDebitDifCost或贷方差异ICreditDifCost。Ia_summary累加借方差异或贷方差异。选择选项：入库超支记借方，节约记贷方，按选项记帐。不选择选项时，入库差异全部记借方，入库存货的实际金额大于计划价时，借方差异为正；入库存货的实际金额小于计划价时，借方差异为负。,计划价,计划价法对出库单记帐时，发出单据的手填实际金额大于计划价时，货方差异为正；发出单据的手填实际金额小于计划价时，贷方差异为负。出库差异全部记贷方。 期末处理时，计算差异率，生成差异结转单，在ia_subsidiary追加一条记录，写ia_summary的iDifRate 。 期末生成差异结转单时，选取此项则登记差异账；不选则不登记差异账，期末无差异结转。,计划价,录入一张入库单，仓库为计划价，存货计划价为20，入库数量为10，入库单价为25，金额250。记账后，我们看一下Ia_subsidiary，Ia_summary的结果。,计划价,再录入一张入库单，还是同样的仓库和存货，入库数量为10，入库单价为16，金额160。记账后，的结果如下：,计划价,录入一张出库库单，同样的仓库和存货，出库数量为20，出库单价不填。记账后，的结果如下：,计划价,进行期末处理。,计划价,数据库中的结果。,个别计价,IA_Individual（个别计价表） CInID 蓝字入库单或红字出库单明细帐中AUTOID COutID 出库单（红、蓝）或红字入库单ID CQuantity 对应的分配数量,个别计价,举例：个别计价仓库，入库单数量100，单价1，出库数量50，记账时进行个别计价成本分配后，查询数据库中的结果：,个别计价,出库单记帐后，ia_subsidiary 的cbatchcode 写对应入库单在ia_subisidiary.autoid 同时在ia_individual 写入一条记录，该记录的inid为相对应的入库单在ia_subsidiary.autoid, outid写出库单rdrecords.autoid计划价法,WWW . UFSOFT . COM,