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 -- linzetao 2022.11.04 增加模式4：从自定义函数SF_ZY_GETDZPJBZ_ZDY获取 ZYSF9-20221025-001 -- chenrh 2024.01.10 增加显示减免金额 ZYSF13-20231103-001 -- chenrh 2024.05.23 增加现金退款、转账退款、银行卡退款 ZYSF13-20240524-002 -- nicl 2024.07.10 添加ZY_PJSYQK.BZ0000备注 ZYSF13-20240617-001 -- nicl 2024.09.05 增加显示优惠金额 ZYSF13-20240829-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_ZZTKJE ZY_JZB000.ZZYE00%type; V_ZFBTK0 ZY_JZB000.ZFBTK0%type; V_WXTK00 ZY_JZB000.WXTK00%type; V_YHTKJE ZY_JZB000.YHTKJE%type; V_SQL000 varchar2(200); V_JMJE00 ZY_FYMX00.JMJE00%type; V_YHJE00 ZY_FYMX00.JMJE00%type; V_ZYSF_DZFPSFXSJMJE XT_XTCS00.VALUE0%type; --系统参数 ZYSF_DZFPSFXSJMJE V_BZ0000 ZY_PJSYQK.BZ0000%type; begin select nvl(max(trim(VALUE0)),'0') into V_ZY_DZFPPZREMARK from XT_XTCS00 where NAME00='ZY_DZFPPZREMARK'; --chenrh 2024.01.10 增加显示减免金额 ZYSF13-20231103-001 --电子发票是否显示减免金额，1是 0否,默认0 select nvl(max(trim(VALUE0)),'0') into V_ZYSF_DZFPSFXSJMJE from XT_XTCS00 where NAME00='ZYSF_DZFPSFXSJMJE'; 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; select a.XJTKJE,a.ZZTKJE,a.YHTKJE into V_XJTKJE,V_ZZTKJE,V_YHTKJE from VW_ZY_FPXX00 a where a.ZYID00=Pzyid00 and a.JZDH00=Pjzdh00; --V_REMARK:=V_REMARK||'医院支付:'||to_char(nvl(V_ZY_JZB000_ROW.YBYL05,0))||';'; V_REMARK:=V_REMARK||'现金退款:'||to_char(V_XJTKJE)||';'; V_REMARK:=V_REMARK||'转账退款:'||to_char(V_ZZTKJE)||';'; V_REMARK:=V_REMARK||'银行卡退款:'||to_char(nvl(V_YHTKJE,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 V_SQL000 := 'select SF_ZY_GETDZPJBZ_ZDY('||PZYID00||','||PJZDH00||') from dual'; execute immediate V_SQL000 into V_REMARK; 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; --chenrh 2024.01.10 增加显示减免金额 ZYSF13-20231103-001 if V_ZYSF_DZFPSFXSJMJE='1' then select sum(nvl(f.JMJE00,0)) into V_JMJE00 from ZY_FYMX00 f where f.BRID00 = V_ZY_JZB000_ROW.BRID00 and f.ZYID00 = V_ZY_JZB000_ROW.ZYID00 and f.JZDH00=V_ZY_JZB000_ROW.JZDH00 and f.JMBZ00='1' and f.XMMC00='项目总额减免费'group by f.XMMC00; if V_JMJE00 is not null then V_REMARK:=V_REMARK||' '||'减免金额：'||to_char(V_JMJE00); else V_REMARK:=V_REMARK||' '||'减免金额：'||to_char(0); end if; end if; select sum(NVL(F.JMJE00, 0)) into V_YHJE00 from ZY_FYMX00 F where F.BRID00 = V_ZY_JZB000_ROW.BRID00 and F.ZYID00 = V_ZY_JZB000_ROW.ZYID00 and F.JZDH00 = V_ZY_JZB000_ROW.JZDH00 and F.JMBZ00 = '2'; if V_YHJE00 is not null and V_YHJE00<>0 then V_REMARK := V_REMARK || ' ' || '优惠金额：' || TO_CHAR(V_YHJE00); end if; end if; select A.BZ0000 into V_BZ0000 from ZY_PJSYQK A where A.PJH000 = (select PJH000 from zy_jzb000 where JZDH00 = Pjzdh00); if V_JMJE00 is not null then V_REMARK:=V_REMARK||' 备注：'||V_BZ0000; end if; return V_REMARK; exception when others then V_REMARK:= '获取异常'; return V_REMARK; end;