----------------------------------------------------------------------------------------
--火星总预算会计标准表_2018年度
--编制时间:年月日
--编制人:爱玩儿の编程小菜机
--数据源:(火星爱玩儿の编程小菜机国库股总预算会计年度备份账套)ZFKJ2005_ZFKJ2005_2018YEAR
--执行结果:生成未包含上级级科目名称的火星总预算会计标准表爱玩儿の编程小菜机_ZYSKJ_BZB_2018
----------------------------------------------------------------------------------------
--执行过程:
--第一步,还原ZFKJ2005_ZFKJ2005_2018YEAR数据库;
use ZFKJ2005_ZFKJ2005_2018YEAR
go
--第二步,在该数据库中新生成火星总预算会计标准表爱玩儿の编程小菜机_ZYSKJ_BZB_2018;
--1.生成含上级科目名的会计科目视图ZF1ZZ01_S
IF (EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME=N'ZF1ZZ01_S'))
DROP VIEW ZF1ZZ01_S
go
alter table ZF1ZZ01
alter column NAME nvarchar(13);
go
create view ZF1ZZ01_S
as
select a.SCM,a.KMH,a.SCM_1,a.SCM_2,a.SCM_3,a.SCM_4,a.SCM_5,a.ATT,a.JC,a.JD_FLAG,
case
when a.JC=1 then b.NAME
when a.JC=2 then replace((b.NAME+'\'+c.NAME),' ','')
when a.JC=3 then replace((b.NAME+'\'+c.NAME+'\'+d.NAME),' ','')
when a.JC=4 then replace((b.NAME+'\'+c.NAME+'\'+d.NAME+'\'+e.NAME),' ','')
when a.JC=5 then replace((b.NAME+'\'+c.NAME+'\'+d.NAME+'\'+e.NAME +'\'+f.NAME),' ','')
end NAME
from ZF1ZZ01 a
left join ZF1ZZ01 b on b.SCM=a.SCM_1
left join ZF1ZZ01 c on c.SCM=a.SCM_2
left join ZF1ZZ01 d on d.SCM=a.SCM_3
left join ZF1ZZ01 e on e.SCM=a.SCM_4
left join ZF1ZZ01 f on f.SCM=a.SCM_5
--order by a.KMH
GO
--2.生成含上级科目名的预算科目(功能科目)视图ZF1KM01_S
IF (EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME=N'ZF1KM01_S'))
DROP VIEW ZF1KM01_S
go
alter table [ZF1KM01]
alter column NAME nvarchar(30);
go
create view ZF1KM01_S
as
select a.SCM,a.KMH,a.SCM_1,a.SCM_2,a.SCM_3,a.SCM_4,a.SCM_5,a.JC,a.JC_FLAG,
case
when a.JC=1 then b.NAME
when a.JC=2 then replace((b.NAME+'\'+c.NAME),' ','')
when a.JC=3 then replace((b.NAME+'\'+c.NAME+'\'+d.NAME),' ','')
when a.JC=4 then replace((b.NAME+'\'+c.NAME+'\'+d.NAME+'\'+e.NAME),' ','')
when a.JC=5 then replace((b.NAME+'\'+c.NAME+'\'+d.NAME+'\'+e.NAME +'\'+f.NAME),' ','')
end NAME
from ZF1KM01 a
left join ZF1KM01 b on b.SCM=a.SCM_1
left join ZF1KM01 c on c.SCM=a.SCM_2
left join ZF1KM01 d on d.SCM=a.SCM_3
left join ZF1KM01 e on e.SCM=a.SCM_4
left join ZF1KM01 f on f.SCM=a.SCM_5
--order by a.KMH
go
--3.生成含上级科目名的经济科目视图ZF1KM_MJ01_S
IF (EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME=N'ZF1KM_MJ01_S'))
DROP VIEW ZF1KM_MJ01_S
go
alter table ZF1KM_MJ01
alter column NAME nvarchar(30);
go
create view ZF1KM_MJ01_S
as
select a.SCM,a.KMH,a.SCM_1,a.SCM_2,a.SCM_3,a.SCM_4,a.SCM_5,a.JC,a.JC_FLAG,
case
when a.JC=1 then b.NAME
when a.JC=2 then replace((b.NAME+'\'+c.NAME),' ','')
when a.JC=3 then replace((b.NAME+'\'+c.NAME+'\'+d.NAME),' ','')
when a.JC=4 then replace((b.NAME+'\'+c.NAME+'\'+d.NAME+'\'+e.NAME),' ','')
when a.JC=5 then replace((b.NAME+'\'+c.NAME+'\'+d.NAME+'\'+e.NAME +'\'+f.NAME),' ','')
end NAME
from ZF1KM_MJ01 a
left join ZF1KM_MJ01 b on b.SCM=a.SCM_1
left join ZF1KM_MJ01 c on c.SCM=a.SCM_2
left join ZF1KM_MJ01 d on d.SCM=a.SCM_3
left join ZF1KM_MJ01 e on e.SCM=a.SCM_4
left join ZF1KM_MJ01 f on f.SCM=a.SCM_5
--order by a.KMH
go
--4.生成总预算会计标准表爱玩儿の编程小菜机_ZYSKJ_2018
if exists(select * from dbo.sysobjects where name='爱玩儿の编程小菜机_ZYSKJ_BZB_2018')
drop table 爱玩儿の编程小菜机_ZYSKJ_BZB_2018
go
select
'爱玩儿の编程小菜机' XZQHDM, --行政区划代码
'火星' XZQHMC, --行政区划名称
'[ZFKJ2005_ZFKJ2005_2018YEAR]' KJZTCODE, --会计账套代码?
'火星爱玩儿の编程小菜机总预算会计年度' KJZTNAME, --会计账套名称?
'2018' KJND, --会计年度
(
case
when len(MONTH)=1 then '0'+cast(MONTH as varchar(2))
else cast(MONTH as varchar(2))
end
) KJQJ, --会计期间
'记账凭证' PZLX, --凭证类型
cast(PDH as varchar(4)) PZCODE, --凭证编号
cast(replace(ZDR_TIME,'-','') as CHAR(8))
PZDATE, --凭证日期
cast(FLH as varchar(4)) PZFLH, --凭证分录号
km.KMH KMCODE, --科目编码
km.NAME KMNAME, --科目名称
(
case
when km.ATT='支出' then ys.KMH --(会计科目为支出类)
else null
end
) GNFLCODE, --功能科目(仅用于支出类)
(
case
when km.ATT='支出' then ys.NAME
else null
end
) GNFLNAME, --功能科目名称
---------------------------------------------------------------------------------
--新增字段:
(
case
when km.ATT='支出' then jj.KMH --(会计科目为支出类)
else null
end
) JJFLCODE, --经济科目(仅用于支出类)
(
case
when km.ATT='支出' then jj.NAME
else null
end
) JJFLNAME, --经济科目名称
---------------------------------------------------------------------------------
--新增字段:
(
case
when km.ATT='收入' then km.KMH --(会计科目为支出类)
else null
end
) SRKMCODE, --收入科目(仅用于支出类)
(
case
when km.ATT='收入' then km.NAME
else null
end
) SRKMNAME, --收入科目名称
---------------------------------------------------------------------------------
(
case
when ZY<>ZY_MX then ZY+'('+ZY_MX +')'
else ZY
end
) ZHAIYAO, --摘要
(
case
when JDF=1 then VAL
else 0
end
) JFJE, --借方金额
(
case
when JDF=-1 then VAL
else 0
end
) DFJE, --借方金额
WB1+WB2+WB3 BZ --备注
INTO 爱玩儿の编程小菜机_ZYSKJ_BZB_2018 --生成:大竹_总预算会计_标准表_2018
from ZF1PD pz left join ZF1ZZ01_S km -- 会计科目
on pz.ZZ_SCM = km.scm
left join ZF1KM01_S ys -- 功能科目(预算科目)
on pz.KM_SCM = ys.scm
-- left join ZF1DW01 dw -- 单位编码(因会计科目未包含具体单位,此条件可删除)
-- on pz.DW_SCM = dw.scm
left join ZF1KM_MJ01_S jj -- 经济科目
on pz.KM_MJ_SCM = jj.scm
go
--第三步,创建新数据库,将总预算会计标准表导入,并备份上报;
--1.创建新数据库[火星总预算会计标准表_2018年度]:
USE master
GO
if exists(select * from dbo.sysdatabases where name='火星总预算会计标准表_2018年度')
drop database 火星总预算会计标准表_2018年度
GO
create database [火星总预算会计标准表_2018年度]
go
--2.将总预算会计标准表导入:
use [ZFKJ2005_ZFKJ2005_2018YEAR]
go
select *
into [火星总预算会计标准表_2018年度].dbo.爱玩儿の编程小菜机_ZYSKJ_BZB_2018
from 爱玩儿の编程小菜机_ZYSKJ_BZB_2018
go
--3.查看[火星总预算会计标准表_2018年度]数据库中的[爱玩儿の编程小菜机_ZYSKJ_BZB_2018]表:
use [火星总预算会计标准表_2018年度]
select *
from 爱玩儿の编程小菜机_ZYSKJ_BZB_2018
order by cast(KJQJ as int),cast(PZCODE as int),cast(PZFLH as int)