prompt 住院收费员缴款视图 VW_ZY_JKB001
create or replace view VW_ZY_JKB001
as
-- MODIFICATION HISTORY
-- Person   Date        Comments
-- daihq    2013.03.21  create
-- qks      2013.11.12  增加挂账(逃跑)收入、挂账退出;
-- qks      2017.03.21  增加医院支付金额; for ZYSF9-20170322-001
-- qks      2017.04.27  增加三个字段：JSHSYJ,JSBJYJ,JSTCYJ for ZYSF9-20170426-001
-- qks      2017.04.28  要求增加取消结算以及重新结算金额; for ZYSF9-20170502-001
-- qks      2017.05.10  福清市医院住院收预交金票据和退预交金票据是分开的,取消PJLYID字段关联; for ZYSF9-20170510-001
-- pwt      2018.06.27  福州新莲花医院预交金应收新增累加WXZF00、ZFBZF0; for ZYSF9-20180525-003
-- gzj      2018.11.16  新增字段挂账结算金额GZJSJE for ZYSF9-20181106-002
-- jlg      2019.02.02  函数SF_ZY_BQYE00_XMLH(原SF_XMLH_BQYE00)新增入参 YYID00  ZYSF9-20190201-005
-- pwt      2019.04.09  YJSRZS,YJTCZS,YJSRJE,YJTCJE,YJYSXJ,YJYSQT关联表BM_JKB000时增加PJLYID关联 for ZYSF9-20190408-001
-- jlg      2020.03.29  修改b.PJLYID --> nvl(b.PJLYID,0) 	ZYSF9-20200329-001
-- linzetao 2020.07.16  当票据领用ID(ZY_PJSYQK.PJLYID)是空或0时, 取外部领用ID(ZY_PJSYQK.WBLYID)    ZYSF9-20200521-003
-- ncl      2021.11.10  禅道：bug8704 添加a.WBPJLY外部票据来源 01预交金电子凭证 02电子发票 03纸质发票 04电子发票作废红票 05纸质发票作废红票
select
  a.JKDH00  ,                                     --缴款单号
  a.PJZS00  ,                                     --票据数
  a.PJBH00  ,                                     --票据编号
  a.WBPJLY  ,                                     --外部票据来源 01预交金电子凭证 02电子发票 03纸质发票 04电子发票作废红票 05纸质发票作废红票
  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 ZY_PJSYQK b where b.JKDID0=a.JKDH00 and a.PJLYID=decode(nvl(b.PJLYID,0),0,b.WBLYID,b.PJLYID) 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.JKDID0=a.JKDH00 and a.PJLYID=decode(nvl(b.PJLYID,0),0,b.WBLYID,b.PJLYID) 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.JKDID0=a.JKDH00 and a.PJLYID=decode(nvl(b.PJLYID,0),0,b.WBLYID,b.PJLYID) 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.JKDID0=a.JKDH00 and a.PJLYID=decode(nvl(b.PJLYID,0),0,b.WBLYID,b.PJLYID) 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 JKDH00=a.JKDH00 and PJLYID=a.PJLYID 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 and rownum=1) as JKRQGG,                    --缴款日期规格化
  (select PLMC00 from BM_PJLXB0 c where c.PLBH00=a.PJBH00 and rownum=1) as PLMC00,    --票据类型名称
  a.SFY000,    --收费员编号
  f.XKH000,    --胸卡号
  f.ZWXM00,    --收费员姓名
  (select count(*) from ZY_PJSYQK b where b.JKDID0=a.JKDH00 and a.PJLYID=decode(nvl(b.PJLYID,0),0,b.WBLYID,b.PJLYID) 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 b.JKDID0=a.JKDH00 and a.PJLYID=decode(nvl(b.PJLYID,0),0,b.WBLYID,b.PJLYID) 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 b.JKDID0=a.JKDH00 and a.PJLYID=decode(nvl(b.PJLYID,0),0,b.WBLYID,b.PJLYID) 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 b.JKDID0=a.JKDH00 and a.PJLYID=decode(nvl(b.PJLYID,0),0,b.WBLYID,b.PJLYID) 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'
/

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%