CREATE VIEW SD_HOSPITAL.VW_WLPT_MZBRJFB0
 (
 DJH000, BRID00, JFLBID, JFJE00, ZFFS00, ZFFSMC, ZPH000, ZPDWMC, ZPZH00, 
PJH000, JFRQ00, JFSJ00, JFCZY0, CZYXM0, JYLSH0, JGBM00, JGMC00, DZMXID, 
SHH000, DDLSH0, BRYE00, YKTSH0, JYLX00

 )
 AS 
select 
    DJH000,
    BRID00,
    JFLBID,
    JFJE00,
    ZFFS00,
    ZFFSMC,
    ZPH000,
    ZPDWMC,
    ZPZH00,
    PJH000,
    JFRQ00,
    JFSJ00,
    decode(a.SHH000,'JKZLPAY',decode(ZFFS00,24,6001,25,6002),JFCZY0) JFCZY0,
    decode(a.SHH000,'JKZLPAY','健康之路',CZYXM0) CZYXM0,
    --JYLSH0,
    nvl(a.jylsh0,(select c.jylsh0 from bm_zfrz00 b,bm_zfrz00 c where b.ddlsh0=a.ddlsh0 and c.ddlsh0=b.tkddh0 and rownum=1)) jylsh0,
    decode(a.SHH000,'JKZLPAY','193',JGBM00) JGBM00,
    decode(a.SHH000,'JKZLPAY','门诊收费处',JGMC00)   JGMC00,
    DZMXID,
    SHH000,
    DDLSH0,
    BRYE00,
    YKTSH0,
    '门诊预交金' JYLX00
    from SF_BRJFB0 a  where a.jflbid<>'0'
/

CREATE VIEW SD_HOSPITAL.VW_WLPT_ZYBRJFB0
 (
 DJH000, BRID00, JFLBID, JFJE00, ZFFS00, ZFFSMC, ZPH000, ZPDWMC, ZPZH00, 
PJH000, JFRQ00, JFSJ00, JFCZY0, CZYXM0, JYLSH0, JGBM00, JGMC00, DZMXID, 
SHH000, DDLSH0, BRYE00, JYLX00

 )
 AS 
select 
    DJH000,
    BRID00,
    JFLBID,
    JFJE00,
    ZFFS00,
    ZFFSMC,
    ZPH000,
    ZPDWMC,
    ZPZH00,
    PJH000,
    JFRQ00,
    JFSJ00,
    sf_wlpt_zfdzczy(DJH000,JFJE00,ZFFS00,ZFFSMC,JFCZY0,CZYXM0) JFCZY0,
    --decode(a.SHH000,'JKZLPAY',decode(ZFFS00,24,6001,25,6002),JFCZY0) JFCZY0,
    decode(a.SHH000,'JKZLPAY','健康之路',CZYXM0) CZYXM0,
    JYLSH0,
    --nvl(nvl(a.YKTSH0,a.JYLSH0),(select c.PTDDH0 from BM_ZFRZ00 b,BM_ZFRZ00 c where b.BRID00=a.BRID00 and b.DJH000=a.DJH000 and b.TKDDH0=c.DDLSH0 and b.JFJE00<0)),
    --(select c.PTDDH0 from BM_ZFRZ00 b,BM_ZFRZ00 c where b.BRID00=a.BRID00 and b.DJH000=a.DJH000 and b.TKDDH0=c.DDLSH0 and b.JFJE00<0) ,
    --decode(a.SHH000,'JKZLPAY','193',JGBM00) JGBM00,
    --decode(a.SHH000,'JKZLPAY','门诊收费处',JGMC00)   JGMC00,
    '193' JGBM00,
    '门诊收费处' JGMC00,    
    DZMXID,
    SHH000,
    DDLSH0,
    BRYE00,
    '住院预交金' JYLX00
    from ZY_BRJFB0 a where a.jflbid<>'0'
/

CREATE OR REPLACE FUNCTION sf_wlpt_zfdzczy(ad_DJH000 number,ad_JFJE00 number,ad_ZFFS00  number,as_ZFFSMC varchar2,ad_JFCZY0 number,as_CZYXM0 varchar2)
return number is
  LS_ZFFSMC   varchar2(100);
  LS_TKDJH0   BM_ZFRZ00.TKDDH0%type;
  LS_JKLXBH   BM_ZFRZ00.JKLXBH%type;
  ad_JKLXBH   BM_ZFRZ00.JKLXBH%type;
  LS_CZYXM0   BM_YGBM00.ZWXM00%type;
  LS_JFCZY0   BM_YGBM00.YGBH00%type;
  LS_CZY000   BM_YGBM00.YGBH00%type;
begin
   begin
      select JKLXBH into ad_JKLXBH from BM_ZFRZ00 where DJH000=ad_DJH000;
   exception
      when others then
      ad_JKLXBH:=0;
   end;
   select ZWXM00 into LS_CZYXM0 from BM_YGBM00 where YGBH00=ad_JFCZY0;
  if ad_JFJE00>0 then
     if ad_JKLXBH in(50000,60000) then --app
        --LS_ZFFSMC:=LS_CZYXM0||as_ZFFSMC;
        LS_ZFFSMC:='掌医';
        if instr(LS_CZYXM0,'掌医')>0 then LS_JFCZY0:=ad_JFCZY0;
        else LS_JFCZY0:=ad_JFCZY0;
        end if;
     else
        --LS_ZFFSMC:='当面付'||as_ZFFSMC;
        LS_ZFFSMC:='当面付';
        if ad_ZFFS00=24 then LS_JFCZY0:=6001;
        elsif ad_ZFFS00=25 then LS_JFCZY0:=6002;
        else LS_JFCZY0:=ad_JFCZY0;
        end if;
     end if;
  else
    begin
      select TKDDH0 into LS_TKDJH0 from BM_ZFRZ00 where DJH000=ad_DJH000;
    exception
      when others then
      LS_TKDJH0:=0;
      LS_JKLXBH:=0;
    end;
    begin
      select JKLXBH,CZY000 into LS_JKLXBH,LS_CZY000 from BM_ZFRZ00 where DDLSH0=LS_TKDJH0;
    exception
      when others then
      LS_JKLXBH:=0;
    end;
    if LS_JKLXBH in(50000,60000) or ((instr(LS_CZYXM0,'掌医')>0 and  LS_JKLXBH=0) )then --app
        --LS_ZFFSMC:=LS_CZYXM0||as_ZFFSMC;
        LS_ZFFSMC:='掌医';
        if instr(LS_CZYXM0,'掌医')>0 then LS_JFCZY0:=ad_JFCZY0;
        else LS_JFCZY0:=LS_CZY000;
        end if;
    else
        --LS_ZFFSMC:='当面付'||as_ZFFSMC;
        LS_ZFFSMC:='当面付';
        if ad_ZFFS00=24 then LS_JFCZY0:=6001;
        elsif ad_ZFFS00=25 then LS_JFCZY0:=6002;
        else LS_JFCZY0:=ad_JFCZY0;
        end if;
    end if;
  end if;
  return   LS_JFCZY0 ;
end;