《个表指标穿透联查》

表1：《科目余额表-个表穿透》

begin

---------------------------基础数据begin-----------------
--匹配需要科目指标
select *
into #temp_VW_CO_JC_BalanceSheet_zb  
from
(
    select t.tablename,t.dbtype,t.dbname,t.accidname,t.companyname,t.iyear,t.iPeriod,t.isbook,t.ruleversion,t.indextype,t.rownum,
    t.hesuan,t.ccode,t.ccodefx,t.jdfx,t.jsfs,t.indexname
    from VW_CO_JC_BalanceSheet_zb as t  
    where 1=1
        and  accidname in ($(合并报表-账套名称))
        and  iyear=$(合并报表-年度)
        and  iperiod=$(合并报表-期间)
        and  isbook='$(合并报表-是否包含未记账)'  
        $(合并报表_报表行号)
        $(合并报表_指标名称)   

    union all
    select t.tablename,t.dbtype,t.dbname,t.accidname,t.companyname,t.iyear,t.iPeriod,t.isbook,t.ruleversion,'',t.rownum,
    '',t.ccode,t.jdfx as ccodefx,t.jdfx,t.jsfs,t.indexname
    from VW_CO_JC_Profit_zb as t  
    where 1=1
        and  accidname in ($(合并报表-账套名称))
        and  iyear=$(合并报表-年度)
        and  iperiod=$(合并报表-期间)
        and  isbook='$(合并报表-是否包含未记账)'  
        $(合并报表_报表行号)
        $(合并报表_指标名称)   


) as mm



--科目档案
select t.*  
into #temp_CO_JC_Ccode
from CO_JC_Ccode as t
where 1=1 
and  accidname in ($(合并报表-账套名称))
and  iyear=$(合并报表-年度)




--科目余额表
select t.dbtype,t.dbname,t.iyear,t.iperiod,isbook,ccode,ccodename,fuzhuhesuan,isdetail,
                 case when isdetail=0 then 0 else 999 end as isdetail2,
                (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,
                           localjffs,localdffs,localjfytdfs,localdfytdfs,
                (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,
                           wbjffs,wbdffs,wbjfytdfs,wbdfytdfs,
                (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
into #kemuye
from VW_CO_JC_ERP_Balance t
where 1=1 
and  accidname in ($(合并报表-账套名称))
and  iyear=$(合并报表-年度)
and  iperiod=$(合并报表-期间)
and  isbook='$(合并报表-是否包含未记账)'  

---------------------------基础数据end-----------------

--需要末级的
select a.tablename,a.dbtype,a.dbname,a.accidname,a.companyname,
a.iyear,a.iPeriod,a.isbook,a.ruleversion,a.indextype,a.rownum,
a.ccode as 指标科目,a.ccodefx as 指标科目方向,isnull(a.jdfx,'') 指定方向,
b.ccode as 末级科目,b.ccodename as 末级科目名称,
case when b.ccodefx='1' then '借' else '贷' end as 末级科目方向,a.jsfs,indexname,
case when hesuan='重分类' then '1' else '0' end as isdetail
into #moji
from #temp_VW_CO_JC_BalanceSheet_zb as a
inner join #temp_CO_JC_Ccode as b
on a.dbname=b.dbname and a.dbtype=b.dbtype and a.iyear=b.iyear
and a.ccode=left(b.ccode,len(a.ccode)) and b.bend=1
where isnull(a.jdfx,'') not in('自借','自贷','自') --and rownum=35 and iPeriod=1 and  isbook='是' and a.indextype='fzqyindexname'

--不需要末级的（自借 自贷）
select a.tablename,a.dbtype,a.dbname,a.accidname,a.companyname,
a.iyear,a.iPeriod,a.isbook,a.ruleversion,a.indextype,a.rownum,
a.ccode as 指标科目,b.ccodename as 科目名称,a.ccodefx as 指标科目方向,isnull(a.jdfx,'') 指定方向,a.jsfs,a.indexname
into #zijiezidai
from #temp_VW_CO_JC_BalanceSheet_zb as a
inner join #temp_CO_JC_Ccode as b
on a.dbname=b.dbname and a.dbtype=b.dbtype and a.iyear=b.iyear and a.ccode=b.ccode
where isnull(a.jdfx,'') in('自借','自贷') and isnull(hesuan,'')=''

--不需要末级的（自）
select a.tablename,a.dbtype,a.dbname,a.accidname,a.companyname,
a.iyear,a.iPeriod,a.isbook,a.ruleversion,a.indextype,a.rownum,
a.ccode as 指标科目,b.ccodename as 科目名称,a.ccodefx as 指标科目方向,isnull(a.jdfx,'') 指定方向,a.jsfs,a.indexname
into #zishen
from #temp_VW_CO_JC_BalanceSheet_zb as a
inner join #temp_CO_JC_Ccode as b
on a.dbname=b.dbname and a.dbtype=b.dbtype and a.iyear=b.iyear  and a.ccode=b.ccode
where isnull(a.jdfx,'') in('自') and isnull(hesuan,'')=''


--需要重算辅助核算的
select a.tablename,a.dbtype,a.dbname,a.accidname,a.companyname,
a.iyear,a.iPeriod,a.isbook,a.ruleversion,a.indextype,a.rownum,
a.ccode as 指标科目,a.ccodefx as 指标科目方向,isnull(a.jdfx,'') 指定方向,
b.ccode as 带辅助核算科目,case when b.ccodefx='1' then '借' else '贷' end as 带辅助核算科目方向 ,a.jsfs,
indexname
into #zjzdcfl
from #temp_VW_CO_JC_BalanceSheet_zb as a
inner join #temp_CO_JC_Ccode as b
on a.dbname=b.dbname and a.dbtype=b.dbtype and a.iyear=b.iyear
and a.ccode=left(b.ccode,len(a.ccode)) 
where isnull(a.jdfx,'')  in('自借','自贷') and isnull(hesuan,'')<>''

-----------------根据不同类型的函数分别找出需要的科目end 分别计算余额begin------------------
--计算末级的
select * from (
select ff.tablename,ff.dbtype,ff.dbname,ff.accidname,ff.companyname,
ff.iyear,ff.iPeriod,ff.ruleversion,ff.isbook,ff.indextype,ff.indexname,
ff.rownum,jsfs,ff.指定方向,ff.末级科目 as accountCode,ff.末级科目名称 as accountName,f1.fuzhuhesuan as accountName2,
ff.末级科目方向 as ccodefx,
localjfqc,localdfqc,localjffs,localdffs,localjfytdfs,localdfytdfs,localjfqm,localdfqm,
case when jsfs='减' and isnull(ff.指定方向,'')=''  then localqc*-1 
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.末级科目方向='借'  and localjfqc>0 then localjfqc*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.末级科目方向='贷'  and localdfqc>0 then localdfqc*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.末级科目方向='借'  and localdfqc>0 then localdfqc*-1*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.末级科目方向='贷'  and localjfqc>0 then localjfqc*-1*-1
	 when jsfs='减' and right(isnull(ff.指定方向,''),1)='借'  then localjfqc*-1 
     when jsfs='减' and right(isnull(ff.指定方向,''),1)='贷'  then localdfqc*-1 
	 when jsfs='加' and isnull(ff.指定方向,'')='' then localqc

	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.末级科目方向='借'  and localjfqc>0 then localjfqc
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.末级科目方向='贷'  and localdfqc>0 then localdfqc
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.末级科目方向='借'  and localdfqc>0 then localdfqc*-1
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.末级科目方向='贷'  and localjfqc>0 then localjfqc*-1
	 when jsfs='加' and right(isnull(ff.指定方向,''),1)='借'  then localjfqc
     when jsfs='加' and right(isnull(ff.指定方向,''),1)='贷'  then localdfqc
	 else 0 end as js_localqc,
case when jsfs='减' and isnull(ff.指定方向,'')=''  then localqm*-1 
     
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.末级科目方向='借'  and localjfqm>0 then localjfqm*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.末级科目方向='贷'  and localdfqm>0 then localdfqm*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.末级科目方向='借'  and localdfqm>0 then localdfqm*-1*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.末级科目方向='贷'  and localjfqm>0 then localjfqm*-1*-1
	 when jsfs='减' and right(isnull(ff.指定方向,''),1)='借'  then localjfqm*-1 
     when jsfs='减' and right(isnull(ff.指定方向,''),1)='贷'  then localdfqm*-1 
	 when jsfs='加' and isnull(ff.指定方向,'')='' then localqm
     
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.末级科目方向='借'  and localjfqm>0 then localjfqm
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.末级科目方向='贷'  and localdfqm>0 then localdfqm
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.末级科目方向='借'  and localdfqm>0 then localdfqm*-1
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.末级科目方向='贷'  and localjfqm>0 then localjfqm*-1
	 when jsfs='加' and right(isnull(ff.指定方向,''),1)='借'  then localjfqm
     when jsfs='加' and right(isnull(ff.指定方向,''),1)='贷'  then localdfqm
	 else 0 end as js_localqm,
case when jsfs='减' and right(isnull(ff.指定方向,''),1)='借' then localjffs*-1 
	 when jsfs='加' and right(isnull(ff.指定方向,''),1)='借' then localjffs 
	 when jsfs='减' and right(isnull(ff.指定方向,''),1)='贷' then localdffs*-1
	 when jsfs='加' and right(isnull(ff.指定方向,''),1)='贷' then localdffs
	 else 0 end as js_localfs
from #moji as ff
inner join #kemuye as f1
on ff.iperiod=f1.iperiod  and ff.isbook=f1.isbook  and ff.iyear=f1.iyear and ff.末级科目=f1.ccode 
and ff.dbtype=f1.dbtype and  ff.dbname=f1.dbName and ff.isdetail=f1.isdetail
) as a
where case when  tablename='资产负债表' and (js_localqc<>0 or js_localqm<>0) then 0 
	       when  tablename='利润表' and (js_localfs<>0 or js_localqc<>0 or js_localqm<>0) then 0	
else 1 end=0


union all
select  ff.tablename,ff.dbtype,ff.dbname,ff.accidname,ff.companyname,
ff.iyear,ff.iPeriod,ff.ruleversion,ff.isbook,ff.indextype,ff.indexname,
ff.rownum,ff.jsfs,ff.指定方向,ff.指标科目 as accountCode,ff.科目名称 as accountName,
fuzhuhesuan as accountName2,ff.指标科目方向 as ccodefx,
localjfqc,localdfqc,localjffs,localdffs,localjfytdfs,localdfytdfs,localjfqm,localdfqm,
case when jsfs='减' and isnull(ff.指定方向,'')='' then localqc*-1 
     
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.指定方向='借'  and localjfqc>0 then localjfqc*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.指定方向='贷'  and localdfqc>0 then localdfqc*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.指定方向='借'  and localdfqc>0 then localdfqc*-1*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.指定方向='贷'  and localjfqc>0 then localjfqc*-1*-1
	 when jsfs='减' and right(isnull(ff.指定方向,''),1)='借'  then localjfqc*-1 
     when jsfs='减' and right(isnull(ff.指定方向,''),1)='贷'  then localdfqc*-1 
	 when jsfs='加' and isnull(ff.指定方向,'')='' then localqc
    
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.指定方向='借'  and localjfqc>0 then localjfqc
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.指定方向='贷'  and localdfqc>0 then localdfqc
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.指定方向='借'  and localdfqc>0 then localdfqc*-1
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.指定方向='贷'  and localjfqc>0 then localjfqc*-1
	 when jsfs='加' and right(isnull(ff.指定方向,''),1)='借'  then localjfqc
     when jsfs='加' and right(isnull(ff.指定方向,''),1)='贷'  then localdfqc
	 else 0 end as js_localqc,
case when jsfs='减' and isnull(ff.指定方向,'')='' then localqm*-1 
     
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.指定方向='借'  and localjfqm>0 then localjfqm*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.指定方向='贷'  and localdfqm>0 then localdfqm*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.指定方向='借'  and localdfqm>0 then localdfqm*-1*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.指定方向='贷'  and localjfqm>0 then localjfqm*-1*-1
	 when jsfs='减' and right(isnull(ff.指定方向,''),1)='借'  then localjfqm*-1 
     when jsfs='减' and right(isnull(ff.指定方向,''),1)='贷'  then localdfqm*-1 
	 when jsfs='加' and isnull(ff.指定方向,'')=''  then localqm
    
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.指定方向='借'  and localjfqm>0 then localjfqm
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.指定方向='贷'  and localdfqm>0 then localdfqm
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.指定方向='借'  and localdfqm>0 then localdfqm*-1
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.指定方向='贷'  and localjfqm>0 then localjfqm*-1
	 when jsfs='加' and right(isnull(ff.指定方向,''),1)='借'  then localjfqm
     when jsfs='加' and right(isnull(ff.指定方向,''),1)='贷'  then localdfqm
	 else 0 end as js_localqm,
case when jsfs='减' and right(isnull(ff.指定方向,''),1)='借' then localjffs*-1 
	 when jsfs='加' and right(isnull(ff.指定方向,''),1)='借' then localjffs 
	 when jsfs='减' and right(isnull(ff.指定方向,''),1)='贷' then localdffs*-1
	 when jsfs='加' and right(isnull(ff.指定方向,''),1)='贷' then localdffs
	 else 0 end as js_localfs
from #zijiezidai as ff
inner join #kemuye as f1
on ff.iperiod=f1.iperiod  and ff.isbook=f1.isbook  and ff.iyear=f1.iyear and ff.指标科目=f1.ccode
and ff.dbtype=f1.dbtype and  ff.dbname=f1.dbName
where f1.isdetail=0

union all
select ff.tablename,ff.dbtype,ff.dbname,ff.accidname,ff.companyname,
ff.iyear,ff.iPeriod,ff.ruleversion,ff.isbook,ff.indextype,ff.indexname,
ff.rownum,ff.jsfs,ff.指定方向,ff.指标科目 as accountCode,ff.科目名称 as accountName,
fuzhuhesuan as accountName2,ff.指标科目方向 as ccodefx,
localjfqc,localdfqc,localjffs,localdffs,localjfytdfs,localdfytdfs,localjfqm,localdfqm,
case when jsfs='减' and isnull(ff.指定方向,'')=''  then localqc*-1 
     
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.指定方向='借'  and localjfqc>0 then localjfqc*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.指定方向='贷'  and localdfqc>0 then localdfqc*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.指定方向='借'  and localdfqc>0 then localdfqc*-1*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.指定方向='贷'  and localjfqc>0 then localjfqc*-1*-1
	 when jsfs='减' and right(isnull(ff.指定方向,''),1)='借'  then localjfqc*-1 
     when jsfs='减' and right(isnull(ff.指定方向,''),1)='贷'  then localdfqc*-1 
	 when jsfs='加' and isnull(ff.指定方向,'')=''  then localqc
    
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.指定方向='借'  and localjfqc>0 then localjfqc
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.指定方向='贷'  and localdfqc>0 then localdfqc
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.指定方向='借'  and localdfqc>0 then localdfqc*-1
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.指定方向='贷'  and localjfqc>0 then localjfqc*-1
	 when jsfs='加' and right(isnull(ff.指定方向,''),1)='借'  then localjfqc
     when jsfs='加' and right(isnull(ff.指定方向,''),1)='贷'  then localdfqc
	 else 0 end as js_localqc,
case when jsfs='减' and isnull(ff.指定方向,'')=''  then localqm*-1 
     
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.指定方向='借'  and localjfqm>0 then localjfqm*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.指定方向='贷'  and localdfqm>0 then localdfqm*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.指定方向='借'  and localdfqm>0 then localdfqm*-1*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.指定方向='贷'  and localjfqm>0 then localjfqm*-1*-1
	 when jsfs='减' and right(isnull(ff.指定方向,''),1)='借'  then localjfqm*-1 
     when jsfs='减' and right(isnull(ff.指定方向,''),1)='贷'  then localdfqm*-1 
	 when jsfs='加' and isnull(ff.指定方向,'')=''  then localqm
     
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.指定方向='借'  and localjfqm>0 then localjfqm
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.指定方向='贷'  and localdfqm>0 then localdfqm
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.指定方向='借'  and localdfqm>0 then localdfqm*-1
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.指定方向='贷'  and localjfqm>0 then localjfqm*-1
	 when jsfs='加' and right(isnull(ff.指定方向,''),1)='借'  then localjfqm
     when jsfs='加' and right(isnull(ff.指定方向,''),1)='贷'  then localdfqm
	 else 0 end as js_localqm,
case when jsfs='减' and right(isnull(ff.指定方向,''),1)='借' then localjffs*-1 
	 when jsfs='加' and right(isnull(ff.指定方向,''),1)='借' then localjffs 
	 when jsfs='减' and right(isnull(ff.指定方向,''),1)='贷' then localdffs*-1
	 when jsfs='加' and right(isnull(ff.指定方向,''),1)='贷' then localdffs
	 else 0 end as js_localfs
from #zishen as ff
inner join #kemuye as f1
on ff.iperiod=f1.iperiod  and ff.isbook=f1.isbook  and ff.iyear=f1.iyear and ff.指标科目=f1.ccode
and ff.dbtype=f1.dbtype and  ff.dbname=f1.dbName
where f1.isdetail=0


union all
--计算自借自贷还要重分类的
select ff.tablename,ff.dbtype,ff.dbname,ff.accidname,ff.companyname,
ff.iyear,ff.iPeriod,ff.ruleversion,ff.isbook,ff.indextype,ff.indexname,
ff.rownum,jsfs,ff.指定方向,ff.带辅助核算科目 as accountCode,f1.ccodename as accountName,f1.fuzhuhesuan as accountName2,
ff.带辅助核算科目方向 as ccodefx,localjfqc,localdfqc,localjffs,localdffs,localjfytdfs,localdfytdfs,localjfqm,localdfqm,
case when jsfs='减' and isnull(ff.指定方向,'')=''  then localqc*-1 
    
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.带辅助核算科目方向='借'  and localjfqc>0 then localjfqc*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.带辅助核算科目方向='贷'  and localdfqc>0 then localdfqc*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.带辅助核算科目方向='借'  and localdfqc>0 then localdfqc*-1*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.带辅助核算科目方向='贷'  and localjfqc>0 then localjfqc*-1*-1
	 when jsfs='减' and right(isnull(ff.指定方向,''),1)='借'  then localjfqc*-1 
     when jsfs='减' and right(isnull(ff.指定方向,''),1)='贷'  then localdfqc*-1 
	 when jsfs='加' and isnull(ff.指定方向,'')=''  then localqc
    
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.带辅助核算科目方向='借'  and localjfqc>0 then localjfqc
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.带辅助核算科目方向='贷'  and localdfqc>0 then localdfqc
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.带辅助核算科目方向='借'  and localdfqc>0 then localdfqc*-1
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.带辅助核算科目方向='贷'  and localjfqc>0 then localjfqc*-1
	 when jsfs='加' and right(isnull(ff.指定方向,''),1)='借'  then localjfqc
     when jsfs='加' and right(isnull(ff.指定方向,''),1)='贷'  then localdfqc
	 else 0 end as js_localqc,
case when jsfs='减' and isnull(ff.指定方向,'')='' then localqm*-1 
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.带辅助核算科目方向='借'  and localjfqm>0 then localjfqm*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.带辅助核算科目方向='贷'  and localdfqm>0 then localdfqm*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.带辅助核算科目方向='借'  and localdfqm>0 then localdfqm*-1*-1
	 when jsfs='减' and isnull(ff.指定方向,'')='自' and  ff.带辅助核算科目方向='贷'  and localjfqm>0 then localjfqm*-1*-1
	 when jsfs='减' and right(isnull(ff.指定方向,''),1)='借'  then localjfqm*-1 
     when jsfs='减' and right(isnull(ff.指定方向,''),1)='贷'  then localdfqm*-1 
	 when jsfs='加' and isnull(ff.指定方向,'')=''  then localqm
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.带辅助核算科目方向='借'  and localjfqm>0 then localjfqm
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.带辅助核算科目方向='贷'  and localdfqm>0 then localdfqm
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.带辅助核算科目方向='借'  and localdfqm>0 then localdfqm*-1
	 when jsfs='加' and isnull(ff.指定方向,'')='自' and  ff.带辅助核算科目方向='贷'  and localjfqm>0 then localjfqm*-1
	 when jsfs='加' and right(isnull(ff.指定方向,''),1)='借'  then localjfqm
     when jsfs='加' and right(isnull(ff.指定方向,''),1)='贷'  then localdfqm
	 else 0 end as js_localqm,
case when jsfs='减' and right(isnull(ff.指定方向,''),1)='借' then localjffs*-1 
	 when jsfs='加' and right(isnull(ff.指定方向,''),1)='借' then localjffs 
	 when jsfs='减' and right(isnull(ff.指定方向,''),1)='贷' then localdffs*-1
	 when jsfs='加' and right(isnull(ff.指定方向,''),1)='贷' then localdffs
	 else 0 end as js_localfs
from #zjzdcfl as ff
inner join #kemuye as f1
on ff.iperiod=f1.iperiod  and ff.isbook=f1.isbook  and ff.iyear=f1.iyear and ff.带辅助核算科目=f1.ccode
and ff.dbtype=f1.dbtype and  ff.dbname=f1.dbName 
where f1.fuzhuhesuan<>''



drop table #temp_VW_CO_JC_BalanceSheet_zb
drop table #temp_CO_JC_Ccode,#kemuye,#moji,#zjzdcfl,#zijiezidai,#zishen


end