自己做的T3转R9i的脚本
本帖最后由 lzgzmn 于 2011-7-14 23:40 编辑碰到一些单位要把T3替换为R9i，需要把以前老数据转换到R9i里接着用。政务自带有个转换工具，必须在R971下转，还问题多多。没办法只能自己写了……这个脚本只转总账，其它模块没涉及，虽不完善，但总的来说能用，我也懒得再改，如果哪位兄台想要有详细注释的更完善功能的版本，请加我QQ：17369026
部分脚本：
--7凭证转换
DELETE gl_pzlx WHERE gsdm=@1gsdm AND ZTH=@2zth
DELETE gl_pzml WHERE gsdm=@1gsdm AND ZTH=@2zth AND left(kjqj,4)=@3kjnd
DELETE gl_pznr WHERE gsdm=@1gsdm AND ZTH=@2zth AND left(kjqj,4)=@3kjnd
DELETE gl_pzflmx WHERE gsdm=@1gsdm AND ZTH=@2zth AND left(kjqj,4)=@3kjnd
--转换凭证类型。不转换类型限制科目，不影响。可再完善
INSERT INTO GL_PZLX(pzlxdm, gsdm, ZTH, pzlxjc, pzlxmc, syzt, pzjfkm_1, pzjfkm_2, pzdfkm_1, pzdfkm_2, pzbykm_1, pzbykm_2, pzbwkm_1, pzbwkm_2, Jlr_ID, Jl_RQ, Xgr_ID, Xg_RQ)
SELECT left(ctext,2),@1gsdm, @2ZTH, left(ctext,2),ctext, '1','', '', '', '', '', '',isignseq, '', 1, '', -1, '' --使用pzjfkm_1暂存T3凭证类型编号，方便凭证转换
FROM UFDATA_011_2011.dbo.dsign
ORDER BY isignseq
--转换凭证明细
INSERT INTO gl_pznr(gsdm,zth,pzly,wbdm,zy,kmdm,kjqj,pzh,flh,jdbz,je,wldrq,bmdm,zydm,wldm)
SELECT
@1gsdm,@2ZTH,'', '',cdigest,
(select 汇总编码 from TMP_KM_2011 where A.ccode=餐饮编码), --摘要、科目代码等凭证信息
@3kjnd+(CASE WHEN iperiod > 0 THEN (CASE WHEN iperiod < 10 THEN '0'+cast(iperiod AS char(1)) ELSE cast(iperiod AS char(2)) END) END) , --凭证所属期间
(SELECT pzlxdm FROM GL_PZLX WHERE gsdm=@1gsdm and ZTH=@2zth and pzbwkm_1=A.isignseq)
+space(6-len(cast(cast(ino_id AS INT) AS CHAR(10))))+cast(cast(ino_id AS INT) AS CHAR(10)) AS pzh,--转换凭证号
inid AS flh, --凭证分录号
(case WHEN md<>0 THEN '借' ELSE '贷' end ) AS jdbz, --判断借方数设置借贷标志
(case WHEN md<>0 THEN md ELSE mc END) AS je, --获取借贷金额
(case when dt_date is not null then left(replace(CONVERT(char(50),dt_date,20),'-',''),8) else '' end) as wldrq, --往来日期
(case when left(A.ccode,4)='6601' then '01' else '' end), 如果为营业费，部门指定为01
--(case when cdept_id is not null then cdept_id else '' end), --部门和职员编码和T3是相同的，直接转
(case when ccus_id is not null then (select 汇总编码 from TMP_DW_2011 where A.ccus_id=餐饮编码 and left(汇总编码,1)='1') else (case when csup_id is not null then (select 汇总编码 from TMP_DW_2011 where A.csup_id=餐饮编码 and left(汇总编码,1)='2') else '' end) end) --客户和供应商编码
FROM UFDATA_011_2011.dbo.GL_accvouch A
where iperiod>=1 and iperiod<=12 and iflag is null --凭证必须有效
ORDER BY isignseq,ino_id,inid
--现金流量转换
--T3现金流量不在凭证中显示，通过指定科目挂接现金流量数据表GL_CashTable实现，不同于一般的辅助项。R9现金流量算个“自定辅助核算项”，项目编号X，明细在GL_Pzflmx中
--转换现金流量明细。注意mxxh（明细序号）字段，在同一账套，相同凭证，相同分录号下是R9是递增的，T3不是，需要用游标循环来转换，不能直接导，否则提示重复
DECLARE @i INT,@gs VARCHAR(50),@zt VARCHAR(50),@qj VARCHAR(50),@pzh VARCHAR(50),@fl INT,@dm CHAR(20),@je float --定义存储变量
DECLARE flh_cursor CURSOR FOR--定义游标
SELECT @1gsdm,@2ZTH,@3kjnd+(CASE WHEN iperiod > 0 THEN (CASE WHEN iperiod < 10 THEN '0'+cast(iperiod AS char(1)) ELSE cast(iperiod AS char(2)) END) END) kjqj,
(SELECT pzlxdm FROM GL_PZLX WHERE gsdm=@1gsdm and ZTH=@2zth and pzbwkm_1=A.isignseq)+space(6-len(cast(cast(ino_id AS INT) AS CHAR(10))))+cast(cast(ino_id AS INT) AS CHAR(10)) AS pzh,--转换凭证号
inid,(SELECT xjdm FROM GL_xjllxm WHERE gsdm=@1gsdm and kjnd=@3kjnd and sfmx='1' andA.cCashItem=right(rtrim(xjdm),2)),(case when md>0 then md else mc end)
FROM UFDATA_011_2011.dbo.GL_CashTable A
ORDER BY kjqj, pzh
OPEN flh_cursor --打开游标
FETCH NEXT FROM FLH_cursor INTO @gs,@zt,@qj,@pzh,@fl,@dm,@je
WHILE @@FETCH_STATUS = 0
BEGIN
SET @i=(SELECT COUNT(*) FROM gl_pzflmx WHERE gsdm=@gs and zth=@zt and kjqj=@qj and pzh=@pzh and flh=@fl) --判断相同记录已有几个
INSERT INTO GL_PZFLMX (gsdm, ZTH, kjqj,pzly,pzh,flh,mxxh,mxlx,fzdm,je) VALUES(@gs,@zt,@qj,'',@pzh,@fl,@i+1,'X',@dm,@je) --关键在mxxh=@i+1
FETCH NEXT FROM flh_cursor INTO @gs,@zt,@qj,@pzh,@fl,@dm,@je --进入下行记录
END
CLOSE flh_cursor
DEALLOCATE flh_cursor
--构建凭证目录Gl_pzml
--T3无凭证目录表，R9需建立
INSERT INTO GL_PZml(gsdm,zth,kjqj,pzly,pzh,pzrq,fjzs,srid,sr,shid,sh,jzrid,jzr,kjzg,srrq,shrq,jzrq,zt,pzje,pzzy)
SELECT DISTINCT
@1gsdm as gsdm,@2ZTH as zth,@3kjnd+(CASE WHEN iperiod > 0 THEN (CASE WHEN iperiod < 10 THEN '0'+cast(iperiod AS char(1)) ELSE cast(iperiod AS char(2)) END) END) as kjqj,'' as pzly, --公司代码、会计期间、凭证来源
(SELECT pzlxdm FROM GL_PZLX WHERE gsdm=@1gsdm and ZTH=@2zth and pzbwkm_1=A.isignseq)
+space(6-len(cast(cast(ino_id AS INT) AS CHAR(10))))+cast(cast(ino_id AS INT) AS CHAR(10)) AS pzh,--转换凭证号
cast(year(dbill_date) as varchar(4)) + (case when month(dbill_date)<10 then '0'+cast(month(dbill_date) as varchar(2)) else cast(month(dbill_date) as varchar(2)) end)+(case when day(dbill_date)<10 then '0'+cast(day(dbill_date) as varchar(2)) else cast(day(dbill_date) as varchar(2)) end) AS pzrq, --凭证日期
(case when idoc<0 then 0 else idoc end), --附件张数
1 as srid,'系统管理员' as sr,1 as shid,'系统管理员' as sh,-1 as jzrid,'' as jzr,'' as kjzg, --录入、审核人ID及姓名、记账人设置为空、会计主管。统一调整，不使用原数据人员
cast(year(dbill_date) as varchar(4)) + (case when month(dbill_date)<10 then '0'+cast(month(dbill_date) as varchar(2)) else cast(month(dbill_date) as varchar(2)) end)+(case when day(dbill_date)<10 then '0'+cast(day(dbill_date) as varchar(2)) else cast(day(dbill_date) as varchar(2)) end) AS srrq, --输入日期
cast(year(dbill_date) as varchar(4)) + (case when month(dbill_date)<10 then '0'+cast(month(dbill_date) as varchar(2)) else cast(month(dbill_date) as varchar(2)) end)+(case when day(dbill_date)<10 then '0'+cast(day(dbill_date) as varchar(2)) else cast(day(dbill_date) as varchar(2)) end) AS shrq, --审核日期
'' as jzrq,--记账日期无
2 as zt ,--使用状态：1未审核；2已审核；3已记帐；0作废
sum(isnull(md,0)) as pzje, --凭证合计金额
(SELECT TOP 1 cdigest FROM UFDATA_011_2011.dbo.GL_accvouch WHERE A.ino_id=ino_id and A.dbill_date=dbill_date) as pzzy --凭证摘要，只使用每个凭证第一行分录摘要作为当前凭证目录摘要
FROMUFDATA_011_2011.dbo.GL_accvouch A
WHERE iperiod>=1 and iperiod<=12 and iflag is null
GROUP BY ino_id,dbill_date,iperiod,isignseq,idoc
留下个记号，其实我倒是好想把一公司的Ｒ９转换成Ｕ８ wqqq 发表于 2011-7-14 23:52 static/image/common/back.gif
留下个记号，其实我倒是好想把一公司的Ｒ９转换成Ｕ８
其实都一样，至少在技术上{:soso_e100:} 金币努力赚，，帖子卖力顶。 u8能转r9的呀~~不能逆转吗？ lz给力~~虽然看不懂~看山去就很厉害 有没有R9转T3的工具发份给我呢，谢谢
27645244@QQ.COM 非常复杂，不过刚好能用上 求助，能否制作一个R9转为T3 或T6 U8 的转换工具呢。感谢楼主了。nemofreedom@126.com 什么时候我也能做到来回自由的地步
页:
[1]