CREATE OR REPLACE PROCEDURE SP_SST_JSDP_JSMX_YKT( P_ZDBH00 in varchar2, --终端编号 P_FZXBH0 in varchar2, --参保机构编号 P_YYJGDM in varchar2, --医院机构代码 P_CARDTYPE in varchar2, --0 代表院内卡 1 代表社保卡 P_CARDNO in varchar2, --IC卡号 P_BYRC00 in varchar2, --备用入参 P_ERRMSG out varchar2 --出错信息 ) as -- MODIFICATION HISTORY -- Person Date Comments -- zhangyc 2012.11.01 create; -- qks 2013.03.13 去掉提示信息中的 "当天" -- qks 2013.12.25 sst_jsdp_jbxx增加Ksbm00 -- qks 2014.04.09 sst_jsdp_jbxx增加YKFJE0 -- zhangyc 2015.01.06 增加记录SST_YKTDJH --zhangyc 2016.01.07 重新整理脚本 --zhangyc 2016.01.11 增加字段YJJYE0 --zhangyc 2016.12.29 注释掉农合结算限制 --zhangyc 2017.01.19 增加参数(SST_ZZJGHYXTS)控制有效挂号时间 --zhangyc 2017.04.01 增加SST_NHBRSFJS 控制农合结算 以及农合病人id0000发送医保卡号 --xuchenjun 2021.05.20 门诊特殊病种编码不允许在自助机上结算 2021-05-20 17:34:33 LS_ICKQZMC XT_XTCS00.VALUE0%type; LS_SSTTSBZ XT_XTCS00.VALUE0%type; LS_CARDNO IC_YHXX00.ICKH00%type; LS_YGBH00 BM_YGBM00.YGBH00%type; LS_YGXM00 BM_YGBM00.ZWXM00%type; LS_BRID00 BM_BRXXB0.BRID00%type; LS_ERRORFLAG varchar2(200); LS_KSRQ00 varchar2(8); --挂号开始日期 LS_JSRQ00 varchar2(8); --挂号结束日期 LS_COUNT number(5); LS_BRNL00 number(3); --病人年龄 LS_FYJE00 number(12,2); LS_CFJE00 number(12,2); LS_YBKH00 BM_BRXXB0.YBKH00%type; --医保卡号 LS_YBID00 BM_BRXXB0.YBID00%type; LS_BRXM00 BM_BRXXB0.BRXM00%type; --病人姓名 LS_BRXB00 BM_BRXXB0.BRXB00%type; --性别 LS_SFZHAO BM_BRXXB0.BRZJBH%type; LS_FBBH00 BM_BRXXB0.FBBH00%type; --性别 LS_XTGZH0 SST_JSDP_JBXX.XTGZH0%type; --系统跟踪号 LS_GHID00 SF_BRXXB0.GHID00%type; LS_GHH000 SF_BRXXB0.GHH000%type; LS_GHKSMC BM_BMBM00.BMMC00%type; LS_YSXM00 SF_BRXXB0.GHYXM0%type; --医生姓名 LS_TSBZBH varchar2(20); --特殊病种编号 LS_TSBZMC varchar2(50); --特殊病种名称 LS_SFTSMZ varchar2(2); --是否特殊门诊 Y是 N否 LS_SFTSBZ varchar2(2); --是否特殊病种 Y是 N否 LS_YBZXLB IC_YBBRLB.YBZXLB%type; --医保中心类别编码 LS_SFXNH0 IC_YBBRLB.SFXNH0%type; LS_YBMC00 IC_YBBRLB.YBMC00%type; LS_YBLB00 IC_YBBRLB.YBLB00%type; LS_XJZFFS varchar2(2); --现金支付方式 1、银行委托支付；2、预缴金支付；3、电子钱包支付 LS_SFSYU0 varchar2(2); --是否生育收费 SF_BRXXB0.BXLB00=’ 工伤’ OR SF_BRXXB0.BXLB00=’ 生育’ LS_KFPC00 number(12); --扣费批次 LS_ZFBRSFJS Varchar2(100); ---自助机自费病人是否记账结算 Y:记账并结算 N:记账不结算 默认值：N LS_CFYJJS Varchar2(100); --是否把医技，费用一起结算（医保病人都要 自费病人参数LS_ZFBRSFJS=Y 都要） LS_YBKSBH BM_BMBM00.YBKSBH%type; --医保科室编号 LS_YKTYKF number(12,4); LS_FYXMS0 number(5); --费用项目数 LS_YJXMS0 number(5); --医技项目数 LS_CFXMS0 number(5); --处方项目数 LS_BCFYZE number(12,4);--本次费用总额 LS_FZXBH0 Varchar2(20); LS_CZRQ00 varchar2(8); LS_CZSJ00 varchar2(8); LS_ZZJGHYXTS number(5);--自助机挂号有效天数 SST_NHBRSFJS Varchar2(100); cursor CUR_SF_GHXX00 IS select GHID00,GHH000,GHKSMC,JZYSXM,DECODE(trim(LS_SSTTSBZ),'Y',TSBZBH,null)TSBZBH,YBKSBH from VW_SST_BRGH00 A where SFYY00 = 'N' and THRQ00 is null and BRID00=LS_BRID00 and GHRQ00 >=LS_KSRQ00 and GHRQ00 <=LS_JSRQ00 order by GHH000; cursor CUR_DBJSMX is select distinct XTGZH0 from SST_JSDP_JBXX where CARDNO=P_CARDNO and BRID00=LS_BRID00 order by XTGZH0; begin LS_ERRORFLAG:=''; LS_COUNT :=0; LS_XJZFFS:='2'; LS_SFSYU0:='N'; LS_FYJE00:=0; LS_CFJE00:=0; LS_FZXBH0:=P_FZXBH0; LS_CZRQ00:=to_char(sysdate,'YYYYMMDD'); LS_CZSJ00:=to_char(sysdate,'HH24:MI:SS'); --获取自助机终端编号对应的收费员原编号 begin select YGBH00,ZWXM00 into LS_YGBH00,LS_YGXM00 from VW_SST_YGBMDYXX where ZZJBH0=P_ZDBH00; exception when no_data_found then P_ERRMSG:='终端编号:'||P_ZDBH00||'在本院SST_YGBHDY表中未对应收费员!'; return; end; --自助结算是否按特殊病种结算 begin select upper(trim(VALUE0)) into LS_SSTTSBZ from XT_XTCS00 where NAME00 = 'SST_YBJSSAZTSBZ'; exception when others then LS_SSTTSBZ:='Y'; end; --获取挂号号有效天数 begin select to_number(upper(trim(VALUE0))) into LS_ZZJGHYXTS from XT_XTCS00 where NAME00 = 'SST_ZZJGHYXTS'; exception when others then LS_ZZJGHYXTS:=0; end; --农合病人是否允许自助机结算 Y:是 N:否 默认值:Y begin select upper(trim(VALUE0)) into SST_NHBRSFJS from XT_XTCS00 where NAME00 = 'SST_NHBRSFJS'; exception when others then SST_NHBRSFJS:='Y'; end; if SST_NHBRSFJS is null then SST_NHBRSFJS:='Y'; end if; LS_KSRQ00:=to_char(sysdate-LS_ZZJGHYXTS,'YYYYMMDD'); LS_JSRQ00:=to_char(SYSDATE,'YYYYMMDD'); select SF_SST_GETICKH00_YKT(P_CARDNO) into LS_CARDNO from dual; begin select brid00 into LS_BRID00 from IC_YHXX00 where ICKH00 =LS_CARDNO and ZT0000 ='1'; exception when no_data_found then P_ERRMSG:='未找到该病人的信息,请确认是否在院内已建档登记!'; return; end; begin select count(1) into LS_COUNT from VW_SST_BRGH00 A where SFYY00 = 'N' and THRQ00 is null and BRID00=LS_BRID00 and GHRQ00 >=LS_KSRQ00 and GHRQ00 <=LS_JSRQ00; exception when no_data_found then LS_COUNT:=0; end; if LS_COUNT=0 then P_ERRMSG:='未找到该病人的就诊信息！'; return; end if; Select a.YBKH00,a.YBID00,a.BRXM00,a.BRXB00,a.BRZJBH,a.FBBH00, SF_YS_CSRQTONL(BRCSRQ)BRNL00,C.YBZXLB,C.SFXNH0,C.YBMC00,C.YBLB00 into LS_YBKH00,LS_YBID00,LS_BRXM00,LS_BRXB00,LS_SFZHAO,LS_FBBH00,LS_BRNL00,LS_YBZXLB,LS_SFXNH0,LS_YBMC00,LS_YBLB00 from BM_BRXXB0 a ,IC_YBBRLB c Where a.FBBH00=c.FBBH00 and a.YBLB00 = c.YBLB00 and a.brid00=LS_BRID00; if LS_FZXBH0 is null then select SF_SST_FZXBH0_YKT(LS_BRID00) into LS_FZXBH0 from dual; end if; --/* --农合病人暂时不支持自助结算功能 if (LS_SFXNH0='1') and (SST_NHBRSFJS='N') then P_ERRMSG := '农合病人暂时不提供自助结算功能!'; return; end if; --异地医保病人暂时不支持自助结算功能 if (ls_SFXNH0='3') and (SST_NHBRSFJS='N') then P_ERRMSG := '异地医保病人暂时不提供自助结算功能!'; return; end if; --*/ --自助机自费病人是否记账结算 Y:记账并结算 N:记账不结算 默认值：N begin select upper(trim(VALUE0)) into LS_ZFBRSFJS from XT_XTCS00 where NAME00 = 'SST_ZFBRSFJS'; exception WHEN others then LS_ZFBRSFJS:='N'; end; --2016.01.11 增加参数控制SST_CFYJJS 自助机结算自费病人是否把医技,费用一起结算(医保病人并且参数SST_ZFBRSFJS=Y 都参与结算) Y:参与结算 N:不参与结算 默认值:N begin select upper(trim(VALUE0)) into LS_CFYJJS from XT_XTCS00 where NAME00 = 'SST_CFYJJS'; exception WHEN others then LS_CFYJJS:='N'; end; if (ls_FBBH00=3) or (LS_ZFBRSFJS='Y') then LS_CFYJJS:='Y'; end if; /* if ls_FBBH00=3 then LS_CFYJJS:='Y'; else ----自助机自费病人是否记账结算 Y:记账并结算 N:记账不结算 默认值：N--- if LS_ZFBRSFJS='Y' then LS_CFYJJS:='Y'; else LS_CFYJJS:='N'; end if; end if; */ -- SQ_YF_MZCF00_KFPC00 if LS_COUNT>0 then delete SST_JSDP_JBXX where CARDNO=P_CARDNO; for CUR_GH in CUR_SF_GHXX00 loop LS_GHID00:=CUR_GH.GHID00; LS_GHH000:=CUR_GH.GHH000; LS_GHKSMC:=CUR_GH.GHKSMC; LS_YSXM00:=CUR_GH.JZYSXM; LS_TSBZBH:=CUR_GH.TSBZBH; LS_YBKSBH:=CUR_GH.YBKSBH; if trim(LS_TSBZBH) is null then LS_SFTSMZ:='N'; LS_SFTSBZ:='N'; LS_TSBZBH:='无'; LS_TSBZMC:='无'; else LS_SFTSMZ:='Y'; LS_SFTSBZ:='Y'; begin select MC0000 into LS_TSBZMC from BM_TSBZB0 where BH0000 = ls_TSBZBH and YBZXLB =LS_YBZXLB; exception when no_data_found then LS_TSBZMC:='无'; end; end if; --未结算费用 if LS_CFYJJS='Y' then select nvl(sum(HJJE00),0),Count(*) Into LS_FYJE00,LS_FYXMS0 from sf_Brfy00 where brid00=LS_BRID00 and MZID00=LS_GHID00 and MZH000=LS_GHH000 and jzdh00+0=0; else LS_FYJE00:=0; end if; --未扣费处方 select nvl(sum(hjje00),0),Count(*) into LS_CFJE00,LS_YJXMS0 from VW_SST_WJZCF WHERE BRID00=LS_BRID00 and GHID00=LS_GHID00 and GHH000=LS_GHH000 and zt0000=0 and (((LS_CFYJJS='Y') and (1=1)) or ((LS_CFYJJS='N') and (CFYJBZ='0'))); if (nvl(LS_FYJE00,0)+nvl(LS_CFJE00,0))>0 then select SST_JSDP_JBXX_XTGZH0.NEXTVAL into LS_XTGZH0 from dual; select nvl(max(KFPC00),0) into LS_KFPC00 from SST_JSDP_JBXX where cardno=P_CARDNO; if nvl(LS_KFPC00,0)=0 then select SQ_YF_MZCF00_KFPC00.NEXTVAL into LS_KFPC00 from dual; end if; LS_BCFYZE:=nvl(LS_FYJE00,0)+nvl(LS_CFJE00,0);--本次费用总额 LS_CFXMS0:=nvl(LS_FYXMS0,0)+nvl(LS_YJXMS0,0);--处方项目数 insert into SST_JSDP_JBXX( FZXBH0,XTGZH0,CARDNO,ID0000,XMING0,XBIE00,BRID00, MZLSH0,BQBM00,BQMC00,CFXMS0,BRNL00, BCKBCS,BCSFCS,GHKSMC,SFTSMZ,SFTSBZ, YSXM00,BCFYZE,XJZFFS,JSXXTS,SFSYU0,Serialno, ZDBH00,QBKKQR,KFPC00,KSBM00,YKFJE0,CZRQ00,CZSJ00,YJJYE0, SFRQ00,SFSJ00,SFRXM0,FBBH00,YBZXLB,YBLB00,YBMC00,YBKH00,YBID00,SFZHAO,YYDJH0) values( LS_FZXBH0,LS_XTGZH0,P_CARDNO,trim(LS_YBKH00),trim(LS_BRXM00),decode(LS_BRXB00,'男','1','0'),LS_BRID00, '无',LS_TSBZBH,substrb(trim(LS_TSBZMC),1,20),LS_CFXMS0,LS_BRNL00, 0,0,LS_GHKSMC,LS_SFTSMZ,LS_SFTSBZ, LS_YSXM00,LS_BCFYZE,LS_XJZFFS,'无','N','无', P_ZDBH00,LS_SFSYU0,nvl(LS_KFPC00,0),LS_YBKSBH,LS_FYJE00, LS_CZRQ00,LS_CZSJ00,SF_SST_GetBRZHYE_YKT(0,LS_BRID00,1,0), LS_CZRQ00,LS_CZSJ00,LS_YGXM00,LS_FBBH00,LS_YBZXLB,LS_YBLB00,LS_YBMC00,LS_YBKH00,LS_YBID00,LS_SFZHAO,LS_XTGZH0); insert into SST_SFDJH0(XTGZH0,BRID00,BRXM00,CARDNO,GHID00,GHH000,DJH000,TSBZBH,JZDH00,CZRQ00,CZSJ00) values(LS_XTGZH0,ls_BRID00,LS_BRXM00,LS_CARDNO,LS_GHID00,LS_GHH000,0,LS_TSBZBH,0,LS_CZRQ00,LS_CZSJ00); delete SST_YKTDJH where BRID00=LS_BRID00 and XTGZH0=LS_XTGZH0; --插入SST_YKTDJH insert into SST_YKTDJH ( XTGZH0, --系统跟踪号F.K=SST_JSDP_JBXX.XTGZH0 YKTID0, --一卡通id,F.K=SF_YKTKFB.ID0000 ID0000, --ID0000,F.K=SF_DJZFFS.ID0000 CARDNO, --卡号 BRID00, --病人ID BRXM00, --姓名 DJH000, --费用单据号 CXDJH0, --冲销单据号 JZDH00, --结账单号 HJJE00,--合计金额 ZFJE00,--自费金额 XJZF00,--现金支付 ZZZF00,--转账支付 YKTZF0,--一卡通支付金额 YKTKTE,--一卡通可退金额 YKTYKE,--一卡通已退金额 YKTSTE,--一卡通实退金额 CZRQ00,--操作日期 CZSJ00,--操作时间 CZY000, --操作员 YYKTID, --原一卡通id YKTLSH, --一卡通流水号 CZFS00,--操作方式 0:支付 1:退货 ZDBH00 ) select LS_XTGZH0, YKTID0, ID0000, P_CARDNO, BRID00, trim(LS_BRXM00), DJH000, 0, 0, HJJE00, ZFJE00, XJZF00, ZZZF00, nvl(MSTZF0,0), nvl(MSTZF0,0)-nvl(MSTYT0,0), nvl(MSTYT0,0), 0, to_char(sysdate,'YYYYMMDD'), to_char(sysdate,'HH24:MI:SS'), LS_YGBH00, '', '', '0', P_ZDBH00 from SF_DJZFFS Where BRID00=LS_BRID00 and nvl(MSTZF0,0)<>0 and DJLX00 in ('1') and JZDH00=0 --1:扣费 8:取消结算补扣 ,'8' and DJH000 in ( select DJH000 from sf_Brfy00 where brid00=LS_BRID00 and MZID00=LS_GHID00 and MZH000=LS_GHH000 and jzdh00+0=0 ); select nvl(sum(YKTZF0),0) into LS_YKTYKF from SST_YKTDJH where BRID00=LS_BRID00 and XTGZH0=LS_XTGZH0; update SST_JSDP_JBXX set YKTYKF=LS_YKTYKF where BRID00=LS_BRID00 and XTGZH0=LS_XTGZH0; update SST_YKTDJH a set YKTLSH=(select substrb(JYLSH0,1,50) from SF_YKTKFB b where b.BRID00=LS_BRID00 and b.ID0000=a.YKTID0 and rownum=1) where a.BRID00=LS_BRID00 and a.XTGZH0=LS_XTGZH0; end if; end loop; end if; Select count(0) into LS_COUNT from SST_JSDP_JBXX where CARDNO=P_CARDNO; if LS_COUNT=0 then if (LS_CFYJJS='N') and (ls_FBBH00=31) then P_ERRMSG := '未找到该病人的未记账处方信息！'; else P_ERRMSG := '未找到该病人的结算信息！'; end if; return; end if; ---门诊特殊病种编码不允许在自助机上结算 2021-05-20 17:34:33 select count(0) into LS_COUNT from SST_JSDP_JBXX where CARDNO=P_CARDNO and BRID00=LS_BRID00 and bqbm00 in (select trim(BH0000) from bm_tyzd00 where ZDMC00 = '自助机不允许特殊病种编码结算字典维护' and YXBZ00='1'); if LS_COUNT>0 then P_ERRMSG := '该特殊病情编码,不允许自助结算,请到收费窗口结算！'; return; end if; --循环执行SP_SST_JSDP_DBJSMX_YKT for CUR_MX in CUR_DBJSMX loop SP_SST_JSDP_DBJSMX_YKT ( P_ZDBH00, --in varchar2, --终端编号 P_FZXBH0, --in varchar2, --参保机构编号 P_YYJGDM, --in varchar2, --医院机构代码 P_CARDTYPE, --in varchar2, --0 代表院内卡 1 代表社保卡 P_CARDNO, --in varchar2, --IC卡号 CUR_MX.XTGZH0, --in varchar2, --系统跟踪号 P_BYRC00, --in varchar2, --备用入参 P_ERRMSG --out varchar2 --出错信息 ); end loop; exception when no_data_found then P_ERRMSG:='获取门诊结算明细失败!错误位置:('||ls_errorflag||')错误原因:'||SQLERRM; when others then P_ERRMSG:='获取门诊结算明细失败!错误位置:('||ls_errorflag||')错误原因:'||SQLERRM; END;