当前位置:首页 > 交流 > 详情

东软望海HERP V3.1系统财务辅助账采集方法

2017-07-08 10:06:06

某三甲医院于2012年上线了东软望海医院综合运营管理系统(版本为HERP V3.1,后台数据库为sql server),AO中无相应的数据采集模板。近日,在对该院院长经济责任审计过程中,笔者使用手工采集的方式,完整的将辅助账采集到了AO2011中,现将采集方法介绍如下:


一、软件基本情况简介及采集所需的数据表

该软件涵盖了成本核算、财务管理、物流管理、人力资源管理、固定资产管理等多项功能。经过前台分析,该单位共设置了辅助项18个,其中:系统辅助项4个、自定义辅助项14个。

经分析后台数据,要完整采集辅助信息,需要用到以下16张数据表,分别是sys_company(单位信息表)、sys_copy(账套信息表)、sys_check_define (系统辅助项字典)、acct_self_type (自定义辅助项字典)、acct_self_item(自定义辅助信息表)、acct_subj(会计科目表)、acct_ledger(科目余额表)、acct_vouch_type(记账凭证类型字典)、acct_vouch(凭证主表)、acct_vouch_detail(凭证子表)、acct_check_items(凭证辅助明细表)、acct_check_ledger(辅助科目余额表)、sys_dept(部门信息表)、sys_vendor_dict(供应商信息表)、sys_emp(职工信息表)、sys_money_resource(资金来源信息表)。


二、数据采集

(一)数据采集前的准备工作

1.获取单位代码和账套代码信息

由于该软件将多单位、多账套、多年度的财务数据均存放在一个数据库内,因此在数据采集前需提前获取所采集的单位及账套的代码信息,以备后期使用。语句为:

select a.comp_code as 单位代码,b.comp_name as 单位名称,a.copy_code as 账套代码,a.copy_name as 账套名称

from sys_copy a

join sys_company b on a.comp_code=b.comp_code

运行后,如下图一所示,可见该单位设置了三个不同的账套(根据需要,现将单位代码确定为1001,账套代码确定为101)

图一   单位及账套信息列表

2.获取系统辅助项及自定义辅助项编码信息

该软件辅助账由系统与自定义两部分组成,在凭证辅助明细表及辅助余额表的字段中,用“checktype+编码”和“zchecktype+编码”(如checktype5、zchecktype8等)的方式分别表示系统辅助项和自定义辅助项。在编写语句前,需要获取辅助项的编号,便于后期整理中对相应的字段进行汉化。语句为:

(1)查看系统辅助项编码的SQL语句:

select check_id 辅助项编码,check_name 辅助项名称,table_id as 辅助信息表名

from [sys_check_define]

(2)查看自定义辅助项编码的SQL语句:

SELECT TYPE_ID AS 辅助项编码,check_type as 辅助项名称 FROM acct_self_type WHERE COPY_CODE=’101’ AND comp_code =’1001’

运行后,如下图二所示,可知在系统辅助项里,部门辅助信息编码为1,若辅助凭证明细表及辅助余额表中字段名为checktype1,则代表该列为“部门”辅助代码;在自定义辅助项里,药房辅助信息编码为8,若辅助凭证明细表及辅助余额表中字段名为zchecktype8,则代表该列存储的为“药房”辅助代码,以此类推。

 

图二   辅助项信息表

(二)数据表的整理

1.会计科目表的整理

会计科目信息主要存储在acct_subj表中,直接获取并汉化即可,代码如下:

SELECT  [comp_code] as 单位代码,[copy_code] as 账套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目代码,[super_code] as 上级科目代码,,[acct_subj_name] as 科目名称,[acct_subj_name_all] as 科目全称,[subj_level] as 科目级次,[is_last] as 是否末级科目,

[direction] as 科目方向

into a_kmb

FROM [acct_subj]

where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015''

2.科目余额表的整理

    科目余额信息主要存储在acct_ledger表中,正常情况下期初余额以正负来区分借贷方向,但由于该单位部分科目的借方余额为负数,且以1221、1602和1702三个开头的科目期初余额在贷方,为了确保跟软件前台数据一致,笔者自行生成了余额方向。代码如下:

SELECT  [comp_code] as 单位代码,[copy_code] as 账套代码,[acct_year] as 会计年份,[acct_subj_code] as 科目代码,[acct_month] as 会计月份,

       余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then ''贷'' else ''借'' end

      ,期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

into a_kmyeb

  FROM [HERP].[dbo].[acct_ledger]

  where comp_code=''1001'' and copy_code=''101'' and acct_year=''2015'' and acct_month=''01''

3.凭证主表的整理

    科目余额信息主要存储在acct_vouch表中,为了获取凭证类型,在此引入acct_vouch_type表中信息,做链接查询,代码如下:

SELECT a.[vouch_id] as 凭证关联号,a.[comp_code] as 单位代码,a.[copy_code] as 账套代码,a.[acct_year] as 会计年度,a.[acct_month] as 会计月份,a.[vouch_no] 凭证号,cast(a.[vouch_date]  AS DATE)as 凭证日期,a.[vouch_bill_num] AS 附件张数,a.[vouch_type_code] as 凭证类型代码,b.vouch_type_name as 凭证类型名称,a.[operator] as 制单,a.[auditor] as 审核,a.[poster] as 记账,a.[is_check] as 检查标记,a.[is_acc] as 记账标志

into a_pzzb

FROM [HERP].[dbo].[acct_vouch] a

  join acct_vouch_type b on a.vouch_type_code=b.vouch_type_code and a.comp_code=b.comp_code and a.copy_code=b.copy_code

    where a.comp_code=''1001'' and a.copy_code=''101''

  and a.acct_year=''2015''

  order by 5,6

4.凭证子表的整理

    凭证子表信息主要存储在acct_vouch_detail表中,直接取数并汉化即可,代码如下:

select vouch_detail_id as 分录序号, vouch_id as 凭证关联号,comp_code as 单位代码,copy_code as 帐套代码,acct_year as 会计年度,acct_subj_code as 科目代码,vouch_page 凭证当前页数,vouch_row as 凭证行数,summary as 摘要,amt_debit as 借方发生额,amt_credit as 贷方发生额

  into a_pzmxb

  from acct_vouch_detail

  where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015''

5.凭证辅助明细表的整理

凭证辅助明细表信息主要存储在acct_check_items表中,该表对辅助信息以“横排”的方式呈现,以“checktype+编码”和“zchecktype+编码”的方式分别表示系统辅助项和自定义辅助项。根据前面获取系统辅助项及自定义辅助项编码信息,直接对相应的字段进行汉化即可,代码如下:

select acct_check_id as 辅助账序号,vouch_detail_id  as 分录序号,line 行号,comp_code as 单位代码,copy_code as 帐套代码,acct_year as 会计年度,acct_subj_code as 科目代码,summary as 摘要,amt_debit as 借方发生额,amt_credit as 贷方发生额,checktype1 as 部门辅助编码,checktype3 as 供应商辅助编码,checktype4 as 职工辅助编码,checktype10 as 资金来源辅助编,zchecktype1 as 医疗保险辅助编码,zchecktype2 as 合作医疗辅助代码,zchecktype3 as 自费病人辅助编码,zchecktype4 as 离休病人辅助编码,zchecktype5 as 应收往来单位辅助编码,zchecktype6 as 应收内部往来,zchecktype7 as 其他人员辅助编码,zchecktype8 药房辅助编码,zchecktype9 as 工程单位辅助编码,zchecktype10 as 宣传单位辅助编码,zchecktype11 as 应付往来单位辅助编码,zchecktype12 as 应付内部往来辅助编码,zchecktype13 as 列支渠道辅助编码,zchecktype15 as 科教项目辅助编码

into a_fzpzmxb

from acct_check_items where comp_code=''1001'' and copy_code=''101'' and acct_year=''2015'' 

6.辅助余额表的整理

凭证辅助明细表信息主要存储在acct_check_ledger表中,该表对辅助信息以“横排”的方式呈现,以“checktype+编码”和“zchecktype+编码”的方式分别表示系统辅助项和自定义辅助项。根据前面获取系统辅助项及自定义辅助项编码信息,可直接对相应的字段进行汉化,余额方向的设置参照上述科目余额表的方法即可。

注:AO虽提供了“单表横排”的导入方式,由于未知原因导致该表无法插入,后将表结构该为“单表竖排”的方式导入成功,客观上增加了工作量。代码如下:

SELECT  [comp_code] as 单位代码,[copy_code] as 帐套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目编码,[acct_month] as 会计月份,''部门'' as 辅助核算类型,''部门'' as 辅助核算名称,[checktype1] as 辅助核算编码,

 余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%'' then ''贷'' else ''借'' end,

期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

into a_fzyeb

FROM [acct_check_ledger] where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015'' and acct_month=''01''

  and [checktype1]<>''''

union all

    SELECT  [comp_code] as 单位代码,[copy_code] as 帐套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目编码,[acct_month] as 会计月份,

''供应商'' as 辅助核算类型,''供应商'' as 辅助核算名称,[checktype3] as 辅助核算编码,余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then ''贷'' else ''借'' end

 ,期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

FROM [HERP].[dbo].[acct_check_ledger] where comp_code=''1001'' and copy_code=''101''

and acct_year=''2015'' and acct_month=''01''

and [checktype3]<>''''

union all

   SELECT  [comp_code] as 单位代码,[copy_code] as 帐套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目编码,[acct_month] as 会计月份,

''职工'' as 辅助核算类型,''职工'' as 辅助核算名称,[checktype4] as 辅助核算编码,

  余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then ''贷'' else ''借'' end

      ,期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

  FROM [HERP].[dbo].[acct_check_ledger] where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015'' and acct_month=''01''

  and [checktype4]<>''''

union all

   SELECT  [comp_code] as 单位代码,[copy_code] as 帐套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目编码,[acct_month] as 会计月份,

''资金来源'' as 辅助核算类型,''资金来源'' as 辅助核算名称,[checktype10] as 辅助核算编码,

  余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then ''贷'' else ''借'' end

      ,期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

    FROM [HERP].[dbo].[acct_check_ledger] where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015'' and acct_month=''01''

  and [checktype10]<>''''

union all

   SELECT  [comp_code] as 单位代码,[copy_code] as 帐套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目编码,[acct_month] as 会计月份,

''医疗保险'' as 辅助核算类型,''医疗保险'' as 辅助核算名称,[zchecktype1] as 辅助核算编码,

  余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then ''贷'' else ''借'' end

      ,期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

    FROM [HERP].[dbo].[acct_check_ledger] where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015'' and acct_month=''01''

  and [zchecktype1]<>''''

union all

   SELECT  [comp_code] as 单位代码,[copy_code] as 帐套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目编码,[acct_month] as 会计月份,

''合作医疗'' as 辅助核算类型,''合作医疗'' as 辅助核算名称,[zchecktype2] as 辅助核算编码,

  余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then ''贷'' else ''借'' end

,期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

  FROM [HERP].[dbo].[acct_check_ledger] where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015'' and acct_month=''01''

  and [zchecktype2]<>''''

union all

   SELECT  [comp_code] as 单位代码,[copy_code] as 帐套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目编码,[acct_month] as 会计月份,

''自费病人'' as 辅助核算类型,''自费病人'' as 辅助核算名称,[zchecktype3] as 辅助核算编码,

  余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then ''贷'' else ''借'' end

,期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

  FROM [HERP].[dbo].[acct_check_ledger] where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015'' and acct_month=''01''

  and [zchecktype3]<>''''

union all

   SELECT  [comp_code] as 单位代码,[copy_code] as 帐套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目编码,[acct_month] as 会计月份,

''离休病人'' as 辅助核算类型,''离休病人'' as 辅助核算名称,[zchecktype4] as 辅助核算编码,

  余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then ''贷'' else ''借'' end

,期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

  FROM [HERP].[dbo].[acct_check_ledger] where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015'' and acct_month=''01''

  and [zchecktype4]<>''''

union all

   SELECT  [comp_code] as 单位代码,[copy_code] as 帐套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目编码,[acct_month] as 会计月份,

''应收往来单位'' as 辅助核算类型,''应收往来单位'' as 辅助核算名称,[zchecktype5] as 辅助核算编码,

  余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then ''贷'' else ''借'' end

,期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

  FROM [HERP].[dbo].[acct_check_ledger] where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015'' and acct_month=''01''

  and [zchecktype5]<>''''

union all

   SELECT  [comp_code] as 单位代码,[copy_code] as 帐套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目编码,[acct_month] as 会计月份,

''应收内部往来'' as 辅助核算类型,''应收内部往来'' as 辅助核算名称,[zchecktype6] as 辅助核算编码,

  余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then ''贷'' else ''借'' end

      ,期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

  FROM [HERP].[dbo].[acct_check_ledger] where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015'' and acct_month=''01''

  and [zchecktype6]<>''''

union all

   SELECT  [comp_code] as 单位代码,[copy_code] as 帐套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目编码,[acct_month] as 会计月份,

''其他人员'' as 辅助核算类型,''其他人员'' as 辅助核算名称,[zchecktype7] as 辅助核算编码,

  余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then ''贷'' else ''借'' end

,期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

  FROM [HERP].[dbo].[acct_check_ledger] where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015'' and acct_month=''01''

  and [zchecktype7]<>''''

union all

   SELECT  [comp_code] as 单位代码,[copy_code] as 帐套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目编码,[acct_month] as 会计月份,

''药房'' as 辅助核算类型,''药房'' as 辅助核算名称,[zchecktype8] as 辅助核算编码,

  余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then ''贷'' else ''借'' end

,期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

  FROM [HERP].[dbo].[acct_check_ledger] where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015'' and acct_month=''01''

  and [zchecktype8]<>''''

union all

   SELECT  [comp_code] as 单位代码,[copy_code] as 帐套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目编码,[acct_month] as 会计月份,

''工程单位'' as 辅助核算类型,''工程单位'' as 辅助核算名称,[zchecktype9] as 辅助核算编码,

  余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then ''贷'' else ''借'' end

,期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

  FROM [HERP].[dbo].[acct_check_ledger] where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015'' and acct_month=''01''

  and [zchecktype9]<>''''

union all

   SELECT  [comp_code] as 单位代码,[copy_code] as 帐套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目编码,[acct_month] as 会计月份,

''宣传单位'' as 辅助核算类型,''宣传单位'' as 辅助核算名称,[zchecktype10] as 辅助核算编码,

  余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then ''贷'' else ''借'' end

,期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

  FROM [HERP].[dbo].[acct_check_ledger] where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015'' and acct_month=''01''

  and [zchecktype10]<>''''

union all

   SELECT  [comp_code] as 单位代码,[copy_code] as 帐套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目编码,[acct_month] as 会计月份,

''应付往来单位'' as 辅助核算类型,''应付往来单位'' as 辅助核算名称,[zchecktype11] as 辅助核算编码,

  余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then ''贷'' else ''借'' end

,期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

  FROM [HERP].[dbo].[acct_check_ledger] where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015'' and acct_month=''01''

  and [zchecktype11]<>''''

union all

   SELECT  [comp_code] as 单位代码,[copy_code] as 帐套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目编码,[acct_month] as 会计月份,

''应付内部往来'' as 辅助核算类型,''应付内部往来'' as 辅助核算名称,[zchecktype12] as 辅助核算编码,

  余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then ''贷'' else ''借'' end

,期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

  FROM [HERP].[dbo].[acct_check_ledger] where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015'' and acct_month=''01''

  and [zchecktype12]<>''''

union all

   SELECT  [comp_code] as 单位代码,[copy_code] as 帐套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目编码,[acct_month] as 会计月份,

''列支渠道'' as 辅助核算类型,''列支渠道'' as 辅助核算名称,[zchecktype13] as 辅助核算编码,

  余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then ''贷'' else ''借'' end

,期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

  FROM [HERP].[dbo].[acct_check_ledger] where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015'' and acct_month=''01''

  and [zchecktype13]<>''''

union all

   SELECT  [comp_code] as 单位代码,[copy_code] as 帐套代码,[acct_year] as 会计年度,[acct_subj_code] as 科目编码,[acct_month] as 会计月份,

''科教项目'' as 辅助核算类型,''科教项目'' as 辅助核算名称,[zchecktype15] as 辅助核算编码,

  余额方向=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then ''贷'' else ''借'' end

      ,期初余额=case when left(acct_subj_code,1) in (''2'',''3'',''4'') or acct_subj_code like ''1221%'' or acct_subj_code like ''1602%'' or acct_subj_code like ''1702%''  then -[bal_os]  else [bal_os]  end

  FROM [HERP].[dbo].[acct_check_ledger] where comp_code=''1001'' and copy_code=''101''

  and acct_year=''2015'' and acct_month=''01''

  and [zchecktype15]<>''''

7. 辅助信息表的整理

辅助信息存储在多个表中,其中:自定义辅助信息存储在一张表里,可以直接提取;系统辅助信息按照不同的辅助项单独进行存放,根据上述图二系统辅助项信息表中的辅助信息表名逐个进行提取即可,代码如下:

select b.check_type as 辅助类型名称,a.item_code as 辅助编码,a.item_name as 辅助名称,'''' as 辅助核算全称,'''' as 辅助核算级次,'''' as 上级辅助编码

into a_fzxxb

from acct_self_item a

  left join acct_self_type b on a.comp_code=b.comp_code and a.copy_code=b.copy_code  and a.[type_id]=b.[type_id]

where a.comp_code=''1001'' and a.copy_code=''101''

union all

select ''部门'' as 辅助类型名称,dept_code as 辅助编码,dept_name  as 辅助名称,dept_name_all  as 辅助核算全称,dept_level  as 辅助核算级次,super_code  as 上级辅助编码

from sys_dept

where comp_code=''1001''

union all

select ''供应商'' as 辅助类型名称,ven_code as 辅助编码,ven_name as 辅助名称,'''' as 辅助核算全称,'''' as 辅助核算级次 ,'''' as 上级辅助编码

from sys_vendor_dict

where comp_code like ''1001''

union all

select ''职工'' as 辅助类型名称,emp_code as 辅助编码,emp_name as 辅助名称,'''' as 辅助核算全称,'''' as 辅助核算级次 ,'''' as 上级辅助编码

from sys_emp

where comp_code  like ''1001''

union all

select ''资金来源'' as 辅助类型名称,fund_source_code as 辅助编码,fund_source_name as 辅助名称,'''' as 辅助核算全称,'''' as 辅助核算级次 ,'''' as 上级辅助编码

from sys_money_resource  

(三)数据采集与账表重建

使用AO2011的【采集转换】-【财务数据】-【财务软件数据库数据】方法将上述整理生成的7张数据表带入AO中,采取【辅助导入】的方法,按照提示逐项导入,直至账表重建成功即可。


三、总结

对此类自定义辅助项较多的软件进行财务数据采集整理时,需找到存储软件相关参数的信息表,再逐一进行分析甄别,最后按照软件使用者的设置进行逐一汉化,方能全面的整理出一套包含所有辅助信息的财务账套。

(湖北省十堰市审计局  邹  彪)


微信公众号

中国内部审计协会
微信公众号

顶部
内部审计统计调查填报系统使用说明
CIA考试报名
CIA年检
CIA在线学习系统
购买网络直播课