《工作底稿-资产负债表》
《工作底稿 -个别报表指标数-资产负债表》
select '个别报表数' as 标题,
t.iyear as 年度,t.iperiod as 期间,t.hbcompany as 合并单位,t.biaoyang as 指标类型,t.rownum as 行号,t.indexname as 指标名称,
t.ordernum as 行次,t1.localmoney as 本币金额,t1.wbmoney as 外币金额
from
(
select *
from
(
select distinct iyear,iperiod,hbcompany
from
(
select distinct iyear,iperiod,companyParent as hbcompany from VW_CO_JC_Account_InvestmentParent01
where 1=1 and invenstmentRatio>0 and iyear=$(合并报表-年度) and iperiod=$(合并报表-期间)
union all
select distinct iyear,iperiod,companyname as hbcompany from VW_CO_JC_Account_InvestmentParent01
where 1=1 and invenstmentRatio>0 and iyear=$(合并报表-年度) and iperiod=$(合并报表-期间)
) as tt
) a
cross join
(select * from VW_CO_HB_index where biaoyang in ('A资产负债表','B资产负债表')) b
)t
left join
(
select 'A资产负债表' as biaoyang, iyear,iperiod,hbcompanyname,rownum,zcindexname as indexname,sum(zclocalqm) as localmoney,sum(zcwbqm) as wbmoney
from CO_GB_BalanceSheet
where isbook = '$(合并报表-是否包含未记账)'
group by iyear,iperiod,hbcompanyname,rownum,zcindexname
union all
select 'B资产负债表' as biaoyang, iyear,iperiod,hbcompanyname,rownum,fzqyindexname as indexname,sum(fzqylocalqm) as localmoney,sum(fzqywbqm) as wbmoney
from CO_GB_BalanceSheet
where isbook = '$(合并报表-是否包含未记账)'
group by iyear,iperiod,hbcompanyname,rownum,fzqyindexname
) t1 on t.iyear=t1.iyear and t.iperiod=t1.iperiod and t.hbcompany=t1.hbcompanyname and t.biaoyang=t1.biaoyang and t.rownum=t1.rownum
where t.indexname is not null
order by t.biaoyang,t.rownum
《工作底稿 - 抵消调整与合并-资产负债表》
begin
select t.iyear as 年度,t.iperiod as 期间,t.biaoyang as 指标类型,t.rownum as 行号,t.indexname as 指标名称,
--t1.hbcompanyname,
t.ordernum as 行次,sum(t1.localmoney) as 本币金额,sum(t1.wbmoney) as 外币金额
into #temp_dixiaoauto000
from
(
select *
from
(
select distinct iyear,iperiod,hbcompany
from
(
select distinct iyear,iperiod,companyParent as hbcompany from VW_CO_JC_Account_InvestmentParent01
where 1=1 and invenstmentRatio>0 and iyear=$(合并报表-年度) and iperiod=$(合并报表-期间)
union all
select distinct iyear,iperiod,companyname as hbcompany from VW_CO_JC_Account_InvestmentParent01
where 1=1 and invenstmentRatio>0 and iyear=$(合并报表-年度) and iperiod=$(合并报表-期间)
) as tt
) a
cross join
(select * from VW_CO_HB_index where biaoyang in ('A资产负债表','B资产负债表')) b
)t
left join
(
select 'A资产负债表' as biaoyang, iyear,iperiod,hbcompanyname,rownum,zcindexname as indexname,sum(zclocalqm) as localmoney,sum(zcwbqm) as wbmoney
from CO_GB_BalanceSheet
where isbook = '$(合并报表-是否包含未记账)'
group by iyear,iperiod,hbcompanyname,rownum,zcindexname
union all
select 'B资产负债表' as biaoyang, iyear,iperiod,hbcompanyname,rownum,fzqyindexname as indexname,sum(fzqylocalqm) as localmoney,sum(fzqywbqm) as wbmoney
from CO_GB_BalanceSheet
where isbook = '$(合并报表-是否包含未记账)'
group by iyear,iperiod,hbcompanyname,rownum,fzqyindexname
) t1 on t.iyear=t1.iyear and t.iperiod=t1.iperiod and t.hbcompany=t1.hbcompanyname and t.biaoyang=t1.biaoyang and t.rownum=t1.rownum
where t.iyear=$(合并报表-年度) and t.iperiod=$(合并报表-期间)
group by t.iyear,t.iperiod,t.biaoyang,t.rownum,t.indexname,t.ordernum,t1.iyear,t1.iperiod,t1.biaoyang
select t.dxtypename as 抵消指标类型,t.rownum as 行号,
t.iyear as 年度,t.iperiod as 月份,t.dxgroupname as 抵消规则,t.hbglcompany as 往来单位,
t.benfangcompany as 本方单位,t.duifangcompany as 对方单位,t.indexname as 指标名称,
case when t.ccodefx='借' then t.localbq else NULL end as 本币借方金额,
case when t.ccodefx='贷' then t.localbq else NULL end as 本币贷方金额,
case when t.ccodefx='借' then t.wbbq else NULL end as 外币借方金额,
case when t.ccodefx='贷' then t.wbbq else NULL end as 外币贷方金额
into #temp_dixiaoauto111
from CO_DX_Auto_mx t
where 1=1
and tablename='资产负债表'
and iyear=$(合并报表-年度)
and iperiod=$(合并报表-期间)
and isbook = '$(合并报表-是否包含未记账)'
order by t.iyear,t.iperiod,t.dxtypename,t.dxgroupname,t.hbglcompany,t.benfangcompany,t.duifangcompany
select 抵消指标类型,t1.rownum as 行号,
t.年度,t.月份,t.抵消规则,t.往来单位,
t.本方单位,t.对方单位,t1.indexname as 指标名称,
sum(t.本币借方金额*(case when t1.jsfs='加' then 1 else -1 end )) as 本币借方金额,
sum(t.本币贷方金额*(case when t1.jsfs='加' then 1 else -1 end )) as 本币贷方金额,
sum(t.外币借方金额*(case when t1.jsfs='加' then 1 else -1 end )) as 外币借方金额,
sum(t.外币贷方金额*(case when t1.jsfs='加' then 1 else -1 end )) as 外币贷方金额
into #temp_dixiaoauto222
from #temp_dixiaoauto111 t
inner join
(
select distinct dxtype,dbtype,hbcompanyname,iyear,iperiod,rownum,indexname,jsindexname,jsfs
from VW_CO_JC_indexname_GS
) t1 on t.指标名称=t1.jsindexname
--and t.抵消指标类型=t1.dxtype
and t.年度=t1.iyear and t.月份=t1.iperiod and t.本方单位=t1.hbcompanyname
group by 抵消指标类型,t1.rownum,t.年度,t.月份,t.抵消规则,t.往来单位,t.本方单位,t.对方单位,t1.indexname
select SUBSTRING(indexname, 1, CHARINDEX('-', indexname) - 1) as 抵消指标类型,
SUBSTRING(SUBSTRING(indexname, CHARINDEX('-', indexname) + 1, 100), 1, CHARINDEX('-', SUBSTRING(indexname, CHARINDEX('-', indexname) + 1, 100)) - 1) as 行号,
iyear as 年度,iperiod as 月份,dxgroupname as 抵消规则,hbcompany as 往来单位,
benfangcompany as 本方单位,duifangcompany as 对方单位,
SUBSTRING(indexname, CHARINDEX('-', indexname) + CHARINDEX('-', SUBSTRING(indexname, CHARINDEX('-', indexname) + 1, 100)) + 1, 100) as 指标名称,
localmoney as 本币调整金额,wbmoney as 外币调整金额
into #temp_dixiaoauto333
from CO_DX_Adjust
where 1=1
and SUBSTRING(indexname, 1, CHARINDEX('-', indexname) - 1)='资产负债表'
and iyear=$(合并报表-年度)
and iperiod=$(合并报表-期间)
select 抵消指标类型,t1.rownum as 行号,
t.年度,t.月份,t.抵消规则,t.往来单位,
t.本方单位,t.对方单位,t1.indexname as 指标名称,
sum(t.本币调整金额*(case when t1.jsfs='加' then 1 else -1 end )) as 本币调整金额,
sum(t.外币调整金额*(case when t1.jsfs='加' then 1 else -1 end )) as 外币调整金额
into #temp_dixiaoauto444
from #temp_dixiaoauto333 t
inner join
(
select distinct dxtype,dbtype,hbcompanyname,iyear,iperiod,rownum,indexname,jsindexname,jsfs
from VW_CO_JC_indexname_GS
) t1 on t.指标名称=t1.jsindexname
--and t.抵消指标类型=t1.dxtype
and t.年度=t1.iyear and t.月份=t1.iperiod and t.本方单位=t1.hbcompanyname
group by 抵消指标类型,t1.rownum,t.年度,t.月份,t.抵消规则,t.往来单位,t.本方单位,t.对方单位,t1.indexname
select t.iyear as 年度,t.iperiod as 期间,t.biaoyang as 指标类型,
t.rownum as 行号,t.indexname as 指标名称,t.ordernum as 行次,
t0.本币金额 as 个别汇总本币金额,t0.外币金额 as 个别汇总外币金额,
t1.本币借方金额 as 本币借方金额,t1.本币贷方金额 as 本币贷方金额,
t1.外币借方金额 as 外币借方金额,t1.外币贷方金额 as 外币贷方金额,
t2.本币调整金额 as 本币调整金额,t2.外币调整金额 as 外币调 整金额,
t0.本币金额-isnull(t1.本币借方金额,0)-isnull(t1.本币贷方金额,0)-isnull(t2.本币调整金额,0) as 合并本币金额,
t0.外币金额-isnull(t1.外币借方金额,0)-isnull(t1.外币贷方金额,0)-isnull(t2.外币调整金额,0) as 合并外币金额
from
(
select *
from
(select $(合并报表-年度) as iyear,$(合并报表-期间) as iperiod) a
cross join (select * from VW_CO_HB_index where biaoyang in ('A资产负债表','B资产负债表')) b
)t
left join #temp_dixiaoauto000 as t0
on t.iyear=t0.年度 and t.iperiod=t0.期间 and t.rownum=t0.行号 and t.indexname=t0.指标名称 and t.biaoyang=t0.指标类型
left join
(
select t.年度 as 年度,t.月份 as 期间,t.行号 as 行号,t.指标名称,
sum(本币借方金额) as 本币借方金额,
sum(本币贷方金额) as 本币贷方金额,
sum(外币借方金额) as 外币借方金额,
sum(外币贷方金额) as 外币贷方金额
from
(
select * from #temp_dixiaoauto111
union all
select * from #temp_dixiaoauto222
)t
group by t.年度,t.月份,t.行号,t.指标名称
) t1 on t.iyear=t1.年度 and t.iperiod=t1.期间 and t.rownum=t1.行号 and t.indexname=t1.指标名称
left join
(
select t.年度 as 年度,t.月份 as 期间,t.行号 as 行号,t.指标名称,
sum(本币调整金额) as 本币调整金额,
sum(外币调整金额) as 外币调整金额
from
(
select * from #temp_dixiaoauto333
union all
select * from #temp_dixiaoauto444
)t
group by t.年度,t.月份,t.行号,t.指标名称
) t2 on t.iyear=t2.年度 and t.iperiod=t2.期间 and t.rownum=t2.行号 and t.indexname=t2.指标名称
where 1=1 and t.indexname is not null
order by t.biaoyang,t.rownum
drop table #temp_dixiaoauto000;
drop table #temp_dixiaoauto111;
drop table #temp_dixiaoauto222;
drop table #temp_dixiaoauto333;
drop table #temp_dixiaoauto444;
end
《工作底稿 -个别报表指标数_资产负债表_年初》
select '个别报表数' as 标题,
t.iyear as 年度,t.iperiod as 期间,t.hbcompany as 合并单位,t.biaoyang as 指标类型,t.rownum as 行号,t.indexname as 指标名称,
t.ordernum as 行次,t1.localmoney as 本币金额,t1.wbmoney as 外币金额
from
(
select *
from
(
select distinct iyear,iperiod,hbcompany
from
(
select distinct iyear,iperiod,companyParent as hbcompany from VW_CO_JC_Account_InvestmentParent01
where 1=1 and invenstmentRatio>0 and iyear=$(合并报表-年度) and iperiod=$(合并报表-期间)
union all
select distinct iyear,iperiod,companyname as hbcompany from VW_CO_JC_Account_InvestmentParent01
where 1=1 and invenstmentRatio>0 and iyear=$(合并报表-年度) and iperiod=$(合并报表-期间)
) as tt
) a
cross join
(select * from VW_CO_HB_index where biaoyang in ('A资产负债表','B资产负债表')) b
)t
left join
(
select 'A资产负债表' as biaoyang, iyear,iperiod,hbcompanyname,rownum,zcindexname as indexname,sum(zclocalnc) as localmoney,sum(zcwbnc) as wbmoney
from CO_GB_BalanceSheet
where isbook = '$(合并报表-是否包含未记账)' and iyear=$(合并报表-年度) and iperiod=$(合并报表-期间)
group by iyear,iperiod,hbcompanyname,rownum,zcindexname
union all
select 'B资产负债表' as biaoyang, iyear,iperiod,hbcompanyname,rownum,fzqyindexname as indexname,sum(fzqylocalnc) as localmoney,sum(fzqywbnc) as wbmoney
from CO_GB_BalanceSheet
where isbook = '$(合并报表-是否包含未记账)' and iyear=$(合并报表-年度) and iperiod=$(合并报表-期 间)
group by iyear,iperiod,hbcompanyname,rownum,fzqyindexname
) t1 on t.iyear=t1.iyear and t.hbcompany=t1.hbcompanyname and t.biaoyang=t1.biaoyang and t.rownum=t1.rownum
where t.indexname is not null
order by t.biaoyang,t.rownum
《工作底稿 - 抵消调整与合并_资产负债表_年初》
begin
select t.iyear as 年度,t.iperiod as 期间,t.biaoyang as 指标类型,t.rownum as 行号,t.indexname as 指标名称,
--t1.hbcompanyname,
t.ordernum as 行次,sum(t1.localmoney) as 本币金额,sum(t1.wbmoney) as 外币金额
into #temp_dixiaoauto000
from
(
select *
from
(
select distinct iyear,iperiod,hbcompany
from
(
select distinct iyear,iperiod,companyParent as hbcompany from VW_CO_JC_Account_InvestmentParent01
where 1=1 and invenstmentRatio>0 and iyear=$(合并报表-年度) and iperiod=$(合并报表-期间)
union all
select distinct iyear,iperiod,companyname as hbcompany from VW_CO_JC_Account_InvestmentParent01
where 1=1 and invenstmentRatio>0 and iyear=$(合并报表-年度) and iperiod=$(合并报表-期间)
) as tt
) a
cross join
(select * from VW_CO_HB_index where biaoyang in ('A资产负债表','B资产负债表')) b
)t
left join
(
select 'A资产负债表' as biaoyang, iyear,iperiod,hbcompanyname,rownum,zcindexname as indexname,sum(zclocalnc) as localmoney,sum(zcwbnc) as wbmoney
from CO_GB_BalanceSheet
where isbook = '$(合并报表-是否包含未记账)' and iyear=$(合并报表-年度) and iperiod=$(合并报表-期间)
group by iyear,iperiod,hbcompanyname,rownum,zcindexname
union all
select 'B资产负债表' as biaoyang, iyear,iperiod,hbcompanyname,rownum,fzqyindexname as indexname,sum(fzqylocalnc) as localmoney,sum(fzqywbnc) as wbmoney
from CO_GB_BalanceSheet
where isbook = '$(合并报表-是否包含未记账)' and iyear=$(合并报表-年度) and iperiod=$(合并报表-期间)
group by iyear,iperiod,hbcompanyname,rownum,fzqyindexname
) t1 on t.iyear=t1.iyear and t.hbcompany=t1.hbcompanyname and t.biaoyang=t1.biaoyang and t.rownum=t1.rownum
group by t.iyear,t.iperiod,t.biaoyang,t.rownum,t.indexname,t.ordernum,t1.iyear,t1.iperiod,t1.biaoyang
select t.dxtypename as 抵消指标类型,t.rownum as 行号,
t.iyear as 年度,t.iperiod as 月份,t.dxgroupname as 抵消规则,t.hbglcompany as 往来单位,
t.benfangcompany as 本方单位,t.duifangcompany as 对方单位,t.indexname as 指标名称,
case when t.ccodefx='借' then t.localbq else NULL end as 本币借方金额,
case when t.ccodefx='贷' then t.localbq else NULL end as 本币贷方金额,
case when t.ccodefx='借' then t.wbbq else NULL end as 外币借方金额,
case when t.ccodefx='贷' then t.wbbq else NULL end as 外币贷方金额
into #temp_dixiaoauto111
from CO_DX_Auto_mx t
where 1=1
and tablename='资产负债表'
and iyear=$(合并报表-年度)
and iperiod=0
and isbook = '$(合并报表-是否包含未记账)'
order by t.iyear,t.iperiod,t.dxtypename,t.dxgroupname,t.hbglcompany,t.benfangcompany,t.duifangcompany
select 抵消指标类型,t1.rownum as 行号,
t.年度,t.月份,t.抵消规则,t.往来单位,
t.本方单位,t.对方单位,t1.indexname as 指标名称,
sum(t.本币借方金额*(case when t1.jsfs='加' then 1 else -1 end )) as 本币借方金额,
sum(t.本币贷方金额*(case when t1.jsfs='加' then 1 else -1 end )) as 本币贷方金额,
sum(t.外币借方金额*(case when t1.jsfs='加' then 1 else -1 end )) as 外币借方金额,
sum(t.外币贷方金额*(case when t1.jsfs='加' then 1 else -1 end )) as 外币贷方金额
into #temp_dixiaoauto222
from #temp_dixiaoauto111 t
inner join
(
select distinct dxtype,dbtype,hbcompanyname,iyear,iperiod,rownum,indexname,jsindexname,jsfs
from VW_CO_JC_indexname_GS
) t1 on t.指标名称=t1.jsindexname
--and t.抵消指标类型=t1.dxtype
and t.年度=t1.iyear and t.月份=t1.iperiod and t.本方单位=t1.hbcompanyname
group by 抵消指标类型,t1.rownum,t.年度,t.月份,t.抵消规则,t.往来单位,t.本方单位,t.对方单位,t1.indexname
select SUBSTRING(indexname, 1, CHARINDEX('-', indexname) - 1) as 抵消指标类型,
SUBSTRING(SUBSTRING(indexname, CHARINDEX('-', indexname) + 1, 100), 1, CHARINDEX('-', SUBSTRING(indexname, CHARINDEX('-', indexname) + 1, 100)) - 1) as 行号,
iyear as 年度,iperiod as 月份,dxgroupname as 抵消规则,hbcompany as 往来单位,
benfangcompany as 本方单位,duifangcompany as 对方单位,
SUBSTRING(indexname, CHARINDEX('-', indexname) + CHARINDEX('-', SUBSTRING(indexname, CHARINDEX('-', indexname) + 1, 100)) + 1, 100) as 指标名称,
localmoney as 本币调整金额,wbmoney as 外币调整金额
into #temp_dixiaoauto333
from CO_DX_Adjust
where iperiod=0
and SUBSTRING(indexname, 1, CHARINDEX('-', indexname) - 1)='资产负债表'
and iyear=$(合并报表-年度)
select 抵消指标类型,t1.rownum as 行号,
t.年度,t.月份,t.抵消规则,t.往来单位,
t.本方单位,t.对方单位,t1.indexname as 指标名称,
sum(t.本币调整金额*(case when t1.jsfs='加' then 1 else -1 end )) as 本币调整金额,
sum(t.外币调整金额*(case when t1.jsfs='加' then 1 else -1 end )) as 外币调整金额
into #temp_dixiaoauto444
from #temp_dixiaoauto333 t
inner join
(
select distinct dxtype,dbtype,hbcompanyname,iyear,iperiod,rownum,indexname,jsindexname,jsfs
from VW_CO_JC_indexname_GS
) t1 on t.指标名称=t1.jsindexname
--and t.抵消指标类型=t1.dxtype
and t.年度=t1.iyear and t.月份=t1.iperiod and t.本方单位=t1.hbcompanyname
group by 抵消指标类型,t1.rownum,t.年度,t.月份,t.抵消规则,t.往来单位,t.本方单位,t.对方单位,t1.indexname
select t.iyear as 年度,t.iperiod as 期间,t.biaoyang as 指标类型,
t.rownum as 行号,t.indexname as 指标名称,t.ordernum as 行次,
t0.本币金额 as 个别汇总本币金额,t0.外币金额 as 个别汇总外币金额,
t1.本币借方金额 as 本币借方金额,t1.本币贷方金额 as 本币贷方金额,
t1.外币借方金额 as 外币借方金额,t1.外币贷方金额 as 外币贷方金额,
t2.本币调整金额 as 本币调整金额,t2.外币调整金额 as 外币调整金额,
t0.本币金额-isnull(t1.本币借方金额,0)-isnull(t1.本币贷方金额,0)-isnull(t2.本币调整金额,0) as 合并本币金额,
t0.外币金额-isnull(t1.外币借方金额,0)-isnull(t1.外币贷方金额,0)-isnull(t2.外币调整金额,0) as 合并外币金额
from
(
select *
from
(select $(合并报表-年度) as iyear,$(合并报表-期间) as iperiod) a
cross join (select * from VW_CO_HB_index where biaoyang in ('A资产负债表','B资产负债表')) b
)t
left join #temp_dixiaoauto000 as t0
on t.iyear=t0.年度 and t.iperiod=t0.期间 and t.rownum=t0.行号 and t.indexname=t0.指标名称 and t.biaoyang=t0.指标类型
left join
(
select t.年度 as 年度,t.月份 as 期间,t.行号 as 行号,t.指标名称,
sum(本币借方金 额) as 本币借方金额,
sum(本币贷方金额) as 本币贷方金额,
sum(外币借方金额) as 外币借方金额,
sum(外币贷方金额) as 外币贷方金额
from
(
select * from #temp_dixiaoauto111
union all
select * from #temp_dixiaoauto222
)t
group by t.年度,t.月份,t.行号,t.指标名称
) t1 on t.iyear=t1.年度 and t.rownum=t1.行号 and t.indexname=t1.指标名称
left join
(
select t.年度 as 年度,t.月份 as 期间,t.行号 as 行号,t.指标名称,
sum(本币调整金额) as 本币调整金额,
sum(外币调整金额) as 外币调整金额
from
(
select * from #temp_dixiaoauto333
union all
select * from #temp_dixiaoauto444
)t
group by t.年度,t.月份,t.行号,t.指标名称
) t2 on t.iyear=t2.年度 and t.rownum=t2.行号 and t.indexname=t2.指标名称
where 1=1 and t.indexname is not null
order by t.biaoyang,t.rownum
drop table #temp_dixiaoauto000;
drop table #temp_dixiaoauto111;
drop table #temp_dixiaoauto222;
drop table #temp_dixiaoauto333;
drop table #temp_dixiaoauto444;
end