--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% --门诊挂号 create or replace procedure SP_SF_BRXXB0_MZDZBL( PCZLX00 in char , --操作类型 0 新增挂号 1 退挂号 PWDWID0 in number , --EMR挂号ID 唯一号 HIS请记录，后面逆流程(退挂号)时有用 PBRID00 in number , --HIS病人ID PXM0000 in varchar2 , --HIS病人姓名 PGHLB00 in number , --挂号类别 PSFCZ00 in char , --是否初诊 1 初诊 0复诊 PJZYS00 in number , --挂号医生HIS工号 PJZKS00 in number , --挂号科室HIS编码 PGHRQ00 in char , --挂号日期 以YYYYMMDD格式进行传送 PGHSJ00 in char , --挂号时间 以HH24:MI:SS格式进行传送 PGHJE00 in number , --挂号费金额 PZCFJE0 in number , --诊查费金额 PZFJE00 in number , --自费金额 PGHHBID in number , --挂号号表ID PHZLB00 in varchar2 , --优先级别,Add at GuangZhou on 2002-11-25 PSFYYGH in char , --是否预约挂号,'Y':预约挂号,'N':正常挂号 PCZBZ00 in char , --挂号标志,'0':持卡病人,'1':现金病人 PSFSBLF in char , --是否收取病人的病历费,'Y':收病历费,'N':不收病历费 TSFXM00 out varchar2 , --挂号时收取的收费项目的连接串,以HIS的收费项目的唯一码进行传送，多个收费项目中间用,隔开 PZYE000 out number , ---总余额 PGHH000 in out varchar2 , --挂号号 PSFZRZY in varchar2 , --是否择日住院 PSZY000 in number , --舒张压 PSSY000 in number , --收缩压 PNZKSTD in varchar2 default '' , --是否脑卒快速通道 PMZCYSF in char default '' --门诊出院随访 '1'是'0'否，默认'0' ) as -- MODIFICATION HISTORY -- Person Date Comments -- chenyh 2018.03.19 create --huangzw 2021.04.10 sf_brxxb0修改脑卒快速通道字段NZKSTD for MZYSSJ5-20210410-001 -- ruanbh 2021.07.01 YS_BRBQB0.CZRQ00插入错误修复 MZYSSJ5-20210629-001 --huangzw 2021.07.15 ys_brxxb0新增门诊出院随访字段 MZYSSJ5-20210625-001 -- chenna 2022.03.22 证件类型长度定义跟随 BM_BRXXB0.BRZJLX MZYSSJ5-20220307-002 -- ruanbh 2023.07.06 增加判断GHXHS0=GHYGHS时，过程SP_SF_BRGHXX.GHHBID传空 MZYSSJ5-20230706-002 -- ruanbh 2023.08.18 挂号报错信息增加TYHMSG0 MZYSSJ5-20230818-001 TGHLBMC varchar2(50); --类别名称 TBRBLH0 varchar2(30); --门诊病历号 TXB0000 varchar2(10); --性别 TBRCSRQ varchar2(8); --出生日期 TFBBH00 number(5); --病人费别编号 TFBMC00 varchar(30); --病人费别名称 TBRGFZH varchar2(30); --公费证号 TBRDH00 varchar2(30); --电话,Add at GZ,on 2002-12-31 TBRHYZK varchar2(30); --婚姻状况,Add at GZ, on 2002-12-31 TCZY000 number(5); --操作员ID TGHYXM0 varchar2(40); --操作员姓名 TCZYKS0 number(5); --挂号员的科室 TJZYSXM varchar2(40); --就诊医生姓名 TYBID00 varchar2(30); --病人医疗保险号(医保Id),非医保病人='0' TYBKH00 varchar2(30); --病人医保卡号 TBRZY00 varchar2(30); --职业(考虑现金病人时,这些信息的填写) TBRMZ00 varchar2(30); --民族 TBRZJLX BM_BRXXB0.BRZJLX%TYPE; --证件类型 TBRZJBH varchar2(30); --证件编号 TTYDWMC varchar2(200); --单位名称 TBRJTDZ varchar2(200); --家庭地址/通信地址 TFJJZDH number; --除了挂号费外的附加费的结算单号(供医保接口使用) --NEW! TZFDJH0 number; --自费金额对应的单据号 TTJDJH0 number; --医保的统筹基金对应的单据号 TGRDJH0 number; --医保的个人账户对应的单据号 TSBZHDH number; --商保个人帐户单据流水号 TSBTJDH number; --商保统筹基金单据流水号 TYHMSG0 varchar2(200); --存储过程提示的中文错误信息 TSYSMSG varchar2(200); --系统提示的错误信息 VCOUNTER number; --计数器变量 ECUSTOM exception; --异常 VTSXX00 varchar2(200); --异常信息 TDJH000 number; --收费单据号 TGHH000 char(12); --guah TBRBQID number; --病人病情ID VYGHH00 char(12); --预挂号号 VGHHBID SF_YSGHHB.GHHBID%type; --挂号号表ID TGHID00 SF_BRXXB0.GHID00%TYPE; --挂号ID V_ERROR0 VARCHAR2(200); dt1 date; dt2 date; VGHHBID2 SF_YSGHHB.GHHBID%type; --挂号号表ID begin dt1:=sysdate; SP_TRANSLOG(sysdate,'SP_SF_BRXXB0_MZDZBL',PJZYS00,PJZKS00,'开始PGHH000:'||PWDWID0); select count(*) into VCOUNTER from bm_ghlbb0 where LBBH00=PGHLB00; if Vcounter=0 then VTSXX00 := '挂号类别ID='||PGHLB00||'不存在！'; raise ECUSTOM; end if; select LBMC00 into TGHLBMC from bm_ghlbb0 where LBBH00 = PGHLB00; TCZY000 := PJZYS00; select count(*) into VCOUNTER from bm_ygbm00 where YGBH00=TCZY000; if Vcounter=0 then VTSXX00 := '员工编号='||TCZY000||'不存在！'; raise ECUSTOM; end if; select ZWXM00,BMBH00 into TGHYXM0,TCZYKS0 from bm_ygbm00 where YGBH00 = TCZY000; TJZYSXM := TGHYXM0; if PCZLX00 = 0 then VGHHBID:=PGHHBID; if VGHHBID is null or VGHHBID=0 then select count(*) into VCOUNTER from SF_YSGHHB where GHKSBH=PJZKS00 and GHYSBH=PJZYS00 and GHMZSJ = to_char(sysdate,'yyyymmdd') and GHQSSJ <= to_char(sysdate,'hh24:mi:ss') and GHJSSJ>= to_char(sysdate,'hh24:mi:ss') and TZBZ00='0'; if VCOUNTER>0 then select GHHBID into VGHHBID from SF_YSGHHB where GHKSBH=PJZKS00 and GHYSBH=PJZYS00 and GHMZSJ = to_char(sysdate,'yyyymmdd') and GHQSSJ <= to_char(sysdate,'hh24:mi:ss') and GHJSSJ>= to_char(sysdate,'hh24:mi:ss') and TZBZ00='0' and rownum=1; end if; end if; select count(*) into VCOUNTER from bm_brxxb0 where BRID00=PBRID00; if Vcounter=0 then VTSXX00 := '病人ID='||PBRID00||'不存在！'; raise ECUSTOM; end if; select BRBLH0,BRXB00,BRCSRQ,FBBH00,BRGFZH,BRDH00,BRHYZK,YBID00,YBKH00,BRZY00,BRMZ00,BRZJLX,BRZJBH,TYDWMC,BRJTDZ into TBRBLH0,TXB0000,TBRCSRQ,TFBBH00,TBRGFZH,TBRDH00,TBRHYZK,TYBID00,TYBKH00,TBRZY00,TBRMZ00,TBRZJLX,TBRZJBH,TTYDWMC,TBRJTDZ from bm_brxxb0 where BRID00 = PBRID00; select count(*) into VCOUNTER from BM_BRFBB0 where FBBH00=TFBBH00; if Vcounter=0 then VTSXX00 := '费别编号='||TFBBH00||'不存在！'; raise ECUSTOM; end if; select FBMC00 into TFBMC00 from BM_BRFBB0 where FBBH00=TFBBH00; dt2:=sysdate; ---- ruanbh 2023.07.06 增加GHXHS0=GHYGHS时，过程SP_SF_BRGHXX.GHHBID传空 select count(*) into VCOUNTER from SF_YSGHHB where GHHBID=VGHHBID and GHXHS0>GHYGHS; if VCOUNTER>0 then VGHHBID2:=VGHHBID; end if; SP_TRANSLOG(sysdate,'SP_SF_BRGHXX',PJZYS00,PJZKS00,'开始PGHH000:'||PWDWID0); SP_SF_BRGHXX(as_BRBLH0 => TBRBLH0,as_YBGHH0 => 0,as_GHLBMC => TGHLBMC,ad_BRID00 => PBRID00,as_SFCZ00 => PSFCZ00,as_XM0000 => PXM0000,as_XB0000 => TXB0000,as_CSRQ00 => TBRCSRQ,as_FBMC00 => TFBMC00,as_GFZH00 =>TBRGFZH,as_BRDH00 => TBRDH00,as_BRHYZK => TBRHYZK,ad_GHY000 => TCZY000,as_GHYXM0 => TGHYXM0,ad_CZYKS0 => TCZYKS0,ad_GHKS00 => PJZKS00,ad_JZYS00 => PJZYS00,as_JZYSXM => TJZYSXM,ad_GHHBID => VGHHBID2,as_YJJXH0 => null ,as_FPXH00 => null,ad_GHF000 => PGHJE00,ad_GRZHZF => null,ad_TCJJZF => null,ad_ZFJE00 => PZFJE00,as_ZPZH00 => null,as_YBID00 => '0',as_YBKH00 => '0',as_GHRQ00 => PGHRQ00,as_SFYYGH => PSFYYGH,as_CZBZ00 => PCZBZ00,as_SFSBLF => PSFSBLF,as_BRZY00 => TBRZY00,as_BRMZ00 => TBRMZ00,as_BRZJLX => TBRZJLX,as_BRZJBH => TBRZJBH,as_TYDWMC => TTYDWMC,as_SFNLYD => 'N',as_HZLB00 => PHZLB00,as_ICKH00 => 0,as_BRJTDZ => TBRJTDZ ,ad_FJJZDH => TFJJZDH,ad_ZFDJH0 => TZFDJH0,ad_TJDJH0 => TTJDJH0,ad_GRDJH0 => TGRDJH0,ad_SBZHDH => TSBTJDH,ad_SBTJDH => TSBTJDH,as_GHH000 => PGHH000,as_YHMSG0 => TYHMSG0,as_SYSMSG => TSYSMSG ); SP_TRANSLOG(sysdate,'SP_SF_BRGHXX',PJZYS00,PJZKS00,'结束PGHH000:'||PWDWID0||',耗时:'||ROUND(TO_NUMBER(sysdate - dt2) * 24 * 60 * 60)); if TSYSMSG is not null then VTSXX00 := replace(TSYSMSG,'预交金:','预交金：'); if TYHMSG0 is not null then VTSXX00 := VTSXX00 || TYHMSG0; end if; raise ECUSTOM; end if; if TYHMSG0 is not null then VTSXX00 := TYHMSG0; raise ECUSTOM; end if; TGHH000 :=PGHH000; select count(*) into VCOUNTER from sf_brxxb0 where GHH000 = TGHH000; if Vcounter=0 then VTSXX00 := '病人挂号号='||PGHH000||'不存在！'; raise ECUSTOM; end if; select GHID00 into TGHID00 FROM sf_brxxb0 where GHH000 = TGHH000; V_ERROR0 := SF_YS_GHZDXZ(ad_GHLB00 => PGHLB00,ad_GHYS00 =>PJZYS00,ad_GHID00 => TGHID00,as_SFZRZY => PSFZRZY,as_GHSDY0 => '0',ad_BRID00 => PBRID00,ad_ghks00 => PJZKS00); if V_ERROR0<>'0' then VTSXX00 := replace(V_ERROR0,':',','); raise ECUSTOM; end if; update sf_brxxb0 set WDWID0 = PWDWID0,SJLY00 = '1',thbz00 = '2',sfzrzy = PSFZRZY,NZKSTD=PNZKSTD,MZCYSF=PMZCYSF where GHH000 = TGHH000 ; select SQ_YS_BRBQB0_BRBQID.Nextval into TBRBQID from dual; insert into YS_BRBQB0(BRBQID,GHID00,GHH000,SFCZ00,SZY000,SSY000,JZYS00,CZRQ00,CZSJ00) values(TBRBQID ,TGHID00,PGHH000,'1',PSZY000,PSSY000,PJZYS00,PGHRQ00,PGHSJ00); --处理预挂号 select max(GHH000) into VYGHH00 from SF_BRXXB0 where BRID00=PBRID00 and GHLB00=-99999 and JZYS00=PJZYS00 and JZKS00=PJZKS00 and GHHBID=VGHHBID; if VYGHH00 is not null then Update SF_BRXXB0 set QHY000=(select QHY000 from SF_BRXXB0 where GHH000=VYGHH00) where GHH000=TGHH000; Delete SF_HZBR00 a WHERE YSPBID=VGHHBID and GHH000 in(select GHH000 from SF_BRXXB0 where BRID00=PBRID00 and GHLB00=-99999 and JZYS00=PJZYS00 and JZKS00=PJZKS00); DELETE SF_BRXXB0 a WHERE GHHBID=VGHHBID and BRID00=PBRID00 and GHLB00=-99999 and JZYS00=PJZYS00 and JZKS00=PJZKS00 and not exists( select 1 from sf_brfy00 where MZID00=a.GHID00); end if; update SF_HZBR00 set HZZT00='1',YZRQ00=to_char(sysdate,'yyyymmdd'),YZSJ00=to_char(sysdate,'hh24:mi:ss') where GHH000=TGHH000 and HZZT00 in ('0','2','4'); elsif PCZLX00 = 1 then TGHH000 := to_char(PWDWID0); select count(*) into VCOUNTER from sf_brxxb0 where GHH000=TGHH000; if Vcounter=0 then VTSXX00 := 'GHH000='||TGHH000||'不存在！'; raise ECUSTOM; end if; select GHH000,BRBLH0,XB0000,CSRQ00,FBBH00 into TGHH000,TBRBLH0,TXB0000,TBRCSRQ,TFBBH00 from sf_brxxb0 where GHH000=TGHH000; select count(*) into VCOUNTER from BM_BRFBB0 where FBBH00=TFBBH00; if Vcounter=0 then VTSXX00 := '费别编号='||TFBBH00||'不存在！'; raise ECUSTOM; end if; select FBMC00 into TFBMC00 from BM_BRFBB0 where FBBH00=TFBBH00; --作废未记账处方 update YS_CFXXB0 set CFZT00='2' where GHH000=TGHH000 and CFZT00='0'; update YF_MZCF00 set CFZT00='3' where GHH000=TGHH000 and CFZT00='0'; --作废未记账项目 update YJ_YW0000 set XMZT00='6' where ZYHGHH= TGHH000 and mzzybz = '0' and sl0000>0 and xmzt00 in ('0','1','9'); update sf_brxxb0 set thbz00 = '1' where GHH000 = TGHH000 ; SP_SF_BRGHXX_EDIT00(as_SFNLYD => null,as_HZLB00 => PHZLB00,as_ICKH00 => 0,as_GHH000 => TGHH000,as_YBGHH0 => 0 ,as_BRBLH0 => TBRBLH0,as_GHLBMC => TGHLBMC,as_SFCZ00 => PSFCZ00,as_XM0000 => PXM0000,as_XB0000 => TXB0000 ,as_CSRQ00 => TBRCSRQ,as_FBMC00 => TFBMC00,ad_GHY000 => TCZY000,as_GHYXM0 => TGHYXM0,ad_CZYKS0 => TCZYKS0 ,ad_GHKS00 => PJZKS00,ad_JZYS00 => PJZYS00,as_JZYSXM => TJZYSXM,ad_GHHBID => PGHHBID,as_YJJXH0 => null ,ad_GHF000 => PGHJE00,ad_GRZHZF => null,ad_TCJJZF => null,ad_ZFJE00 => PZFJE00,as_ZPZH00 => null ,as_CZBZ00 => 0,as_SFSBLF => PSFSBLF,ad_ZFDJH0 => TZFDJH0,ad_TJDJH0 => TTJDJH0,ad_GRDJH0 => TGRDJH0 ,as_YHMSG0 => TYHMSG0,as_SYSMSG => TSYSMSG); if TYHMSG0 is not null then VTSXX00 := TYHMSG0; raise ECUSTOM; end if; if TSYSMSG is not null then VTSXX00 := TSYSMSG; raise ECUSTOM; end if; for RW in(select YJDJH0,SL0000 from YJ_YW0000 a where ZYHGHH = TGHH000 and MZZYBZ = '0' and nvl(CXDJH0,0)=0 and a.zje000>0 and a.xmzt00<>'6' and a.sl0000>0 and not exists(select 1 from YJ_YW0000 where CXDJH0=a.YJDJH0 and zyghid=a.zyghid and mzzybz=a.mzzybz and xmzt00 in ('2','3'))) loop SP_YJ_YWCX( RW.YJDJH0, --医技单据号 PJZYS00, --员工编号 PJZKS00, --科室编号 'Y', --病区申请的项目是否直接退费 'N', --是否重新申请 RW.SL0000 --冲消数量 ); end loop; end if; for dacolum in (select a.XMBH00 from sf_fymx00 a,sf_brfy00 b where a.DJH000 = b.DJH000 and b.mzh000 = TGHH000) loop if TSFXM00 is null then TSFXM00 := dacolum.xmbh00; else TSFXM00 := TSFXM00||','||dacolum.xmbh00; end if; end loop; select D.ZYE000 into PZYE000 FROM BM_BRXXB0 A,IC_YBBRLB C,SF_BRZHXX D where A.YBLB00=C.YBLB00 and A.FBBH00=C.FBBH00 and A.BRID00=D.BRID00(+) and A.BRID00=PBRID00; delete from XT_ZJRWB0 where BM0000='SF_BRXXB0' and ZJZDZ0=TGHH000 and ZXBZ00='0'; delete from XT_ZJRWB0 where BM0000='YJ_YW0000' and ZJZDZ0 in(select to_char(YJDJH0) from YJ_YW0000 where ZYHGHH=TGHH000) and ZXBZ00='0'; SP_TRANSLOG(sysdate,'SP_SF_BRXXB0_MZDZBL',PJZYS00,PJZKS00,'结束PGHH000:'||PWDWID0||',耗时:'||ROUND(TO_NUMBER(sysdate - dt1) * 24 * 60 * 60)); exception when no_data_found then raise_application_error(-20001,substrb('没有找到数据！*'||sqlerrm,1,240)); when ECUSTOM then raise_application_error(-20010,substrb(VTSXX00||'！*',1,240)); when others then raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240)); end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%