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

年度结转 U8产品支持部26号工程师李晨 对帐不平是年度结转后遇到最多的问题包括 购销链结转后期初余额和上年期末余额不平新年度中库存和存货期初不平应收应付和总帐不平总账明细帐和总帐不平今天主要讲解购销链在年度结转中后出现对帐不平现象的处理方法 主要内容 对帐不平的问题在日常维护工作中属于一类常见的问题 年度结转后的对帐不平问题属于其中的一个分支 下面我们看一个对帐不平问题数据的解决过程 来总结一下解决这类问题的思路 用这个思路我们再套用到年度结转造成的对帐不平问题中 思路 案例一 U852版本存货暂估材料余额表和采购暂估入库余额表的余额不相等 采购暂估入库余额表的余额 228617844 77存货暂估材料余额表的余额 174522322 53 采购暂估入库余额表 存货暂估材料余额表 问题分析 从这两张表中可以看出它们的期初余额就是不平的 利用输出功能将这两张表输出到EXCEL表中 对每个存货进行比较 问题分析 以存货020084为例 采购期初数量是27500 存货核算期初数量17500 这一定会导致结存不一致 查询入库单列表 入库日期小于2005 01 01的入库单都应该作为期初暂估入库单 问题分析 问题分析 存货020084的期初暂估入库数量应该是17500 那麽采购暂估入库余额表的期初数量是错误的 问题分析 问题分析 跟踪暂估入库余额表的取数过程 得到下面这条语句 SELECTT4 cVenCode T4 cInvCode SUM iOriQuantity asiOriQuantity SUM iOriMoney asiOriMoneyFrom SELECTT1 cVenCode T1 cInvCode ISNULL T1 iQuantity 0 ISNULL T2 iSQuantity 0 ISNULL T3 iSVQuantity 0 ASiOriQuantity ISNULL T1 iAPrice 0 ISNULL T2 iMoney 0 ISNULL T3 iSVAPrice 0 ASiOriMoneyFrom SELECTRdRecord cVenCode RdRecords cInvCode RdRecords AutoID RdRecords iQuantity RdRecords fACost RdRecords iAPriceFROMRdRecordINNERJOINRdRecordsONRdRecord ID RdRecords IDLEFTJOINInventoryONRdRecords cInvCode Inventory cInvCodeLEFTJOINVendorONRdRecord cVenCode Vendor cVenCodeWHERE1 1ANDcVouchType 01 ANDcBusType 普通采购 ANDRdRecord dDate 1999 01 01 ANDRdRecord dDate 2005 01 01 ANDcBusType 普通采购 GROUPBYPurSettleVouchs iRdsID asT3ONT1 AutoID T3 iRdsID AST4WHERE1 1ANDcInvCode 020084 GROUPBYT4 cVenCode T4 cInvCodeOrderBycVenCode cInvCode 把这条语句拆开分析 ISNULL T1 iQuantity 0 ISNULL T2 iSQuantity 0 ISNULL T3 iSVQuantity 0 ASiOriQuantity可以看出期初数量是有三部分组成 T1表的iQuantity减去T2表的iSQuantity再加上T3表的iSVQuantity 问题分析 从语句中看出T1表的iQuantity是rdrecords表中的入库数量 T2表的iSQuantity是rdrecords表的结算数量 T3表的iSVQuantity是PurSettleVouchs表的结算数量把存货020084带入到这三张表中分别查询就可以看出 问题分析 问题分析 rdrecords表的结算数量 PurSettleVouchs表的结算数量 很明显 RdRecords表中结算数量全是0 而PurSettleVouchs表中确有结算数量 这就导致该表期初数量的错误 问题分析 首先按PurSettleVouchs iRdsID汇总生成结算单子表的结算结果SELECTPurSettleVouchs iRdsID SUM PurSettleVouchs iSVQuantity ASiSVQuantity SUM PurSettleVouchs iSVAPrice ASiSVAPriceintolichenFROMPurSettleVouchINNERJOINPurSettleVouchsONPurSettleVouch PSVID PurSettleVouchs PSVIDWHEREPurSettleVouch dSVDate 2005 01 01 ANDcBusType 普通采购 GROUPBYPurSettleVouchs iRdsID 修改方法 根据此结果回写到Rdrecords表的累计结算数量和金额 updaterdrecordssetisquantity isvquantity imoney isvapricefromrdrecordsjoinlichenonAutoID iRdsIDwhereiSQuantityiSVQuantityoriMoneyiSVAPrice 修改方法 小结 把对帐问题具体化 把一个总的错误分解成若干部分 细分到最小的错误结点 抓住其中一个结点找出原因修改同样错误的其他结点如果还有错误 重复2 3步骤 年度结转后的对帐问题 分为两种情况 1 结转后的年度和上年比较 相同的报表新年度的期初数和上年度的期末数不相等 2 结转后的新年度不同模块之间比较 比如存货和库存期初对帐不平 应收应付和总帐期初对帐不平等 案例二 问题说明 年度结转后存货核算模块收发存汇总表2005年期初数量和2004年期末数量不等 选择仓库01进行比较2004年期末数量 171070 072005年期初数量 2409956 68 2004年收发存汇总表 2005年收发存汇总表 问题分析 错误细分 由于很容易就可以发现01仓库第一个存货30101001两年的数量就不一致2004年结存数量和金额都为0 2005年期初数量为21345 金额为0 由于我们知道存货模块的收发存汇总表是从ia subsidiary表取数 这部分内容省去了跟踪 问题分析 selectsum iainquantity sum iaoutquantity fromia subsidiarywherecwhcode 01andcinvcode 30101001 可以查出2004年数据库中30101001在01仓库结存为数量0selectsum iainquantity fromia subsidiarywherecwhcode 01andcinvcode 30101001 andcVouType 33 Andimonth 0结转到2005年后期初数量为21345 问题分析 可以看出存货30101001在结转后结存数量从0变为21345 我们只能跟踪结转过程 看一看数量是如何结转的 下面是跟踪出的语句 SELECTdistinctIA valuationass autoid IA SubTab cWhCode IA SubTab cInvCode cast str casewhenIA valuationass iiQuantityisnullthenIA valuationass ioQuantityelseIA valuationass iiQuantityend 20 5 asfloat ASSumQ casewhenIA valuationass iiPriceisnullthenIA valuationass ioPriceelseIA valuationass iiPriceend ASSumMFROM IA valuationassleftjoin select fromia subsidiarywhere isnull iainquantity 0 isnull iaoutquantity 0 0 IA SubTabonIA SubTab id IA valuationass inidleftjoinwarehouseonIA SubTab cwhcode warehouse cwhcode WHEREIA valuationass dikeepdate IA SubTab dkeepdateand Warehouse cWhvaluestyle 先进先出法 orWarehouse cWhvaluestyle 后进先出法 And IA SubTab iMonth 0or IA SubTab iMonth 0andIA SubTab cVouType 34 andIA valuationass cinvcode IA SubTab cinvcodeand IA valuationass dIKeepDateisnullOrIA valuationass dOKeepDateisnull 问题分析 问题分析 从语句中看出仓库计价方式是先进先出或后进先出的存货 明细账数据的来源是根据计价辅助数据表的结存结转的 把上面的语句添加条件 IA valuationass cwhcode 01andIA valuationass cinvcode 30101001 得出下面的结果 问题分析 这是辅助计价表中数据 问题分析 这是2005年存货期初余额 问题原因 原因找到了 2004年存货明细账和计价辅助数据表数据不一致导致的年度结转错误 解决办法 修改2004年计价辅助数据表 修改方法 备份fifofilo辅助表 select intowjg ia valuationass 887 2004 bakfromia valuationass valuationass和summary对比查看 selects cwhcode s cinvcode s imonth s inum b totalfromia summarys innerjoin selectcwhcode cinvcode imonth max imonth fromia summarygroupbycwhcode cinvcode aona cwhcode s cwhcodeanda cinvcode s cinvcodeands imonth a imonth leftjoin selectcwhcode cinvcode sum isnull iiquantity 0 isnull ioquantity 0 astotal fromia valuationass groupbycwhcode cinvcode bonb cwhcode s cwhcodeandb cinvcode s cinvcode wherenots inum b totalandinum 0 根据summary中的0库存调valuationassupdateia valuationasssetioquantity iiquantity dokeepdate dikeepdate iocost iicost ioprice iiprice covoucode 600010008 fromia valuationassrightjoin selects cwhcode s cinvcode s imonth s inum b totalfromia summarysinnerjoin selectcwhcode cinvcode imonth max imonth fromia summarygroupbycwhcode cinvcode aona cwhcode s cwhcodeanda cinvcode s cinvcodeands imonth a imonthleftjoin selectcwhcode cinvcode sum isnull iiquantity 0 isnull ioquantity 0 astotalfromia valuationassgroupbycwhcode cinvcode bonb cwhcode s cwhcodeandb cinvcode s cinvcodewheres inum 0and nots inum b total conia valuationass cwhcode c cwhcodeandia valuationass cinvcode c cinvcodewherenotisnull iiquantity 0 isnull ioquantity 0 andisnull iiquantity 0 0 修改方法 将辅助库中可以一条补差后余零的调整updateia valuationasssetioquantity iiquantity dokeepdate dikeepdate iocost iicost ioprice iiprice covoucode 600010009 fromia valuationassrightjoin selectv cwhcode v cinvcode autoid max autoid fromia valuationassvrightjoin selects cwhcode s cinvcode s imonth s inum b total cwhvaluestylefromia summarysleftjoinwarehouseonwarehouse cwhcode s cwhcodeandcwhvaluestyle 先进先出法 innerjoin selectcwhcode cinvcode imonth max imonth fromia summarygroupbycwhcode cinvcode aona cwhcode s cwhcodeanda cinvcode s cinvcodeands imonth a imonthleftjoin selectcwhcode cinvcode sum isnull iiquantity 0 isnull ioquantity 0 astotalfromia valuationassgroupbycwhcode cinvcode bonb cwhcode s cwhcodeandb cinvcode s cinvcodewherenots inum b totalandnots inum 0 donv cwhcode d cwhcodeandv cinvcode d cinvcodeandioquantityisnullwhereiiquantity d total d inumgroupbyv cwhcode v cinvcode eonia valuationass autoid e autoidleftjoin selects cwhcode s cinvcode s imonth s inum b total cwhvaluestyle total inumasmodfromia summarysleftjoinwarehouseonwarehouse cwhcode s cwhcodeandcwhvaluestyle 先进先出法 innerjoin selectcwhcode cinvcode imonth max imonth fromia summarygroupbycwhcode cinvcode aona cwhcode s cwhcodeanda cinvcode s cinvcodeands imonth a imonthleftjoin selectcwhcode cinvcode sum isnull iiquantity 0 isnull ioquantity 0 astotalfromia valuationassgroupbycwhcode cinvcode bonb cwhcode s cwhcodeandb cinvcode s cinvcodewherenots inum b totalandnots inum 0 fone cwhcode f cwhcodeande cinvcode f cinvcode 修改方法 调节需完全出库的入库数据updateia valuationasssetioquantity iiquantity dokeepdate dikeepdate iocost iicost ioprice iiprice covoucode 600010009 fromia valuationassrightjoin selectv cwhcode v cinvcode autoid max autoid fromia valuationassvrightjoin selects cwhcode s cinvcode s imonth s inum b total cwhvaluestylefromia summarysleftjoinwarehouseonwarehouse cwhcode s cwhcodeandcwhvaluestyle 先进先出法 innerjoin selectcwhcode cinvcode imonth max imonth fromia summarygroupbycwhcode cinvcode aona cwhcode s cwhcodeanda cinvcode s cinvcodeands imonth a imonthleftjoin selectcwhcode cinvcode sum isnull iiquantity 0 isnull ioquantity 0 astotalfromia valuationassgroupbycwhcode cinvcode bonb cwhcode s cwhcodeandb cinvcode s cinvcodewherenots inum b totalandnots inum 0 donv cwhcode d cwhcodeandv cinvcode d cinvcodeandioquantityisnullwhereiiquantity d total d inumgroupbyv cwhcode v cinvcode eonia valuationass autoid e autoidleftjoin selects cwhcode s cinvcode s imonth s inum b total cwhvaluestyle total inumasmodfromia summarysleftjoinwarehouseonwarehouse cwhcode s cwhcodeandcwhvaluestyle 先进先出法 innerjoin selectcwhcode cinvcode imonth max imonth fromia summarygroupbycwhcode cinvcode aona cwhcode s cwhcodeanda cinvcode s cinvcodeands imonth a imonthleftjoin selectcwhcode cinvcode sum isnull iiquantity 0 isnull ioquantity 0 astotalfromia valuationassgroupbycwhcode cinvcode bonb cwhcode s cwhcodeandb cinvcode s cinvcodewherenots inum b totalandnots inum 0 fone cwhcode f cwhcodeande cinvcode f cinvcode 修改方法 没有全部出库的分录 为避免拆单 调整入库数量updateia valuationasssetiIquantity iiquantity MOD ioprice iiprice MOD iicost covoucode 600010010 fromia valuationassrightjoin selectv cwhcode v cinvcode autoid max autoid fromia valuationassvrightjoin selects cwhcode s cinvcode s imonth s inum b total cwhvaluestylefromia summarysleftjoinwarehouseonwarehouse cwhcode s cwhcodeandcwhvaluestyle 先进先出法 innerjoin selectcwhcode cinvcode imonth max imonth fromia summarygroupbycwhcode cinvcode aona cwhcode s cwhcodeanda cinvcode s cinvcodeands imonth a imonthleftjoin selectcwhcode cinvcode sum isnull iiquantity 0 isnull ioquantity 0 astotalfromia valuationassgroupbycwhcode cinvcode bonb cwhcode s cwhcodeandb cinvcode s cinvcodewherenots inum b totalandnots inum 0 donv cwhcode d cwhcodeandv cinvcode d cinvcodeandioquantityisnullwhereiiquantity d total d inumgroupbyv cwhcode v cinvcode eonia valuationass autoid e autoidleftjoin selects cwhcode s cinvcode s imonth s inum b total cwhvaluestyle total inumasmodfromia summarysleftjoinwarehouseonwarehouse cwhcode s cwhcodeandcwhvaluestyle 先进先出法 innerjoin selectcwhcode cinvcode imonth max imonth fromia summarygroupbycwhcode cinvcode aona cwhcode s cwhcodeanda cinvcode s cinvcodeands imonth a imonthleftjoin selectcwhcode cinvcode sum isnull iiquantity 0 isnull ioquantity 0 astotalfromia valuationassgroupbycwhcode cinvcode bonb cwhcode s cwhcodeandb cinvcode s cinvcodewherenots inum b totalandnots inum 0 fone cwhcode f cwhcodeande cinvcode f cinvcode 修改方法 重点内容 下面针对这个数据中遇到的两个难点分别介绍 如何从跟踪出的语句中发现数据的错误 如何从跟踪的结果中找到我们需要的语句 计价辅助数据表 采用先进先出 后进先出计价方式需要用到的数据表 下面举一个简单的例子 录一张采购入库单 选择先进先出的仓库 数量100 单价1 存货记账后我们查看计价辅助数据表中数据 关联ia subsidiary表id字段 录入一张出库单 相同仓库 相同存货 数量20 单价不填 记账后 我们再查看计价辅助数据表 计价辅助数据表 再录入一张相同的出库单 数量为80 这样对应开始那张数量为100的入库单将被完全出库 记账后 查询计价辅助数据表 计价辅助数据表 Inid ia subsidiary id 入库单记帐的记录id按记帐时间排序 outid 对应的出库单在ia subsidiary的idIa valuationass inid相同记录的sum iiquantity 应等于ia subsidiary iainquantityIa valuationass outid相同记录的sum ioquantity 应等于ia subsidiary iaoutquantity 计价辅助数据表 我们重新看一下刚才跟踪出来的语句中的条件部分WHEREIA valuationass dikeepdate IA SubTab dkeepdateand Warehouse cWhvaluestyle 先进先出法 orWarehouse cWhvaluestyle 后进先出法 And IA SubTab iMonth 0or IA SubTab iMonth 0andIA SubTab cVouType 34 andIA valuationass cinvcode IA SubTab cinvcodeand IA valuationass dIKeepDateisnullOrIA valuationass dOKeepDateisnull 计价辅助数据表 关键内容 关键点 如何跟踪出结转年度结转时结转存货明细账的语句 跟踪 大体范围找到了 这些语句开始写入存货明细账 组织数据源的语句应该在这一片语句上面 在这些语句的上面果然发现了三条查询语句 第一条 SELECTIA Subsidiary cWhCode 34 IA Subsidiary cInvCode IA Subsidiary cInvHead casewhenIsnull IA Subsidiary cPzID 0 0 then0else1end asPZID 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 asfloat ASSumQ Sum ISNULL IA Subsidiary iAInPrice 0 ISNULL IA Subsidiary iAOutPrice 0 ASSumMFROM IA SubsidiaryinnerjoinwarehouseonIA Subsidiary cwhcode warehouse cwhcode WHEREWarehouse cWhvaluestyle 个别计价法 andWarehouse cWhvaluestyle 先进先出法 andWarehouse cWhvaluestyle 后进先出法 And IA Subsidiary iMonth 0or IA Subsidiary iMonth 0andIA Subsidiary cVouType 34 GROUPBYIA Subsidiary cWhCode IA Subsidiary cInvCode IA Subsidiary cInvHead casewhenIsnull IA Subsidiary cPzID 0 0 then0else1end cfree1 cfree2 cfree3 cfree4 cfree5 cfree6 cfree7 cfree8 cfree9 cfree10havingSum IsNull IA Subsidiary iAInQuantity 0 IsNull IA Subsidiary iAOutQuantity 0 0orSum ISNULL IA Subsidiary iAInPrice 0 ISNULL IA Subsidiary iAOutPrice 0 0 跟踪 第二条 Selectdistinctia subsidiary autoid ia subsidiary cdepcodefrom ia subsidiaryinnerjoinwarehouseonia subsidiary cwhcode warehouse cwhcode leftjoinia individualonia subsidiary brdflag 0andia subsidiary id ia individual coutidandcissale casewhenia subsidiary cvoutypein 25 26 27 28 then1elsecasewhenia subsidiary cvoutypein 5 6 05 06 then2else0endendwhereisnull ia subsidiary cbustype 假退料 Andwarehouse cwhvaluestyle 个别计价法 and ia subsidiary iMonth 0or ia subsidiary imonth 0andia subsidiary cvoutype 34 andia subsidiary cVoutype 24 andia subsidiary cVoutype 30 andia subsidiary cVoutype 21 and ia subsidiary cBatchCodeisnull and isnull ia subsidiary iainquantity 0 isnull ia subsidiary iaoutquantity 0 0 and ia subsidiary brdflag 1oria individual autoidisnull 跟踪 第三条 SELECTdistinctIA valuationass autoid IA SubTab cWhCode 34 NullAscDepCode IA SubTab cInvCode IA SubTab cInvHead casewhenIsNull IA SubTab cPzID 0 0 then0else1end asPZID cfree1 cfree2 cfree3 cfree4 cfree5 cfree6 cfree7 cfree8 cfree9 cfree10 cast str casewhenIA valuationass iiQuantityisnullthenIA valuationass ioQuantityelseIA valuationass iiQuantityend 20 5 asfloat ASSumQ casewhenIA valuationass iiPriceisnullthenIA valuationass ioPriceelseIA valuationass iiPriceend ASSumMFROM IA valuationasslef