prompt 21、门诊病人挂号修改、退号存储过程 SP_SF_BRGHXX_EDIT00 create or replace procedure SP_SF_BRGHXX_EDIT00 ( as_SFNLYD in char, --老年优待,Add at GuangZhou on 2002-11-25 as_HZLB00 in char, --优先级别,Add at GuangZhou on 2002-11-25 as_ICKH00 in char, --就诊卡号,若没有卡号(='0'),现金病人挂号时,同时分配卡号 ,Add at GuangZhou on 2002-12-28 as_GHH000 in char, --门诊挂号号 as_YBGHH0 in char, --医保程序生成的挂号流水号,默认值为0,冲销,医保仍生成新的挂号流水号 as_BRBLH0 in char, --门诊病历号 as_GHLBMC in char, --挂号类别名称 as_SFCZ00 in char, --是否初诊,'0':初诊病人,'1':复诊病人 as_XM0000 in char, --姓名 as_XB0000 in char, --性别 as_CSRQ00 in char, --出生年月 as_FBMC00 in char, --病人费别 ad_GHY000 in number, --挂号员编号、或操作员编号 as_GHYXM0 in char, --挂号员姓名 ad_CZYKS0 in number, --操作员的科室,或挂号员的科室 ad_GHKS00 in number, --挂号科室编号 ad_JZYS00 in number, --就诊医生编号 as_JZYSXM in char, --就诊医生姓名 ad_GHHBID in number, --挂号号表流水号 as_YJJXH0 in char, --打印在挂号单的外部序列号 ad_GHF000 in number, --挂号费总金额(挂号费+挂号附加费) ad_GRZHZF in number, --医保个人医疗账户支付(冲销时,该金额是负的) ad_TCJJZF in number, --医保统筹基金支付(冲销时,该金额是负的) ad_ZFJE00 in number, --医保自付金额,若是现金病人,与挂号费总金额相同(冲销时,该金额是负的) as_ZPZH00 in char, --对应的医保中心的编号 as_CZBZ00 in char, --'0':挂号退号,'1':挂号修改 as_SFSBLF in char, --是否收取病人的病历费,'Y':收病历费,'N':不收病历费 ad_ZFDJH0 out number, --自费金额对应的单据号 ad_TJDJH0 out number, --医保的统筹基金对应的单据号 ad_GRDJH0 out number, --医保的个人账户对应的单据号 as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar --系统提示的错误信息 ) as --本存储过程的操作步骤如下: -- 1、先冲销病人原来的挂号信息(对于病人的挂号信息,update,对于病人的账目信息,要求Insert冲销记录) -- 2、生成新的病人的挂号信息的记录 ls_GHKSBH_OLD000 SF_YSGHHB.GHKSBH%TYPE; --旧挂号对应的挂号科室编号 ls_GHYSBH_OLD000 SF_YSGHHB.GHYSBH%TYPE; --旧挂号对应的挂号医生编号 ls_GHLBBH_OLD000 SF_YSGHHB.GHLBBH%TYPE; --旧挂号对应的挂号类别编号 ls_GHMZSJ_OLD000 SF_YSGHHB.GHMZSJ%TYPE; --旧挂号对应的挂号日期 ls_GHHBID_OLD000 SF_YSGHHB.GHHBID%TYPE; --挂号号表ID ls_PJSYQK_OLDCZY SF_PJSYQK.CZY000%TYPE; --旧的票据的操作员 ls_PJSYQK_JKDID0 SF_PJSYQK.JKDID0%TYPE; --旧的票据是否已缴款过 ls_MZID00 SF_BRXXB0.GHID00%TYPE; --门诊ID ls_BRID00 BM_BRXXB0.BRID00%TYPE; --病人ID ls_GHLB00 BM_GHLBB0.LBBH00%TYPE; --挂号类别编码 ls_FBBH00 BM_BRFBB0.FBBH00%TYPE; --病人费别编码 ls_FBBH01 BM_BRFBB0.FBBH00%TYPE; --病人费别编码 ls_JFLBID BM_BRJFLB.JFLBID%TYPE; --病人交费类别ID --ls_TMPDJH SF_BRFY00.DJH000%TYPE; --病人费用的临时流水号 ls_DJH000 SF_BRFY00.DJH000%TYPE; --病人费用的流水号 ls_WJSDJH SF_BRFY00.DJH000%TYPE; --广东省中医院在挂号时，虽是现金，但有部分的费用放在结算时，一起结算 ls_OLDDJH SF_BRFY00.DJH000%TYPE; --病人费用表中要被冲销的单据号 ls_JZDH00 SF_BRFY00.JZDH00%TYPE; --病人挂号时的结账单号 ls_JZDH00_NEW000 SF_BRFY00.JZDH00%TYPE; --病人新的结账单号 ls_OLDJE0 SF_BRFY00.ZFJE00%TYPE; --原挂号费对应的挂号金额 ls_GFBL00 number(12,2); --公费报销比例 ls_JZBL00 number(12,2); --记账报销比例 ls_ZFBLJE BM_YBSFDY.ZFJE00%TYPE; --自付比例对应的金额 ls_JZBLJE BM_YBSFDY.ZFJE00%TYPE; --记账比例对应的金额 ls_GFBLJE BM_YBSFDY.JMJE00%TYPE; --公费比例对应的金额 ls_SCZFJE BM_YBSFDY.ZFJE00%TYPE; --挂号时对应的首次自付金额 ls_YBBRLB BM_BRXXB0.YBBRLB%TYPE; --病人类别 ls_YBZXLB IC_YBBRLB.YBZXLB%TYPE; --医保中心类别 ls_HJJE00 SF_BRFY00.HJJE00%TYPE; --合计总金额 ls_ZFJE00 SF_BRFY00.ZFJE00%TYPE; --自费总金额 ls_JZJE00 SF_BRFY00.JZJE00%TYPE; --记账总金额 ls_GFJE00 SF_BRFY00.GFJE00%TYPE; --公费总金额 ls_XMMC00 SF_FYMX00.XMMC00%TYPE; --费用明细表的项目名称 ls_XMGG00 SF_FYMX00.XMGG00%TYPE; --费用明细表的项目规格 ls_XMDW00 SF_FYMX00.XMDW00%TYPE; --费用明细表的项目单位 ls_MXZFJE SF_FYMX00.ZFJE00%TYPE; --费用明细表的自付金额 ls_MXJZJE SF_FYMX00.JZJE00%TYPE; --费用明细表的记账金额 ls_MXGFJE SF_FYMX00.GFJE00%TYPE; --费用明细表的公费金额 ls_MXHJJE SF_FYMX00.HJJE00%TYPE; --费用明细表的合计金额 ls_FJGHKS SF_FJGHF0.FJGHKS%TYPE; --附加挂号费的附加挂号科室 ls_TMPKS0 SF_FJGHF0.FJGHKS%TYPE; --附加挂号费的附加挂号科室 ls_FJFFBH SF_FJGHF0.FJFFBH%TYPE; --附加挂号费的附加费编码 ls_YDJM00 SF_GHF000.YDJM00%TYPE; --能否优待减免 ls_SFDM00 SF_GHF000.SFXMID%TYPE; --挂号费的收费代码 ls_GHHS00 SF_YSGHHB.GHXHS0%TYPE; --医生挂号的限号数 ls_PJH000 SF_PJSYQK.PJH000%TYPE; --票据流水号 ls_OLDPJH SF_PJSYQK.PJH000%TYPE; --票据号临时保存 ls_PJXH00_OLD SF_PJSYQK.PJXH00%TYPE; --旧的票据序号 ls_PJXJJE_OLD SF_PJSYQK.XJJE00%TYPE; --票据上的现金金额 ls_PJZZJE_OLD SF_PJSYQK.ZZJE00%TYPE; --转账金额，也可称为从病人预交金账户上扣除的金额 ls_PJXJJE SF_PJSYQK.XJJE00%TYPE; --票据上的现金金额 ls_PJZZJE SF_PJSYQK.ZZJE00%TYPE; --转账金额，也可称为从病人预交金账户上扣除的金额 ls_ZFFSBH BM_BRZFFS.ZFFSBH%TYPE; --病人支付方式编码 ls_ZFFSMC BM_BRZFFS.ZFFSMC%TYPE; --病人支付方式名称 ls_ZFFSBH_2 BM_BRZFFS.ZFFSBH%TYPE; --病人支付方式编码 ls_ZFFSMC_2 BM_BRZFFS.ZFFSMC%TYPE; --病人支付方式名称 ls_MZFPID BM_YYSFXM.MZFPID%TYPE; --门诊发票ID ls_SFXMID BM_YYSFXM.SFXMID%TYPE; --收费项目ID ls_YSKS00 SF_FYMX00.YSKS00%TYPE; --医生科室编码 ls_BLSFDM BM_YYSFXM.SFXMID%TYPE; --收费项目ID ls_BLSFJE BM_YYSFXM.SFJE00%TYPE; --病历本费 ls_BLXMMC BM_YYSFXM.XMMC00%TYPE; --病历工本费收费名称 ls_OGHJE0 BM_YYSFXM.SFJE00%TYPE; --挂号费金额,本金额只是挂号的金额,不含诊疗费部门的金额 ls_HZLB00 SF_BRXXB0.HZLB00%TYPE; --优先级别,或称为候诊类别 Add at GuangZhou on 2002-11-25 ls_HZLB01 SF_BRXXB0.HZLB00%TYPE; ls_HZLBID SF_HZBR00.ID0000%TYPE; --候诊类别ID,SF_HZBR00.ID0000 ls_HZSJTD_STR SF_BRXXB0.HZSJD0%TYPE; --候诊时间段 ls_OLDKH0 IC_YHXX00.ICKH00%TYPE; --原病人所持的就诊卡号 ls_ICKDM0 IC_YHXX00.SFDM00%TYPE; --就诊卡收费代码 ls_ICKJE0 IC_YHXX00.ICKJE0%TYPE; --就诊卡收费金额 ls_ICKMC0 SF_FYMX00.XMMC00%TYPE; ls_ICKDW0 SF_FYMX00.XMDW00%TYPE; ls_pjje00 SF_PJSYQK.XJJE00%TYPE; ls_yhbl00 bm_yyhsxm.yhbl00%type; ls_GHDLX0 VARCHAR2(30); --系统所设定的挂号单类型 ls_COUNT0 number(5); --存放记录数 ls_COUNT1 number(5); ls_CZRQ00 char(8); --操作日期 ls_CZSJ00 char(8); --操作时间 SF_YJJGHZXJ char(5); ls_yhdate_1 char(14); ls_yhdate_2 char(14); ls_lbzfbz sf_brxxb0.lbzfbz%type; ls_lbbh00 sf_brxxb0.lbbh00%type; ls_PJJZJE_OLD SF_PJSYQK.JZJE00%TYPE; ls_PJJZJE SF_PJSYQK.JZJE00%TYPE; ls_yblb00 sf_jzb000.yblb00%TYPE; ls_GFAZFGH XT_XTCS00.value0%type; --公费病人是否可以按自费挂号 ls_OLDCZYKS SF_BRFY00.CZYKS0%TYPE; --原挂号费对应的操作员科室 LS_SFYXKYTK XT_XTCS00.value0%type; ls_THBZ00 sf_brxxb0.THBZ00%type; ls_YYJZF0 SF_BRFY00.ZFJE00%TYPE; --原挂号预交金支付总额 ls_SFDTYY SF_HZBR00.SFDTYY%TYPE; --是否当天预约：'1'当天预约 ls_YYID00 SF_YYBRB0.YYID00%TYPE; ls_YYSD00 SF_YYBRB0.YYSD00%TYPE; ld_XZGHTHTS number(5); cursor CUR_SF_BRGH00_FPXM00 IS --判断收费项目对应的项目是否有发票项目存在 select C.MZFPID,C.SFXMID from SF_FYMX00 A,BM_YYSFXM C,SF_BRFY00 D where A.XMBH00 = C.SFXMID and A.DJH000 = D.DJH000 and D.MZH000 = as_GHH000 and D.JZDH00 = ls_JZDH00; cursor CUR_SF_FJGHF0_GHF000 IS --判断挂号费的附加费是否存在 select FJFFBH from SF_FJGHF0 where FJBRLB=as_SFCZ00 and FJGHLB=ls_GHLB00 and (FJGHKS=ls_FJGHKS or (ls_FJGHKS = ls_TMPKS0 and FJGHKS is null)); -- MODIFICATION HISTORY -- Person Date Comments -- qks 2008.08.04 解决：自费病人在挂号时选择了公医挂号类别挂号，退号是退不了。 MZSF-20080804-001; -- qks 2008.12.08 有处方存在，须先经医生申请才可进行退号或改号 MZSF-20081208-004; -- qks 2009.04.20 使用参数SF_SFYXKYTKTH，是否允许跨院退款以及退号等 -- qks 2009.05.22 广东MZSF-20090522-001 -- qks 2009.08.03 参数MZSF_XZGHTHTS>0控制医保病人在医生处挂号也可以自动退号 MZSF-20090720-002 -- qks 2010.10.24 广东省中医院,现金挂号时可以使用预交金支付,退号时支付方式要跟原挂号相同。 -- qks 2011.02.24 广东省中医院,当天预约ls_SFDTYY='1'退号时,sf_ysghhb.yyxhs0,tqyys0都要-1 -- qks 2011.03.09 广东省中医院,自助挂号,使用现金退费 -- linzy 2014.09.03 预约病人退号后不会把号源放出去，先要求把号源放出去，且将sf_yybrb0的状态改为取消 MZSF-20140903-003 -- ------- --------------- -------------------------------------------------------------------------------------------------- begin ls_CZRQ00 := to_char(sysdate,'YYYYMMDD'); ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); ls_ICKDM0 := 0 ; ls_ICKJE0 := 0 ; begin select to_number(VALUE0) into ld_XZGHTHTS from XT_XTCS00 where name00='MZSF_XZGHTHTS'; exception when others then ld_XZGHTHTS := 0; end; begin select trim(value0) into LS_SFYXKYTK from xt_xtcs00 where name00 = 'SF_SFYXKYTKTH'; exception when OTHERS then LS_SFYXKYTK:='Y'; end; begin select trim(value0) into SF_YJJGHZXJ from xt_xtcs00 where name00='SF_YJJGHZXJ'; exception when others then SF_YJJGHZXJ:='N'; end; begin select substrb(upper(trim(value0)),1,1) into ls_GFAZFGH from xt_xtcs00 where name00='SF_GFBRSFKAZFGH'; exception when others then ls_GFAZFGH:='N'; --公费病人是否可以按自费挂号 end; if as_ICKH00 <> '0' then begin select NVL(SFDM00,0),NVL(ICKJE0,0) into ls_ICKDM0,ls_ICKJE0 from IC_YHXX00 where ICKH00 = as_ICKH00 and ZT0000 = '0'; if ls_ICKDM0 <> 0 then select trim(XMMC00), NVL(trim(DW0000),'次') into ls_ICKMC0,ls_ICKDW0 from BM_YYSFXM where SFXMID = ls_ICKDM0; end if; exception when others then as_YHMSG0:='非法卡,请重新输入病人的就诊卡号!'; as_SYSMSG:=SQLERRM||'select * from IC_YHXX00 where ZT0000=0 and ICKH00 = '||as_ICKH00; rollback; return; end; end if; begin update sf_fymx00 set XMDJ00=0,hjje00=0,zfje00=0,jzje00=0,gfje00=0,jmje00=0 where djh000 in( select djh000 from sf_brfy00 where czrq00='18000101' and jzdh00+0=0 and MZH000=as_GHH000); update sf_brfy00 set hjje00=0,zfje00=0,jzje00=0,gfje00=0,qzfje0=0 where czrq00='18000101' and jzdh00+0=0 and MZH000=as_GHH000; end; --病人在挂号修改或挂号退号前，SF_BRFY00中未冲销的记录只有一条，否则，系统提示非法操作 if (SF_YJJGHZXJ='Y') then select fbbh00,lbbh00,lbzfbz into ls_fbbh01,ls_lbbh00,ls_lbzfbz from sf_brxxb0 where GHH000=as_GHH000; end if; if (SF_YJJGHZXJ='Y') and (ls_fbbh01=3)and(ls_lbbh00=1)and(ls_lbzfbz='1') then--医保病人挂号转换 select sum(hjje00) into ls_COUNT0 from SF_BRFY00 where MZH000 = as_GHH000; if ls_COUNT0 <> 0 then as_YHMSG0:='系统查找该挂号信息还有病人费用,不能退号!'; as_SYSMSG:=substrb(SQLERRM||'select sum(hjje00) into ls_COUNT0 from SF_BRFY00 where MZH000 = '||as_GHH000,1,200); rollback; return; end if; ls_OLDDJH:=0; ls_JFLBID:=0; ls_JZDH00:=0; ls_OLDJE0:=0; else select nvl(sum(b.hjje00),0) into ls_COUNT0 from SF_BRFY00 a,sf_fymx00 b where a.djh000=b.djh000 and xmbh00>9999999990 and MZH000 = as_GHH000; if ls_COUNT0 <> 0 then as_YHMSG0:='系统该挂号信息还有对应的药品费用,请察看详细信息或稍候再试!'; as_SYSMSG:=substrb(SQLERRM||'select nvl(sum(b.hjje00),0) from SF_BRFY00 a,sf_fymx00 b where a.djh000=b.djh000 and xmbh00>9999999990 and MZH000='||as_GHH000,1,200); rollback; return; end if; select nvl(b.xjje00,0)+nvl(b.zzje00,0)+nvl(b.jzje00,0) into ls_pjje00 from sf_brxxb0 a,sf_pjsyqk b where a.pjh000=b.pjh000 and b.PLBH00='0' and a.GHH000=as_GHH000 and rownum=1; --modify by xuzw 添加上记帐金额 if ls_pjje00<>0 then --有挂号费用 select min(a.djh000) into ls_OLDDJH from sf_fymx00 a,sf_brfy00 b where a.djh000=b.djh000 and a.CXBZ00='Z' and b.mzh000=as_ghh000; select nvl(sum(hjje00),0) into ls_COUNT0 from SF_BRFY00 where djh000+0<>ls_olddjh and MZH000 = as_GHH000; if ls_COUNT0 <> 0 then as_YHMSG0:='系统查找该挂号信息对应的费用时,该挂号还有除挂号费用外的其它费用!'; as_SYSMSG:=substrb(SQLERRM||' select nvl(sum(hjje00),0) from SF_BRFY00 where djh000+0<> '||to_char(ls_olddjh)||' and MZH000= '||as_GHH000,1,200); rollback; return; end if; --预交病人结算过不能退号 select nvl(sum(hjje00),0) into ls_COUNT0 from SF_BRFY00 where JZDH00+0<>0 AND JFLBID=1 and djh000=ls_olddjh and MZH000 = as_GHH000; if ls_COUNT0 > 0 then as_YHMSG0:='预交金病人挂号费用结算过不能改号、退号!'; as_SYSMSG:=substrb(SQLERRM||'select nvl(sum(hjje00),0) from SF_BRFY00 where JZDH00+0<>0 AND JFLBID=1 and djh000+0= '||to_char(ls_olddjh)||' and MZH000= '||as_GHH000,1,200); rollback; return; end if; --医生站挂号的病人，须先经医生申请才可进行退号或改号 if ld_XZGHTHTS<=0 then --add by qks 2009.08.03 select count(*) into ls_COUNT0 from sf_fymx00 a,sf_brfy00 b,sf_brxxb0 c where a.djh000=b.djh000 and b.mzid00=c.ghid00 and a.djh000=ls_OLDDJH and c.ghh000 = as_GHH000 and c.thbz00='2' and a.sfydj0='0'; if ls_COUNT0 <> 0 then as_YHMSG0:='医生站挂号的病人，须先经医生申请才可进行退号或改号!'; as_SYSMSG:=substrb(SQLERRM||' select thbz00 from SF_BRXXB0 where GHH000= '||as_GHH000,1,200); rollback; return; end if; end if; --有处方存在，须先经医生申请才可进行退号或改号 2008.12.08 qks select count(*) into ls_COUNT0 from YS_CFXXB0 a,sf_brxxb0 c where a.ghid00=c.ghid00 and c.ghh000 = as_GHH000 and c.thbz00='2' and a.CFZT00<>'2'; if ls_COUNT0 <> 0 then as_YHMSG0:='有处方存在，须先经医生申请才可进行退号或改号!'; as_SYSMSG:=substrb(SQLERRM||' select thbz00 from SF_BRXXB0 where GHH000= '||as_GHH000,1,200); rollback; return; end if; begin select distinct A.DJH000,A.JFLBID,A.JZDH00,A.ZFJE00,A.CZYKS0 into ls_OLDDJH,ls_JFLBID,ls_JZDH00,ls_OLDJE0,ls_OLDCZYKS from SF_BRFY00 A where exists (select 1 from SF_FYMX00 B where A.DJH000 = B.DJH000 and b.djh000=ls_olddjh and b.xmbh00<9999999990) and (jflbid = 1 or jzdh00+0 <> 0) and A.MZH000 = as_GHH000; exception when others then ls_OLDDJH:=0; ls_JFLBID:=0; ls_JZDH00:=0; ls_OLDJE0:=0; ls_OLDCZYKS:=0; end; else ls_OLDDJH:=0; ls_JFLBID:=0; ls_JZDH00:=0; ls_OLDJE0:=0; ls_OLDCZYKS:=0; end if; end if; if (ls_OLDCZYKS<>0) and (LS_SFYXKYTK='N') then --add by qks 2009.04.20 select count(1) into ls_COUNT0 from BM_BMBM00 a,BM_BMBM00 b where a.BMBH00=LS_OLDCZYKS and b.BMBH00=ad_CZYKS0 and a.YYID00=b.YYID00; if ls_COUNT0=0 then as_YHMSG0:='不能进行跨院退号操作!'; as_SYSMSG:=SQLERRM; rollback; return; end if; end if; --得到门诊的候诊类别 begin select HZLB00 into ls_HZLB00 from BM_HZLB00 where HZLBMC = as_HZLB00; exception when OTHERS then as_YHMSG0:='系统无法得到该挂号病人的优先级别对应的编码,请与系统管理员联系!'; as_SYSMSG:=SQLERRM||'select * from BM_HZLB00 where HZLBMC = '||as_HZLB00; rollback; return; end; --得到就诊医生所在的科室 begin select BMBH00 into ls_YSKS00 from BM_YGBM00 where YGBH00 = ad_JZYS00; exception when NO_DATA_FOUND then ls_YSKS00 := 0 ; end; --得到挂号单的类型 begin select NVL(rtrim(value0),'0') into ls_GHDLX0 from XT_XTCS00 where name00 = 'SF_GHDLX0'; exception when OTHERS then ls_GHDLX0:='0'; --若没有查找到,挂号单类型默认为'0', ls_GHDLX0=221094:挂号单使用发票 end; --0、先得到旧的挂号的信息,得到后，可以修改挂号号表，将该医生对应的挂号号数减少1 begin select GHKS00,JZYS00,GHLB00,GHRQ00,BRID00,GHID00,PJH000,FBBH00,THBZ00 into ls_GHKSBH_OLD000,ls_GHYSBH_OLD000,ls_GHLBBH_OLD000,ls_GHMZSJ_OLD000,ls_BRID00,ls_MZID00,ls_OLDPJH,ls_FBBH00,ls_THBZ00 from SF_BRXXB0 where GHH000 = as_GHH000; select PJXH00,XJJE00,ZZJE00,CZY000,JKDID0,JZJE00 into --modify by xuzw 添加记帐金额 ls_PJXH00_OLD,ls_PJXJJE_OLD,ls_PJZZJE_OLD,ls_PJSYQK_OLDCZY,ls_PJSYQK_JKDID0,ls_PJJZJE_OLD from SF_PJSYQK where PJH000 = ls_OLDPJH; exception when OTHERS then as_YHMSG0:='系统没有找到该病人的挂号信息,操作失败!'; as_SYSMSG:=SQLERRM||'select * from sf_pjsyqk where pjh000='||to_char(ls_OLDPJH); rollback; return; end; if ls_GHDLX0='9' then --add by qks for MZSF-20090522-001 select count(1) into ls_COUNT0 from YS_BRBQB0 where GHID00=ls_MZID00; if (ls_THBZ00='2') or (ls_COUNT0>0) then as_YHMSG0:='对不起,此病人已经有医生接诊,请到护士站做退号申请!'; as_SYSMSG:=SQLERRM||'select count(1) into ls_COUNT0 from YS_BRBQB0 where GHID00=ls_MZID00'; rollback; return; end if; end if; --Add at 广州，在收费项目视图中按费别，医保中心类别，病人类别等字段来获得对应的自付比例 begin select A.YBBRLB,B.YBZXLB into ls_YBBRLB,ls_YBZXLB from BM_BRXXB0 A,IC_YBBRLB B where A.YBLB00 = B.YBLB00 and A.FBBH00 = B.FBBH00 and A.BRID00 = ls_BRID00 and A.FBBH00 = ls_FBBH00; exception when OTHERS then if (ls_GFAZFGH='Y') then if ls_FBBH00<>1 then --自费转公费 added by qks for MZSF-20080804-001 ls_YBBRLB := '0'; begin select YBZXLB,YBLB00 into ls_YBZXLB,ls_yblb00 from IC_YBBRLB where FBBH00 = ls_FBBH00 and YBLB00 =(select yblb00 from sf_jzb000 where mzid00=ls_MZID00 and rownum=1); --as_ZPZH00; exception when NO_DATA_FOUND then as_YHMSG0:='医保病人所属医保中心名称有错，系统字典维护不完整！'; as_SYSMSG:=SQLERRM||'Not exists(select YBZXLB,YBLB00 into ls_YBZXLB,ls_yblb00 from IC_YBBRLB where FBBH00 = '||to_char(ls_FBBH00)||' and YBLB00 =(select yblb00 from sf_jzb000 where mzid00='||to_char(ls_FBBH00)||' and rownum=1))'; rollback; return; end; else --公费转自费 ls_YBBRLB := '0'; ls_YBZXLB := '0'; end if; else as_YHMSG0:='系统提取该病人的类别名称时,未查找到满足条件的记录!'; as_SYSMSG:=SQLERRM||'select A.YBBRLB,B.YBZXLB from BM_BRXXB0 A,IC_YBBRLB B where A.YBLB00 = B.YBLB00 and A.FBBH00 = B.FBBH00 and A.BRID00 ='||to_char(ls_BRID00)||' and FBBH00 ='||to_char(ls_FBBH00); rollback; return; end if; end; --得到候诊表的对应ID select nvl(max(ID0000),0),NVL(max(YSPBID),0),NVL(max(HZLB00),0),NVL(max(SFDTYY),'0') into ls_HZLBID,ls_GHHBID_OLD000,ls_HZLB01,ls_SFDTYY from SF_HZBR00 where GHH000 = as_GHH000 and HZZT00<>'Z'; if ls_HZLB01=3 then--退预约号 update SF_YSGHHB set GHYGHS = nvl(GHYGHS,0)-1, GHYYHS = nvl(GHYYHS,0)-1, YYXHS0 = nvl(YYXHS0,0)-decode(ls_SFDTYY,'1',1,0), TQYYS0 = nvl(TQYYS0,0)-decode(ls_SFDTYY,'1',1,0) where GHKSBH = ls_GHKSBH_OLD000 and GHYSBH = ls_GHYSBH_OLD000 and GHLBBH = ls_GHLBBH_OLD000 and GHMZSJ = ls_GHMZSJ_OLD000 and (GHHBID = ls_GHHBID_OLD000 or ls_GHHBID_OLD000 = 0) and GHYGHS >= 1 and rownum = 1; else update SF_YSGHHB set GHYGHS = nvl(GHYGHS,0)-1 where GHKSBH = ls_GHKSBH_OLD000 and GHYSBH = ls_GHYSBH_OLD000 and GHLBBH = ls_GHLBBH_OLD000 and GHMZSJ = ls_GHMZSJ_OLD000 and (GHHBID = ls_GHHBID_OLD000 or ls_GHHBID_OLD000 = 0) and GHYGHS >= 1 and rownum = 1; end if; --1、修改病人的挂号信息记录 if as_CZBZ00 = '1' then --若操作是挂号修改 begin select LBBH00 into ls_GHLB00 from BM_GHLBB0 where LBMC00 = as_GHLBMC; exception when NO_DATA_FOUND then as_YHMSG0:='系统没有找到满足条件的挂号类别名称，请重新输入该病人的挂号类别!'; as_SYSMSG:=SQLERRM; rollback; return; end; begin select FBBH00 into ls_FBBH00 from BM_BRFBB0 where FBMC00 = as_FBMC00; exception when NO_DATA_FOUND then as_YHMSG0:='系统没有找到满足条件的病人费别名称，请重新输入该病人的费别名称!'; as_SYSMSG:=SQLERRM; rollback; return; end; --判断要收取的病历本费是否正常 if as_SFSBLF = 'Y' then begin select SFXMID,NVL(SFJE00,0),XMMC00 into ls_BLSFDM,ls_BLSFJE,ls_BLXMMC 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||'病历工本费=0'; rollback; return; end if; exception when NO_DATA_FOUND then as_YHMSG0:='系统未找到病历工本费所对应的收费项目,请先定义病历工本费后再进行本操作!'; as_SYSMSG:=SQLERRM||'Not exists select * from bm_yysfxm where xmmc00='||'病历工本费'; rollback; return; end; end if; begin ls_HZSJTD_STR:=trim(SF_YS_HZSJ00(ls_HZLBID,ls_CZSJ00)); --根据函数得到相关的时间段 exception when OTHERS then as_YHMSG0:='系统计算该病人的候诊时间时出现错误,请稍候再试!'; as_SYSMSG:=SQLERRM||'SF_YS_HZSJ00('||to_char(ls_HZLBID)||')'; rollback; return; end; --判断输入的门诊病历号是否合法 select count(*) into ls_COUNT0 from BM_BRXXB0 where BRBLH0 = as_BRBLH0 and BRBLH0 <> '0' and BRID00<>ls_BRID00; if ls_COUNT0 > 1 then as_YHMSG0:='门诊病历号重复,请重新输入病人的病历号!'; as_SYSMSG:=SQLERRM; rollback; return; end if; update SF_BRXXB0 set YBGHH0 = as_YBGHH0, --医保生成的挂号流水号 BRBLH0 = as_BRBLH0, GHLB00 = ls_GHLB00, XM0000 = as_XM0000, XB0000 = as_XB0000, CSRQ00 = as_CSRQ00, FBBH00 = ls_FBBH00, GHRQ00 = ls_CZRQ00, GHSJ00 = ls_CZSJ00, GHY000 = ad_GHY000, GHYXM0 = as_GHYXM0, GHKS00 = ad_GHKS00, JZRQ00 = ls_CZRQ00, JZSJ00 = ls_CZSJ00, JZKS00 = ad_GHKS00, JZYS00 = ad_JZYS00, JZYSXM = as_JZYSXM, SFNLYD = as_SFNLYD, HZLB00 = ls_HZLB00, HZSJD0 = ls_HZSJTD_STR where GHH000 = as_GHH000; update BM_BRXXB0 set BRBLH0 = as_BRBLH0, BRXM00 = as_XM0000, BRXB00 = as_XB0000, BRCSRQ = as_CSRQ00 where BRID00 = ls_BRID00; update SF_HZBR00 set GHKS00 = ad_GHKS00, JZKS00 = ad_GHKS00, GHYS00 = ad_JZYS00, GHLB00 = ls_GHLB00, HZLB00 = ls_HZLB00, DJRQ00 = ls_CZRQ00, DJSJ00 = ls_CZSJ00, HZQSRQ = NVL(substr(ls_HZSJTD_STR,1,8),HZQSRQ), --候诊起始日期 HZQSSJ = NVL(substr(ls_HZSJTD_STR,9,8),HZQSSJ), --候诊起始时间 HZJZRQ = NVL(substr(ls_HZSJTD_STR,18,8),HZJZRQ), --候诊结束日期 HZJZSJ = NVL(substr(ls_HZSJTD_STR,26,8),HZJZSJ), --候诊结束时间 HZXH00 = NVL(substr(ls_HZSJTD_STR,35,5),HZXH00), --候诊序号 CZY000 = ad_GHY000, JZZS00 = (select min(GHHZCK) from SF_YSGHHB where ghhbid =ad_GHHBID), YSPBID = ad_GHHBID where ID0000 = ls_HZLBID; if SQL%NOTFOUND then as_YHMSG0:='系统对病人候诊信息操作失败,请稍候再试!'; as_SYSMSG:=SQLERRM||'update SF_HZBR00 where HZZT00=0 and ID0000='||to_char(ls_HZLBID); rollback; return; end if; end if; if as_CZBZ00 = '0' then --若操作是挂号退号 update SF_BRXXB0 set YBGHH0 = as_YBGHH0, --医保生成的挂号流水号 THRQ00 = ls_CZRQ00, THSJ00 = ls_CZSJ00, THY000 = ad_GHY000, THYXM0 = as_GHYXM0, THBZ00 = '0' --该挂号信息已退号 where GHH000 = as_GHH000; --普通病人保留旧记录，其他病人删除候诊记录 邹小平修改 begin delete from SF_HZBR00 where ID0000 = ls_HZLBID and HZLB00<>'1'; --将病人候诊表中的信息删除 update SF_HZBR00 set DLXH00=-100,HZZT00='Z' where ID0000=ls_HZLBID and HZLB00='1'; delete from ys_czbrxx where ghid00 in (select ghid00 from sf_brxxb0 where ghh000=as_GHH000); end; -- linzy 预约取号后退号要求号源能够释放 YYGH-20140814-001 select nvl(max(GHHBID),0) into ls_GHHBID_OLD000 from SF_BRXXB0 where GHH000=as_GHH000; select count(1),MAX(YYID00),MAX(YYSD00) into ls_COUNT0,ls_YYID00,ls_YYSD00 from SF_YYBRB0 where GHHBID=ls_GHHBID_OLD000 and GHID00=ls_MZID00 and YYRQ00=ls_GHMZSJ_OLD000; --如果是预约病人 if ls_COUNT0>0 then select count(1) into ls_COUNT1 from SF_BRXXB0 where GHH000=as_GHH000 and GHYXM0 like '%预约%'; if ls_COUNT1>0 then update YS_HBHZXH set BRID00=null,YYID00=null,BRXM00=null,GHH000=null,ZY0000=ZY0000||trim(GHH000)||'退转号' where GHHBID=ls_GHHBID_OLD000 and GHH000=as_GHH000 and YXBZ00='1'; update SF_YSGHHB set YYXHS0=YYXHS0-1,YYXHMX=SF_YY_YYXHMX(YYXHMX,GHQSSJ,ls_YYSD00,-1,ls_GHHBID_OLD000),YYSL00=YYSL00-1 where ghhbid = ls_GHHBID_OLD000; end if; update SF_YYBRB0 set YYZT00='2',QXRQ00=to_char(sysdate,'YYYYMMDD'),QXSJ00=to_char(sysdate,'HH24:MI:SS'),QXR000=ad_GHY000,QXLX00='1',BZ0000='退号自动取消' where YYID00=ls_YYID00 and (YYZT00='3') and YYRQ00=ls_GHMZSJ_OLD000; end if; end if; --2、冲销病人的旧的挂号对应的账目信息,挂号修改及挂号退号的冲销过程相同 if ls_OLDDJH <> 0 then --2.0、修改医生挂号号数 --考虑到修改历史挂号数的情况,可能没有多大意义，这部分暂时不做处理！ --2.1、添加费用信息主表的冲销记录 select SQ_SF_PJSYQK_PJH000.nextval into ls_PJH000 from dual; --若是现金病人,其冲销的记录也要立刻结算,否则,就不用进行结算处理 if ls_JFLBID = 0 then select SQ_SF_JZB000_JZDH00.nextval into ls_JZDH00_NEW000 from dual; else ls_JZDH00_NEW000:=0; end if; --挂号冲销也应该有相应的冲销的票据号 select SQ_SF_BRJFB0_DJH000.nextval into ls_DJH000 from dual; --2.1、添加费用信息明细表的冲销记录 Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00,JMJE00,JMSHR0,JMYY00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,KDBQ00,ZXKS00,ZXYS00,YSKS00,SFYDJ0,CXBZ00,ZFBL00) select SQ_SF_FYMX00_MXID00.nextval,ls_DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,-XMSL00,-HJJE00,-ZFJE00,-GFJE00,-JZJE00,-JMJE00,JMSHR0,JMYY00, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,KDKS00,KDYS00,KDBQ00,ZXKS00,ZXYS00,YSKS00,SFYDJ0,'*',ZFBL00 from SF_FYMX00 where DJH000=ls_OLDDJH and cxbz00='Z'; select sum(hjje00),sum(zfje00),sum(jzje00),sum(gfje00) into ls_HJJE00,ls_zfje00,ls_jzje00,ls_gfje00 from sf_fymx00 where djh000=ls_DJH000; --2.2、添加新的冲销记录 Insert into SF_BRFY00(DJH000,MZH000,MZID00,JFLBID,BRID00,XM0000,BRFB00,HJJE00,ZFJE00,JZJE00,GFJE00,BRYE00, CZRQ00,CZSJ00,CZY000,CZYXM0,CZYKS0,JZDH00,CXDJH0,CXPJR0,CXYY00) select ls_DJH000,MZH000,MZID00,JFLBID,BRID00,XM0000,BRFB00,nvl(ls_hjje00,0),nvl(ls_zfje00,0),nvl(ls_jzje00,0),nvl(ls_gfje00,0),0, ls_CZRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,ad_CZYKS0,decode(JZDH00,0,0,ls_JZDH00_NEW000),DJH000,as_GHYXM0,'挂号冲销' from SF_BRFY00 where DJH000=ls_OLDDJH; --以下两句的位置开始到这,Modified on 2002-12-24 --本句之前曾用EXISTS,但速度很慢,又改成用IN update SF_FYMX00 A Set CXBZ00 = '+' where A.DJH000 in (select DJH000 from SF_BRFY00 where MZH000 = as_GHH000 and CXDJH0+0 = 0 );--DJH000 = ls_OLDDJH; --已被冲销 --2.2.1、Update 旧的费用明细信息记录，SF_BRFY00表中旧的费用记录不做修改 update SF_BRFY00 Set CXDJH0 = ls_OLDDJH, --这样可以保证只有一条有效的记录 CXPJR0 = as_GHYXM0, CXYY00 = '挂号冲销' where MZH000 = as_GHH000 and HJJE00 > 0; --DJH000 = ls_OLDDJH --2.3、若是现金病人，还应该再添加一条新的交费冲销的记录,结算记录 if ls_JFLBID = 0 then --说明该病人为现金病人 --添加一条交费冲销记录 begin select ZFFSBH ,ZFFSMC into ls_ZFFSBH,ls_ZFFSMC from BM_BRZFFS where ZFFSMC = '现金'; exception when NO_DATA_FOUND then as_YHMSG0:='系统没有找到支付方式为现金的记录，门诊字典表BM_BRZFFS数据不完整,操作失败!'; as_SYSMSG:=SQLERRM; rollback; return; end; begin select ZFFSBH ,ZFFSMC into ls_ZFFSBH_2,ls_ZFFSMC_2 from BM_BRZFFS where ZFFSBH = 7 and ZFFSMC like '%记账%'; exception when NO_DATA_FOUND then as_YHMSG0:='系统没有找到支付方式为记账的字典数据，门诊字典表BM_BRZFFS数据不完整,操作失败!'; as_SYSMSG:=SQLERRM; rollback; return; end; --modify by qks 2010.10.24 广东省中医院,现金挂号时可以使用预交金支付,退号时支付方式要跟原挂号相同 ls_YYJZF0 := 0; -- if ls_GHDLX0 = '9' and ls_OLDJE0<>0 then -- select nvl(sum(JFJE00),0) into ls_YYJZF0 from sf_brjfb0 where jzdh00=ls_JZDH00 and zffs00=19; -- if ls_YYJZF0<>0 then -- Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000, -- JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) -- values(SQ_SF_BRJFB0_DJH000.nextval,ls_BRID00,ls_JFLBID,-ls_YYJZF0,19,'预交金',ls_PJH000, -- ls_CZRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'1',0,ls_JZDH00_NEW000,1,-ls_YYJZF0); -- end if; -- end if; --modify by qks 增加 +ls_YYJZF0 select SQ_SF_BRJFB0_DJH000.nextval into ad_ZFDJH0 from dual; --自费金额对应的单据号 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) values(ad_ZFDJH0,ls_BRID00,ls_JFLBID,-ls_OLDJE0+ls_YYJZF0,ls_ZFFSBH,ls_ZFFSMC,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'1',0,ls_JZDH00_NEW000,1,-ls_OLDJE0+ls_YYJZF0); end if; --2.4、对冲销的挂号费做结算登记 --先判断收费项目对应的是否有发票项目存在 OPEN CUR_SF_BRGH00_FPXM00; LOOP FETCH CUR_SF_BRGH00_FPXM00 INTO ls_MZFPID,ls_SFXMID; EXIT when CUR_SF_BRGH00_FPXM00%NOTFOUND; select count(*) into ls_COUNT0 from BM_FPXM00 where FPXMID = ls_MZFPID; if ls_COUNT0 = 0 then as_YHMSG0:='操作失败，发票项目不完整!'; as_SYSMSG:='BM_YYSFXM.SFXMID='||to_char(ls_SFXMID)||'的MZFPID的值NOT in BM_FPXM00.FPXMID!'; rollback; return; end if; end LOOP; CLOSE CUR_SF_BRGH00_FPXM00; --Modified on 2001-11-08,预交金病人的挂号费是未结算过的 if ls_JZDH00_NEW000 <> 0 and ls_JZDH00 <> 0 then select count(*) into ls_COUNT0 from SF_JZB000 where JZDH00 = ls_JZDH00; if ls_COUNT0 = 0 then as_YHMSG0:='挂号冲销时操作失败,原因:将冲销的挂号费结算时，查找不到被冲销的挂号费对应的结算记录!'; as_SYSMSG:='NOT EXISTS(select * from SF_JZB000 where JZDH00='||to_char(ls_JZDH00); rollback; return; end if; --Modify on 2001-21-03,因为在对收费员进行缴款登记时候,会从SF_JZB000入手,所以,若没有操作的话,就有可能出现问题,(Insert into SF_JZB000将旧的挂号费对应的冲销费用结算) Insert into SF_JZB000(JZDH00,BRID00,MZID00,BRZJE0,JZJE00,GFJE00,HJJE00,JMJE00,GRZHZF,TCJJZF,ZFJE00,PJH000,JZRQ00,JZSJ00,XJFKJE,ZZFKJE,JZY000) select ls_JZDH00_NEW000,BRID00,MZID00,-BRZJE0,-JZJE00,-GFJE00,-HJJE00,-JMJE00,-GRZHZF,-TCJJZF,-ZFJE00,ls_PJH000,ls_CZRQ00,ls_CZSJ00,-XJFKJE,-ZZFKJE,ad_GHY000 from SF_JZB000 where JZDH00 = ls_JZDH00; if (ls_GFAZFGH='Y') then update SF_JZB000 SET FBBH00 = ls_FBBH00,YBLB00 = ls_yblb00,YBBRLB = ls_YBBRLB where JZDH00 = ls_JZDH00_NEW000; end if; Insert into SF_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00,ZFBL00) select ls_JZDH00_NEW000,B.FPXMID,B.XMMC00,-sum(A.HJJE00),A.ZFBL00 --因为金额是根据冲销前的记录,其金额是正的 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 = ls_JZDH00 group by D.JZDH00,B.FPXMID,B.XMMC00,A.ZFBL00; --由于广东省中医院公医病人在挂号时,诊金被拆分成两条,同时,未结算的操作日期有设置成'18000101' update SF_FYMX00 set CZRQ00 = ls_CZRQ00,ZXRQ00 = ls_CZRQ00 where CZRQ00 = '18000101' and DJH000 in (select DJH000 from SF_BRFY00 where MZH000 = as_GHH000 and CZRQ00 = '18000101'); update SF_BRFY00 set CZRQ00 = ls_CZRQ00 where MZH000 = as_GHH000 and CZRQ00 = '18000101'; end if; --添加票据使用情况表,由于用户要求退款时不再打印挂号单,挂号单是有价的,所以,PJXH00='0' --if ls_JFLBID <> 0 then --有预交金账户的病人 -- ls_PJXJJE := 0; --票据上的现金金额 -- ls_PJZZJE := -ls_OLDJE0; --转账金额，也可称为从病人预交金账户上扣除的金额 -- else -- ls_PJXJJE := -ls_OLDJE0; --票据上的现金金额 -- ls_PJZZJE := 0; --转账金额，也可称为从病人预交金账户上扣除的金额 --end if; ls_PJXJJE := -ls_OLDJE0; --票据上的现金金额 ls_PJZZJE := 0; --转账金额，也可称为从病人预交金账户上扣除的金额 --注意:由于挂号退号,挂号修改时,都是将先冲销一条做为回收的标志 Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,OLDPJH,XM0000,XB0000,BRID00,JZJE00) select ls_PJH000,PLBH00,PLMC00,'-'||rtrim(ls_PJXH00_OLD),-ls_PJXJJE_OLD,-ls_PJZZJE_OLD,ad_GHY000,as_GHYXM0, ls_CZRQ00,ls_CZSJ00,'N','2',as_GHH000,as_XM0000,as_XB0000,ls_BRID00,-ls_PJJZJE_OLD from bm_pjlxb0 where PLBH00 = 0;--PLBH00=0是挂号单 MODIFY BY XUZW 添加记帐金额 if SQL%NOTFOUND then as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败!'; as_SYSMSG:=SQLERRM||'票据类型(挂号单)不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 0)'; rollback; return; end if; --添加票据使用明细表的信息 --不管是挂号修改还是挂号退号,都应该将旧的挂号单作废 update SF_PJSYQK set PJZT00 = '1',HSCZY0 =ad_GHY000, HSCZXM =as_GHYXM0,HSRQ00 =ls_CZRQ00,HSSJ00 =ls_CZSJ00,OLDPJH = as_GHH000,NEWPJH=ls_PJH000 where PJH000 = ls_OLDPJH; --2.5、若是有生成医保接口文件，即有返回个人账户金额、统筹基金金额,也应该在病人交费表中添加两条记录 if as_YBGHH0 <> '0' then select SQ_SF_BRJFB0_DJH000.nextval into ad_TJDJH0 from dual; --医保的统筹基金对应的单据号 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) values(ad_TJDJH0,ls_BRID00,ls_JFLBID,ad_GRZHZF,3,'医保个人账户',as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ls_JZDH00_NEW000,1,ad_GRZHZF); select SQ_SF_BRJFB0_DJH000.nextval into ad_GRDJH0 from dual; --医保的个人账户对应的单据号 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) values(ad_GRDJH0,ls_BRID00,ls_JFLBID,ad_TCJJZF,4,'医保统筹基金',as_ZPZH00,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ls_JZDH00_NEW000,1,ad_TCJJZF); end if; ELSIF (SF_YJJGHZXJ='Y') and (ls_fbbh00=3)and(ls_lbbh00=1)and(ls_lbzfbz='1') then select SQ_SF_PJSYQK_PJH000.nextval into ls_PJH000 from dual; Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,OLDPJH,XM0000,XB0000,BRID00,JZJE00) select ls_PJH000,PLBH00,PLMC00,'-'||rtrim(PJXH00),-XJJE00,-ZZJE00,ad_GHY000,as_GHYXM0,ls_CZRQ00,ls_CZSJ00,'N','2',as_GHH000,as_XM0000,as_XB0000,BRID00,-JZJE00 from sf_pjsyqk where PJH000 = ls_OLDPJH; update SF_PJSYQK set PJZT00 = '1',HSCZY0 =ad_GHY000, HSCZXM =as_GHYXM0,HSRQ00 =ls_CZRQ00,HSSJ00 =ls_CZSJ00,OLDPJH = as_GHH000,NEWPJH=ls_PJH000 where PJH000 = ls_OLDPJH; end if; --end of ls_OLDDJH <> 0 --3、若操作是挂号修改(不考虑医保病人,因为医保病人必须先挂号冲销后，再重新挂号),还应该添加病人新的挂号对应的账目信息 if as_CZBZ00 = '1' then select SQ_SF_PJSYQK_PJH000.nextval into ls_PJH000 from dual; --3.0得到新的费用表、费用明细表都用到的单据号 select SQ_SF_BRJFB0_DJH000.nextval into ls_DJH000 from dual; --3.1、生成病人新的挂号记录相对应的费用(挂号费、附加费)信息 begin select NVL(SFXMID,0),YDJM00 into ls_SFDM00,ls_YDJM00 from VW_SF_GHF000 where GHLB00 = ls_GHLB00 and SFYY00 = 'N'; exception when NO_DATA_FOUND then ls_SFDM00 := 0; 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; --查找挂号费的报销比例 ls_GFBL00 := 0; ls_JZBL00 := 0; --特殊医院特殊处理，广东省中医院还要挂号费进行拆分(如:残疾人士,特困人士,其挂号费还可能要减免) begin if ls_SFDM00 <> 0 then begin select substrb(VALUE0,1,14) into ls_yhdate_1 from XT_XTCS00 WHERE NAME00='YJ_HHSJD'; select substrb(VALUE0,16,14) into ls_yhdate_2 from XT_XTCS00 WHERE NAME00='YJ_HHSJD'; select nvl(yhbl00,1) into ls_yhbl00 from BM_YYSFXM A,BM_YYHSXM B where A.hsxmid=B.hsxmid and A.SFXMID = ls_SFDM00 and to_char(sysdate,'YY.MM.DD HH24:MI') between ls_yhdate_1 and ls_yhdate_2; select NVL(ls_yhbl00,1) into ls_yhbl00 from dual; exception when others then ls_yhbl00:=1; end; select NVL(1-ZFBL00-JMBL00,0),NVL(JMBL00,0),NVL(XMJE00*ls_yhbl00,0),NVL(ZFJE00*ls_yhbl00,0),NVL(XMJE00*ls_yhbl00-ZFJE00*ls_yhbl00-JMJE00*ls_yhbl00,0),NVL(JMJE00*ls_yhbl00,0) into ls_JZBL00,ls_GFBL00,ls_MXHJJE,ls_MXZFJE,ls_MXJZJE,ls_MXGFJE from VW_BM_YBSFDY where YBBRLB = ls_YBBRLB and YBZXLB = ls_YBZXLB and SFXMID = ls_SFDM00 and FBBH00 = ls_FBBH00 and SFYP00 = 'N'; --是否药品 else ls_JZBL00 := 0; ls_GFBL00 := 0; ls_MXHJJE := 0; ls_MXZFJE := 0; ls_MXJZJE := 0; ls_MXGFJE := 0; end if; exception when NO_DATA_FOUND then as_YHMSG0:='系统未查找到满足条件的挂号费的自付比例、记账比例的记录!'; as_SYSMSG:=SQLERRM||'select * from VW_BM_YBSFDY where YBBRLB = '||ls_YBBRLB||' and YBZXLB='||ls_YBZXLB||' and SFXMID ='||to_char(ls_SFDM00)||' and FBBH00 ='||to_char(ls_FBBH00)||' and SFYP00=N'; rollback; return; end; --若as_SFNLYD='Y',说明有老年优待，同时该类别的挂号号允许减免,则应处理免收挂号费,否则按收费项目的对应关系获取比例 if as_SFNLYD = 'Y' and ls_YDJM00 = 'Y' then ls_GFBL00 := 1; ls_JZBL00 := 0; ls_MXZFJE := 0; ls_MXJZJE := 0; ls_MXGFJE := ls_MXHJJE; end if; --添加挂号费的费用 if ls_SFDM00 <> 0 then insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,YSKS00,SFYDJ0,ZFBL00) select SQ_SF_FYMX00_MXID00.nextval,ls_DJH000,SFXMID,XMMC00,'',DW0000,ls_MXHJJE,1,ls_MXHJJE,ls_MXZFJE,ls_MXGFJE,ls_MXJZJE, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,'1',(1-ls_JZBL00-ls_GFBL00) from BM_YYSFXM where SFXMID = ls_SFDM00; --修改相关冗余字段的值,Added on 2002-11-26 select SFJE00 into ls_OGHJE0 from BM_YYSFXM where SFXMID = ls_SFDM00; --挂号费金额,本金额只是挂号的金额,不含诊疗费部门的金额 else ls_OGHJE0 := 0 ; end if; update SF_BRXXB0 SET GHJE00 = ls_OGHJE0 where GHH000 = as_GHH000; if ls_GHDLX0 = '9' then select SQ_SF_BRJFB0_DJH000.nextval into ls_WJSDJH from dual; end if; --广东省中医院格式的挂号单,其医院要求将记账部分放在发票中一起打印,故强行将其拆分成多条 --添加病人病历工本费 if as_SFSBLF = 'Y' then --若有收病人病历本的工本费 --得到对应的记账比例，公费比例 begin select NVL((1-ZFBL00-JMBL00),0),NVL(JMBL00,0),NVL(XMJE00-SCZFJE,0),NVL(ZFJE00-SCZFJE,0),NVL(XMJE00-ZFJE00-JMJE00,0),NVL(JMJE00,0),NVL(SCZFJE,0) into ls_JZBL00,ls_GFBL00,ls_MXHJJE,ls_MXZFJE,ls_MXJZJE,ls_MXGFJE,ls_SCZFJE from VW_BM_YBSFDY where SFXMID = ls_BLSFDM and FBBH00 = ls_FBBH00 and YBZXLB = ls_YBZXLB and YBBRLB = ls_YBBRLB and SFYP00 = 'N' and ROWNUM = 1; --得到对应的记账比例，公费比例 exception when NO_DATA_FOUND then as_YHMSG0:='系统未查找到满足条件的病历工本费的自付比例、记账比例的记录!'; as_SYSMSG:=SQLERRM||'select * from VW_BM_YBSFDY where YBBRLB = '||ls_YBBRLB||' and YBZXLB='||ls_YBZXLB||' and SFXMID ='||to_char(ls_BLSFDM)||' and FBBH00 ='||to_char(ls_FBBH00)||' and SFYP00=N'; rollback; return; end; if ls_GHDLX0 = '9' then --广东省中医院格式的挂号单,其医院要求将记账部分放在发票中一起打印,故强行将其拆分成多条 --对于广东省中医院的挂号附加费的拆分规则的说明： --先提取VW_BM_YBSFDY中的自付金额，公费金额，先添加一条自付金额+公费金额，再按记账金额添加新记录 if ls_SCZFJE <> 0 then Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,YSKS00,SFYDJ0,ZFBL00) values(SQ_SF_FYMX00_MXID00.nextval,ls_DJH000,ls_BLSFDM,ls_BLXMMC,'','本',ls_SCZFJE,1,ls_SCZFJE,ls_SCZFJE,0,0, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,'1',1); end if; if ls_MXHJJE <> 0 then Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,YSKS00,SFYDJ0,ZFBL00) values(SQ_SF_FYMX00_MXID00.nextval,ls_WJSDJH,ls_BLSFDM,ls_BLXMMC,'','本',ls_MXHJJE,1,ls_MXHJJE,ls_MXZFJE,ls_MXGFJE,ls_MXJZJE, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,'1',(1-ls_JZBL00-ls_GFBL00)); end if; else Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,YSKS00,SFYDJ0,ZFBL00) values(SQ_SF_FYMX00_MXID00.nextval,ls_DJH000,ls_BLSFDM,ls_BLXMMC,'','本',ls_BLSFJE,1,ls_BLSFJE,ls_BLSFJE*(1-ls_JZBL00-ls_GFBL00),ls_BLSFJE*ls_GFBL00,ls_BLSFJE*ls_JZBL00, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,'1',(1-ls_JZBL00-ls_GFBL00)); end if; end if; begin select NVL(ICKH00,0) into ls_OLDKH0 from IC_YHXX00 where BRID00 = ls_BRID00 and ZT0000 = '1'; exception when NO_DATA_FOUND then ls_OLDKH0:='0'; end; --a.2收取卡费 if as_ICKH00 <> '0' and ls_ICKJE0 <> 0 then begin select NVL((1-ZFBL00-JMBL00),0),NVL(JMBL00,0),NVL(XMJE00-SCZFJE,0),NVL(ZFJE00-SCZFJE,0),NVL(XMJE00-ZFJE00-JMJE00,0),NVL(JMJE00,0),NVL(SCZFJE,0) into ls_JZBL00,ls_GFBL00,ls_MXHJJE,ls_MXZFJE,ls_MXJZJE,ls_MXGFJE,ls_SCZFJE from VW_BM_YBSFDY where SFXMID = ls_ICKDM0 and FBBH00 = ls_FBBH00 and YBZXLB = ls_YBZXLB and YBBRLB = ls_YBBRLB and SFYP00 = 'N' and ROWNUM = 1; --得到对应的记账比例，公费比例 exception when NO_DATA_FOUND then as_YHMSG0:='系统未查找到满足条件的就诊卡对应的自付比例、记账比例的记录!'; as_SYSMSG:=SQLERRM||'select * from VW_BM_YBSFDY where YBBRLB = '||ls_YBBRLB||' and YBZXLB='||ls_YBZXLB||' and SFXMID ='||to_char(ls_ICKDM0)||' and FBBH00 ='||to_char(ls_FBBH00)||' and SFYP00=N'; rollback; return; end; if ls_GHDLX0 = '9' then --广东省中医院格式的挂号单,其医院要求将记账部分放在发票中一起打印,故强行将其拆分成多条 --对于广东省中医院的挂号附加费的拆分规则的说明： --先提取VW_BM_YBSFDY中的自付金额，公费金额，先添加一条自付金额+公费金额，再按记账金额添加新记录 if ls_SCZFJE <> 0 then Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,YSKS00,SFYDJ0,ZFBL00) values(SQ_SF_FYMX00_MXID00.nextval,ls_DJH000,ls_ICKDM0,ls_ICKMC0,'',ls_ICKDW0,ls_SCZFJE,1,ls_SCZFJE,ls_SCZFJE,0,0, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,'1',1); end if; if ls_MXHJJE <> 0 then Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,YSKS00,SFYDJ0,ZFBL00) values(SQ_SF_FYMX00_MXID00.nextval,ls_WJSDJH,ls_ICKDM0,ls_ICKMC0,'',ls_ICKDW0,ls_MXHJJE,1,ls_MXHJJE,ls_MXZFJE,ls_MXGFJE,ls_MXJZJE, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,'1',(1-ls_JZBL00-ls_GFBL00)); end if; else Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,YSKS00,SFYDJ0,ZFBL00) values(SQ_SF_FYMX00_MXID00.nextval,ls_DJH000,ls_ICKDM0,ls_ICKMC0,'',ls_ICKDW0,ls_ICKJE0,1,ls_ICKJE0,ls_ICKJE0*(1-ls_JZBL00-ls_GFBL00),ls_ICKJE0*ls_GFBL00,ls_ICKJE0*ls_JZBL00, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,'1',(1-ls_JZBL00-ls_GFBL00)); end if; end if; if as_ICKH00 <> '0' then --再开始修改就诊卡的信息 update IC_YHXX00 set BRID00 = ls_BRID00 , ZT0000 = '1' where ICKH00 = as_ICKH00 and ZT0000 = '0'; Insert into IC_YWLS00(YWID00,ICKH00,YGBH00,YWCZ00,CZRQ00,CZSJ00,FSJE00,ICKNBH) values(SQ_IC_YWLS00_YWID00.nextval,as_ICKH00,ad_GHY000,'1',ls_CZRQ00,ls_CZSJ00,ls_ICKJE0,as_ICKH00); --特别补充:若原挂号有收取卡费,则在该处也要对卡费进行处理 if ls_OLDKH0 <> '0' then ls_ICKDM0:=0; ls_ICKJE0:=0; begin select NVL(SFDM00,0),NVL(ICKJE0,0) into ls_ICKDM0,ls_ICKJE0 from IC_YHXX00 where ICKH00 = ls_OLDKH0; if ls_ICKDM0 <> 0 then select trim(XMMC00), NVL(trim(DW0000),'次') into ls_ICKMC0,ls_ICKDW0 from BM_YYSFXM where SFXMID = ls_ICKDM0; end if; exception when OTHERS then as_YHMSG0:='系统无法或的该病人的旧的卡的信息!'; as_SYSMSG:=SQLERRM||'select * from IC_YHXX00 where ZT0000=0 and ICKH00 = '||ls_OLDKH0; rollback; return; end; --作废旧卡 if as_ICKH00 <> '0' then --若有分配新卡,则将旧卡作废 update IC_YHXX00 Set ZT0000 = '4' --无效卡 where ICKH00 = ls_OLDKH0 and ZT0000 <> '4'; Insert into IC_YWLS00(YWID00,ICKH00,YGBH00,YWCZ00,CZRQ00,CZSJ00,FSJE00,ICKNBH) values(SQ_IC_YWLS00_YWID00.nextval,ls_OLDKH0,ad_GHY000,'5',ls_CZRQ00,ls_CZSJ00,ls_ICKJE0,ad_GHY000); end if; --添加旧卡的费用信息 if ls_ICKJE0 <> 0 then begin select NVL((1-ZFBL00-JMBL00),0),NVL(JMBL00,0),NVL(XMJE00-SCZFJE,0),NVL(ZFJE00-SCZFJE,0),NVL(XMJE00-ZFJE00-JMJE00,0),NVL(JMJE00,0),NVL(SCZFJE,0) into ls_JZBL00,ls_GFBL00,ls_MXHJJE,ls_MXZFJE,ls_MXJZJE,ls_MXGFJE,ls_SCZFJE from VW_BM_YBSFDY where SFXMID = ls_ICKDM0 and FBBH00 = ls_FBBH00 and YBZXLB = ls_YBZXLB and YBBRLB = ls_YBBRLB and SFYP00 = 'N' and ROWNUM = 1; --得到对应的记账比例，公费比例 exception when NO_DATA_FOUND then as_YHMSG0:='系统未查找到满足条件的就诊卡对应的自付比例、记账比例的记录!'; as_SYSMSG:=SQLERRM||'select * from VW_BM_YBSFDY where YBBRLB = '||ls_YBBRLB||' and YBZXLB='||ls_YBZXLB||' and SFXMID ='||to_char(ls_ICKDM0)||' and FBBH00 ='||to_char(ls_FBBH00)||' and SFYP00=N'; rollback; return; end; if ls_GHDLX0 = '9' then --广东省中医院格式的挂号单,其医院要求将记账部分放在发票中一起打印,故强行将其拆分成多条 if ls_SCZFJE <> 0 then Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,YSKS00,SFYDJ0,ZFBL00) values(SQ_SF_FYMX00_MXID00.nextval,ls_DJH000,ls_ICKDM0,ls_ICKMC0,'',ls_ICKDW0,ls_SCZFJE,1,ls_SCZFJE,ls_SCZFJE,0,0, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,'1',1); end if; if ls_MXHJJE <> 0 then Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,YSKS00,SFYDJ0,ZFBL00) values(SQ_SF_FYMX00_MXID00.nextval,ls_WJSDJH,ls_ICKDM0,ls_ICKMC0,'',ls_ICKDW0,ls_MXHJJE,1,ls_MXHJJE,ls_MXZFJE,ls_MXGFJE,ls_MXJZJE, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,'1',(1-ls_JZBL00-ls_GFBL00)); end if; else Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,YSKS00,SFYDJ0,ZFBL00) values(SQ_SF_FYMX00_MXID00.nextval,ls_DJH000,ls_ICKDM0,ls_ICKMC0,'',ls_ICKDW0,ls_ICKJE0,1,ls_ICKJE0,ls_ICKJE0*(1-ls_JZBL00-ls_GFBL00),ls_ICKJE0*ls_GFBL00,ls_ICKJE0*ls_JZBL00, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,'1',(1-ls_JZBL00-ls_GFBL00)); end if; --if ls_GHDLX0 = '9' end if; --end of if ls_ICKJE0 <> 0 then end if; end if; --3.2、挂号费附加费的添加 select count(*) into ls_COUNT0 from SF_FJGHF0 where FJBRLB = as_SFCZ00 and FJGHLB = ls_GHLB00 and FJGHKS = ad_GHKS00; if ls_COUNT0 = 0 then ls_TMPKS0 := ad_GHKS00; end if; if ls_COUNT0 > 0 then ls_TMPKS0 := -1; end if; --按病人类别、挂号类别、挂号科室来算附加挂号费 ls_FJGHKS:=ad_GHKS00; --游标的挂号科室赋值 OPEN CUR_SF_FJGHF0_GHF000; LOOP FETCH CUR_SF_FJGHF0_GHF000 INTO ls_FJFFBH; EXIT when CUR_SF_FJGHF0_GHF000%NOTFOUND; begin select substrb(VALUE0,1,14) into ls_yhdate_1 from XT_XTCS00 WHERE NAME00='YJ_HHSJD'; select substrb(VALUE0,16,14) into ls_yhdate_2 from XT_XTCS00 WHERE NAME00='YJ_HHSJD'; select nvl(yhbl00,1) into ls_yhbl00 from BM_YYSFXM A,BM_YYHSXM B where A.hsxmid=B.hsxmid and A.SFXMID =ls_FJFFBH and to_char(sysdate,'YY.MM.DD HH24:MI') between ls_yhdate_1 and ls_yhdate_2; select NVL(ls_yhbl00,1) into ls_yhbl00 from dual; exception when others then ls_yhbl00:=1; end; begin select NVL((1-ZFBL00-JMBL00),0),NVL(JMBL00,0),NVL(XMJE00*ls_yhbl00-SCZFJE*ls_yhbl00,0),NVL(ZFJE00*ls_yhbl00-SCZFJE*ls_yhbl00,0),NVL(XMJE00*ls_yhbl00-ZFJE00*ls_yhbl00-JMJE00*ls_yhbl00,0),NVL(JMJE00*ls_yhbl00,0),NVL(SCZFJE*ls_yhbl00,0) into ls_JZBL00,ls_GFBL00,ls_MXHJJE,ls_MXZFJE,ls_MXJZJE,ls_MXGFJE,ls_SCZFJE from VW_BM_YBSFDY where SFXMID = ls_FJFFBH and FBBH00 = ls_FBBH00 and YBZXLB = ls_YBZXLB and YBBRLB = ls_YBBRLB and SFYP00 = 'N' and ROWNUM = 1; --得到对应的记账比例，公费比例 exception when NO_DATA_FOUND then as_YHMSG0:='系统未查找到满足条件的挂号费的自付比例、记账比例的记录!'; as_SYSMSG:=SQLERRM||'select * from VW_BM_YBSFDY where YBBRLB = '||ls_YBBRLB||' and YBZXLB='||ls_YBZXLB||' and SFXMID ='||to_char(ls_FJFFBH)||' and FBBH00 ='||to_char(ls_FBBH00)||' and SFYP00=N'; rollback; return; end; if ls_GHDLX0 = '9' then --广东省中医院格式的挂号单,其医院要求将记账部分放在发票中一起打印,故强行将其拆分成多条 --对于广东省中医院的挂号附加费的拆分规则的说明： --先提取VW_BM_YBSFDY中的自付金额，公费金额，先添加一条自付金额+公费金额，再按记账金额添加新记录 if ls_SCZFJE <> 0 then Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,YSKS00,SFYDJ0,ZFBL00) select SQ_SF_FYMX00_MXID00.nextval,ls_DJH000,SFXMID,XMMC00,'',DW0000,ls_SCZFJE,1,ls_SCZFJE,ls_SCZFJE,0,0, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,'1',1 from BM_YYSFXM where SFXMID = ls_FJFFBH; end if; if ls_MXHJJE <> 0 then Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,YSKS00,SFYDJ0,ZFBL00) select SQ_SF_FYMX00_MXID00.nextval,ls_WJSDJH,SFXMID,XMMC00,'',DW0000,ls_MXHJJE,1,ls_MXHJJE,ls_MXZFJE,ls_MXGFJE,ls_MXJZJE, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,'1',(1-ls_JZBL00-ls_GFBL00) from BM_YYSFXM where SFXMID = ls_FJFFBH; end if; --end of ls_JZBL00 <> 0 else insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,YSKS00,SFYDJ0,ZFBL00) select SQ_SF_FYMX00_MXID00.nextval,ls_DJH000,SFXMID,XMMC00,'',DW0000,SFJE00,1,SFJE00, SFJE00*(1-ls_JZBL00-ls_GFBL00),SFJE00*ls_GFBL00,SFJE00*ls_JZBL00, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00, ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,'1',(1-ls_JZBL00-ls_GFBL00) from VW_SF_YYSFXM where SFXMID = ls_FJFFBH and FBBH00 = ls_FBBH00 and YBZXLB = ls_YBZXLB and YBBRLB = ls_YBBRLB and ROWNUM = 1; end if; --end of ls_GHDLX0='9' end LOOP; CLOSE CUR_SF_FJGHF0_GHF000; --3.3、填写病人相应的费用表的信息 if ad_GHHBID > 0 then begin --医生挂号号表的内容的修改 select (GHXHS0 - GHYGHS) into ls_GHHS00 from SF_YSGHHB where GHHBID = ad_GHHBID; if NVL(ls_GHHS00,0) < 2 then as_YHMSG0:='您所选择的医生的挂号号表的挂号号数已用完,请选择新的医生来挂号!'; as_SYSMSG:=SQLERRM; rollback; return; end if; --添加该医生的挂号号数 update SF_YSGHHB SET GHYGHS=NVL(GHYGHS,0)+1 where GHHBID = ad_GHHBID; exception when NO_DATA_FOUND then as_YHMSG0:='挂号号表中未找到该医生的号表信息，请核对您所输入的医生是否正确!'; as_SYSMSG:=SQLERRM; rollback; return; end; end if; if ls_JFLBID = 0 then --若病人是现金病人,则要将其所有的费用进行结算 select SQ_SF_JZB000_JZDH00.nextval into ls_JZDH00_NEW000 from dual; else ls_JZDH00_NEW000:=0; end if; begin select NVL(sum(HJJE00),0),NVL(sum(ZFJE00),0),NVL(sum(JZJE00),0),NVL(sum(GFJE00),0) into ls_MXHJJE,ls_MXZFJE,ls_MXJZJE,ls_MXGFJE from SF_FYMX00 where DJH000 = ls_DJH000; exception when NO_DATA_FOUND then ls_MXHJJE:=0; ls_MXZFJE:=0; ls_MXJZJE:=0; ls_MXGFJE:=0; end; if ls_MXHJJE <> 0 then 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,as_GHH000,ls_MZID00,ls_JFLBID,ls_BRID00,as_XM0000,ls_FBBH00, ls_MXHJJE,ls_MXZFJE,ls_MXJZJE,ls_MXGFJE,0,ls_CZRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,ad_CZYKS0,ls_JZDH00_NEW000); end if; if ls_GHDLX0 = '9' then --若是广东省中医院，还要进行挂号 select NVL(sum(HJJE00),0),NVL(sum(ZFJE00),0),NVL(sum(JZJE00),0),NVL(sum(GFJE00),0) into ls_MXHJJE,ls_MXZFJE,ls_MXJZJE,ls_MXGFJE from SF_FYMX00 where DJH000 = ls_WJSDJH; if ls_MXHJJE <> 0 then insert into SF_BRFY00(DJH000,MZH000,MZID00,JFLBID,BRID00,XM0000,BRFB00, HJJE00,ZFJE00,JZJE00,GFJE00,BRYE00,CZRQ00,CZSJ00,CZY000,CZYXM0,CZYKS0,JZDH00) values(ls_WJSDJH,as_GHH000,ls_MZID00,ls_JFLBID,ls_BRID00,as_XM0000,ls_FBBH00, ls_MXHJJE,ls_MXZFJE,ls_MXJZJE,ls_MXGFJE,0,ls_CZRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,ad_CZYKS0,0); end if; end if; if ls_JFLBID = 0 then --说明该病人为现金病人,添加一条新的交费记录,同时添加结账的信息 Insert into SF_JZB000(JZDH00,BRID00,MZID00,BRZJE0,JZJE00,GFJE00,HJJE00,JMJE00,GRZHZF,TCJJZF,ZFJE00,PJH000,JZRQ00,JZSJ00,XJFKJE,ZZFKJE,JZY000) select ls_JZDH00_NEW000,ls_BRID00,ls_MZID00,HJJE00,JZJE00,GFJE00,HJJE00,0,0,0,ZFJE00,ls_PJH000,ls_CZRQ00,ls_CZSJ00,ZFJE00,0,ad_GHY000 from SF_BRFY00 where DJH000 = ls_DJH000; --挂号费的费用结算,对应的票据是挂号单 if (ls_GFAZFGH='Y') then update SF_JZB000 SET FBBH00 = ls_FBBH00,YBLB00 = ls_yblb00,YBBRLB = ls_YBBRLB where JZDH00 = ls_JZDH00_NEW000; end if; --Insert into SF_JZMX00 Insert into SF_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00,ZFBL00) select ls_JZDH00_NEW000,B.FPXMID,B.XMMC00,sum(A.HJJE00),A.ZFBL00 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 = ls_JZDH00_NEW000 group by B.FPXMID,B.XMMC00,A.ZFBL00; --添加挂号费的现金交费金额 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select SQ_SF_BRJFB0_DJH000.nextval,ls_BRID00,ls_JFLBID,ZFJE00,ls_ZFFSBH,ls_ZFFSMC,ls_PJH000, --对应挂号单的票据号 ls_CZRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ls_JZDH00_NEW000,1,ZFJE00 from SF_BRFY00 where DJH000 = ls_DJH000; --添加挂号费用的记账金额 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select SQ_SF_BRJFB0_DJH000.nextval,ls_BRID00,ls_JFLBID,JZJE00,ls_ZFFSBH_2,ls_ZFFSMC_2,ls_PJH000, --对应挂号单的票据号 ls_CZRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ls_JZDH00_NEW000,1,JZJE00 from SF_BRFY00 where DJH000 = ls_DJH000; end if; --票据使用情况表,打印的是修改后的新挂号信息,冲销记录不打印挂号单 --if ls_JFLBID <> 0 then --有预交金账户的病人 -- ls_PJXJJE := 0; --票据上的现金金额 -- ls_PJZZJE := ad_ZFJE00; --转账金额，也可称为从病人预交金账户上扣除的金额 -- else -- ls_PJXJJE := ad_ZFJE00; --票据上的现金金额 -- ls_PJZZJE := 0; --转账金额，也可称为从病人预交金账户上扣除的金额 --end if; begin select SUM(ZFJE00), SUM(JZJE00+GFJE00),SUM(JZJE00) into ls_PJXJJE,ls_PJZZJE,ls_PJJZJE from SF_FYMX00 where DJH000 = ls_DJH000; --票据上的现金金额 MODIFY BY XUZW exception when NO_DATA_FOUND then ls_PJXJJE:=0; ls_PJZZJE:=0; ls_PJJZJE:=0; end; Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,JZJE00) select ls_PJH000,PLBH00,PLMC00,as_YJJXH0,NVL(ls_PJXJJE,0),NVL(ls_PJZZJE,0),ad_GHY000,as_GHYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',as_XM0000,as_XB0000,ls_BRID00,NVL(ls_PJJZJE,0) from bm_pjlxb0 where PLBH00 = 0;--PLBH00=0是挂号单 --MODIFY BY XUZW if SQL%NOTFOUND then as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败!'; as_SYSMSG:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 0)'; rollback; return; end if; --添加票据使用明细表的信息 --Insert into SF_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,XMBZ00) -- select ls_PJH000,XMBH00,XMMC00,XMJE00,0 from SF_JZMX00 where JZDH00 = ls_JZDH00; --补充: 若是挂号修改,若操作员与原来的操作员相同,同时是未进行过缴款登记,则将旧的票据进行作废登记,否则,做为回收标志 if ad_GHY000 <> ls_PJSYQK_OLDCZY or ls_PJSYQK_JKDID0 <> 0 then update SF_PJSYQK set PJZT00 = '2',HSCZY0 =ad_GHY000, HSCZXM =as_GHYXM0,HSRQ00 =ls_CZRQ00,HSSJ00 =ls_CZSJ00,OLDPJH = as_GHH000 ,NEWPJH = ls_PJH000 where PJH000 = ls_OLDPJH; else update SF_PJSYQK set PJZT00 = '1',HSCZY0 =ad_GHY000, HSCZXM =as_GHYXM0,HSRQ00 =ls_CZRQ00,HSSJ00 =ls_CZSJ00,OLDPJH = as_GHH000 ,NEWPJH = ls_PJH000 where PJH000 = ls_OLDPJH; end if; end if; --4.挂号修改，挂号退号都要操作的部分 update SF_BRXXB0 set PJH000 = nvl(ls_PJH000,0), THPJH0 = ls_OLDPJH where GHH000 = as_GHH000; --COMMIT,若是医保病人,还应该调用医保补充的存储过程 exception when OTHERS then as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_BRGHXX_EDIT00('||as_GHH000||','||as_YBGHH0||','|| as_BRBLH0||','||as_GHLBMC||','||as_SFCZ00||','||as_XM0000||','||as_XB0000||','|| as_CSRQ00||','||as_FBMC00||','||to_char(ad_GHY000)||','||as_GHYXM0||','||to_char(ad_CZYKS0)||','|| to_char(ad_GHKS00)||','||to_char(ad_JZYS00)||','||as_JZYSXM||','||to_char(ad_GHHBID)||','||as_YJJXH0||','|| to_char(ad_GHF000)||','||to_char(ad_GRZHZF)||','||to_char(ad_TCJJZF)||','||to_char(ad_ZFJE00)||','|| as_ZPZH00||','||as_CZBZ00||','||as_SFSBLF||',ad_ZFDJH0,ad_TJDJH0,ad_GRDJH0,as_YHMSG0,as_SYSMSG)',1,150); rollback; end; / show error; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%