create or replace view vw_sf_jkb001 as
select
  a.JKDH00,                                                            --缴款单号
  a.PJZS00,                                                            --票据数
  a.PJBH00,                                                            --票据编号
  a.WBPJLY,                                                            --外部票据来源 01预交金电子凭证 02电子发票 03纸质发票 04电子发票作废红票 05纸质发票作废红票
  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,                                                            --收费员编号
  c.XKH000,                                                            --胸卡号
  c.ZWXM00,                                                            --收费员姓名  
  a.PJZS00-a.PJZFH0 as YXPJS0,                                         --缴款当时有效 
  a.HSZFH0 as CXPJS0,                                                  --冲销票据数(缴款后取消结算或者补打)
  a.PJZFH0 as 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 SF_PJSYQK b where b.JKDID0=a.JKDH00 and a.PJLYID=decode(nvl(b.PJLYID,0),0,b.WBLYID,b.PJLYID) and a.PJBH00 in (2,7) and b.PLBH00 in (2,7)
         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 SF_PJSYQK b where b.JKDID0=a.JKDH00 and a.PJLYID=decode(nvl(b.PJLYID,0),0,b.WBLYID,b.PJLYID) and a.PJBH00 in (2,7) and b.PLBH00 in (2,7)
         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 SF_PJSYQK b where b.JKDID0=a.JKDH00 and a.PJLYID=decode(nvl(b.PJLYID,0),0,NVL(b.WBLYID,0),b.PJLYID) and a.PJBH00 in (2,7) and b.PLBH00 in (2,7)
         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 SF_PJSYQK b where b.JKDID0=a.JKDH00 and a.PJLYID=decode(nvl(b.PJLYID,0),0,NVL(b.WBLYID,0),b.PJLYID) and a.PJBH00 in (2,7) and b.PLBH00 in (2,7)
         and ((substrb(PJXH00,1,1)='-' and HJJE00>0) or ((substrb(PJXH00,1,1)<>'-' and HJJE00<0))) and PJZT00 in ('0','1','2')) as YJTCJE,  --预交金退出金额  
  (a.XJJE00+a.YHKJE0+a.ZZJE00+a.DZQBJE+a.WXZF00+a.ZFBZF0) as YJYSXJ,                                 --预交金应收
  (a.YJJZF0) as YJYSQT,                                                                              --预交金应收其他
  (select count(*) from SF_PJSYQK b where b.JKDID0=a.JKDH00 and b.PLBH00 in (2) 
         and substr(b.PJXH00,1,1)<>'-' and b.SFDY00='Y' and XJJE00+ZZJE00+YHKJE0+DZQBJE<0 and PJZT00 in ('0','1','2')) as YJTCZS1,          --预交金退出张数1 
  (select sum(HJJE00) from SF_PJSYQK b where b.JKDID0=a.JKDH00 and b.PLBH00 in (2) 
         and ((substrb(PJXH00,1,1)='-' and HJJE00>0) or ((substrb(PJXH00,1,1)<>'-' and HJJE00<0))) and PJZT00 in ('0','1','2')) as YJTCJE1, --预交金退出金额1           
   (a.XJJE00+a.YHKJE0+a.ZZJE00+a.DZQBJE+a.WXZF00+a.ZFBZF0+(select sum(XJJE00+YHKJE0+ZZJE00+DZQBJE+WXZF00+ZFBZF0) from BM_JKB000 where JKDH00=a.JKDH00 and PJBH00='2')) as YJYSXJ1,  --预交金应收1          
   (a.YJJZF0+(select sum(YJJZF0) from BM_JKB000 where JKDH00=a.JKDH00 and PJBH00='2')) as YJYSQT1,   --预交金应收其他1
   nvl(a.YJJZF0,0) + nvl(a.WXZF00,0) + nvl(a.ZFBZF0,0) + nvl(a.YHKJE0,0) as YJYSQT2,                 --预交金应收其他2 
   (select min(HTFPXH) ||'-'|| max(HTFPXH) from SF_PJSYQK b where b.JKDID0 = a.JKDH00) as HTFPQJ     --航天发票区段
from BM_JKB000 a,BM_YGBM00 c 
where a.SFY000=c.YGBH00
;