表1：科目余额表_自动抵消穿透

更新日期：2022-05-29

  1. begin 
  3. select distinct accidname,companyname,hbcompanyname 
  4. into  #temp_CO_JC_Account 
  5. from [dbo].[CO_JC_Account] 
  6. where 1=1 
  7. and hbcompanyname in ($(合并报表_本方单位))
  10. select  tt.*,t1.igrade,t1.ccode as ccode2 
  11. into #temp_kemuyuechuantou111
  12. from 
  13. (
  15.         select  dbtype,dbName ,companyName,accidname,iyear,iperiod,isbook,ruleversion,rownum,indexname,ccode,jdfx,jsfs,hesuan
  16.         from [dbo].[VW_CO_JC_BalanceSheet_zb] 
  17.         where 1=1 
  18.         and  accidname in (select accidname from #temp_CO_JC_Account) 
  19.         and  iyear=$(合并报表-年度)
  20.         and  iperiod=(case when $(合并报表-期间) =0 then 1 else $(合并报表-期间) end )
  21.         and  isbook='$(合并报表-是否包含未记账)'  
  22.         $(合并报表_报表行号) 
  23.         $(合并报表_指标名称2)  
  25.         union all 
  27.         select  dbtype,dbName ,companyName,accidname,iyear,iperiod,isbook,ruleversion,rownum,indexname,ccode,jdfx,jsfs,NULL as hesuan  
  28.         from [dbo].[VW_CO_JC_Profit_zb]
  29.         where 1=1 
  30.         and  accidname in (select accidname from #temp_CO_JC_Account) 
  31.         and  iyear=$(合并报表-年度)
  32.         and  iperiod=(case when $(合并报表-期间) =0 then 1 else $(合并报表-期间) end )
  33.         and  isbook='$(合并报表-是否包含未记账)'  
  34.         $(合并报表_报表行号) 
  35.         $(合并报表_指标名称2)  
  37. )as tt  
  38. left join 
  39. (
  40.         select distinct dbtype,dbName ,companyName,accidname,ccode,igrade,iyear
  41.         from CO_JC_Ccode  
  42.         where 1=1 
  43.         and  accidname in (select accidname from #temp_CO_JC_Account) 
  44.         and  iyear=$(合并报表-年度) 
  45. ) as t1 on  tt.dbtype=t1.dbtype and tt.accidname=t1.accidname and tt.iyear=t1.iyear and left(t1.ccode,len(tt.ccode))=tt.ccode  
  46. where 1=1 
  47. and (case when len(isnull(tt.jdfx,N''))<1  and len(isnull(tt.hesuan,N''))<1 then tt.ccode else t1.ccode end) =t1.ccode
  51. select 
  52. cast(tt.iyear as varchar(10))+'年'+cast(tt.iperiod as varchar(4))+'月' as iyperiod,
  53. tt.dbtype,tt.dbName ,tt.companyName,tt.accidname,tt.iyear,tt.iperiod,
  54. accountTypeID,accountTypeName,accountCode,
  55. currencyCode,currencyName,igrade,
  56. case when igrade=6 then N'     '+accountCode2 when igrade=5 then N'    '+accountCode2 when igrade=4 then N'   '+accountCode2 when igrade=3 then N'  '+accountCode2 when igrade=2 then N' '+accountCode2  
  57. else accountCode2 end  as accountCode2,
  58. accountName,ccodefx,
  59. case when isdetail=0 then 
  60. (case when igrade=6 then N'     '+accountName when igrade=5 then N'    '+accountName when igrade=4 then N'   '+accountName when igrade=3 then N'  '+accountName when igrade=2 then N' '+accountName  
  61. else accountName end)   else (case when igrade=6 then N'     '+accountName2 when igrade=5 then N'    '+accountName2 when igrade=4 then N'   '+accountName2 when igrade=3 then N'  '+accountName2 when igrade=2 then N' '+accountName2  
  62. else accountName2 end) end as accountName2,
  63. localqc,localjfqc,localdfqc,localjffs,localdffs,localytdjffs,localytddffs,localqm,localjfqm,localdfqm  
  64. from 
  65. (
  66.         SELECT 
  67.         dbtype,dbName ,companyName,accidname,iyear,iperiod,isbook,
  68.         accountTypeID,accountTypeCode,accountTypeName,calDirection,
  69.         --currencyID,accountID,
  70.         currencyCode,currencyName,
  71.         accountCode,accountName,
  72.         (Case When len(isnull(isauxaccdepartmentcode,N''))<1 then  N'' Else  N' [' + isnull(isauxaccdepartmentcode,N'') +  N'] ' End) + ISNULL(isauxaccdepartment, N'') 
  73.         + (Case When len(isnull(isauxaccpersoncode,N''))<1 then  N'' Else  N' [' + isnull(isauxaccpersoncode,N'') +  N'] ' End) + ISNULL(isauxaccperson, N'') 
  74.         + (Case When len(isnull(isauxacccustomercode,N''))<1 then  N'' Else  N' [' + isnull(isauxacccustomercode,N'') +  N'] ' End) + ISNULL(isauxacccustomer, N'') 
  75.         + (Case When len(isnull(isauxaccinventorycode,N''))<1 then  N'' Else  N' [' + isnull(isauxaccinventorycode,N'') +  N'] ' End) + ISNULL(isauxaccinventory, N'')  
  76.         + (Case When len(isnull(isauxaccprojectcode,N''))<1 then  N'' Else  N' [' + isnull(isauxaccprojectcode,N'') +  N'] ' End) + ISNULL(isauxaccproject, N'')  
  77.         + (Case When len(isnull(isexauxacc1code,N''))<1 then  N'' Else  N' [' + isnull(isexauxacc1code,N'') +  N'] ' End) + ISNULL(isexauxacc1, N'')  
  78.         + (Case When len(isnull(isexauxacc2code,N''))<1 then  N'' Else  N' [' + isnull(isexauxacc2code,N'') +  N'] ' End) + ISNULL(isexauxacc2, N'')  
  79.         + (Case When len(isnull(isexauxacc3code,N''))<1 then  N'' Else  N' [' + isnull(isexauxacc3code,N'') +  N'] ' End) + ISNULL(isexauxacc3, N'')  
  80.         + (Case When len(isnull(isexauxacc4code,N''))<1 then  N'' Else  N' [' + isnull(isexauxacc4code,N'') +  N'] ' End) + ISNULL(isexauxacc4, N'')  
  81.         + (Case When len(isnull(isexauxacc5code,N''))<1 then  N'' Else  N' [' + isnull(isexauxacc5code,N'') +  N'] ' End) + ISNULL(isexauxacc5, N'')  
  82.         + (Case When len(isnull(isexauxacc6code,N''))<1 then  N'' Else  N' [' + isnull(isexauxacc6code,N'') +  N'] ' End) + ISNULL(isexauxacc6, N'')  
  83.         + (Case When len(isnull(isexauxacc7code,N''))<1 then  N'' Else  N' [' + isnull(isexauxacc7code,N'') +  N'] ' End) + ISNULL(isexauxacc7, N'')  
  84.         + (Case When len(isnull(isexauxacc8code,N''))<1 then  N'' Else  N' [' + isnull(isexauxacc8code,N'') +  N'] ' End) + ISNULL(isexauxacc8, N'')  
  85.         + (Case When len(isnull(isexauxacc9code,N''))<1 then  N'' Else  N' [' + isnull(isexauxacc9code,N'') +  N'] ' End) + ISNULL(isexauxacc9, N'')  
  86.         + (Case When len(isnull(isexauxacc10code,N''))<1 then  N'' Else  N' [' + isnull(isexauxacc10code,N'') +  N'] ' End) + ISNULL(isexauxacc10, N'')  as accountName2,
  87.         case when accountDcdirection='652' then '借' else '贷' end as ccodefx,
  88.         --accountID2,accountUnit,
  89.         case when isdetail=0 then accountCode else N'' end as accountCode2,
  90.         isEndNode,isdetail,
  91.         isauxacccustomer,isauxaccdepartment,
  92.         isauxaccperson,isauxaccproject,isauxaccinventory,
  93.         isexauxacc1,isexauxacc2,isexauxacc3,isexauxacc4,isexauxacc5,
  94.         isexauxacc6,isexauxacc7,isexauxacc8,isexauxacc9,isexauxacc10,
  95.         --quantityBalance,origAmountBalance,
  96.         amountBalance as localqc,
  97.         --case when amountBalance>0 then '借' when amountBalance<0 then '贷' else '平' end as localqcfx,
  98.         case when amountBalance>0 then amountBalance  else 0 end as localjfqc,
  99.         case when amountBalance<0 then abs(amountBalance)  else 0 end as localdfqc,
  100.         --quantityDr,quantityCr,origAmountDr,origAmountCr,
  101.         amountDr as localjffs,
  102.         amountCr as localdffs,
  103.         --cumQuantityDr,cumQuantityCr,
  104.         --cumOrigAmountDr,cumOrigAmountCr,
  105.         cumAmountDr as localytdjffs,cumAmountCr as localytddffs,
  106.         --endquantityBalance,endorigAmountBalance,
  107.         endamountBalance as localqm,
  108.         case when endamountBalance>0 then endamountBalance  else 0 end as localjfqm,
  109.         case when endamountBalance<0 then abs(endamountBalance)  else 0 end as localdfqm 
  110.         FROM CO_JC_ERP_Balance  
  111.         where 1=1 
  112.         and  accidname in (select accidname from #temp_CO_JC_Account) 
  113.         and  ISNULL(isauxacccustomer, N'') in ($(合并报表_对方单位))
  114.         and  iyear=$(合并报表-年度)
  115.         and  iperiod=(case when $(合并报表-期间) =0 then 1 else $(合并报表-期间) end )  
  116.         and  isbook='$(合并报表-是否包含未记账)' 
  119. ) as tt  
  120. inner join #temp_kemuyuechuantou111  as t1 on  tt.dbtype=t1.dbtype and tt.accidname=t1.accidname and tt.iyear=t1.iyear  
  121. and  left(tt.accountcode,len(t1.ccode2))=t1.ccode2 
  122. --and tt.accountcode=t1.ccode2 
  123. order by accounttypecode,accountcode,igrade, accountName2 
  127. drop table #temp_kemuyuechuantou111  
  128. drop table #temp_CO_JC_Account  
  131. end