个人卡付款sql

select   arap_djfb.billdate as 单据日期,
       bd_corp.unitname as 付款单位,
       arap_djfb.zy as 付款摘要,
       bd_cubasdoc.custcode as 收款单位编码,
       bd_cubasdoc.custname as 收款单位名称,
       bd_accbank.unitname 收款单位名称,
       arap_djfb.bbye as 付款金额,
       --arap_djfb.fkyhmc as 付款银行名称,
       (select bankacc from bd_accbank where pk_accbank = arap_djfb.fkyhzh) 付款银行账号,
        
      -- bd_accbank.bankname as 收款银行名称,
      -- bd_accbank.bankacc as 收款银行账号,
       arap_djzb.djbh 单据编号,
       arap_djfb.payflag
  from bd_cubasdoc, arap_djfb, bd_cumandoc, arap_djzb, bd_corp, bd_accbank
 where arap_djfb.ksbm_cl = bd_cumandoc.pk_cumandoc
   and bd_cumandoc.pk_cubasdoc = bd_cubasdoc.pk_cubasdoc
   and arap_djfb.vouchid = arap_djzb.vouchid
   and bd_corp.pk_corp = arap_djzb.dwbm
   and bd_accbank.pk_accbank = arap_djfb.skyhzh
   and arap_djfb.payflag in ('1', '2')
   and arap_djfb.dr = '0'
   --and (select bankacc from bd_accbank where pk_accbank = arap_djfb.fkyhzh) ='32001735038059899999'
    --and bd_corp.unitcode='011701'
   --arap_djfb.billdate='2012-01-09'
   --and bd_cubasdoc.custname ='江苏力山建设工程有限公司'
   --and bd_cubasdoc.custcode = '17051211693'
   and  substr(arap_djfb.billdate,1,4) >='2012'
   and length( bd_accbank.unitname)<=4  --需要用开户银行的单位名称不能用客商名称
 
 order by bd_corp.unitcode, arap_djfb.paydate 

像山东公司这种行为，bs

 

2013-08-26 15:04:49 加入个人卡在每个公司总的个人卡付款中的比例

通过DJLXBM的23A0 23A1来确定单据类型

select 
       bd_corp.unitname as 付款单位,
        arap_djfb.billdate as 单据日期,
       arap_djfb.zy as 付款摘要,
       bd_cubasdoc.custcode as 收款单位编码,
       bd_cubasdoc.custname as 收款单位名称,
       bd_accbank.unitname 收款单位名称,
       trunc(arap_djfb.bbye,2) as 付款金额,
       --arap_djfb.fkyhmc as 付款银行名称,
      -- (select bankacc from bd_accbank where pk_accbank = arap_djfb.fkyhzh) 付款银行账号,
         
      -- bd_accbank.bankname as 收款银行名称,
      -- bd_accbank.bankacc as 收款银行账号,
trunc((sum(arap_djfb.bbye)over(partition by bd_corp.unitname, bd_accbank.unitname)/ sum(arap_djfb.bbye)over(partition by bd_corp.unitname))*100,2)个人付款比例,
       (case arap_djzb.djlxbm when '23A0' then '当地行'  when '23A1' THEN '委托中心' else  null end)单据类型,
       arap_djzb.djbh 单据编号,
       arap_djfb.payflag
  from bd_cubasdoc, arap_djfb, bd_cumandoc, arap_djzb, bd_corp, bd_accbank
 where arap_djfb.ksbm_cl = bd_cumandoc.pk_cumandoc
   and bd_cumandoc.pk_cubasdoc = bd_cubasdoc.pk_cubasdoc
   and arap_djfb.vouchid = arap_djzb.vouchid
   and bd_corp.pk_corp = arap_djzb.dwbm
   and bd_accbank.pk_accbank = arap_djfb.skyhzh
   and arap_djfb.payflag in ('1', '2')
   and arap_djfb.dr = '0'
   --and (select bankacc from bd_accbank where pk_accbank = arap_djfb.fkyhzh) ='32001735038059899999'
    --and bd_corp.unitcode='011701'
   --arap_djfb.billdate='2012-01-09'
   --and bd_cubasdoc.custname ='江苏力山建设工程有限公司'
   --and bd_cubasdoc.custcode = '17051211693'
   and  substr(arap_djfb.billdate,1,4) >='2012'
   and length( bd_accbank.unitname)<=4  --需要用开户银行的单位名称不能用客商名称
  
 order by bd_corp.unitcode,  bd_accbank.unitname,arap_djfb.paydate 

　　

2013-08-26 16:02:32

增加汇款的比例

增加委托中心汇款的个人卡比例

select bd_corp.unitcode,
       bd_corp.unitname as 付款单位,
       -- arap_djfb.billdate as 单据日期,
      -- arap_djfb.zy as 付款摘要,
       --bd_cubasdoc.custcode as 收款单位编码,
       --bd_cubasdoc.custname as 收款单位名称,
       --bd_accbank.unitname 收款单位名称,
       --trunc(arap_djfb.bbye,2) as 付款金额,
       
       --以下是所有
       sum(case when length( bd_accbank.unitname)>4 then arap_djfb.bbye else 0 end  ) 单位汇总 ,
       sum(case when length( bd_accbank.unitname)<=4 then arap_djfb.bbye else 0 end  ) 个人卡汇总,
       sum( arap_djfb.bbye)合计,
       trunc(sum(case when length( bd_accbank.unitname)<=4 then arap_djfb.bbye else 0 end  ) / sum( arap_djfb.bbye)*100,2) 个人卡比例,
      
      
       --以下是委托中心
       sum(case when length( bd_accbank.unitname)>4 and arap_djzb.djlxbm='23A1' then arap_djfb.bbye else 0 end  ) "单位汇总-委托",
       sum(case when length( bd_accbank.unitname)<=4 and arap_djzb.djlxbm='23A1'  then arap_djfb.bbye else 0 end  ) "个人卡汇总-委托" ,
       sum( case when arap_djzb.djlxbm='23A1' then arap_djfb.bbye else 0 end )"合计-委托" , 
       trunc(sum(case when length( bd_accbank.unitname)<=4 and arap_djzb.djlxbm='23A1'then arap_djfb.bbye else 0 end  ) / sum( case when arap_djzb.djlxbm='23A1' then arap_djfb.bbye else 0 end )*100,2) "个人卡比例-委托" 
      
       --arap_djfb.fkyhmc as 付款银行名称,
      -- (select bankacc from bd_accbank where pk_accbank = arap_djfb.fkyhzh) 付款银行账号,
 
      -- bd_accbank.bankname as 收款银行名称,
      -- bd_accbank.bankacc as 收款银行账号,
--trunc((sum(arap_djfb.bbye)over(partition by bd_corp.unitname, bd_accbank.unitname)/ sum(arap_djfb.bbye)over(partition by bd_corp.unitname))*100,2)个人付款比例,
       --arap_djzb.djbh 单据编号,
      -- arap_djfb.payflag
  from bd_cubasdoc, arap_djfb, bd_cumandoc, arap_djzb, bd_corp, bd_accbank
 where arap_djfb.ksbm_cl = bd_cumandoc.pk_cumandoc
   and bd_cumandoc.pk_cubasdoc = bd_cubasdoc.pk_cubasdoc
   and arap_djfb.vouchid = arap_djzb.vouchid
   and bd_corp.pk_corp = arap_djzb.dwbm
   and bd_accbank.pk_accbank = arap_djfb.skyhzh
   and arap_djfb.payflag in ('1', '2')
   and arap_djfb.dr = '0'
   --and (select bankacc from bd_accbank where pk_accbank = arap_djfb.fkyhzh) ='32001735038059899999'
    --and bd_corp.unitcode='011701'
   --arap_djfb.billdate='2012-01-09'
   --and bd_cubasdoc.custname ='江苏力山建设工程有限公司'
   --and bd_cubasdoc.custcode = '17051211693'
   and  substr(arap_djfb.billdate,1,4) >='2012'
   and bd_corp.unitcode<>'0100'
   --and length( bd_accbank.unitname)<=4  --需要用开户银行的单位名称不能用客商名称
   group by bd_corp.unitcode,bd_corp.unitname
 
 order by bd_corp.unitcode

2013-09-18 15:51:57 更新

35%　部分的付款，增加银行单位名称

select 
       bd_corp.unitname as 付款单位,
        arap_djfb.billdate as 单据日期,
          (select bd_accbank.unitname from bd_accbank where pk_accbank = arap_djfb.fkyhzh) 付款账号单位名称,
       arap_djfb.fkyhmc as 付款银行名称,
      (select bankacc from bd_accbank where pk_accbank = arap_djfb.fkyhzh) 付款银行账号,
       arap_djfb.zy as 付款摘要,
         trunc(arap_djfb.bbye,2) as 付款金额,
       bd_cubasdoc.custcode as 收款单位编码,
       --bd_cubasdoc.custname as 收款单位名称,
       bd_accbank.unitname 收款单位名称,
     
     
      
         
      -- bd_accbank.bankname as 收款银行名称,
      -- bd_accbank.bankacc as 收款银行账号,
--trunc((sum(arap_djfb.bbye)over(partition by bd_corp.unitname, bd_accbank.unitname)/ sum(arap_djfb.bbye)over(partition by bd_corp.unitname))*100,2)个人付款比例,
       --(case arap_djzb.djlxbm when '23A0' then '当地行'  when '23A1' THEN '委托中心' else  null end)单据类型,
       arap_djzb.djbh 单据编号,
       arap_djfb.payflag
  from bd_cubasdoc, arap_djfb, bd_cumandoc, arap_djzb, bd_corp, bd_accbank
 where arap_djfb.ksbm_cl = bd_cumandoc.pk_cumandoc
   and bd_cumandoc.pk_cubasdoc = bd_cubasdoc.pk_cubasdoc
   and arap_djfb.vouchid = arap_djzb.vouchid
   and bd_corp.pk_corp = arap_djzb.dwbm
   and bd_accbank.pk_accbank = arap_djfb.skyhzh
   and arap_djfb.payflag in ('1', '2')
   and arap_djfb.dr = '0'
   --and (select bankacc from bd_accbank where pk_accbank = arap_djfb.fkyhzh) ='32001735038059899999'
    --and bd_corp.unitcode='011701'
   --arap_djfb.billdate='2012-01-09'
   --and bd_cubasdoc.custname ='江苏力山建设工程有限公司'
   --and bd_cubasdoc.custcode = '17051211693'
   and  substr(arap_djfb.billdate,1,4) >='2012'--从2012年开始
   --and length( bd_accbank.unitname)<=4  --需要用开户银行的单位名称不能用客商名称
  and arap_djzb.djlxbm='23A0'--当地行支付
  and bd_corp.unitcode<>'0100'
 order by bd_corp.unitcode,  arap_djfb.paydate