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_YBTSBZ in varchar2 default 'N' , --医保特殊病种 N 不是 Y是 as_XZQH00 in varchar2 default '' , --行政区号 as_GRSFEN in varchar2 default '' , --个人身份类型 as_GRSFMC in varchar2 default '' , --个人身份名称 as_SFBLWT in varchar2 default '' , --是否办理委托0未办理1已办理 as_CARD16 in varchar2 default '' , --CARD16 as_MSKF00 in varchar2 default 'N' , --是否免收卡费 Y:免收卡费 N:收取卡费 as_JG0000 in varchar2 default '' , --籍贯 as_SFSY00 in varchar2 default '0' , --是否生育 0:否 1:是 as_SFBJ00 in varchar2 default '0' , --是否保健 0否 1是 as_JKKLX0 in varchar2 default '-1' , --健康卡登记类型 0:正式卡建卡 1:临时建卡 2:院外卡建卡 as_JKKKF0 in varchar2 default 'N' , --健康卡登记是否收取卡费 Y:收取 N:不收取 as_HZDWMC in varchar2 default '' , --合作单位名称 as_BRDZBM in varchar2 default '' , --地址编码 as_SFGHXZ in varchar2 default '0' , --是否挂号限制 0:不限制 1:限制 as_SFJZFP in varchar2 default '0' , --是否精准扶贫 1:是 0:否 as_BRSZC0 in varchar2 default '' , --病人所在村(居委会) as_SFSMRZ in varchar2 default '0' , --病人是否实名认证 0:否 1:是 as_JDCZFL in varchar2 default 'N' , --建档充值业务是否分离 Y:是 N:否 默认值:N as_YBKSBM in varchar2 default '' , --医保卡识别码 as_YBJKID in varchar2 default '' , --医保接口ID=SF_YBJKRZ.YBJKID as_BRSZX0 in varchar2 default '' , --病人所在县 as_ZYFL00 in varchar2 default '' , --新冠职业分类 as_ZYZFL0 in varchar2 default '' --新冠职业子分类 ) -- 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 增加病人所在村字段 -- qks 2008.11.12 省老年医院允许卡面号为012326 - 012330五张卡停用金额上限-2万 MZSF-20081111-002 -- xzw 2009.03.23 为了区别出城镇居民医保，先从表BM_BRZTQK中查找YBBRLB... -- xzw 2009.05.06 公费病人卡费是否根据BM_YBSFDY.ZFBL00来处理（增加参数SF_GFBRKYJSFAZFBLLHQ） -- qks 2009.10.30 新增异地医保分中心表，根据BM_FZXDY0的信息找到大中心 -- zhr 2010.04.07 填写服务半径,内容和地域范围一致 -- zhr 2010.04.22 绿色通道自动担保3000,卡押金写入到TYJE00, 参数SF_YBSFYXQF改为SF_YBGRZHZWYXQFJE -- qks 2010.06.23 SF_PJSYQK增加字段DZQBJE -- zhangyc 2010.11.05 修改医院id取值(select SF_SF_GETYYID00() YYID00 from dual) -- zhangwz 2010.12.07 增加传入参数 as_YBTSBZ 来控制建卡的时候是否进行特殊病种病人标注. by MZSF-20101123-002. -- qks 2011.01.12 表ic_yhxx00增加字段sfjh00,ad_YGBH00=-998 指自助激活旧卡 -999指是自助建卡 -- qks 2011.03.05 旧卡姓名跟传入的姓名一致才允许激活 -- zhangyc 2011.04.28 增加传入参数 as_XZQH00 by MZSF-20110427-002 -- zhangyc 2011.06.08 修正bug体检预交金病人登记,设置了允许欠费金额，而往插入SF_BRZHXX.YXQFJE却为0 -- zhangyc 2011.06.27 修正医保病人建卡时卡号已存在提示信息过长报错。by MZSF-20110624-004 -- zhangyc 2011.11.04 增加参数grsfen:个人身份类型grsfmc:个人身份名称sfblwt:是否办理委托0未办理1已办理card16 by MZSF-20111101-006 --zhangyc 2011.12.19 增加参数SF_MZZYBRXXSFYZ=Y.如果医保病人已经入院登记,不在往bm_brxxb0插入记录 by MZSF-20111109-003 -- jinfl 2011.12.31 提示病人是否重复建卡时显示病人病历号+ickh00，然后进行截断处理 MZSF-20111230-001 --zhangyc 2012.11.13 增加入参数as_MSKF00是否免收卡费 Y:免收卡费 N:收取卡费 by MZSF-20121112-001 --jinfl 2013.06.04 增加参数限制邮编位数 MZSF-20130527-001 --jinfl 2013.09.05 重要来人短信通知 归档 SP_SF_BRXX00_REG000 --daihq 2014.07.09 增加入参籍贯as_JG0000 for MZSF-20140624-003 --daihq 2014.07.18 SF_YBBRSFCFJK=N时,增加限制身份证号已经存在的情况下不允许重复建卡 for MZSF-20140704-001 --zhangyc 2014.10.24 参数SF_YBBRSFCFJK=N时根据身份证以及姓名判断是否建档过，增加IC_YHXX00.ZT0000条件判断 by MZSF-20141024-003 --zhangyc 2014.11.10 增加入参as_SFBJ00 0否 1是 by MZSF-20140918-004 --linzy 2015.01.21 修正启用IC卡押金使用冻结金额方式后病人账户未设置冻结金额的问题 by MZSF-20141212-002 --zhangyc 2015.02.13 医保建档时根据姓名和身份证判断是否重复建档增加费别编号判断只判断医保病人 by MZSF-20150115-002 --zhangyc 2015.04.09 增加判断厦门健康卡院外卡登记是否收取卡费 by MZSF-20141223-001 --zhangyc 2015.07.31 增加合作单位 by MZSF-20150715-001 -- qks 2015.08.19 写入病历工本费,改为 费用明细表先写入,再写入费用表 by MZSF-20150819-004 --zhangyc 2015.08.26 增加写入地址码 by MZSF-20150724-001 --zhangyc 2015.10.15 增加as_SFGHXZ是否挂号限制 0:不限制 1:限制 by MZSF-20150731-003 --zhangyc 2017.06.05 增加微信支付、支付宝支付、医院支付、健康通支付 by MZSF-20170508-001 --zhangyc 2017.06.19 增加参数SF_SFQYYKTPT 控制 by MZSF-20170619-002 -- qks 2017.07.18 当参数SF_KFSFKQ=Y,需要修正SF_BRZHLS.JYQZYE,JYHZYE; by MZSF-20170710-001 --zhangyc 2018.01.10 修正错误出参太长报错 by MZSF-20180110-002 --zhangyc 2018.03.12 修正as_BRDYFW长度太长报错 by MZSF-20180312-002 --zhuyr 2018.05.07 增加是否精准扶贫字段 by MZSF-20180427-002 --zhangyc 2018.05.10 修正精准扶贫 1:是 0:否 by MZSF-20180426-006 --pwt 2018.11.01 新增新增参数as_BRSZC0病人所在村(居委会) by MZSF-20180512-001 --linshu 2019.02.15 把入参as_BRSZS0同时也保存在BRSZX0字段内 by MZSF-20190118-001 --zhuyr 2019.04.29 增加入参是否实名认证as_SFSMRZ，保存在SFSMRZ字段内 by MZSF-20190429-003 --zhangyc 2019.06.13 as_SFSMRZ 类型number-->varchar2 by MZSF-20190613-001 --zhangyc 2019.07.02 增加入参as_JDCZFL：建档充值业务是否分离 Y:是 N:否 默认值:N by MZSF-20190425-002 --qiulf 2019.10.22 增加入参as_YBKSBM:医保卡识别码 by MYSF-20191022-002 --zhangyc 2019.11.07 增加入参as_YBJKID医保接口ID=SF_YBJKRZ.YBJKID by MZSF-20191107-001 --qiulf 2019.12.09 增加入参as_BRSZX0病人所在县 by MZSF-20191003-001 --zhangyc 2020.01.03 资格信息查询增加写入表BM_YBBRXX by MZSF-20200106-001 --linbin 2020.05.19 参数SF_MZICKYJDJFS=Y, 自费病人充值金额不得小于IC卡费 by MZSF-20200508-002 --linbin 2020.06.19 增加入参as_ZYFL00新冠职业分类、as_ZYZFL0新冠职业子分类 by MZSF-20200618-003 --linbin 2020.07.20 增加参数SF_MZJDSFTBDZDH判断家庭电话和家庭地址是否同步至电话号码和联系地址 by MZSF-20200708-002 ---------- -------------- --------------------------------------------------------------------------------------------- as LS_FBBH00 BM_BRFBB0.FBBH00%type; --费别编码 LS_JFLBID BM_BRJFLB.JFLBID%type; --交费类别ID LS_BRID00 BM_BRXXB0.BRID00%type; --病人ID TEMP_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_YBLBBH IC_YBBRLB.YBLB00%type; --病人所属医保中心名称对应的编号 LS_YBZXLB IC_YBBRLB.YBZXLB%type; --病人医保中心类别 LS_KFSFKQ XT_XTCS00.value0%type; --卡费是否可能欠费 LS_YBSFYXQF XT_XTCS00.value0%type; --取医保个人帐户余额作为允许欠费金额(建阳模式) LS_BRXXYZ XT_XTCS00.value0%type; --门诊信息与住院信息是否保持一致 LS_YXQFJE SF_BRZHXX.yxqfje%type; LS_TSJK00 BM_YBBRLB.tsjk00%type; LS_TYDWID BM_BRXXB0.tydwid%type; --特约单位ID 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_YYID00 XT_YYXX00.YYID00%type; LS_KMH000 IC_YHXX00.KMH000%type; LS_TYJE00 SF_BRZHXX.TYJE00%type; LV_ZFJE00 SF_FYMX00.ZFJE00%type; LV_JZJE00 SF_FYMX00.JZJE00%type; LS_COUNT0 number(5) ; --计数器 LS_GFXYDW char(1); LS_CZRQ00 char(8); LS_CZSJ00 char(8); LS_GFKJE0 char(1); --参数SF_GFBRKYJSFAZFBLLHQ的值(特约单位病人)) LS_LSTDFB varchar2(20) ; --绿色通道病人费别 LS_DZQBJE SF_PJSYQK.DZQBJE%TYPE; --电子钱包金额 LS_YBXZWS number(5); VZXCGBZ number; --门诊ICK押金是否以帐户冻结方式出现，默认为N LS_SF_MZICKYJDJFS XT_XTCS00.VALUE0%type; LS_TYJE00_TEMP SF_BRZHXX.TYJE00%type; V_JKKJE0 BM_YYSFXM.SFJE00%type; IC_SFDM varchar2(100);--IC卡售卡收费代码 LS_BRJTDZ BM_BRXXB0.BRJTDZ%type; LS_BRDZBM BM_BRXXB0.BRDZBM%type; SF_SFQYYKTPT varchar2(100);--是否启用一卡通平台接口 Y:启用 N:不启用 默认:N不启用 LS_ZYE000 SF_BRZHXX.ZYE000%type; LS_JDCZFL varchar2(10);--建档充值业务是否分离 Y:是 N:否 默认值:N LS_TBDZDH XT_XTCS00.VALUE0%type; --门诊建档是否同步地址和电话 Y:是 N:否 默认:N LS_BRLXDZ BM_BRXXB0.brlxdz%type; LS_BRLXDH BM_BRXXB0.brlxdh%type; lzg_ickje0 number(5,2); ls_zzjjk0 XT_XTCS00.value0%type; 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_DZQBJE := 0; --电子钱包金额 LS_ZZYE00 := ad_ZZYE00; TEMP_BRID00:=0; LS_JDCZFL:=substrb(nvl(trim(as_JDCZFL),'N'),1,10);--建档充值业务是否分离 Y:是 N:否 默认值:N AD_PJH000:=0; begin select value0 into ls_zzjjk0 from XT_XTCS00 where name00='SST_ZZJJKSFYCZ' and exists ( select 1 from SST_YGBHDY where ygbh00 = ad_CZY000 ) ; exception when others then ls_zzjjk0:='N'; end; begin select SF_SF_TYZD00('一卡通平台参数','SF_SFQYYKTPT') into SF_SFQYYKTPT from dual; exception when others then SF_SFQYYKTPT:='N'; end; --门诊ICK押金是否以帐户冻结方式出现，默认为N select nvl(max(trim(VALUE0)),'N') into ls_SF_MZICKYJDJFS from XT_XTCS00 where NAME00='SF_MZICKYJDJFS'; if ad_YGBH00=-998 then update ic_yhxx00 set sfjh00='1',ICKMM0 = decode(as_KL0000,null,ICKMM0,as_KL0000) where ickh00=as_ICKH00 and zt0000='1' and nvl(sfjh00,'0')='0'; if sql%notfound then as_YHMSG0:='找不到未激活的有效卡,操作失败！'; as_SYSMSG:=sqlerrm||'找不到记录: select * from ic_yhxx00 where ickh00='||as_ICKH00||' and zt0000=''1'' and nvl(sfjh00,''0'')=''0'')'; rollback; return; end if; select nvl(max(brid00),0) into ls_BRID00 from bm_brxxb0 where brid00 in (select brid00 from ic_yhxx00 where ickh00=as_ICKH00 and zt0000='1') and brxm00=Trim(as_BRXM00); if ls_BRID00=0 then as_YHMSG0:='诊疗卡姓名跟身份证上的姓名不符合，请到柜台修改姓名后再来激活！'; as_SYSMSG:=sqlerrm; rollback; return; end if; update bm_brxxb0 set BRXM00=Trim(as_BRXM00),BRCSRQ=Trim(as_BRCSRQ),brzjbh=trim(as_BRZJBH),brdh00=trim(as_BRDH00),BRJTDZ=decode(as_BRJTDZ,NULL,BRJTDZ,as_BRJTDZ) where brid00=ls_BRID00; if trim(Pcommit)='Y' then COMMIT; end if; return; --最后退出 end if; if ad_YGBH00=-999 then select count(1) into LS_COUNT0 from IC_YHXX00 where ickh00=as_ICKH00; if LS_COUNT0 = 0 then as_YHMSG0:='找不到卡记录,操作失败！'; as_SYSMSG:=sqlerrm||'找不到记录: select * from ic_yhxx00 where ickh00='||as_ICKH00; rollback; return; end if; end if; begin select to_number(value0) into ls_YBXZWS from XT_XTCS00 where name00='SF_BRYBXZWS' and value0<>'N'; exception when others then ls_YBXZWS:=0; end; if (ls_YBXZWS>0) and (ls_YBXZWS0 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 substr('(病历号:'||trim(b.BRBLH0)||'卡号:'||a.ickh00||')',1,50) into as_YHMSG0 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=as_BRXM00 and ybid00=as_YBID00) and rownum=1; exception when others then as_YHMSG0:=as_YHMSG0; end ; as_YHMSG0:='该医保病人已经建过卡，不能再次建卡，请使用换卡或补卡功能！'||substrb(as_YHMSG0,1,120); as_SYSMSG:=sqlerrm; rollback; return; end if; --增加ic_yhxx00.zt0000='1'以及费别编号 条件来判断 by MZSF-20141024-003,MZSF-20150115-002 select count(*) into LS_COUNT0 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=as_BRXM00 and b.BRZJBH=as_BRZJBH and a.zt0000='1' and b.BRZJBH is not null and b.FBBH00=3); if LS_COUNT0>0 then begin select substr('(病历号:'||trim(b.BRBLH0)||'卡号:'||a.ickh00||')',1,50) into as_YHMSG0 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=as_BRXM00 and BRZJBH=as_BRZJBH and FBBH00=3 ) and rownum=1; exception when others then as_YHMSG0:=as_YHMSG0; end ; as_YHMSG0:='该医保病人已经建过卡，不能再次建卡，请使用换卡或补卡功能！'||substrb(as_YHMSG0,1,120); as_SYSMSG:=sqlerrm; rollback; return; end if; end if; begin --select YYID00 into ls_YYID00 from XT_YYXX00 WHERE ROWNUM=1; select SF_SF_GETYYID00() YYID00 into ls_YYID00 from dual; exception when others then ls_YYID00:='0'; end; --诊疗卡是否允许欠费 select nvl(trim(max(value0)),'N') into ls_KFSFKQ from XT_XTCS00 where name00='SF_KFSFKQ'; --公费病人建卡时是否一定要输入单位 select upper(substrb(nvl(trim(max(value0)),'Y'),1,1)) into ls_gfxydw from XT_XTCS00 where name00='SF_GFBRJKBXSRDW'; --医保病人是否允许欠费(将医保病人的个人帐户金额作为病人的允许欠费金额，建阳使用的模式) select nvl(trim(max(value0)),'N') into ls_YBSFYXQF from XT_XTCS00 where name00='SF_YBGRZHZWYXQFJE'; --判断并取得参数SF_GFBRKYJSFAZFBLLHQ的值 --公费病人的卡押金是不是按BM_YBSFDY的自费比例来收取。默认不是，就是卡押金自费 select nvl(trim(max(value0)),'N') into ls_GFKJE0 from XT_XTCS00 where name00='SF_GFBRKYJSFAZFBLLHQ'; --绿色通道病人费别; select nvl(trim(max(value0)),'0') into ls_LSTDFB from XT_XTCS00 where name00='SF_LSTDBRFB'; 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 in (8,24,25) then --银行卡,微信支付,支付宝支付 ls_YHKJE0 := ad_ZZYE00; ls_ZZYE00 := 0; end if; if ls_ZFFSBH = 18 then ls_DZQBJE := 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 begin select a.YBLB00,a.YBZXLB into ls_YBLBBH,ls_YBZXLB from IC_YBBRLB a,BM_FZXDY0 b where a.FBBH00=b.FBBH00 and a.YBLB00=b.YBLB00 and b.FZXMC0 = 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; end; --再得到医保病人类别 begin select YBBRLB into ls_YBBRLB from BM_BRZTQK where ZTQKMC = as_BRZTQK and YBZXLB = ls_YBZXLB and FBBH00=ls_FBBH00; --先从BM_BRZTQK表中取，是否是城镇居民医保 exception when others then 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; 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),KMH000 into ls_SFDM00, ls_FSJE00, ls_KMH000 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_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; end if; --by MZSF-20121112-001--- if as_MSKF00='Y' then ls_FSJE00:=0; end if; ---2015.04.09 BY MZSF-20141223-001 --厦门健康卡登记类型 0:正式卡建卡 1:临时建卡 2:院外卡建卡 if (as_JKKLX0='2') and (as_JKKKF0='Y') and (ls_FSJE00=0) then --健康卡院外卡登记如果没有收取卡费前台打钩后重新收取 begin select trim(VALUE0) into IC_SFDM from XT_XTCS00 where name00='IC_SFDM'; exception when no_data_found then IC_SFDM:='0'; end; begin select to_number(VALUE0) into v_jkkje0 from XT_XTCS00 where NAME00='SF_IC_XMJKKSFJE'; exception when others then v_jkkje0 := 0; end; if (IC_SFDM<>'0') and (v_jkkje0>0) then ls_SFDM00:=substrb(IC_SFDM,1,10); ls_FSJE00:=v_jkkje0; end if; end if; --判断病人的病历号是否重复 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; --本院职工建卡 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_BM_BRXXB0_BRID00.nextval into ls_BRID00 from dual; select SQ_SF_BRZHXX_ZHYEID.nextval into ls_MZZHID from dual; select SQ_SF_PJSYQK_PJH000.nextval into ls_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; ---医保病人BM_BRXXB0.BRID00值门诊信息与住院信息是否保持一致 Y:是 N:否 默认值:N--- begin select trim(value0) into ls_BRXXYZ from xt_xtcs00 where name00 = 'SF_MZZYBRXXSFYZ'; exception when others then ls_BRXXYZ:='N'; end; --by MZSF-20111109-003--如果医保病人在住院已经登记过在门诊建卡不往BM_BRXXB0插入信息--- if (ls_FBBH00=3) and (ls_BRXXYZ='Y') then begin Select BRID00 into Temp_BRID00 from bm_brxxb0 where YBID00=as_YBID00 and BRXM00=as_BRXM00 ; exception when others then Temp_BRID00:=0; end; if Temp_BRID00<>0 then ls_BRID00:=Temp_BRID00; end if; else Temp_BRID00:=0; end if; if as_BRJTDZ is null then LS_BRJTDZ:=as_BRSZSF||as_BRSZS0||as_BRSZX0||as_BRSZZ0||as_BRSZC0; else LS_BRJTDZ := as_BRJTDZ; end if; --地址编码 if (as_BRDZBM is null) or (as_BRDZBM in ('0')) then LS_BRDZBM:=SF_SF_BRDZBM(as_BRSZSF,as_BRSZS0,as_BRSZZ0,as_BRSZC0,LS_BRJTDZ,'0'); else LS_BRDZBM:=as_BRDZBM; end if; --门诊建档是否同步地址和电话 Y:是 N:否 默认:N begin select trim(value0) into ls_TBDZDH from xt_xtcs00 where name00 = 'SF_MZJDSFTBDZDH'; exception when others then LS_TBDZDH:='N'; end; if ls_TBDZDH ='Y' THEN ls_BRLXDZ := ls_BRJTDZ; ls_BRLXDH := as_BRDH00; else ls_BRLXDZ := as_BRLXDZ; ls_BRLXDH := as_BRLXDH; end if; --1、设置病人的基本信息,姓名、性别、病历号等信息 if Temp_BRID00=0 then 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,GRSFEN,GRSFMC,SFBLWT,CARD16, JG0000,SFSY00,SFBJ00,HZDWMC,BRDZBM,SFGHXZ,SFJZFP,BRSZX0,SFSMRZ) 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,substrb(as_BRDYFW,1,30),as_BRDH00,as_BRSZSF,as_BRSZS0,as_BRSZZ0,as_BRSZC0,LS_BRJTDZ,as_BRYB00, ls_tydwid,as_TYDWMC,as_BRLXGX,as_BRLXXM,ls_BRLXDZ,ls_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,'普通'),substrb(as_BRDYFW,1,20),as_XZQH00,as_GRSFEN,as_GRSFMC,as_SFBLWT,as_CARD16, as_JG0000,as_SFSY00,as_SFBJ00,as_HZDWMC,LS_BRDZBM,nvl(as_SFGHXZ,'0'),nvl(as_SFJZFP,'0'),as_BRSZX0,as_SFSMRZ); if as_SFSMRZ = '1' then update BM_BRXXB0 set ZJXXLY = 3 where BRID00 = ls_BRID00; --认证过的病人证件信息来源是身份证5 end if; else update BM_BRXXB0 set BRBLH0=as_BRBLH0,BRHYZK=as_BRHYZK,BRZY00=as_BRZY00,BRMZ00=as_BRMZ00, BRGJ00=as_BRGJ00,BRZJLX=as_BRZJLX,BRZJBH=as_BRZJBH,BRDH00=as_BRDH00, BRSZSF=as_BRSZSF,BRSZS0=as_BRSZS0,BRSZZ0=as_BRSZZ0,BRSZC0=as_BRSZC0, BRJTDZ=LS_BRJTDZ,BRYB00=as_BRYB00,BRSZX0=as_BRSZX0, SFYD00=ad_SFYD00,SFYXJB=nvl(as_SFYXJB,'0'),BJJBIE=nvl(as_BJJBIE,'04'), TSSXMC=NVL(as_TSSXMC,'普通'),FWBJ00=substrb(as_BRDYFW,1,20),XZQH00=as_XZQH00, GRSFEN=as_GRSFEN,GRSFMC=as_GRSFMC,SFBLWT=as_SFBLWT,CARD16=as_CARD16, JG0000=as_JG0000,SFSY00=as_SFSY00,SFBJ00=as_SFBJ00,HZDWMC=as_HZDWMC, BRDZBM=LS_BRDZBM,SFGHXZ=nvl(as_SFGHXZ,'0'),SFJZFP=nvl(as_SFJZFP,'0'), YBKSBM=as_YBKSBM where brid00=ls_BRID00; end if; --新冠职业分类 by MZSF-20200618-003 select count(*) into LS_COUNT0 from BM_QTFJSX where DATAID=ls_BRID00 and ZDM000='ZYFL00' ; if LS_COUNT0>0 then update BM_QTFJSX set VALUE0=as_ZYFL00,CZY000=ad_CZY000,CZRQ00=LS_CZRQ00,CZSJ00=LS_CZSJ00 where DATAID=ls_BRID00 and ZDM000='ZYFL00' ; else insert into BM_QTFJSX (DATAID,TABNAM,ZDM000,ZDSM00,VALUE0,CZY000,CZRQ00,CZSJ00) VALUES (ls_BRID00,'BM_BRXXB0','ZYFL00','职业分类',as_ZYFL00,ad_CZY000,LS_CZRQ00,LS_CZSJ00); end if ; --新冠职业子分类 by MZSF-20200618-003 select count(*) into LS_COUNT0 from BM_QTFJSX where DATAID=ls_BRID00 and ZDM000='ZYZFL0' ; if LS_COUNT0>0 then update BM_QTFJSX set VALUE0=as_ZYZFL0,CZY000=ad_CZY000,CZRQ00=LS_CZRQ00,CZSJ00=LS_CZSJ00 where DATAID=ls_BRID00 and ZDM000='ZYZFL0' ; else insert into BM_QTFJSX (DATAID,TABNAM,ZDM000,ZDSM00,VALUE0,CZY000,CZRQ00,CZSJ00) VALUES (ls_BRID00,'BM_BRXXB0','ZYZFL0','职业子分类',as_ZYZFL0,ad_CZY000,LS_CZRQ00,LS_CZSJ00); end if ; if ls_FBBH00=3 and as_YBTSBZ='Y' then -- by MZSF-20101123-002 insert into XT_YBTSBZ(BRID00,GHID00,TSBZBH,MZZYBZ,CZRQ00,CZSJ00) values (ls_BRID00,0,'0','0',to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS')); 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、设置病人的账户信息 ls_TYJE00_TEMP:=ad_TYJE00; if ls_JFLBID <> 0 then --自费病人 启用冻结卡押金模式 by MZSF-20141212-002 if ls_FBBH00=1 and ls_SF_MZICKYJDJFS='Y' then if ls_ZJE000 < LS_FSJE00 and ls_zzjjk0 = 'N' then --预交金不得小于IC卡费 by MZSF-20200508-002 as_YHMSG0:='预交金的金额非法,操作失败!'; as_SYSMSG:=sqlerrm||'预交金的金额不能小于'||LS_FSJE00; rollback; return; else --冻结金额为IC卡费 ls_TYJE00_TEMP:=ls_FSJE00; --冻结卡押金则以ICKJE0为准 SFDM00设置为0 update IC_YHXX00 set SFDM00=0 where ICKH00 = as_ICKH00; end if; end if; ls_yxqfje:=ad_yxqfje; ls_TYJE00:=ls_TYJE00_TEMP; if (ls_YBSFYXQF='Y') and (ls_FBBH00=3) then--如果医保账户余额可做欠费金额，一般是采用换卡，卡本身就不需要押金了。 ls_TYJE00:=-ls_FSJE00+nvl(ls_TYJE00_TEMP,0); end if; if (ls_YYID00='220035') and (ls_KMH000>='012326') and (ls_KMH000<='012330') then --MZSF-20081111-002 ls_TYJE00:=-(20000+ls_FSJE00)+nvl(ls_TYJE00_TEMP,0); --停用金额2万上限 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; --modify by MZSF-20110608-003 修正往Insert into SF_BRZHXX(YXQFJE,YBZHYE)values(0,ls_YXQFJE)-->(ls_YXQFJE,0) --修正bug体检预交金病人登记,设置了允许欠费金额，而往插入SF_BRZHXX.YXQFJE却为0 insert into SF_BRZHXX(MZZHID,JFLBID,BRID00,SFDJ00,KHRQ00,KHSJ00, CZYBH0,CZYXM0,XJYE00,ZZYE00,ZYE000,YKZYE0,BJJE00,TYJE00,YXQFJE,YBZHYE,KL0000) 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,0,as_KL0000); --ad_TYJE00 --设置病人的交费信息及该预交金票据对应使用情况 select ickje0 into lzg_ickje0 from ic_yhxx00 where ickh00 = as_ICKH00; if ls_SF_MZICKYJDJFS = 'Y' and ls_ZJE000 < lzg_ickje0 and ls_FBBH00 = 1 and ls_zzjjk0 = 'N' then as_YHMSG0:='预交金的金额非法,操作失败!'; as_SYSMSG:=sqlerrm||'预交金的金额不能小于2.8'; rollback; return; end if ; if ls_ZJE000 < 0 and ls_zzjjk0 = 'N' then as_YHMSG0:='预交金的金额非法,操作失败！'; as_SYSMSG:=sqlerrm||'预交金的金额不能小于0'; rollback; return; elsif ls_ZJE000 > 0 then --添加病人的交费信息 if LS_JDCZFL not in ('Y') then --建档充值业务是否分离 Y:是 N:否 默认值:N select SQ_SF_BRJFB0_DJH000.nextval into ad_PJH000 from dual; insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,WBHLID,WBHLJE,JFYY00) 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,'冻结金额:'||to_char(ls_TYJE00)); --添加票据使用情况 insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,YHKJE0,DZQBJE) -- 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,ls_DZQBJE 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; if instrb(','||ls_LSTDFB||',',','||to_char(ls_FBBH00)||',')>0 then insert into SF_BRDB00(BRDBID,YGBH00,JFLBID,BRID00,DBJE00,SFYX00,DBRQ00,DBSJ00,CZY000,CZYXM0,DBJSRQ,DBJSSJ,SSLB00,DBYY00) values(SQ_SF_BRDB00_BRDBID.nextval,0,1,ls_BRID00,3000,'Y',to_char(sysdate,'YYYYMMDD'), to_char(sysdate,'HH24:MI:SS'),0,'系统',to_char(sysdate+2,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),'2','绿色通道病人自动欠费'); end if; --4、若病人有购卡,还应该添加病人的购卡对应的信息 if as_ICKH00 <> '0' then --4、update IC卡信息表的BRID00 update IC_YHXX00 set BRID00 = ls_BRID00 , ICKMM0 = as_KL0000 , ZT0000 = '1' , SFJH00 = decode(ad_YGBH00,-999,'1',NULL) where ICKH00 = as_ICKH00 and ZT0000 = '0'; if as_MSKF00='Y' then update IC_YHXX00 set ICKJE0=0 where BRID00=ls_BRID00 and ICKH00 = as_ICKH00; 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; --6、若IC卡对应的有金额，还应该Insert into SF_BRFY00,SF_FYMX00,不对该项目做结算处理,因为若单独结算，还要与医保接口 --卡押金以冻结方式SF_MZICKYJDJFS='Y' 则不写SF_BRFY00 by MZSF-20141212-002 if ls_FSJE00 <> 0 and ls_SF_MZICKYJDJFS<>'Y' then if ls_KFSFKQ ='Y' or LS_JDCZFL in ('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; if (ls_GFKJE0='Y') and (ls_FBBH00 not in(1,3)) then --公费病人是否ICK金额按BM_YBSFDY中的ZFBL00来结算 select NVL(ZFBL00*ls_FSJE00,0),NVL((1-ZFBL00)*ls_FSJE00,0) into lv_ZFJE00,lv_JZJE00 from bm_ybsfdy where FBBH00=ls_FBBH00 AND SFYP00='N' and YBZXLB=ls_YBZXLB and sfxmid=ls_SFDM00; 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,lv_ZFJE00,lv_JZJE00,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,lv_ZFJE00,0,lv_JZJE00,0, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_CZYKSH,ad_CZYKSH,ad_CZYKSH from BM_YYSFXM where SFXMID = ls_SFDM00; else 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; end if; if ls_KFSFKQ ='Y' or LS_JDCZFL in ('Y') then update SF_BRZHXX set XJYE00=XJYE00-ls_FSJE00,ZYE000=ZYE000-ls_FSJE00 where BRID00=ls_BRID00; Update SF_BRZHLS set JYQZYE=JYQZYE-ls_FSJE00,JYHZYE=JYHZYE-ls_FSJE00 where BRID00=ls_BRID00 and JYLX00='2' and JYDJH0=ls_DJH000; end if; end if; select count(1) into LS_COUNT0 from BM_TYZD00 where ZDMC00 = '来人提醒医保类别' and MC0000=ls_YBLBBH and BZ0000=to_char(ls_FBBH00); --重要来人短信通知 if LS_COUNT0>0 then SP_YJ_DXTZ00(ls_BRID00,'来人提醒','建卡',ad_CZY000,ad_CZYKSH,'N',Vzxcgbz,as_YHMSG0,as_SYSMSG); end if; end if; --7.病历工本费的问题的处理 if as_SFSBLF = 'Y' then select SQ_SF_BRJFB0_DJH000.nextval into ls_DJH000 from dual; if LS_JDCZFL in ('Y') then update SF_BRZHXX set xjye00=nvl(xjye00,0)+ls_BLSFJE,zye000=nvl(zye000,0)+ls_BLSFJE where brid00=ls_BRID00; end if; 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; 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); --update SF_BRZHXX的预扣金额 --update SF_BRZHXX set YKZYE0 = YKZYE0 - ls_BLSFJE where BRID00 = ls_BRID00 and JFLBID = ls_JFLBID; if LS_JDCZFL in ('Y') then update SF_BRZHXX set xjye00=nvl(xjye00,0)-ls_BLSFJE,zye000=nvl(zye000,0)-ls_BLSFJE where brid00=ls_BRID00; end if; 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; --zhangyc 2017.06.19 by MZSF-20170619-002 增加账户总余额判断 if SF_SFQYYKTPT='Y' then begin select ZYE000 into LS_ZYE000 from SF_BRZHXX where BRID00=LS_BRID00; exception when others then LS_ZYE000:=0; end; if nvl(LS_ZYE000,0)<0 then AS_YHMSG0:='病人账户余额不足:至少需要预交'||trim(to_char(abs(nvl(LS_ZYE000,0)),'999999990.99'))||'元！'; AS_SYSMSG:=AS_YHMSG0||sqlerrm; rollback; return; end if; end if; --新医保接口参保信息插入到表BM_YBBRXX if trim(as_YBJKID) is not null and trim(as_YBJKID) not in ('0') then update SF_YBJKRZ set BRID00=LS_BRID00,BRXM00=substrb(as_BRXM00,1,50) where YBJKID=as_YBJKID; update BM_YBBRXX set YXBZ00='0' where BRID00=LS_BRID00; insert into BM_YBBRXX(BRID00,JDBM00,JDMC00,AAB034_MC,AAE140_MC,BKC001_MC,BKC026_MC,AKA037,AKA037_MC,AKE053,AAZ149,AKA121, AKB020,AKB021,AAE019,BKE361,AAE030,AAE031,AAB301,AAB301_MC,BKE174,BKE174_MC,AAZ238, AMC020,AAE013,AAZ128,ALC020,BKA162,BKA162_MC,BKA543,BKA543_MC,BKEB35,ZDBM00,ZDMC00,VALUE0) select LS_BRID00,JDBM00,JDMC00,AAB034_MC,AAE140_MC,BKC001_MC,BKC026_MC,AKA037,AKA037_MC,AKE053,AAZ149,AKA121, AKB020,AKB021,AAE019,BKE361,AAE030,AAE031,AAB301,AAB301_MC,BKE174,BKE174_MC,AAZ238, AMC020,AAE013,AAZ128,ALC020,BKA162,BKA162_MC,BKA543,BKA543_MC,BKEB35,ZDBM00,ZDMC00,VALUE0 from SF_YBJKXX where YBJKID=as_YBJKID; end if; if trim(Pcommit)='Y' then commit; end if; exception when no_data_found then as_YHMSG0:='数据没有找到，请核对病人费别或病人的交费类别是否正确！'; as_SYSMSG:=sqlerrm; rollback; when others then as_YHMSG0:=substrb('门诊病人建档失败,失败原因:'||sqlerrm,1,250); as_SYSMSG:=substrb('执行门诊建档过程(SP_SF_BRXX00_REG000)错误,错误原因:'||sqlerrm,1,250); rollback; end;