东莞精诚网络技术交流论坛

 找回密码
 注册
搜索
热搜: 活动 交友 discuz
查看: 2757|回复: 0

金蝶K/3现金流量表余额不正确校验方法(更新)

[复制链接]
发表于 2010-4-16 12:15:00 | 显示全部楼层 |阅读模式
自从用了K/3的现金流量表,财务出报表时经常发现K/3的现金流量表余额与科目余额表现金类科目的余额总和不相等, 在T型账户上检查只能检查到一些比较明显的问题,一些细节问题还是检查不出来,有时一个细节的问题要查找一两天,比较典型的两个问题是:1.在流量指定界面,[对方分录科目]列出现空白行 2.还是在流量指定界面,[本位币]列的余额不等于上方现金科目的金额.经过多账套的测试,可以肯定的讲,这是系统的BUG.目前用V12.1,希望下一个版本此问题能得以解决.为了方便财务部同事能自己查找原因,写了SQL代码辅助找原因,取数原理是将现金流量表的收入-支出的值与凭证的现金类科目的借方-贷方的值作差异比较.代码与效果图如下:
--以下代码是针对现金流量表主表
create proc [dbo].[现金流量表故障分析表]
@FYear int,
@FPeriod int
as

select b.FNumber as 凭证号,FAmount1 流量表金额,FAmount2 凭证金额,FAmount1-FAmount2 差额
from (
select d.FName+'-'+ltrim(b.FNumber) FNumber,
  sum(case
   when c.FNumber like 'CI[1-4].01%' then a.famount
   when c.FNumber like 'CI[1-4].02%' then -a.famount
   else 0
   end) FAmount1
from t_CashFlowBal a
  join t_Voucher b on a.FVoucherID=b.FVoucherID and a.FItemID>0
  join t_Item c on a.FItemID=c.FItemID and c.FItemClassID=9
  join t_VoucherGroup d on b.FGroupID=d.FGroupID
where [email protected] and [email protected]
group by b.FNumber,d.FName
) a
right join (
  select d.FName+'-'+ltrim(a.FNumber) FNumber,
   sum(case b.FDC when 1 then b.FAmount else -b.FAmount end) FAmount2
  from t_Voucher a
   join t_VoucherEntry b on a.FVoucherID=b.FVoucherID
   join t_VoucherGroup d on a.FGroupID=d.FGroupID
  where [email protected] and [email protected]
  and b.FAccountID IN(select FAccountID from t_Account where  (FIsCash=1 or FIsBank=1 or FIsCashFlow=1) and FDetail=1)
  group by a.FNumber,d.FName
  ) b
   on a.FNumber=b.FNumber
where isnull(FAmount1,FAmount2)<>0
union all
select '合计',FAmount1 流量表金额,FAmount2 凭证金额,FAmount1-FAmount2 差额
from (
select
  sum(case
   when c.FNumber like 'CI[1-4].01%' then a.famount
   when c.FNumber like 'CI[1-4].02%' then -a.famount
   else 0
   end) FAmount1
from t_CashFlowBal a
  join t_Voucher b on a.FVoucherID=b.FVoucherID and FItemID>0
  join t_Item c on a.FItemID=c.FItemID and c.FItemClassID=9
where [email protected] and [email protected]
) a
cross join (
  select
   sum(case b.FDC when 1 then b.FAmount else -b.FAmount end) FAmount2
  from t_Voucher a
   join t_VoucherEntry b on a.FVoucherID=b.FVoucherID
  where [email protected] and [email protected]
  and b.FAccountID IN(select FAccountID from t_Account where  (FIsCash=1 or FIsBank=1 or FIsCashFlow=1) and FDetail=1)
  ) b
/*--
转载请保留此信息:http://community.kingdee.com/pages/htl258/blog/archive/2010/03/12/401153.aspx
好望角2010-03-12
*/
GO


--考虑了凭证可能使用收,付,转的凭证字,同时去掉了绝对值的比较,能准确判断流量方向是否指定错误,修改了代码,最终代码如上.
更新日期:2010-03-22
您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|小黑屋|手机版|Archiver|精诚网络 ( 粤ICP备13030199号 )

GMT+8, 2021-10-23 19:02 , Processed in 0.112321 second(s), 20 queries , Gzip On.

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

快速回复 返回顶部 返回列表