《工作底稿 -个别报表指标数-资产负债表》

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

天智合并报表

《工作底稿-资产负债表》

《工作底稿 -个别报表指标数-资产负债表》

《工作底稿 - 抵消调整与合并-资产负债表》

《工作底稿 -个别报表指标数资产负债表年初》

《工作底稿 - 抵消调整与合并资产负债表年初》