AO數(shù)據(jù)采集轉(zhuǎn)換初探方正春元

1、AO數(shù)據(jù)采集轉(zhuǎn)換初探（方正春元）                                           在利用現(xiàn)場(chǎng)審計(jì)實(shí)施系統(tǒng)（以下稱(chēng)AO）進(jìn)行審計(jì)時(shí)，首要

2、的環(huán)節(jié)是將被審計(jì)單位的財(cái)務(wù)數(shù)據(jù)及業(yè)務(wù)數(shù)據(jù)進(jìn)行采集轉(zhuǎn)換并準(zhǔn)確導(dǎo)入到AO中，但是由于目前在AO中沒(méi)有部分財(cái)務(wù)軟件的采集模板，而且軟件本身也未提供國(guó)標(biāo)數(shù)據(jù)接口，致使一些財(cái)務(wù)數(shù)據(jù)無(wú)法直接轉(zhuǎn)換，已成為制約AO正常使用的“瓶頸”，如果這一“瓶頸”不突破，利用AO開(kāi)展計(jì)算機(jī)審計(jì)將無(wú)從談起。筆者通過(guò)近兩年的實(shí)踐和探索，通過(guò)數(shù)據(jù)庫(kù)采集的方式采集數(shù)據(jù)并手工轉(zhuǎn)換，形成AO可直接利用的數(shù)據(jù)，初步解決了AO數(shù)據(jù)采集轉(zhuǎn)換的“瓶頸”問(wèn)題。下面以方正春元財(cái)務(wù)軟件為例，簡(jiǎn)要介紹數(shù)據(jù)采集轉(zhuǎn)換的方式方法。一、數(shù)據(jù)采集    方正春元財(cái)務(wù)軟件的后臺(tái)數(shù)據(jù)庫(kù)為oracle 10g，可以利用ODBC數(shù)據(jù)源進(jìn)行

3、數(shù)據(jù)采集，導(dǎo)出為ACCESS類(lèi)型數(shù)據(jù)，然后進(jìn)行數(shù)據(jù)轉(zhuǎn)換，按照實(shí)際需要轉(zhuǎn)換成財(cái)務(wù)數(shù)據(jù)的三張表，并導(dǎo)入到AO中。在采集數(shù)據(jù)過(guò)程中需要系統(tǒng)管理員提供數(shù)據(jù)庫(kù)口令和密碼。  具體采集步驟：  1、在方正春元軟件客戶(hù)端（已安裝oracle 10g客戶(hù)端工具）配置oracle ODBC數(shù)據(jù)源。選擇:我的電腦-控制面板-管理工具-數(shù)據(jù)源（ODBC）-系統(tǒng)DSN（選項(xiàng)卡）-添加（按鈕）-選擇oracle for 10g驅(qū)動(dòng)，在彈出的對(duì)話(huà)框中配置參數(shù)。  主要配置四個(gè)參數(shù)：（1）數(shù)據(jù)源名稱(chēng)。可自定，如shenji。（2）數(shù)據(jù)源描述。描述數(shù)據(jù)源的基本情況，

4、非必填項(xiàng)。（3）TNS服務(wù)器名。從下拉菜單中選擇，如果存在多個(gè)選項(xiàng)，詢(xún)問(wèn)管理員哪個(gè)是方正春元的服務(wù)名。（4）用戶(hù)名（user id）。從管理員處獲得，如果無(wú)法取得用戶(hù)名，試用system用戶(hù)名。  2、建立一個(gè)ACCESS空數(shù)據(jù)庫(kù)。3、數(shù)據(jù)采集。打開(kāi)ACCESS庫(kù)，選擇：文件-獲取外部數(shù)據(jù)-導(dǎo)入，在“導(dǎo)入”對(duì)話(huà)框中的“文件類(lèi)型”下拉菜單中選擇ODBC database數(shù)據(jù)類(lèi)型，在彈出窗口中選擇“機(jī)器數(shù)據(jù)源”，然后選擇已配置的數(shù)據(jù)源名，如shenji。點(diǎn)擊確定，在彈出窗口中輸入口令（從管理員處獲得，如果用system用戶(hù)名，默認(rèn)口令是manager），點(diǎn)擊確定，連接oracl

5、e后臺(tái)數(shù)據(jù)庫(kù)。連接成功后，在彈出窗口中可以看到很多表，選擇和預(yù)算內(nèi)外有關(guān)系的用戶(hù)名下的所有數(shù)據(jù)表，然后將所選擇數(shù)據(jù)表導(dǎo)出。  二、方正春元財(cái)務(wù)軟件后臺(tái)數(shù)據(jù)結(jié)構(gòu)特點(diǎn)從獲取的財(cái)務(wù)后臺(tái)數(shù)據(jù)來(lái)看，方正春元數(shù)據(jù)結(jié)構(gòu)有以下特點(diǎn)：（1）收支分類(lèi)、往來(lái)、預(yù)算單位等信息采用輔助核算形式。各單位財(cái)務(wù)數(shù)據(jù)初使化方式不一樣，后臺(tái)數(shù)據(jù)結(jié)構(gòu)也有區(qū)別，如有的單位往來(lái)是以科目代碼形式存儲(chǔ)，未采用輔助核算形式。（2）一個(gè)數(shù)據(jù)庫(kù)存儲(chǔ)多年度預(yù)算內(nèi)、外、集中支付及專(zhuān)項(xiàng)賬套數(shù)據(jù)。如憑證明細(xì)表中存儲(chǔ)多年度、多個(gè)賬套的憑證，通過(guò)年度（set_year）和賬套編碼(set_code)區(qū)分。年度字段值即為會(huì)計(jì)年度，賬套編碼值一般預(yù)算

6、內(nèi)為“0001”、預(yù)算外為“0002”,如果出現(xiàn)其他賬套編碼即為其他財(cái)政專(zhuān)項(xiàng)賬簿。（3）數(shù)據(jù)表中用到的各類(lèi)編碼分內(nèi)碼（in_code）和外碼(disp_code)對(duì)照管理。內(nèi)碼是級(jí)別碼，各級(jí)代碼長(zhǎng)度都為3-3-3-3形式，在后臺(tái)數(shù)據(jù)運(yùn)行時(shí)使用；外碼的各級(jí)代碼長(zhǎng)度不一樣，一般為3-2-2-2形式，方正春元軟件前臺(tái)輸入格式為外碼。三、數(shù)據(jù)轉(zhuǎn)換數(shù)據(jù)轉(zhuǎn)換是將采集到的財(cái)務(wù)數(shù)據(jù)庫(kù)數(shù)據(jù)導(dǎo)入到sql server 2000中，通過(guò)編寫(xiě)sql語(yǔ)句實(shí)現(xiàn)的。根據(jù)使用需要，一般將輔助核算的形式轉(zhuǎn)化為科目代碼形式，這是AO2005的數(shù)據(jù)采集需要的數(shù)據(jù)形式，也符合一般手工賬簿的核算習(xí)慣，同時(shí)便于瀏覽電子賬簿。如果不進(jìn)行科

7、目代碼轉(zhuǎn)換導(dǎo)入到AO中，在AO中生成的賬套為一級(jí)科目流水賬，使用不方便。重新轉(zhuǎn)換科目代碼的原則：如果生成新科目編碼，一定同時(shí)轉(zhuǎn)換三張表，即科目表、余額表和憑證表必須重新轉(zhuǎn)換并保持一致，否則數(shù)據(jù)出錯(cuò)，無(wú)法順利實(shí)現(xiàn)賬表重建。因各地（市）縣數(shù)據(jù)結(jié)構(gòu)會(huì)有差別，所以在轉(zhuǎn)換數(shù)據(jù)時(shí)應(yīng)靈活運(yùn)用sql語(yǔ)句，使轉(zhuǎn)換后的數(shù)據(jù)能夠滿(mǎn)足審計(jì)需要。1、數(shù)據(jù)字典在采集的數(shù)據(jù)中有兩張非常重要的表，一張是table_manager表（業(yè)務(wù)表和基礎(chǔ)表管理表），存儲(chǔ)表的中文名的表，另一張是 field_manager表（業(yè)務(wù)表和基礎(chǔ)數(shù)據(jù)表的字段管理表），存儲(chǔ)各表的字段中文名及相關(guān)聯(lián)的表及字段。這兩張表對(duì)分析表結(jié)構(gòu)及數(shù)據(jù)轉(zhuǎn)換非常有幫

8、助。  2、數(shù)據(jù)轉(zhuǎn)換涉及的方正春元軟件后臺(tái)數(shù)據(jù)的七張表。表名及字段名見(jiàn)下表：表代碼表名字段代碼字段名ACCOUNTANT_SUBJECT會(huì)計(jì)科目表IN_CODE 內(nèi)碼(預(yù)算科目編碼)Disp_Code預(yù)算科目外碼DISP_NAME名稱(chēng)（預(yù)算科目名稱(chēng)）IS_DEBIT余額方向Set_Code帳套代碼SET_YEAR業(yè)務(wù)年度Enabled是否啟用Elem_budget_flag指標(biāo)業(yè)務(wù)可用標(biāo)志Elem_Pay_Flag支付業(yè)務(wù)指定標(biāo)志Elem_Plan_Flag計(jì)劃業(yè)務(wù)指定標(biāo)志Start_date啟用日期BUDGET_SUBJECT支出功能分類(lèi)科目表IN_CODE內(nèi)碼DISP_

9、NAME名稱(chēng)SET_YEAR年度BUDGET_SUBJECT_INCOME收入分類(lèi)科目表IN_CODE內(nèi)碼DISP_NAME名稱(chēng)SET_YEAR年度ENTERPRISE預(yù)算單位表IN_CODE內(nèi)碼DISP_NAME名稱(chēng)REMAIN_BUDGET總預(yù)算會(huì)計(jì)賬期初數(shù)表AS_CODE會(huì)計(jì)科目Remain_id順序號(hào)BALANCE年初余額SET_YEAR年度Deb_Money借方金額Cre_Money貸方金額En_Code單位分類(lèi)代碼Mb_Code業(yè)務(wù)處室代碼Dp_Code部門(mén)代碼Cr_Code往來(lái)單位代碼Clear_account_code清算賬戶(hù)代碼SET_CODE賬套編碼VOUCHER_BUDG

10、ET憑證主表VOUCHER_ID 流水號(hào)V_MONTH月V_DAY日VOUCHER_NO憑證號(hào)DEFINE_ID憑證類(lèi)別SET_YEAR年度SET_CODE賬套編碼VOUCHER_DETAIL_BUDGET憑證明細(xì)表VOUCHER_ID流水號(hào)V_LINE明細(xì)號(hào)SORT_LINE分類(lèi)明細(xì)號(hào)AS_CODE會(huì)計(jì)科目SUMMARY摘要DEB_MONEY借方金額CRE_MONEY貸方金額EN_CODE預(yù)算單位BS_CODE預(yù)算科目IN_BS_CODE預(yù)算收入科目SET_YEAR年度SET_CODE賬套編碼3、數(shù)據(jù)轉(zhuǎn)換后新生成表名及字段名見(jiàn)下表： 表代碼表名字段代碼字段名稱(chēng)pz憑證表lsh流水號(hào)mxh明細(xì)

11、號(hào)flmxh分錄明細(xì)號(hào)pzh憑證號(hào)kmbm科目編碼pzrq憑證日期zy摘要jfje借方金額dfje貸方金額bmbm部門(mén)編碼bmmc部門(mén)名稱(chēng)kmbm科目編碼表kmbm科目編碼kmmc科目名稱(chēng)yeb余額表kmbm科目編碼kmmc科目名稱(chēng)yefx余額方向 1為借，0為貸ncye年初余額bmb預(yù)算部門(mén)表bmbm部門(mén)編碼bmmc部門(mén)名稱(chēng)pz_mx憑證明細(xì)表pz_zb憑證主表  4、將上述表導(dǎo)入到sql server 2000中。注意在導(dǎo)入sql server  2000之前，在Access中將各表前面的用戶(hù)名和下劃線(xiàn)去掉。  5、數(shù)據(jù)轉(zhuǎn)換過(guò)程&

12、#160;   （1）轉(zhuǎn)換預(yù)算單位表轉(zhuǎn)換思路：通過(guò)設(shè)置年度條件，將某年的預(yù)算單位編碼和預(yù)算單位名稱(chēng)篩選出來(lái)，生成新的預(yù)算單位表。參考語(yǔ)句：  select IN_CODE bmbm,DISP_NAME bmmcinto bmbfrom dbo.ENTERPRISEwhere SET_YEAR='2007'（2）轉(zhuǎn)換憑證表a.轉(zhuǎn)換憑證明細(xì)表轉(zhuǎn)換思路：通過(guò)轉(zhuǎn)換憑證表，生成新的收入科目、支出科目和往來(lái)科目，并在新生成的憑證明細(xì)表中保留預(yù)算單位編碼，同時(shí)對(duì)年度和賬套編碼字段設(shè)定條件。參考語(yǔ)句：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.轉(zhuǎn)換憑證主表轉(zhuǎn)換思路：轉(zhuǎn)換憑證主表的同時(shí)合并憑證年、月、日三個(gè)字段，生成憑證日期字段，同時(shí)對(duì)年度和賬套編碼字段設(shè)定條件。參考語(yǔ)句：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.合并憑證主表和憑證明細(xì)表轉(zhuǎn)換思路：將憑證主表和憑證明細(xì)表合并為一張憑證表，對(duì)于數(shù)據(jù)量大的單位可以縮短導(dǎo)入AO

16、的時(shí)間。參考語(yǔ)句：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）轉(zhuǎn)換科目編碼表（kmbm）轉(zhuǎn)換思路：把憑證明細(xì)表（pz_mx）中新生成的科目編碼及對(duì)應(yīng)的科目名稱(chēng)歸入科目編碼表中，并生成對(duì)應(yīng)的上一級(jí)科目編碼和名稱(chēng)。在轉(zhuǎn)

17、換時(shí)需要將新生成的憑證明細(xì)表（pz_mx）和收入分類(lèi)科目表（BUDGET_SUBJECT_INCOME）進(jìn)行表連接，找到新生成的科目編碼對(duì)應(yīng)的科目名稱(chēng)。參考語(yǔ)句：a. 轉(zhuǎn)換新生成的收入科目編碼表-先轉(zhuǎn)換新生成的末級(jí)收入科目編碼。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'-逐級(jí)生成上一級(jí)科目編碼。先看科目編碼（kmbm） 的最大長(zhǎng)度，從最大長(zhǎng)度開(kāi)始逐級(jí)向上轉(zhuǎn)換，直到和會(huì)計(jì)科目表（ACCOUNTANT_SUBJECT）中的一級(jí)科目相銜接為止。 -轉(zhuǎn)換末數(shù)二級(jí)科目編碼（假設(shè)最大為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'-轉(zhuǎn)換末數(shù)三級(jí)科目編碼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'-轉(zhuǎn)換末數(shù)四級(jí)科目編碼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'支出、往來(lái)科目的轉(zhuǎn)換參照轉(zhuǎn)換收入科目的過(guò)程，注意將憑證明細(xì)表

21、和對(duì)應(yīng)的編碼表相關(guān)聯(lián)，如轉(zhuǎn)換功能支出科目時(shí)，憑證明細(xì)表（pz_mx）和支出功能分類(lèi)科目表（BUDGET_SUBJECT）進(jìn)行表連接。各類(lèi)編碼轉(zhuǎn)換完成后，匯總生成科目編碼表（kmbm）。 參考語(yǔ)句：select *into kmbm from kmbm_1unionselect *from kmbm_2unionselect *from kmbm_3 （4）轉(zhuǎn)換余額表轉(zhuǎn)換思路：在轉(zhuǎn)換余額表的同時(shí)生成余額方向（yefx）字段。參考語(yǔ)句： 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'四、數(shù)據(jù)校驗(yàn)數(shù)據(jù)轉(zhuǎn)換后，需要驗(yàn)證數(shù)據(jù)的完整性和合規(guī)性，符合AO要求的數(shù)據(jù)標(biāo)準(zhǔn)格式才能進(jìn)行數(shù)據(jù)導(dǎo)入。1、驗(yàn)證憑證完整性。將借貸方發(fā)生額合計(jì)與被審計(jì)單位紙制表核對(duì)是否有誤。參考語(yǔ)句：select sum(jfje) 借方發(fā)生額合計(jì),sum(dfje) 貸方發(fā)生額合計(jì)from pz_mx2、驗(yàn)證憑證表(pz)中出現(xiàn)的科目編碼是否全部含在科目編碼表（kmbm）中。語(yǔ)句執(zhí)行無(wú)結(jié)果既為驗(yàn)證正確。參考語(yǔ)句：select a.kmbmf