CREATE or replace PROCEDURE SP_SF_BRXX00_REG000 ( as_ICKH00 IN CHAR, --IC卡号 as_MRN000 IN CHAR, --病案号 as_BRBLH0 IN CHAR, --门诊病历号 as_BRXM00 IN CHAR, --姓名 as_BRXB00 IN CHAR, --性别 as_BRXX00 IN CHAR, --血型 as_BRCSRQ IN CHAR, --出生日期 as_BRHYZK IN CHAR, --婚姻状况 as_BRZY00 IN CHAR, --职业 as_BRMZ00 IN CHAR, --民族 as_BRGJ00 IN CHAR, --国籍 as_BRZJLX IN CHAR, --证件类型 as_BRZJBH IN CHAR, --证件编号 as_FBMC00 IN CHAR, --费别名称 as_GFZH00 IN CHAR, --公费证号 as_BRDYFW IN CHAR, --地域范围;本市、外地） as_BRDH00 IN CHAR, --电话 as_BRSZSF IN VARCHAR2 default '', --病人所在省份 as_BRSZS0 IN VARCHAR2 default '', --病人所在市(县、区) as_BRSZZ0 IN VARCHAR2 default '', --病人所在镇（办事处） as_BRJTDZ IN VARCHAR2, --家庭地址/通信地址 as_BRYB00 IN CHAR, --邮编 ad_TYDWID IN NUMBER, --单位ID as_TYDWMC IN VARCHAR2, --单位名称 as_BRLXGX IN CHAR, --联系人关系 as_BRLXXM IN CHAR, --联系人姓名 as_BRLXDZ IN VARCHAR2, --联系人地址 as_BRLXDH IN CHAR, --联系人电话 as_BRZTQK IN CHAR, --职退情况 as_BRJKQK IN VARCHAR2, --健康状况 as_BRLX00 IN CHAR, --病人类型,如:'普通病人' as_JFLBMC IN CHAR, --账户类别 ad_XJYE00 IN NUMBER, --病人现金金额 ad_ZZYE00 IN NUMBER, --病人转账金额 ad_BJJE00 IN NUMBER, --报警限额 ad_TYJE00 IN NUMBER, --停用限额 ad_YXQFJE IN NUMBER, --允许病人的欠费的最大金额 as_KL0000 IN CHAR, --账户口令,就诊卡的口令 as_ZFFSMC IN CHAR, --支付方式 as_ZPH000 IN CHAR, --支票号 as_ZPKHYH IN CHAR, --出票的开户银行 as_ZPDWMC IN CHAR, --出票的单位名称 ad_CZY000 IN NUMBER, --操作员ID as_CZYXM0 IN CHAR, --操作员姓名 ad_CZYKSH IN NUMBER, --操作员所在科室编码 as_YJJXH0 IN CHAR, --预交金票据号 as_YBKH00 IN CHAR, --病人医保卡号 as_YBID00 IN CHAR, --病人医疗保险号(医保ID) as_YBLB00 IN CHAR, --病人所属医保中心名称 as_SFYB00 IN CHAR, --是否是医保病人信息登记,'Y':医保病人 as_SFSBLF IN CHAR, --是否收取病人的病历费,'Y':收病历费,'N':不收病历费 ad_YGBH00 IN NUMBER DEFAULT -1, --员工编号 ad_SFYD00 IN CHAR DEFAULT '0', --是否优待 as_SFYXJB IN CHAR DEFAULT '0', --是否优先 Pcommit IN varchar2 default 'Y', --是否提交 as_BJJBIE IN CHAR default '04', --保健级别 '00':无级别或非保健人员 '01' 一级 '02' 二级 '03' 三级 '04' 是还未从医保那边获取病人的保健级别时的值 ad_JZDH00 OUT NUMBER, --结算单号,广东省中医院新增的 ad_PJH000 OUT NUMBER, --打印票据的内部号,或称为交费表的单据号,或预交金的票据内部号 as_YHMSG0 OUT VARCHAR, --存储过程提示的错误信息 as_SYSMSG OUT VARCHAR, --系统提示的错误信息 as_TSSXMC IN VARCHAR2 default '普通' --病人特殊属性类别 ) as ls_FBBH00 BM_BRFBB0.FBBH00%TYPE; --费别编码 ls_JFLBID BM_BRJFLB.JFLBID%TYPE; --交费类别ID ls_BRID00 BM_BRXXB0.BRID00%TYPE; --病人ID ls_YBBRLB BM_BRXXB0.YBBRLB%TYPE; --医保病人类别 ls_ZJE000 SF_BRZHXX.ZYE000%TYPE; --总金额 ls_MZZHID SF_BRZHXX.MZZHID%TYPE; --门诊账户ID ls_ZFFSBH BM_BRZFFS.ZFFSBH%TYPE; --支付方式编码 ls_FSJE00 IC_YWLS00.FSJE00%TYPE; --IC卡金额 ls_SFDM00 BM_YYSFXM.SFXMID%TYPE; --收费代码ID ls_BLSFDM BM_YYSFXM.SFXMID%TYPE; --病历本费对应的收费代码ID ls_BLSFJE BM_YYSFXM.SFJE00%TYPE; --病历本费对应的收费金额 ls_DJH000 SF_BRFY00.DJH000%TYPE; --病人费用信息单据号 ls_HJJE00 SF_BRFY00.HJJE00%TYPE; --合计金额 ls_ZFJE00 SF_BRFY00.ZFJE00%TYPE; --自付金额 ls_PJH000 SF_PJSYQK.PJH000%TYPE; --票据使用情况表的票据号 ls_BRDBID SF_BRDB00.BRDBID%TYPE; --记账病人担保ID ls_COUNT0 NUMBER(5) ; --计数器 ls_YBLBBH IC_YBBRLB.YBLB00%TYPE; --病人所属医保中心名称对应的编号 ls_YBZXLB IC_YBBRLB.YBZXLB%TYPE; --病人医保中心类别 ls_KFSFKQ XT_XTCS00.value0%type; --卡费是否可能欠费 ls_YBSFYXQF XT_XTCS00.value0%type; --取医保个人帐户余额作为允许欠费金额(建阳模式) ls_yxqfje sf_brzhxx.yxqfje%type; ls_tsjk00 bm_ybbrlb.tsjk00%type; ls_tydwid bm_brxxb0.tydwid%type; --特约单位ID ls_gfxydw char(1); ls_CZRQ00 CHAR(8); ls_CZSJ00 CHAR(8); ls_BZ0000 BM_BRXXB0.BZ0000%type default ''; --备注:如果是急诊建卡的标上'急诊' ls_BMMC00 BM_BMBM00.BMMC00%type; --操作员所在科室名称 ls_YHKJE0 SF_PJSYQK.YHKJE0%TYPE; --银行卡金额 ls_ZZYE00 SF_PJSYQK.ZZJE00%TYPE; --转账金额 ls_DJZLKYJ char(1); --冻结诊疗卡押金 ls_TYJE00 number(10,2); ls_maxbrid bm_brxxb0.brid00%type; --最大病人ID ls_NEWICKH IC_YHXX00.ICKH00%TYPE; v_ydybykt char(1); --异地医保是否使用一卡通 Y:是 N否 默认为Y v_tlybykt char(1); --铁路医保是否使用一卡通 Y:是 N否 默认为Y E_ICKH_ERROR EXCEPTION; ls_WXTYJEBRFB varchar2(50); --无需冻结病人账户的病人费别 龙岩人民医院为 9,6,4 ls_brlb00 char(1); --0:无效的病人 1:一卡通用户 2: 医保换卡病人(不包括离休病人) 3：离休病人 4：非医保的病人 -- MODIFICATION HISTORY -- Person Date Comments -- xzw 2007.06.15 传递参数增加保健级别; -- xzw 2007.11.16 传递参数增加病人特殊属性类别 ; -- xzw 2007.11.26 预交金处理增加银行卡方式； -- xzw 2008.01.11 医保病人根据参数SF_YBBRSFCFJK来判断是否可以重复建卡 -- zhr 2008.06.13 增加病人所在村字段 -- yangy 2008.12.08 优待 -- yangy 2008.12.14 已经有病人信息的病人，建卡时不判断病人病历号 -- yangy 2009.01.07 考虑不同医保中心相同社会保障号的问题 -- yangy 2009.02.08 病人账户增加操作员科室 -- yangy 2009.04.16 某些费别的病人，无需冻结病人账户余额 -- yangy 2009.06.27 由于卡入库时没有取到病人的医保卡号，建卡时临时再纠正卡面号 -- zhr 2009.09.19 修正医保一卡通病人建卡，如果病人的医保类别发生变化，系统会按新病人处理 -- yangy 2010.02.24 当不同医保中心出现相同社会保障号，应该当做不同病人处理。 -- zhangyc 2010.04.07 ls_brlb00病人类别为2: 医保换卡病人(不包括离休病人) 3：离休病人 不修改卡面号 -- zhangyc 2010.05.11 医保病人BM_BRXXB0可能存在多条记录(YBID00一样)，有的记录只是住院，因此建卡时，可能补到住院记录上，需要修正。 BEGIN ls_CZRQ00 := to_char(sysdate,'YYYYMMDD'); ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); --判断医保中心对应的医保的编号 ls_YBBRLB:='0'; ls_YBLBBH:='0'; ls_tydwid:=ad_TYDWID; ls_YHKJE0 := 0; --银行卡金额 ls_ZZYE00 := ad_ZZYE00; ls_TYJE00:=ad_TYJE00; select nvl(trim(max(VALUE0)),'Y') into v_ydybykt from xt_xtcs00 WHERE NAME00='SF_YDYBSFSYYKT'; select nvl(trim(max(VALUE0)),'Y') into v_tlybykt from xt_xtcs00 WHERE NAME00='SF_TLYBSFSYYKT'; select nvl(trim(max(VALUE0)),'0') into ls_WXTYJEBRFB from xt_xtcs00 WHERE NAME00='SF_WXTYJEBRFB'; if ((v_ydybykt='N') AND (substr(as_ICKH00,5,1)='@')) OR ((v_tlybykt='N') AND (substr(as_ICKH00,1,4)='MTYB')) then raise E_ICKH_ERROR; end if; --急诊建卡的病人在BM_BRXXB0.BZ0000标上'急诊'字样 select count(*) into ls_COUNT0 from BM_BMBM00 where BMBH00=ad_CZYKSH and BMMC00 LIKE '%急诊%'; if ls_COUNT0 >0 then ls_BZ0000:='急诊'; end if ; --医保病人是否允许重复建卡 select count(*) into ls_COUNT0 from xt_xtcs00 where Name00='SF_YBBRSFCFJK' and Value0='Y'; -- /* if (as_YBID00<>'0') and (ls_COUNT0=0) then -- select count(*) into ls_COUNT0 from sf_brzhxx where SFDJ00<>'2'and brid00 in ( -- select brid00 from bm_brxxb0 where brxm00=as_BRXM00 and ybid00=as_YBID00); -- if ls_COUNT0>0 then -- begin -- select '(卡号:'||trim(ickh00)||')' into as_YHMSG0 from ic_yhxx00 -- where zt0000='1' and brid00 in (select brid00 from bm_brxxb0 -- where brxm00=as_BRXM00 and ybid00=as_YBID00) and rownum=1; -- exception -- when others then -- as_YHMSG0:=as_YHMSG0; -- end ; -- as_YHMSG0:='该医保病人已经建过卡，不能再次建卡，请使用换卡或补卡功能！'||as_YHMSG0; -- as_SYSMSG:=SQLERRM; -- rollback; -- RETURN; -- end if; -- end if; -- */ --诊疗卡是否允许欠费 select upper(substrb(nvl(max(trim(value0)),'N'),1,1)) into ls_KFSFKQ from xt_xtcs00 where name00='SF_KFSFKQ'; --是否建卡不收卡费，改为冻结诊疗卡押金 select upper(substrb(nvl(max(trim(value0)),'N'),1,1)) into ls_DJZLKYJ from xt_xtcs00 where name00='SF_DJZLKYJ'; --公费病人建卡时是否一定要输入单位 select upper(substrb(nvl(max(trim(value0)),'Y'),1,1)) into ls_gfxydw from xt_xtcs00 where name00='SF_GFBRJKBXSRDW'; --医保病人是否允许欠费(将医保病人的个人帐户金额作为病人的允许欠费金额，建阳使用的模式) select upper(substrb(nvl(max(trim(value0)),'N'),1,1)) into ls_YBSFYXQF from xt_xtcs00 where name00='SF_YBSFYXQF'; --IF as_SFSBLF = 'Y' THEN -- BEGIN -- select SFXMID,NVL(SFJE00,0) into ls_BLSFDM,ls_BLSFJE from BM_YYSFXM -- WHERE SFXMID=(SELECT to_number(VALUE0) from XT_XTCS00 where name00='SF_BLGBFSFXMBH') and -- MZFPID is not null and NVL(SFJE00,0) <> 0 and rownum = 1; -- if ls_BLSFJE = 0 then -- as_YHMSG0:='病历工本费所对应的收费金额未确定,请与系统管理人员联系,先确定病历工本费后,再进行本操作!'; -- as_SYSMSG:=SQLERRM||'select sfje00 from bm_yysfxm where xmmc00=病历工本费 or sfje00=0'; -- ROLLBACK; -- RETURN; -- end if; -- EXCEPTION -- WHEN OTHERS THEN -- as_YHMSG0:='系统未找到病历工本费所对应的收费项目,请先定义病历工本费后再进行本操作!'; -- as_SYSMSG:=SQLERRM||'Not exists select * from bm_yysfxm where xmmc00='||'病历工本费'; -- ROLLBACK; -- RETURN; -- END; -- END IF; BEGIN select FBBH00 into ls_FBBH00 from BM_BRFBB0 where FBMC00 = as_FBMC00; EXCEPTION WHEN OTHERS THEN as_YHMSG0:='系统未找到您所输入的费别名称！'; as_SYSMSG:=SQLERRM||'系统未找到您所输入的费别名称, select * from BM_BRFBB0 where FBMC00='||as_FBMC00; ROLLBACK; RETURN; END; if ls_gfxydw='Y' then --公费病人建卡时一定要输入单位 if ls_FBBH00 not in(1,3) and (trim(as_TYDWMC)='' or trim(as_TYDWMC) is null) then as_YHMSG0:='公费病人必须输入单位名称'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; end if; end if; if (ls_FBBH00=90) and (Lengthb(nvl(as_BRZJBH,' '))<>16) then as_YHMSG0:='新农合病人，必须输入证件号码(16位农合证号)！'; as_SYSMSG:=''; ROLLBACK; RETURN; end if; -- IF trim(NVL(trim(as_JFLBMC),' ')) <> '' THEN -- BEGIN -- select JFLBID into ls_JFLBID from BM_BRJFLB where JFLBMC = as_JFLBMC; -- EXCEPTION -- WHEN OTHERS THEN -- as_YHMSG0:='系统未找到您所输入的病人交费类别名称！'; -- as_SYSMSG:=SQLERRM||'系统未找到您所输入的病人交费类别名称, select * from BM_BRJFLB where JFLBMC='||as_JFLBMC; -- ROLLBACK; -- RETURN; -- END; -- ELSE -- ls_JFLBID:=0; -- END IF; BEGIN select JFLBID into ls_JFLBID from BM_BRJFLB where JFLBMC = as_JFLBMC; EXCEPTION WHEN OTHERS THEN as_YHMSG0:='系统未找到您所输入的病人交费类别名称！'; as_SYSMSG:=SQLERRM||'系统未找到您所输入的病人交费类别名称, select * from BM_BRJFLB where JFLBMC='||as_JFLBMC; ROLLBACK; RETURN; END; BEGIN select ZFFSBH into ls_ZFFSBH from BM_BRZFFS where ZFFSMC = as_ZFFSMC; EXCEPTION WHEN NO_DATA_FOUND THEN as_YHMSG0:='系统未找到您所输入的支付方式名称！'; as_SYSMSG:=SQLERRM||'系统未找到您所输入的支付方式名称, select * from BM_BRZFFS where ZFFSMC='||as_ZFFSMC; ROLLBACK; RETURN; END; if ls_ZFFSBH = 8 then ls_YHKJE0 := ad_ZZYE00; ls_ZZYE00 := 0; end if; IF as_SFYB00 = 'Y' THEN --是否是医保病人信息登记,'Y':医保病人 BEGIN select YBLB00,YBZXLB into ls_YBLBBH,ls_YBZXLB from IC_YBBRLB where YBMC00 = as_YBLB00; EXCEPTION WHEN NO_DATA_FOUND THEN as_YHMSG0:='医保病人所属医保中心名称有错，系统字典维护不完整！'; as_SYSMSG:=SQLERRM||'Not exists(select * from ic_ybbrlb where ybmc00='||as_YBLB00||')'; ROLLBACK; RETURN; END; --再得到医保病人类别 BEGIN select YBBRLB into ls_YBBRLB from BM_YBBRLB where BRLBMC = as_BRZTQK and YBZXLB = ls_YBZXLB; EXCEPTION WHEN OTHERS THEN ls_YBBRLB:='0'; END; ELSE if as_YBLB00 = '0' then ls_YBLBBH := '0'; ls_YBZXLB := '0'; else BEGIN --广东省中医院使用,这样，根据VW_BM_YBSFDY就可查出相关的收费标准 select YBLB00,YBZXLB into ls_YBLBBH,ls_YBZXLB from IC_YBBRLB where YBMC00 = as_YBLB00 and FBBH00=ls_FBBH00; EXCEPTION WHEN NO_DATA_FOUND THEN as_YHMSG0:='系统字典维护不完整,请察看详细信息后与系统管理员联系！'; as_SYSMSG:=SQLERRM||'select * from IC_YBBRLB where ybmc00='||as_YBLB00||' and BFFH00='||to_char(ls_FBBH00); ROLLBACK; RETURN; END; end if; select tsjk00 into ls_tsjk00 from bm_ybbrlb where fbbh00=ls_fbbh00 and ybzxlb=ls_ybzxlb and ybbrlb='0'; if ls_tsjk00='1' then as_YHMSG0:='不能对特殊病人进行建卡'; as_SYSMSG:=SQLERRM||'select * from bm_ybbrlb where fbbh00=' ||to_char(ls_fbbh00)||' and ybzxlb='||ls_ybzxlb||' and ybbrlb=0'; ROLLBACK; RETURN; end if; END IF; --若病人有购卡,还应该判断该卡是否有效,判断该卡对应的收费项目的发票项目是否存在 IF as_ICKH00 <> '0' THEN BEGIN select NVL(SFDM00,0),NVL(ICKJE0,0) into ls_SFDM00, ls_FSJE00 from IC_YHXX00 where ICKH00 =as_ICKH00 and ZT0000 = '0'; --该卡是空白有效卡 EXCEPTION WHEN NO_DATA_FOUND THEN as_YHMSG0:='您所使用的卡在系统中未登记或该卡已被使用，请换一张卡后再进行本操作！'; as_SYSMSG:=SQLERRM||'数据库中未找到该卡的信息, select * from ic_yhxx00 where zt0000=0 and ICKH00='||as_ICKH00; ROLLBACK; RETURN; END; if ls_DJZLKYJ='Y' then--冻结诊疗卡押金 ls_TYJE00:= ls_FSJE00;ls_SFDM00:=0; ls_FSJE00:=0; if (ls_WXTYJEBRFB<>'0') and (ls_TYJE00>0) then if instrb(','||ls_WXTYJEBRFB||',',','||to_char(ls_FBBH00)||',') >0 then ls_TYJE00 := 0; end if; end if; end if; IF ls_SFDM00 <> 0 THEN select count(A.FPXMID) into ls_COUNT0 from BM_FPXM00 A,BM_YYSFXM B where A.FPXMID = B.MZFPID and B.SFXMID=ls_SFDM00; IF ls_COUNT0 = 0 THEN as_YHMSG0:='操作失败，由于就诊卡对应的收费项目的发票项目不完整!'; as_SYSMSG:='BM_YYSFXM.SFXMID='||to_char(ls_SFDM00)||'的MZFPID的值NOT IN BM_FPXM00.FPXMID!'; ROLLBACK; RETURN; END IF; END IF; ----------病人类别为2: 医保换卡病人(不包括离休病人) 3：离休病人 不能重新更正卡面号-------- select SF_SF_YKTHKPD(as_YBLB00,as_BRZTQK,0) into ls_brlb00 from dual; if (as_SFYB00 = 'Y') and (as_YBKH00 is not null) and (ls_brlb00 not in ('2','3')) then --if (as_SFYB00 = 'Y') and (as_YBKH00 is not null) --医保病人重新更正卡面号。edit zhangyc 2010.04.07 update ic_yhxx00 set kmh000=as_YBKH00 where ickh00=as_ICKH00; end if; END IF; --本院职工建卡 IF ad_YGBH00>0 THEN select count(*) into ls_COUNT0 FROM BM_YGBM00 where YGBH00 = ad_YGBH00; IF ls_COUNT0=0 then as_YHMSG0:='警告:该员工不存在！'; as_SYSMSG:=SQLERRM; rollback; RETURN; END IF; select count(*) into ls_COUNT0 from IC_YHXX00 WHERE BRID00=(SELECT BRID00 FROM BM_YGBM00 where YGBH00 = ad_YGBH00) AND ZT0000='1'; IF ls_COUNT0>0 then as_YHMSG0:='警告:该员工已存在一张有效的就诊卡，不须再对该员工做售卡登记！'; as_SYSMSG:=SQLERRM; rollback; RETURN; END IF; END IF; select SQ_SF_PJSYQK_PJH000.nextval into ls_PJH000 from dual; select SQ_SF_BRJFB0_DJH000.nextval into ad_PJH000 from dual; ls_ZJE000:=ad_XJYE00+ad_ZZYE00; if ad_TYDWID is null and as_TYDWMC is not null then begin select dwid00 into ls_tydwid from bm_tydwb0 where dwmc00=as_TYDWMC; exception when others then ls_tydwid:=null; end; end if; --select count(1) into ls_COUNT0 from bm_brxxb0 where ybkh00=as_YBKH00 and ybkh00<>'0' and ybkh00 is not null; --2009.09.19 ZHR 修正医保一卡通病人建卡，如果病人的医保类别发生变化，系统会按新病人处理的BUG --2010.02.24 yangy 当不同医保中心出现相同社会保障号，应该当做不同病人处理。 select count(1) into ls_COUNT0 from bm_brxxb0 a,ic_ybbrlb b where a.fbbh00=b.fbbh00 and a.yblb00=b.yblb00 and a.YBID00=as_YBID00 and a.fbbh00=ls_FBBH00 and b.ybzxlb=ls_YBZXLB and nvl(trim(a.YBID00),'0')<>'0'; if ls_COUNT0=0 then --判断病人的病历号是否重复 select COUNT(*) into ls_COUNT0 from BM_BRXXB0 where BRBLH0 = as_BRBLH0; IF ls_COUNT0 > 0 THEN as_YHMSG0:='警告:您所输入的病人病历号与其它病人的病历号重复，登记失败,请重新输入！'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; END IF; select SQ_BM_BRXXB0_BRID00.nextval into ls_BRID00 from dual; --1、设置病人的基本信息,姓名、性别、病历号等信息 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) values(ls_BRID00,'0',as_BRBLH0,as_YBKH00,as_YBID00,ls_YBLBBH,as_BRXM00,as_BRXB00,as_BRXX00,as_BRCSRQ,as_BRHYZK,as_BRZY00,as_BRMZ00, as_BRGJ00,as_BRZJLX,as_BRZJBH,ls_FBBH00,as_GFZH00,as_BRDYFW,as_BRDH00,as_BRSZSF,as_BRSZS0,as_BRSZZ0,as_BRJTDZ,as_BRSZSF||as_BRSZS0||as_BRSZZ0||as_BRJTDZ,as_BRYB00,ls_tydwid,as_TYDWMC,as_BRLXGX, as_BRLXXM,as_BRLXDZ,as_BRLXDH,as_BRZTQK,as_BRJKQK,as_BRLX00,ad_CZY000,ls_CZRQ00,ls_CZSJ00,0,'Y',ls_BZ0000,ls_YBBRLB,ad_SFYD00,nvl(as_SFYXJB,'0'),nvl(as_BJJBIE,'04'),NVL(as_TSSXMC,'普通')); else --有门诊账户的优先，然后才是直接入院产生的BRID00 select brid00 into ls_BRID00 from (select a.brid00 from bm_brxxb0 a,ic_ybbrlb b,sf_brzhxx c where a.fbbh00=b.fbbh00 and a.yblb00=b.yblb00 and a.brid00=c.brid00(+) and a.YBID00=as_YBID00 and a.fbbh00=ls_FBBH00 and b.ybzxlb=ls_YBZXLB and nvl(a.YBID00,'0')<>'0' order by nvl(SFDJ00,'2'),nvl(c.THRQ00,'20991231') desc,a.brid00 desc) where rownum=1; update BM_BRXXB0 set YBKH00=as_YBKH00,YBBRLB=ls_YBBRLB,YBLB00=ls_YBLBBH,BRBLH0=as_BRBLH0, BRZTQK=as_BRZTQK,TYDWMC=as_TYDWMC,BJJBIE=nvl(as_BJJBIE,'04'), BRXM00=as_BRXM00,BRXB00=as_BRXB00,BRCSRQ=as_BRCSRQ,SFYD00=ad_SFYD00 where brid00=ls_BRID00; END IF; --对于广州的,暂时不做添加 --IF trim(as_GFZH00) <> '0' THEN --select SQ_SF_BRDB00_BRDBID.nextval into ls_BRDBID from dual; --Insert into BM_JZBRB0(JZBRID,JZZH00,KSRQ00,KSSJ00,JZSX00,SFJZ00,BZ0000,BRID00) -- values(ls_BRDBID,as_GFZH00,ls_CZRQ00,'00:00:01',0,'0','病人购卡登记',ls_BRID00); --select count(*) into ls_COUNT0 from BM_JZBRB0 where JZZH00 = as_GFZH00; --只能有一条在用 --if ls_COUNT0 > 1 then -- as_YHMSG0:='病人记账号重复，请重新输入该记账病人的记账号!'; -- as_SYSMSG:=SQLERRM; -- ROLLBACK; -- RETURN; --end if; --END IF; --Added on 2001-11-08,由于医保病人也有首页登记,持卡登记 --select count(*) into ls_COUNT0 from BM_BRXXB0 where YBID00 = as_YBID00 and as_YBID00 <> '0'; --if ls_COUNT0 > 1 then -- as_YHMSG0:='该医保病人已在本院数据库中存在!病人的病历号是: '||as_BRBLH0||' !'; -- as_SYSMSG:=SQLERRM||'该医保病人已在本院数据库中存在!病人的病历号是: '||as_BRBLH0||' !'; -- ROLLBACK; -- RETURN; --end if; --2、设置病人的账户信息 IF ls_JFLBID <> 0 THEN if (ls_YBSFYXQF='Y') and (ls_FBBH00=3) then ls_yxqfje:=ad_yxqfje+ls_FSJE00; --if instr(as_TYDWMC,'建阳市本级离休')>0 then -- ls_yxqfje:=ls_yxqfje+1000; --end if; else ls_yxqfje:=ad_yxqfje; end if; --if (ls_yxqfje<>0)and(ls_YBSFYXQF='Y') then --select SQ_SF_BRDB00_BRDBID.nextval into ls_BRDBID from dual; --Insert into SF_BRDB00(BRDBID,YGBH00,JFLBID,BRID00,DBJE00,SFYX00,DBRQ00,DBSJ00,CZY000,CZYXM0,DBJSRQ,DBJSSJ,SSLB00,DBYY00) -- values(ls_BRDBID,ad_CZY000,ls_JFLBID,ls_BRID00,ls_yxqfje,'Y',ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,to_char(sysdate+30,'YYYYMMDD'),'23:59:59','1','医保允许欠费'); --end if; --SELECT COUNT(1) INTO LS_COUNT0 FROM SF_BRZHXX WHERE BRID00=ls_BRID00 AND JFLBID='1'; --IF LS_COUNT0=0 THEN select count(1) into ls_COUNT0 from SF_BRZHXX where BRID00=LS_BRID00; if ls_COUNT0=0 then select SQ_SF_BRZHXX_ZHYEID.nextval into ls_MZZHID from dual; Insert into SF_BRZHXX(MZZHID,JFLBID,BRID00,SFDJ00,KHRQ00,KHSJ00, CZYBH0,CZYXM0,XJYE00,ZZYE00,ZYE000,YKZYE0,BJJE00,TYJE00,YXQFJE,KL0000,CZYKS0) values(ls_MZZHID,ls_JFLBID,ls_BRID00,'0',ls_CZRQ00,ls_CZSJ00, ad_CZY000,as_CZYXM0,0,0,0,0,ad_BJJE00,ls_TYJE00,ls_YXQFJE,as_KL0000,ad_CZYKSH); else update SF_BRZHXX set THRQ00=null,THSJ00=null,THCZY0=null,THCZYM=null where BRID00=LS_BRID00 and THRQ00 is not null; END IF; --END IF; --设置病人的交费信息及该预交金票据对应使用情况 IF ls_ZJE000 < 0 THEN as_YHMSG0:='预交金的金额非法,操作失败!'; as_SYSMSG:=SQLERRM||'预交金的金额不能小于0'; ROLLBACK; RETURN; ELSIF ls_ZJE000 > 0 THEN --添加病人的交费信息 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,WBHLID,WBHLJE) values(ad_PJH000,ls_BRID00,ls_JFLBID,ls_ZJE000,ls_ZFFSBH,as_ZFFSMC,as_ZPH000,as_ZPKHYH,as_ZPDWMC,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,'0',0,1,ls_ZJE000); --添加票据使用情况 Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,YHKJE0) -- select ls_PJH000,PLBH00,PLMC00,as_YJJXH0,ad_XJYE00,ad_ZZYE00,ad_CZY000,as_CZYXM0, select ls_PJH000,PLBH00,PLMC00,as_YJJXH0,ad_XJYE00,ls_ZZYE00,ad_CZY000,as_CZYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',as_BRXM00,as_BRXB00,ls_BRID00,ls_YHKJE0 from BM_PJLXB0 where PLBH00 = 7 ;--7是交预交金单据 if SQL%NOTFOUND then as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败!'; as_SYSMSG:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 2)'; ROLLBACK; RETURN; end if; END IF; END IF; -- end if; --4、若病人有购卡,还应该添加病人的购卡对应的信息 IF as_ICKH00 <> '0' THEN SELECT COUNT(1) INTO ls_COUNT0 FROM IC_YHXX00 WHERE BRID00=LS_BRID00 AND ICKH00<>AS_ICKH00 AND ZT0000='1'; IF ls_COUNT0>0 THEN as_YHMSG0:='该病人还有其他有效卡!'; as_SYSMSG:=SQLERRM||'该病人还有其他有效卡!'; ROLLBACK; RETURN; END IF; --5、Insert into IC卡业务信息表 Insert into IC_YWLS00(YWID00,ICKH00,YGBH00,YWCZ00,CZRQ00,CZSJ00,FSJE00,ICKNBH) select SQ_IC_YWLS00_YWID00.nextval,as_ICKH00,ad_CZY000,'1',ls_CZRQ00,ls_CZSJ00,ls_FSJE00,ICKNBH from IC_YHXX00 where ICKH00 = as_ICKH00 AND ZT0000='0'; Update IC_YHXX00 set BRID00 = ls_BRID00 , ICKMM0 = as_KL0000 , ZT0000 = '1' where ICKH00 = as_ICKH00 and ZT0000 = '0'; --6、若IC卡对应的有金额，还应该Insert into SF_BRFY00,SF_FYMX00,不对该项目做结算处理,因为若单独结算，还要与医保接口 IF ls_FSJE00 <> 0 THEN IF ls_KFSFKQ ='Y' THEN update sf_brzhxx set xjye00=xjye00+ls_FSJE00,zye000=zye000+ls_FSJE00 where brid00=ls_BRID00; END IF; select SQ_SF_BRJFB0_DJH000.nextval into ls_DJH000 from dual; Insert into SF_BRFY00(DJH000,MZH000,MZID00,JFLBID,BRID00,XM0000,BRFB00,HJJE00,ZFJE00,JZJE00,GFJE00,BRYE00, CZRQ00,CZSJ00,CZY000,CZYXM0,CZYKS0,JZDH00) values(ls_DJH000,0,0,ls_JFLBID,ls_BRID00,as_BRXM00,ls_FBBH00,ls_FSJE00,ls_FSJE00,0,0,0, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,ad_CZYKSH,0); Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00,JMJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,ZXKS00,YSKS00) select SQ_SF_FYMX00_MXID00.nextval,ls_DJH000,SFXMID,XMMC00,'',DW0000,ls_FSJE00,1,ls_FSJE00,ls_FSJE00,0,0,0, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_CZYKSH,ad_CZYKSH,ad_CZYKSH from BM_YYSFXM where SFXMID = ls_SFDM00; IF ls_KFSFKQ ='Y' THEN update sf_brzhxx set xjye00=xjye00-ls_FSJE00,zye000=zye000-ls_FSJE00 where brid00=ls_BRID00; END IF; END IF; END IF; --7.病历工本费的问题的处理 IF as_SFSBLF = 'Y' THEN select SQ_SF_BRJFB0_DJH000.nextval into ls_DJH000 from dual; Insert into SF_BRFY00(DJH000,MZH000,MZID00,JFLBID,BRID00,XM0000,BRFB00,HJJE00,ZFJE00,JZJE00,GFJE00,BRYE00, CZRQ00,CZSJ00,CZY000,CZYXM0,CZYKS0,JZDH00) values(ls_DJH000,0,0,ls_JFLBID,ls_BRID00,as_BRXM00,ls_FBBH00,ls_BLSFJE,ls_BLSFJE,0,0,0, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,ad_CZYKSH,0); Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00,JMJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,ZXKS00,YSKS00) select SQ_SF_FYMX00_MXID00.nextval,ls_DJH000,SFXMID,XMMC00,'',DW0000,ls_BLSFJE,1,ls_BLSFJE,ls_BLSFJE,0,0,0, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_CZYKSH,ad_CZYKSH,ad_CZYKSH from BM_YYSFXM where SFXMID = ls_BLSFDM; --Update SF_BRZHXX的预扣金额 --Update SF_BRZHXX set YKZYE0 = YKZYE0 - ls_BLSFJE where BRID00 = ls_BRID00 and JFLBID = ls_JFLBID; END IF; --8.若是现金病人,还要进行立刻的结算 IF ls_JFLBID = 0 THEN select SQ_SF_PJSYQK_PJH000.nextval into ls_PJH000 from dual; select SQ_SF_BRJFB0_DJH000.nextval into ls_DJH000 from dual; select SQ_SF_JZB000_JZDH00.nextval into ad_JZDH00 from dual; --将对应的费用信息做结算登记 Update SF_BRFY00 SET JZDH00 = ad_JZDH00 where BRID00 = ls_BRID00 and JFLBID = ls_JFLBID and JZDH00+0=0; select NVL(sum(HJJE00),0), NVL(sum(ZFJE00),0) into ls_HJJE00,ls_ZFJE00 from SF_BRFY00 where BRID00 = ls_BRID00 and JFLBID = ls_JFLBID and JZDH00 = ad_JZDH00; IF ls_HJJE00 <> 0 THEN --添加交费表信息 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,WBHLJE,JZDH00) values(ls_DJH000,ls_BRID00,ls_JFLBID,ls_ZFJE00,1,'现金',ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,'0',0,ls_ZFJE00,ad_JZDH00); --添加票据使用情况 Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,FZPJH0,BRID00) select ls_PJH000,PLBH00,PLMC00,trim(substr(SF_SF_GETPJXH(ad_CZY000,1),1,100)),ls_ZFJE00,0,ad_CZY000,as_CZYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',as_BRXM00,as_BRXB00,ls_PJH000,ls_BRID00 from BM_PJLXB0 where PLBH00 = 1 ;--1是门诊发票 if SQL%NOTFOUND then as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败!'; as_SYSMSG:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 1)'; ROLLBACK; RETURN; end if; --添加结算信息 Insert into SF_JZB000(JZDH00,BRID00,MZID00,BRZJE0,JZJE00,GFJE00,HJJE00,JMJE00,GRZHZF,TCJJZF,ZFJE00,PJH000,JZRQ00,JZSJ00,XJFKJE,ZZFKJE,JZY000) select ad_JZDH00,ls_BRID00,0,ls_ZFJE00,0,0,ls_ZFJE00,0,0,0,ls_ZFJE00,ls_PJH000,ls_CZRQ00,ls_CZSJ00,ls_ZFJE00,0,ad_CZY000 from SF_BRFY00 where JZDH00 = ad_JZDH00; --添加结算明细信息 Insert into SF_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00) select ad_JZDH00,B.FPXMID,B.XMMC00,sum(A.HJJE00) from SF_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,SF_BRFY00 D where A.XMBH00 = C.SFXMID and C.MZFPID = B.FPXMID and A.DJH000 = D.DJH000 and D.JZDH00 = ad_JZDH00 group by D.JZDH00,B.FPXMID,B.XMMC00; END IF; END IF; --9.本院职工处理 IF ad_YGBH00>0 THEN Update IC_YHXX00 set YGBH00 = ad_YGBH00 where ICKH00 = as_ICKH00 and BRID00 = ls_BRID00 and ZT0000 = '1'; IF sql%notfound then as_YHMSG0:='警告:更新本院职工的诊疗卡信息表时失败，请重新建卡！'; as_SYSMSG:=SQLERRM; rollback; RETURN; END IF; Update BM_YGBM00 set BRID00 = ls_BRID00 where YGBH00 = ad_YGBH00; IF sql%notfound then as_YHMSG0:='警告:本院职工建卡时更新职工表信息表时失败，请重新建卡！'; as_SYSMSG:=SQLERRM; rollback; RETURN; END IF; END IF; IF trim(Pcommit)='Y' THEN COMMIT; END IF; EXCEPTION when E_ICKH_ERROR then as_YHMSG0:=as_ICKH00||'该卡是铁路医保或异地医保!'; as_SYSMSG:=SQLERRM; rollback; WHEN NO_DATA_FOUND THEN as_YHMSG0:='数据没有找到，请核对病人费别或病人的交费类别是否正确!'; as_SYSMSG:=SQLERRM; ROLLBACK; WHEN OTHERS THEN as_YHMSG0:='数据库错误：出错原因未知！'; as_SYSMSG:='执行存储过程:SP_SF_BRXX00_REG000('||as_ICKH00||','||as_MRN000||','||as_BRBLH0||','||as_BRXM00||','|| as_BRXB00||','||as_BRXX00||','||as_BRCSRQ||','||as_BRHYZK||','||as_BRZY00||','||as_BRMZ00||','||as_BRGJ00||','|| as_BRZJLX||','||as_BRZJBH||','||as_FBMC00||','||as_BRDYFW||','||as_BRDH00||','||as_BRJTDZ||','||as_BRYB00||','|| to_char(ad_TYDWID)||','||as_TYDWMC||','||as_BRLXGX||','||as_BRLXXM||','||as_BRLXDZ||','||as_BRLXDH||','|| as_BRZTQK||','||as_BRJKQK||','||as_BRLX00||','||as_JFLBMC||','||to_char(ad_XJYE00)||','||to_char(ad_ZZYE00)||','|| to_char(ad_BJJE00)||','||to_char(ad_TYJE00)||','||to_char(ad_YXQFJE)||','||as_KL0000||','||as_ZFFSMC||','||as_ZPH000||','|| as_ZPKHYH||','||as_ZPDWMC||','||to_char(ad_CZY000)||','||as_CZYXM0||','||to_char(ad_CZYKSH)||','||as_YJJXH0||','|| as_YBKH00||','||as_YBID00||','||as_YBLB00||','||as_SFYB00||','||as_SFSBLF||',ad_PJH000,as_YHMSG0,as_SYSMSG'||SQLERRM; ROLLBACK; END; /