-- Start of DDL Script for View SD_HOSPITAL.VW_SF_JKB001
-- Generated 07-2月-2018 10:45:50 from SD_HOSPITAL@ZHZYYGL

CREATE OR REPLACE VIEW vw_sf_jkb001 (
   jkdh00,
   pjzs00,
   pjbh00,
   yxpjs0,
   cxpjs0,
   zfpjs0,
   pjqjhm,
   fpzje0,
   fpzfje,
   cxje00,
   jmje00,
   yjsrzs,
   yjtczs,
   yjzfzs,
   yjsrje,
   yjtcje,
   yjysxj,
   yjysqt,
   jkrq00,
   jkrqgg,
   plmc00,
   sfy000,
   xkh000,
   zwxm00,
   yjtczs1,
   yjtcje1,
   yjysxj1,
   yjysqt1 )
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,       --减免金额
  (select count(*) from SF_PJSYQK b where a.PJLYID=b.PJLYID and b.JKDID0=a.JKDH00 and a.PJBH00 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')) YJSRZS,  --预交金收入张数   
  (select count(*) from SF_PJSYQK b where a.PJLYID=b.PJLYID and b.JKDID0=a.JKDH00 and a.PJBH00 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')) YJTCZS,  --预交金退出张数   
  a.PJZFH0 YJZFZS,  --作废票据数   
  (select sum(HJJE00) from SF_PJSYQK b where a.PJLYID=b.PJLYID and b.JKDID0=a.JKDH00 and a.PJBH00 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')) YJSRJE,  --预交金收入金额
  (select sum(HJJE00) from SF_PJSYQK b where a.PJLYID=b.PJLYID and b.JKDID0=a.JKDH00 and a.PJBH00 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')) YJTCJE,  --预交金退出金额  
  (XJJE00+YHKJE0+ZZJE00+DZQBJE)YJYSXJ,  --预交金应收
  (YJJZF0) 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,    --收费员编号
  c.XKH000,    --胸卡号
  c.ZWXM00,     --收费员姓名
  (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')) YJTCZS1,  --预交金退出张数 
  (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')) YJTCJE1,  --预交金退出金额           
   (XJJE00+YHKJE0+ZZJE00+DZQBJE+(select XJJE00+YHKJE0+ZZJE00+DZQBJE from BM_JKB000 where JKDH00=a.JKDH00 and PJBH00='2')) as YJYSXJ1,  --预交金应收          
   (YJJZF0+(select YJJZF0 from BM_JKB000 where JKDH00=a.JKDH00 and PJBH00='2')) YJYSQT1  --预交金应收其他
from BM_JKB000 a,BM_YGBM00 c where a.SFY000=c.YGBH00
/


-- End of DDL Script for View SD_HOSPITAL.VW_SF_JKB001