用友T6软件本期存货核算与总账对账不平脚本查询
技术咨询，有偿服务！
复制微信号
本期存货核算与总账对账不平脚本查询
原因分析：本期的存货明细账收入金额、发出金额与总账借方、贷方对账不平，调拨业务制单没有设置中间科目；应付模块使用库存商品制单。
问题解答：--存货与总账对账系统查询条件--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) Bleft join(select sum(iainprice) as iainprice,sum(iaoutprice)as iaoutprice,cpzid from ia_subsidiary where imonth=5 and cpzid is not nullgroup by cpzid ) Aon A.cpzid=B.coutno_idwhere isnull(A.iainprice,0)<>md or isnull(A.iaoutprice,0)<>mcorder 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 总账明细表借方金额记录赋予表bselect 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 cpzidselect 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 总账明细表贷方金额记录赋予表dselect 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 cpzidselect 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
如果您的问题还没有解决，或者在百度上找不到答案可以到右上角搜索中进行搜索。