U8财务软件常用审计SQL查询语句(2)

U8财务软件常用审计SQL查询语句

含三个字段（ccode 科目代码，ccode_name 科目全称，bend科目末级标志）。

select http://www.70edu.comode, km.bend, http://www.70edu.comode_name + case when len(http://www.70edu.comode) >3 then '/' + http://www.70edu.comode_name else '' end
+ case when len(http://www.70edu.comode) >5 then '/' + http://www.70edu.comode_name else '' end as 科目全称
from code km inner join code km1 on left(http://www.70edu.comode,3) = http://www.70edu.comode
inner join code km2 on left(http://www.70edu.comode,5) =http://www.70edu.comode
inner join code km3 on left(http://www.70edu.comode,7) =http://www.70edu.comode
order by http://www.70edu.comode

2、检索出12月份登记主营业务收入科目的记帐凭证。

select a.* from gl_accvouch a inner join gl_accvouch b
on a.iperiod = b.iperiod and a.csign = b.csign and a.ino_id = b.ino_id
where a.iperiod = 12 and http://www.70edu.comode like '501%'


3、检索出所有通过应收帐款科目核算主营业务收入的记帐凭证。

select a.* from gl_accvouch a inner join gl_accvouch b
on a.iperiod = b.iperiod and a.csign = b.csign and a.ino_id = b.ino_id
inner join gl_accvouch c
on c.iperiod = b.iperiod and c.csign = b.csign and c.ino_id = b.ino_id
where http://www.70edu.comode like '113%' and b.md<>0 and http://www.70edu.comode like '501%' and c.mc<>0

4、检查主营业务收入明细账记录中所登记发票是否连续编号（断号，重号）。

--是否断号,先统计各类发票的总数,最大值，最小值
select coutbillsign,count(distinct coutid) 总数,max(coutid) 最大号,min(coutid) 最小号
from gl_accvouch where ccode like '501%' and mc <>0 group by coutbillsign

5、发票库（salebillvouch）中哪些发票未登记主营业务收入明细账。

select a.* from gl_accvouch a left join salebillvouch b
on a.coutbillsign = b.cvouchtype and a.coutid = b.csbvcode
where http://www.70edu.comode like '501%' and a.mc<>0 and b.sbvid is null



6、审计人员为检查主营业务收入记账金额是否准确，将主营业务收入明细帐与相关发票金额进行核对。
--汇总子表上相同发票号的金额
create view v1
as
select sbvid, sum(inatmoney) inatmoney from salebillvouchs group by sbvid

--关联发票主表
create view v2
as
select a.cvouchtype,a.csbvcode, b.inatmoney from salebillvouch a inner join v1 b
on a.sbvid = b.sbvid

--和主营业务核对
select a.mc , b.inatmoney, case when a.mc - b.inatmoney <> 0 then '出错' else '正确' end 结果 from gl_accvouch a inner join v2 b
on a.coutbillsign = b.cvouchtype and a.coutid = b.csbvcode
where http://www.70edu.comode like '501%' and a.mc<>0

7、检查每笔业务从发货到记帐凭证制单之间相差天数，结果按相差天数降序排列。

select a.iperiod,a.csign,a.ino_id,a.dbill_date, b.ddate, c.ddate ,datediff(day,c.ddate,a.dbill_date) 差值
from gl_accvouch a inner join salebillvouch b
on a.coutbillsign = b.cvouchtype and a.coutid = b.csbvcode
inner joi
n dispatchlist c
on b.sbvid = c.sbvid
where http://www.70edu.comode like '501%' and mc<>0



（五）、审计目标、结构、算法（步骤）与程序

1、审计人员为检查所转换

百度搜索“70edu”或“70教育网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，70教育网，提供经典教育范文U8财务软件常用审计SQL查询语句(2)在线全文阅读。