CREATE OR REPLACE VIEW VW_ZY_JKB001 AS
select
  a.JKDH00  ,                                     --缴款单号
  a.PJZS00  ,                                     --票据数
  a.PJBH00  ,                                     --票据编号
  a.PJZS00-a.PJZFH0 YXPJS0,  --缴款当时有效
  a.HSZFH0 CXPJS0,  --冲销票据数(缴款后取消结算或者补打)
  a.PJZFH0 ZFPJS0,  --作废票据数(缴款前取消结算或者补打)
  a.PJQSH0||' - '||a.PJZZH0 PJQJHM ,      --票据区间号码
  a.HJJE00 FPZJE0,       --发票金额
  a.ZFJE00 FPZFJE,       --发票自付金额
  -1*a.HSHJJE CXJE00,   --冲销金额
  a.GFJE00 JMJE00,       --减免金额
   --a.PJLYID=b.PJLYID and 
  (select count(*) from ZY_PJSYQK b where 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')) YJSRZS,  --预交金收入张数
  (select count(*) from ZY_PJSYQK b where 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')) YJTCZS,  --预交金退出张数
  a.PJZFH0 YJZFZS,  --作废票据数
  (select sum(HJJE00) from ZY_PJSYQK b where 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')) YJSRJE,  --预交金收入金额
  (select sum(HJJE00) from ZY_PJSYQK b where 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')) YJTCJE,  --预交金退出金额
  nvl((select sum(XJJE00+YHKJE0+ZZJE00+DZQBJE+WXZF00+ZFBZF0) from BM_JKB000 where JKDH00=a.JKDH00 and PJBH00 in (4,8) and a.PJBH00 in (4,8)),0) YJYSXJ,  --预交金应收
  nvl((select sum(YHKJE0+ZZJE00+DZQBJE) from BM_JKB000 where JKDH00=a.JKDH00 and PJBH00 in (4,8) and a.PJBH00 in (4,8)),0) 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) JKRQGG, --缴款日期规格化
  (select PLMC00 from BM_PJLXB0 c where c.PLBH00=a.PJBH00)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')) 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')) 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')) 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')) DZTCJE, --呆账结清退出金额          
  (XJJE00+YHKJE0+ZZJE00+DZQBJE)DZJQYS,  --呆账结清应收
  (select sum(JFJE00) from zy_brjfb0 where a.PJBH00=8 and JKBID0=a.JKDH00 and zffs00=9 and JFJE00>0) GZSRJE,  --挂账收入  --PLBH00=a.PJBH00
  (select sum(JFJE00) from zy_brjfb0 where a.PJBH00=8 and JKBID0=a.JKDH00 and zffs00=9 and JFJE00<0) GZTCJE,  --挂账退出   --PLBH00=a.PJBH00
  (select sum(JFJE00) from zy_brjfb0 where a.PJBH00=3 and JKBID0=a.JKDH00 and zffs00=20) 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    
     ) 
   ) JSHSYJ,  --结算回收预交金额  
  (select sum(JFJE00) from zy_brjfb0 where JKBID0=a.JKDH00 and zffs00 in (1,2,8) and JSLX00='1' and JFJE00>0) JSBJYJ,  --结算补交预交金额
  (select sum(JFJE00) from zy_brjfb0 where JKBID0=a.JKDH00 and zffs00 in (1,2,8) and JSLX00='1' and JFJE00<0) JSTCYJ   --结算退出预交金额
from BM_JKB000 a,BM_YGBM00 f
where a.SFY000=f.YGBH00
  and a.MZBZ00='1'
;