巧用AO模板采集用友U8系列财务账及整理语句

武汉市审计局  刘芳

近年来，随着被审计单位财务软件不断更新升级，现场审计实施系统（AO）的采集模板已无法满足现场采集财务数据的需求。本文以最常见的用友U8系列为例，介绍如何使用AO的模板采集功能转换财务账。

   一、将采集到的原始财务数据库还原到SQL中

    从被审计单位用友U8中备份出来所需采集账套，格式如图1所示：

   或者对财务账套对应的sql数据库进行分离拷贝，格式如图2所示：

 将数据库还原到安装有AO 的电脑上，如图3所示：

二、分析财务数据库表结构

通过对用友U8数据库的分析，确定重点表：code为科目表，gl_accsum为科目余额表，GL_accass为辅助余额表，GL_accvouch为凭证表，Department为辅助信息表。用友U8系列的凭证表与凭证辅助明细表在一张表存储。

按照AO手动采集的要求，科目表关键要素为科目代码、科目名称；余额表关键要素为科目编码、科目余额、余额方向；辅助余额表关键要素为科目编码、辅助核算类型、辅助名称、辅助核算编码、辅助核算余额、余额方向；凭证表关键要素为会计月份、凭证类型、凭证号、分录序号、凭证日期、制单人、审核人、出纳、记账人、科目编码、摘要、借方发生额、贷方发生额、部门编码、人员编码、客户编码、供应商编码；辅助信息表关键要素为辅助核算类型、辅助核算编码、辅助核算名称；辅助余额表关键要素为科目编码、辅助核算类型、辅助核算编码、辅助名称、辅助核算余额、余额方向；凭证辅助明细表关键要素为辅助类型名称、辅助编码、借方发生额、贷方发生额、摘要、科目编码、分录序号。对上述表进行分析分别生成可以直接导入AO的标准科目表、科目余额表、凭证表、辅助信息表、辅助余额表。如图4所示：

   三、手动导入AO

得到标准表后，进行标准表手动导入AO。

利用AO手动采集功能进行采集时用友U8源凭证表为一个表，如图5所示：

图5 手动导入AO之凭证表导入方式

按照AO手动采集的流程，依次导入科目余额表、会计科目表、凭证表、辅助信息表、辅助余额表，如图6所示：

注意事项：用友U8系列的凭证表与辅助凭证表为一张表，遇到导辅助账时，需要凭证表与辅助明细表中的分录序号完全一一对应，否则无法导入，解决方式是确定分录序号时选择多个字段（月份、凭证号、凭证类型、分录序号字段），如图7所示：

图7手动导入AO之凭证表分录序号选择

导辅助明细表也需要选对应字段（月份、凭证号、凭证类型、分录序号字段），如图8所示：

  导入凭证辅助明细表时，根据辅助类型存储方式的不同有三种可选导入方式，用友U8系列选择“数据以单表横排方式存储”，如下：

  遇到手动导入其他财务软件时，要根据辅助类型存储方式选择导入方式，导的过程可以多试几次，根据模板提示选择正确的导入模式。

    四、生成手动采集模板

    所有的信息采集完成后，选择“保存设置”，生成用友U8系列采集模板，如图10所示:

    以后再对用友U8系列进行数据采集，只需从数据库中选择生成五张标准表（如果凭证表与辅助明细表为一张表），利用AO数据采集功能，选择该模板，可大大提高财务数据采集成效。如图11所示：

 五、常见手动采集财务系统语句参考

    如何从用友数据库结构分析中快速整理出六张标准表，是采集转换的基础。笔者梳理了日常采集过程中形成的对用友系列关键表的整理语句，以供参考。

（一）用友U8系列关键表的整理语句

--生成会计科目表

SELECT

ccode as 科目代码,

ccode_name as 科目名称,

iyear 年份

into 标准科目表

FROM code

order by ccode

--生成科目余额表

Select

ccode  as 科目编码 ,

mb  as 科目余额,

cbegind_c as 余额方向,

iyear 年份

into 标准余额表

from  gl_accsum

where iperiod='1'

order by ccode

--生成辅助余额表

select 

ccode as 科目编码,

'部门分类' as 辅助核算类型,

'部门' as 辅助名称,

cdept_id as 辅助核算编码,

mb as 辅助核算余额,

cbegind_c as 余额方向,

iyear 年份

into 标准辅助余额表

from GL_accass

where cdept_id is not null and cperson_id is null and iperiod='1'

union

select

ccode as 科目编码,

'人员分类' as 辅助核算类型,

'人员' as 辅助名称,

cperson_id as 辅助核算编码,

mb as 辅助核算余额,

cbegind_c as 余额方向,

iyear 年份

from GL_accass

where cperson_id is not null and iperiod='1'

union

select ccode as 科目编码,

'客户分类' as 辅助核算类型,

'客户' as 辅助名称,

ccus_id as 辅助核算编码,

mb as 辅助核算余额,

cbegind_c as 余额方向,

iyear 年份

from GL_accass

where ccus_id is not null and iperiod='1'

union

select ccode as 科目编码,

'供应商分类' as 辅助核算类型,

'供应商' as 辅助名称,

csup_id as 辅助核算编码,

mb as 辅助核算余额,

cbegind_c as 余额方向,

iyear 年份

from GL_accass

where csup_id is not null and iperiod='1'

--生成凭证表

select

iperiod as 会计月份,

csign as 凭证类型,

ino_id as 凭证号,

inid as 分录序号,

cast(dbill_date as DATE) as 凭证日期,

cbill as 制单人,

ccheck as 审核人,

ccashier as 出纳,

cbook as 记账人,

ccode as 科目编码,

cdigest as 摘要,

md as 借方发生额,

mc as 贷方发生额,

cdept_id as 部门编码,

cperson_id as 人员编码,

ccus_id as 客户编码,

csup_id as 供应商编码,

iyear 年份

into 标准凭证表

from GL_accvouch

where iperiod between 1 and 12

--生成辅助信息表

select '部门' as 辅助核算类型,

cdepcode as 辅助核算编码,

cdepname as 辅助核算名称

/*into 标准辅助信息表*/

from Department

union

select '人员' as 辅助核算类型,

cPersonCode as 辅助核算编码,

cPersonName as 辅助核算名称

from Person

union

select '客户' as 辅助核算类型,

ccuscode as 辅助核算编码,

ccusname as 辅助核算名称

from Customer

union

select '供应商' as 辅助核算类型,

cVenCode as 辅助核算编码,

cvenname as 辅助核算名称

from Vendor

（二）用友NC5.7系列关键表的整理语句

--会计科目表,[PK_GLORGBOOK]是单位账套编号

select

subjcode   科目编码  ,

subjname   科目名称  ,

balanorient   余额方向  ,

subjlev 科目级次  ,

endflag 末级标志  ,

into 标准科目表

from wc.BD_accsubj where [PK_GLORGBOOK]='0001A510000000000***' /*输入对应单位账套编号*/

order by [SUBJCODE]

--辅助信息表

SELECT 

FREEVALUEID 辅助核算标识

,b.[BDNAME] 辅助类型

,[VALUECODE] 辅助编码

,[VALUENAME] 辅助名称

into 标准辅助信息表

FROM [wc].[WC].[GL_FREEVALUE]   a

left join  WC.BD_BDINFO b

on a.[CHECKTYPE]=b.[PK_BDINFO]

where  [FREEVALUEID]

in (SELECT  distinct FREEVALUEID

FROM WC.GL_DETAIL where FREEVALUEID is not null  )

order by  b.[BDNAME] ,[VALUECODE]

--科目余额表临时表

SELECT

pk_balance 余额主键  ,

pk_accsubj 科目主键  ,

localdebitamount 本币借发生额  ,

localcreditamount  本币贷发生额  ,

 [SUBJCODE]  科目编码,

 [SUBJNAME]  科目名称

--into a_kmyeb_1 --生成科目余额表临时表

FROM   GL_balance  a

left join  dbo.a_kmb b 

on a.[科目主键]=b.[PK_ACCSUBJ]

left join dbo.a_fzmxb d

on a.[辅助核算标识]=d.FREEVALUEID

where 会计年度=2016  and 会计期间=00   --采集年账套数据

order by  [SUBJCODE]

--科目余额表

SELECT

sum([本币借发生额]) [本币借发生额]

,sum([本币贷发生额]) [本币贷发生额]

,科目编码

,科目名称

into 标准科目余额表

FROM [wc].[dbo].[a_kmyeb_1]

group by

科目编码

,科目名称

--辅助余额表

SELECT 

[会计年度]

辅助核算类型,

辅助核算编码,

辅助核算名称,

[本币借发生额],

[本币贷发生额],

科目编码,

科目名称,

into 标准辅助余额表

FROM [wc].[dbo].[a_kmyeb_1] where [辅助核算标识] is not null

--凭证主表

select

year 会计年度  ,

period  会计期间  ,

no 凭证编码  ,

prepareddate  制单日期  ,

signdate   签字日期  ,

checkeddate   审核日期  ,

tallydate  记账日期  ,

attachment 附单据数  ,

pk_prepared   制单人 ,

pk_checked 审核人 ,

pk_casher  出纳  ,

pk_manager 记账人 ,

voucherkind   凭证类型  ,

totaldebit 借方合计 ,

totalcredit   贷方合计  ,

explanation   摘要  ,

into 标准凭证主表

from wc.GL_VOUCHER

where year=2016 and period<>'00' order by period  --采集年账套数据

--凭证明细表

select

FREEVALUEID 辅助核算标识,

[BDNAME] 辅助核算类型,

[VALUECODE] 辅助核算编码,

[VALUENAME] 辅助核算名称,

pk_detail  分录主键  ,

pk_voucher 凭证主键  ,

pk_accsubj 科目主键  ,

pk_currtype   币种主键  ,

pk_sob  账簿主键  ,

pk_corp 公司编码  ,

detailindex   分录号 ,

assid 辅助核算标识  ,

explanation   摘要内容  ,

price 单价  ,

excrate1   汇率  ,

excrate2   汇率  ,

debitquantity 借方数量  ,

debitamount   原币借发生额  ,

fracdebitamount 辅币借发生额  ,

localdebitamount 本币借发生额  ,

creditquantity  贷方数量  ,

creditamount  原币贷发生额  ,

fraccreditamount 辅币贷发生额  ,

localcreditamount  本币贷发生额  ,    

c.[会计期间],

c.[凭证编码], c.[制单日期],

c.[签字日期],

c.[审核日期],

c.[记账日期],

[SUBJLEV]   科目级次,

[SUBJCODE]  科目编码,

[SUBJNAME]  科目名称

into 标准凭证明细表

FROM WC.GL_DETAIL a

left join  dbo.a_kmb b

on a.pk_accsubj=b.[PK_ACCSUBJ]

left join  dbo.a_pzb c

on a.pk_voucher=c.凭证主键

left join dbo.a_fzmxb d

on a.FREEVALUEID=d.FREEVALUEID

where a.pk_voucher in (SELECT  distinct pk_voucher  FROM [wc].[dbo].[a_pzb])

巧用AO模板采集用友U8系列财务账及整理语句

武汉市审计局  刘芳

近年来，随着被审计单位财务软件不断更新升级，现场审计实施系统（AO）的采集模板已无法满足现场采集财务数据的需求。本文以最常见的用友U8系列为例，介绍如何使用AO的模板采集功能转换财务账。

   一、将采集到的原始财务数据库还原到SQL中

    从被审计单位用友U8中备份出来所需采集账套，格式如图1所示：

   或者对财务账套对应的sql数据库进行分离拷贝，格式如图2所示：

 将数据库还原到安装有AO 的电脑上，如图3所示：

二、分析财务数据库表结构

通过对用友U8数据库的分析，确定重点表：code为科目表，gl_accsum为科目余额表，GL_accass为辅助余额表，GL_accvouch为凭证表，Department为辅助信息表。用友U8系列的凭证表与凭证辅助明细表在一张表存储。

按照AO手动采集的要求，科目表关键要素为科目代码、科目名称；余额表关键要素为科目编码、科目余额、余额方向；辅助余额表关键要素为科目编码、辅助核算类型、辅助名称、辅助核算编码、辅助核算余额、余额方向；凭证表关键要素为会计月份、凭证类型、凭证号、分录序号、凭证日期、制单人、审核人、出纳、记账人、科目编码、摘要、借方发生额、贷方发生额、部门编码、人员编码、客户编码、供应商编码；辅助信息表关键要素为辅助核算类型、辅助核算编码、辅助核算名称；辅助余额表关键要素为科目编码、辅助核算类型、辅助核算编码、辅助名称、辅助核算余额、余额方向；凭证辅助明细表关键要素为辅助类型名称、辅助编码、借方发生额、贷方发生额、摘要、科目编码、分录序号。对上述表进行分析分别生成可以直接导入AO的标准科目表、科目余额表、凭证表、辅助信息表、辅助余额表。如图4所示：

   三、手动导入AO

得到标准表后，进行标准表手动导入AO。

利用AO手动采集功能进行采集时用友U8源凭证表为一个表，如图5所示：

图5 手动导入AO之凭证表导入方式

按照AO手动采集的流程，依次导入科目余额表、会计科目表、凭证表、辅助信息表、辅助余额表，如图6所示：

注意事项：用友U8系列的凭证表与辅助凭证表为一张表，遇到导辅助账时，需要凭证表与辅助明细表中的分录序号完全一一对应，否则无法导入，解决方式是确定分录序号时选择多个字段（月份、凭证号、凭证类型、分录序号字段），如图7所示：

图7手动导入AO之凭证表分录序号选择

导辅助明细表也需要选对应字段（月份、凭证号、凭证类型、分录序号字段），如图8所示：

  导入凭证辅助明细表时，根据辅助类型存储方式的不同有三种可选导入方式，用友U8系列选择“数据以单表横排方式存储”，如下：

  遇到手动导入其他财务软件时，要根据辅助类型存储方式选择导入方式，导的过程可以多试几次，根据模板提示选择正确的导入模式。

    四、生成手动采集模板

    所有的信息采集完成后，选择“保存设置”，生成用友U8系列采集模板，如图10所示:

    以后再对用友U8系列进行数据采集，只需从数据库中选择生成五张标准表（如果凭证表与辅助明细表为一张表），利用AO数据采集功能，选择该模板，可大大提高财务数据采集成效。如图11所示：

 五、常见手动采集财务系统语句参考

    如何从用友数据库结构分析中快速整理出六张标准表，是采集转换的基础。笔者梳理了日常采集过程中形成的对用友系列关键表的整理语句，以供参考。

（一）用友U8系列关键表的整理语句

--生成会计科目表

SELECT

ccode as 科目代码,

ccode_name as 科目名称,

iyear 年份

into 标准科目表

FROM code

order by ccode

--生成科目余额表

Select

ccode  as 科目编码 ,

mb  as 科目余额,

cbegind_c as 余额方向,

iyear 年份

into 标准余额表

from  gl_accsum

where iperiod='1'

order by ccode

--生成辅助余额表

select 

ccode as 科目编码,

'部门分类' as 辅助核算类型,

'部门' as 辅助名称,

cdept_id as 辅助核算编码,

mb as 辅助核算余额,

cbegind_c as 余额方向,

iyear 年份

into 标准辅助余额表

from GL_accass

where cdept_id is not null and cperson_id is null and iperiod='1'

union

select

ccode as 科目编码,

'人员分类' as 辅助核算类型,

'人员' as 辅助名称,

cperson_id as 辅助核算编码,

mb as 辅助核算余额,

cbegind_c as 余额方向,

iyear 年份

from GL_accass

where cperson_id is not null and iperiod='1'

union

select ccode as 科目编码,

'客户分类' as 辅助核算类型,

'客户' as 辅助名称,

ccus_id as 辅助核算编码,

mb as 辅助核算余额,

cbegind_c as 余额方向,

iyear 年份

from GL_accass

where ccus_id is not null and iperiod='1'

union

select ccode as 科目编码,

'供应商分类' as 辅助核算类型,

'供应商' as 辅助名称,

csup_id as 辅助核算编码,

mb as 辅助核算余额,

cbegind_c as 余额方向,

iyear 年份

from GL_accass

where csup_id is not null and iperiod='1'

--生成凭证表

select

iperiod as 会计月份,

csign as 凭证类型,

ino_id as 凭证号,

inid as 分录序号,

cast(dbill_date as DATE) as 凭证日期,

cbill as 制单人,

ccheck as 审核人,

ccashier as 出纳,

cbook as 记账人,

ccode as 科目编码,

cdigest as 摘要,

md as 借方发生额,

mc as 贷方发生额,

cdept_id as 部门编码,

cperson_id as 人员编码,

ccus_id as 客户编码,

csup_id as 供应商编码,

iyear 年份

into 标准凭证表

from GL_accvouch

where iperiod between 1 and 12

--生成辅助信息表

select '部门' as 辅助核算类型,

cdepcode as 辅助核算编码,

cdepname as 辅助核算名称

/*into 标准辅助信息表*/

from Department

union

select '人员' as 辅助核算类型,

cPersonCode as 辅助核算编码,

cPersonName as 辅助核算名称

from Person

union

select '客户' as 辅助核算类型,

ccuscode as 辅助核算编码,

ccusname as 辅助核算名称

from Customer

union

select '供应商' as 辅助核算类型,

cVenCode as 辅助核算编码,

cvenname as 辅助核算名称

from Vendor

（二）用友NC5.7系列关键表的整理语句

--会计科目表,[PK_GLORGBOOK]是单位账套编号

select

subjcode   科目编码  ,

subjname   科目名称  ,

balanorient   余额方向  ,

subjlev 科目级次  ,

endflag 末级标志  ,

into 标准科目表

from wc.BD_accsubj where [PK_GLORGBOOK]='0001A510000000000***' /*输入对应单位账套编号*/

order by [SUBJCODE]

--辅助信息表

SELECT 

FREEVALUEID 辅助核算标识

,b.[BDNAME] 辅助类型

,[VALUECODE] 辅助编码

,[VALUENAME] 辅助名称

into 标准辅助信息表

FROM [wc].[WC].[GL_FREEVALUE]   a

left join  WC.BD_BDINFO b

on a.[CHECKTYPE]=b.[PK_BDINFO]

where  [FREEVALUEID]

in (SELECT  distinct FREEVALUEID

FROM WC.GL_DETAIL where FREEVALUEID is not null  )

order by  b.[BDNAME] ,[VALUECODE]

--科目余额表临时表

SELECT

pk_balance 余额主键  ,

pk_accsubj 科目主键  ,

localdebitamount 本币借发生额  ,

localcreditamount  本币贷发生额  ,

 [SUBJCODE]  科目编码,

 [SUBJNAME]  科目名称

--into a_kmyeb_1 --生成科目余额表临时表

FROM   GL_balance  a

left join  dbo.a_kmb b 

on a.[科目主键]=b.[PK_ACCSUBJ]

left join dbo.a_fzmxb d

on a.[辅助核算标识]=d.FREEVALUEID

where 会计年度=2016  and 会计期间=00   --采集年账套数据

order by  [SUBJCODE]

--科目余额表

SELECT

sum([本币借发生额]) [本币借发生额]

,sum([本币贷发生额]) [本币贷发生额]

,科目编码

,科目名称

into 标准科目余额表

FROM [wc].[dbo].[a_kmyeb_1]

group by

科目编码

,科目名称

--辅助余额表

SELECT 

[会计年度]

辅助核算类型,

辅助核算编码,

辅助核算名称,

[本币借发生额],

[本币贷发生额],

科目编码,

科目名称,

into 标准辅助余额表

FROM [wc].[dbo].[a_kmyeb_1] where [辅助核算标识] is not null

--凭证主表

select

year 会计年度  ,

period  会计期间  ,

no 凭证编码  ,

prepareddate  制单日期  ,

signdate   签字日期  ,

checkeddate   审核日期  ,

tallydate  记账日期  ,

attachment 附单据数  ,

pk_prepared   制单人 ,

pk_checked 审核人 ,

pk_casher  出纳  ,

pk_manager 记账人 ,

voucherkind   凭证类型  ,

totaldebit 借方合计 ,

totalcredit   贷方合计  ,

explanation   摘要  ,

into 标准凭证主表

from wc.GL_VOUCHER

where year=2016 and period<>'00' order by period  --采集年账套数据

--凭证明细表

select

FREEVALUEID 辅助核算标识,

[BDNAME] 辅助核算类型,

[VALUECODE] 辅助核算编码,

[VALUENAME] 辅助核算名称,

pk_detail  分录主键  ,

pk_voucher 凭证主键  ,

pk_accsubj 科目主键  ,

pk_currtype   币种主键  ,

pk_sob  账簿主键  ,

pk_corp 公司编码  ,

detailindex   分录号 ,

assid 辅助核算标识  ,

explanation   摘要内容  ,

price 单价  ,

excrate1   汇率  ,

excrate2   汇率  ,

debitquantity 借方数量  ,

debitamount   原币借发生额  ,

fracdebitamount 辅币借发生额  ,

localdebitamount 本币借发生额  ,

creditquantity  贷方数量  ,

creditamount  原币贷发生额  ,

fraccreditamount 辅币贷发生额  ,

localcreditamount  本币贷发生额  ,    

c.[会计期间],

c.[凭证编码], c.[制单日期],

c.[签字日期],

c.[审核日期],

c.[记账日期],

[SUBJLEV]   科目级次,

[SUBJCODE]  科目编码,

[SUBJNAME]  科目名称

into 标准凭证明细表

FROM WC.GL_DETAIL a

left join  dbo.a_kmb b

on a.pk_accsubj=b.[PK_ACCSUBJ]

left join  dbo.a_pzb c

on a.pk_voucher=c.凭证主键

left join dbo.a_fzmxb d

on a.FREEVALUEID=d.FREEVALUEID

where a.pk_voucher in (SELECT  distinct pk_voucher  FROM [wc].[dbo].[a_pzb])