CREATE OR REPLACE PROCEDURE SP_SST_MZ_DAXXDJ_YKT( P_ZDBH00 in varchar2, --终端编号 P_FZXBH0 in varchar2, --参保机构代码 P_CARDNO in varchar2, --病人卡号 P_ID0000 in varchar2, --保险编号 P_SFZHAO in varchar2, --身份证号 P_XMING0 in varchar2, --姓名 P_XBIE00 in varchar2, --性别(0:男；1：女) -->(1:男；2：女) P_BRNL00 in varchar2, --病人年龄 P_RYLB00 in varchar2, --人员类别(工作状态) P_CSRQ00 in varchar2, --出生日期(如：19861202) P_FZXMC0 in varchar2, --分中心名称 P_GZDWMC in varchar2, --工作单位名称 P_GRZHYE in varchar2, --个人账户余额 P_LXDH00 in varchar2, --联系电话 P_BYRC00 in varchar2, --备用入参 P_ERRMSG out varchar2, --无法获取明细的错误信息，空表示正确 P_BYCC00 out varchar2, --备用出参 P_SFJZFP in varchar2 default 'N' --是否精准扶贫，Y 是, N 否, 默认N ) as -- MODIFICATION HISTORY -- Person Date Comments -- zhangyc 2012.04.27 create -- zhangyc 2014.01.01 增加P_LXDH00 -- zhangyc 2014.01.14 增加病历号判断 -- zhangyc 2014.03.07 如果没找到医保中心直接按自费建档 -- zhangyc 2014.04.11 原来性别(0:男；1：女) -->(1:男；2：女) -- zhangyc 2014.07.16 根据P_ID0000判断该病人是否在院内已经建档 -- dsm 2015.01.20 获取变量的地方加rownum=1 for ZZJJK-20150120-001 -- zhangyc 2015.10.20 修改健康通 备用入参 1全部建档 2、单独HIS建档 3、单独一卡通建档 -- zhangyc 2019.10.08 修正建档报错 -- linbin 2020.09.28 增加入参P_SFJZFP是否精准扶贫 for ZZJJK-20200917-001 -- qiulf 2021.12.29 增加医保卡是否已建档（身份证+姓名） by ZZJJK-20211227-001 -- --------- ---------- ------- ls_COUNT number(5); ls_BRXB00 BM_BRXXB0.BRXB00%type; --病人性别 ls_YBMC00 IC_YBBRLB.YBMC00%type; --医保中心名称 ls_YGBH00 BM_YGBM00.YGBH00%type; ls_YGXM00 BM_YGBM00.ZWXM00%type; ls_YGBMBH BM_YGBM00.BMBH00%type; ls_YHMSG0 varchar2(400); ls_SYSMSG varchar2(400); E_ERROR exception; LS_CARDNO varchar2(220); LS_ICKQZMC varchar2(50); LS_CarNum number(5); --卡号位数 ls_CZRQ00 char(8); ls_CZSJ00 char(8); v_icklb0 IC_YHXX00.ICKLB0%type; --'0'本院卡(默认), '1'外院卡, '2'医保卡 v_icksx0 IC_YHXX00.ICKSX0%type; v_KLDH00 IC_YHXX00.KLDH00%type; v_yyid00 IC_YYID00.YYID00%type; p_ywcz00 IC_YWLS00.YWCZ00%type; --0入库,1售病人卡,2售员工卡,3报停,4报废,5退卡,6取消报停,7重制空白卡,8医保卡登记,9外院卡登记 v_ywid00 IC_YWLS00.YWID00%type; LS_MZZHID SF_BRZHXX.MZZHID%type; --门诊账户ID LS_YBLBBH IC_YBBRLB.YBLB00%type; --病人所属医保中心名称对应的编号 LS_YBZXLB IC_YBBRLB.YBZXLB%type; --病人医保中心类别 LS_FBBH00 IC_YBBRLB.FBBH00%type; LS_BRID00 BM_BRXXB0.BRID00%type; --病人ID LS_YBBRLB BM_BRXXB0.YBBRLB%type; --医保病人类别 LS_BRBLH0 BM_BRXXB0.BRBLH0%type; LS_YBKH00 BM_BRXXB0.YBKH00%type; LS_YBID00 BM_BRXXB0.YBID00%type; LS_YBZHYE number(12,4); LS_KHQZBH varchar2(4); LS_CSRQ00 varchar2(10); LS_ZT0000 varchar2(10); LS_ID0000 SF_YKTKFB.ID0000%type; LS_YKTLSH varchar2(30); --一卡通流水号 LS_YYID00 XT_YYXX00.YYID00%type; LS_SFJZFP BM_BRXXB0.SFJZFP%type; --是否精准扶贫 begin P_ERRMSG:=''; ls_CZRQ00 := to_char(sysdate,'YYYYMMDD'); ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); LS_CSRQ00:=P_CSRQ00; ls_SYSMSG:='获取终端编码'; begin select YGBH00,ZWXM00,BMBH00 into ls_YGBH00,ls_YGXM00,ls_YGBMBH from VW_SST_YGBMDYXX where ZZJBH0=P_ZDBH00; exception when NO_DATA_FOUND then P_ERRMSG:='终端编号:'||P_ZDBH00||'在本院SST_YGBHDY表中未对应收费员!'; return; end; begin select YYID00 into v_yyid00 from IC_YYID00 where BZ0000 = '0'; --标志:0本院，1外院 exception when no_data_found then v_yyid00:= '0'; end; select length(P_CARDNO)into LS_CarNum from dual; if LS_CarNum=9 then --社保卡 v_KLDH00:='YB-SBK'; v_icksx0:=v_yyid00; v_icklb0:='2'; p_ywcz00:='8'; elsif LS_CarNum=12 then --医保卡 v_KLDH00:='YB-MMRK'; v_icksx0:=v_yyid00; v_icklb0:='2'; p_ywcz00:='8'; else LS_KHQZBH:=''; v_KLDH00:='SLE4442'; v_icklb0:='0'; v_icksx0:=v_yyid00; p_ywcz00:='0'; end if; select SF_SST_GETICKH00_YKT(P_CARDNO) into LS_CARDNO from dual; ls_SYSMSG:='判断是否重复建档'; if nvl(P_BYRC00,'2') in ('1','2') then --1全部建档 2、单独HIS建档 3、单独一卡通建档 select Count(*) into ls_count from IC_YHXX00 where ICKH00 = LS_CARDNO AND ZT0000='1'; if ls_count > 0 then P_ERRMSG:='该卡号已经建档，不能重复建档!'; return; end if; ls_SYSMSG:='根据医保ID判断是否重复建档'; --2014.07.16 根据P_ID0000判断该病人是否在院内已经建档 if nvl(P_ID0000,'0')<>'0' then --2018.06.29 返回多行，修改 /* begin select BRID00 into LS_BRID00 from BM_BRXXB0 where YBID00=rpad(P_ID0000,30,' ') ; exception when no_data_found then LS_BRID00:=0; end; select Count(*) into ls_count from IC_YHXX00 where BRID00 = LS_BRID00 AND ZT0000='1'; select count(*) into ls_count from BM_BRXXB0 a,IC_YHXX00 b where a.brid00=b.brid00 and a.YBID00=rpad(P_ID0000,30,' ') AND ZT0000='1'; if ls_count > 0 then P_ERRMSG:='医保ID:'||P_ID0000||'在院内已经建档,不能重复建档!'; return; end if;*/ --根据医保卡号和姓名判断是否建档--begin-- --根据医保ID和姓名判断 select count(*) into ls_count from sf_brzhxx where SFDJ00<>'2'and brid00 in ( select brid00 from bm_brxxb0 where brxm00=P_XMING0 and ybid00=P_ID0000); if ls_count>0 then begin select substr('(病历号:'||trim(b.BRBLH0)||'卡号:'||a.ickh00||')',1,50) into P_ERRMSG from ic_yhxx00 a,bm_brxxb0 b where a.brid00=b.brid00 and a.zt0000='1' and a.brid00 in (select brid00 from bm_brxxb0 where brxm00=P_XMING0 and ybid00=P_ID0000) and rownum=1; exception when others then P_ERRMSG:=''; end ; P_ERRMSG:='该医保病人已经建过卡，不能再次建卡，请使用换卡或补卡功能！'||substrb(P_ERRMSG,1,120); return; end if; --一代证只有15位YBID00 MZSF-20210609-002 select count(*) into LS_COUNT from sf_brzhxx where SFDJ00<>'2'and brid00 in ( select brid00 from bm_brxxb0 where brxm00=P_XMING0 and trim(ybid00)=substrb(P_ID0000,1,6)||substrb(P_ID0000,9,9)); if LS_COUNT>0 then begin select substr('(病历号:'||trim(b.BRBLH0)||'卡号:'||a.ickh00||')',1,50) into P_ERRMSG from ic_yhxx00 a,bm_brxxb0 b where a.brid00=b.brid00 and a.zt0000='1' and a.brid00 in (select brid00 from bm_brxxb0 where brxm00=P_XMING0 and trim(ybid00)=substrb(P_ID0000,1,6)||substrb(P_ID0000,9,9)) and rownum=1; exception when others then P_ERRMSG:=''; end ; P_ERRMSG:='该医保病人已经建过卡，不能再次建卡，请使用换卡或补卡功能！'||substrb(P_ERRMSG,1,120); return; end if; --根据病人姓名和身份证判断 --增加ic_yhxx00.zt0000='1'以及费别编号 条件来判断 by MZSF-20141024-003,MZSF-20150115-002 select count(*) into LS_COUNT from sf_brzhxx where SFDJ00<>'2'and brid00 in ( select b.brid00 from bm_brxxb0 b,ic_yhxx00 a where b.brid00=a.brid00 and b.brxm00=P_XMING0 and b.BRZJBH=P_SFZHAO and a.zt0000='1' and b.BRZJBH is not null and b.FBBH00=3); if LS_COUNT>0 then begin select substr('(病历号:'||trim(b.BRBLH0)||'卡号:'||a.ickh00||')',1,50) into P_ERRMSG from ic_yhxx00 a,bm_brxxb0 b where a.brid00=b.brid00 and a.zt0000='1' and a.brid00 in (select brid00 from bm_brxxb0 where brxm00=P_XMING0 and BRZJBH=P_SFZHAO) and rownum=1; exception when others then P_ERRMSG:=''; end ; P_ERRMSG:='该医保病人已经建过卡，不能再次建卡，请使用换卡或补卡功能！'||substrb(P_ERRMSG,1,120); return; end if; --根据医保卡号和姓名判断是否建档--end-- end if; /* --1:男；2：女 if P_XBIE00='2' then ls_BRXB00:='女'; else ls_BRXB00:='男'; end if; */ select SF_SST_XBIE00_YKT(P_XBIE00,'0') into ls_BRXB00 from dual; select SF_SST_GETCSRQ00_YKT(P_BRNL00,P_CSRQ00,P_SFZHAO) into LS_CSRQ00 from dual; select decode(P_SFJZFP,'Y','Y','1','Y','N') into LS_SFJZFP from dual; LS_YBZHYE:=to_number(nvl(P_GRZHYE,'0')); --判断医保中心名称是否存在 select Count(*) into LS_COUNT from IC_YBBRLB where YBMC00 = P_FZXMC0; if LS_COUNT<0 then select Count(*) into LS_COUNT from IC_YBBRLB a,BM_FZXDY0 b where a.FBBH00=b.FBBH00 and a.YBLB00=b.YBLB00 and b.FZXMC0 = P_FZXMC0; end if; if LS_COUNT>0 then --有存在医保中心名称--begin--- begin select YBLB00,YBZXLB,YBMC00,FBBH00 into ls_YBLBBH,ls_YBZXLB,ls_YBMC00,LS_FBBH00 from IC_YBBRLB where YBMC00 = P_FZXMC0; exception when no_data_found then begin select a.YBLB00,a.YBZXLB,a.YBMC00 into ls_YBLBBH,ls_YBZXLB,ls_YBMC00 from IC_YBBRLB a,BM_FZXDY0 b where a.FBBH00=b.FBBH00 and a.YBLB00=b.YBLB00 and b.FZXMC0 = P_FZXMC0 and rownum=1; exception when no_data_found then P_ERRMSG:='医保中心:'||P_FZXMC0||'在本院IC_YBBRLB表中未找到!'; return; end; end; --再得到医保病人类别 begin select YBBRLB into ls_YBBRLB from BM_BRZTQK where ZTQKMC = P_RYLB00 and YBZXLB = ls_YBZXLB and FBBH00=3 and rownum=1; --先从BM_BRZTQK表中取，是否是城镇居民医保 exception when others then begin select YBBRLB into ls_YBBRLB from BM_YBBRLB where BRLBMC = P_RYLB00 and YBZXLB = ls_YBZXLB and rownum=1; exception when others then ls_YBBRLB:='0'; end; end; else --有存在医保中心名称--end---如果不存在按自费处理 select YBLB00,YBZXLB,YBMC00,FBBH00 into LS_YBLBBH,LS_YBZXLB,LS_YBMC00,LS_FBBH00 from IC_YBBRLB where FBBH00 = '1' and YBMC00 in ('自费','非医保'); ls_YBBRLB:='0'; end if; if LS_FBBH00=1 then LS_YBKH00:='0'; LS_YBID00:='0'; else LS_YBKH00:=P_CARDNO; LS_YBID00:=P_ID0000; end if; --2：判断该卡号是否已经入库登记无先入库登记 select SQ_BM_BRXXB0_BRID00.nextval into ls_BRID00 from dual; select SQ_IC_YWLS00_YWID00.NEXTVAL into v_ywid00 from dual; select SQ_SF_BRZHXX_ZHYEID.nextval into ls_MZZHID from dual; ls_SYSMSG:='IC_YHXX00'; --ick入库-- select Count(*) into ls_count from IC_YHXX00 where ICKH00 = LS_CARDNO and ZT0000='0'; if ls_count=0 then select Count(*),min(ZT0000) ZT0000 into ls_count,LS_ZT0000 from IC_YHXX00 where ICKH00 = LS_CARDNO and rownum=1; if (ls_count>0) then if LS_ZT0000 not in ('4') then --0空白卡,1有效卡,2暂停使用卡,3作废卡,4无效卡(退卡) P_ERRMSG:='该IC卡号已经存在，不能入库!'; return; else --如果是作废卡或无效卡直接更卡号后在用 update IC_YHXX00 set BRID00=ls_BRID00,ZT0000='1' where ICKH00=LS_CARDNO and ZT0000 in ('3','4'); end if; else begin insert into IC_YHXX00(ICKNBH,ICKH00,ICKMM0,KMH000,YGBH00,BRID00,KLDH00,ICKLB0,ICKSX0,ZT0000,SFDM00,ICKJE0) values(LS_CARDNO,LS_CARDNO,null,P_CARDNO,ls_YGBH00,ls_BRID00,v_KLDH00,v_icklb0,v_icksx0,'1',0,0); exception when no_data_found then P_ERRMSG:='插入IC_YHXX00数据错误!'; return; end; end if; else update IC_YHXX00 set BRID00=ls_BRID00,ZT0000=1 where ICKH00=LS_CARDNO and ZT0000='0'; end if; ls_SYSMSG:='IC_YWLS00'; --业务流水 begin insert into IC_YWLS00(YWID00,ICKNBH,ICKH00,YGBH00,YWCZ00,CZRQ00,CZSJ00,FSJE00) values(v_ywid00,LS_CARDNO,LS_CARDNO,ls_YGBH00,p_ywcz00,ls_CZRQ00,ls_CZSJ00,0.00); exception when no_data_found then P_ERRMSG:='插入IC_YWLS00数据错误!'; return; end; ls_SYSMSG:='BM_BRXXB0'; --基本信息- --判断病例号 ls_BRBLH0:=P_ID0000; /* if LS_FBBH00=3 then if nvl(P_ID0000,'0')='0' then select SQ_BM_BRXXB0_BRBLH0.nextval into ls_BRBLH0 from dual; else ls_BRBLH0:=trim(P_ID0000); select count(1) into ls_count from BM_BRXXB0 where BRBLH0 = ls_BRBLH0; if ls_count>0 then --病历号已经存在 select SQ_BM_BRXXB0_BRBLH0.nextval into ls_BRBLH0 from dual; ls_BRBLH0:='*'||trim(ls_BRBLH0); end if; end if; else select SQ_BM_BRXXB0_BRBLH0.nextval into ls_BRBLH0 from dual; ls_BRBLH0:='*'||trim(ls_BRBLH0); end if; */ select SQ_BM_BRXXB0_BRBLH0.nextval into ls_BRBLH0 from dual; ls_BRBLH0:='*'||trim(ls_BRBLH0); begin Insert into BM_BRXXB0(BRID00,MRN000,BRBLH0,YBKH00,YBID00,YBLB00,BRXM00,BRXB00,BRXX00,BRCSRQ, BRHYZK,BRZY00,BRMZ00,BRGJ00,BRZJLX,BRZJBH,FBBH00,BRGFZH,BRDYFW,BRDH00, BRSZSF,BRSZS0,BRSZZ0,BRSZC0,BRJTDZ,BRYB00,TYDWID,TYDWMC,BRLXGX,BRLXXM, BRLXDZ,BRLXDH,BRZTQK,BRJKQK,BRLX00,CZY000,CZRQ00,CZSJ00,RYCS00,SFYX00, BZ0000,YBBRLB,SFYD00,SFYXJB,BJJBIE,TSSXMC,FWBJ00,XZQH00,SFJZFP) values(ls_BRID00,'0',ls_BRBLH0,LS_YBKH00,LS_YBID00,ls_YBLBBH,P_XMING0,ls_BRXB00,null,LS_CSRQ00, null,null,'汉族','中国','身份证',P_SFZHAO,LS_FBBH00,'0','未知',P_LXDH00, null,null,null,null,null,null,null,P_GZDWMC,null,null, null,null,P_RYLB00,null,'初诊',ls_YGBH00,ls_CZRQ00,ls_CZSJ00,0,'Y', null,ls_YBBRLB,'0','0','04','普通',null,null,LS_SFJZFP); exception when no_data_found then P_ERRMSG:='插入BM_BRXXB0数据错误!'; return; end; ls_SYSMSG:='SF_BRZHXX'; --账户信息 Insert into SF_BRZHXX(MZZHID,JFLBID,BRID00,SFDJ00,KHRQ00,KHSJ00, CZYBH0,CZYXM0,XJYE00,ZZYE00,ZYE000,YKZYE0,BJJE00,TYJE00,YXQFJE,YBZHYE,KL0000) values(ls_MZZHID,1,ls_BRID00,'0',ls_CZRQ00,ls_CZSJ00, ls_YGBH00,ls_YGXM00,0,0,0,0,0,0,0,LS_YBZHYE,null); end if; --1全部建档 2、单独HIS建档 3、单独一卡通建档 if nvl(P_BYRC00,'2') in ('1','3') then --1全部建档 2、单独HIS建档 3、单独一卡通建档 --获取该卡的病人的基本信息 begin select b.BRID00,a.ybkh00,a.ybid00 into ls_brid00,LS_YBKH00,LS_YBID00 from bm_brxxb0 a, IC_YHXX00 b where a.brid00=b.brid00 and ICKH00 =LS_CARDNO and ZT0000=1 and rownum=1; exception when no_data_found then P_ERRMSG:='卡号:'||LS_CARDNO||'在本院所对应的病人信息未找到!'; return; end; select Count(*) into ls_count from SF_YKTKFB where ICKH00 =LS_CARDNO and BRID00=LS_BRID00 and MKDM00='7'; if ls_count > 0 then P_ERRMSG:='该病人健康通已经建档，不能重复建档!'; return; end if; select SQ_SF_YKTKFB_ID0000.nextval into LS_ID0000 from dual; insert into SF_YKTKFB( ID0000, --ID CZRQ00, --操作日期 CZSJ00, --操作时间 CZY000, --操作员 ICKH00, --IC卡号 BRID00, --病人ID BRXM00, --病人姓名 YBKH00, --医保卡号 YBID00, --医保卡号 KFRQ00, --扣费日期 KFSJ00, --扣费时间 ZFFS00, --支付方式 ZFFSMC, --支付方式名称 JYJE00, --金额 JFBZ00, --交费备注 '0'交 1:退 KFZT00, --扣费状态 1:扣费成功 2:院内交费成功 3:冲销成功 ZDBH00, --终端编号 收费处0 MKDM00, --模块代码 MKDMMC, --模块代码名称 CZFS00, --0 现金 1银行 YKTLSH, --一卡通流水号 JYLX00 ) values( LS_ID0000, --ID ls_CZRQ00, --操作日期 ls_CZSJ00, --操作时间 ls_YGBH00, --操作员 LS_CARDNO, --IC卡号 LS_BRID00, --病人ID P_XMING0, --病人姓名 LS_YBKH00, --医保卡号 LS_YBID00, --医保卡号 ls_CZRQ00, --扣费日期 ls_CZSJ00, --扣费时间 26, --支付方式 '健康通支付', --支付方式名称 0, --金额 '0', --交费备注 '0'交 1:退 '0', --状态0:其它数据 2:业务数据 4:无效数据 5:自助机民生通充值 P_ZDBH00, --终端编号 收费处0 '7', --模块代码 '健康通账户建立', --模块代码名称 '0', --0 现金 1银行 LS_YKTLSH, --一卡通流水号 '00' ); end if; exception when no_data_found then P_ERRMSG:=substrb(P_ERRMSG||'病人建档失败！'||SQLERRM||'错误位置:'||ls_SYSMSG,1,255); when E_ERROR then P_ERRMSG:=substrb(P_ERRMSG||'病人建档失败！'||SQLERRM||'错误位置:'||ls_SYSMSG,1,255); when others then P_ERRMSG:=substrb(P_ERRMSG||'病人建档失败！'||SQLERRM||'错误位置:'||ls_SYSMSG,1,255); END;