-- Start of DDL Script for View SD_HOSPITAL.VW_SF_ZLKJS0
-- Generated 2018-04-20 21:02:35 from SD_HOSPITAL@XMLH_ZSK

CREATE OR REPLACE VIEW vw_sf_zlkjs0 (
   jkdh00,
   fksl00,
   tksl00,
   hksl00,
   ickje0,
   jkrqgg,
   sfy000,
   xkh000,
   zwxm00 )
AS
select
  --YWCZ: 0入库,1售病人卡,2售员工卡,3报停,4报废,5退卡,6取消报停,7重制空白卡,
  --      8医保卡登记,9外院卡登记 A作废卡回收 B厦门健康卡登记 C换卡(新)E换卡回收(旧)F补卡(新)';  
  b.JKDH00, --缴款单号
  (select count(*) from IC_YWLS00 e,IC_YHXX00 f 
     where e.CZRQ00 between b.JKQSRQ and b.JKRQ00 and e.CZRQ00||e.CZSJ00 between b.JKQSSJ and b.JKSJ00
       and e.YWCZ00 in ('1','2') and e.YGBH00 = b.JKRY00 and e.ICKH00 = f.ICKH00 and f.ICKLB0 = '0') FKSL00, --发卡数量
  (select count(*) from IC_YWLS00 e,IC_YHXX00 f
     where e.CZRQ00 between b.JKQSRQ and b.JKRQ00 and e.CZRQ00||e.CZSJ00 between b.JKQSSJ and b.JKSJ00
       and e.YWCZ00 in ('5') and e.YGBH00 = b.JKRY00 and e.ICKH00 = f.ICKH00 and f.ICKLB0 ='0') TKSL00, --退卡数量
  (select count(*) from IC_YWLS00 e,IC_YHXX00 f
     where e.CZRQ00 between b.JKQSRQ and b.JKRQ00 and e.CZRQ00||e.CZSJ00 between b.JKQSSJ and b.JKSJ00
       and e.YWCZ00 in ('E','4','F') and e.YGBH00 = b.JKRY00 and e.ICKH00 = f.ICKH00 and f.ICKLB0 = '0') HKSL00, --换补卡数量
  (select sum(FSJE00) from IC_YWLS00 e,IC_YHXX00 f
     where e.CZRQ00 between b.JKQSRQ and b.JKRQ00 and e.CZRQ00||e.CZSJ00 between b.JKQSSJ and b.JKSJ00
       and e.YWCZ00 in ('1','2','F') and e.YGBH00 = b.JKRY00 and e.ICKH00=f.ICKH00 and f.SFSKYJ=1) ICKJE0, --IC卡金额
  (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=b.JKDH00) JKRQGG, --缴款日期规格化
  b.JKRY00 SFY000,    --收费员编号
  c.XKH000,    --胸卡号
  c.ZWXM00     --收费员姓名
from BM_JKSJ00 b,BM_YGBM00 c where b.JKRY00=c.YGBH00
/


-- End of DDL Script for View SD_HOSPITAL.VW_SF_ZLKJS0