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;