巧用Access 2007采集用友A++財(cái)政財(cái)務(wù)管理軟件導(dǎo)出XML財(cái)務(wù)數(shù)據(jù)

文/龔澤平

巧用Access 2007采集用友A++財(cái)政財(cái)務(wù)管理軟件導(dǎo)出XML財(cái)務(wù)數(shù)據(jù)

文/龔澤平

2016年8月，曲靖市審計(jì)局在對(duì)某縣縣長(zhǎng)離任進(jìn)行經(jīng)濟(jì)責(zé)任審計(jì)時(shí)，采集到某單位用友A++財(cái)政財(cái)務(wù)管理軟件(V5.2)備份XML財(cái)務(wù)數(shù)據(jù)，采用AO2011數(shù)據(jù)采集模板和SQL數(shù)據(jù)庫(kù)轉(zhuǎn)換等多種方法，始終無(wú)法導(dǎo)入A02011系統(tǒng)。

偶然間，筆者想到既然能用Microsoft Excel 2007電子表格處理XML文件，那么也許Microsoft Access 2007數(shù)據(jù)庫(kù)也能處理XML數(shù)據(jù)，于是試了一下，果然成功，且過(guò)程十分簡(jiǎn)單，現(xiàn)將該方法介紹如下。

一、利用Microsoft Access 2007數(shù)據(jù)導(dǎo)入功能將被審計(jì)單位提供的XML格式財(cái)務(wù)數(shù)據(jù)導(dǎo)入Access，還原成一個(gè)完整的財(cái)務(wù)數(shù)據(jù)庫(kù)

1.在Microsoft Access 2007中新建一個(gè)Access數(shù)據(jù)庫(kù)XXGA.accdb，鼠標(biāo)右鍵點(diǎn)擊右邊“表1：表”→“導(dǎo)入(M)”→“XML文件(M)”，選擇要導(dǎo)入的XML文件“公共檔案類(lèi)”，將其包含的文件全部導(dǎo)入Access數(shù)據(jù)庫(kù)XXGA.accdb。

2.點(diǎn)擊“創(chuàng)建”→“表”新建表，按(一)步驟導(dǎo)入“總賬類(lèi)”全部文件。

二、Microsoft Access 2007數(shù)據(jù)庫(kù)升遷

XML數(shù)據(jù)轉(zhuǎn)換完成后，利用Microsoft Access 2007據(jù)庫(kù)升遷功能，將Microsoft Access 2007數(shù)據(jù)庫(kù)XXGA.accdb升遷到SQL Server 2008 R2中。具體步驟如下：

1.更改安全設(shè)置。點(diǎn)擊“安全警告 已禁用了數(shù)據(jù)庫(kù)的某些內(nèi)容”后的“選項(xiàng)”按鈕，點(diǎn)擊“啟用此內(nèi)容”更改安全設(shè)置。

2.點(diǎn)擊工具欄上的“SQL Server”按鈕，啟動(dòng)數(shù)據(jù)庫(kù)升遷向?qū)А?/p>

3.選擇(或輸入)SQL數(shù)據(jù)庫(kù)服務(wù)器名稱(chēng)、登錄賬戶(hù)、密碼和升遷后的SQL Server數(shù)據(jù)庫(kù)名稱(chēng)(XXGA2014)。

4.按＞＞按鈕，將可用表全部選擇到“導(dǎo)出SQL Server”中，點(diǎn)擊“下一步”完成數(shù)據(jù)升遷。

需要注意的是，在升遷Microsoft Acess 2007數(shù)據(jù)庫(kù)前要關(guān)閉所有已打開(kāi)的數(shù)據(jù)表，否則會(huì)出現(xiàn)“任何數(shù)據(jù)庫(kù)對(duì)象處于打開(kāi)狀態(tài)時(shí)’升遷向?qū)А鶡o(wú)法運(yùn)行。請(qǐng)關(guān)閉打開(kāi)的對(duì)象并重試”如下錯(cuò)誤提示信息：

三、SQL數(shù)據(jù)整理與AO數(shù)據(jù)采集

完成Access數(shù)據(jù)庫(kù)升遷后，即可啟動(dòng)SQL Server 2008 R2，對(duì)升遷后的數(shù)據(jù)庫(kù)XXGA2014進(jìn)行數(shù)據(jù)整理，重點(diǎn)檢查科目表、科目余額表有無(wú)編碼重復(fù)情況，科目余額表各級(jí)科目余額是否齊全，憑證表、余額表借貸是否平衡；以及時(shí)間、金額等字段類(lèi)型是否正確。

科目編碼重復(fù)與各級(jí)科目余額不全，是造成AO賬表重建失敗的主要原因之一。如果各級(jí)科目余額不全，需要按照末級(jí)科目→一級(jí)科目的順序逐級(jí)匯總。有關(guān)數(shù)據(jù)整理語(yǔ)句如下：

(一)科目表

select 科目編號(hào)，科目名稱(chēng)，余額方向 into XXGA2014.dbo.審計(jì)_會(huì)計(jì)科目表 from XXGA2014.dbo.會(huì)計(jì)科目

(二)憑證表

SELECT 會(huì)計(jì)年度，會(huì)計(jì)期間號(hào) as 會(huì)計(jì)期間，left(記賬憑證日期，4)+'-'+substring(記賬憑證日期，5，2)+'-'+right(記賬憑證日期，2) as 憑證日期， case when 記賬憑證類(lèi)型編號(hào)='JZ' then '記賬' end as 憑證類(lèi)型，記賬憑證編號(hào) as 憑證編號(hào)，記賬憑證行號(hào) as 分錄號(hào)，記賬憑證摘要 as 摘要，科目編號(hào)， 輔助項(xiàng)1編號(hào) as 輔助核算代碼，cast(借方本幣金額 as decimal(18，2)) as 借方金額，cast(貸方本幣金額 as decimal(18，2)) as 貸方金額，附件數(shù)，制單人，審核人，記賬人，記賬標(biāo)志，作廢標(biāo)志 into XXGA2014.dbo.審計(jì)_會(huì)計(jì)憑證表 FROM XXGA2014. dbo.記賬憑證 where 記賬標(biāo)志=1 and 作廢標(biāo)志=0 order by 會(huì)計(jì)年度，會(huì)計(jì)期間號(hào)，left(記賬憑證日期，4)+'-'+substring(記賬憑證日期，5，2)+'-'+right(記賬憑證日期，2)

(三)余額表

1.原始數(shù)據(jù)轉(zhuǎn)換

SELECT 會(huì)計(jì)年度，會(huì)計(jì)期間號(hào) as 會(huì)計(jì)期間，科目編號(hào)，case when 期初余額方向='借' then cast(期初本幣余額 as decimal(18，2)) else '0.00' end as 期初借方余額，case when 期初余額方向='貸' then abs(cast(期初本幣余額 as decimal(18，2))) else '0.00' end as 期初貸方余額 into #科目余額 FROM XXGA2014. dbo.科目余額及發(fā)生額 where 會(huì)計(jì)期間號(hào)='1' order by 會(huì)計(jì)年度，會(huì)計(jì)期間號(hào)，科目編號(hào)

2.查看科目編碼長(zhǎng)度及重復(fù)情況

select distinct 會(huì)計(jì)年度，LEN(科目編號(hào)) as 碼長(zhǎng) from #科目余額 group by 會(huì)計(jì)年度，科目編號(hào) order by 會(huì)計(jì)年度，LEN(科目編號(hào)) ---科長(zhǎng)分別 為目6、8、10、12共4級(jí)，無(wú)1級(jí)科目

select distinct 會(huì)計(jì)年度，科目編號(hào)，count(科目編號(hào)) as次數(shù) from #科目余額 group by 會(huì)計(jì)年度，科目編號(hào) having count(科目編號(hào))＞1 order by 會(huì)計(jì)年度，科目編號(hào)

(1)查詢(xún)末級(jí)明細(xì)科目，生成5級(jí)科目余額

SELECT會(huì)計(jì)年度，科目編號(hào)，sum(期初借方余額) as期初借方余額，sum(期初貸方余額) as 期初貸方余額 into XXGA2014.dbo.審計(jì)_科目余額表 FROM #科目余額 group by會(huì)計(jì)年度，科目編號(hào) order by 會(huì)計(jì)年度，科目編號(hào)

(2)匯總5級(jí)科目，生成4級(jí)科目余額

insert into XXGA2014.dbo.審計(jì)_科目余額表 select 會(huì)計(jì)年度，left(科目編號(hào)，10) as 科目編號(hào)，sum(期初借方余額) as 期初借方余額，sum(期初貸方余額) as 期初貸余額 from XXGA2014.dbo.審計(jì)_科目余額表 where len(科目編號(hào))=12 group by 會(huì)計(jì)年度，left(科目編號(hào)，10) order by 會(huì)計(jì)年度，left(科目編號(hào)，10)

(3)匯總4級(jí)科目，生成3級(jí)科目余額

insert into XXGA2014.dbo.審計(jì)_科目余額表 select 會(huì)計(jì)年度，left(科目編號(hào)，8) as 科目編號(hào)，sum(期初借方余額) as 期初借方余額， sum(期初貸方余額) as 期初貸余額 from XXGA2014.dbo.審計(jì)_科目余額表 where len(科目編號(hào))=10 group by 會(huì)計(jì)年度，left(科目編號(hào)，8) order by 會(huì)計(jì)年度，left(科目編號(hào)，8)

(4)匯總3級(jí)科目，生成2級(jí)科目余額

insert into XXGA2014.dbo.審計(jì)_科目余額表 select 會(huì)計(jì)年度，left(科目編號(hào)，6) as 科目編號(hào)，sum(期初借方余額) as 期初借方余額， sum(期初貸方余額) as 期初貸余額 from XXGA2014.dbo.審計(jì)_科目余額表 where len(科目編號(hào))=8 group by 會(huì)計(jì)年度，left(科目編號(hào)，6) order by 會(huì)計(jì)年度，left(科目編號(hào)，6 )

(5)匯總2級(jí)科目，生成1級(jí)科目余額

insert into XXGA2014.dbo.審計(jì)_科目余額表 select 會(huì)計(jì)年度，left(科目編號(hào)，4) as 科目編號(hào)，sum(期初借方余額) as 期初借方余額，sum(期初貸方余額) as 期初貸余額 from XXGA2014.dbo.審計(jì)_科目余額表 where len(科目編號(hào))=6 group by 會(huì)計(jì)年度，left(科目編號(hào)，4) order by 會(huì)計(jì)年度，left(科目編號(hào)，4)

(四)輔助核算信息

由于各單位輔助核算設(shè)置不一致，因此難形成統(tǒng)一的轉(zhuǎn)換腳本。以下腳本僅供參考。

1.輔助信息

SELECT distinct 檔案名稱(chēng) as 輔助類(lèi)別，檔案值編碼 as 輔助代碼，檔案值名稱(chēng) as 輔助名稱(chēng) into XXGA2014.dbo.審計(jì)_輔助信息表 FROM XXGA2014.dbo.自定義檔案項(xiàng) a left join XXGA2014.dbo.自定義檔案值 b on a.檔案編碼=b.檔案編碼order by 檔案值編碼。

2.輔助余額表

SELECT 會(huì)計(jì)年度，會(huì)計(jì)期間號(hào)，科目編號(hào)，輔助項(xiàng)編號(hào)as 輔助代碼，期初余額方向， cast(期初本幣余額 as decimal(18，2)) as期初余額 into 輔助余額表 FROM XXGA2014.dbo.科目余額及發(fā)生額。

3.輔助憑證

SELECT會(huì)計(jì)年度，會(huì)計(jì)期間號(hào)，cast (substring(記賬憑證日期，1，4)+'-' + substring(記賬憑證日期，5，2)+'-'+ substring(記賬憑證日期，7，2) as DATE) as 記賬憑證日期，記賬憑證類(lèi)型編號(hào)，記賬憑證編號(hào)，記賬憑證行號(hào)，科目編號(hào)，記賬憑證摘要，輔助項(xiàng)編號(hào)， cast(借方本幣金額 as decimal(18，2)) as 借方金額， cast(貸方本幣金額as decimal(18，2)) as貸方金額，附件數(shù)，制單人，審核人，記賬人 into 輔助憑證表FROM XXGA2014.dbo.記賬憑證 where 記賬標(biāo)志=1 and 作廢標(biāo)志=0

整理完成后即可利用AO2011系統(tǒng)輔助導(dǎo)入功能將科目表、憑證表、余額表及輔助核算信息導(dǎo)入AO系統(tǒng)進(jìn)行賬表重建，供AO審計(jì)分析。

(作者單位：云南省曲靖羅平縣審計(jì)局)