1、G9-综合计划财务管理系统 采集方法恩施州咸丰县财政集中核算 2013 年 5 月前采用的是方正春园财务软件，2013 年 5 月后数据转让 G9-综合计划财务管理系统，后台采用的是 sql-server 2008 r2。AO2011系统中没有相关的采集模板，需手工采集转换，下面介绍下采集转换方法：一、采集方法：采集时要采集数据库原始文件，不能采集数据库备份文件，采集数据库原始文件时候会存在文件正在使用无法拷贝的情况，应先停止 sql-server 服务1、停止 sql-server 服务我的电脑点击右键-管理点击服务和应用程序-服务找到 sql server（sqlexpress）右键停止服
2、务（注意最好在下班时间系统未运行业务的时候进行-文件拷贝结束后要启动服务以免影响第二天办理业务）2、找到数据文件保存位置进行拷贝与当地信息中心人员沟通，查到后台数据库名（一般为 gdwzw2013）-点击右键属性-文件-路径（此截图为我在本地转换后的路径，实际拷贝时已具体路径为准）将两个数据库原始文件采集过来3、将采集的数据库原始文件附加完成采集过程2、转换导入 AO20111、查看-对象资源管理器详细信息-打开行计数降序排列-找出对应表2、生成中间表生成会计科目表（a_kmb）select SET_Code as 帐套号, IN_CODE as 科目编码, DISP_NAME as 科目名称
3、 ,IS_DEBIT as 科目方向 into a_kmb from dbo.ACCOUNTANT_SUBJECT where SET_Code like SET_Code order by IN_CODE生成科目余额表Select SET_Code as 帐套号,convert(varchar(10),SET_YEAR) as 会计年度, AS_CODE as 科目编码 ,SUM(BALANCE) as 科目余额, 余额方向=case when left(AS_CODE,1)=1 OR left(AS_CODE,1)=5 then 1 else 0 end into a_yeb from d
4、bo.REMAIN where SET_Code like SET_Code group by SET_Code, convert(varchar(10), SET_YEAR) , AS_CODEunion allselect SET_Code as 帐套号 ,convert(varchar(10),SET_YEAR) as 会计年度, substring(AS_CODE,1,3) as 科目编码, SUM(BALANCE) as 科目余额, 余额方向=case when left(substring(AS_CODE,1,3),1)=1 OR left(substring(AS_CODE,1,
5、3),1)=5 then 1 else 0 end from dbo.REMAIN where SET_Code like SET_Code and len(AS_CODE)=6 group by SET_Code, convert(varchar(10),SET_YEAR) , substring(AS_CODE,1,3)生成辅助科目余额表Select SET_Code as 帐套号,convert(varchar(10),SET_YEAR) as 会计年度, AS_CODE as 科目编码, 往来分类 as 辅助核算类型, 往来 as 辅助名称, CR_CODE as 辅助核算编码, BA
6、LANCE as 辅助核算余额,余额方向=case when left(AS_CODE,1)=1 OR left(AS_CODE,1)=5 then 1 else 0 end into a_fzyeb from dbo.REMAIN where SET_Code like SET_Code and CR_CODE is not null生成凭证主表Select a.SET_Code 帐套号,convert(varchar(10),a.VOUCHER_ID) as 源凭证关联号,convert(varchar(10),a.VOUCHER_NO) as 凭证号, convert(varchar(1
7、0),a.DEFINE_ID) as 凭证类型 , convert(char(4), a.SET_YEAR) as 会计年度,convert(char(2),a.V_MONTH) as 会计月份,convert(char(2), a.V_DAY) as 会计日,a.BILLS as 附件数, a.MAKER_CODE as 制单人,a.AUDITOR_CODE as 审核人,a.KEEPER_CODE as 记账人,a.MANAGER_CODE as 会计主管,a.STATUS as 凭证状态,convert(varchar(10),b.V_LINE ) as 行号, convert(varc
8、har(10),b.SORT_LINE) as 顺序号,b.SUMMARY as 摘要,b.AS_CODE as 科目编码, b.DEB_MONEY as 借方发生额, b.CRE_MONEY as 贷方发生额,b.dataflag as 删除标志 into a_pzb from dbo.VOUCHER as a inner join dbo.VOUCHER_DETAIL as b on a.SET_Code=b.SET_Code and a.VOUCHER_ID=b.VOUCHER_ID where a.SET_Code like SET_Code生成凭证辅助明细表Select a.SET_
9、Code 帐套号,convert(varchar(10),a.VOUCHER_ID) as 源凭证关联号,convert(varchar(10),a.VOUCHER_NO) as 凭证号,convert(varchar(10),a.DEFINE_ID) as 凭证类型 , convert(char(4), a.SET_YEAR) as 会计年度,convert(char(2),a.V_MONTH) as 会计月份,convert(char(2), a.V_DAY) as 会计日,a.BILLS as 附件数, a.MAKER_CODE as 制单人, a.AUDITOR_CODE as 审核人
10、,a.KEEPER_CODE as 记账人,a.MANAGER_CODE as 会计主管,a.STATUS as 凭证状态,convert(varchar(10),b.V_LINE ) as 行号,convert(varchar(10),b.SORT_LINE) as 顺序号,b.SUMMARY as 摘要,b.AS_CODE as 科目编码,b.bS_CODE as 功能分类编码, b.BSI_CODE as 经济分类编码,b.CR_CODE as 往来分类编码,b.OTH_CODE as 自定义分类编码,b.DEB_MONEY as 借方发生额,b.CRE_MONEY as 贷方发生额,b
11、.dataflag as 删除标志 into a_fzpzb from dbo.VOUCHER as a inner join dbo.VOUCHER_DETAIL as b on a.SET_Code=b.SET_Code and a.VOUCHER_ID=b.VOUCHER_IDwhere a.SET_Code like SET_Code生成辅助信息表select 经济分类 as 辅助核算类型,IN_CODE as 辅助核算编码, DISP_NAME as 辅助核算名称 into a_fzxxb from dbo.BUDGET_SUBJECT_ITEM where SET_Code lik
12、e SET_Codeunionselect 功能分类 as 辅助核算类型, IN_CODE as辅助核算编码,DISP_NAME as辅助核算名称 from dbo.BUDGET_SUBJECT unionselect 往来分类 as 辅助核算类型, IN_CODE as 辅助核算编码, DISP_NAME as辅助核算名称 from dbo.CURRENTS where SET_Code like SET_Codeunionselect 自定分类 as 辅助核算类型,IN_CODE as 辅助核算编码, DISP_NAME as 辅助核算名称 from dbo.OTHER where SET
13、_Code like SET_Code由于各地单位代码都不相同，为方便生成中间表，建立存储过程，在 book_set 中查出各核算单位的单位代码，将单位代码值赋给SET_Code 便可生成对应数据。 （将相应中间表语句粘贴到对应位置，点 执行该存储过程，生成相应中间表）create proc sjql set_code nvarchar(42)asbegindrop table a_kmb,a_yeb,a_pzb,a_fzpzb,a_fzyeb,a_fzxxb-生成会计科目表（a_kmb）-生成科目余额表-生成辅助科目余额表-生成凭证辅助明细表-生成辅助信息表endgoexec sjql 008009002 Go3、采集转换到 AO2011中
