重分类功能扩展
扩展1：个人往来或其他辅助核算重分类；
1.执行下方SQL修改视图
注意：下方视图是往来单位+个人重分类；可以根据实际重分类需要修改视图。
alter view VW_CO_JC_ERP_Balance as
SELECT dbtype, dbName, accidname, companyName, iyear, iperiod, isbook, accountCode AS ccode, accountName AS ccodename, isdetail,
CASE WHEN MAX(accountDcdirection) = '652' THEN '1' ELSE 2 END AS ccodefx,
isauxacccustomercode+isauxaccpersoncode AS wanglaiFzhsCode,
isauxacccustomer+isauxaccperson AS wanglaiFzhs, SUM(origAmountBalance) AS wbqc, SUM(origAmountDr) AS wbjffs, SUM(origAmountCr) AS wbdffs,
SUM(cumOrigAmountDr) AS wbjfytdfs, SUM(cumOrigAmountCr) AS wbdfytdfs, SUM(endorigAmountBalance) AS wbqm, SUM(amountBalance) AS localqc,
SUM(amountDr) AS localjffs, SUM(amountCr) AS localdffs, SUM(cumAmountDr) AS localjfytdfs, SUM(cumAmountCr) AS localdfytdfs,
SUM(endamountBalance) AS localqm
FROM dbo.CO_JC_ERP_Balance
GROUP BY dbtype, dbName, accidname, companyName, iyear, iperiod, isbook, accountCode, accountName, isdetail, isauxacccustomercode+isauxaccpersoncode, isauxacccustomer+isauxaccperson
2.更新整合任务中的《个别-资产负债表》
注意：用下方的SQL，替换整合任务中的SQL。
begin
select *
into #temp_VW_CO_JC_BalanceSheet_zb
from VW_CO_JC_BalanceSheet_zb where 1=1 and accidname='$(报表更新_账套)' and iyear=$(报表更新_年度)
select *
into #temp_VW_CO_JC_BalanceSheet_by
from VW_CO_JC_BalanceSheet_by where 1=1 and accidname='$(报表更新_账套)' and iyear=$(报表更新_年度)
select *
into #temp_CO_JC_Ccode
from CO_JC_Ccode where 1=1 and accidname='$(报表更新_账套)' and iyear=$(报表更新_年度)
select *
into #temp_VW_CO_JC_ERP_Balance
from VW_CO_JC_ERP_Balance where 1=1 and accidname='$(报表更新_账套)' and iyear=$(报表更新_年度)
select faa.tablename,faa.dbtype,faa.dbname,faa.accidname,faa.companyname,
faa.iyear,faa.iperiod,faa.ruleVersion,faa.isbook,faa.indextype,faa.rownum,faa.indexname,
sum(localqm) as localqm,sum(localqc) as localqc,sum(wbqm) as wbqm,sum(wbqc) as wbqc
into #temp_zichanfuzhaidibiao111
from
(
select a.tablename as tablename,a.dbtype as dbtype,a.dbname as dbname,
a.accidname as accidname,a.companyname as companyname,
a.iyear as iyear,a.iperiod as iperiod,a.ruleVersion as ruleVersion,
a.isbook as isbook,a.indextype as indextype,a.rownum as rownum,a.indexname as indexname,
case when a.jsfs='减' then localqm*-1 else localqm end as localqm,
case when a.jsfs='减' then localqc*-1 else localqc end as localqc,
case when a.jsfs='减' then wbqm*-1 else wbqm end as wbqm,
case when a.jsfs='减' then wbqc*-1 else wbqc end as wbqc
--sum(localqm) as localqm,sum(localqc) as localqc,sum(wbqm) as wbqm,sum(wbqc) as wbqc
from
(
select mm.tablename,mm.dbtype,mm.dbname,mm.companyname,mm.accidname,mm.iyear,mm.iperiod,
mm.ruleversion,mm.indexname,mm.ccode,mm.jdfx,mm.jsfs,mm.isbook,mm.ccodefx,mm.hesuan,mm.isdetail2,mm.indextype,mm.rownum,
case when mm.jdfx='借' then mm.localjfqc when mm.jdfx='贷' then mm.localdfqc else mm.localqc end as localqc,
case when mm.jdfx='借' then mm.localjfqm when mm.jdfx='贷' then mm.localdfqm else mm.localqm end as localqm,
case when mm.jdfx='借' then mm.wbjfqc when mm.jdfx='贷' then mm.wbdfqc else mm.wbqc end as wbqc,
case when mm.jdfx='借' then mm.wbjfqm when mm.jdfx='贷' then mm.wbdfqm else mm.wbqm end as wbqm
from
(
select
ff.tablename,ff.dbtype,ff.dbname,ff.companyname,ff.accidname,ff.iyear,ff.iperiod,
ff.ruleversion,ff.indexname,ff.ccode,ff.jdfx,ff.jsfs,ff.isbook,ff.ccodefx,ff.hesuan,ff.isdetail2,ff.indextype,ff.rownum,
wanglaiFzhsCode,wanglaiFzhs,
--ff.ccodemx,ff.ccodemxfx,ff.ccodefxname,
--case when ff.jdfx='借' then f1.localjfqc when ff.jdfx='贷' then f1.localdfqc else f1.localqc end as localqc,
--case when ff.jdfx='借' then f1.localjfqm when ff.jdfx='贷' then f1.localdfqm else f1.localqm end as localqm,
--case when ff.jdfx='借' then f1.wbjfqc when ff.jdfx='贷' then f1.wbdfqc else f1.wbqc end as wbqc,
--case when ff.jdfx='借' then f1.wbjfqm when ff.jdfx='贷' then f1.wbdfqm else f1.wbqm end as wbqm
case when sum(isnull(f1.localjfqc,0))-sum(isnull(f1.localdfqc,0))>0 then sum(isnull(f1.localjfqc,0))-sum(isnull(f1.localdfqc,0)) else 0 end as localjfqc,
case when sum(isnull(f1.localjfqc,0))-sum(isnull(f1.localdfqc,0))<0 then abs(sum(isnull(f1.localjfqc,0))-sum(isnull(f1.localdfqc,0))) else 0 end as localdfqc,
case when ff.ccodefx='借' then sum(isnull(f1.localjfqc,0))-sum(isnull(f1.localdfqc,0)) else sum(isnull(f1.localdfqc,0))-sum(isnull(f1.localjfqc,0)) end as localqc,
case when sum(isnull(f1.localjfqm,0))-sum(isnull(f1.localdfqm,0))>0 then sum(isnull(f1.localjfqm,0))-sum(isnull(f1.localdfqm,0)) else 0 end as localjfqm,
case when sum(isnull(f1.localjfqm,0))-sum(isnull(f1.localdfqm,0))<0 then abs(sum(isnull(f1.localjfqm,0))-sum(isnull(f1.localdfqm,0))) else 0 end as localdfqm,
case when ff.ccodefx='借' then sum(isnull(f1.localjfqm,0))-sum(isnull(f1.localdfqm,0)) else sum(isnull(f1.localdfqm,0))-sum(isnull(f1.localjfqm,0)) end as localqm,
case when sum(isnull(f1.wbjfqc,0))-sum(isnull(f1.wbdfqc,0))>0 then sum(isnull(f1.wbjfqc,0))-sum(isnull(f1.wbdfqc,0)) else 0 end as wbjfqc,
case when sum(isnull(f1.wbjfqc,0))-sum(isnull(f1.wbdfqc,0))<0 then abs(sum(isnull(f1.wbjfqc,0))-sum(isnull(f1.wbdfqc,0))) else 0 end as wbdfqc,
case when ff.ccodefx='借' then sum(isnull(f1.wbjfqc,0))-sum(isnull(f1.wbdfqc,0)) else sum(isnull(f1.wbdfqc,0))-sum(isnull(f1.wbjfqc,0)) end as wbqc,
case when sum(isnull(f1.wbjfqm,0))-sum(isnull(f1.wbdfqm,0))>0 then sum(isnull(f1.wbjfqm,0))-sum(isnull(f1.wbdfqm,0)) else 0 end as wbjfqm,
case when sum(isnull(f1.wbjfqm,0))-sum(isnull(f1.wbdfqm,0))<0 then abs(sum(isnull(f1.wbjfqm,0))-sum(isnull(f1.wbdfqm,0))) else 0 end as wbdfqm,
case when ff.ccodefx='借' then sum(isnull(f1.wbjfqm,0))-sum(isnull(f1.wbdfqm,0)) else sum(isnull(f1.wbdfqm,0))-sum(isnull(f1.wbjfqm,0)) end as wbqm
--sum(isnull(f1.localjfqc,0)) as localjfqc,sum(isnull(f1.localjfqm,0)) as localjfqm,
--sum(isnull(f1.localdfqc,0)) as localdfqc,sum(isnull(f1.localdfqm,0)) as localdfqm,
--sum(isnull(f1.wbjfqc,0)) as wbjfqc,sum(isnull(f1.wbjfqm,0)) as wbjfqm,
--sum(isnull(f1.wbdfqc,0)) as wbdfqc,sum(isnull(f1.wbdfqm,0)) as wbdfqm,
--f1.localdfqc,f1.localdfqm,f1.localdfqm,f1.localdfqm
--sum(isnull(f1.localqc,0)) as localqc,sum(isnull(f1.localqm,0)) as localqm,
--sum(isnull(f1.wbqc,0)) as wbqc,sum(isnull(f1.wbqc,0)) as wbqm
from
(
select t.*,t1.ccode as ccodemx,t1.ccodefx as ccodemxfx,t1.ccodefxname,
case when t.hesuan='重分类' then 999 else 0 end as isdetail2
from #temp_VW_CO_JC_BalanceSheet_zb as t
left join #temp_CO_JC_Ccode t1 on t.ccode=substring(t1.ccode,1,len(t.ccode)) and t1.bend='1'
and (case when len(t1.ccode)-LEN(t.ccode)>0 and len(t1.ccode)-LEN(t.ccode)<2 then 0 else 1 end)=1
) as ff
left join
(
select
dbtype,dbname,accidname,companyname,iyear,iperiod,isbook,ccode,ccodename,ccodefx,
isdetail,case when isdetail=0 then 0 else 999 end as isdetail2,
wanglaiFzhsCode,wanglaiFzhs,
(case when ccodefx='1' then isnull(localqc,0) else 0-isnull(localqc,0) end ) as localqc,
(case when isnull(localqc,0)>0 then isnull(localqc,0) else 0 end ) as localjfqc,
(case when isnull(localqc,0)<0 then abs(isnull(localqc,0)) else 0 end ) as localdfqc,
(case when ccodefx='1' then localqm else 0-isnull(localqm,0) end ) as localqm,
(case when isnull(localqm,0)>0 then isnull(localqm,0) else 0 end ) as localjfqm,
(case when isnull(localqm,0)<0 then abs(isnull(localqm,0)) else 0 end ) as localdfqm,
(case when ccodefx='1' then isnull(wbqc,0) else 0-isnull(wbqc,0) end ) as wbqc,
(case when isnull(wbqc,0)>0 then isnull(wbqc,0) else 0 end ) as wbjfqc,
(case when isnull(wbqc,0)<0 then abs(isnull(wbqc,0)) else 0 end ) as wbdfqc,
(case when ccodefx='1' then wbqm else 0-isnull(wbqm,0) end ) as wbqm,
(case when isnull(wbqm,0)>0 then isnull(wbqm,0) else 0 end ) as wbjfqm,
(case when isnull(wbqm,0)<0 then abs(isnull(wbqm,0)) else 0 end ) as wbdfqm
from #temp_VW_CO_JC_ERP_Balance
)as f1
on ff.iperiod=f1.iperiod and ff.isdetail2=f1.isdetail2 and ff.isbook=f1.isbook and ff.ccodemx=f1.ccode
where 1=1
group by ff.tablename,ff.dbtype,ff.dbname,ff.companyname,ff.accidname,ff.iyear,ff.iperiod,
ff.ruleversion,ff.indexname,ff.ccode,ff.jdfx,ff.jsfs,ff.isbook,ff.ccodefx,ff.hesuan,ff.isdetail2,ff.indextype,ff.rownum,
wanglaiFzhsCode,wanglaiFzhs
) as mm
)a
)faa
group by faa.tablename,faa.dbtype,faa.dbname,faa.accidname,faa.companyname,faa.iyear,faa.iperiod,
faa.ruleVersion,faa.isbook,faa.indextype,faa.rownum,faa.indexname
order by faa.rownum
select t.*,isnull(t1.localqc,0) as localnc,isnull(t1.wbqc,0) as wbnc
into #temp_zichanfuzhaidibiao222
from #temp_zichanfuzhaidibiao111 t
left join (select * from #temp_zichanfuzhaidibiao111 where iperiod=1 ) t1
on t.tablename=t1.tablename and t.dbtype=t1.dbtype and t.dbname=t1.dbname and t.accidname=t1.accidname
and t.iyear=t1.iyear and t.ruleVersion=t1.ruleVersion and t.isbook=t1.isbook and t.indextype=t1.indextype
and t.rownum=t1.rownum and t.indexname=t1.indexname
select b.tablename as tablename,b.dbtype as dbtype,b.dbname as dbname,b.accidname as accidname,b.companyname as companyname,
b.iyear as iyear,b.iperiod as iperiod,b.RuleVersion as RuleVersion,b.isbook as isbook,
a.指标类型 as indextype,a.行号 as rownum,a.指标名称 as indexname,
isnull(SUM(case when a.计算方式='减' then b.localqm*-1 else b.localqm end),0) as localqm,
isnull(SUM(case when a.计算方式='减' then b.localqc*-1 else b.localqc end),0) as localqc,
isnull(SUM(case when a.计算方式='减' then b.wbqm*-1 else b.wbqm end),0) as wbqm,
isnull(SUM(case when a.计算方式='减' then b.wbqc*-1 else b.wbqc end),0) as wbqc,
isnull(SUM(case when a.计算方式='减' then b.localnc*-1 else b.localnc end),0) as localnc,
isnull(SUM(case when a.计算方式='减' then b.wbnc*-1 else b.wbnc end),0) as wbnc
into #temp_zichanfuzhaidibiao333
from CO_JC_BalanceSheet_Styles_GS as a
inner join #temp_zichanfuzhaidibiao222 as b
on a.报表名称=b.tablename and a.取值规则版本=b.ruleversion and a.计算指标=b.indexname and a.指标类型=b.indextype
group by b.tablename,b.dbtype,b.dbname,b.accidname,b.companyname,b.iyear,b.iperiod,b.RuleVersion,b.isbook,a.指标名称,a.指标类型,a.行号;
select t.*,t1.localnc as zclocalnc,t1.localqm as zclocalqm,t1.localqc as zclocalqc,t1.wbnc as zcwbnc,t1.wbqm as zcwbqm,t1.wbqc as zcwbqc,
t2.localnc as fzqylocalnc,t2.localqm as fzqylocalqm,t2.localqc as fzqylocalqc,t2.wbnc as fzqywbnc,t2.wbqm as fzqywbqm,t2.wbqc as fzqywbqc,
getdate() as update_time
into #temp_zichanfuzhaidibiao444
from
(
select distinct a.tablename,a.dbtype,a.dbname,a.companyname,a.accidname,hbcompanyname,iyear,iperiod,
ruleversion,rownum,zcindexname,zcindexordernum,zclogictype,fzqyindexname,fzqyindexordernum,fzqylogictype,isbook
from #temp_VW_CO_JC_BalanceSheet_by a
left join VW_CO_JC_Account as b on a.dbtype=b.dbtype and a.dbname=b.dbname and a.companyname=b.companyname and a.accidname=b.accidname
) t
left join
(
select * from
( select * from #temp_zichanfuzhaidibiao222 union all select * from #temp_zichanfuzhaidibiao333) m
where indextype='zcindexname'
) as t1
on t.tablename=t1.tablename and t.dbtype=t1.dbtype and t.dbname=t1.dbname and t.accidname=t1.accidname and t.iyear=t1.iyear and t.iperiod=t1.iperiod
and t.rownum=t1.rownum and t.isbook=t1.isbook
left join
(
select * from
( select * from #temp_zichanfuzhaidibiao222 union all select * from #temp_zichanfuzhaidibiao333) m
where indextype='fzqyindexname'
) as t2
on t.tablename=t2.tablename and t.dbtype=t2.dbtype and t.dbname=t2.dbname and t.accidname=t2.accidname and t.iyear=t2.iyear and t.iperiod=t2.iperiod
and t.rownum=t2.rownum and t.isbook=t2.isbook
select t.*,case when isnull(invenstmentRatio,0)<0 then 0 else isnull(invenstmentRatio,0) end as invenstmentRatio2
into #temp_InvestmentParent000
from VW_CO_JC_Account_InvestmentParent01 as t
where 1=1
and t.iyear=$(报表更新_年度)
select t.*,t2.companyParent,
t1.fzqylocalnc*t2.invenstmentRatio2 as fzqylocalnc2,
t1.fzqylocalqc*t2.invenstmentRatio2 as fzqylocalqc2,
t1.fzqylocalqm*t2.invenstmentRatio2 as fzqylocalqm2,
t1.fzqywbnc*t2.invenstmentRatio2 as fzqywbnc2,
t1.fzqywbqc*t2.invenstmentRatio2 as fzqywbqc2,
t1.fzqywbqm*t2.invenstmentRatio2 as fzqywbqm2
into #temp_guishumugongsi1111
from
(
select *
from #temp_zichanfuzhaidibiao444
where 1=1
and (fzqyindexname like '%归属母公司所有者权益%' )
) as t
left join
(
select *
from #temp_zichanfuzhaidibiao444
where 1=1
and (fzqyindexname like '所有者权益%计' )
) as t1 on t.tablename=t1.tablename and t.dbtype=t1.dbtype and t.companyname=t1.companyname and t.accidname=t1.accidname and t.iyear=t1.iyear and t.iperiod=t1.iperiod and t.isbook=t1.isbook
left join #temp_InvestmentParent000 as t2 on t.dbtype=t2.dbtype and t.companyname=t2.companyname and t.iyear=t2.iyear and t.iperiod=t2.iperiod
select t.*,t2.companyParent,
t1.fzqylocalnc*(1-t2.invenstmentRatio2) as fzqylocalnc2,
t1.fzqylocalqc*(1-t2.invenstmentRatio2) as fzqylocalqc2,
t1.fzqylocalqm*(1-t2.invenstmentRatio2) as fzqylocalqm2,
t1.fzqywbnc*(1-t2.invenstmentRatio2) as fzqywbnc2,
t1.fzqywbqc*(1-t2.invenstmentRatio2) as fzqywbqc2,
t1.fzqywbqm*(1-t2.invenstmentRatio2) as fzqywbqm2
into #temp_guishumugongsi2222
from
(
select *
from #temp_zichanfuzhaidibiao444
where 1=1
and (fzqyindexname like '%少数股东权益%' )
) as t
left join
(
select *
from #temp_zichanfuzhaidibiao444
where 1=1
and (fzqyindexname like '所有者权益%计' )
) as t1 on t.tablename=t1.tablename and t.dbtype=t1.dbtype and t.companyname=t1.companyname and t.accidname=t1.accidname and t.iyear=t1.iyear and t.iperiod=t1.iperiod and t.isbook=t1.isbook
left join #temp_InvestmentParent000 as t2 on t.dbtype=t2.dbtype and t.companyname=t2.companyname and t.iyear=t2.iyear and t.iperiod=t2.iperiod
select *
from
(
select tablename,dbtype,dbname,companyname,accidname,hbcompanyname,iyear,iperiod,ruleversion,rownum,zcindexname,zcindexordernum,zclogictype,fzqyindexname,fzqyindexordernum,fzqylogictype,isbook,
zclocalnc,zclocalqm,zclocalqc,zcwbnc,zcwbqm,zcwbqc,fzqylocalnc,fzqylocalqm,fzqylocalqc,fzqywbnc,fzqywbqm,fzqywbqc,update_time
from #temp_zichanfuzhaidibiao444
where 1=1
and (fzqyindexname not like '%归属母公司所有者权益%' and fzqyindexname not like '少数股东权益%' )
union all
select tablename,dbtype,dbname,companyname,accidname,hbcompanyname,iyear,iperiod,ruleversion,rownum,zcindexname,zcindexordernum,zclogictype,fzqyindexname,fzqyindexordernum,fzqylogictype,isbook,
zclocalnc,zclocalqm,zclocalqc,zcwbnc,zcwbqm,zcwbqc,
case when companyParent is null then NULL else fzqylocalnc2 end as fzqylocalnc,
case when companyParent is null then NULL else fzqylocalqm2 end as fzqylocalqm,
case when companyParent is null then NULL else fzqylocalqc2 end as fzqylocalqc,
case when companyParent is null then NULL else fzqywbnc2 end as fzqywbnc,
case when companyParent is null then NULL else fzqywbqm2 end as fzqywbqm,
case when companyParent is null then NULL else fzqywbqc2 end as fzqywbqc,
update_time
from #temp_guishumugongsi1111
union all
select tablename,dbtype,dbname,companyname,accidname,hbcompanyname,iyear,iperiod,ruleversion,rownum,zcindexname,zcindexordernum,zclogictype,fzqyindexname,fzqyindexordernum,fzqylogictype,isbook,
zclocalnc,zclocalqm,zclocalqc,zcwbnc,zcwbqm,zcwbqc,
case when companyParent is null then NULL else fzqylocalnc2 end as fzqylocalnc,
case when companyParent is null then NULL else fzqylocalqm2 end as fzqylocalqm,
case when companyParent is null then NULL else fzqylocalqc2 end as fzqylocalqc,
case when companyParent is null then NULL else fzqywbnc2 end as fzqywbnc,
case when companyParent is null then NULL else fzqywbqm2 end as fzqywbqm,
case when companyParent is null then NULL else fzqywbqc2 end as fzqywbqc,
update_time
from #temp_guishumugongsi2222
) as mm
drop table #temp_VW_CO_JC_BalanceSheet_zb
drop table #temp_VW_CO_JC_BalanceSheet_by
drop table #temp_CO_JC_Ccode
drop table #temp_VW_CO_JC_ERP_Balance
drop table #temp_zichanfuzhaidibiao111
drop table #temp_zichanfuzhaidibiao222
drop table #temp_zichanfuzhaidibiao333
drop table #temp_zichanfuzhaidibiao444
drop table #temp_InvestmentParent000
drop table #temp_guishumugongsi1111
drop table #temp_guishumugongsi2222
end
扩展2：