procedure SP_SF_BRGHXX ( as_BRBLH0 in char, --门诊病历号 as_YBGHH0 in char, --医保程序生成的挂号流水号,默认值为0 as_GHLBMC in char, --挂号类别名 ad_BRID00 in number, --病人ID号,若=0,说明是现金病人 as_SFCZ00 in char, --是否初诊,'0':初诊病人,'1':复诊病人 as_XM0000 in char, --姓名 as_XB0000 in char, --性别 as_CSRQ00 in char, --出生日期a as_FBMC00 in char, --病人费别 as_GFZH00 in char, --公费证号,Add at GZ, on 2002-12-28 as_BRDH00 in char, --电话,Add at GZ,on 2002-12-31 as_BRHYZK in char, --婚姻状况,Add at GZ, on 2002-12-31 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, --打印在挂号单的外部序列号 as_FPXH00 in char, --打印在发票上的序号 ad_GHF000 in number, --挂号费总金额(挂号费+挂号附加费)+病历工本费(若在窗体有打勾收病历费) ad_GRZHZF in number, --医保个人医疗账户支付 ad_TCJJZF in number, --医保统筹基金支付 ad_ZFJE00 in number, --医保自付金额,若是现金病人，与挂号费总金额相同 as_ZPZH00 in char, --对应的医保中心的编号 as_YBID00 in char, --病人医疗保险号(医保Id),非医保病人='0' as_YBKH00 in char, --病人医保卡号 as_GHRQ00 in char, --挂号日期,只是在预约挂号时使用 as_SFYYGH in char, --是否预约挂号,'Y':预约挂号,'N':正常挂号 as_CZBZ00 in char, --挂号标志,'0':持卡病人,'1':现金病人 as_SFSBLF in char, --是否收取病人的病历费,'Y':收病历费,'N':不收病历费 as_BRZY00 in char, --职业(考虑现金病人时,这些信息的填写) as_BRMZ00 in char, --民族 as_BRZJLX in char, --证件类型 as_BRZJBH in char, --证件编号 as_TYDWMC in char, --单位名称 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_BRJTDZ in varchar2, --家庭地址/通信地址 ad_FJJZDH out number, --除了挂号费外的附加费的结算单号(供医保接口使用) --NEW! ad_ZFDJH0 out number, --自费金额对应的单据号 ad_TJDJH0 out number, --医保的统筹基金对应的单据号 ad_GRDJH0 out number, --医保的个人账户对应的单据号 ad_SBZHDH out number, --商保个人帐户单据流水号 ad_SBTJDH out number, --商保统筹基金单据流水号 as_GHH000 in out varchar2, --门诊挂号号,供前台程序打印挂号单时使用 as_YHMSG0 out varchar, --存储过程提示的中文错误信息 as_SYSMSG out varchar, --系统提示的错误信息 as_JZSJ00 in char default '', --计划就诊时间 as_GHXZQK in char default 'N',--挂号限制情况 'Y':预约挂号即广东的那种情况,'N':正常挂号 'Z' :预约转正式挂号 as_ybzhye in number default 0, --医保账户余额 as_SFHSGH in char default 'N' ,--是否护士站挂号(医技系统挂号员和结诊医生不同时也采用这种方式) 默认为N ad_jjdjh0 in number default 0 , --保健基金单据号 as_GHSJD0 in varchar2 default '', --预约时间段 as_HZXH00 in number default 0, --候诊时间 as_ZSQGFF in char default 'N', --是否只收取挂号费 as_YBYWLX in varchar2 default '', --医保业务类型 as_YBDYLB in varchar2 default '', --医保待遇类别 as_JYLSH0 in varchar2 default '' --交易流水号 ) as ls_YGHGHH SF_BRXXB0.GHH000%TYPE; --病人的预挂号的挂号号 ls_YGHID0 SF_BRXXB0.GHID00%TYPE; -- 病人的预挂号的挂号id ls_YGHDJH SF_BRXXB0.DJH000%TYPE; --病人预挂号对应的收费单据号 ls_MZID00 SF_BRXXB0.GHID00%TYPE; --病人的挂号ID ls_DJH000 SF_BRFY00.DJH000%TYPE; --病人费用单据号 ls_FJDJH0 SF_BRFY00.DJH000%TYPE; --病人费用单据号 ls_WJSDJH SF_BRFY00.DJH000%TYPE; --广东省中医院在挂号时，虽是现金，但有部分的费用放在结算时，一起结算 ls_GHLB00 BM_GHLBB0.LBBH00%TYPE; --挂号类别编码 ls_GHLB01 BM_GHLBB0.LBBH00%TYPE; --挂号类别编码 ls_FBBH00 BM_BRFBB0.FBBH00%TYPE; --病人费别名称 ls_LBBH00 BM_BRJFLB.JFLBID%TYPE; --病人交费类别 ls_GFBL00 NUMBER(12,2); --公费报销比例 ls_JZBL00 NUMBER(12,2); --记账报销比例 ls_SCZFJE BM_YBSFDY.ZFJE00%TYPE; --首次自付金额,由于挂号时,广东省的收费是分两次进行的 ls_YBBRLB BM_BRXXB0.YBBRLB%TYPE; --病人类别 ls_YBZXLB IC_YBBRLB.YBZXLB%TYPE; --医保中心类别 ls_TMPJE0 SF_BRFY00.ZFJE00%TYPE; --自费金额,临时保存 ls_TMPJZ0 SF_BRFY00.JZJE00%TYPE; --记账金额,临时保存 ls_JZJE00 SF_BRFY00.JZJE00%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; --挂号费的收费代码 vSFXMID SF_GHF000.SFXMID%TYPE; --预约费的收费代码 ls_GHHZCK SF_YSGHHB.GHHZCK%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_JZDH00 SF_JZB000.JZDH00%TYPE; --病人结账单号 ls_GHRQ00 SF_BRXXB0.GHRQ00%TYPE; --挂号日期 ls_BRID00 SF_BRXXB0.BRID00%TYPE; --病人ID ls_PJH000 SF_PJSYQK.PJH000%TYPE; --票据流水号 ls_FJPJH0 SF_PJSYQK.PJH000%TYPE; --附加费对应的票据流水号 ls_PJXJJE SF_PJSYQK.XJJE00%TYPE; --票据上的现金金额 ls_PJZZJE SF_PJSYQK.ZZJE00%TYPE; --转账金额，也可称为从病人预交金账户上扣除的金额 ls_YSKS00 SF_FYMX00.YSKS00%TYPE; --医生科室名称 ls_SFYDJ0 SF_FYMX00.SFYDJ0%TYPE; --是否是收费处登记 ls_BLSFDM BM_YYSFXM.SFXMID%TYPE; --病历工本费收费代码 ls_BLSFJE BM_YYSFXM.SFJE00%TYPE; --病历工本费金额 ls_BLXMMC BM_YYSFXM.XMMC00%TYPE; --病历工本费收费名称 ls_OGHJE0 BM_YYSFXM.SFJE00%TYPE; --挂号费金额,本金额只是挂号的金额,不含诊疗费部门的金额 ls_GHYXTS SF_BRXXB0.GHYXTS%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_ICKDM0 IC_YHXX00.SFDM00%TYPE; --就诊卡收费代码 ls_ICKJE0 IC_YHXX00.ICKJE0%TYPE; --就诊卡收费金额 ls_ICKMC0 SF_FYMX00.XMMC00%TYPE; --就诊卡收费项目名称 ls_ICKDW0 SF_FYMX00.XMDW00%TYPE; --就诊卡收费项目单位 ls_HZQSRQ SF_HZBR00.HZQSRQ%TYPE; --候诊开始日期 ls_HZQSSJ SF_HZBR00.HZQSSJ%TYPE; ls_HZJZRQ SF_HZBR00.HZJZRQ%TYPE; --候诊结束日期 ls_HZJZSJ SF_HZBR00.HZJZSJ%TYPE; ls_xzghlb bm_ybbrlb.xzghlb%type; ls_xzghlb_byzg bm_ghlbb0.lbbh00%type; --本院职工的限制挂号类别 ls_tydw00 bm_brxxb0.tydwmc%type; ls_brfb00 bm_brxxb0.fbbh00%type; --ls_YBSFYXQF XT_XTCS00.value0%type; --取医保个人帐户余额作为允许欠费金额(建阳模式) ls_yhbl00 bm_yyhsxm.yhbl00%type; ls_tydwid bm_brxxb0.tydwid%type; YS_DETJZMSGHF CHAR(1); --参数 ls_SFSQGHF CHAR(1); --是否收取挂号费 默认为 ：1 ls_YBGHFJF CHAR(1); ls_YXCFGH CHAR(1); --允许医生有效期内给病人重复挂号 ls_CZRQ00 CHAR(8); ls_CZRQZH CHAR(8); --允许提前挂义诊号等免费号 ls_CZSJ00 CHAR(8); ls_COUNT0 NUMBER(5); ls_COUNT2 NUMBER(5); V_COUNT NUMBER(5); ls_GHDLX0 VARCHAR2(30); ls_GHDJXZ CHAR(1); SF_YJJGHZXJ CHAR(5); ls_gfxydw char(1); ls_yhdate_1 char(14); ls_yhdate_2 char(14); ls_BZ0000 BM_BRXXB0.BZ0000%type; ls_ghlbbh char(8); ls_GHXMBH number(8); ls_GHXMBH_1 number(8); ls_sfje00 bm_yysfxm.sfje00%type; ls_xmmc00_1 bm_yysfxm.xmmc00%type; ls_GHZKBH sf_ysghhb.GHZKBH%type; --专科名称编号 ls_HZXH00 VARCHAR2(5); ls_DLXH00 SF_HZBR00.DLXH00%type; ls_HZXHLB CHAR(1); ls_XZHZXH CHAR(1); ls_YYBZ00 CHAR(1); ls_GHXZQK CHAR(1); ls_GFAZFGH XT_XTCS00.value0%type; --公费病人是否可以按自费挂号 ls_SFLNYD char(1); --老年优待 Vbrcsrq BM_BRXXB0.BRCSRQ%TYPE; --病人出生日期 Vbrzjbh BM_BRXXB0.BRZJBH%TYPE; --病人证件编号 Vsfyd00 BM_BRXXB0.SFYD00%TYPE; --是否优待 Vzdydfb varchar2(50); --自动优待费别 Vlnzdyd char(1); --老年自动优待 ls_YHMSG0 varchar2(200); ls_SYSMSG varchar2(200); ls_YyCount NUMBER(5); ls_COUNT1 NUMBER(5); ls_kyje00 NUMBER(12); ls_ghfzje NUMBER(5); --挂号费总金额 ls_ghxjje NUMBER(5); --挂号现金金额 Vghksbh NUMBER(5); Vghlbbh NUMBER(5); ls_CZBZ00 char(1); --挂号标志,'0':持卡病人,'1':现金病人 LS_YYID00 SF_YYBRB0.YYID00%TYPE; --预约id ls_ZSXX00 SF_BRXXB0.ZSXX00%TYPE; --诊室信息 ls_ZYE000 SF_BRZHXX.ZYE000%TYPE; --帐户总余额 ls_SFDTYY SF_HZBR00.SFDTYY%TYPE; --是否当天预约,'1'当天预约 ls_CKBH00 SF_YSGHHB.GHHZCK%type; --分诊窗口编号 ls_YGXZ00 BM_YGBM00.YGXZ00%TYPE; ls_FZCOUNT number(3); --分诊计数台 ls_SFXZNXYBBRGFKH XT_XTCS00.VALUE0%TYPE; --参数是否限制男性医保病人挂妇科号 by MZYS-20110223-002 ls_GHDCZL XT_XTCS00.VALUE0%TYPE; --挂号带出诊疗 ls_GHKSMC BM_BMBM00.BMMC00%TYPE; --挂号科室名称 ls_ZSQGHF char(1); --是否只收取挂号费。 Y 是N 否 ls_SFLNYX varchar(1); --是否大于80的挂优先号 ls_XSTYPBBYXGTYYS XT_XTCS00.VALUE0%type; ls_tmpGHLB BM_GHLBB0.LBBH00%type; --预约类别 ls_SF_YYBRB0_YYLB00 SF_YYBRB0.YYLB00%TYPE; --预约备注 ls_SF_YYBRB0_BZ0000 SF_YYBRB0.BZ0000%TYPE; --是否网络预约 linzy 2014.08.21 用于处理挂号费分单 b_SFWLYY number(1); --指定科室启用候诊队列新算法 ls_MZHS_HZBRSFXYXDL XT_XTCS00.VALUE0%TYPE; --候诊状态 ls_HZZT00 char(1); ls_SF_GHY000_XKH000 BM_YGBM00.XKH000%TYPE; -- ls_SF_YDZFCZY XT_XTCS00.VALUE0%TYPE; ls_BDKS00_TYZD BM_TYZD00.MC0000%type; ls_sfzj00 ys_hbhzxh.sfzj00%type; ls_brcsrq bm_brxxb0.brcsrq%type; ls_countcs NUMBER(5); --进行挂号登记限制 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)); CURSOR CUR_BM_GHLBB0_MC IS select LBMC00 from BM_GHLBB0 where instr(','||LS_XZGHLB||',',','||LBBH00||',')>0; --挂号带出诊疗项目 --ls_count0>0说明是邵武医院,则按医生的职称类别带出诊疗项目，否则直接按选择的挂号类别带出诊疗项目 CURSOR CUR_BM_GHXMDY_ZLXMID IS select ZLXMID from BM_GHXMDY WHERE ((ls_count0>0 and LBBH00=(select MRGHLB from BM_YGBM00 where YGBH00=ad_jzys00)) or (LBBH00=ls_GHLB00 and ls_count0=0)) and SYBZ00='1' ORDER BY LBBH00,XMDYID; CURSOR CUR_GHDCZL IS select YJDJH0 from YJ_YW0000 where ZYGHID=ls_MZID00 and MZZYBZ='0' and XMZT00 IN ('0'); --电子钱包定义的诊查费 CURSOR CUR_BM_GHXMDY_ZLXMID_2 IS select ZLXMID from BM_GHXMDY WHERE LBBH00=ls_GHLB00 and SYBZ00='2'; cursor C_YHH000 is select a.SFNLYD,b.HZLBMC,a.GHH000,a.YBGHH0,a.BRBLH0,c.LBMC00,a.SFCZ00,a.XM0000,a.XB0000,a.CSRQ00,d.FBMC00, a.GHKS00,a.JZYS00,a.JZYSXM,a.GHJE00,a.YBZXLB from SF_BRXXB0 a,BM_HZLB00 b,BM_GHLBB0 c,BM_BRFBB0 d where a.GHH000=ls_YGHGHH and a.HZLB00 = b.HZLB00(+) and a.GHLB00 = c.LBBH00(+) and a.FBBH00 = d.FBBH00; --2007.07.13 zhr 邵武医院对医保病人统一挂“医保号”(GHLB00=22)，要求系统自动根据医生技术职称类别带出相应的诊疗项目，其他医院直接按医生选择的挂号类别带出相应的诊疗项目 -- MODIFICATION HISTORY -- Person Date Comments -- xzw 2007.06.15 传递参数增加基金单据号; -- yangy 2007.07.03 在门诊护士挂号时，虽然挂号员和就诊医生不同，但是也不需要分配票据号; -- xzw 2007.07.04 解决挂号的时候附加费信息在SF_PJSYQK中没有记录的问题; -- jlg 2007.07.11 修改游标CUR_BM_GHXMDY_ZLXMID,使取前台传入的挂号类别 -- zhr 2007.07.13 邵武医院对医保病人统一挂“医保号”(GHLB00=22)，要求系统自动根据医生技术职称类别带出相应的诊疗项目，其他医院直接按医生选择的挂号类别带出相应的诊疗项目 -- xzw 2007.07.23 预约时传递参数增加预约时间段 -- yangy 2007.08.08 省二候诊病人候诊序号跳号和变号的问题 -- yangy 2007.08.13 省二候诊病人预约病人预挂号转为正式挂号 -- yangy 2007.08.13 省二候诊病人优待病人预挂号转为正式挂号 -- xzw 2007.08.14 特困病人挂号时,挂“简易医生”时费用1元照收。 -- xzw 2007.08.15 残疾军人挂号时：若挂专家或名专家号则费用照收 -- yangy 2007.08.15 护士站挂号时，可以选择候诊序号进行挂号 -- xzw 2007.08.20 对省公医及市公医病人挂急诊号时对诊金的处理 -- yangy 2007.08.22 挂预挂号时，SF_YSGHHB.GHYGHS不变,挂预约号时，GHYYHS要增加 -- yangy 2007.09.05 补挂号病人的DLXH00不变 -- yangy 2007.09.14 预挂号不加入重复挂号判定。 -- xzw 2007.09.21 省公医,市公医挂急诊号对诊金进行减免的时候比例要跟原来一样 -- zhr 2008.01.03 预约病人进行提前挂号，如果预约时间之前的候诊段有余号，会被自动转为普通病人。 -- zhr 2008.03.19 收费处不能跨分院挂号。 -- jlg 2008.04.28 公费病人选择自费费别挂号(广东医院)MZSF-20080428-002 -- qks 2008.07.11 自费病人选择公费费别挂号(广东医院)MZSF-20080711-001 -- qks 2008.08.04 公费病人选择公费费别挂号(广东医院)MZSF-20080804-006 -- qks 2009.02.25 市公医病人挂无诊金的号别，要收3元的普通门诊诊查费(广东医院)MZSF-20090225-003 2009.03.06取消 -- qks 2009.03.10 公医病人在护士工作站挂免费号成功后，在病人的基本信息里，病人的公医证号丢失了 MZHS-20090309-002 -- zhr 2009.04.14 70岁以上的老人和两费病人自动优待 -- csf 2009.06.11 当参数YS_HSGHXZHZXH为Y，也就是护士可以指定候诊序号的预约挂号模式，病人如果直接到医生处读卡挂号则无法进入候诊队列现纠正 -- xzw 2009.08.03 福州市长乐医院：在门诊收费处挂一次退休县处级号(sf_brxxb0.ghlb00=27)后，那么在医生工作站挂号都免收挂号费，诊疗费要; --在门诊收费处挂一次离休干部号(sf_brxxb0.ghlb00=23)后，那么在医生工作站挂号都免收挂号费，诊疗费要正常收; -- chenqw 2009.08.10 修改了参数as_SFHSGH备注 -- csf 2009.09.08 预约挂号收费 -- csf 2009.09.25 当下午排班的医生，上午就诊时候，也会传入 ad_GHHBID,此时直接在门诊医生站读卡就诊的病人 必须重新判断号表的就诊时间是否包括当前时间方可取号 -- jxq 2009.12.02 本院职工是否可挂体检号，省老年 -- csf 2009.12.11 科室排班预挂号处理医生叫号队列。 -- ZHR 2010.04.22 医保账户余额不再写入YXQFJE,写入YBZHYE -- zhr 2010.05.24 增加填写GHHBID号表ID,ZSXX00诊室信息 -- zhr 2010.06.03 SF_BRZHXX.YBZHYE非空，所以as_ybzhye应该要进行非空处理 -- qks 2010.07.14 增加传入参数as_ZSQGFF,是否只收取挂号费,不收诊察费,同时根据挂号类别带出相应的诊疗项目(CUR_BM_GHXMDY_ZLXMID_2) MZYS-20100712-002 -- qks 2010.07.23 CUR_BM_GHXMDY_ZLXMID_2 执行科室由sf_ys_xmzxks(zl.ZLXMID,ad_GHKS00)改用ad_GHKS00 -- liuj 2010.09.06 增加限制病人余额不足不让预挂号，由参数SF_ZHYEBZSFYXYYGH决定是否启用该功能。 -- csf 2010.09.23 预挂号先扣挂号费,转正式挂号再扣诊查费。 -- csf 2010.10.12 预挂号若有转正式挂号，那么原预挂号的费用要转到正式挂号下。 -- csf 2010.10.15 预挂号若有转正式挂号, 在参数SF_YJJGHZXJ=N的时候不能，没有将原预挂号的费用要转到正式挂号下现纠正。 -- qks 2010.10.24 广东省中医院 现金挂号时是否使用预交金支付 -- zhr 2010.10.30 挂号前，修正YS_HBHZXH表的数据 -- qks 2010.11.03 广东自助取号 -- qks 2011.01.12 广东as_CZBZ00值传人有误,所有条件判断改用变量ls_CZBZ00,广东值改为'1' -- qks 2011.02.25 广东省中医院：当参数YS_HSGHXZHZXH=J时,as_HZXH00=SF_YYBRB0.YYID00 -- csf 2011.03.02 市皮要求，冲销挂号同时也要冲销原预挂号（原来要求不允许退预挂号费）的费用所以改变预挂号转正式挂号算法，改为（先冲销原预挂号，再进行正式挂号） -- chenyw 2011.03.15 区分分诊台收费 -- csf 2011.03.15 长乐市医院原有功能(见此次修改内容-- xzw 2009.08.03),现要求停止部分病人的优惠处理， -- 现让医院把要停用的病人病历号描述到通用字典“处级干部优惠病人停用名单”的备注字段。 -- csf 2011.03.18 用通用字典，字典名称为“按病人类别定义预约费优惠比例”来定义病人的优惠比例， -- 备注描述成FBBH00-YBZXLB-YBBRLB,DJ0000为优惠比例0-1之间 0为不优惠 1为100%优惠 -- zhangwz 2011.03.25 增加参数YS_SFXZNXYBBRGFKH控制男性医保病人不能在挂号科室名称含有“妇”、“产”的科室进行挂号 by MZYS-20110223-002. -- zhangyc 2011.03.25 增加参数SF_SFCGHSFDCZL医保病人收费处挂号是否带出诊疗项目(BM_GHXMDY维护)Y是N否默认N,厦门同安中医院职工体检不要带出 by MZSF-20110325-003 -- CSF 2011.03.29 SF_SFCGHSFDCZL,不仅要控制医保病人收费处挂号是否带出诊疗项目(BM_GHXMDY维护)，厦门同安中医院职工体检不要带出，同时挂号时候只收取挂号费，不收诊查费。 --zhangyc 2011.04.18 长乐医院：病人在门诊收费处挂一次本院职工医保号后。以后在门诊收费处挂号免收诊疗费，只收挂号费。医生无此权限 by MZSF-20110214-002 -- qks 2011.09.28 修正挂号费和诊查费的金额:MZFPID值修改。 -- qks 2011.10.31 珠海医院：挂号号=6位年月日+4位序列号 -- qks 2011.11.06 珠海医院：允许提前挂义诊号等免费号 -- qks 2011.11.09 记帐金额不为0时才写入缴费表 -- qks 2011.12.10 珠海医院：医生挂免费号，自动带出对应职称的挂号类别 MZHS-20111211-001 -- qks 2011.12.28 “新增免费号”菜单中，挂指定的“急诊自挂号”免费号（bm_ghlbb0.lbbh00=20），要求自动带出对应的诊疗项目 MZYS-20111228-003 -- DSM 2012.03.27 如果 HS_NLBSYXCL='Y'时,大于80的病人挂有待号FOR MZHS-20120314-001 -- linzy 2012.10.19 增加入参as_YBYWLX,as_YBDYLB by MZSF-20121019-001 -- zhangyc 2012.12.14 重新归档 by MZSF-20121204-002 -- linzy 2013.04.11 增加参数控制自助机和人工窗口现场挂号时限制同一病人同一号表只能挂一个号，不能挂2个及2个以上 by MZSF-20130322-002 -- linzy 2013.04.16 修正限制同一病人只能挂一个号增加退号判断 by MZSF-20130415-003 -- linzy 2014.08.26 预约公司接口：所有公医病人通过预约接口取号时，挂号费全部按自费收，不进行记账减免；预约数据增加个标示是否收费，因预约公司可以预收费 MZSF-20140826-002 -- linzy 2015.01.19 启用报到功能后病人挂号时写入候诊队列sf_hzbr00的状态为9 by MZHS-20150113-001 -- linzy 2015.03.02 所有公医病人通过预约接口取号按自费收取，只针对网络预约取号，院内预约取号按正常比例收费。 by -- linzy 2015.06.07 增加微信、支付方式，挂号时消息写入中间表 by MZSF-20150610-002 -- linzy 2015.06.11 报到科室维护参数扩展取BM_TYZD00 by -- linzy 2015.06.13 增加交易流水号 by -- dsm 2015.08.08 函数sf_ys_hzsj00增加参数as_SFZJ00,解决问题:当最后一个预约时段的加号病人,比正常号的病人先取号时,会取到同时段的正常号 -- piw 2015.11.15 限制14岁以上病人不能挂儿科 -- fengcx 2015.12.21 解决天灸无法挂号问题：根据挂号类别过滤义诊 ---piw 2015.12.24 支付宝、微信、医程通定义为网络预约 -- daihq 2016.05.12 增加修改号表的加号标志，使用加号的候诊序号正式挂号之后将号表加号状态取消 for MZSF-20160512-002 -- daihq 2016.05.31 现场脚本补归档，需求MZSF-20160512-002的功能重新修改 for MZSF-20160531-001 -- fengcx 2016.08.18 增加阳光康众的医程通(银联)支付入参处理 ad_TCJJZF=0.0000995 by MZSF-20160822-001 -- gaoyj 2016.09.29 增加自助机挂号入参处理 ad_TCJJZF=0.0000994 by MZSF-20161007-001 -- gaoyj 2016.10.06 更新bm_brxxb0时，先查询有没有数据，再更新，避免跳出ORA-0000的异常 by MZSF-20161007-001 -- fcx 2016.11.23 增加对BM_BRXXB0中BRZJBH字段进行解密获取身份证号明文 by MZSF-20161116-002 -- -------- --------------- ------------------------------------------------------------------------------------------------------------------------------------------------------ begin ls_ZSQGHF:='N';--只收取挂号费为N LS_YYID00:=0; --预约id SF_YYBRB0.YYID00 ls_CZRQ00 := to_char(sysdate,'YYYYMMDD'); ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); ad_SBZHDH := 0 ; --商保个人帐户单据流水号 ad_SBTJDH := 0 ; --商保统筹基金单据流水号 ad_FJJZDH := 0 ; --除了挂号费外的附加费的结算单号 ad_ZFDJH0 := 0 ; --自费金额对应的单据号 ad_TJDJH0 := 0 ; --医保的统筹基金对应的单据号 ad_GRDJH0 := 0 ; --医保的个人账户对应的单据号 ls_YSKS00 := 0 ; --门诊挂号医生默认的科室名称 ls_FJPJH0 := 0 ; ls_GHYXTS := 1 ; ls_ICKDM0 := 0 ; ls_ICKJE0 := 0 ; ls_sfje00 := 0 ; ls_FJFFBH := 0 ; --2009.02.25 ls_SFSQGHF :='1'; ls_YYBZ00 :='1'; ls_SFLNYD :=substrb(as_SFNLYD,1,1); ls_CZBZ00 :=substrb(as_CZBZ00,1,1); ls_GHXZQK := as_GHXZQK; ls_COUNT1 := 0 ; ls_COUNT2 :=1; b_SFWLYY:=0; --是否老年有待 begin select upper(substrb(value0,1,1)) into ls_SFLNYX from xt_xtcs00 where name00='HS_NLBSYXCL'; exception when others then ls_SFLNYX:='N'; end; --指定科室启用候诊队列新算法 select nvl(max(MC0000),' ') into ls_BDKS00_TYZD from BM_TYZD00 where ZDMC00='自助报到对应医院科室字典' and rownum=1; select nvl(max(trim(VALUE0)),'N') into ls_MZHS_HZBRSFXYXDL from XT_XTCS00 where NAME00='MZHS_HZBRSFXYXDL'; --珠海医院：允许提前挂义诊号等免费号 if (as_SFYYGH = 'N') and (as_GHRQ00>ls_CZRQ00) then ls_CZRQZH:=as_GHRQ00; else ls_CZRQZH:=ls_CZRQ00; end if; if ad_GHY000<>nvl(ad_JZYS00,0) then select count(distinct YYID00) into ls_COUNT0 from bm_bmbm00 where bmbh00 in (ad_CZYKS0,ad_GHKS00) and YYID00 is not null; if ls_COUNT0=2 then as_YHMSG0:='登陆科室和挂号科室不属于同一个分院，不能跨院挂号！'; as_SYSMSG:=SQLERRM||'brid00='||to_char(ad_BRID00); rollback; return; end if; end if; --公费病人建卡时是否一定要输入单位 begin select upper(substrb(value0,1,1)) into ls_gfxydw from xt_xtcs00 where name00='SF_GFBRJKBXSRDW'; exception when others then ls_gfxydw:='N'; end; --begin -- select value0 into ls_YBSFYXQF from xt_xtcs00 where name00='SF_YBSFYXQF'; --exception -- when others then -- ls_YBSFYXQF:='N'; --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_GHDJXZ from xt_xtcs00 where name00='XT_GHDJXZ'; exception when others then ls_GHDJXZ:='N'; end; begin select substrb(upper(trim(value0)),1,1) into ls_YXCFGH from xt_xtcs00 where name00='YS_YXCFGH'; exception when others then ls_YXCFGH:='Y'; --允许医生有效期内给病人重复挂号 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; begin select trim(VALUE0) into ls_SFXZNXYBBRGFKH from XT_XTCS00 where name00='YS_SFXZNXYBBRGFKH'; exception when others then ls_SFXZNXYBBRGFKH:='N'; end; --收费处挂号是否带出诊疗项目(BM_GHXMDY维护)Y是N否默认N 厦门同安中医院=Y begin select NVL(rtrim(value0),'N') into ls_GHDCZL from xt_xtcs00 where name00='SF_SFCGHSFDCZL'; exception when others then ls_GHDCZL:='N'; end; begin select trim(BMMC00) into ls_GHKSMC from BM_BMBM00 where BMBH00 = ad_GHKS00; exception when others then ls_GHKSMC:='无挂号科室'; end; -- 病人在一个科室挂完号后，过了挂号有效期后的第二天来这个科室就诊，是否免收挂号费 Y: 是； N: 否 默认为N begin select substrb(upper(trim(value0)),1,1) into YS_DETJZMSGHF from xt_xtcs00 where name00='YS_DETJZMSGHF'; exception when others then YS_DETJZMSGHF:='N'; -- end; begin select trim(value0) into ls_ghlbbh from xt_xtcs00 where name00='SF_GHLBBH'; exception when others then ls_ghlbbh:='-100000'; end; begin select trim(value0) into ls_XSTYPBBYXGTYYS from XT_XTCS00 where NAME00='SF_XSTYPBBYXGTYYS'; exception when others then ls_XSTYPBBYXGTYYS:='N'; end; /* ----- 限制14岁以上病人不能挂儿科 if INSTR(ls_GHKSMC,'儿')>'0' and to_number(trim(replace(SF_YF_CSRQTONL(as_CSRQ00,as_GHRQ00),'岁','')))>'14' then as_YHMSG0:='病人年龄大于14岁不能挂儿科号！'||trim(replace(SF_YF_CSRQTONL(as_CSRQ00,as_GHRQ00),'岁','')); as_SYSMSG:=sqlerrm; rollback; return; end if; */ ----- 限制14岁以上病人不能挂儿科 select nvl(max(XKH000),'0') into ls_SF_GHY000_XKH000 from BM_YGBM00 where YGBH00=ad_GHY000; ---解决天灸无法挂号问题：根据挂号类别过滤义诊 2015.12.21 Fengcx if as_GHLBMC<>'义诊' then if (instr(as_GHYXM0,'自助')>0 or instr(ls_SF_GHY000_XKH000,'yy')>0) then select b.bmmc00,a.ghmzsj into ls_GHKSMC,ls_ghrq00 from sf_ysghhb a,bm_bmbm00 b where a.ghksbh=b.bmbh00 and a.ghhbid=ad_GHHBID; --select nvl(max(XKH000),'0') into ls_SF_GHY000_XKH000 from BM_YGBM00 where YGBH00=ad_GHY000; select brcsrq into ls_brcsrq from bm_brxxb0 where brid00=ad_brid00; if (INSTR(ls_GHKSMC,'儿')>'0') and (to_char(ADD_MONTHS(to_date(ls_ghrq00,'yyyymmdd'),-168),'yyyymmdd')>ls_brcsrq) then as_SYSMSG:='病人年龄大于14岁不能挂儿科号！'; as_YHMSG0:=sqlerrm; rollback; return; end if; end if; end if; ----支付宝、微信、医程通支付定义为网络预约 if ad_GHY000=63336 or ad_GHY000=63377 or ad_GHY000=63790 then b_SFWLYY:=1; end if; --是否限制同一个病人在同一个号表不能重复挂号 by MZSF-20130322-002 linzy if nvl(ad_GHHBID,0)<>0 then select count(*) into ls_count0 from SF_BRXXB0 where GHHBID=ad_GHHBID and BRID00=ad_BRID00 and THBZ00<>'0'; if ls_count0>0 then if (ls_XSTYPBBYXGTYYS='0' or ls_XSTYPBBYXGTYYS='1') and instr(as_GHYXM0,'自助')>0 then as_YHMSG0:='病人在该医生相同的就诊时间已挂过号，不允许重复挂号！'; as_SYSMSG:=sqlerrm; rollback; return; end if; if (ls_XSTYPBBYXGTYYS='0' or ls_XSTYPBBYXGTYYS='2') then as_YHMSG0:='病人在该医生相同的就诊时间已挂过号，不允许重复挂号！'; as_SYSMSG:=sqlerrm; rollback; return; end if; end if; end if; --移动支付操作员 select nvl(max(value0),'N') into ls_SF_YDZFCZY from XT_XTCS00 where NAME00='SF_YDZFCZY'; if ad_BRID00>0 and ls_SFLNYD<>'Y' then --两费病人自动优待，免除挂号费 select ';'||nvl(max(trim(value0)),'0,0')||';' into Vzdydfb from XT_XTCS00 where name00='SF_ZDYDFB'; if Vzdydfb<>';0,0;' then select count(*) into ls_COUNT0 from BM_BRXXB0 a,IC_YBBRLB b where a.BRID00=ad_BRID00 and a.FBBH00=b.FBBH00 and a.YBLB00=b.YBLB00 and instrb(Vzdydfb,';'||to_char(a.FBBH00)||','||trim(b.YBZXLB)||';')>0; if ls_COUNT0>0 then update BM_BRXXB0 set SFYD00='1' where BRID00=ad_BRID00; ls_SFLNYD:='Y'; end if; end if; --要求70岁以上的老人，两费病人自动优待，免除挂号费 select substrb(nvl(max(trim(value0)),'N'),1,1) into Vlnzdyd from XT_XTCS00 where name00='SF_LNZDYD'; if Vlnzdyd='Y' then --增加对BM_BRXXB0中BRZJBH字段进行解密获取身份证号明文 2016.11.23 select BRCSRQ,nvl(trim(DECRYPT_SFZH(BRZJBH,33)),as_BRZJBH),SFYD00 into Vbrcsrq,Vbrzjbh,Vsfyd00 from BM_BRXXB0 where BRID00=ad_BRID00; if (Vsfyd00='0') and (sysdate-to_date(Vbrcsrq,'YYYYMMDD'))>70*365 and (substrb(Vbrzjbh,7,6)=substrb(Vbrcsrq,3,6) or substrb(Vbrzjbh,7,8)=Vbrcsrq) then update BM_BRXXB0 set SFYD00='1' where BRID00=ad_BRID00; ls_SFLNYD:='Y'; end if; end if; end if; --'护士挂号时，是否指定候诊序号 Y: 是 , N: 否 默认为N' select upper(trim(nvl(max(VALUE0),'N'))) into ls_XZHZXH from XT_XTCS00 where NAME00='YS_HSGHXZHZXH'; if (ad_GHKS00=0) and (as_GHLBMC='0') then begin select trim(value0) into ls_GHXMBH from XT_XTCS00 where NAME00='SF_GHXMBH'; exception when others then ls_GHXMBH:=''; end; begin select SFXMID,SFJE00,XMMC00 into ls_GHXMBH_1,ls_sfje00,ls_xmmc00_1 from BM_YYSFXM where SFXMID=ls_ghxmbh; exception when others then ls_GHXMBH_1:=''; ls_sfje00:=0; end; if (ls_GHXMBH_1='') or (ls_sfje00=0) then as_YHMSG0:='挂号项目没有维护完整，请找相关人员'; as_SYSMSG:=SQLERRM||'brid00='||to_char(ad_BRID00); rollback; return; end if; end if; if ad_BRID00>0 and ls_YXCFGH='N' then--预交金挂号，要判定病人是否被重复挂号 select count(*) into ls_COUNT0 from VW_YS_YXGH00 where BRID00=ad_BRID00 and GHKS00=ad_GHKS00 and JZYS00=ad_JZYS00 and ghlb00<>-99999; if ls_COUNT0 > 0 then as_YHMSG0:='该病人已经在挂过号，不允许重复挂号！'; as_SYSMSG:=SQLERRM||'brid00='||to_char(ad_BRID00); rollback; return; end if; end if; 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 if LS_SFLNYX='Y' and to_number(SF_BQ_CSRQTONL_YEAR(as_csrq00))>=80 then select HZLB00 into ls_HZLB00 from BM_HZLB00 where HZLBMC = '优特'; else select HZLB00 into ls_HZLB00 from BM_HZLB00 where HZLBMC = as_HZLB00; end if; exception when others then as_YHMSG0:='系统无法得到该挂号病人的优先级别对应的编码,请与系统管理员联系!'; as_SYSMSG:=SQLERRM||'select * from BM_HZLB00 where HZLBMC = '||as_HZLB00; rollback; return; end; if (trim(ls_SFXZNXYBBRGFKH)='Y') and (instr(as_FBMC00,'医保')>0) and (instr(as_XB0000,'男')>0) then--限制男性医保病人不能挂科室名称含妇 产的号 -- by MZYS-20110223-002. select count(*) into ls_COUNT0 from BM_BMBM00 where BMBH00=ad_GHKS00 and ((instr(BMMC00,'妇')>0) or (instr(BMMC00,'产')>0)); if ls_COUNT0 > 0 then as_YHMSG0:='读到的卡为男性医保卡,系统限制该类病人不能挂妇科、产科的号进行就诊！'; as_SYSMSG:=SQLERRM||'select count(*)from BM_BMBM00 where BMBH00='||to_char(ad_GHKS00)||' and ((instr(BMMC00,''妇'')>0) or (instr(BMMC00,''产'')>0)) '; rollback; return; end if; end if; /*--判断是否急诊挂号病人 if ad_BRID00>0 then select BZ0000 into ls_BZ0000 from BM_BRXXB0 where BRID00 = ad_BRID00; if ls_BZ0000='急诊' then select count(*) into ls_COUNT0 from sf_brxxb0 where BRID00 = ad_BRID00 and ghlb00<>-99999; if ls_COUNT0=0 then if INSTR(as_GHLBMC,'急诊')<=0 then as_YHMSG0:='该病人为急诊收费处建卡的病人，第一次挂号必须挂急诊号,病人挂号登记失败!'; as_SYSMSG:=SQLERRM||'brid00='||to_char(ad_BRID00); rollback; return; end if; end if ; end if ; end if ;*/ --先得到挂号有效天数,Modified on 2001-09-28 begin select NVL(max(VAlUE0),1) into ls_GHYXTS from XT_XTCS00 where name00 = 'GHYXTS'; exception when OTHERS then ls_GHYXTS:=1 ; --若没有查找到,设置成1天 end; begin select NVL(rtrim(VAlUE0),'Y') into ls_YBGHFJF from XT_XTCS00 where NAME00 = 'SF_YBGHFJF'; exception when OTHERS then ls_YBGHFJF:='Y' ; --若没有查找到,设置成医保病人挂号附加费打印在发票上 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; if ls_GHDLX0 = '9' and as_GHLBMC<>'体检号' then ls_CZBZ00:='1'; end if; if ad_GHY000 = ad_JZYS00 then --为了不修改医生工作站的程序,所以直接判断挂号员与医生是否为同一个人 ls_SFYDJ0:='0'; else ls_SFYDJ0:='1'; end if; if as_SFHSGH='Y' then ls_SFYDJ0:='0'; end if; update SF_BRZHXX set YBZHYE=nvl(as_ybzhye,0) where BRID00=ad_BRID00; --.判断病人能否挂号,若是住院病人就不能挂号 select count(*) into ls_COUNT0 from ZY_BRXXB0 where BRID00 = ad_BRID00 and CYRQ00 is null; --未出院的病人 if ls_COUNT0 > 0 then as_YHMSG0:='该病人正在住院,不能挂号,病人挂号登记失败!'; as_SYSMSG:=SQLERRM||'brid00='||to_char(ad_BRID00); rollback; return; end if; 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||'select sfje00 from bm_yysfxm where xmmc00 = 病历工本费'; 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; --0.若挂号有指定医生,查找医生的科室编号 if ad_JZYS00 <> 0 then begin select BMBH00 into ls_YSKS00 from BM_YGBM00 where YGBH00 = ad_JZYS00; exception when others then as_YHMSG0:='查找不到该医生对应的挂号科室名称!'; as_SYSMSG:=SQLERRM||'YGBH00='||to_char(ad_JZYS00); rollback; return; end; end if; --1、判断用户输入的挂号类别是否存在 --if (ad_GHKS00<>0) and ((as_GHLBMC<>'0') or (as_GHLBMC='免费号')) then if (as_GHLBMC<>'0') then begin select LBBH00 into ls_GHLB00 from BM_GHLBB0 where LBMC00 = as_GHLBMC; exception when others then as_YHMSG0:='系统没有找到满足条件的挂号类别名称，请重新输入该病人的挂号类别!'; as_SYSMSG:=SQLERRM; rollback; return; end; end if; if ls_GHDJXZ='Y' then--进行医生挂号等级限制 select count(*) into ls_COUNT0 from BM_GHLBB0 where LBBH00 =ls_GHLB00 and GHDJ00>(select GHDJ00 from BM_GHLBB0 where lbbh00=(select MRGHLB from BM_YGBM00 where YGBH00=ad_JZYS00 and ad_GHY000=ad_JZYS00)); if ls_COUNT0>0 then as_YHMSG0:='不能挂级别高的号！*'; as_SYSMSG:=SQLERRM; rollback; return; end if; end if; --2、得到挂号费对应的收费代码 begin select NVL(SFXMID,0),YDJM00 into ls_SFDM00,ls_YDJM00 from VW_SF_GHF000 where GHLB00 = ls_GHLB00 and SFYY00 = as_SFYYGH ;--'Y':预约挂号,'N':正常挂号 exception when others then if (trim(as_SFYYGH) <> 'Y') then --若不是预约挂号,则提示挂号费表维护不完整! ls_SFDM00:=0; --Modify on 2002-12-26,放宽该条件 end if; 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; --判断预约挂号病人账户余额不足不允许挂号。 begin if ls_ghlb00=-99999 then select count(1) into ls_count0 from XT_XTCS00 where NAME00='SF_ZHYEBZSFYXYYGH' and VALUE0='N'; if ls_COUNT0<>0 then select KYJE00 into LS_KYJE00 from VW_YS_BRXXB0 where BRID00= ad_BRID00; select GHKSBH,GHLBBH into Vghksbh,Vghlbbh from SF_YSGHHB where GHHBID=ad_GHHBID; sp_ys_ghf000(ad_BRID00,Vghlbbh,Vghksbh,'0','N','N','N',ls_ghfzje,ls_ghxjje); if ls_kyje00 0 then as_YHMSG0:='您所输入的病人病历号已被使用,请重新输入病人的病历号!'; as_SYSMSG:=SQLERRM||'BRBLH0='||as_BRBLH0; rollback; return; end if; Select SQ_BM_BRXXB0_BRID00.nextval into ls_BRID00 from dual; insert into BM_BRXXB0(BRID00,BRBLH0,YBKH00,YBID00,YBLB00,BRXM00,BRXB00,BRCSRQ,FBBH00,BRGFZH,CZY000,CZRQ00,CZSJ00,RYCS00,ZYZT00,SFYX00,BRZY00,BRMZ00,BRZJLX,BRZJBH,BRJTDZ,TYDWMC,TYDWID,BRDH00,BRHYZK) values(ls_BRID00,as_BRBLH0,as_YBKH00,as_YBID00,as_ZPZH00,as_XM0000,as_XB0000,as_CSRQ00,ls_FBBH00,as_GFZH00,ad_GHY000,ls_CZRQ00,ls_CZSJ00,1,'0','Y',as_BRZY00,as_BRMZ00,as_BRZJLX,as_BRZJBH,as_BRJTDZ,as_TYDWMC,ls_tydwid,as_BRDH00,as_BRHYZK); end; end; else begin ls_BRID00 := ad_BRID00; if ls_CZBZ00 = '1' then --现金病人挂号 select count(*) into ls_countcs from BM_BRXXB0 where BRID00 = ls_BRID00;--2016.10.06 gaoyj 先查询有没有数据，再更新，避免跳出ORA-0000的异常 if ls_countcs>0 then Update BM_BRXXB0 SET BRCSRQ = as_CSRQ00, BRZY00 = as_BRZY00, BRMZ00 = as_BRMZ00, BRZJLX = as_BRZJLX, BRZJBH = nvl(as_BRZJBH,DECRYPT_SFZH(BRZJBH,33)), --modify by fcx 2016.11.23 BRJTDZ = decode(as_GFZH00,' ',BRJTDZ,as_BRJTDZ), --modify by qks 2009.03.10 BRGFZH = decode(as_GFZH00,' ',BRGFZH,as_GFZH00), TYDWID = decode(as_GFZH00,' ',TYDWID,ls_tydwid), TYDWMC = decode(as_GFZH00,' ',TYDWMC,as_TYDWMC), BRDH00 = decode(as_GFZH00,' ',BRDH00,as_BRDH00), BRHYZK = as_BRHYZK where BRID00 = ls_BRID00; --根据挂号时信息修改 --if SQL%NOTFOUND then ----2016.10.06 gaoyj 先查询有没有数据，再更新，避免跳出ORA-0000的异常 -- insert into BM_BRXXB0(BRID00,BRBLH0,YBKH00,YBID00,YBLB00,BRXM00,BRXB00,BRCSRQ,FBBH00,BRGFZH,CZY000,CZRQ00,CZSJ00,RYCS00,ZYZT00,SFYX00,BRZY00,BRMZ00,BRZJLX,BRZJBH,BRJTDZ,TYDWMC,TYDWID,BRDH00,BRHYZK) -- values(ls_BRID00,as_BRBLH0,as_YBKH00,as_YBID00,as_ZPZH00,as_XM0000,as_XB0000,as_CSRQ00,ls_FBBH00,as_GFZH00,ad_GHY000,ls_CZRQ00,ls_CZSJ00,1,'0','Y',as_BRZY00,as_BRMZ00,as_BRZJLX,as_BRZJBH,as_BRJTDZ,as_TYDWMC,ls_tydwid,as_BRDH00,as_BRHYZK); --end if; else insert into BM_BRXXB0(BRID00,BRBLH0,YBKH00,YBID00,YBLB00,BRXM00,BRXB00,BRCSRQ,FBBH00,BRGFZH,CZY000,CZRQ00,CZSJ00,RYCS00,ZYZT00,SFYX00,BRZY00,BRMZ00,BRZJLX,BRZJBH,BRJTDZ,TYDWMC,TYDWID,BRDH00,BRHYZK) values(ls_BRID00,as_BRBLH0,as_YBKH00,as_YBID00,as_ZPZH00,as_XM0000,as_XB0000,as_CSRQ00,ls_FBBH00,as_GFZH00,ad_GHY000,ls_CZRQ00,ls_CZSJ00,1,'0','Y',as_BRZY00,as_BRMZ00,as_BRZJLX,as_BRZJBH,as_BRJTDZ,as_TYDWMC,ls_tydwid,as_BRDH00,as_BRHYZK); end if; end if; end; end if; --7、填写病人挂号信息表 IF as_SFYYGH = 'Y' then ls_GHRQ00:=as_GHRQ00; else ls_GHRQ00:=ls_CZRQ00; end if; ls_OGHJE0:=0; 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(SFJE00*ls_yhbl00,0) into ls_OGHJE0 from BM_YYSFXM where SFXMID = ls_SFDM00; end if; --挂号费金额,本金额只是挂号的金额,不含诊疗费部门的金额 if ad_GHHBID > 0 then begin select GHZKBH,GHMZSJ,GHQSSJ,GHMZSJ,GHJSSJ,GHHZCK,nvl(CKXXDZ,CKMC00) into ls_GHZKBH,ls_HZQSRQ,ls_HZQSSJ,ls_HZJZRQ,ls_HZJZSJ,ls_GHHZCK,ls_ZSXX00 from SF_YSGHHB a,BM_CKBM00 b where a.GHHZCK = b.CKBH00(+) and GHHBID = ad_GHHBID; exception when NO_DATA_FOUND then as_YHMSG0:='数据没有找到，请核对您所输入的医生挂号号表是否正确!'; as_SYSMSG:=SQLERRM||'GHHBID='||to_char(ad_GHHBID); rollback; return; end; SP_SF_XZGSKH(ad_GHHBID,0,0,'N',ls_COUNT0,as_YHMSG0,as_SYSMSG); else begin ls_HZQSRQ:=ls_CZRQ00; ls_HZQSSJ:=ls_CZSJ00; ls_HZJZRQ:=ls_CZRQ00; ls_HZJZSJ:=ls_CZSJ00; ls_GHHZCK:=0; end; end if; if (ad_GHKS00<>0) and (as_GHLBMC<>'0') then -- 如果挂号科室和挂号类别不为空的话 ls_HZZT00:='0'; if trim(ls_MZHS_HZBRSFXYXDL)='Y' or instr(','||trim(ls_MZHS_HZBRSFXYXDL)||','||trim(ls_BDKS00_TYZD)||',',','||ad_GHKS00||',')>0 then ls_HZZT00:='9'; end if; select SQ_SF_HZBR00_ID0000.nextval into ls_HZLBID from dual; insert into SF_HZBR00(ID0000,BRID00,GHH000,GHKS00,JZKS00,GHYS00,GHLB00,HZLB00,HZXH00,DJRQ00,DJSJ00,HZQSRQ,HZQSSJ,HZJZRQ,HZJZSJ,HZZT00,CZY000,YSPBID,JZZS00) values(ls_HZLBID,ls_BRID00,as_GHH000,ad_GHKS00,ad_GHKS00,ad_JZYS00,ls_GHLB00,ls_HZLB00,0,ls_CZRQ00,ls_CZSJ00,ls_HZQSRQ,ls_HZQSSJ,ls_HZJZRQ,ls_HZJZSJ,ls_HZZT00,ad_GHY000,ad_GHHBID,ls_GHHZCK); if (ls_XZHZXH='Y') and (ls_ghlb00=-99999) then --预约挂号 update SF_HZBR00 set HZXH00 = as_HZXH00 --候诊序号 where ID0000 = ls_HZLBID; update YS_HBHZXH set BRID00=ls_BRID00, BRXM00= as_XM0000 --修改候诊序号 where GHHBID=ad_GHHBID and HZXH00=as_HZXH00; select NVL(YYBZ00,'1') into ls_YYBZ00 from YS_HBHZXH where GHHBID=ad_GHHBID and HZXH00=as_HZXH00; ls_HZSJTD_STR := null; elsif (ls_XZHZXH='Y') and (ls_ghlb00<>-99999) then --ls_HZXH00 := NVL(substr(ls_HZSJTD_STR,35,5),'0'); ls_DLXH00 := -100; select count(1) into ls_COUNT0 from VW_YS_HZBR00 where JZKS00=ad_GHKS00 and JZYS00=ad_JZYS00 and BRID00=ls_BRID00 and DLXH00>0 and GHLB00=-99999 AND YSPBID=ad_GHHBID; if ls_COUNT0>0 then --如果存在一个预约挂号，就是预约挂号转正式挂号 select TO_CHAR(NVL(max(HZXH00),0)), max(HZLB00), nvl(max(DLXH00),-100) INTO ls_HZXH00, ls_HZLB00, ls_DLXH00 FROM VW_YS_HZBR00 where jzks00=ad_GHKS00 and jzys00=ad_JZYS00 AND BRID00=ls_BRID00 and DLXH00>0 AND GHLB00=-99999 AND YSPBID=ad_GHHBID; --and SUBSTR(LPAD(TO_CHAR(HZXH00),4,'0'),1,1)=ls_HZXHLB; ---记录原有的预挂号的GHID00 (有收过挂号费的) select nvl(max(b.GHH000),' ') into ls_YGHGHH from SF_BRFY00 a,VW_YS_HZBR00 b where b.JZKS00=ad_GHKS00 and b.JZYS00=ad_JZYS00 and b.BRID00=ls_BRID00 and b.DLXH00>0 AND b.GHLB00=-99999 AND b.YSPBID=ad_GHHBID and b.HZXH00=ls_HZXH00 and a.MZID00=b.GHID00; --冲销预挂号 if ls_YGHGHH<>' ' then for gh in C_YHH000 loop SP_SF_BRGHXX_EDIT00(gh.SFNLYD,gh.HZLBMC,'0',gh.GHH000,gh.YBGHH0,gh.BRBLH0,gh.LBMC00,gh.SFCZ00,gh.XM0000,gh.XB0000, gh.CSRQ00,gh.FBMC00,ad_GHY000,as_GHYXM0,gh.GHKS00,gh.GHKS00,gh.JZYS00,gh.JZYSXM,ad_GHHBID,'0', gh.GHJE00,0,0,0,gh.YBZXLB,'0','N',ad_ZFDJH0,ad_TJDJH0,ad_GRDJH0, as_YHMSG0,as_SYSMSG); end loop; end if; else select count(1) into ls_COUNT0 from VW_YS_HZBR00 where jzks00=ad_GHKS00 and jzys00=ad_JZYS00 and BRID00=ls_BRID00 and DLXH00>0 and GHLB00<>-99999 and YSPBID=ad_GHHBID; if ls_COUNT0>0 then --如果是补挂号的情况 select TO_CHAR(NVL(max(HZXH00),0)+1000), nvl((max(DLXH00)),-100) into ls_HZXH00, ls_DLXH00 from VW_YS_HZBR00 where JZKS00=ad_GHKS00 and JZYS00=ad_JZYS00 and BRID00=ls_BRID00 and DLXH00>0 AND GHLB00<>-99999 and YSPBID=ad_GHHBID; --and SUBSTR(LPAD(TO_CHAR(HZXH00),4,'0'),1,1)=ls_HZXHLB; else --ls_HZXH00 := '0'; --ls_DLXH00 := -100; begin --判断当前时间后表是否在使用 select count(*) into LS_COUNT0 from SF_YSGHHB where GHHBID=ad_GHHBID and GHMZSJ=to_char(sysdate,'YYYYMMDD') and GHQSSJ<=TO_CHAR(sysdate,'HH24:MI:SS') and GHJSSJ>=TO_CHAR(sysdate,'HH24:MI:SS'); if LS_COUNT0>0 then select min(HZXH00) into ls_hzxh00 from YS_HBHZXH where GHHBID=ad_GHHBID and yybz00=ls_hzlb00 and brid00 is null; update YS_HBHZXH set BRID00=ls_BRID00, BRXM00= as_XM0000 --修改候诊序号 where GHHBID=ad_GHHBID and HZXH00=ls_hzxh00; if ls_HZLB00<>2 then update SF_HZBR00 set HZXH00 = ls_HZXH00 , HZLB00 = ls_HZLB00 --候诊序号 where ID0000 = ls_HZLBID; end if; end if; exception when others then ls_hzxh00:='0'; ls_dlxh00:=-100; end; end if; end if; update SF_HZBR00 set HZXH00 = ls_HZXH00, --候诊序号 HZLB00 = ls_HZLB00 where ID0000 = ls_HZLBID; if (ls_DLXH00<>-100) AND (ls_HZLB00=2) then Update SF_HZBR00 set DLXH00=ls_DLXH00 where ID0000 = ls_HZLBID; end if; --省二预约挂号,正式挂号时候,补收预约费,并把预约记录状态改为就诊 if (ls_XZHZXH='Y') and (ls_ghlb00<>-99999) then begin select YYID00 into LS_YYID00 from YS_HBHZXH where GHHBID=ad_GHHBID and HZXH00=ls_HZXH00 and BRID00=ad_BRID00; exception when others then LS_YYID00:=0; end; end if; else--ls_XZHZXH='N'排班时候不指定候诊序号 if (ls_XZHZXH='J') and (as_HZXH00>0) and (ad_GHHBID>0) then select nvl(max(sfzj00),'0') into ls_sfzj00 from ys_hbhzxh where ghhbid=ad_ghhbid and yyid00=as_hzxh00; end if; begin ls_HZSJTD_STR:=trim(SF_YS_HZSJ00(ls_HZLBID,trim(as_jzsj00),ls_sfzj00)); --根据函数得到相关的时间段 --ls_HZSJTD_STR:=trim(SF_YS_HZSJ00(ls_HZLBID)); --根据函数得到相关的时间段 exception when OTHERS then as_YHMSG0:='获得候诊时间出错误,请稍候再试!'; as_SYSMSG:=SQLERRM||'SF_YS_HZSJ00('||to_char(ls_HZLBID)||')'; rollback; return; end; if ls_HZSJTD_STR is not null then ls_HZXH00 := NVL(substr(ls_HZSJTD_STR,35,5),'0'); ls_HZXHLB := substr(lpad(ls_HZXH00,4,'0'),1,1); ls_DLXH00 := -100; if (ls_ghlb00<>-99999) then select TO_CHAR(NVL(max(HZXH00),TO_NUMBER(ls_HZXH00))), nvl(max(HZLB00),ls_HZLB00), nvl(max(DLXH00),-100) into ls_HZXH00, ls_HZLB00, ls_DLXH00 from VW_YS_HZBR00 where JZKS00=ad_GHKS00 and JZYS00=ad_JZYS00 and BRID00=ls_BRID00 and DLXH00>0 and GHLB00=-99999 and YSPBID=ad_GHHBID; --and SUBSTR(LPAD(TO_CHAR(HZXH00),4,'0'),1,1)=ls_HZXHLB; end IF; --zhr 2008.01.03 预约病人进行提前挂号，如果预约时间之前的候诊段有余号，会被自动转为普通病人。 select HZLB00 into ls_HZLB01 from SF_HZBR00 where ID0000=ls_HZLBID; if ls_HZLB00<>ls_HZLB01 then ls_HZLB00:=ls_HZLB01; if ls_HZLB00='1' then ls_GHXZQK:='N'; end if; end if ; --modify by qks 2011.02.25 if (ls_XZHZXH='J') and (as_HZXH00>0) and (ad_GHHBID>0) then --AND (ad_TCJJZF = 0.0000998) select nvl(max(SFDTYY),'0'),max(YYLB00),max(BZ0000) into ls_SFDTYY,ls_SF_YYBRB0_YYLB00,ls_SF_YYBRB0_BZ0000 from SF_YYBRB0 A,BM_YGBM00 B where A.YYID00=as_HZXH00 and A.YYYGBH=B.YGBH00; --是否网络预约 select nvl(max(XKH000),'0') into ls_SF_GHY000_XKH000 from BM_YGBM00 where YGBH00=ad_GHY000; if ((ls_SF_YYBRB0_YYLB00='2') or (trim(ls_SF_YYBRB0_BZ0000)='住院预约')) and instr(ls_SF_GHY000_XKH000,'yy')>0 then b_SFWLYY:=1; else b_SFWLYY:=0; end if; /* ---- 支付宝、微信、医程通为网络预约 if ad_GHY000=63336 or ad_GHY000=63377 then b_SFWLYY:=1; end if; */ --修改预约记录为的就诊状态 update SF_YYBRB0 set YYZT00='3',GHID00=ls_MZID00,BRID00=ad_BRID00,BDRQ00=ls_CZRQ00,BDSJ00=ls_CZSJ00,BDCZY0=ad_GHY000 where YYID00=as_HZXH00 and YYZT00 IN ('0','1'); end if; update SF_HZBR00 set HZQSRQ = NVL(substr(ls_HZSJTD_STR,1,8),ls_CZRQ00), --候诊起始日期 HZQSSJ = NVL(substr(ls_HZSJTD_STR,9,8),ls_CZSJ00), --候诊起始时间 HZJZRQ = NVL(substr(ls_HZSJTD_STR,18,8),ls_CZRQ00), --候诊结束日期 HZJZSJ = NVL(substr(ls_HZSJTD_STR,26,8),ls_CZSJ00), --候诊结束时间 HZXH00 = ls_HZXH00, --候诊序号 SFDTYY = ls_SFDTYY --是否当天预约,'1'当天预约 where ID0000 = ls_HZLBID; if (ls_DLXH00<>-100) and (ls_HZLB00=2) then update SF_HZBR00 set DLXH00=ls_DLXH00 where ID0000 = ls_HZLBID; end if; end if; end if; end if; --SF_HZBR00的队列序号处理结束 if (ad_GHHBID > 0) then begin if ls_XZHZXH='Y' then ls_GHXZQK:='N'; end IF; if (trim(ls_GHXZQK) = 'N') and (ls_ghlb00<>-99999) then --- zjf add and(trim(ls_HZLB00)<>'3') Update SF_YSGHHB SET GHYGHS=NVL(GHYGHS,0)+1 where GHHBID = ad_GHHBID; end if; if (trim(ls_GHXZQK) = 'Y') AND (ls_ghlb00<>-99999) then Update SF_YSGHHB SET GHYGHS=NVL(GHYGHS,0)+1,GHYYHS=NVL(GHYYHS,0)+1 where GHHBID = ad_GHHBID; end if; if ls_YYBZ00='3' then Update SF_YSGHHB SET GHYYHS=NVL(GHYYHS,0)+1 where GHHBID = ad_GHHBID; end if; --2016.05.31 daihq 在最新脚本重新修改 for MZSF-20160531-001 --2016.05.12 daihq 增加取消加号标志 for MZSF-20160512-002 if ((trim(ls_GHXZQK) = 'N') or (trim(ls_GHXZQK) = 'Y')) and (ls_ghlb00<>-99999) then if (ls_XZHZXH='J') and (as_HZXH00>0) and (ad_GHHBID>0) and (ls_sfzj00='1') then update SF_YSGHHB set SFZJGH='0' where GHHBID = ad_GHHBID; elsif (ad_GHHBID>0) and (ls_hzxh00<>'0') then select nvl(max(sfzj00),'0') into ls_sfzj00 from YS_HBHZXH where GHHBID=ad_ghhbid and HZXH00=ls_hzxh00; if ls_sfzj00='1' then update SF_YSGHHB set SFZJGH='0' where GHHBID = ad_GHHBID; end if; end if; end if; --add by qks 2010.11.03 广东自助取号 -- if (ad_TCJJZF = 0.0000998) and (ls_ghlb00=-99999) and (as_HZXH00>0) then -- Update SF_YSGHHB SET GHYYHS=NVL(GHYYHS,0)+1 where GHHBID = ad_GHHBID; -- end if; exception when NO_DATA_FOUND then as_YHMSG0:='数据没有找到，请核对您所输入的医生挂号号表是否正确!'; as_SYSMSG:=SQLERRM||'GHHBID='||to_char(ad_GHHBID); rollback; return; end; end if; --发现SF_YS_HZSJ00返回的参数有问题，故添加了如下的重新得到函数的返回值 --ls_HZSJTD_STR:=NVL(substr(ls_HZSJTD_STR,1,8),ls_CZRQ00)||NVL(substr(ls_HZSJTD_STR,9,8),ls_CZSJ00)||','||NVL(substr(ls_HZSJTD_STR,18,8),ls_CZRQ00)||NVL(substr(ls_HZSJTD_STR,26,8),ls_CZSJ00)||','||NVL(substr(ls_HZSJTD_STR,35,5),'0'); --特殊处理OVER! --if (ad_GHKS00=0) and ((as_GHLBMC='0') or (as_GHLBMC<>'0')) and (ls_ghlbbh<>'-100000') then if (ad_GHKS00=0) and (ls_ghlbbh<>'-100000') then insert into SF_BRXXB0(GHID00,BRBLH0,GHH000,YBGHH0,GHLB00,BRID00,SFCZ00,XM0000,XB0000,CSRQ00, FBBH00,LBBH00,GFZH00,GHRQ00,GHSJ00,GHY000,GHYXM0,GHKS00, JZRQ00,JZSJ00,JZKS00,JZYS00,JZYSXM,SFYY00,THBZ00,PJH000,GHYXTS,SFNLYD,HZLB00,HZSJD0,GHJE00,CZRQ00,CZSJ00,ghfzje,GHZKBH,GHSJD0,GHHBID,ZSXX00,YBYWLX,YBDYLB) --Values(ls_MZID00,as_BRBLH0,as_GHH000,as_YBGHH0,ls_ghlbbh,ls_BRID00,as_SFCZ00,as_XM0000,as_XB0000,as_CSRQ00, values(ls_MZID00,as_BRBLH0,as_GHH000,as_YBGHH0,decode(as_GHLBMC,'0',ls_ghlbbh,ls_GHLB00),ls_BRID00,as_SFCZ00,as_XM0000,as_XB0000,as_CSRQ00, --Values(ls_MZID00,as_BRBLH0,as_GHH000,as_YBGHH0,1,ls_BRID00,as_SFCZ00,as_XM0000,as_XB0000,as_CSRQ00, ls_FBBH00,ls_LBBH00,as_GFZH00,NVL(trim(substr(ls_HZSJTD_STR,1,8)),ls_CZRQ00),ls_CZSJ00,ad_GHY000,as_GHYXM0,0, NVL(trim(substr(ls_HZSJTD_STR,1,8)),ls_CZRQ00),ls_CZSJ00,ad_GHKS00,ad_JZYS00,as_JZYSXM,as_SFYYGH,1,ls_PJH000,ls_GHYXTS,ls_SFLNYD,ls_HZLB00,ls_HZSJTD_STR,ls_sfje00,ls_CZRQ00,ls_CZSJ00,ls_sfje00,ls_GHZKBH,as_GHSJD0,ad_GHHBID,ls_ZSXX00,as_YBYWLX,as_YBDYLB); else --ls_CZRQ00-->ls_CZRQZH insert into SF_BRXXB0(GHID00,BRBLH0,GHH000,YBGHH0,GHLB00,BRID00,SFCZ00,XM0000,XB0000,CSRQ00, FBBH00,LBBH00,GFZH00,GHRQ00,GHSJ00,GHY000,GHYXM0,GHKS00, JZRQ00,JZSJ00,JZKS00,JZYS00,JZYSXM,SFYY00,THBZ00,PJH000,GHYXTS,SFNLYD,HZLB00,HZSJD0,GHJE00,CZRQ00,CZSJ00,GHZKBH,GHSJD0,GHHBID,ZSXX00,YBYWLX,YBDYLB) Values(ls_MZID00,as_BRBLH0,as_GHH000,as_YBGHH0,ls_GHLB00,ls_BRID00,as_SFCZ00,as_XM0000,as_XB0000,as_CSRQ00, ls_FBBH00,ls_LBBH00,as_GFZH00,NVL(trim(substr(ls_HZSJTD_STR,1,8)),ls_CZRQZH),ls_CZSJ00,ad_GHY000,as_GHYXM0,ad_GHKS00, NVL(trim(substr(ls_HZSJTD_STR,1,8)),ls_CZRQZH),nvl(as_JZSJ00,ls_CZSJ00),ad_GHKS00,ad_JZYS00,as_JZYSXM,as_SFYYGH,'1',ls_PJH000,ls_GHYXTS,ls_SFLNYD,ls_HZLB00,ls_HZSJTD_STR,ls_OGHJE0,ls_CZRQ00,ls_CZSJ00,ls_GHZKBH,as_GHSJD0,ad_GHHBID,ls_ZSXX00,as_YBYWLX,as_YBDYLB); end if; --先得到费用表、费用明细表都要用到的单据号 select SQ_SF_BRJFB0_DJH000.nextval into ls_DJH000 from dual; --8.0.若病人是现金病人,说明该病人的挂号附加费是要立刻结算的,否则这些费用可以日后结算 IF as_YBID00 <> '0' then --说明:若是医保病人,将病人的费用拆分成2条费用 select SQ_SF_BRJFB0_DJH000.nextval into ls_FJDJH0 from dual; if ls_LBBH00 = 0 or ad_GRZHZF = 0.0000999 then --若是医保的现金病人，立刻结算，若是医保的持卡病人，若要立刻结算的 select SQ_SF_JZB000_JZDH00.nextval into ad_FJJZDH from dual; select SQ_SF_PJSYQK_PJH000.nextval into ls_FJPJH0 from dual; end if; else --说明:若是非医保病人,病人的费用只有一条费用 IF ls_GHDLX0 = '221094' then begin select SQ_SF_BRJFB0_DJH000.nextval into ls_FJDJH0 from dual; select SQ_SF_JZB000_JZDH00.nextval into ad_FJJZDH from dual; select SQ_SF_PJSYQK_PJH000.nextval into ls_FJPJH0 from dual; end; else begin ls_FJDJH0:=ls_DJH000; if ls_LBBH00 = 0 then ad_FJJZDH := ls_JZDH00; ls_FJPJH0 := ls_PJH000; end if; end; end if; end if; --Add at 广州 on 2002-12-01，在收费项目视图中按费别，医保中心类别，病人类别等字段来获得对应的自付比例 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 B.FBBH00 =ls_FBBH00; IF (ls_GFAZFGH='Y') and (ls_FBBH00<>1) and (ls_FBBH00<>99) and (as_ZPZH00<>ls_YBZXLB) and (as_ZPZH00<>'0') then -- 公费转公费 added by qks for MZSF-20080804-006 select YBZXLB into ls_YBZXLB from IC_YBBRLB where FBBH00 = ls_FBBH00 and YBLB00 = as_ZPZH00; end if; exception when others then if (ls_GFAZFGH='Y') then if ls_FBBH00<>1 then --自费转公费 added by qks for MZSF-20080711-001 ls_YBBRLB := '0'; begin select YBZXLB into ls_YBZXLB from IC_YBBRLB where FBBH00 = ls_FBBH00 and YBLB00 = as_ZPZH00; exception when NO_DATA_FOUND then as_YHMSG0:='医保病人所属医保中心名称有错，系统字典维护不完整！'; as_SYSMSG:=SQLERRM||'Not exists(select YBZXLB into ls_YBZXLB from IC_YBBRLB where FBBH00 ='||to_char(ls_FBBH00)||' and YBLB00 ='||as_ZPZH00||')'; 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; --判定挂号的合法性,只能挂限制的号 --select count(*) into ls_COUNT0 from XT_XTCS00 where NAME00='YJ_YJMRGHLB' and to_number(value0)=ls_ghlb00; --linzy 更改写法 在9i to_number(value0)=ls_ghlb00 提示无效数字 select to_number(nvl(trim(value0),0)) into ls_tmpGHLB from XT_XTCS00 where NAME00='YJ_YJMRGHLB'; if ls_tmpGHLB=ls_ghlb00 then ls_COUNT0:=1; else ls_COUNT0:=0; end if; if ls_COUNT0=0 then--不是医技默认挂号，需要进行判定 begin begin select lbbh00 into ls_xzghlb_byzg from bm_ghlbb0 where lbbh00 in (select to_number(value0) from XT_XTCS00 where NAME00='SF_BYZGGHLB'); exception when others then ls_xzghlb_byzg:=-1; end; select count(1) into ls_count0 from BM_YGBM00 where BRID00=ls_BRID00; if ls_count0>0 then--本院职工 select count(1) into ls_count0 from xt_xtcs00 where NAME00='SF_BYZGSFKGTJH' and value0 = 'Y'; --jxq if (ls_xzghlb_byzg<>ls_ghlb00) and (ls_count0 <> 1) then select lbmc00 into as_YHMSG0 from BM_GHLBB0 where LBBH00=ls_xzghlb_byzg; as_YHMSG0:='挂号类别不对，本院职工只能挂"'||as_YHMSG0||'"!'; as_SYSMSG:='请重新选择挂号类别!'; rollback; return; end if; else--普通病人 if ls_xzghlb_byzg= ls_ghlb00 then select lbmc00 into as_YHMSG0 from BM_GHLBB0 where lbbh00=ls_xzghlb_byzg; as_YHMSG0:='挂号类别不对，非本院职工不能挂"'||as_YHMSG0||'"!'; as_SYSMSG:='请重新选择挂号类别!'; rollback; return; end if; begin select nvl(xzghlb,'0') into ls_xzghlb from BM_YBBRLB where YBBRLB=ls_ybbrlb and YBZXLB=ls_ybzxlb and rownum=1; exception when others then ls_xzghlb:='0'; end; if (as_SFHSGH='N') and (ls_ghlb00=-99999) THEN as_YHMSG0:='挂号失败,该挂号类别只能在护士站使用'; as_SYSMSG:='请重新选择挂号类别!'; rollback; return; end if; if (ls_xzghlb<>'0') and (ls_ghlb00<>-99999) then if instr(','||ls_xzghlb||',',','||ls_ghlb00||',')=0 then --select lbmc00 from bm_ghlbb0 where INSTR(ls_xzghlb,','|| lbbh00||',')>0; for LBMC IN CUR_BM_GHLBB0_MC loop as_YHMSG0 := as_YHMSG0 ||';'||LBMC.LBMC00; end loop; as_YHMSG0:='挂号类别不对，该病人只能挂"'||as_YHMSG0||'"!'; as_SYSMSG:='请重新选择挂号类别!'; rollback; return; end if; --else --如果自己没有限号，也不能挂其他人的限制号 -- select count(*) into ls_xzghlb from bm_ybbrlb where xzghlb=ls_ghlb00 and rownum=1; -- if ls_xzghlb>0 then -- select lbmc00 into as_YHMSG0 from bm_ghlbb0 where lbbh00=ls_ghlb00; -- as_YHMSG0:='挂号类别不对，该病人不能挂"'||as_YHMSG0||'"!'; -- as_SYSMSG:='请重新选择挂号类别!'; -- rollback; -- RETURN; -- end if; end if; end if; exception when others then ls_xzghlb_byzg:=0; end; end if; --8、填写病人的费用的详细的信息 --a、挂号费信息的添加 ls_GFBL00 := 0; ls_JZBL00 := 0; ls_yhbl00 :=1; --特殊医院特殊处理，广东省中医院还要挂号费进行拆分(如:残疾人士,特困人士,其挂号费还可能要减免) begin IF ls_SFDM00 <> 0 then --广州特困病人挂“简易医生”时挂号费用1元照收 add by xzw MZSF-20070203-007+残疾军人挂号时：若挂专家或名专家号则费用照收MZSF-20070203-008 if (ls_GHDLX0='9') and (((ls_FBBH00=10) and (ls_GHLB00=12)) or ((ls_FBBH00=9) and ((ls_GHLB00=60005) or (ls_GHLB00=10)))) then select 0,0,NVL(XMJE00*1,0),NVL(XMJE00*1,0),0,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 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'; --是否药品 end if; 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; when others then as_YHMSG0:='获取该挂号类别对应的挂号费的自付比例、记账比例时,出现异常错误!'; as_SYSMSG:=SQLERRM||'select * from VW_BM_YBSFDY where SFYP00 = N and YBBRLB ='||ls_YBBRLB||' and YBZXLB='||ls_YBZXLB||' and SFXMID='||to_char(ls_SFDM00)||'and FBBH00='||to_char(ls_FBBH00); rollback; return; end; if ls_SFLNYD = 'Y' and ls_YDJM00 = 'Y' and b_SFWLYY=0 then --若ls_SFLNYD='Y',说明有老年优待，同时该类别的挂号号允许减免,则应处理免收挂号费,否则按收费项目的对应关系获取比例 ls_GFBL00 := 1; ls_JZBL00 := 0; ls_MXZFJE := 0; ls_MXJZJE := 0; --ls_MXGFJE := ls_MXHJJE; ls_MXGFJE := 0;--泉州老年优待金额为0不产生费用 ls_MXHJJE := 0; END IF; --a.添加挂号费的费用 1=2 IF (ls_SFDM00 <> 0) and ((ls_MXZFJE+ls_MXJZJE)<>0) THEN IF YS_DETJZMSGHF='Y' THEN SELECT COUNT(1) INTO ls_count0 FROM sf_brxxb0 a,bm_bmbm00 b ,bm_ghlbb0 c where a.ghks00=b.bmbh00(+) and a.ghlb00=c.lbbh00 and a.THBZ00<>'0' and (trunc(sysdate-1/4)-a.GHYXTS-1) -99999; IF ls_count0>1 THEN ls_SFSQGHF := '0'; ELSE ls_SFSQGHF := '1'; END IF; ELSE ls_SFSQGHF := '1'; END IF; --福州市长乐医院:在门诊收费处挂一次离休干部号,退休县处级号(ghlb00=23,27)后，那么在医生工作站挂号都免收挂号费，诊疗费要正常收; select count(1) into ls_YyCount from xt_yyxx00 where yyid00='222137'; if ls_YyCount>0 then select count(1) into ls_COUNT0 from sf_brxxb0 a where brid00=ad_BRID00 and ghlb00 in (23,27) and ghy000<>nvl(JZYS00,0) and THBZ00<>'0' and not exists (select 1 from BM_TYZD00 where ZDMC00='处级干部优惠病人停用名单' and instrb(','||BZ0000||',' ,','||a.BRBLH0||',')>0 and YXBZ00='1'); if ls_COUNT0>0 then ls_SFSQGHF :='0'; end if; end if; --市皮医院，预挂号收过挂号费的，那么转正式挂号只收诊查费不收挂号费 (按冲销预挂号的做法 ，所以改成正式挂号时候，挂号费和诊查费都收取) --if ls_YGHGHH<>' ' then -- ls_SFSQGHF :='0'; -- end if; if ls_SFSQGHF='1' then --自负金额+记帐金额大于0的才写挂号费明细 add by jlg 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_GHRQ00,ls_CZSJ00,ls_GHRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,ls_SFYDJ0,(1-ls_GFBL00-ls_JZBL00) from BM_YYSFXM where SFXMID = ls_SFDM00; if SQL%NOTFOUND then if (trim(as_SFYYGH) <> 'Y') then --若不是预约挂号,则提示挂号费表维护不完整! as_YHMSG0:='病人挂号费收取失败!请核对挂号费对应的收费标准是否合法!'; as_SYSMSG:=SQLERRM||'NOT EXISTS select from BM_YYSFXM where SFXMID = '||to_char(ls_SFDM00); ROLLBACK; RETURN; end if; end if; end if; --elsif (ad_GHKS00=0) and ((as_GHLBMC='0') OR (as_GHLBMC<>'0')) then --福大医院 elsif (ad_GHKS00=0) and (as_GHLBMC='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_GHXMBH_1,ls_xmmc00_1,'','次',ls_sfje00,1,ls_sfje00,ls_sfje00,0,0, --values(SQ_SF_FYMX00_MXID00.nextval,ls_DJH000,decode(as_GHLBMC,'0',ls_GHXMBH_1,ls_SFDM00),ls_xmmc00_1,'','次',1,1,ls_sfje00,ls_sfje00,0,0, ls_GHRQ00,ls_CZSJ00,ls_GHRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,ls_SFYDJ0,(1-ls_GFBL00-ls_JZBL00)); END IF; if ls_GHDLX0 = '9' then select SQ_SF_BRJFB0_DJH000.nextval into ls_WJSDJH from dual; end if; --广东省中医院格式的挂号单,其医院要求将记账部分放在发票中一起打印,故强行将其拆分成多条 --MODIFY:由于挂号的附加费可以由医保报销,所以将挂号附加费单独设置成一个单据号 --a.1.添加病人病历工本费 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 OTHERS 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; --如果预约病人则走自费处理 挂号费不做分单 2014.08.20 linzy if b_SFWLYY=1 then ls_MXJZJE:=0; ls_MXGFJE:=0; ls_MXHJJE:=0; ls_MXZFJE:=0; ls_GFBL00:=0; ls_JZBL00:=0; end if; 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_FJDJH0,ls_BLSFDM,ls_BLXMMC,'','本',ls_SCZFJE,1,ls_SCZFJE,ls_SCZFJE,0,0, ls_GHRQ00,ls_CZSJ00,ls_GHRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,ls_SFYDJ0,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_GHRQ00,ls_CZSJ00,ls_GHRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,ls_SFYDJ0,(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_FJDJH0,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_GHRQ00,ls_CZSJ00,ls_GHRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,ls_SFYDJ0,(1-ls_JZBL00-ls_GFBL00)); end if; END IF; --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 OTHERS 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中的自付金额，公费金额，先添加一条自付金额+公费金额，再按记账金额添加新记录 --如果预约病人则走自费处理 挂号费不做分单 2014.08.20 linzy if b_SFWLYY=1 then ls_MXJZJE:=0; ls_MXGFJE:=0; ls_MXHJJE:=0; ls_MXZFJE:=0; ls_GFBL00:=0; ls_JZBL00:=0; end if; 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_FJDJH0,ls_ICKDM0,ls_ICKMC0,'',ls_ICKDW0,ls_SCZFJE,1,ls_SCZFJE,ls_SCZFJE,0,0, ls_GHRQ00,ls_CZSJ00,ls_GHRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,ls_SFYDJ0,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_GHRQ00,ls_CZSJ00,ls_GHRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,ls_SFYDJ0,(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_FJDJH0,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_GHRQ00,ls_CZSJ00,ls_GHRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,ls_SFYDJ0,(1-ls_JZBL00-ls_GFBL00)); end if; end if; if as_ICKH00 <> '0' then --将该病人旧的卡进行作废登记 insert into IC_YWLS00(YWID00,ICKH00,YGBH00,YWCZ00,CZRQ00,CZSJ00,FSJE00,ICKNBH) select SQ_IC_YWLS00_YWID00.nextval,ICKH00,ad_GHY000,'5',ls_GHRQ00,ls_CZSJ00,ICKJE0,ICKH00 from IC_YHXX00 where BRID00 = ls_BRID00 and ZT0000 = '1'; update IC_YHXX00 set ZT0000 = '4' --无效卡 where BRID00 = ls_BRID00 and ZT0000 = '1'; --再开始修改就诊卡的信息 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); end if; --b、挂号费附加费的添加,注意,挂号附加费的收取是对于非预约挂号的病人才适用 if (trim(as_SFYYGH) <> 'Y') then --若不是预约挂号,既是正常的挂号,开始收取病人的挂号附加费 select count(*) into ls_COUNT0 from SF_FJGHF0 where FJBRLB =as_SFCZ00 and FJGHLB = ls_GHLB00 and FJGHKS=ad_GHKS00; if ls_YyCount>0 then --如果是长乐医院，判断改病人是否挂过退休县处级号，有挂过的话则不收诊察费 select count(1) into ls_COUNT1 from sf_brxxb0 where brid00=ad_BRID00 and ghlb00=27 and ghy000<>nvl(JZYS00,0) and THBZ00<>'0'; --长乐医院：病人在门诊收费处挂一次本院职工医保号后。以后在门诊收费处挂号免收诊疗费，只收挂号费。医生无此权限 ---by MZSF-20110214-002 begin-- begin select LBBH00 into ls_GHLB01 from BM_GHLBB0 where LBMC00 like '%本院职工医保号%'; exception when others then ls_GHLB01:=-1; end; select nvl(YGXZ00,'0') into ls_YGXZ00 from BM_YGBM00 where YGBH00=ad_GHY000; if (ls_GHLB01<>-1) and instrb(ls_YGXZ00,'3')>0 then --收费员性质 select count(1) into ls_COUNT2 from sf_brxxb0 where brid00=ad_BRID00 and ghlb00=ls_GHLB01 and THBZ00<>'0' and GHY000<>nvl(JZYS00,0); else ls_COUNT2:=1; end if; ---by MZSF-20110214-002 end-- end if; 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; --if ls_COUNT1=0 then ----挂号带出诊疗(那么诊疗费在yj_yw0000收取，不在挂号时候收取)。 if (ls_FBBH00=3) and (trim(ls_GHDCZL)='Y') and (ls_GHKSMC<>'职工体检') then ls_GHDCZL:='Y'; select count(*) into ls_COUNT0 from BM_GHXMDY where LBBH00=ls_GHLB00 and SYBZ00='1'; if ls_COUNT0>0 then ls_ZSQGHF:='Y'; end if; end if; if (ls_COUNT1=0) and (as_ZSQGFF<>'Y') and (ls_ZSQGHF<>'Y') and (ls_COUNT2=1) then 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 --广州中医院省公医（10%、20%）及市公医（10%、20%）的情况下，急诊病人诊金费要减免2元.MZSF-20070203-001 /* if (ls_GHDLX0='9') and ( ((ls_FBBH00=5) or (ls_FBBH00=7)) and (ls_GHLB00=2) and ((ls_YBZXLB='2') or (ls_YBZXLB='3'))) then --select 0,0.3,4,4,0,2,4 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; --得到对应的记账比例，公费比例 select NVL((1-ZFBL00-JMBL00),0),NVL(JMBL00,0),4,4,0,2,4 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; --得到对应的记账比例，公费比例 --省公医（全免）的情况下，急诊病人诊金费要减免3元 elsif (ls_GHDLX0='9') and ((ls_FBBH00=5) and (ls_GHLB00=2) and (ls_YBZXLB='1')) then --select 0,0.5,3,3,0,3,3 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; --得到对应的记账比例，公费比例 select NVL((1-ZFBL00-JMBL00),0),NVL(JMBL00,0),3,3,0,3,3 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; --得到对应的记账比例，公费比例 --市公医（全免）的情况下，急诊病人诊查费要减免6元,即诊金费为0 elsif (ls_GHDLX0='9') and ((ls_FBBH00=7) and (ls_GHLB00=2) and (ls_YBZXLB='1')) then --select 0,1,0,0,0,6,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; --得到对应的记账比例，公费比例 select NVL((1-ZFBL00-JMBL00),0),NVL(JMBL00,0),0,0,0,6,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; --得到对应的记账比例，公费比例 --其他 else */ 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 others 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; -- end if; end; if ls_GHDLX0 = '9' then --广东省中医院格式的挂号单,其医院要求将记账部分放在发票中一起打印,故强行将其拆分成多条 --对于广东省中医院的挂号附加费的拆分规则的说明： --先提取VW_BM_YBSFDY中的自付金额，公费金额，先添加一条自付金额+公费金额，再按记账金额添加新记录 --如果预约病人则走自费处理 挂号费不做分单 2014.08.20 linzy if b_SFWLYY=1 then ls_SCZFJE:=ls_SCZFJE+ls_MXZFJE+ls_MXJZJE; ls_MXJZJE:=0; ls_MXGFJE:=0; ls_MXHJJE:=0; ls_MXZFJE:=0; ls_GFBL00:=0; ls_JZBL00:=0; end if; 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_FJDJH0,SFXMID,XMMC00,'',DW0000,ls_SCZFJE,1,ls_SCZFJE,ls_SCZFJE,0,0, ls_GHRQ00,ls_CZSJ00,ls_GHRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,ls_SFYDJ0,1 from BM_YYSFXM where SFXMID = ls_FJFFBH; end if; -- if ls_MXHJJE <> 0 then if (ls_MXZFJE+ls_MXJZJE)<>0 then --上一行改为如此 jlg 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_GHRQ00,ls_CZSJ00,ls_GHRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,ls_SFYDJ0,(1-ls_JZBL00-ls_GFBL00) from BM_YYSFXM where SFXMID = ls_FJFFBH; end if; --End of ls_JZBL00 <> 0 elsif (ls_MXZFJE+ls_MXJZJE+ls_SCZFJE)<>0 then --add by jlg 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_FJDJH0,SFXMID,XMMC00,'',DW0000,SFJE00,1,SFJE00, decode(ls_fbbh00,'3',sfje00,SFJE00*(1-ls_JZBL00-ls_GFBL00)),decode(ls_fbbh00,'3',0,SFJE00*ls_GFBL00),decode(ls_fbbh00,'3',0,SFJE00*ls_JZBL00), ls_GHRQ00,ls_CZSJ00,ls_GHRQ00,ls_CZSJ00, ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,ls_SFYDJ0,(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; end if; end IF; -- end of IF as_SFYYGH<>'Y' --insert into SF_BRFY00,会自动触发修改SF_BRZHXX的TRIGGER --添加挂号费的信息，费用表SF_BRFY00 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_LBBH00,ls_BRID00,as_XM0000,ls_FBBH00, ls_MXHJJE,ls_MXZFJE,ls_MXJZJE,ls_MXGFJE,0,ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,ad_CZYKS0,ls_JZDH00); end if; --添加挂号附加费的信息 if ls_FJDJH0 <> ls_DJH000 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_FJDJH0; 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_FJDJH0,as_GHH000,ls_MZID00,ls_LBBH00,ls_BRID00,as_XM0000,ls_FBBH00, ls_MXHJJE,ls_MXZFJE,ls_MXJZJE,ls_MXGFJE,0,ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,ad_CZYKS0,ad_FJJZDH); end if; end if; --若是广东省中医院，市公医病人挂无诊金的号别，还要收3元的普通门诊诊查费(SFXMID=20008) add by qks 2009.02.25 /*if (ls_GHDLX0 = '9') and (ls_FBBH00=7) and (ls_FJFFBH=0) then if ls_SFDM00 = 0 then ls_yhbl00:=1; end if; 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 = (select sfxmid from bm_yysfxm where xmmc00='普通门诊诊查费' and bmgzid=1) and FBBH00 = ls_FBBH00 and YBZXLB = ls_YBZXLB and YBBRLB = ls_YBBRLB and SFYP00 = 'N' and ROWNUM = 1; --得到对应的记账比例，公费比例 EXCEPTION WHEN OTHERS 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_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,GG0000,DW0000,ls_MXHJJE,1,ls_MXHJJE,ls_MXZFJE,ls_MXGFJE,ls_MXJZJE, ls_GHRQ00,ls_CZSJ00,ls_GHRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,ls_SFYDJ0,(1-ls_JZBL00-ls_GFBL00) from bm_yysfxm where SFXMID = (select sfxmid from bm_yysfxm where xmmc00='普通门诊诊查费' and bmgzid=1); end if; end if; --end if 2009.02.25 */ 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_LBBH00,ls_BRID00,as_XM0000,ls_FBBH00, ls_MXHJJE,ls_MXZFJE,ls_MXJZJE,ls_MXGFJE,0,ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,ad_CZYKS0,0); end if; end if; --10、若是现金病人，还应该填写病人交费表的信息,病人结账表、病人结账明细,若病人有预交金账户，不对其做结算处理 if ls_LBBH00 = 0 then begin select ZFFSBH ,ZFFSMC into ls_ZFFSBH,ls_ZFFSMC from BM_BRZFFS where ZFFSMC = '现金'; exception when OTHERS then as_YHMSG0:='系统没有找到支付方式为现金的记录，门诊字典表BM_BRZFFS数据不完整,操作失败!'; as_SYSMSG:=SQLERRM||'select * from BM_BRZFFS where ZFFSMC ='||'现金'; 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 OTHERS then as_YHMSG0:='系统没有找到支付方式为记账的字典数据，门诊字典表BM_BRZFFS数据不完整,操作失败!'; as_SYSMSG:=SQLERRM||'select * from BM_BRZFFS where ZFFSBH = 7 and ZFFSMC like '|| '%记账%'; ROLLBACK; return; end; -- --1.0、添加挂号费的现金交费金额 --modify by qks 2010.10.24 -- 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_LBBH00,ZFJE00,ls_ZFFSBH,ls_ZFFSMC,ls_PJH000, --对应挂号单的票据号 -- ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ls_JZDH00,1,ZFJE00 -- from SF_BRFY00 where DJH000 = ls_DJH000; --modiy by qks 2010.10.24 广东省中医院 现金挂号时是否使用预交金支付 ls_ZYE000 := 0; if ad_TCJJZF = 0.0000999 then --可允许部分预交金支付 select nvl(max(zye000),0) into ls_ZYE000 from sf_brzhxx where brid00=ls_BRID00 and sfdj00='0'; if ls_ZYE000>0 then --1.0.0、添加挂号费的预交金支付金额 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_LBBH00,decode(sign(ZFJE00-ls_ZYE000),1,ls_ZYE000,ZFJE00),19,'预交金',ls_PJH000, --对应挂号单的票据号 ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ls_JZDH00,1,ZFJE00 from SF_BRFY00 where DJH000 = ls_DJH000; end if; end if; if ad_TCJJZF = 0.0000998 then --只允许预交金支付 --1.0.0、添加挂号费的预交金支付金额 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_LBBH00,ZFJE00,19,'预交金',ls_PJH000, --对应挂号单的票据号 ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ls_JZDH00,1,ZFJE00 from SF_BRFY00 where DJH000 = ls_DJH000; --2015.06.06 增加微信、支付宝支付方式 byLinzy --2016.08.18 增加阳光康众的医程通(银联)支付入参处理 ad_TCJJZF=0.0000995 Bygaoyj elsif ad_TCJJZF = 0.0000997 then insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,ZPH000) select SQ_SF_BRJFB0_DJH000.nextval,ls_BRID00,ls_LBBH00,ZFJE00,20,'支付宝',ls_PJH000, --对应挂号单的票据号 ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ls_JZDH00,1,ZFJE00,as_JYLSH0 from SF_BRFY00 where DJH000 = ls_DJH000; elsif ad_TCJJZF = 0.0000996 then insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,ZPH000) select SQ_SF_BRJFB0_DJH000.nextval,ls_BRID00,ls_LBBH00,ZFJE00,21,'微信',ls_PJH000, --对应挂号单的票据号 ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ls_JZDH00,1,ZFJE00,as_JYLSH0 from SF_BRFY00 where DJH000 = ls_DJH000; elsif ad_TCJJZF = 0.0000995 then --增加阳光康众的医程通(银联)支付入参处理 ad_TCJJZF=0.0000995 Bygaoyj 2016.08.18 insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,ZPH000) select SQ_SF_BRJFB0_DJH000.nextval,ls_BRID00,ls_LBBH00,ZFJE00,22,'医程通',ls_PJH000, --对应挂号单的票据号 ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ls_JZDH00,1,ZFJE00,as_JYLSH0 from SF_BRFY00 where DJH000 = ls_DJH000; elsif ad_TCJJZF = 0.0000994 then --增加阳光康众的医程通(银联)支付入参处理 ad_TCJJZF=0.0000995 Bygaoyj 2016.08.18 insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,ZPH000) select SQ_SF_BRJFB0_DJH000.nextval,ls_BRID00,ls_LBBH00,ZFJE00,8,'银行卡',ls_PJH000, --对应挂号单的票据号 ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ls_JZDH00,1,ZFJE00,as_JYLSH0 from SF_BRFY00 where DJH000 = ls_DJH000; else --非广东模式 以及 可允许部分预交金支付 --1.0、添加挂号费的现金交费金额 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_LBBH00,ZFJE00-ls_ZYE000,ls_ZFFSBH,ls_ZFFSMC,ls_PJH000, --对应挂号单的票据号 ZFJE00 ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ls_JZDH00,1,ZFJE00 --from SF_BRFY00 where DJH000 = ls_DJH000; from SF_BRFY00 where DJH000 = ls_DJH000 and ZFJE00>ls_ZYE000; end if; --1.1、添加挂号费用的记账金额 modified by 2011.11.09 记帐金额不为0时才写入缴费表 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_LBBH00,JZJE00,ls_ZFFSBH_2,ls_ZFFSMC_2,ls_PJH000, --对应挂号单的票据号 ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ls_JZDH00,1,JZJE00 from SF_BRFY00 where DJH000 = ls_DJH000 and JZJE00<>0; --2.0、添加挂号附加费的现金交费金额 if ls_DJH000 <> ls_FJDJH0 then 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) select ad_ZFDJH0,ls_BRID00,ls_LBBH00,ZFJE00,ls_ZFFSBH,ls_ZFFSMC,ls_FJPJH0, --对应现金发票的票据号 ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ad_FJJZDH,1,ZFJE00 from SF_BRFY00 where DJH000 = ls_FJDJH0; --2.1、添加挂号附加费的记账金额 modified by 2011.11.09 记帐金额不为0时才写入缴费表 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_LBBH00,JZJE00,ls_ZFFSBH_2,ls_ZFFSMC_2,ls_PJH000, --对应挂号单的票据号 ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ls_JZDH00,1,JZJE00 from SF_BRFY00 where DJH000 = ls_FJDJH0 and JZJE00<>0; end if; END IF; --11、若结算单号<>'0',说明还应该对挂号费做结算登记 IF ls_JZDH00 <> 0 THEN --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,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 -- added by jlg for MZSF-20080428-002 Update SF_JZB000 SET FBBH00 = ls_FBBH00,YBLB00 = as_ZPZH00,YBBRLB = ls_YBBRLB where JZDH00 = ls_JZDH00; end if; --insert into SF_JZMX00 insert into SF_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00,ZFBL00) select ls_JZDH00,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; end if; --添加挂号单票据的使用情况,补充:不管是收费员挂号的或医生挂号的,都要有挂号单的信息 begin select nvl(sum(ZFJE00),0),NVL(sum(JZJE00),0) into ls_TMPJE0,ls_TMPJZ0 from SF_BRFY00 where DJH000 = ls_DJH000; --本句加sum,为了不会出现没有的记录(预约挂号有可能有没有的记录) exception when NO_DATA_FOUND then ls_TMPJE0:=0; ls_TMPJZ0:=0; end; IF ls_CZBZ00 = '0' then --持卡病人 ls_PJXJJE := ls_TMPJE0; --不管是现金病人,还是持卡病人,所使用的都是现金金额 ls_PJZZJE := 0; --转账金额，也可称为从病人预交金账户上扣除的金额 ls_JZJE00:=ls_TMPJZ0; --记帐金额 else ls_PJXJJE := ls_TMPJE0; --票据上的现金金额 ls_PJZZJE := 0; --转账金额，也可称为从病人预交金账户上扣除的金额 ls_JZJE00:=ls_TMPJZ0; --记帐金额 end if; if as_SFYYGH <> 'Y' then --若不是预约挂号,则都要有挂号单的信息 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,decode(ls_SFYDJ0,'0','-'||as_YJJXH0,as_YJJXH0),ls_PJXJJE,ls_PJZZJE,ad_GHY000,as_GHYXM0, ls_GHRQ00,ls_CZSJ00,decode(ls_SFYDJ0,'0','N','Y'),'0',as_XM0000,as_XB0000,ls_BRID00,ls_JZJE00 from bm_pjlxb0 where PLBH00 = 0;--PLBH00=0是挂号单 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; end if; --11.2.若附加结算单号<>0,说明还应该对挂号附加费做结算登记,注意:附加费的单独结算只是对于医保病人适用 if (ad_FJJZDH <> 0) and (ls_FJDJH0 <> ls_DJH000) then --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 ad_FJJZDH,ls_BRID00,ls_MZID00,HJJE00,JZJE00,GFJE00,ZFJE00,0,0,0,ZFJE00,ls_FJPJH0,ls_CZRQ00,ls_CZSJ00,ZFJE00,0,ad_GHY000 from SF_BRFY00 where DJH000 = ls_FJDJH0; --挂号附加费的费用结算,对应的票据是发票 if (ls_GFAZFGH='Y') THEN -- added by jlg for MZSF-20080428-002 update SF_JZB000 SET FBBH00 = ls_FBBH00,YBLB00 = as_ZPZH00,YBBRLB = ls_YBBRLB where JZDH00 = ad_FJJZDH; end if; --insert into SF_JZMX00 insert into SF_JZMX00(JZDH00,XMBH00,XMMC00,XMJE00,ZFBL00) select ad_FJJZDH,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 = ad_FJJZDH group by D.JZDH00,B.FPXMID,B.XMMC00,A.ZFBL00; --添加附加费结算对应的发票的使用情况 begin select NVL(ZFJE00,0),NVL(JZJE00,0) into ls_TMPJE0,ls_TMPJZ0 from SF_BRFY00 where DJH000 = ls_FJDJH0; --ADD XUZW exception when NO_DATA_FOUND then ls_TMPJE0:=0; ls_TMPJZ0:=0; end; if ls_CZBZ00 = '0' then --持卡病人 ls_PJXJJE := ls_TMPJE0; --票据上的现金金额 ls_PJZZJE := 0; --转账金额，也可称为从病人预交金账户上扣除的金额 ls_JZJE00 := ls_TMPJZ0; --转帐金额 else ls_PJXJJE := ls_TMPJE0; --票据上的现金金额 ls_PJZZJE := 0; --转账金额，也可称为从病人预交金账户上扣除的金额 ls_JZJE00 := ls_TMPJZ0; --转帐金额 end if; if ls_YBGHFJF = 'Y' then --若挂号附加费打印在发票上, if ls_PJXJJE+ls_PJZZJE+ls_JZJE00 <> 0 THEN --modify by xuzw insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,JZJE00) select ls_FJPJH0,PLBH00,PLMC00,as_FPXH00,ls_PJXJJE,ls_PJZZJE,ad_GHY000,as_GHYXM0, ls_GHRQ00,ls_CZSJ00,'Y','0',as_XM0000,as_XB0000,ls_BRID00,ls_JZJE00 from bm_pjlxb0 where PLBH00 = 1;--PLBH00=1是门诊发票 if SQL%NOTFOUND then as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败!'; as_SYSMSG:=SQLERRM||'票据类型(门诊发票)不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 1)'; ROLLBACK; return; end if; end if; --添加票据使用明细表的信息 --insert into SF_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,XMBZ00) -- select ls_FJPJH0,XMBH00,XMMC00,XMJE00,0 from SF_JZMX00 where JZDH00 = ad_FJJZDH; else --否则,挂号附加费打印在发票上 Update SF_JZB000 Set PJH000 = ls_PJH000 where JZDH00 = ad_FJJZDH; Update SF_PJSYQK Set XJJE00 = XJJE00 + ls_PJXJJE , ZZJE00 = ZZJE00 + ls_PJZZJE, JZJE00 = JZJE00 + ls_JZJE00 where PJH000 = ls_PJH000; end if; end if; --12、若是医保程序有返回医保挂号流水号,将医保传回的个人账户金额、统筹基金金额做为转账金额添加到病人交费表中. --注意核对在insert完成后，病人账户的余额是否有变化,及SF_BRZHXX表中的病人的余额是否变化，注意核对！ IF as_YBID00 <> '0' THEN 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_LBBH00,0,3,'医保个人账户',as_ZPZH00,ls_FJPJH0, ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ad_FJJZDH,1,0); 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_LBBH00,0,4,'医保统筹基金',as_ZPZH00,ls_FJPJH0, ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ad_FJJZDH,1,0); select SQ_SF_BRJFB0_DJH000.nextval into ad_SBZHDH from dual; --商保个人帐户单据流水号 insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_SBZHDH,ls_BRID00,ls_LBBH00,0,ZFFSBH,ZFFSMC,as_ZPZH00,ls_FJPJH0, ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ad_FJJZDH,1,0 from BM_BRZFFS where ZFFSBH=10; select SQ_SF_BRJFB0_DJH000.nextval into ad_SBTJDH from dual; --商保统筹基金单据流水号 insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_SBTJDH,ls_BRID00,ls_LBBH00,0,ZFFSBH,ZFFSMC,as_ZPZH00,ls_FJPJH0, ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ad_FJJZDH,1,0 from BM_BRZFFS where ZFFSBH=11; -- select SQ_SF_BRJFB0_DJH000.nextval into ad_jjdjh0 from dual; --医保基金单据流水号 insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE) select ad_jjdjh0,ls_BRID00,ls_LBBH00,0,ZFFSBH,ZFFSMC,as_ZPZH00,ls_FJPJH0, ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,0,'0',0,ad_FJJZDH,1,0 from BM_BRZFFS where ZFFSBH=6; END IF; --14、非现金病人基本信息表的病人类别的修改 IF ad_BRID00 <> 0 THEN update BM_BRXXB0 set BRXM00 = as_XM0000, BRXB00 = as_XB0000, ZYZT00 = '0', --病人的状态改为门诊 BRLX00 = '复诊' -- BRDH00 = as_BRDH00, -- BRJTDZ = as_BRJTDZ where BRID00 = ls_BRID00; END IF; --特殊控制:考虑的其它报表的统计,由于广东省中医院在挂号时,有未结算的费用发生,但又没有结算的,所以人为将日期进行Update Update SF_FYMX00 set CZRQ00 = '18000101',ZXRQ00 = '18000101' where DJH000 in (select DJH000 from SF_BRFY00 where JZDH00+0 =0 and MZH000 = as_GHH000 and JFLBID = 0); Update SF_BRFY00 set CZRQ00 = '18000101' where JZDH00+0 =0 and MZH000 = as_GHH000 and JFLBID = 0; if (trim(SF_YJJGHZXJ)='Y') then--更新挂号在SF_FYMX00中的单据号 update sf_brxxb0 set djh000=ls_djh000 where ghid00=ls_MZID00; end if; --修正挂号费和诊查费的金额 if (ad_GHKS00<>0) and (as_GHLBMC<>'0') then -- 如果挂号科室和挂号类别不为空的话 select nvl(max(FPXMID),4) into v_count from bm_fpxm00 where xmmc00='挂号费'; select nvl(max(FPXMID),6) into ls_count0 from bm_fpxm00 where xmmc00='诊察费'; update sf_brxxb0 A set ghfzje=(select hjje00 from sf_brfy00 where djh000=(select min(djh000) from sf_brfy00 where mzid00=ls_MZID00)), zcfje0=(select nvl(sum(X.hjje00),0) from SF_FYMX00 X,BM_YYSFXM S where x.djh000=(select min(djh000) from sf_brfy00 where mzid00=ls_MZID00) and X.xmbh00=S.sfxmid and MZFPID+0= ls_count0), ghje00=(select nvl(sum(X.hjje00),0) from SF_FYMX00 X,BM_YYSFXM S where x.djh000=(select min(djh000) from sf_brfy00 where mzid00=ls_MZID00) and X.xmbh00=S.sfxmid and MZFPID+0 = v_count) where ghid00 = ls_MZID00; update sf_brxxb0 A set ghfzje=nvl(ghfzje,0),zcfje0=nvl(zcfje0,0),ghje00=nvl(ghje00,0) where ghid00 = ls_MZID00; --if ls_YGHGHH<>' ' then --市皮医院，预挂号有收费，转正式挂号后，将进行挂号费的合并处理 -- select nvl(max(GHID00),0),nvl(max(DJH000),0) into ls_YGHID0,ls_YGHDJH from SF_BRXXB0 where GHH000=ls_YGHGHH; -- update SF_BRFY00 set MZID00=ls_MZID00,MZH000=as_GHH000 where MZID00=ls_YGHID0; -- update SF_BRXXB0 a Set (GHFZJE,ZCFJE0,GHJE00)=(select a.GHFZJE+b.GHFZJE,a.ZCFJE0+b.ZCFJE0,a.GHJE00+b.GHJE00 -- from SF_BRXXB0 b where GHID00=ls_YGHID0) where GHID00=ls_MZID00; -- update SF_BRXXB0 a set GHFZJE=0,ZCFJE0=0,GHJE00=0 where GHID00 = ls_YGHID0; -- end if; END IF; --科室排班，医生叫号队列处理 if (ls_XZHZXH='Y') and (ls_ghlb00=-99999) and (ad_GHHBID>0) then select count(*) into v_count from SF_YSGHHB where GHHBID=ad_GHHBID and PBLX00='1'; if v_count>0 then begin sp_ys_ksysdlcl(ad_GHHBID,ad_GHKS00,ad_BRID00,as_HZXH00,ls_HZLBID,ls_YHMSG0,ls_SYSMSG); exception when others then as_YHMSG0:=ls_YHMSG0; as_SYSMSG:=ls_SYSMSG; ROLLBACK; RETURN; end; end if; end if; --省二人民医院，补收预约费begin if ls_yyid00>0 then select count(*) into v_count from SF_YYBRB0 where YYID00=ls_yyid00 and YYZT00 IN ('0','1') and (DJH000=0 or DJH000 is null ); if v_count>0 then --先判断去哪个收费标准 begin select C.Sckbh0 into ls_CKBH00 from SF_YYBRB0 a,SF_YSGHHB b,bm_CKDY00 C where b.GHHZCK=c.DCKBH0 and a.ghhbid=b.ghhbid and a.YYID00=LS_YYID00 ; select count(1) into ls_FZCOUNT from xt_xtcs00 where instrb(','||trim(value0)||',',','||to_char(ls_CKBH00)||',')>0 and name00='YS_FZTQYLYSFBZ'; exception when others then ls_FZCOUNT:=0; end; --取收费项目id if ls_FZCOUNT>0 then begin select to_number(trim(value0)) into vSFXMID from xt_xtcs00 where name00='YY_YYSFXMID2' and value0>'0'; exception when others then begin select to_number(trim(value0)) into vSFXMID from xt_xtcs00 where name00='YY_YYSFXMID'; exception when others then as_YHMSG0:='补收费预约费失败,参数YY_YYSFXMID未设置!'; as_SYSMSG:=SQLERRM||'票据类型(挂号单)不存在: Not exists(select trim(value0) into vSFXMID from xt_xtcs00 where name00=YY_YYSFXMID)'; ROLLBACK; RETURN; end; end; else begin select to_number(trim(value0)) into vSFXMID from xt_xtcs00 where name00='YY_YYSFXMID'; exception when others then as_YHMSG0:='补收费预约费失败,参数YY_YYSFXMID未设置!'; as_SYSMSG:=SQLERRM||'票据类型(挂号单)不存在: Not exists(select trim(value0) into vSFXMID from xt_xtcs00 where name00=YY_YYSFXMID)'; ROLLBACK; RETURN; end; end if; --先得到费用表、费用明细表都要用到的单据号 select SQ_SF_BRJFB0_DJH000.nextval into ls_DJH000 from dual; ls_GFBL00 := 0; ls_JZBL00 := 0; ls_yhbl00 :=1; --取优惠比例 select nvl(max(DJ0000),-1) into ls_yhbl00 from BM_TYZD00 where ZDMC00='按病人类别定义预约费优惠比例' and YXBZ00='1' and instrb(','||trim(BZ0000)||',',','||to_char(ls_FBBH00)||'-'||ls_YBZXLB||'-'||ls_YBBRLB||',')>0 and rownum=1 ; if (ls_yhbl00<0) or (ls_yhbl00>1) then ls_yhbl00:=1; else ls_yhbl00:=1-ls_yhbl00; end if; begin 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 = vSFXMID and FBBH00 = ls_FBBH00 and SFYP00 = 'N'; --是否药品 EXCEPTION WHEN NO_DATA_FOUND THEN as_YHMSG0:='预约费维护错误，ZLXMID='||to_char(vSFXMID)||'!'; as_SYSMSG:=SQLERRM||'未找到数据，select * from VW_BM_YBSFDY where YBBRLB = '||ls_YBBRLB||' and YBZXLB='||ls_YBZXLB||' and SFXMID ='||to_char(vSFXMID)||' and FBBH00 ='||to_char(ls_FBBH00)||' and SFYP00=N'; ROLLBACK; RETURN; WHEN OTHERS THEN as_YHMSG0:='获取预约费的自付比例、记账比例时,出现异常错误!'; as_SYSMSG:=SQLERRM||'select * from VW_BM_YBSFDY where SFYP00 = N and YBBRLB ='||ls_YBBRLB||' and YBZXLB='||ls_YBZXLB||' and SFXMID='||to_char(vSFXMID)||'and FBBH00='||to_char(ls_FBBH00); ROLLBACK; RETURN; END; --自负金额+记帐金额大于0的才写挂号费明细 add by jlg 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_GHRQ00,ls_CZSJ00,ls_GHRQ00,ls_CZSJ00,ad_GHKS00,ad_JZYS00,ad_GHKS00,ad_JZYS00,ls_YSKS00,ls_SFYDJ0,(1-ls_GFBL00-ls_JZBL00) from BM_YYSFXM where SFXMID = vSFXMID; if SQL%NOTFOUND then as_YHMSG0:='病人预约费收取失败!请核对对应的收费标准是否合法!'; as_SYSMSG:=SQLERRM||'NOT EXISTS select from BM_YYSFXM where SFXMID = '||to_char(vSFXMID); ROLLBACK; RETURN; end if; --insert into SF_BRFY00,会自动触发修改SF_BRZHXX的TRIGGER --添加预约费的信息，费用表SF_BRFY00 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_LBBH00,ls_BRID00,as_XM0000,ls_FBBH00, ls_MXHJJE,ls_MXZFJE,ls_MXJZJE,ls_MXGFJE,0,ls_GHRQ00,ls_CZSJ00,ad_GHY000,as_GHYXM0,ad_CZYKS0,0); end if; update SF_YYBRB0 SET DJH000=ls_DJH000 where YYID00=LS_YYID00 and YYZT00 IN ('0','1'); end if; --修改预约记录为的就诊状态 update SF_YYBRB0 SET YYZT00='3' where YYID00=LS_YYID00 and YYZT00 IN ('0','1'); if SQL%NOTFOUND then as_YHMSG0:='未找到病人的预约记录'; as_SYSMSG:=SQLERRM||'NOT EXISTS select from SF_BRYYB0 where YYID00=LS_YYID00 and YYZT00 IN (''0'',''1'')'; ROLLBACK; RETURN; end if; end if; --省二人民医院，补收预约费end --15挂号类别自动带出诊疗项目 --邵武医院对医保病人统一挂“医保号”(gllb00=22)，要求系统自动根据医生技术职称类别带出相应的诊疗项目， --其他医院则是直接按医生选择的挂号类别带出相应的诊疗项目 --医保病人收费处挂号是否带出诊疗项目(BM_GHXMDY维护)Y是N否默认N,厦门同安中医院职工体检不要带出 MZSF-20110325-003 --珠海医院对挂“医生自挂号”(gllb00=17),要求系统自动根据医生技术职称类别带出相应的诊疗项目,ad_GHY000 -->nvl(ad_JZYS00,ad_GHY000) select count(*) into ls_count0 from xt_yyxx00 where YYJC00 like '%珠海医院%'; if (ls_count0=0 and trim(ls_GHDCZL)='N' ) or (ls_ghlb00=17 and ls_count0>0) or ( ls_FBBH00=3 and trim(ls_GHDCZL)='Y' and ls_GHKSMC<>'职工体检' ) then for zl in CUR_BM_GHXMDY_ZLXMID loop insert INTO YJ_YW0000(ZLXMID,SL0000,DW0000,YJKSBH,KDYS00,KDKSBH,KDRQ00,KDSJ00, LRXM00,XMZT00,DCBZ00,YJDJH0,BRID00,ZYGHID,MZZYBZ ) select zl.ZLXMID,1,DW0000,sf_ys_xmzxks(zl.ZLXMID,ad_GHKS00),nvl(ad_JZYS00,ad_GHY000),ad_GHKS00,to_char(sysdate,'YYYYMMDD'), to_char(sysdate,'HH24:MI:SS'),as_GHYXM0,'0','0',SQ_YJ_YW0000_YJDJH0.NEXTVAL,ls_BRID00,ls_MZID00,'0' from VW_BM_ZLZD00 where ZLXMID=zl.ZLXMID; end loop; for yj in CUR_GHDCZL loop insert INTO YJ_YWMX00( ID0000,YJDJH0,ZLXMID ) SELECT SQ_YJ_YWMX00_ID0000.NEXTVAL, YJDJH0, ZLXMID FROM YJ_YW0000 WHERE YJDJH0=yj.YJDJH0; SF_YJ_YWJJ_NOCOMMIT(yj.YJDJH0,1); end loop; end if; --珠海医院:挂指定的“急诊自挂号”免费号（bm_ghlbb0.lbbh00=20），要求自动带出对应的诊疗项目 if (ls_count0>0) and (ls_ghlb00=20) then ls_count0 := 0; for zl in CUR_BM_GHXMDY_ZLXMID loop insert INTO YJ_YW0000(ZLXMID,SL0000,DW0000,YJKSBH,KDYS00,KDKSBH,KDRQ00,KDSJ00, LRXM00,XMZT00,DCBZ00,YJDJH0,BRID00,ZYGHID,MZZYBZ ) select zl.ZLXMID,1,DW0000,sf_ys_xmzxks(zl.ZLXMID,ad_GHKS00),nvl(ad_JZYS00,ad_GHY000),ad_GHKS00,to_char(sysdate,'YYYYMMDD'), to_char(sysdate,'HH24:MI:SS'),as_GHYXM0,'0','0',SQ_YJ_YW0000_YJDJH0.NEXTVAL,ls_BRID00,ls_MZID00,'0' from VW_BM_ZLZD00 where ZLXMID=zl.ZLXMID; end loop; for yj in CUR_GHDCZL loop insert INTO YJ_YWMX00( ID0000,YJDJH0,ZLXMID ) SELECT SQ_YJ_YWMX00_ID0000.NEXTVAL, YJDJH0, ZLXMID FROM YJ_YW0000 WHERE YJDJH0=yj.YJDJH0; SF_YJ_YWJJ_NOCOMMIT(yj.YJDJH0,1); end loop; end if; if as_ZSQGFF='Y' then for zl in CUR_BM_GHXMDY_ZLXMID_2 loop insert INTO YJ_YW0000(ZLXMID,SL0000,DW0000,YJKSBH,KDYS00,KDKSBH,KDRQ00,KDSJ00, LRXM00,XMZT00,DCBZ00,YJDJH0,BRID00,ZYGHID,MZZYBZ,BZ0000) select zl.ZLXMID,1,DW0000,ad_GHKS00,ad_GHY000,ad_GHKS00,to_char(sysdate,'YYYYMMDD'), to_char(sysdate,'HH24:MI:SS'),as_GHYXM0,'0','0',SQ_YJ_YW0000_YJDJH0.NEXTVAL,ls_BRID00,ls_MZID00,'0','诊察费' from VW_BM_ZLZD00 where ZLXMID=zl.ZLXMID; end loop; for yj in CUR_GHDCZL loop insert into YJ_YWMX00( ID0000,YJDJH0,ZLXMID ) select SQ_YJ_YWMX00_ID0000.NEXTVAL, YJDJH0, ZLXMID from YJ_YW0000 where YJDJH0=yj.YJDJH0; SF_YJ_YWJJ_NOCOMMIT(yj.YJDJH0,1); end loop; end if; --写入消息推送中间表 by MZSF-20150610-002 /* begin if instr(','||trim(ls_SF_YDZFCZY)||',',','||to_char(ad_GHY000)||',')>0 then insert into YS_JKFSRZ(FSRZID,GHH000,KSMC00,YSXM00,FSZT00,CZRQ00,CZSJ00,FSRZLB) select SQ_YS_JKFSRZ_FSRZID.NextVal,as_GHH000,BMMC00,as_JZYSXM,'0',ls_CZRQ00,ls_CZSJ00,'5' from BM_BMBM00 where BMBH00=ad_GHKS00; end if; exception when others then as_YHMSG0:=''; as_SYSMSG:=''; end; */ --特殊控制OVER! --COMMIT,放在Delphi中控制 exception when others then as_YHMSG0:=nvl(as_YHMSG0,'出错原因不详,请记录此信息并和系统管理员联系！'); as_SYSMSG:=substr(SQLERRM||',执行存储过程错误:SP_SF_BRGHXX('||as_BRBLH0||','||as_YBGHH0||','||as_GHLBMC||','||to_char(ad_BRID00)||','|| 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_YBID00||','||as_YBKH00||','||as_GHRQ00||','||as_SFYYGH||','||as_CZBZ00||','||as_SFSBLF||','|| as_BRZY00||','||as_BRMZ00||','||as_BRZJLX||','||as_BRZJBH||','||as_TYDWMC||','||as_BRJTDZ||','|| 'ad_ZFDJH0,ad_TJDJH0,ad_GRDJH0,as_GHH000,as_YHMSG0,as_SYSMSG)',1,150); ROLLBACK; end SP_SF_BRGHXX;