总预算审计脚本2010.docVIP

总预算审计脚本2010 --这是2010年度适用的脚本，以后每年要把脚本里的年度改为相应年度，再运行脚本，然后把生成的拨款单、科目表、凭证表、余额表等几张表导进AO就可以了 --先处理单位表 create table dw1( Zid uniqueidentifier not null, Zdhz varchar(50), dm int IDENTITY (1, 1) NOT NULL) insert into dw1 select Zid,Zdhz from dbo.zdDW select Zid,dm=cast(dm as varchar),Zdhz into dw from dw1 update dw set dm=9+RIGHT(dm,2) where len(dm)=3 and left(dm,1)=4 update dw set dm=8+RIGHT(dm,2) where len(dm)=3 and left(dm,1)=3 update dw set dm=7+RIGHT(dm,2) where len(dm)=3 and left(dm,1)=2 update dw set dm=6+RIGHT(dm,2) where len(dm)=3 and left(dm,1)=1 update dw set dm=5+RIGHT(dm,2) where len(dm)=2 update dw set dm=40+RIGHT(dm,2) where len(dm)=1 --处理凭证表中预算科目不为空，单位代码为空的科目 create table 科目表过渡( 科目代码 varchar(100), 科目名称 varchar(100)) --插入会计科目表 insert 科目表过渡 select Zddm,Zdhz from dbo.zdZHJG -- insert 科目表过渡 SELECT DISTINCT 科目代码=(case when len(a.ZkjkmId)=3 then rtrim(a.ZkjkmId)+00+rtrim(b.Zddm) else rtrim(a.ZkjkmId)+rtrim(b.Zddm) end), 科目名称=b.Zdhz FROM dbo.tbPZ1 A JOIN dbo.zdYSKM b ON LEFT(A.ZyskmId,3)=LEFT(B.Zddm,3) WHERE ZyskmId IS NOT NULL order by 科目代码 insert 科目表过渡 SELECT DISTINCT 科目代码=rtrim(ZkjkmId)+rtrim(00),科目名称=过渡 from dbo.tbPZ1 WHERE LEN(ZkjkmId)=3 and ZyskmId IS NOT NULL --处理凭证表中预算科目为空，单位代码不为空的科目 insert 科目表过渡 SELECT DISTINCT 科目代码=(case when len(ZkjkmId)=3 then rtrim(ZkjkmId)+00+rtrim(b.dm) when len(ZkjkmId)=5 then rtrim(ZkjkmId)+rtrim(b.dm) end), 科目名称=b.Zdhz FROM dbo.tbPZ1 A JOIN dbo.dw b ON a.ZdwId=B.Zid WHERE ZyskmId IS NULL AND ZdwId IS not NULL order by 科目代码 --处理凭证表中预算科目不为空，单位代码不为空的科目 insert 科目表过渡 SELECT DISTINCT 科目代码=(case when len(a.ZkjkmId)=3 and len(a.ZyskmId)=5 then rtrim(a.ZkjkmId)+00+rtrim(b.Zddm)+00 when len(a.ZkjkmId)=5 and len(a.ZyskmId)=5 then rtrim(a.ZkjkmId)+rtrim(b.Zddm)+00 end), 科目名称=过渡 FROM dbo.tbPZ1 A JOIN dbo.zdYSKM b ON A.ZyskmId=B.Zddm WHERE ZyskmId IS NOT NULL AND ZdwId IS not NULL order by 科目代码 insert 科目表过渡 SELECT DISTINCT 科目代码=(case when len(a.ZkjkmId)=3 and