用友软件股份有限公司U8客户服务中心137号2010年10月21日从SQL角度分析存货与总账对账不平一、存货跟踪分析二、总账跟踪分析三、对账不平总结跟踪分析-存货DropTabletempdb..OptVouchBLUE0--------------------------------------------------存货系统---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------期初存货科目SelectcInvHead,cCode_Name,--科目cast(isnull(iAInPrice,0)-isnull(iAOutPrice,0)asdecimal(38,2))asQcPrice,--期初金额（入-出）cast(isnull(iAInQuantity,0)-isnull(iAOutQuantity,0)asfloat)asQcQuantity,--期初数量（入-出）cast(0asfloat)asInPRice,--入库金额cast(0asfloat)asInQuantity,--入库数量cast(0asfloat)asOutPrice,--出库数量cast(0asfloat)asoutQuantity--出库金额intotempdb..OptVouchBLUE0fromia_subsidiaryleftjoincodeonia_subsidiary.cInvHead=code.ccode--单据上的存货科目wherenotcPZIDisnull--凭证线索号不为空andnotcInvHeadisnull--存货科目编码不为空andia_subsidiary.cvoutype<>N'33'--单据类型不等于33andiMonth<10--记账期间小于10（期初单据记账期间为0）--------------------------------------------------------------------------------------------------------------------------期初差异科目Insertintotempdb..OptVouchBLUE0SelectcDifHead,cCode_Name,cast(isnull(iDebitDifCost,0)-isnull(iCreditDifCost,0)asdecimal(38,2))asQcPrice,cast(isnull((casewhenidebitdifcostisnullthen0else(cast(isnull(iAInQuantity,0)asfloat)-cast(isnull(iAOutQuantity,0)asfloat))end),0)-isnull((casewhenbrdflag=1then(casewheniCreditDifCostisnullthen0else(cast(isnull(iAInQuantity,0)asfloat)-cast(isnull(iAOutQuantity,0)asfloat))end)else(casewheniCreditDifCostisnullthen0else(cast(isnull(iAoutQuantity,0)asfloat)-cast(isnull(iAinQuantity,0)asfloat))end)end),0)asfloat)asQcQuantity,cast(0asfloat)asInPRice,cast(0asfloat)asInQuantity,cast(0asfloat)asOutPrice,cast(0asfloat)asoutQuantityfromia_subsidiaryleftjoincodeonia_subsidiary.cDifHead=code.ccodewherenotcPZIDisnullandnotcDifHeadisnullandia_subsidiary.cvoutype<>N'33'andiMonth<10跟踪分析-存货----------------------------------------------------------------------------------------------------------------------本期存货科目Insertintotempdb..OptVouchBLUE0SelectcInvHead,cCode_Name,cast(0asfloat)asqcPRice,cast(0asfloat)asqcQuantity,cast(isnull(iAInPrice,0)asdecimal(38,2))asinPrice,cast(isnull(iAInQuantity,0)asfloat)asinQuantity,cast(isnull(iAOutPrice,0)asdecimal(38,2))asOutPrice,cast(isnull(iAOutQuantity,0)asfloat)asoutQuantityfromia_subsidiaryleftjoincodeonia_subsidiary.cInvHead=code.ccodewherenotcPZIDisnullandnotcInvHeadisnullandia_subsidiary.cvoutype<>N'33'andiMonth=10---------------------------------------------------------------------------------------------------------------------本期差异科目Insertintotempdb..OptVouchBLUE0SelectcDifHead,cCode_Name,cast(0asfloat)asQcPrice,0asqcQuantity,isnull(iDebitDifCost,0)asInPrice,(casewhenidebitdifcostisnullthen0else(cast(isnull(iAInQuantity,0)asfloat)-cast(isnull(iAOutQuantity,0)asfloat))end)asinQuantity,cast(isnull(iCreditDifCost,0)asdecimal(38,2))asOutPrice,(casewhenbrdflag=1then(casewheniCreditDifCostisnullthen0else(cast(isnull(iAInQuantity,0)asfloat)-cast(isnull(iAOutQuantity,0)asfloat))end)else(casewheniCreditDifCostisnullthen0else(cast(isnull(iAoutQuantity,0)asfloat)-cast(isnull(iAinQuantity,0)asfloat))end)end)asoutQuantityfromia_subsidiaryleftjoincodeonia_subsidiary.cDifHead=code.ccodewherenotcPZIDisnullandnotcDifHeadisnullandia_subsidiary.cvoutype<>N'33'andiMonth=10存货小结--按记账期间统计期初和本期发生。统计对象为存货科目和差异科目--没有考虑对方科目、--只考虑已经记账单据--存货、差异科目为生成凭证时所选科目跟踪分析-总账deletefromtempdb..OptVouchBLUE0--------------------------------------------------------------------------总账系统----------------------------------------------------------------------------------------------------------------------------------------------------------------------凭证及明细帐中取非记账凭证数据---------------------------------------------------------------------------------------------------------期初未记账凭证数据Insertintotempdb..OptVouchBLUE0Selectccode,null,cast(sum(md)-sum(mc)asdecimal(38,2)),--借方金额-贷方金额cast(sum(nd_s)-sum(nc_s)asdecimal(38,2)),0,0,0,0--数量借方-数量贷方FromGL_accVouch--凭证及明细账Whereiperiod<10--凭证期间小于10Andisnull(iflag,0)<>1--凭证标志不等于1AndisNull(ibook,0)<>1--记账标志不等于1(未记账信息)Andccode=N'1401'--会计科目GroupBycCode-------------------------------------------------------------------------------------------------------本期未记账凭证数据Insertintotempdb..OptVouchBLUE0Selectccode,null,0,0,sum(md),sum(nd_s),sum(mc),sum(nc_s)FromGL_accVouchwhereiperiod=10Andisnull(iflag,0)<>1AndisNull(ibook,0)<>1Andccode=N'1401'GroupBycCode--select*fromgl_accvouch跟踪分析-总账deletefromtempdb..OptVouchBLUE0--------------------------------------------------------------------------总账系统----------------------------------------------------------------------------------------------------------------------------------------------------------------------凭证及明细帐中取非记账凭证数据---------------------------------------------------------------------------------------------------------期初未记账凭证数据Insertintotempdb..OptVouchBLUE0Selectccode,null,cast(sum(md)-sum(mc)asdecimal(38,2)),--借方金额-贷方金额cast(sum(nd_s)-sum(nc_s)asdecimal(38,2)),0,0,0,0--数量借方-数量贷方FromGL_accVouch--凭证及明细账Whereiperiod<10--凭证期间小于10Andisnull(iflag,0)<>1--凭证标志不等于1AndisNull(ibook,0)<>1--记账标志不等于1(未记账信息)Andccode=N'1401'--会计科目GroupBycCode-------------------------------------------------------------------------------------------------------本期未记账凭证数据Insertintotempdb..OptVouchBLUE0Selectccode,null,0,0,sum(md),sum(nd_s),sum(mc),sum(nc_s)FromGL_accVouchwhereiperiod=10Andisnull(iflag,0)<>1AndisNull(ibook,0)<>1Andccode=N'1401'GroupBycCode--select*fromgl_accvouch跟踪分析-总账-----------------------------------------------------------------------------------------------------------------------科目总账中取已记账凭证数据------------------------------------------------------------------------------------------------年初科目数据Insertintotempdb..OptVouchBLUE0Selectccode,null,cast((casewhencbegind_c=N'贷'then-1*mbelsembend)asdecimal(38,2)),--金额期初cast((casewhencbegind_c=N'贷'then-1*nb_selsenb_send)asdecimal(38,2)),0,0,0,0--数量期初FromGl_accsum--科目总账whereiperiod=1--会计期间（实际为年初数）AndGl_accsum.ccode=N'1401'-------------------------------------------------------------------------------------------------前期各月份科目数据Insertintotempdb..OptVouchBLUE0Selectccode,null,cast(md-mcasdecimal(38,2)),cast(nd_s-nc_sasdecimal(38,2)),0,0,0,0FromGl_accsumWhereiperiod<10AndGl_accsum.ccode=N'1401'--------------------------------------------------------------------------------------------------本期科目数据Insertintotempdb..OptVouchBLUE0Selectccode,null,0,0,md,nd_s,mc,nc_sFromGl_accsumwhereiperiod=10AndGl_accsum.ccode=N'1401'总账小结--统计存货科目、差异科目--从总账系统取数，与存货系统无关--取数是从科目总账（凭证及明细帐中取数），即总帐中的所有数据（不区分来源）。存货与总账对账总结存货与总账对账不平：1.期初：存货期初：记账期间在对账期间以前的所有已生成凭证单据总账期初：年初数+对账期间前凭证数据2.本期：存货本期：本期记账且已生成凭证单据（不考虑凭证是否记账，不考虑生成凭证期间）总账本期：本期已记账（未记账）3.不平常见原因：#存货期初未录入存货、差异科目（记账期间为0的数据统计不到）#存货生成凭证选择科目后保存凭证时修改了会计科目或者金额（来源存货系统的凭证与存货系统数据不一致）。#存货生成凭证时对方科目使用了存货/差异科目（存货系统统计不到对方科目数据）#记账期间和生成凭证期间不一致（存货数据和总账数据的所在期间不一致，造成存货按记账期间统计，总账按凭证期间统计）#除存货模块的其他模块使用了存货科目/差异科目（存货系统统计不到其他模块数据）#存在未记账凭证且对账时没有选择包含未记账凭证（存货统计已生成凭证但未记账的单据，总帐不统计）
