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

更新日期：2022-05-29

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