AO数据采集转换初探方正春元

1、AO数据采集转换初探（方正春元）                                           在利用现场审计实施系统（以下称AO）进行审计时，首要

2、的环节是将被审计单位的财务数据及业务数据进行采集转换并准确导入到AO中，但是由于目前在AO中没有部分财务软件的采集模板，而且软件本身也未提供国标数据接口，致使一些财务数据无法直接转换，已成为制约AO正常使用的“瓶颈”，如果这一“瓶颈”不突破，利用AO开展计算机审计将无从谈起。笔者通过近两年的实践和探索，通过数据库采集的方式采集数据并手工转换，形成AO可直接利用的数据，初步解决了AO数据采集转换的“瓶颈”问题。下面以方正春元财务软件为例，简要介绍数据采集转换的方式方法。一、数据采集    方正春元财务软件的后台数据库为oracle 10g，可以利用ODBC数据源进行

3、数据采集，导出为ACCESS类型数据，然后进行数据转换，按照实际需要转换成财务数据的三张表，并导入到AO中。在采集数据过程中需要系统管理员提供数据库口令和密码。  具体采集步骤：  1、在方正春元软件客户端（已安装oracle 10g客户端工具）配置oracle ODBC数据源。选择:我的电脑-控制面板-管理工具-数据源（ODBC）-系统DSN（选项卡）-添加（按钮）-选择oracle for 10g驱动，在弹出的对话框中配置参数。  主要配置四个参数：（1）数据源名称。可自定，如shenji。（2）数据源描述。描述数据源的基本情况，

4、非必填项。（3）TNS服务器名。从下拉菜单中选择，如果存在多个选项，询问管理员哪个是方正春元的服务名。（4）用户名（user id）。从管理员处获得，如果无法取得用户名，试用system用户名。  2、建立一个ACCESS空数据库。3、数据采集。打开ACCESS库，选择：文件-获取外部数据-导入，在“导入”对话框中的“文件类型”下拉菜单中选择ODBC database数据类型，在弹出窗口中选择“机器数据源”，然后选择已配置的数据源名，如shenji。点击确定，在弹出窗口中输入口令（从管理员处获得，如果用system用户名，默认口令是manager），点击确定，连接oracl

5、e后台数据库。连接成功后，在弹出窗口中可以看到很多表，选择和预算内外有关系的用户名下的所有数据表，然后将所选择数据表导出。  二、方正春元财务软件后台数据结构特点从获取的财务后台数据来看，方正春元数据结构有以下特点：（1）收支分类、往来、预算单位等信息采用辅助核算形式。各单位财务数据初使化方式不一样，后台数据结构也有区别，如有的单位往来是以科目代码形式存储，未采用辅助核算形式。（2）一个数据库存储多年度预算内、外、集中支付及专项账套数据。如凭证明细表中存储多年度、多个账套的凭证，通过年度（set_year）和账套编码(set_code)区分。年度字段值即为会计年度，账套编码值一般预算

6、内为“0001”、预算外为“0002”,如果出现其他账套编码即为其他财政专项账簿。（3）数据表中用到的各类编码分内码（in_code）和外码(disp_code)对照管理。内码是级别码，各级代码长度都为3-3-3-3形式，在后台数据运行时使用；外码的各级代码长度不一样，一般为3-2-2-2形式，方正春元软件前台输入格式为外码。三、数据转换数据转换是将采集到的财务数据库数据导入到sql server 2000中，通过编写sql语句实现的。根据使用需要，一般将辅助核算的形式转化为科目代码形式，这是AO2005的数据采集需要的数据形式，也符合一般手工账簿的核算习惯，同时便于浏览电子账簿。如果不进行科

7、目代码转换导入到AO中，在AO中生成的账套为一级科目流水账，使用不方便。重新转换科目代码的原则：如果生成新科目编码，一定同时转换三张表，即科目表、余额表和凭证表必须重新转换并保持一致，否则数据出错，无法顺利实现账表重建。因各地（市）县数据结构会有差别，所以在转换数据时应灵活运用sql语句，使转换后的数据能够满足审计需要。1、数据字典在采集的数据中有两张非常重要的表，一张是table_manager表（业务表和基础表管理表），存储表的中文名的表，另一张是 field_manager表（业务表和基础数据表的字段管理表），存储各表的字段中文名及相关联的表及字段。这两张表对分析表结构及数据转换非常有帮

8、助。  2、数据转换涉及的方正春元软件后台数据的七张表。表名及字段名见下表：表代码表名字段代码字段名ACCOUNTANT_SUBJECT会计科目表IN_CODE 内码(预算科目编码)Disp_Code预算科目外码DISP_NAME名称（预算科目名称）IS_DEBIT余额方向Set_Code帐套代码SET_YEAR业务年度Enabled是否启用Elem_budget_flag指标业务可用标志Elem_Pay_Flag支付业务指定标志Elem_Plan_Flag计划业务指定标志Start_date启用日期BUDGET_SUBJECT支出功能分类科目表IN_CODE内码DISP_

9、NAME名称SET_YEAR年度BUDGET_SUBJECT_INCOME收入分类科目表IN_CODE内码DISP_NAME名称SET_YEAR年度ENTERPRISE预算单位表IN_CODE内码DISP_NAME名称REMAIN_BUDGET总预算会计账期初数表AS_CODE会计科目Remain_id顺序号BALANCE年初余额SET_YEAR年度Deb_Money借方金额Cre_Money贷方金额En_Code单位分类代码Mb_Code业务处室代码Dp_Code部门代码Cr_Code往来单位代码Clear_account_code清算账户代码SET_CODE账套编码VOUCHER_BUDG

10、ET凭证主表VOUCHER_ID 流水号V_MONTH月V_DAY日VOUCHER_NO凭证号DEFINE_ID凭证类别SET_YEAR年度SET_CODE账套编码VOUCHER_DETAIL_BUDGET凭证明细表VOUCHER_ID流水号V_LINE明细号SORT_LINE分类明细号AS_CODE会计科目SUMMARY摘要DEB_MONEY借方金额CRE_MONEY贷方金额EN_CODE预算单位BS_CODE预算科目IN_BS_CODE预算收入科目SET_YEAR年度SET_CODE账套编码3、数据转换后新生成表名及字段名见下表： 表代码表名字段代码字段名称pz凭证表lsh流水号mxh明细

11、号flmxh分录明细号pzh凭证号kmbm科目编码pzrq凭证日期zy摘要jfje借方金额dfje贷方金额bmbm部门编码bmmc部门名称kmbm科目编码表kmbm科目编码kmmc科目名称yeb余额表kmbm科目编码kmmc科目名称yefx余额方向 1为借，0为贷ncye年初余额bmb预算部门表bmbm部门编码bmmc部门名称pz_mx凭证明细表pz_zb凭证主表  4、将上述表导入到sql server 2000中。注意在导入sql server  2000之前，在Access中将各表前面的用户名和下划线去掉。  5、数据转换过程&

12、#160;   （1）转换预算单位表转换思路：通过设置年度条件，将某年的预算单位编码和预算单位名称筛选出来，生成新的预算单位表。参考语句：  select IN_CODE bmbm,DISP_NAME bmmcinto bmbfrom dbo.ENTERPRISEwhere SET_YEAR='2007'（2）转换凭证表a.转换凭证明细表转换思路：通过转换凭证表，生成新的收入科目、支出科目和往来科目，并在新生成的凭证明细表中保留预算单位编码，同时对年度和账套编码字段设定条件。参考语句：select VOUCHER_ID lsh ,V_LI

13、NE mxh,SORT_LINE flmxh,kmbm=casewhen EN_CODE is not null and BS_CODE is not null and left(AS_CODE,3)='501'then AS_CODE+BS_CODEwhen IN_BS_CODE is not null and left(AS_CODE,3)='401'then AS_CODE+IN_BS_CODEelse AS_CODEend, SUMMARY zy,DEB_MONEY jfje,CRE_MONEY dfje,bmbm=casewhen EN_CODE is

14、 not null and BS_CODE is not nullthen EN_CODEwhen EN_CODE is not null and BS_CODE is nullthen nullelse EN_CODEendinto pz_mxfrom dbo.VOUCHER_DETAIL_BUDGETwhere set_code='0001' and set_year='2007'b.转换凭证主表转换思路：转换凭证主表的同时合并凭证年、月、日三个字段，生成凭证日期字段，同时对年度和账套编码字段设定条件。参考语句：select VOUCHER_ID lsh,c

15、ast(cast(SET_YEAR as varchar(4)+'-'+cast(V_MONTH as varchar(2)+'-'+cast(V_DAY as varchar(2)as datetime) pzrq,VOUCHER_NO pzh,DEFINE_ID pzlbinto pz_zbfrom dbo.VOUCHER_BUDGETwhere set_code='0001' and set_year='2007'c.合并凭证主表和凭证明细表转换思路：将凭证主表和凭证明细表合并为一张凭证表，对于数据量大的单位可以缩短导入AO

16、的时间。参考语句：select a.lsh,b.pzh,b.pzrq,b.pzlb,a.mxh,a.flmxh,a.kmbm,a.zy,a.jfje,a.dfje,a.bmbm,c.bmmcinto pzfrom pz_mx a left join pz_zb b on a.lsh=b.lsh left joinbmb c on a.bmbm=c.bmbmorder by month(b.pzrq),b.pzh,a.mxh,a.flmxh（3）转换科目编码表（kmbm）转换思路：把凭证明细表（pz_mx）中新生成的科目编码及对应的科目名称归入科目编码表中，并生成对应的上一级科目编码和名称。在转

17、换时需要将新生成的凭证明细表（pz_mx）和收入分类科目表（BUDGET_SUBJECT_INCOME）进行表连接，找到新生成的科目编码对应的科目名称。参考语句：a. 转换新生成的收入科目编码表-先转换新生成的末级收入科目编码。select distinct a.kmbm,b.DISP_NAME kmmc,'0' yefxinto kmbm_1from pz_mx a left join dbo.BUDGET_SUBJECT_INCOME b onsubstring(a.kmbm,7,len(a.kmbm)-6)=b.IN_CODEwhere left(a.kmbm,3)=&#

18、39;401' and b.set_year='2007'-逐级生成上一级科目编码。先看科目编码（kmbm） 的最大长度，从最大长度开始逐级向上转换，直到和会计科目表（ACCOUNTANT_SUBJECT）中的一级科目相衔接为止。 -转换末数二级科目编码（假设最大为18位）insert into kmbm_1select distinct left(kmbm,18-3) kmbm,b.DISP_NAME kmmc,'0' yefx from kmbm_1 a left join dbo.BUDGET_SUBJECT_INCOME b onsubstrin

19、g(a.kmbm,7,18-6-3)=b.IN_CODEwhere len(a.kmbm)=18+3-3 and b.set_year='2007'-转换末数三级科目编码insert into kmbm_1select distinct left(kmbm,18-6) kmbm,b.DISP_NAME kmmc,'0' yefx from kmbm_1 a left join dbo.BUDGET_SUBJECT_INCOME b onsubstring(a.kmbm,7,18-6-6)=b.IN_CODEwhere len(a.kmbm)=18+3-6 and

20、 b.set_year='2007'-转换末数四级科目编码insert into kmbm_1select distinct left(kmbm,18-9) kmbm,b.DISP_NAME kmmc,'0' yefx from kmbm_1 a left join dbo.BUDGET_SUBJECT_INCOME b onsubstring(a.kmbm,7,18-6-9)=b.IN_CODEwhere len(a.kmbm)=18+3-9 and b.set_year='2007'支出、往来科目的转换参照转换收入科目的过程，注意将凭证明细表

21、和对应的编码表相关联，如转换功能支出科目时，凭证明细表（pz_mx）和支出功能分类科目表（BUDGET_SUBJECT）进行表连接。各类编码转换完成后，汇总生成科目编码表（kmbm）。 参考语句：select *into kmbm from kmbm_1unionselect *from kmbm_2unionselect *from kmbm_3 （4）转换余额表转换思路：在转换余额表的同时生成余额方向（yefx）字段。参考语句： select kmbm= AS_CODE,BALANCE ncye,yefx=casewhen left(AS_CODE,1) in('1',&#

22、39;5') then '1'when left(AS_CODE,1) in('2','3','4') then '0'endinto yebfrom dbo.REMAIN_BUDGETwhere set_code='0001' and set_year='2007'四、数据校验数据转换后，需要验证数据的完整性和合规性，符合AO要求的数据标准格式才能进行数据导入。1、验证凭证完整性。将借贷方发生额合计与被审计单位纸制表核对是否有误。参考语句：select sum(jfje) 借方发生额合计,sum(dfje) 贷方发生额合计from pz_mx2、验证凭证表(pz)中出现的科目编码是否全部含在科目编码表（kmbm）中。语句执行无结果既为验证正确。参考语句：select a.kmbmf