CREATE OR REPLACE PROCEDURE SP_SF_BRFPHB ( Pjzdh00 in varchar2 , --结算单号，可能有多条 Pbrid00 in number , --病人ID Pxm0000 in varchar2 , --病人姓名 Pxb0000 in varchar2 , --病人性别 Pyjjxh0 in varchar2 , --打印在发票上的外部序号 Pczy000 in number , --操作员编码 Pczyxm0 in char , --操作员姓名 Pczyks0 in number , --操作员科室 Pcommit in varchar2 default 'N', --提交标志 Ppjh000 out number , --票据号 Pzxcgbz out number , --执行成功标志 1成功 0失败 Ptsxx00 out varchar2 , --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 out varchar2 , --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 psfdzfp in varchar2 default '0' --是否电子发票 0:否 1：是 ) -- MODIFICATION HISTORY -- Person Date Comments -- qks 2012.08.21 Create 诊持卡病人已结算的费用合并成一张发票 -- qks 2012.10.22 ls_XJJE00-->nvl(ls_XJJE00,0)等 -- xuzw 2015.10.27 MZSF9-20151022-001：如果打印收据，修改SF_PJSYQK.PJXH00,生成表SF_PJJZDY数据 -- xuzw 2015.12.31 打印收据，增加SP_SF_PJSYQK_INSERT_NPFY过程 -- xuzw 2016.01.18 政和针对自助机发票修改 -- jlg 2016.10.14 整理并补归档 MZSF9-20161013-005 -- qks 2017.10.23 参数SF_SFDYMZFPSJ值为Y时,自助机结账单合并打印发票时,只会打印第1张结账单; for MZSF9-20171023-001 -- qks 2018.07.13 参数SF_SFDYMZFPSJ值为Y时,作废发票导致SF_JZB000.PJH000=0问题; for MZSF9-20180713-002 -- jlg 2019.05.24 增加兼容处理5.0下的自助机结算的数据 MZSF9-20190510-001 -- linzetao 2019.12.16 修改参数SF_SFDYMZFPSJ值为非‘N’时，已结算的费用合并成一张发票 for MZSF9-20191211-003 -- linzetao 2021.04.17 增加未升级前，多点结算，升级后重新补打发票 ZYSF9-20210417-001 -- linzetao 2021.04.23 处理补打升级前诊间结算发票不跳号问题 MZSF9-20210423-002 -- zhangyc 2022.08.23 SP_SF_BRFPHB增加入参psfdzfp（是否电子发票 0:否 1：是）by MZSF9-20220825-001 -- zhangyc 2022.08.26 SP_SF_PJSYQK_INSERT增加入参psfdzfp（是否电子发票 0:否 1：是） by MZSF9-20220829-002 as ls_CZRQ00 char(8); ls_CZSJ00 char(8); ls_COUNT0 number(5); ls_COUNT1 number(5); -- ls_STJZDH varchar2(200); ls_STJZDH varchar2(500); ls_JZDH00 number(10); ls_XJJE00 SF_PJSYQK.XJJE00%type; ls_HJJE00 SF_PJSYQK.XJJE00%type; ls_ZFJE00 SF_PJSYQK.XJJE00%type; ls_JZJE00 SF_PJSYQK.XJJE00%type; ls_JMJE00 SF_PJSYQK.XJJE00%type; ls_YHJE00 SF_PJSYQK.XJJE00%type; ls_PJH000 SF_JZB000.PJH000%type; ls_HBJZDH varchar2(500); ls_SFDYMZFPSJ XT_XTCS00.VALUE0%type; --查看系统所设置的系统参数的值 ls_SFDYSJ SF_PJSYQK.SFDYSJ%type; Ecustom exception ; --错误变量 ls_PJXH00 BM_PJLYQK.PJDQH0%type; --当前使用票据号 ls_cnt number(3); ls_JZY000 SF_JZB000.JZY000%type; ls_ZZJCNT number(3); LS_PJH000_0RD SF_JZB000.PJH000%type; LS_SFDY00_ORD SF_PJSYQK.SFDY00%type; LS_PHLYXZ SF_PJSYQK.PHLYXZ%type;--票据领用限制 N:不限制 Y或null:限制 begin ls_CZRQ00 := to_char(sysdate,'YYYYMMDD'); ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); ls_XJJE00 := 0; ls_STJZDH := trim(Pjzdh00); LS_PHLYXZ :=null; if psfdzfp='1' then --是否电子发票 0:否 1：是 LS_PHLYXZ:='N'; end if; --查找票据号 begin select B.PJH000,B.SFDY00 into LS_PJH000_0RD,LS_SFDY00_ORD from SF_JZB000 A,SF_PJSYQK B where A.PJH000=B.FZPJH0 and B.PJZT00='0' and A.BRID00=PBRID00 and instrb(','||Pjzdh00||',',','||to_Char(A.JZDH00)||',')>0; exception when others then LS_PJH000_0RD:=0; LS_SFDY00_ORD:='N'; end; if nvl(LS_PJH000_0RD,0)>0 and nvl(LS_SFDY00_ORD,'N')='N' then --直接修改打印这台 update SF_PJSYQK set PJXH00 = Pyjjxh0, --发票序号 SFDY00 = 'Y', --是否打印 DYRQ00=DYRQ00, --打印日期 DYSJ00=DYSJ00, --打印时间 CZY000=CZY000, CZYXM0=CZYXM0, PHLYXZ=nvl(LS_PHLYXZ,PHLYXZ) --票据领用限制 N:不限制 Y或null:限制 where PJH000 = LS_PJH000_0RD; Ppjh000 := LS_PJH000_0RD; SP_SF_PJSYQK_INSERT( Ppjh000, --票据号 Pczy000, --输入：操作员编码 Pczyks0, --操作员科室 'N', --是否提交 Pzxcgbz, --执行成功标志 1成功 0失败 Ptsxx00, --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00, --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 '1', --是否使用入参操作员, 0否1是, 默认0 psfdzfp --是否电子发票 0:否 1：是 ); else --是否打印收据参数 SF_SFDYMZFPSJ参数启用，字段SFDYSJ为Y且票据序号为0 select nvl(max(trim(VALUE0)),'N') into ls_SFDYMZFPSJ from XT_XTCS00 where NAME00='SF_SFDYMZFPSJ'; --参数SF_SFDYMZFPSJ<>'N'时, 已结算未打印的发票合并打印 if ls_SFDYMZFPSJ <> 'N' then ls_cnt := 0; ls_JZDH00 := to_number(substrb(ls_STJZDH,1,instrb(ls_STJZDH, ',')-1)); select nvl(trim(PJH000),0),nvl(JZY000,0),nvl(BRZJE0,0),nvl(ZFJE00,0),nvl(JZJE00,0),nvl(GFJE00,0),nvl(JMJE00,0) into ls_PJH000,ls_JZY000,ls_XJJE00,ls_ZFJE00,ls_JZJE00,ls_JMJE00,ls_YHJE00 from SF_JZB000 WHERE BRID00 = Pbrid00 and JZDH00 = ls_JZDH00; select nvl(max(SFDYSJ),'N'),nvl(MAX(PJXH00),0) into ls_SFDYSJ,ls_PJXH00 from SF_PJSYQK where PJH000 = ls_PJH000; if ls_SFDYSJ = 'Y' and ls_PJXH00 = '0' then while instrb(ls_STJZDH, ',')>0 loop ls_cnt := ls_cnt+1; ls_COUNT0 := instrb(ls_STJZDH, ','); ls_STJZDH := trim(substrb(ls_STJZDH,ls_COUNT0+1,lengthb(ls_STJZDH))); end loop; if ls_cnt > 1 then Ptsxx00 := '收据'||to_char(ls_PJH000)||'只能单独打印'; raise Ecustom; end if; end if; --判断是否是自助机结算 select count(*) into ls_ZZJCNT from SST_YGBHDY where YGBH00 = ls_JZY000; if ls_ZZJCNT > 0 or ls_PJH000 = 0 then select SQ_SF_PJSYQK_PJH000.nextval into Ppjh000 from dual; ls_XJJE00 := 0; ls_ZFJE00 := 0; ls_JZJE00 := 0; ls_JMJE00 := 0; ls_YHJE00 := 0; while instrb(ls_STJZDH, ',')>0 loop ls_COUNT0 := instrb(ls_STJZDH, ','); ls_JZDH00 := to_number(substrb(ls_STJZDH,1,ls_COUNT0-1)); select BRZJE0 + nvl(ls_XJJE00,0),ZFJE00+nvl(ls_ZFJE00,0),JZJE00+nvl(ls_JZJE00,0),GFJE00+nvl(ls_JMJE00,0),JMJE00+nvl(ls_YHJE00,0), PJH000 into ls_XJJE00,ls_ZFJE00,ls_JZJE00,ls_JMJE00,ls_YHJE00,ls_PJH000 from SF_JZB000 where JZDH00= ls_JZDH00; if (ls_PJH000 is not null) and (ls_PJH000<>0) then Ptsxx00 := '记帐单'||to_char(ls_JZDH00)||'已经打过发票'; raise Ecustom; end if; update sf_jzb000 set PJH000= Ppjh000 where jzdh00 = ls_JZDH00; ls_STJZDH := trim(substrb(ls_STJZDH,ls_COUNT0+1,lengthb(ls_STJZDH))); end loop; ls_HBJZDH := substrb(Pjzdh00,1,lengthb(Pjzdh00)-1); sp_execute_sql('insert into SF_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,XMBZ00,ZFBL00,QZFJE0) select '||Ppjh000||',xmbh00,xmmc00,sum(xmje00),0,zfbl00,sum(qzfje0) from SF_JZMX00 where jzdh00 in ('||ls_HBJZDH||') and jzdh00<>0 group by xmbh00,xmmc00,zfbl00'); insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0, DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,FZPJH0, XSRQ00,CZYKS0,HJJE00,ZFJE00,JZJE00,JMJE00,YHJE00,JFLBID,PHLYXZ) select Ppjh000,PLBH00,PLMC00,Pyjjxh0,ls_XJJE00,0,Pczy000,Pczyxm0, ls_CZRQ00,ls_CZSJ00,'Y','0',Pxm0000,Pxb0000,Pbrid00,Ppjh000, ls_CZRQ00,Pczyks0,ls_XJJE00,ls_ZFJE00,ls_JZJE00,ls_JMJE00,ls_YHJE00,'1',LS_PHLYXZ from bm_pjlxb0 where PLBH00 = 1;--PLBH00=1是门诊发票 ls_PJH000 :=Ppjh000 ; end if; update SF_PJSYQK set PJXH00 = Pyjjxh0,SFDYSJ = 'N' where PJH000 = ls_PJH000; insert into SF_PJJZDY (JZDH00,FZPJH0) values (ls_JZDH00,ls_PJH000); Ppjh000 := ls_PJH000; SP_SF_PJSYQK_INSERT_NPFY( Ppjh000, --票据号 Pczy000, --输入：操作员编码 Pczyks0, --操作员科室 'N', --是否提交 Pzxcgbz, --执行成功标志 1成功 0失败 Ptsxx00, --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00, --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ls_PJXH00 ); else --ls_SFDYMZFPSJ为N,则按照原来的规则继续下去 select SQ_SF_PJSYQK_PJH000.nextval into Ppjh000 from dual; while instrb(ls_STJZDH, ',')>0 loop ls_COUNT0 := instrb(ls_STJZDH, ','); ls_JZDH00 := to_number(substrb(ls_STJZDH,1,ls_COUNT0-1)); select PJH000 into ls_PJH000 from SF_JZB000 where JZDH00= ls_JZDH00; /* select BRZJE0 + nvl(ls_XJJE00,0),ZFJE00+nvl(ls_ZFJE00,0),JZJE00+nvl(ls_JZJE00,0),GFJE00+nvl(ls_JMJE00,0),JMJE00+nvl(ls_YHJE00,0), PJH000 into ls_XJJE00,ls_ZFJE00,ls_JZJE00,ls_JMJE00,ls_YHJE00,ls_PJH000 from SF_JZB000 where JZDH00= ls_JZDH00; */ if ls_PJH000<>0 then select count(*) into ls_COUNT1 from SF_PJSYQK where FZPJH0 = ls_PJH000 and PJZT00='0' and SFDY00='Y'; if ls_COUNT1 >0 then Ptsxx00 := '结账单'||to_char(ls_JZDH00)||'已经打过发票'; raise Ecustom; end if; --兼容处理HIS5.0自助机结算记录(旧结算票据信息作废) update SF_PJSYQK set PJZT00 = decode(sign(JKDID0),1,'1','3'), --缴款过的补打视为冲销1，未缴款视为作废3 HSCZY0 = Pczy000, HSCZXM = Pczyxm0, HSRQ00 = ls_CZRQ00, HSSJ00 = ls_CZSJ00, SFBBD0 ='1' where PJH000 = ls_PJH000; end if; update sf_jzb000 set PJH000= Ppjh000 where jzdh00 = ls_JZDH00; ls_STJZDH := trim(substrb(ls_STJZDH,ls_COUNT0+1,lengthb(ls_STJZDH))); end loop; ls_HBJZDH := substrb(Pjzdh00,1,lengthb(Pjzdh00)-1); sp_execute_sql('insert into SF_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,XMBZ00,ZFBL00,QZFJE0) select '||Ppjh000||',xmbh00,xmmc00,sum(xmje00),0,zfbl00,sum(qzfje0) from SF_JZMX00 where jzdh00 in ('||ls_HBJZDH||') and jzdh00<>0 group by xmbh00,xmmc00,zfbl00'); -- 从结算表获取合计的相关金额 select nvl(sum(HJJE00),0),nvl(sum(ZFJE00),0),nvl(sum(JZJE00),0),nvl(sum(GFJE00),0),nvl(sum(JMJE00),0) into ls_XJJE00,ls_ZFJE00,ls_JZJE00,ls_JMJE00,ls_YHJE00 from SF_JZB000 where PJH000 = Ppjh000 and BRID00 = Pbrid00; insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0, DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,FZPJH0, XSRQ00,CZYKS0,HJJE00,ZFJE00,JZJE00,JMJE00,YHJE00,JFLBID,PHLYXZ) select Ppjh000,PLBH00,PLMC00,Pyjjxh0,ls_XJJE00,0,Pczy000,Pczyxm0, ls_CZRQ00,ls_CZSJ00,'Y','0',Pxm0000,Pxb0000,Pbrid00,Ppjh000, ls_CZRQ00,Pczyks0,ls_XJJE00,ls_ZFJE00,ls_JZJE00,ls_JMJE00,ls_YHJE00,'1',LS_PHLYXZ from bm_pjlxb0 where PLBH00 = 1;--PLBH00=1是门诊发票 SP_SF_PJSYQK_INSERT( Ppjh000, --票据号 Pczy000, --输入：操作员编码 Pczyks0, --操作员科室 'N', --是否提交 Pzxcgbz, --执行成功标志 1成功 0失败 Ptsxx00, --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00, --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 '0', --是否使用入参操作员, 0否1是, 默认0 psfdzfp --是否电子发票 0:否 1：是 ); end if; end if; if Pcommit='Y' then commit; end if; Pzxcgbz := 1; exception when no_data_found then raise_application_error(-20001,substrb('数据没有找到！*'||sqlerrm,1,120)); when Ecustom then raise_application_error(-20010,substrb(Ptsxx00,1,120)); when others then raise_application_error(-20266,dbms_utility.format_error_backtrace||'$'||substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,120)); end;