create or replace function SF_ZY_GETDZFPREMARK
(
  Pzyid00  in  number,                        --住院ID
  Pjzdh00  in  number                        --结算单号
)
-- Modification History
-- Person          Date        Comments
-- chenHeyi        2022.01.05  created 获取电子发票REMARK字段的字串函数   ZYSF9-20220104-002
-- chenHeyi        2022.05.07  增加模式2：单病种信息，医院支付、微信退款、支付宝退款    ZYSF9-20220421-002
-- chenHeyi        2022.05.14  增加模式3：单病种信息，医保病人账户余额    ZYSF9-20220513-001
return varchar2 is
  V_REMARK  varchar2(200);
  
  V_ZY_JZB000_ROW                   ZY_JZB000%rowtype;
  V_ZY_DZFPPZREMARK                 XT_XTCS00.VALUE0%type;  --系统参数 ZY_DZFPPZREMARK
  V_YBYL01                          ZY_JZB000.YBYL01%type; 
  V_BZMC00                          BM_BZSFBZ.BZMC00%type; 
  V_GHHZJE                          ZY_YBBZMX.BKEB34%type;
  V_XJTKJE                          ZY_JZB000.XJYE00%type;
  V_ZFBTK0                          ZY_JZB000.ZFBTK0%type;
  V_WXTK00                          ZY_JZB000.WXTK00%type;
  V_YHTKJE                          ZY_JZB000.YHTKJE%type;
  V_ZZTKJE                          ZY_PJSYQK.ZZJE00%type;
  
begin
    select nvl(max(trim(VALUE0)),'0') into V_ZY_DZFPPZREMARK from XT_XTCS00 where NAME00='ZY_DZFPPZREMARK'; 
    select * into V_ZY_JZB000_ROW  from ZY_JZB000 a where ZYID00=Pzyid00 and JZDH00=Pjzdh00;
    V_REMARK:='';
  if V_ZY_DZFPPZREMARK='1' then
    select a.XJTKJE,a.ZFBTK0,a.WXTK00,a.YHTKJE into V_XJTKJE, V_ZFBTK0,V_WXTK00,V_YHTKJE from VW_ZY_FPXX00 a where a.ZYID00=Pzyid00 and a.JZDH00=Pjzdh00;
    
     
    select SF_ZY_GETYBBZXMJE(Pjzdh00,'ghhzje') into V_GHHZJE from dual;
    if V_ZY_JZB000_ROW.YBSM09 is not null and V_ZY_JZB000_ROW.YBSM10 in ('01','02','03') then
      --CDRG
      V_YBYL01:=nvl(V_ZY_JZB000_ROW.YBYL01,0);
      if V_YBYL01=0 then
        V_YBYL01:=nvl(V_ZY_JZB000_ROW.YBYL60,0);
      end if;
       
      V_REMARK:=V_REMARK||'A账户支付:'||to_char(V_ZY_JZB000_ROW.GRZHZF)||';'|| 'B账户支付:'||to_char(V_ZY_JZB000_ROW.YBYL41)||';';
      if nvl(V_ZY_JZB000_ROW.GWYBZ0,0)<>0 then
        V_REMARK:=V_REMARK||'公务员补助:'||to_char(V_ZY_JZB000_ROW.GWYBZ0)||';';
      end if;
      if nvl(V_YBYL01,0)<>0 then
        V_REMARK:=V_REMARK||'民政救助:'||to_char(V_YBYL01)||';';
      end if;
      if nvl(V_ZY_JZB000_ROW.YBYL40,0)<>0 then
        V_REMARK:=V_REMARK||'大病保险:'||to_char(V_ZY_JZB000_ROW.YBYL40)||';';
      end if;
      if nvl(V_GHHZJE,0)<>0 then
        V_REMARK:=V_REMARK||'工会互助:'||to_char(V_GHHZJE)||';';
      end if;       
    end if;
    V_REMARK:=V_REMARK||'个人账户余额:'||to_char(V_ZY_JZB000_ROW.YBYL64)||';';
    if nvl(V_XJTKJE,0)<>0 then
      V_REMARK:=V_REMARK||'现金退款:'||to_char(V_XJTKJE)||';';
    end if;
    if nvl(V_ZFBTK0,0)<>0 then
      V_REMARK:=V_REMARK||'支付宝退款:'||to_char(V_ZFBTK0)||';';
    end if;
    if nvl(V_WXTK00,0)<>0 then
      V_REMARK:=V_REMARK||'微信退款:'||to_char(V_WXTK00)||';';
    end if;
    if nvl(V_YHTKJE,0)<>0 then
      V_REMARK:=V_REMARK||'银行卡退款:'||to_char(V_YHTKJE)||';';
    end if;
  elsif V_ZY_DZFPPZREMARK='2' then
    if V_ZY_JZB000_ROW.FFBZBM is not null or V_ZY_JZB000_ROW.FFBZBM <> '' then
      select BZMC00 into V_BZMC00 from BM_BZSFBZ where FBBH00=V_ZY_JZB000_ROW.FBBH00 and YBZXLB=V_ZY_JZB000_ROW.YBZXLB and FFBZBM = V_ZY_JZB000_ROW.FFBZBM and rownum=1;
      V_REMARK:=V_REMARK||'病种结算名称：'||V_BZMC00||';  ';
    end if;
    V_REMARK:=V_REMARK||'医院支付:'||to_char(nvl(V_ZY_JZB000_ROW.YBYL05,0))||';';
    V_REMARK:=V_REMARK||'微信退款:'||to_char(nvl(V_ZY_JZB000_ROW.WXTK00,0))||';';
    V_REMARK:=V_REMARK||'支付宝退款:'||to_char(nvl(V_ZY_JZB000_ROW.ZFBTK0,0));
  elsif V_ZY_DZFPPZREMARK='3' then
    if V_ZY_JZB000_ROW.FFBZBM is not null or V_ZY_JZB000_ROW.FFBZBM <> '' then
      select BZMC00 into V_BZMC00 from BM_BZSFBZ where FBBH00=V_ZY_JZB000_ROW.FBBH00 and YBZXLB=V_ZY_JZB000_ROW.YBZXLB and FFBZBM = V_ZY_JZB000_ROW.FFBZBM and rownum=1;
      V_REMARK:=V_REMARK||'病种结算名称：'||V_BZMC00||';  ';
    end if;
    V_REMARK:=V_REMARK||'医保账户余额:'||to_char(nvl(V_ZY_JZB000_ROW.YBZHYE,0));

    elsif V_ZY_DZFPPZREMARK='4' then
          select a.XJTKJE,a.ZFBTK0,a.WXTK00,a.YHTKJE,a.ZZTKJE into V_XJTKJE, V_ZFBTK0,V_WXTK00,V_YHTKJE,V_ZZTKJE from VW_ZY_FPXX00 a where a.ZYID00=Pzyid00 and a.JZDH00=Pjzdh00;
    
     
    select SF_ZY_GETYBBZXMJE(Pjzdh00,'ghhzje') into V_GHHZJE from dual;
    if V_ZY_JZB000_ROW.YBSM09 is not null and V_ZY_JZB000_ROW.YBSM10 in ('01','02','03') then
      --CDRG
       V_YBYL01:=nvl(V_ZY_JZB000_ROW.YBYL01,0);
      if V_YBYL01=0 then
        V_YBYL01:=nvl(V_ZY_JZB000_ROW.YBYL60,0);
      end if;
       
      V_REMARK:=V_REMARK||'A账户支付:'||to_char(V_ZY_JZB000_ROW.GRZHZF)||';'|| 'B账户支付:'||to_char(V_ZY_JZB000_ROW.YBYL41)||';';
      if nvl(V_ZY_JZB000_ROW.GWYBZ0,0)<>0 then
        V_REMARK:=V_REMARK||'公务员补助:'||to_char(V_ZY_JZB000_ROW.GWYBZ0)||';';
      end if;
      if nvl(V_YBYL01,0)<>0 then
        V_REMARK:=V_REMARK||'民政救助:'||to_char(V_YBYL01)||';';
      end if;
      if nvl(V_ZY_JZB000_ROW.YBYL40,0)<>0 then
        V_REMARK:=V_REMARK||'大病保险:'||to_char(V_ZY_JZB000_ROW.YBYL40)||';';
      end if;
      if nvl(V_GHHZJE,0)<>0 then
        V_REMARK:=V_REMARK||'工会互助:'||to_char(V_GHHZJE)||';';
      end if;       
    end if;
    V_REMARK:=V_REMARK||'个人账户余额:'||to_char(V_ZY_JZB000_ROW.YBYL64)||';';
    if nvl(V_XJTKJE,0)<>0 then
      V_REMARK:=V_REMARK||'现金退款:'||to_char(V_XJTKJE)||';';
    end if;
    if nvl(V_ZFBTK0,0)<>0 then
      V_REMARK:=V_REMARK||'支付宝退款:'||to_char(V_ZFBTK0)||';';
    end if;
    if nvl(V_WXTK00,0)<>0 then
      V_REMARK:=V_REMARK||'微信退款:'||to_char(V_WXTK00)||';';
    end if;
    if nvl(V_YHTKJE,0)<>0 then
      V_REMARK:=V_REMARK||'银行卡退款:'||to_char(V_YHTKJE)||';';
       end if;
        if nvl(V_ZZTKJE,0)<>0 then
      V_REMARK:=V_REMARK||'转账退款:'||to_char(V_ZZTKJE)||';';
    end if;
     if V_ZY_JZB000_ROW.FFBZBM is not null or V_ZY_JZB000_ROW.FFBZBM <> '' then
      select BZMC00 into V_BZMC00 from BM_BZSFBZ where FBBH00=V_ZY_JZB000_ROW.FBBH00 and YBZXLB=V_ZY_JZB000_ROW.YBZXLB and FFBZBM = V_ZY_JZB000_ROW.FFBZBM and rownum=1;
      V_REMARK:=V_REMARK||'病种结算名称：'||V_BZMC00||';'||'病种定额：'||V_ZY_JZB000_ROW.ybyl04||';'||'住院天数:'||V_ZY_JZB000_ROW.Ptbcts||';';
    end if;
    
    V_REMARK:=V_REMARK||'民政救助:'||to_char(nvl(V_ZY_JZB000_ROW.YBYL60,0));
    V_REMARK:=V_REMARK||'微信退款:'||to_char(nvl(V_ZY_JZB000_ROW.WXTK00,0))||';';
    V_REMARK:=V_REMARK||'支付宝退款:'||to_char(nvl(V_ZY_JZB000_ROW.ZFBTK0,0));
    V_REMARK:=V_REMARK||'医保账户余额:'||to_char(nvl(V_ZY_JZB000_ROW.YBZHYE,0));
    

     else
    
    
    --默认模式
    if V_ZY_JZB000_ROW.FFBZBM is not null or V_ZY_JZB000_ROW.FFBZBM <> '' then
      select BZMC00 into V_BZMC00 from BM_BZSFBZ where FBBH00=V_ZY_JZB000_ROW.FBBH00 and YBZXLB=V_ZY_JZB000_ROW.YBZXLB and FFBZBM = V_ZY_JZB000_ROW.FFBZBM and rownum=1;
      V_REMARK:=V_REMARK||'病种结算名称：'||V_BZMC00;
    end if;
  end if;
    
    return V_REMARK;
exception
  when others then
    V_REMARK:= '获取异常';
    return V_REMARK;    
end;
