2014-01-07 23:16:39
原因分析: 本期的存货明细账收入金额、发出金额与总账借方、贷方对账不平，调拨业务制单没有设置中间科目；应付模块使用库存商品制单。
解决方案:
–存货与总账对账系统查询条件
–1.1 存货核算查询条件：查询会计期间、已制凭证的明细科目即（cpzid is not null）
–2.2 总账查询条件：查询会计期间、外部凭证系统为ia、的未作废（iflag is null）记账（ibook=1）
–开发恢复脚本
select * from (select md,mc,coutno_id,ino_id from dbo.GL_accvouch where ccode=’1406′ and iperiod=5) B
left join(select sum(iainprice) as iainprice,sum(iaoutprice)as iaoutprice,cpzid from ia_subsidiary where imonth=5 and cpzid is not null
group by cpzid ) A
on A.cpzid=B.coutno_id
where isnull(A.iainprice,0)<>md or isnull(A.iaoutprice,0)<>mc
order by coutno_id
–存货核算与总账对账不平总结
–查询存货明细出入库合计与凭证明细借贷方合计(考虑到对账科目传到总账的凭证可能从其他模块（如：应收/付模块、总账）生成，故需从两步考虑)
select sum(iAInPrice) as [收入金额合计],sum(iAOutPrice) as [发出金额合计] from ia_subsidiary where imonth=5 and cinvhead=’1406′ and not cPZID is null — 核算合计
select sum(md) as [核算借方合计],sum(mc) as [核算贷方合计] from gl_accvouch where iperiod=5 and ccode=’1406′ and coutsysname=’ia’ and ibook = 1 and iflag is null — 由核算制单的总账合计
select sum(md) as [总账借方],sum(mc) as [总账贷方] from gl_accvouch where iperiod=5 and ccode=’1406′ and ibook = 1 and iflag is null –总账合计
–说明一：假如“核算合计”=“由核算制单的总账合计”，而<>“总账合计”则说明有外部模块使用‘1406’制凭证，可以通过下面脚本
select coutsysname as [外部系统模块],coutno_id as [凭证索引号],ioutperiod as [外部凭证期间],iperiod as [总账期间],ino_id as [凭证号],cdigest,ccode,md,mc,ccode_equal from gl_accvouch where coutsysname is null and ccode=’1406′ and ibook = 1 and iflag is null –不是核算生成的凭证
select coutsysname,coutno_id,md,mc,* from gl_accvouch where iperiod=5 and ccode=’1406′ and ibook = 1 and iflag is null order by coutsysname –查询凭证来源
–如查询出1406有应付生成，则需将下面查询出的借方/贷方合计放在“核算合计”对应脚本的借方货方，查看算出的值是否与“总账合计”一致（如不一致，说明我的理论有问题。目前还未发现）
select sum(md),sum(mc) from gl_accvouch where iperiod=5 and ccode=’1405′ and coutsysname=’ap’ and ibook = 1 and iflag is null –应付制单借贷方合计
–说明二：假如“核算合计”<>“由核算制单的总账合计”，则说明存货核算制单分录录错（如调拨业务）、或者凭证金额手工修改
–此时需要比较借方、贷方为什么不对，可以通过以下笨拙的方式查询
–2.1 查询存货明细收入金额赋予表a 总账明细表借方金额记录赋予表b
select sum(iAInPrice) as iAInPrice,cpzid into a from ia_subsidiary where imonth=5 and cinvhead=’1406′ and not cPZID is null and not iainprice is null group by cpzid
select ino_id,sum(md) as md,coutno_id into b from gl_accvouch where iperiod=5 and ccode=’1406′ and coutsysname=’ia’ and ibook = 1 and iflag is null and md<>0 group by ino_id,coutno_id
–2.2 查询存货明细发出金额赋予表c 总账明细表贷方金额记录赋予表d
select sum(iAOutPrice) as iAOutPrice,cpzid into c from ia_subsidiary where imonth=5 and cinvhead=’1406′ and not cPZID is null and not iAOutPrice is null group by cpzid
select ino_id,sum(mc) as mc,coutno_id into d from gl_accvouch where iperiod=5 and ccode=’1406′ and coutsysname=’ia’ and ibook = 1 and iflag is null and mc<>0 group by ino_id,coutno_id
–2.1.1 查询出收入错误的凭证号
select * from b where coutno_id not in (select cpzid from a) or
coutno_id in (select cpzid from a inner join b on a.cpzid=b.coutno_id where a.iainprice<>b.md)
–2.2.1查询出发出错误的凭证号
select * from d where coutno_id not in (select cpzid from c) or
coutno_id in (select c.cpzid from c inner join d on c.cpzid=d.coutno_id where c.iaoutprice<>d.mc)
–计算出“核算合计”=“由核算制单的总账合计”即可
–查询对应的存货明细
select sum(iainprice) as [收入金额合计],sum(iaoutprice) as [发出金额合计] from ia_subsidiary
where cpzid in (’2012IA0000000001096′)
select cbustype as [业务类型],cvoucode as [单据号],cwhcode as [仓库编码],cinvcode as [存货编码],iainprice as [收入金额],iaoutprice as [发出金额],* from ia_subsidiary
where cpzid in (’2012IA0000000001096′)
–删除临时表
–drop table a drop table b drop table c drop table d
账对账的功能时出现存货核算模块与总账模块对账不平的现象，用友知识库分析主要原因如下：
存货系统中的与总账对账环节，经常发现存货系统的期初/收发存与总账的期初/收发存数据不一致。 存货与总账对账不平，存货系统是单据记完账的数据，总账是凭证记账完的数据，归集起来常见的有：
1、前期月份就已对账不平，导致后期月份也不平
2、存货期初余额未指定科目
3、存货系统记完账，但当月未生成凭证
4、总账的存货类科目除了存货系统传递外，自身或其它系统也会使用存货类科目
5、存货系统里的单据记录丢失凭证索引号
6、由四舍五入有尾差导致对账不平如图例示：
【解决方法】
