-- Start of DDL Script for Procedure SD_HOSPITAL.SP_SF_BRGHXX -- Generated 12-八月-2016 10:26:57 from SD_HOSPITAL@tosd_kfclyy CREATE OR REPLACE 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, --出生日期 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_SFZJDK in char default 'N', --门诊医生站-病人就诊是否直接读卡挂号 Y 是 N 否 默认为N as_SFZZ00 in char default '' , --是否社区转诊 as_YGHH00 in char default '0', --预挂号号 as_SFZRZY in char default 'N', --是否择日住院 as_WBBR00 in char default 'N' --未病病人 ) as LS_FYID00 XT_YYXX00.YYID00%type; LS_ID0000 YY_YYQMXB.id0000%type; LS_YYQH01 SF_YYBRB0.YYQH01%type; LS_VIPLB0 SF_YYBRB0.VIPLB0%type; YY_SFSQYYFCJMC XT_XTCS00.VALUE0%type; -- 取某个厂家的是否扣费参数,可定义多个厂家，每个厂家之间用逗号隔开 YYKF char(1);--预约是否扣费 1 扣费 0 不扣费 P_ZXCGBZ number(1); --执行成功标志 1成功 0失败 P_TSXX00 varchar2(225); --执行结果提示信息，成功的情况下也可能有警告信息 P_XXXX00 varchar2(225); --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 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_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 YS_HBHZXH.YYBZ00%type; 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_yjdjh0 YJ_YW0000.YJDJH0%TYPE; ls_FZCOUNT number(3); --分诊计数台 ls_SFXZNXYBBRGFKH XT_XTCS00.VALUE0%TYPE; --参数是否限制男性医保病人挂妇科号 by MZYS-20110223-002 ls_MSGHF0 XT_XTCS00.VALUE0%TYPE; --参数免收挂号费YBLB00 BY MZSF-20110428-001 ls_YBLB00 BM_BRXXB0.YBLB00%TYPE; ls_GHDCZL XT_XTCS00.VALUE0%TYPE; --挂号带出诊疗 ls_GHKSMC BM_BMBM00.BMMC00%TYPE; --挂号科室名称 ls_ZSQGHF char(1);--是否只收取挂号费。 Y 是N 否dbms_output.put_line(' '); YS_ZYBRSFXZMZJZ XT_XTCS00.VALUE0%TYPE;--住院病人是否限制门诊就诊,Y限制 N不限制 默认为Y ls_HBQX00 SF_BRXXB0.HBQX00%type; --号别权限0 不限制 1 限制开处方 YS_PBQJZJDKGHSFYXKCF XT_XTCS00.VALUE0%TYPE;--医生排班期间，直接读卡挂号是否允许开处方 Y允许N不允许 默认为Y ls_kqxzgh char(1);--是否门诊口腔病人未结算，下次需结算才能就诊。Y 是N否 ls_GRSFEN VARCHAR2(20); ls_YYDJH0 number(12); ls_ZJDKGHSFZYHY XT_XTCS00.VALUE0%TYPE; --未在分诊排队病人，在医生站读卡挂号是否占用号源，Y占用N不占用默认为Y ls_SFGHFZLXM varchar2(2); -- ls_YGHID0 SF_BRXXB0.GHID00%type; LS_YYSJ00 SF_HZBR00.YYSJ00%type; LS_YYBZ01 YS_HBHZXH.YYBZ00%type; ls_SFJZLB BM_GHLBB0.SFJZLB%type;--是否急诊类别 急诊挂号:1 非急诊挂号:0 2不分急诊 ls_ZLXMID BM_ZLZD00.ZLXMID%type;--诊疗项目ID ls_SQGHFZLXM VARCHAR2(2); ls_SFLNYYTSCL varchar2(2); --老年医院两费”、“保健”和“福利卡”以及70岁以上病人、本院职工在费别不等于3时，诊察费都以诊疗项目带出 Vzxcgbz number; ls_yybrb0 SF_YYBRB0%ROWTYPE;--预约病人表记录 ls_MSFYLB BM_YBBRLB.MSFYLB%type;--1:免收挂号费 2:免收诊查费 3:免收挂号费和诊查费 ls_SFSQZCF varchar2(1); --是否收取挂号费 1:收取诊查费 0:不收取诊查费 ls_GHZCDCFBBH varchar2(200); --设置挂号费与诊查费以诊疗项目形式带出的费别编号 LS_GHZCDCYBZXLB varchar2(200); --设置挂号费与诊查费以诊疗项目形式带出的医保中心类别 ls_GHDCZLXMCWBR varchar2(1); --挂号带出诊疗项目的排除病人 ls_SFXNH0 IC_YBBRLB.SFXNH0%type;--是否新农合 LS_ZRZYLS number(10); YS_BYZGSFZXSZGH varchar2(10); ls_MRGHLBSFXYGH varchar2(10);--默认挂号类别的挂号是否需要预挂号 Y 需要预挂号 N 不控制 ls_MSZCF0 BM_TYDWB0.MSZCF0%type; ls_YYID01 YS_HBHZXH.YYID00%type; LS_HZDWMC BM_BRXXB0.HZDWMC%type; MZHS_YXHBZYHY varchar2(10); --进行挂号登记限制 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)) and ls_MSZCF0='0'; 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; CURSOR CUR_SF_XMWJZ0 IS select count(1),f.ghrq00 from YJ_YW0000 D,bm_bmbm00 e,sf_brxxb0 f where 1=1 and d.zyhghh=f.ghh000 and d.KDKSBH=e.bmbh00 and d.MZZYBZ='0' and D.XMZT00 in( '1') and nvl(CXDJH0,0)=0 and D.brid00= ad_BRID00 and E.BMMC00 like '%口腔科%' and E.BMXZ00='1' and not exists (select 1 from yj_yw0000 where cxdjh0=d.yjdjh0 and brid00= ad_BRID00) and f.GHRQ00 > to_char(trunc(SysDate-1/4)-GHYXTS-14,'YYYYMMDD' ) group by f.ghrq00; --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 -- CSF 2011.06.20 加参数放开住院期间对门诊病人的挂号限制。 -- CSF 2011.06.23 在医生排班期间直接读卡挂的号，在SF_BRXXB0标识成HBQX00=0(为了在前台控制该号不能开处方) -- qks 2011.06.28 as_ZSQGFF='Y'时，诊察费自动生成医技单，不需要维护“挂号带出诊疗项目”。 -- csf 2011.06.30 加参数控制某些费别的病人不允许门诊就诊 -- csf 2011.07.27 转正式挂号时候如果冲销预挂号，应该先把thbz00改为1，否则MZSF_XZGHTHTS《=0时候会出现错误。 --zhangyc 2011.07.28 修正MZSF-20110214-002 病人在门诊收费处挂一次本院职工医保号后。以后在医生站挂号免收诊疗费，只收挂号费 --jinfl 2011.09.29 门诊口腔科的病人如果在此次诊疗中只开单没结算，那么下次就诊时就需要先到收费处把上次未结的费用结算掉，然后才能在医院进行就诊 --zhangyc 2011.11.04 厦门同安中医院持B卡病人(BM_BRXXB0.GRSFEN='B')挂号时往SF_BRXXB0 插入卡类别 by MZSF-20111101-006 --zhangwz 2011.11.09 挂号新增加时增加传入参数 as_SFZZ00(是否社区转诊) by MZYS-20111026-001. --zhangyc 2011.12.21 增加参数 SF_MGHFYBLB00 维护某类病人免收挂号费(ic_ybbrlb.yblb00) by MZSF-20110428-001 --zhangyc 2012.03.15 在8i下报错(PLS-00123:程序太大),顾把执行广州的代码独立一个过程SP_SF_BRGHXX_GZ by MZSF-20120306-001 -- dsm 2012.07.09 预约记录与正式挂号作关联 for MZYS-20120626-001 -- dsm 2012.07.16 修改SF_YSGHHB0.YGHS00 for MZHS-20120611-002 -- dsm 2012.07.23 转正式挂号时,把预约挂号费的MZID00为0的记录 补填 for YYGH-20120718-001 -- csf 2012.11.20 防止候诊序号重复，在本过程直接判断序号是否已被占用 for MZSF-20121119-001 -- csf 2012.12.05 肺科要求直接读卡挂号的不占用号源。for MZYS-20121203-002 -- dsm 2013.01.29 sf_hzbr00写入YYSJ00,YYBZ00。for MZYS-20130129-001 -- csf 2013.03.01 挂号急诊标志写入SF_BRXXB0.SFJZLB 数据取自BM_GHLBB0.SFJZLB MZSF-20130301-001 -- csf 2013.03.01 厦门市挂号减免 要求只要挂“普通医师诊查”和“（辩证）普通医师诊查”两种号减免1块钱挂号费,同时要求年龄优待的可只按年龄判断 MZSF-20130301-003 -- jinfl 2013.03.28 挂号费以诊疗项目方式带出,进行记账而不直接收费 MZYS-20130322-003 -- jinfl 2013.04.10 MZYS-20130322-003 中如果诊疗项目未维护收费项目，则提示错误 MZSF-20130401-001 -- jinfl 2013.05.09 老年医院70岁以上所有病人和两费、保健病人、本院职工福利卡和职工医保卡挂号费和诊察费以诊疗项目形式带出 MZYS-20130506-001 -- jinfl 2013.07.12 当挂号费和诊察费以诊疗项目形式带出时,添加标志FJDJBZ MZSF-20130709-002 -- jinfl 2013.09.05 重要来人短信通知 归档 SP_SF_BRGHXX -- csf 2013.09.24 预约费的收取改成直接调用预约系统的预约费用登记过程 YYGH-20131012-001 -- zhangyc 2013.11.21 增加控制挂号时免收挂号费和诊查费功能，BM_YBBRLB.MSFYLB:1:免收挂号费 2:免收诊查费 3:免收挂号费和诊查费 by MZYS-20131120-001 -- jinfl 2013.11.28 增加参数控制各费别挂号费和诊察费以诊疗项目形式带出 MZYS-20131126-001 -- dsm 2014.04.18 增加 as_SFZRZY for MZYS-20140418-001 --zhangyc 2014.09.19 择日住院挂号时记录择日流水号 by MZSF-20140917-001 -- dsm 2014.10.21 挂号号带出诊疗项目参数启用的情况下排除农合和城镇保病人 for MZYS-20140930-003 -- dsm 2015.01.20 扩展YS_HSGHXZHZXH=M 时设置成Y for MZHS-20150106-001 -- dsm 2015.02.02 本院职工挂号时,除了显示本院职工号,还要显示其它的号 for MZYS-20150119-002 -- csf 2015.02.04 当参数YS_ZJDKGHSFZYHH=Y,排班医生在医生站直接读卡挂优先号,但号表又没有优先号时候，HZXH00为空,改成为0处理。MZSF-20150204-001 -- dsm 2015.03.16 默认挂号类别的挂号是否需要预挂号(YS_MRGHLBSFXYGH) for MZYS-20150310-004 -- dsm 2015.04.02 YS_MRGHLBSFXYGH=Y时要判断本医生的预挂号，不能只判断科室 for MZYS-20150402-002 -- csf 2015.04.30 预约券功能接入，预约病人正式就诊时候，记录预约券的优先权限，并更改预约券记录的使用标志 YYGH-20150430-001 -- dsm 2015.06.16 泉州中医院养老病人不收诊察费(通过BM_TYDW00.MSZCF0控制) for MZYS-20150525-002\ -- dsm 2015.07.16 不是预约的,直接预挂号的SF_YSGHHB.GHYYHS不增加 for MZHS-20150713-001 -- dsm 2015.07.22 预约券的viplb0写入sf_brxxb0 for MZYS-20150721-002 -- zhangyc 2015.07.31 修改需求MZYS-20150525-002 取值现改成从合作单位取值 by MZSF-20150715-001 -- zhangyc 2015.08.05 增加参数YS_GHFZCFZLXMDCYBZXLB(设置挂号费与诊查费以诊疗项目形式带出的医保中心类别(IC_YBBRLB.YBZXLB)(同参数YS_GHFZCFZLXMDCFB并存))by MZSF-20150610-003 -- dsm 2015.08.24 如果病人已经在护士站预挂号，之后医生直接操作读卡，会在候诊队列中插入一条和预挂号序号一样的记录，如此两条相同记录会导致医生误解及错误操作 for MZYS-20150730-001 -- dsm 2015.09.08 MZHS_YXHBZYHY='Y'时,优先号不占用号源 for MZHS-20150828-002 -- zhangyc 2015.12.08 修改门诊择日费用转住院去掉插入ZRZYLS值 by ZYSF-20151109-001 -- dsm 2015.12.14 增加as_WBBR00参数 for MZYS-20151119-002 -- dsm 2016.03.09 ls_YYBZ00 char(1)定义改成YS_HBHZXH.YYBZ00%tyype for MZHS-20160307-001 -- dsm 2016.05.17 国医堂修改SF_BRXXB0.VIPLB0时,只要根据医生(不要根据可是)获取VIPLB0 for MZYS-20160509-002 BEGIN ls_HBQX00:='0';--号别权限 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_SFSQZCF :='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 :=0; YS_BYZGSFZXSZGH:='Y'; ls_YYID01:=0; MZHS_YXHBZYHY:='N'; LS_ZRZYLS:=0; --本院职工是否限制亲属挂号 begin select trim(value0) into YS_BYZGSFZXSZGH from xt_xtcs00 where name00='YS_BYZGSFZXSZGH' and value0='N'; exception when others then YS_BYZGSFZXSZGH:='Y'; end; 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 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' then --广州独立执行过程 by MZSF-20120306-001 SP_SF_BRGHXX_GZ(as_BRBLH0,as_YBGHH0,as_GHLBMC,ad_BRID00,as_SFCZ00,as_XM0000,as_XB0000,as_CSRQ00,as_FBMC00,as_GFZH00, as_BRDH00,as_BRHYZK,ad_GHY000,as_GHYXM0,ad_CZYKS0,ad_GHKS00,ad_JZYS00,as_JZYSXM,ad_GHHBID,as_YJJXH0, as_FPXH00,ad_GHF000,ad_GRZHZF,ad_TCJJZF,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_SFNLYD,as_HZLB00,as_ICKH00, as_BRJTDZ,ad_FJJZDH,ad_ZFDJH0,ad_TJDJH0,ad_GRDJH0,ad_SBZHDH,ad_SBTJDH,as_GHH000,as_YHMSG0,as_SYSMSG, as_JZSJ00,as_GHXZQK,as_ybzhye,as_SFHSGH,ad_jjdjh0,as_GHSJD0,as_HZXH00,as_ZSQGFF,as_SFZJDK,as_SFZZ00); else --非广州begin --公费病人建卡时是否一定要输入单位 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 YS_PBQJZJDKGHSFYXKCF from xt_xtcs00 where name00='YS_PBQJZJDKGHSFYXKCF'; exception when others then YS_PBQJZJDKGHSFYXKCF:='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_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; --设置挂号费与诊查费以诊疗项目形式带出的费别编号(ps:须YS_SFGHFZLXM和SF_SFCGHSFDCZL参数开启前提),多费别以逗号分隔,默认为3 begin --select nvl(trim(value0),'3') into ls_GHZCDCFBBH from XT_XTCS00 where NAME00='YS_GHFZCFZLXMDCFB'; select trim(value0) into ls_GHZCDCFBBH from XT_XTCS00 where NAME00='YS_GHFZCFZLXMDCFB'; exception when others then ls_GHZCDCFBBH:='3'; end; --设置挂号费与诊查费以诊疗项目形式带出的医保中心类别(IC_YBBRLB.YBZXLB)(同参数YS_GHFZCFZLXMDCFB并存),多值以逗号分隔,默认为空值 begin select trim(value0) into LS_GHZCDCYBZXLB from XT_XTCS00 where NAME00='YS_GHFZCFZLXMDCYBZXLB'; exception when others then LS_GHZCDCYBZXLB:=null; end; --如果YS_GHFZCFZLXMDCYBZXLB没开启时参数YS_GHFZCFZLXMDCFB为空时默认为3(原程序这么取值) if (LS_GHZCDCYBZXLB is null) and (ls_GHZCDCFBBH is null) then ls_GHZCDCFBBH:='3'; end if; --收费处挂号是否带出诊疗项目(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_kqxzgh from xt_xtcs00 where name00='SF_MZKQGHXZ'; exception when others then ls_kqxzgh:='N'; end; begin select trim(value0) into ls_SFGHFZLXM from xt_xtcs00 where name00='YS_SFGHFZLXM'; exception when others then ls_SFGHFZLXM:='N'; end; begin select trim(VALUE0) into ls_MSGHF0 from XT_XTCS00 where name00='SF_MGHFYBLB00'; exception when others then ls_MSGHF0:=NULL; end; begin select substr(trim(VALUE0),1,1) into ls_GHDCZLXMCWBR from XT_XTCS00 where name00='YS_DCZLFYCWBR'; exception when others then ls_GHDCZLXMCWBR:='0'; end; select nvl(max(trim(VALUE0)),'Y') into YS_ZYBRSFXZMZJZ from XT_XTCS00 where NAME00='YS_ZYBRSFXZMZJZ'; begin select substr(trim(VALUE0),1,1) into ls_MRGHLBSFXYGH from XT_XTCS00 where name00='YS_MRGHLBSFXYGH'; exception when others then ls_MRGHLBSFXYGH:='N'; end; --优先号是否暂用号源 MZHS-20150828-002 begin select substr(trim(VALUE0),1,1) into MZHS_YXHBZYHY from XT_XTCS00 where name00='MZHS_YXHBZYHY' and value0='Y'; exception when others then MZHS_YXHBZYHY:='N'; end; begin Select HZDWMC into LS_HZDWMC from BM_BRXXB0 where BRID00=ad_BRID00 and ad_BRID00>0; exception when others then LS_HZDWMC:=null; end; 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岁以上的老人，两费病人自动优待，免除挂号费(判断是否70岁按证件编号来校验) select substrb(nvl(max(trim(value0)),'N'),1,1) into Vlnzdyd from XT_XTCS00 where name00='SF_LNZDYD'; if Vlnzdyd='Y' then select BRCSRQ,nvl(trim(BRZJBH),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; --要求70岁以上的老人，两费病人自动优待，免除挂号费(判断是否70岁只按年龄不用按证件编号来校验) select substrb(nvl(max(trim(value0)),'N'),1,1) into Vlnzdyd from XT_XTCS00 where name00='SF_LNZDYD'; if Vlnzdyd='M' then select BRCSRQ,nvl(trim(BRZJBH),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 ls_XZHZXH='M' then ls_XZHZXH:='Y'; end if; 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; --防止不同病人候诊序号重复 if (ls_XZHZXH='Y' ) and (ls_ghlb00=-99999) and (as_HZXH00>0) and (ad_GHHBID>0) then select count(*) into ls_COUNT0 from SF_HZBR00 where YSPBID=ad_GHHBID and GHLB00=-99999 and HZXH00=as_HZXH00 ; if ls_COUNT0>0 then as_YHMSG0:='候诊序号重复，请重新选择序号！'; as_SYSMSG:=SQLERRM||'候诊序号重复，请重新选择序号！'; ROLLBACK; RETURN; end if; end if; 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 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; 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; 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; --zhr 2010.04.22 医保账户余额不再写入YXQFJE 建阳医院修改余额 --if (ls_YBSFYXQF='Y') then -- begin -- select TYDWMC,fbbh00 into ls_tydw00,ls_brfb00 from bm_brxxb0 where brid00=ad_BRID00; -- exception -- when others then -- ls_tydw00:=''; -- end; -- if ls_BRFB00=3 and (ad_GHY000<>ad_JZYS00) then--收费处挂号的，有传递as_ybzhye,医生站挂号的没有 -- if (ls_tydw00 not in('建阳市本级离休、5.12、二乙人员','建阳市乡镇离休、512人员')) then -- update sf_brzhxx set YXQFJE=as_ybzhye+ls_ICKJE0 where brid00=ad_BRID00; -- end if; -- end if; --end if; update sf_brzhxx set YBZHYE=nvl(as_ybzhye,0) where brid00=ad_BRID00; if YS_ZYBRSFXZMZJZ='Y' then --.判断病人能否挂号,若是住院病人就不能挂号 select count(*) into ls_COUNT0 from ZY_BRXXB0 where BRID00 = ad_BRID00 and (CYRQ00 is null or CYRQ00='20991231'); --未出院的病人 if ls_COUNT0 > 0 then as_YHMSG0:='该病人正在住院,不能挂号,病人挂号登记失败!'; as_SYSMSG:=SQLERRM||'brid00='||to_char(ad_BRID00); ROLLBACK; RETURN; end if; 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; --取是否急诊标志 SELECT NVL(MAX(SFJZLB),'2') into LS_SFJZLB from BM_GHLBB0 where LBBH00=ls_GHLB00; 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; --默认挂号类别的挂号是否需要预挂号 MZYS-20150310-004 if ls_MRGHLBSFXYGH='Y' and ls_ghlb00<>-99999 and instr(as_GHLBMC,'转方')=0 then 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; if ls_ghlb00<>ls_xzghlb_byzg then --select count(*) into ls_COUNT0 from BM_YGBM00 where YGBH00=ad_JZYS00 and MRGHLB=ls_ghlb00; select count(*) into ls_COUNT1 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)); select count(*) into ls_COUNT0 from SF_YSGHHB where GHKSBH=ad_GHKS00 and GHYSBH=ad_JZYS00 and GHMZSJ=to_char(sysdate,'YYYYMMDD') and TZBZ00='0' and (to_char(sysdate,'HH24:MI:SS') between GHQSSJ and GHJSSJ); if ls_COUNT0>0 and ls_COUNT1>0 then --增加 and JZYS00=ad_JZYS00 MZYS-20150402-002 select count(*) into ls_COUNT0 from sf_brxxb0 where brid00=ad_brid00 and ghrq00=to_char(sysdate,'YYYYMMDD') and JZYS00=ad_JZYS00 and JZKS00=ad_GHKS00 and GHLB00=-99999; if ls_COUNT0=0 then as_YHMSG0:='患者没有预挂号，请到收费处挂号再就诊。!'; as_SYSMSG:=SQLERRM; rollback; return; end if; end if; end if; end if; ls_xzghlb_byzg:=0; ls_COUNT1:=0; ls_COUNT0:=0; --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 begin select xmmc00 into ls_xmmc00_1 from bm_yysfxm where sfxmid=ls_SFDM00; exception when others then ls_xmmc00_1:=''; end; end if; */ --3、判断用户输入的病人费别名称是否存在 BEGIN select FBBH00 into ls_FBBH00 from BM_BRFBB0 where FBMC00 = as_FBMC00; EXCEPTION WHEN OTHERS THEN as_YHMSG0:='系统没有找到满足条件的病人费别名称，请重新输入该病人的费别名称!'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; END; select count(*) into ls_COUNT0 from XT_XTCS00 where NAME00='SF_XZMZJZDFB' and instrb(','||trim(VALUE0)||',',','||to_char(LS_FBBH00)||',')>0; if ls_COUNT0>0 then as_YHMSG0:='该费别病人不允许在门诊就诊，请先到收费处处理！'; as_SYSMSG:=SQLERRM||'brid00='||to_char(ad_BRID00); ROLLBACK; RETURN; end if; if (ls_FBBH00=5) and (ls_GHLB00=12) THEN --广州中医院 as_YHMSG0:='省公医的病人不能挂简易门诊'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; end if; if (ls_gfxydw='Y') and (ls_CZBZ00='1') then --公费病人建卡时一定要输入单位 if (ls_FBBH00 not in(1,3)) and (trim(as_TYDWMC)='' or trim(as_TYDWMC) is null) then as_YHMSG0:='公费病人必须输入单位名称'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; end if; end if; --4、判断用户输入的交费类别名称是否存在 IF ls_CZBZ00 = '0' THEN --若是持卡病人(既有预交金账户的病人)挂号登记 BEGIN select JFLBID into ls_LBBH00 from BM_BRJFLB where SYBZ00 = '0'; --使用默认的门诊预交金账户,JFLBMC = as_JFLBMC; EXCEPTION WHEN OTHERS THEN as_YHMSG0:='系统没有找到满足条件的病人的交费类别名称，请重新输入该病人的交费类别名称!'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; END; ELSE ls_LBBH00:=0; --交费类别(或预交金账户类别为0,说明该病人为现金病人) END IF; --5、将病人的挂号费做自动的结算,并得到结算单号 IF ls_LBBH00 = 0 THEN --只有是现金病人的才将挂号费也做结算 select SQ_SF_JZB000_JZDH00.nextval into ls_JZDH00 from dual; else --否则，若是医保的持卡病人，也要立刻结算的 if ad_GRZHZF = 0.0000999 then select SQ_SF_JZB000_JZDH00.nextval into ls_JZDH00 from dual; else ls_JZDH00 := 0; end if; END IF; Select SQ_SF_PJSYQK_PJH000.nextval into ls_PJH000 from dual; --挂号单的票据号 --6、得到门诊病人信息表(门诊挂号号表)的挂号号或病人ID select SQ_SF_BRXXB0_GHID00.nextval into ls_MZID00 from dual; if as_GHH000 is null or trim(as_GHH000) = '' then select SQ_SF_BRXXB0_GHH000.nextval into as_GHH000 from dual; end if; if as_TYDWMC is not null then begin select dwid00 into ls_tydwid from bm_tydwb0 where dwmc00=as_TYDWMC; exception when others then ls_tydwid:=null; end; end if; if ad_BRID00 = 0 then --若病人是现金病人 BEGIN select BRID00 into ls_BRID00 from BM_BRXXB0 where BRBLH0 = as_BRBLH0 and BRXM00=as_XM0000 and YBID00=as_YBID00; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN select count(*) into ls_COUNT0 from BM_BRXXB0 where BRBLH0 = as_BRBLH0; if ls_COUNT0 > 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 --现金病人挂号 Update BM_BRXXB0 SET BRCSRQ = as_CSRQ00, BRZY00 = as_BRZY00, BRMZ00 = as_BRMZ00, BRZJLX = as_BRZJLX, BRZJBH = as_BRZJBH, 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 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; --MZYS-20150525-002 ls_MSZCF0:='0'; --select nvl(max(nvl(MSZCF0,'0')),'0') into ls_MSZCF0 from bm_tydwb0 where DWID00 in(select TYDWID from BM_BRXXB0 where BRID00=ls_BRID00); -- BY MZSF-20150715-001 改成合作单位名称取值 HZDWMC if LS_HZDWMC is not null then select nvl(max(nvl(MSZCF0,'0')),'0') into ls_MSZCF0 from bm_tydwb0 where DWMC00=LS_HZDWMC; 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 (YS_PBQJZJDKGHSFYXKCF='N') and (as_SFZJDK='Y') and (ad_GHHBID>0) and (ls_ghlb00<>-99999) then ls_HBQX00:='1';--医生排班期间直接读卡挂号标识，以便后面控制不能开处方。 end if; if (ad_GHKS00<>0) and (as_GHLBMC<>'0') then -- 如果挂号科室和挂号类别不为空的话 select SQ_SF_HZBR00_ID0000.nextval into ls_HZLBID from dual; --预约病人要多写YYBZ00,YYSJ00 MZYS-20130129-001 LS_YYSJ00:=' '; if ls_HZLB00='3' then begin select YYBZ00,HZQSSJ into LS_YYBZ01,LS_YYSJ00 from YS_HBHZXH where GHHBID=ad_GHHBID and HZXH00=as_HZXH00; LS_YYSJ00:=LS_YYSJ00||':00'; exception when others then LS_YYBZ01:=''; LS_YYSJ00:=' '; end; end if; --end if; Insert into SF_HZBR00(ID0000,BRID00,GHH000,GHKS00,JZKS00,GHYS00,GHLB00,HZLB00,HZXH00,DJRQ00,DJSJ00,HZQSRQ,HZQSSJ,HZJZRQ,HZJZSJ,HZZT00,CZY000,YSPBID,JZZS00,YYBZ00,YYSJ00) 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,'0',ad_GHY000,ad_GHHBID,ls_GHHZCK,LS_YYBZ01,LS_YYSJ00); --MZYS-20120626-001 begin 预约病人的正式挂号记录与预约记录,要求能直接做关联,现有程序是与预挂号做关联,要求改为与正式挂号做关联. if as_YGHH00<>'0' and (ls_ghlb00<>-99999) then begin select GHID00 into ls_YGHID0 from SF_BRXXB0 where GHH000=as_YGHH00 and GHLB00=-99999; exception when others then ls_YGHID0:=0; end; update SF_YYBRB0 set ZSGHID=ls_MZID00 where Brid00=ls_BRID00 and GHHBID=ad_GHHBID and GHID00=ls_YGHID0; end if; --MZYS-20120626-001 end if (ls_XZHZXH='Y') AND (ls_ghlb00=-99999) and not (MZHS_YXHBZYHY='Y' and ls_HZLB00='2') --优先号不暂用号源MZHS-20150828-002 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'),nvl(YYID00,0) INTO ls_YYBZ00,ls_YYID01 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 update SF_BRXXB0 set THBZ00='1' where GHH000=gh.GHH000; 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; --第6个 as_CZBZ00='0' -- update SF_HZBR00 set HZZT00='3' where GHH000=ls_YGHGHH; --MZYS-20150730-001 状态修改掉，否则在候诊页面会出现多条 update SF_HZBR00 set HZZT00='Z' where BRID00=ls_BRID00 and GHLB00=-99999 AND YSPBID=ad_GHHBID and HZXH00=ls_HZXH00; 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 --如果是补挂号的情况 begin 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; --肺科直接读卡挂号(不占用队列号源) select nvl(max(trim(VALUE0)),'Y') into ls_ZJDKGHSFZYHY from XT_XTCS00 where NAME00='YS_ZJDKGHSFZYHH'; if ls_ZJDKGHSFZYHY='Y' then--a 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; else ls_hzxh00:='0'; ls_dlxh00:=-100; update SF_HZBR00 set DLXH00=-100 where ID0000 = ls_HZLBID; end if;--a end if; --如果是补挂号的情况 end end if; Update SF_HZBR00 set HZXH00 =nvl(ls_HZXH00,0), --候诊序号 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'排班时候不指定候诊序号 BEGIN ls_HZSJTD_STR:=trim(SF_YS_HZSJ00(ls_HZLBID,trim(as_jzsj00))); --根据函数得到相关的时间段 --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') into ls_SFDTYY from SF_YYBRB0 where YYID00=as_HZXH00; --修改预约记录为的就诊状态 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) and not (MZHS_YXHBZYHY='Y' and ls_HZLB00='2') and (nvl(as_YGHH00,' ')<>' ') then --MZHS-20150828-002 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 in('3','4','5','6','7') and ls_YYID01>0 then Update SF_YSGHHB SET GHYYHS=NVL(GHYYHS,0)+1 where GHHBID = ad_GHHBID; 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; if ls_ghlb00=-99999 then -- add for MZHS-20120611-002 update SF_YSGHHB set YGHS00=nvl(YGHS00,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; select GRSFEN into ls_GRSFEN from BM_BRXXB0 WHERE BRID00=ls_BRID00; --发现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,HBQX00,GRSFEN,SFZZ00,SFJZLB,SFZRZY,WBBR00) --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,ls_HBQX00,ls_GRSFEN,as_SFZZ00,ls_SFJZLB,as_SFZRZY,as_WBBR00); else 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,HBQX00,GRSFEN,SFZZ00,SFJZLB,SFZRZY,WBBR00) 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_CZRQ00),ls_CZSJ00,ad_GHY000, as_GHYXM0,ad_GHKS00,NVL(trim(substr(ls_HZSJTD_STR,1,8)),ls_CZRQ00),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,ls_HBQX00,ls_GRSFEN,as_SFZZ00,ls_SFJZLB,as_SFZRZY,as_WBBR00); end if; select count(1) into ls_COUNT0 from BM_TYZD00 a where ZDMC00 = '来人提醒医保类别' and BZ0000 = to_char(ls_FBBH00) and exists (select 1 from bm_brxxb0 where BRID00=ls_BRID00 and YBLB00=trim(a.MC0000)); --重要来人短信通知 if (ls_COUNT0>0) then SP_YJ_DXTZ00(ls_BRID00,'来人提醒','挂号',ad_GHY000,ad_GHKS00,'N',Vzxcgbz,as_YHMSG0,as_SYSMSG); end if; /* -----择日住院功能------ if nvl(as_SFZRZY,'N')='Y' then begin select ZRZYLS into LS_ZRZYLS from SF_BRXXB0 where GHID00=ls_MZID00 and BRID00=ad_BRID00; exception when others then LS_ZRZYLS:=null; end; if (LS_ZRZYLS is null) or nvl(LS_ZRZYLS,0)=0 then select SQ_SF_ZRZYXX_ZRZYLS.NEXTVAL into LS_ZRZYLS from dual; update SF_BRXXB0 set ZRZYLS=LS_ZRZYLS where GHID00=ls_MZID00 and BRID00=ad_BRID00; end if; insert into SF_ZRZYXX(ZRZYLS,GHID00,GHH000,BRID00,BRXM00,BRXB00,DJKS00,DJYS00,CZY000,CZKS00,DJRQ00,DJSJ00,ZT0000) values(LS_ZRZYLS,ls_MZID00,as_GHH000,ad_BRID00,as_XM0000,as_XB0000,ad_GHKS00,ad_JZYS00,ad_GHY000,ad_CZYKS0,ls_CZRQ00,ls_CZSJ00,'0'); 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,b.SFXNH0 into ls_YBBRLB,ls_YBZXLB ,ls_SFXNH0 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; ls_SFXNH0:='0'; END; --判定挂号的合法性,只能挂限制的号 select count(*) into ls_COUNT0 from xt_xtcs00 where name00='YJ_YJMRGHLB' and to_number(value0)=ls_ghlb00; 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(*) into ls_count0 from BM_BRXXB0 where BRID00= ls_BRID00 and SFBYZG='1'; end if; if ls_count0>0 and YS_BYZGSFZXSZGH='Y' 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; elsif ls_count0=0 then --普通病人 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' 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 ---by MZYS-20130506-001---老年医院两费”、“保健”和“福利卡”以及70岁以上病人诊察费以诊疗项目带出--begin--- ls_SFLNYYTSCL:='N'; select count(1) into ls_YyCount from xt_yyxx00 where yyid00='220035'; if (ls_MSGHF0 is not null) and (ls_YyCount>0) then begin Select YBLB00 into ls_yblb00 from bm_brxxb0 where brid00=ad_BRID00; exception when others then ls_yblb00:='0'; end; if (Instrb(','||trim(ls_MSGHF0)||',',','||trim(ls_yblb00)||',')>0) then ls_SFLNYYTSCL :='Y'; end if; end if; if (ls_SFLNYD = 'Y' and ls_YDJM00 = 'Y' and ls_YyCount>0) then ls_SFLNYYTSCL := 'Y' ; end if; select count(1) into ls_count0 from bm_ygbm00 where brid00=ls_BRID00; if (ls_count0>0) and (ls_YyCount>0) then--老年医院本院职工 ls_SFLNYYTSCL := 'Y' ; end if; ---by MZYS-20130506-001---老年医院两费”、“保健”和“福利卡”以及70岁以上病人诊察费以诊疗项目带出--end--- select count(1) into v_count from BM_GHFXM0 where LBBH00=ls_GHLB00 and SYBZ00='1'; if (ls_SFGHFZLXM='Y') and ( (instr(','||ls_GHZCDCFBBH||',',','||ls_FBBH00||',')>0) or ((instr(','||LS_GHZCDCYBZXLB||',',','||ls_YBZXLB||',')>0) and (LS_GHZCDCYBZXLB is not null)) ) and (v_count>0) and (ls_GHDCZLXMCWBR='0' or (ls_GHDCZLXMCWBR='1' and ls_YBBRLB<>'C' and ls_SFXNH0<>'1' )) then ls_SQGHFZLXM:='Y'; else ls_SQGHFZLXM:='N'; end if; 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; ---by MZSF-20110428-001---两费”和“保健”病人不收挂号--begin--- if ls_MSGHF0 is not null then begin Select YBLB00 into ls_yblb00 from bm_brxxb0 where brid00=ad_BRID00; exception when others then ls_yblb00:='0'; end; if Instrb(','||trim(ls_MSGHF0)||',',','||trim(ls_yblb00)||',')>0 then ls_SFSQGHF :='0'; else ls_SFSQGHF :='1'; end if; end if; ---by MZSF-20110428-001---两费”和“保健”病人不收挂号--end--- ------by MZYS-20131120-001 --BM_YBBRLB.MSFYLB 1:免收挂号费 2:免收诊查费 3:免收挂号费和诊查费---begin---------- begin select MSFYLB into ls_MSFYLB from BM_YBBRLB where YBZXLB=ls_YBZXLB and YBBRLB=ls_YBBRLB and FBBH00=ls_FBBH00; exception when others then ls_MSFYLB:='0'; end; if (ls_MSFYLB='1') or (ls_MSFYLB='3') then ls_SFSQGHF :='0'; --0:不收挂号费 1:收取挂号费 end if; if (ls_MSFYLB='2') or (ls_MSFYLB='3') then ls_SFSQZCF :='0'; --0:不收挂号费附加费 1:收取挂号费附加费 end if; ------by MZYS-20131120-001 --BM_YBBRLB.MSFYLB 1:免收挂号费 2:免收诊查费 3:免收挂号费和诊查费---end---------- --市皮医院，预挂号收过挂号费的，那么转正式挂号只收诊查费不收挂号费 (按冲销预挂号的做法 ，所以改成正式挂号时候，挂号费和诊查费都收取) --if ls_YGHGHH<>' ' then -- ls_SFSQGHF :='0'; -- end if; if ls_SFSQGHF='1' then if ls_SQGHFZLXM='Y' then select ZLXMID into ls_ZLXMID from BM_GHFXM0 where LBBH00=ls_GHLB00 and SYBZ00='1' and rownum=1; select count(1) into v_count from XT_ZLSFGX where ZLXMID=ls_ZLXMID; if v_count<>1 then as_YHMSG0:='挂号费的诊疗项目只能维护一个收费项目'; as_SYSMSG:=SQLERRM||'未找到数据，select * from XT_ZLSFGX where ZLXMID = '||ls_ZLXMID; ROLLBACK; RETURN; end if; INSERT INTO YJ_YW0000(ZLXMID,SL0000,DW0000,YJKSBH,KDYS00,KDKSBH,KDRQ00,KDSJ00, LRXM00,XMZT00,DCBZ00,YJDJH0,BRID00,ZYGHID,MZZYBZ,FJDJBZ,ZRZYLS ) select ls_ZLXMID,1,DW0000,sf_ys_xmzxks(ls_ZLXMID,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','3',LS_ZRZYLS from VW_BM_ZLZD00 where ZLXMID=ls_ZLXMID; 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; else --自负金额+记帐金额大于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,ZRZYLS) 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),LS_ZRZYLS 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; 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,ZRZYLS) 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),LS_ZRZYLS); 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; /*--=== 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,ZRZYLS) 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),LS_ZRZYLS); --===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中的自付金额，公费金额，先添加一条自付金额+公费金额，再按记账金额添加新记录 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,ZRZYLS) 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),LS_ZRZYLS); --=== 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-- select nvl(YGXZ00,'0') into ls_YGXZ00 from bm_ygbm00 where YGBH00=ad_GHY000; if (instrb(ls_YGXZ00,'3')<=0) and (INSTR('本院职工医保号',as_GHLBMC)>0) then ---非收费，无法挂本院职工医保号---- as_YHMSG0:='挂号类别不对，非收费员无法挂"'||as_GHLBMC||'"!'; as_SYSMSG:='请重新选择挂号类别!'; ROLLBACK; RETURN; end if; begin select LBBH00 into ls_GHLB01 from BM_GHLBB0 where LBMC00 like '本院职工医保号%'; exception when others then ls_GHLB01:=-1; end; if (instrb(ls_YGXZ00,'3')>0) then --收费处挂号都收取-- ls_COUNT2:=0; else select count(1) into ls_COUNT2 from sf_brxxb0 where brid00=ad_BRID00 and ghlb00=ls_GHLB01; 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 ( ( (instr(','||ls_GHZCDCFBBH||',',','||ls_FBBH00||',')>0) or ((instr(','||LS_GHZCDCYBZXLB||',',','||ls_YBZXLB||',')>0) and (LS_GHZCDCYBZXLB is not null)) ) and (trim(ls_GHDCZL)='Y') and (ls_GHKSMC<>'职工体检') and (ls_GHDCZLXMCWBR='0' or (ls_GHDCZLXMCWBR='1' and ls_YBBRLB<>'C' and ls_SFXNH0<>'1' )) ) or (trim(ls_GHDCZL)='Y' and ls_SFLNYYTSCL='Y') 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; elsif ls_GHDCZLXMCWBR='1' then ls_GHDCZL:='N'; end if; select count(1) into ls_YyCount from xt_yyxx00 where yyid00='220035'; --老年医院个人体检有收取挂号附加费,导致单位体检执行不过 if (ls_COUNT1=0) and (as_ZSQGFF<>'Y') and (ls_ZSQGHF<>'Y') and (ls_COUNT2=0) and (ls_FBBH00<>'99' or ls_YyCount=0) and (ls_SFSQZCF='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中的自付金额，公费金额，先添加一条自付金额+公费金额，再按记账金额添加新记录 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' */--=== --===by MZSF-20120306-001 if (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,ZRZYLS) 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),LS_ZRZYLS 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 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; 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、添加挂号费用的记账金额 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; --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、添加挂号附加费的记账金额 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; 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 select count(*) into v_count from ZY_BRXXB0 where BRID00=ad_BRID00 and CYRQ00 is null; if v_count>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; else 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; 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 -- 如果挂号科室和挂号类别不为空的话 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=6), 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=4) 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; --把预约费的GHID00补入 for YYGH-20120718-001 if ls_yyid00>0 then begin select nvl(DJH000,0) into ls_YYDJH0 from SF_YYBRB0 where YYID00=ls_yyid00 and rownum=1; exception when others then ls_YYDJH0:=0; end; if ls_YYDJH0>0 then update SF_BRFY00 set MZID00=ls_MZID00,MZH000=as_GHH000 where DJH000=ls_YYDJH0 and nvl(MZID00,0)=0; 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 --先判断去哪个收费标准 select * into ls_yybrb0 from SF_YYBRB0 where YYID00=ls_yyid00 and YYZT00 IN ('0','1') and (DJH000=0 or DJH000 is null ); --取扣费方式过程 不自动扣费 --取某个厂家的是否扣费参数,可定义多个厂家，每个厂家之间用逗号隔开 begin select trim(VALUE0) into YY_SFSQYYFCJMC from XT_XTCS00 where NAME00='YY_SFSQYYFCJMC'; exception when others then YY_SFSQYYFCJMC:='N'; end; YYKF:=1; if instrb(YY_SFSQYYFCJMC,','||ls_yybrb0.WLYYMC||',')>0 then --指定某个网络厂家不收预约费 YYKF:='0'; end if; if YYKF=1 then SP_YY_YYFYDJ( to_char(sysdate,'YYYYMMDD'),--记账日期 to_char(sysdate,'HH24:MI:SS'),--记账时间 ls_yybrb0.YYKSBH,--预约科室编号 SF_YSGHHB.GHKSBH ls_yybrb0.YYYSBH,--预约医生编号 SF_YSGHHB.GHYSBH ls_yybrb0.BRID00,--病人ID ls_yybrb0.BRXM00,-- 病人姓名 ls_yybrb0.YYYGBH,--预约操作员编号 BM_YGBM00.YGBH00 ls_yybrb0.YYYGXM,--预约员工姓名BM_YGBM00.ZWXM0 ls_yybrb0.CZYBM0,--操作科室 ls_yybrb0.GHHBID, --挂号号表ID ls_yybrb0.WLYYMC, --预约厂家名称 LS_MZID00,--门诊ID P_ZXCGBZ, --执行成功标志 1成功 0失败 P_TSXX00, --执行结果提示信息，成功的情况下也可能有警告信息 P_XXXX00,--执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ls_DJH000 --预约费单据号 ); if P_ZXCGBZ=0 then as_YHMSG0:=substr('收取预约费失败'||P_TSXX00,1,120); as_SYSMSG:=substr('收取预约费失败'||P_TSXX00,1,120); ROLLBACK; RETURN; end if; update SF_YYBRB0 SET DJH000=ls_DJH000 where YYID00=LS_YYID00 and YYZT00 IN ('0','1'); else --有些厂家在外网扣费，院内不收取预约费用，健康之路找之前有扣费的单据号,没找到，直接将单据号改成 -1 select nvl(min(a.djh000),0) into ls_DJH000 from SF_BRFY00 a,SF_FYMX00 b where a.DJH000=b.DJH000 and a.BRID00=ls_yybrb0.BRID00 and b.BEIZHU='健康之路预约费用' and a.MZID00=0 and a.HJJE00>=0 and a.CZRQ00>=to_char(sysdate-7,'YYYYMMDD') and not exists (select 1 from SF_YYBRB0 where BRID00=ls_yybrb0.BRID00 and YYRQ00>=to_char(sysdate-7,'YYYYMMDD') and DJH000=a.DJH000); if LS_DJH000>0 then update SF_YYBRB0 set DJH000=ls_DJH000 where YYID00=LS_YYID00 and YYZT00 IN ('0','1'); update SF_BRFY00 set MZID00=LS_MZID00 where DJH000=ls_DJH000; end if; end if; 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; SELECT NVL(MAX(YYQH01),'0'), NVL(MAX(VIPLB0),'0') into LS_YYQH01,LS_VIPLB0 from SF_YYBRB0 where YYID00=LS_YYID00; --正式挂号，将预约券信息改为使用状态 if LS_YYQH01<>'0' then select NVL(max(YYID00),'0') into LS_FYID00 from BM_BMBM00 where BMBH00=ad_CZYKS0; select ID0000 INTO LS_ID0000 from YY_YYQMXB WHERE YYQH01=LS_YYQH01; SP_YY_YYQXXGL(LS_YYID00,'6',LS_ID0000,'',ad_GHY000,-- ad_CZYKS0,LS_FYID00,'','','','',0,0,0,LS_VIPLB0,'6',as_YHMSG0,as_SYSMSG,P_TSXX00); -- 修改预约券类别 for MZYS-20150721-002 update SF_BRXXB0 set VIPLB0=LS_VIPLB0 where GHID00=ls_MZID00; end if; end if; select count(*) into ls_count0 from XT_XTCS00 where name00='YY_SFSYYYQGN' and VALUE0='Y'; if ls_count0>0 and nvl(ls_yyid00,0)=0 then --MZYS-20160509-002 select NVL(MAX(YYQH01),'0'), NVL(MAX(VIPLB0),'0'),nvl(MAX(YYID00),0) into LS_YYQH01,LS_VIPLB0,ls_YYID00 from SF_YYBRB0 where BRID00=ad_BRID00 and YYYSBH=ad_JZYS00 and YYRQ00=to_char(sysdate,'YYYYMMDD'); if LS_YYQH01<>'0' then select NVL(max(YYID00),'0') into LS_FYID00 from BM_BMBM00 where BMBH00=ad_CZYKS0; select ID0000 INTO LS_ID0000 from YY_YYQMXB WHERE YYQH01=LS_YYQH01; SP_YY_YYQXXGL(LS_YYID00,'6',LS_ID0000,'',ad_GHY000,-- ad_CZYKS0,LS_FYID00,'','','','',0,0,0,LS_VIPLB0,'6',as_YHMSG0,as_SYSMSG,P_TSXX00); -- 修改预约券类别 for MZYS-20150721-002 update SF_BRXXB0 set VIPLB0=LS_VIPLB0 where GHID00=ls_MZID00; end if; end if; --省二人民医院，补收预约费end --15挂号类别自动带出诊疗项目 --邵武医院对医保病人统一挂“医保号”(gllb00=22)，要求系统自动根据医生技术职称类别带出相应的诊疗项目， --其他医院则是直接按医生选择的挂号类别带出相应的诊疗项目 --医保病人收费处挂号是否带出诊疗项目(BM_GHXMDY维护)Y是N否默认N,厦门同安中医院职工体检不要带出 MZSF-20110325-003 select count(*) into ls_count0 from xt_yyxx00 where YYJC00 like '%邵武%人民%'; if (ls_count0=0 and trim(ls_GHDCZL)='N' and ls_GHDCZLXMCWBR='0') or (ls_ghlb00=22 and ls_count0>0) or ( ( (instr(','||ls_GHZCDCFBBH||',',','||ls_FBBH00||',')>0) or ((instr(','||LS_GHZCDCYBZXLB||',',','||ls_YBZXLB||',')>0) and (LS_GHZCDCYBZXLB is not null)) ) and trim(ls_GHDCZL)='Y' and ls_GHKSMC<>'职工体检' ) or (trim(ls_GHDCZL)='Y' and ls_SFLNYYTSCL='Y') 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,FJDJBZ,ZRZYLS ) select zl.ZLXMID,1,DW0000,sf_ys_xmzxks(zl.ZLXMID,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','4',LS_ZRZYLS 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 ls_yjdjh0 := 0; OPEN CUR_SF_FJGHF0_GHF000; LOOP FETCH CUR_SF_FJGHF0_GHF000 INTO ls_FJFFBH; EXIT WHEN CUR_SF_FJGHF0_GHF000%NOTFOUND; if ls_yjdjh0 = 0 then select SQ_YJ_YW0000_YJDJH0.NEXTVAL into ls_yjdjh0 from dual; INSERT INTO YJ_YW0000(ZLXMID,ZLXMJC,SL0000,DW0000,YJKSBH,KDYS00,KDKSBH,KDRQ00,KDSJ00, LRXM00,XMZT00,DCBZ00,YJDJH0,BRID00,ZYGHID,MZZYBZ,BZ0000,ZRZYLS) select 0,'诊察费',1,'次',ad_GHKS00,ad_GHY000,ad_GHKS00,to_char(sysdate,'YYYYMMDD'), to_char(sysdate,'HH24:MI:SS'),as_GHYXM0,'1','0',ls_yjdjh0,ls_BRID00,ls_MZID00,'0','电子钱包挂号',LS_ZRZYLS from dual; end if; v_count:=SF_YJ_YWJJ_ADDTOJJB(ls_yjdjh0,ls_FJFFBH,'N','N','Y',1,'0',0,1,0,'N'); IF v_count=0 THEN as_YHMSG0:='使用“电子钱包”支付诊察费时出错！请联系管理员处理！'; as_SYSMSG:=SQLERRM; ROLLBACK; RETURN; END IF; end loop; CLOSE CUR_SF_FJGHF0_GHF000; end if; --门诊口腔科的病人限制挂号 if ls_kqxzgh='Y' then OPEN CUR_SF_XMWJZ0; LOOP FETCH CUR_SF_XMWJZ0 into ls_COUNT0,ls_GHRQ00; EXIT WHEN CUR_SF_XMWJZ0%NOTFOUND; if ls_COUNT0 > 0 then as_YHMSG0:='该病人于'||ls_GHRQ00||'有在门诊口腔科未记账的项目，不能进行挂号！'; as_SYSMSG:=SQLERRM||'brid00='||to_char(ad_BRID00); ROLLBACK; RETURN; end if; END LOOP; CLOSE CUR_SF_XMWJZ0; end if; end if;--非广州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; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_SF_BRGHXX