create or replace view vw_zy_jkb001 as
select
  a.JKDH00  ,                                     --缴款单号
  a.PJZS00  ,                                     --票据数
  a.PJBH00  ,                                     --票据编号
  a.PJZS00-a.PJZFH0 as YXPJS0,                    --缴款当时有效
  a.HSZFH0 CXPJS0,                                --冲销票据数(缴款后取消结算或者补打)
  a.PJZFH0 ZFPJS0,                                --作废票据数(缴款前取消结算或者补打)
  a.PJQSH0||' - '||a.PJZZH0 as PJQJHM,            --票据区间号码
  a.HJJE00 as FPZJE0,                             --发票金额
  a.ZFJE00 as FPZFJE,                             --发票自付金额
  -1*a.HSHJJE as CXJE00,                          --冲销金额
  a.GFJE00 as JMJE00,                             --减免金额
  (select count(*) from ZY_PJSYQK b where b.PJLYID=a.PJLYID and b.JKDID0=a.JKDH00 and b.PLBH00 in (4,8) and a.PJBH00 in (4,8) 
          and substr(b.PJXH00,1,1)<>'-' and b.SFDY00='Y' and XJJE00+ZZJE00+YHKJE0+DZQBJE>0 and PJZT00 in ('0','1','2')) as YJSRZS,  --预交金收入张数
  (select count(*) from ZY_PJSYQK b where b.PJLYID=a.PJLYID and b.JKDID0=a.JKDH00 and b.PLBH00 in (4,8) and a.PJBH00 in (4,8) 
          and substr(b.PJXH00,1,1)<>'-' and b.SFDY00='Y' and XJJE00+ZZJE00+YHKJE0+DZQBJE<0 and PJZT00 in ('0','1','2')) as YJTCZS,  --预交金退出张数
  a.PJZFH0 as YJZFZS,  --作废票据数
  (select sum(HJJE00) from ZY_PJSYQK b where b.PJLYID=a.PJLYID and b.JKDID0=a.JKDH00 and b.PLBH00 in (4,8) and a.PJBH00 in (4,8) 
          and ((substrb(PJXH00,1,1)='-' and HJJE00<0) or ((substrb(PJXH00,1,1)<>'-' and HJJE00>0))) and PJZT00 in ('0','1','2')) as YJSRJE,  --预交金收入金额
  (select sum(HJJE00) from ZY_PJSYQK b where b.PJLYID=a.PJLYID and b.JKDID0=a.JKDH00 and b.PLBH00 in (4,8) and a.PJBH00 in (4,8) 
          and ((substrb(PJXH00,1,1)='-' and HJJE00>0) or ((substrb(PJXH00,1,1)<>'-' and HJJE00<0))) and PJZT00 in ('0','1','2')) as YJTCJE,  --预交金退出金额
  nvl((select sum(XJJE00+YHKJE0+ZZJE00+DZQBJE+WXZF00+ZFBZF0) from BM_JKB000 where PJLYID=a.PJLYID and JKDH00=a.JKDH00 and PJBH00 in (4,8) and a.PJBH00 in (4,8)),0) as YJYSXJ,  --预交金应收
  nvl((select sum(YHKJE0+ZZJE00+DZQBJE) from BM_JKB000 where PJLYID=a.PJLYID and JKDH00=a.JKDH00 and PJBH00 in (4,8) and a.PJBH00 in (4,8)),0) as YJYSQT,  --预交金应收其他
  a.JKRQ00,    --缴款日期
  (select substr(JKQSSJ,1,4)||'-'||substr(JKQSSJ,5,2)||'-'||substr(JKQSSJ,7,2)||' '||substrb(JKQSSJ,9,8)||' 至 '||
          substr(JKSJ00,1,4)||'-'||substr(JKSJ00,5,2)||'-'||substr(JKSJ00,7,2)||' '||substrb(JKSJ00,9,8)
     from BM_JKSJ00 where JKDH00=a.JKDH00) as JKRQGG,                    --缴款日期规格化
  (select PLMC00 from BM_PJLXB0 c where c.PLBH00=a.PJBH00) as PLMC00,    --票据类型名称
  a.SFY000,    --收费员编号
  f.XKH000,    --胸卡号
  f.ZWXM00,    --收费员姓名
  (select count(*) from ZY_PJSYQK b where a.PJLYID=b.PJLYID and b.JKDID0=a.JKDH00 and b.PLBH00 in (20) 
          and substr(b.PJXH00,1,1)<>'-' and b.SFDY00='Y' and XJJE00+ZZJE00+YHKJE0+DZQBJE>0 and PJZT00 in ('0','1','2')) as DZSRZS,  --呆账结清收入张数
  (select count(*) from ZY_PJSYQK b where a.PJLYID=b.PJLYID and b.JKDID0=a.JKDH00 and b.PLBH00 in (20) 
          and substr(b.PJXH00,1,1)<>'-' and b.SFDY00='Y' and XJJE00+ZZJE00+YHKJE0+DZQBJE<0 and PJZT00 in ('0','1','2')) as DZTCZS,  --呆账结清退出张数
  (select sum(HJJE00) from ZY_PJSYQK b where a.PJLYID=b.PJLYID and b.JKDID0=a.JKDH00 and b.PLBH00 in (20) 
          and ((substrb(PJXH00,1,1)='-' and HJJE00<0) or ((substrb(PJXH00,1,1)<>'-' and HJJE00>0))) and PJZT00 in ('0','1','2')) as DZSRJE,  --呆账结清收入金额
  (select sum(HJJE00) from ZY_PJSYQK b where a.PJLYID=b.PJLYID and b.JKDID0=a.JKDH00 and b.PLBH00 in (20) 
          and ((substrb(PJXH00,1,1)='-' and HJJE00>0) or ((substrb(PJXH00,1,1)<>'-' and HJJE00<0))) and PJZT00 in ('0','1','2')) as DZTCJE, --呆账结清退出金额          
  (XJJE00+YHKJE0+ZZJE00+DZQBJE) as DZJQYS,  --呆账结清应收
  (select sum(JFJE00) from ZY_BRJFB0 where a.PJBH00=8 and JKBID0=a.JKDH00 and ZFFS00=9 and JFJE00>0) as GZSRJE,  --挂账收入   --PLBH00=a.PJBH00
  (select sum(JFJE00) from ZY_BRJFB0 where a.PJBH00=8 and JKBID0=a.JKDH00 and ZFFS00=9 and JFJE00<0) as GZTCJE,  --挂账退出   --PLBH00=a.PJBH00
  (select sum(JFJE00) from ZY_BRJFB0 where a.PJBH00=3 and JKBID0=a.JKDH00 and ZFFS00=20) as YYZFJE,   --医院支付金额
  (
    (select sum(XJJE00+ZZJE00+YHKJE0) FROM zy_pjsyqk b where plbh00 in ('4','8') AND pjzt00 <> '1' 
     and HSJKID=a.JKDH00 and not exists (select 1 from zy_brjfb0 where pjh000=b.pjh000 and JSLX00='1')
     )
  + (select nvl(-sum(c.JFJE00),0)
      from zy_pjsyqk b,zy_brjfb0 c
     where b.pjh000=c.pjh000 and b.plbh00 in ('3') and c.jslx00='3'  and substr (b.pjxh00, 1, 1)='-' 
       and c.zffs00 in (1,2,8)
       and b.JKDID0=a.JKDH00
     )
  +(select nvl(sum (b.JFJE00),0)
    from zy_brjfb0 b,zy_jzb000 c,zy_pjsyqk d
   where    b.jzdh00=c.jzdh00 and c.pjh000=d.pjh000 and b.jslx00='3' and d.plbh00 in ('3') and substr (d.pjxh00, 1, 1)<>'-'
            and b.zffs00 in (1,2,8) 
            and d.JKDID0=a.JKDH00    
     ) 
   ) as JSHSYJ,  --结算回收预交金额  
  (select sum(JFJE00) from ZY_BRJFB0 where JKBID0=a.JKDH00 and ZFFS00 in (1,2,8) and JSLX00='1' and JFJE00>0) as JSBJYJ,  --结算补交预交金额
  (select sum(JFJE00) from ZY_BRJFB0 where JKBID0=a.JKDH00 and ZFFS00 in (1,2,8) and JSLX00='1' and JFJE00<0) as JSTCYJ,  --结算退出预交金额
  SF_ZY_BQYE00_XMLH(a.JKRQ00,a.JKSJ00,a.YYID00) as BQYE00 , --本期余额  
  (select sum(D.HJJE00) from ZY_JZB000 C,ZY_PJSYQK D where D.PJH000 not in (C.PJH000) and D.JKDID0=A.JKDH00 and C.ZYID00=D.ZYID00 and C.JSLX00='2'  and C.JZZT00 in ('0','1')) as GZJSJE ----挂账结算金额
from BM_JKB000 a,BM_YGBM00 f
where a.SFY000=f.YGBH00
  and a.MZBZ00='1'