CREATE OR REPLACE PROCEDURE sd_hospital.sp_ys_yjxmzddc --本存储过程应用于门诊医生开处方时，根据用法、皮试自动生成对应项目的过程。 ( as_YZCFID in char , --注射相关处方号； ad_ZYGHID in char , --门诊(GHH000)流水号 as_LRXM00 in char , --录入人姓名； as_JZBZ00 in char , --急诊标志'Y'是'N'否； as_ZYCFPD in char --中药处方判定'1'中药处方'0'非中药处方 ) -- MODIFICATION HISTORY -- Person Date Comments -- YANGY 2007.11.12 大输液用药带出项目时，执行的药品数写入YF_MZCFMX表里 -- YANGY 2007.11.15 药房大输液进行寄药处理时，由于寄药科室有可能取不到，现改成由医生工作站来取 -- CSF 2008.02.22 儿科处方的界定方式改由参数控制 -- zhr 2008.06.13 静滴滴注带出，CFID00也要进行填写 -- yangy 2008.06.13 每组N-1次改算法有变，跟频次无关 -- qks 2008.12.03 皮试带出,YJ.YW0000.CFID00也要进行填写 -- CSF 2008.12.03 当一个号有多个带出项目则开单日期被改成了同一天 造成项目费用只收一次 现纠正 -- CSF 2009.06.26 省二偶尔输液类加药卡无法打印 发现yj_yw0000的 yzcfid=null cfid00 is not null ,现在加保护行赋值 -- CSF 2009.10.13 当频次的周期单位为“周”，数量为空值，造成错误。 -- JXQ 2010.01.28 泉州医大附二，西成药一天只开一次 -- csf 2010.03.15 删除第二张处方单每天一次的收费方式, 全部按每组一次收取 -- CSF 2010.04.09 省二人民医院 毒麻中药处方持续天数限制 -- CSF 2010.05.07 特殊病种对应的药品限制 -- zhangwz 2010.05.26 扩展参数YS_EKCFJDFS的值来控制儿科处方的界定，扩展后为:默认为0按年龄(<=15周岁) 1按科室 2按年龄(<=14周岁) -- CSF 2010.07.28 判断是否属于是高血压特殊病种目录，但选择的是糖尿病目录药品，或者反过来，那么则进行提示该药品属于什么病种目录 -- zhr 2010.09.23 参数YS_JMSYDZTZ='Y' 如果只有一组药品，静脉滴注费1天按最高频次收(tid 3次)，如果有多组，静脉滴注费一天只收1次 -- ='N' 静脉滴注费一天固定收1次,='M' 静脉滴注费一天内按最高频次收取; -- 一张处方同时包含静脉输液(精密输液器,参数YS_JMSYYF)和静脉输液(普通)的，由于精密输液器比较贵，所以要优先按静脉输液(精密输液器)定义的诊疗项目带出相应的项目 -- 算法做了修改，参数YS_JMSYDZTZ为M的情况下，一天之内可以同时静脉输液(精密输液器)和静脉输液(普通)。 -- csf 2011.03.15 变量scfid00定义不规范，导致一张处方中同一用法的药品比较多的话，报错出错，现纠正。 -- dsm 2012.09.04 当第二张处方的天数超过第1张处方,都含每日1次带出项目时,不够的要在第二张处方补 for MZYS-20120822-001。 -- csf 2013.04.19 纠正bug,1：当参数YS_JMSYDZTZ启用时候，如果用法为每周几次，并且带出项目又存在多条的情况下，带出几条翻几倍，现纠正。 ----------------------------2：一张处方多组用法时候，从第二组用法带出数量有误，现纠正。 MZYS-20130321-002 -- dsm 2013.05.06 相同皮试项目是否重复带出 for MZYS-20130307-003。 -- CSF 2013.07.12 带出项目按数量汇总时候，开单日期为操作日期 MZYS-20130712-001 -- dsm 2014.04.17 限制时考虑滤特殊病种目录BM_TSBZYL MZYS-20140307-001 -- dsm 2014.06.17 字符串太长，导致前台卡住,所以错误抛出时截取字符串50 for MZYS-20140610-001 -- dsm 2014.07.28 增加YS_CFTZYPXZ(处方同种药品限制) for MZYS-20140721-001 -- dsm 2015.10.15 特殊病种处方带出的治疗费用，要自动设置成特殊病种费用 for MZYS-20150928-002 -- dsm 2016.03.28 增加YS_YTNZYCFBNCF控制中药处方是否允许重复 for MZYS-20160326-002 -- dsm 2016.05.28 一天内中药不能重复开时要除掉被退处方(hjh) for MZYS-20160526-002 -- dsm 2016.06.21 省二当医生设置在BM_TYZD00.ZDMC00='特殊病种开最大药品天数豁免医生'时,不判断中药处方重复限制 for MZYS-20160524-004 -- dsm 2017.08.14 增加附加带出规则"A"每日1次的处理for MZYS-20170811-001 -- dsm 2017.10.24 增加YS_CFFJFKDRQDT参数控制附加费拆分明细时,是否把开单日期写成当天 for MZYS-20171020-001 -- htf 2018.01.15 在YS_YW0000_YJXMCF为Y时，拆分数量大于0的项目改成拆分数量大于1的项目，为了避免有些时候执行皮试后没有皮试结果 for MZYS-20170611-001 -- dsm 2018.01.16 增加药事服务费的处理 for MZYS-20171230-001 -- dsm 2018.02.02 医生的名字含"便民"的不收要事服务费(在BM_TYZD00.ZDMC00='药事服务费'的PYSM00设置成“便民”即可) for MZYS-20180202-001 -- dsm 2018.02.09 如果BM_YGBM00表没有处方的药房人员时会报错误 for MZYS-20180209-001 -- dsm 2018.04.12 普通处方改成特殊病种处方时药事服务费要删除 MZYS-20180412-002 -- dsm 2018.05.15 药事服务费获取药房工作人员时要剔除停止的员工 归档 for BQ-20180507-001 -- dsm 2018.05.30 可以不收药事服务费的科室有多个 for MZYS-20180526-001 -- dsm 2018.06.20 同一张处方的药品性质必须一样YS_JCYPHFJCYPFKCF=Y时 for MZYS-20180620-001 -- dsm 2018.07.06 MZYS-20180620-001需求要BRZTQK<>'城乡居民'的时候才限制 MZYS-20180706-001 -- dsm 2018.08.21 增加参数YS_ZYCFZSPZS控制中药处方至少有几味品种数MZYS-20180806-004 -- hetf 2018.10.16 挂号等级小于0的挂号（如：预防接种服务号），不收取药事服务费。 for MZYS-20181015-002 -- hetf 2018.10.16 挂复诊号时，不收取药事服务费。 for MZYS-20180830-002 -- dsm 2018.11.12 YS_ZYCFZSPZS的控制增加 c.ypczfl=0条件 MZYS-20180926-002 -- dsm 2019.04.29 判读同组的执行次数必须一致 for MZYS-20181203-001 -- huangjy 2019.08.20 增加判断新增处方药品用法是否归属门诊用法 for MZYS-20190726-002 ----------------------------------------------------------------------------------------------------------------------------------------------------- AS Vbrid00 YS_CFXXB0.BRID00%type; Vghid00 YS_CFXXB0.GHID00%type; Vkdys00 YS_CFXXB0.YSGZH0%type; Vkdksbh YS_CFXXB0.GHKS00%type; Vzyts00 YS_CFXXB0.ZYTS00%type; Vyysj00 YS_CFMX00.YYSJ00%type; Vypnm00 YS_CFMX00.YPNM00%type; Vzb0000 YS_CFMX00.CFID00%type; Vzdlczb YS_CFMX00.CFID00%type; --最大疗程对应的组别； Vpcid00 YS_CFMX00.PCID00%type; Vzqcd00 BM_PC0000.ZQCD00%type; Vzqcs00 BM_PC0000.ZQCS00%type; Vzqdw00 BM_PC0000.ZQDW00%type; Vjsxmks BM_BMBM00.BMBH00%type; Vlyxmid MZ_DCXMGZ.LYXMID%type; Vsl0000 YJ_YW0000.SL0000%type; Vzxcs00 YF_MZCFMX.ZXCS00%type; Vcfid00 YJ_YW0000.CFID00%type; Vzdnm00 BM_YD0000.YPNM00%type; --织袋药品内码 Vdcxmcf char(1); --带出项目拆分 Vbzypgs number(4); --该组处方药品个数 counteryps number(4); --皮试带出项目已收费或已执行 Vzdsl00 number(2); --药品剂数量 Vzyfjyp number(10); Vxyfjyp number(10); Vcount0 number(5); Verrmsg varchar2(255); C_CFXZ00 YS_CFXXB0.CFXZ00%TYPE; C_YPSJDJ BM_YD0000.SJDJ00%TYPE; C_BRNL00 NUMBER(5); ls_ZSDSYSFJY char(1); ls_DXSYBZ BM_YD0000.DXSYBZ%TYPE; --是否大输液 VDcxmid MZ_DCXMGZ.DCXMID%type; VEKCFJDFS XT_XTCS00.VALUE0%TYPE; --儿科处方界定方式 E_ERROR0 EXCEPTION; VYPMC00 varchar(500); --药品名称 T_YPMC00 YS_CFMX00.YPMC00%TYPE; V_SQLSTR VARCHAR2(1000); V_KDCS00 number(5); TYPE T_CUR_YS_XYKDCS IS REF CURSOR; --西成药开单次数 CUR_YS_XYKDCS T_CUR_YS_XYKDCS; vZYDMCFCXTS VARCHAR2(500);--中药毒麻处方持续天数限制 v_DMZYCFCXTSXZ number(3); --中药毒麻处方持续天数限制 默认为0 v_ZQDW00 BM_PC0000.zqdw00%type;--周期单位 --特殊病种限制变量 Vtsbzmc bm_tsbzb0.mc0000%type; Vybzxlb ic_ybbrlb.ybzxlb%type; -- Vfbbh00 ic_ybbrlb.fbbh00%type; Vybbrlb bm_ybbrlb.YBBRLB%type; Vtsbzxz char(1); --是否根据特殊病种限制处方和项目，Y:是 N:否 默认为N Vtsbzbh yj_yw0000.bh0000%type; --特殊病种编号 VMLXZBZ bm_tsbzb0.MLXZBZ%type; --目录限制标志'0'不限制'1'只限制药品'2'只限制诊疗'3'都限制 scfid00 YJ_YW0000.CFID00%type;-- VARCHAR2(100); --拆分方式为N，将被合并的cfid00 YS_ZTJXHDCXMID varchar2(20); --嘱托加小壶带出的项目ID Vyfzt00 varchar2(20); --用法嘱托标志 Vjmsyyf varchar2(100);--静脉滴注精密输液器用法 v_JMSYDZTZ CHAR(1); --静脉输液单组时是否进行项目调整 单组时，用每方每疗程的项目替代第二组起每组一次的项目 LS_SL0000 YJ_YW0000.SL0000%type; LS_SL0001 YJ_YW0000.SL0000%type; LS_XTPSBCFDZ varchar2(10);--相同皮试项目不重复带出 LS_DCXMID YJ_YW0000.ZLXMID%type; ls_COU number(10); ls_COU1 number(10); ls_OLDCFID YF_MZCF00.CFLSH0%type; ls_CFTZYPXZ varchar2(10); VYSBH00 BM_YGBM00.YGBH00%type; VcountSL number(5); YS_CFFJFKDRQDT varchar2(10);--处方附加费开单日期是否写成当天,这样就不会漏执行 VZLXMID1 BM_ZLZD00.ZLXMID%type; VZLXMID2 BM_ZLZD00.ZLXMID%type; Vcount2 number(5); Vcount1 number(5); VYJDJH0 YJ_YW0000.YJDJH0%type; VYPDLBH varchar2(2); VYFBMBH YS_CFXXB0.YFBMBH%type; VBMKS001 varchar2(100); VBMKS002 varchar2(100);-- BM_BMBM00.BMBH00%type; VFYR000 BM_YGBM00.YGBH00%type; VYSXM001 BM_TYZD00.PYSM00%type; VCFLSH0 YS_CFXXB0.CFLSH0%type; YS_JCYPHFJCYPFKCF varchar2(10); LS_AYPXZFKJS varchar2(10); YS_ZYCFZSPZS number(5); VYPPZSL number(5); ls_QYYPYFGS XT_XTCS00.VALUE0%TYPE; vYPYFGSTS VARCHAR2(500);--处方药品用法门诊住院归类提示 -- vtsbzmc YJ_YW0000.MC0000%type; --取处方组别 cursor CUR_MZ_DCXMGZ_ZB0000 is select distinct a.YFID00,nvl(a.ZB0000,a.CFID00)ZB0000,a.ZXCS00,a.PCID00,a.YYSJ00, '用法' yfzt00 from YS_CFMX00 a,MZ_DCXMGZ b where a.CFLSH0=as_YZCFID and a.YFID00=b.LYXMID and A.ZXCS00>0 and a.PCID00 is not null union all select distinct '0' YFID00,nvl(a.ZB0000,a.CFID00),a.ZXCS00,a.PCID00,a.YYSJ00, '嘱托' yfzt00 from YS_CFMX00 a where a.CFLSH0=as_YZCFID and A.ZXCS00>0 and a.PCID00 is not null and yyzf00 like '%加小壶%' and YS_ZTJXHDCXMID<>'0' order by ZB0000,YFID00; --取本组对应的CFID00 cursor CUR_CFID00 is select a.CFID00, a.YPNM00, nvl(a.CSL000, 0) csl000, nvl(a.zxcs00, 0) zxcs00, a.zhl000 from YS_CFMX00 a where a.CFLSH0=as_YZCFID and nvl(a.ZB0000,a.CFID00)=Vzb0000; --取本组带出的项目 cursor CUR_MZ_DCXMGZ_DCXMID is select DCXMID,SL0000,CJFS00 from MZ_DCXMGZ where LYXMID=Vlyxmid and CSLY00='0' union all select to_number(YS_ZTJXHDCXMID) DCXMID, 1 SL0000,'a' CJFS00 from dual where Vlyxmid=0 and YS_ZTJXHDCXMID<>'0'; --取本组带出的项目_皮试 cursor CUR_MZ_DCXMGZ_DCXMID_PS is select DCXMID,SL0000,CJFS00 from MZ_DCXMGZ where LYXMID=Vlyxmid and CSLY00='1'; --如果该组别有第二组起每组一次的采集方式(加药) cursor C_JY0000 is select DCXMID,SL0000 from MZ_DCXMGZ where LYXMID=Vlyxmid and CJFS00 in ('5'); --中药处方的整剂用法 cursor CUR_MZ_DCXMGZ_ZJYFID is select distinct a.ZJYFID,nvl(a.ZB0000,999) from YS_CFMX00 a,MZ_DCXMGZ b where a.CFLSH0=as_YZCFID and a.ZJYFID=b.LYXMID ORDER BY nvl(a.ZB0000,999),a.ZJYFID; --皮试项目 cursor CUR_MZ_DCXMGZ_PSLBID is select distinct a.PSLBID,nvl(a.ZB0000,a.CFID00),a.CFID00 from YS_CFMX00 a,MZ_DCXMGZ b where a.CFLSH0=as_YZCFID and a.PSLBID is not null and a.PSLBID=b.LYXMID ORDER BY nvl(a.ZB0000,a.CFID00),a.PSLBID; --本次产生的诊疗单 cursor CUR_YJ_YW0000 is select YJDJH0 from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and MZZYBZ='0' and XMZT00 in ('0','1','5') and DCBZ00 in ('1','2') ; --本次产生的诊疗单(每个带出项目只取最大一条记录) cursor CUR_YJ_YW0001 is select YJDJH0,ZLXMID from YJ_YW0000 a where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and MZZYBZ='0' and XMZT00 in ('0','1','5') and DCBZ00 in ('1','2') and YJDJH0= (select min(YJDJH0) from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and MZZYBZ='0' and XMZT00 in ('0','1','5') and DCBZ00 in ('1','2') and ZLXMID=a.ZLXMID); --本次产生的诊疗单(每个带出项目每组只取最大一条记录) cursor CUR_YJ_YW0002 is select YJDJH0,ZLXMID,CFID00 from YJ_YW0000 a where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and MZZYBZ='0' and XMZT00 in ('0','1','5') and DCBZ00 in ('1','2') and YJDJH0= (select min(YJDJH0) from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and MZZYBZ='0' and XMZT00 in ('0','1','5') and DCBZ00 in ('1','2') and ZLXMID=a.ZLXMID and nvl(CFID00,'0')=nvl(a.CFID00,'0')); --取毒麻处方的中药 cursor CUR_MZ_DMZYCF is Select b.* from YS_CFXXB0 a,YS_CFMX00 b where a.CFLSH0=as_YZCFID and a.CFXZ00>'0' and a.CFLSH0=b.CFLSH0 and b.PCID00 is not null and exists(select 1 from BM_YD0000 c where c.YPNM00=b.YPNM00 and c.LBBH00='2') ; --取特殊病种不允许开的药品 cursor c_tsbz is select e.YPMC00 from YS_CFMX00 e,VW_BM_YBSFDY f where e.CFLSH0=as_YZCFID and e.YPNM00=f.SFXMID and f.SFYP00='Y' and f.FBBH00=Vfbbh00 and f.YBZXLB=Vybzxlb and f.YBBRLB=Vybbrlb and not exists ( select 1 from BM_TSBZXM b, BM_TSBZB0 c where f.YBXMBH=B.XMBH00 AND b.BH0000=C.BH0000 and B.YBZXLB=C.YBZXLB and B.YBZXLB=Vybzxlb and ( B.BH0000=Vtsbzbh or ( exists (select 1 from VW_YS_TSBZB_GXYTNB L where L.BH0000=Vtsbzbh) and B.BH0000 in (select BH0000 from VW_YS_TSBZB_GXYTNB) ) ) and c.MLXZBZ in ('1','3')) and not exists(select 1 from VW_YF_TSBZYLYPMX where BH0000=Vtsbzbh and YBZXLB=Vybzxlb and YPNM00=e.YPNM00); --取处方信息 cursor CUR_MZ_CFXX is select a.CFID00, a.YPNM00,a.YPMC00,a.YFID00 from YS_CFMX00 a where a.CFLSH0=as_YZCFID ; begin Verrmsg:=''; select count(*) into Vcount0 from XT_XTCS00 where NAME00 = 'YS_SFXZXCYYTZKYC' and VALUE0 = 'Y'; select nvl(max(trim(VALUE0)),'N') into YS_CFFJFKDRQDT from XT_XTCS00 where NAME00 = 'YS_CFFJFKDRQDT' and VALUE0 = 'Y'; select nvl(max(trim(VALUE0)),'N') into YS_JCYPHFJCYPFKCF from XT_XTCS00 where NAME00 = 'YS_JCYPHFJCYPFKCF' and VALUE0 = 'Y'; begin select to_number(nvl(max(trim(VALUE0)),'0')) into YS_ZYCFZSPZS from XT_XTCS00 where NAME00 = 'YS_ZYCFZSPZS' ;--中药处方最少品种数：0不限制,>0表示最少品种数，默认0 exception when others then YS_ZYCFZSPZS:=0; end; if YS_ZYCFZSPZS>0 then --2018.11.12增加 c.ypczfl=0条件。MZYS-20180926-002 select count(*) into VYPPZSL from YF_MZCF00 a,YF_MZCFMX b,BM_YD0000 c where a.CFLSH0=as_YZCFID and a.CFLSH0=b.CFLSH0 and b.YPNM00=c.YPNM00 and c.LBBH00=2 and nvl(c.YPCZFL,'0')='0'; if VYPPZSL>0 and VYPPZSL1 then Verrmsg := '此处方中包含基础用药和非基础用药,请分开开'; raise E_ERROR0; end if; end if; end if; --begin 同组的执行次数必须一致 MZYS-20181203-001 select count(*) into Vcount0 from ( select ZB0000,count(*) cou from ( select distinct nvl(ZB0000,' ') ZB0000,ZXCS00 from YF_MZCFMX a where a.CFLSH0=as_YZCFID and nvl(ZB0000,' ')<>' ' ) group by ZB0000) where COU>1; if Vcount0 >0 then Verrmsg := '此处方中包含同组药品但是执行次数不一致的，请检查处方的执行次数'; raise E_ERROR0; end if; --end 同组的执行次数必须一致 MZYS-20181203-001 if Vcount0 > 0 then --西成药一天只开一次 Vcount0 := 0; begin select BRID00 into Vbrid00 from YS_CFXXB0 where GHH000 = ad_ZYGHID and rownum = 1; exception when others then Vbrid00 := NULL; end; if Vbrid00 is not null then V_SQLSTR := 'SELECT a.ypmc00, count(1) KDCS00 FROM ys_cfmx00 a, ys_cfxxb0 b '|| 'WHERE a.cflsh0 = b.cflsh0 AND b.ypdlbh = ''0'' AND b.brid00 = '||Vbrid00|| 'AND b.srrq00 = TO_CHAR (SYSDATE, ''YYYYMMDD'') '|| 'having count(1) > 1 GROUP BY ypnm00, ypmc00'; open CUR_YS_XYKDCS for V_SQLSTR; loop fetch CUR_YS_XYKDCS into T_YPMC00, V_KDCS00; exit when CUR_YS_XYKDCS%NOTFOUND; Vcount0 := Vcount0 + 1; if Vcount0 = 1 then VYPMC00 := '（'||T_YPMC00||'）' ; else VYPMC00 := VYPMC00||'，（'||T_YPMC00||'）'; end if; end loop; if Vcount0 > 0 then Verrmsg := '西成药：'||VYPMC00||'。一天内相同药品不可开多条记录！请检查！'; raise E_ERROR0; end if; end if; end if; select count(*) into Vcount0 from XT_XTCS00 where NAME00 = 'YS_YTNZYCFBNCF' and VALUE0 = 'Y'; if Vcount0 > 0 then --中药处方一天只开一次 Vcount0 := 0; begin select BRID00,YSGZH0 into Vbrid00,VYSBH00 from YS_CFXXB0 where GHH000 = ad_ZYGHID and rownum = 1; exception when others then Vbrid00 := NULL; VYSBH00:=0; end; if VYSBH00>0 then select count(*) into Vcount0 from BM_TYZD00 where ZDMC00='特殊病种开最大药品天数豁免医生' and instr(','||MC0000||',',','||VYSBH00||',')>0; end if; if Vbrid00 is not null and Vcount0=0 then select count(*) into Vcount0 from ys_cfmx00 where CFLSH0=AS_YZCFID; ls_COU:=0; for c_zycfcs in( select CFLSH0 from ( SELECT a.CFLSH0, count(*) YPCOU FROM ys_cfmx00 a, ys_cfxxb0 b WHERE a.cflsh0 = b.cflsh0 AND b.ypdlbh = '2' AND b.brid00 = Vbrid00 AND b.srrq00 = TO_CHAR (SYSDATE, 'YYYYMMDD') and a.YPNM00 in (select YPNM00 from YS_CFMX00 where CFLSH0= AS_YZCFID ) and not exists (select 1 from yf_mzcf00 h where h.cflsh0=b.cflsh0 and h.cfzt00 in ('3','6')) and a.CFLSH0<>AS_YZCFID GROUP BY a.CFLSH0) where YPCOU=Vcount0) loop select count(*) into ls_COU1 from ys_cfmx00 where CFLSH0=c_zycfcs.CFLSH0; if ls_COU1=Vcount0 then if ls_COU = 0 then VYPMC00 := '（'||c_zycfcs.CFLSH0||'）' ; else VYPMC00 := VYPMC00||'，（'||c_zycfcs.CFLSH0||'）'; end if; ls_COU:=ls_COU+1; end if; end loop; if ls_COU > 0 then Verrmsg := '此中药处方重复：'||VYPMC00||'。一天相同中药处方不能重复开！请检查！'; raise E_ERROR0; end if; end if; end if; --毒麻中药处方持续天数限制 select to_number(nvl(max(trim(VALUE0)),'0')) into v_DMZYCFCXTSXZ from XT_XTCS00 where NAME00='YS_DMZYCFCXTSXZ'; if v_DMZYCFCXTSXZ>0 then vZYDMCFCXTS :=' '; for DMZYCFMX in CUR_MZ_DMZYCF loop Verrmsg:='BM_PC0000找不到PCID00='||to_char(DMZYCFMX.PCID00)||'的记录'; select trim(ZQDW00) into v_ZQDW00 from BM_PC0000 where PCID00=DMZYCFMX.PCID00; if (((v_ZQDW00='天') and (DMZYCFMX.YYSJ00>v_DMZYCFCXTSXZ))) or (v_ZQDW00='周') or (((v_ZQDW00='小时') and (DMZYCFMX.YYSJ00>v_DMZYCFCXTSXZ*24))) then if vZYDMCFCXTS=' ' then vZYDMCFCXTS:=vZYDMCFCXTS||DMZYCFMX.YPMC00; else vZYDMCFCXTS:=vZYDMCFCXTS||'、'||DMZYCFMX.YPMC00; end if; end if; end loop; if vZYDMCFCXTS<>' ' then Verrmsg:=vZYDMCFCXTS||'的持续天数不能超过'||to_char(v_DMZYCFCXTSXZ)||'天 '; raise E_ERROR0; end if; end if; begin--'Y' 所有带出项目都拆分成数量为1, 'N'相同项目按处方进行累加 --'T' 默认,相同项目按组别进行累加, 'M':'T'基础上按每天执行拆分 select upper(substrb(VALUE0,1,1)) into Vdcxmcf from XT_XTCS00 where NAME00='YS_YW0000_YJXMCF'; exception when others then Vdcxmcf:='N'; end; select nvl(max(trim(value0)),'0') into YS_ZTJXHDCXMID from xt_xtcs00 where name00='YS_ZTJXHDCXMID'; select nvl(max(trim(value0)),'N') into v_JMSYDZTZ from xt_xtcs00 where name00='YS_JMSYDZTZ'; select nvl(max(trim(VALUE0)),'0') into Vjmsyyf from XT_XTCS00 where name00='YS_JMSYYF'; select trim(NVL(max(value0),'N')) into ls_ZSDSYSFJY FROM xt_xtcs00 where name00='YS_ZSDSYSFJY'; --处方同种药品开多条限制YS_CFTZYPXZ select trim(NVL(max(value0),'N')) into ls_CFTZYPXZ FROM xt_xtcs00 where name00='YS_CFTZYPXZ'; -- 取出儿科处方界定方式 参数 begin select trim(value0) into VEKCFJDFS from xt_xtcs00 where name00='YS_EKCFJDFS'; exception when others then VEKCFJDFS:='0'; --默认按年龄 ‘1’按科室 end; --处方同种药品开多条限制YS_CFTZYPXZ begin if LS_CFTZYPXZ='Y' then Vcount0 := 0; for c_cfyp in ( select YPMC00 from ( select YPNM00,YPMC00,count(*) COU from YS_CFMX00 a where CFLSH0=AS_YZCFID and exists (select 1 from BM_YPYF00 where YFID00=a.YFID00 and YFLBID=0) group by YPNM00,YPMC00 ) where COU>1) loop Vcount0 := Vcount0 +1; if Vcount0=1 then VYPMC00:=c_cfyp.YPMC00; else VYPMC00:=VYPMC00||','||c_cfyp.YPMC00; end if; end loop; if Vcount0>0 then Verrmsg:='处方中同种药品不能重复['||VYPMC00||']'; raise E_ERROR0; end if; end if; --处方同种药品开多条限制YS_CFTZYPXZ end --*******************************特殊病种对应的药品目录限制begin***********************************************************-- select nvl(MAX(TRIM(VALUE0)),'N') INTO Vtsbzxz FROM XT_XTCS00 WHERE NAME00='YS_TSBZXZCFXM'; if Vtsbzxz='M' then Vtsbzxz:='Y'; end if; begin select a.TSBZBH INTO Vtsbzbh from YF_MZCF00 a where CFLSH0=as_YZCFID; select MC0000 into vtsbzmc from BM_TSBZB0 where BH0000=Vtsbzbh and rownum=1; exception when no_data_found then Vtsbzbh:=''; end; if Vtsbzxz='Y' THEN if (trim(Vtsbzbh) is not null) then begin select BRID00 into Vbrid00 from YS_CFXXB0 where GHH000 = ad_ZYGHID and rownum = 1; exception when others then Vbrid00 := NULL; end; if Vbrid00 is not null then select ybzxlb,fbbh00,ybbrlb into Vybzxlb,Vfbbh00,Vybbrlb from VW_YS_BRXXB0 where brid00= Vbrid00; select nvl(max(MLXZBZ),'0'), max(mc0000) into VMLXZBZ, Vtsbzmc from bm_tsbzb0 where bh0000=Vtsbzbh and ybzxlb= Vybzxlb; if VMLXZBZ in ('1','3') then Vcount0 := 0; VYPMC00:=''; for tsbz in c_tsbz loop Vcount0 := Vcount0 +1; if Vcount0 = 1 then VYPMC00 := '（'||tsbz.YPMC00||'）' ; else VYPMC00 := VYPMC00||'，（'||tsbz.YPMC00||'）'; end if; end loop; if Vcount0 > 0 then Verrmsg :=substr('特殊病种"'||trim(Vtsbzmc) ||'"不允许开药品：'||VYPMC00,1,50); raise E_ERROR0; end if; end if; end if; end if; end if; --***************************************特殊病种对应的药品目录限制end***************************************************-- --*******************************自动带出药品方式处理附加费*********************************-- if as_ZYCFPD='1' then --中药处方判定'1'中药处方'0'非中药处方 --医大附二要求根据表XT_ZDSZ00的设置，中药处方自动带出织袋，作为药品处理 begin select TRIM(VALUE0) into Vzdnm00 from XT_XTCS00 where TRIM(UPPER(NAME00))='BM_ZDNM00'; exception when OTHERS then Vzdnm00 :=''; end; if Vzdnm00 is not null then begin --清除旧的织袋费 delete from YS_CFMX00 where CFLSH0=AS_YZCFID and YPNM00=Vzdnm00; delete from YF_MZCFMX where CFLSH0=AS_YZCFID and YPNM00=Vzdnm00; -------------- --带出织袋费 --select NVL(count(1),0) into Vzdsl00 from YS_CFMX00 where CFLSH0=AS_YZCFID and YPNM00=Vzdnm00; select NVL(count(1),0) into Vzdsl00 from XT_ZDSZ00 A ,YS_CFMX00 B where CFLSH0=AS_YZCFID and A.YPNM00=B.YPNM00; if Vzdsl00>0 then ---- insert into YS_CFMX00 (CFID00, CFLSH0, YPNM00, YPMC00,csl000,YPZSL0,jldw00,CFDW00,ZHL000,LSDJ00,YPGG00,XMYFMC ) select (SQ_YS_CFMX00_CFID00.NEXTVAL),AS_YZCFID,Vzdnm00,YPMC00,Vzdsl00,ZYTS00*Vzdsl00,JLDW00,JLDW00,'1',LSJ000,YPGG00,'' from BM_YD0000,YS_CFXXB0 where CFLSH0=AS_YZCFID and YPNM00=Vzdnm00 and ROWNUM=1; insert into YF_MZCFMX(CFID00,CFLSH0,YPNM00,YPMC00,YPGG00,LSDJ00,YPYFMC,PCMC00,CSL000,JLDW00,YYSJ00,YYSJDW,YFSMMC,ZB0000,ZJYFMC,XMYFMC,YPZSL0,CFDW00,ZHL000,CXBZ00,SFXS00,SFZBY0,THBZ00,PSLBID,ZFBL00,YBLB00,BZ0000,ZXCS00) select A.CFID00,A.CFLSH0,A.YPNM00,A.YPMC00,A.YPGG00,A.LSDJ00,B.YPYFMC,C.PCMC00,A.CSL000,A.JLDW00,A.YYSJ00,A.YYSJDW,A.YYZF00,A.ZB0000,B.YPYFMC,A.XMYFMC,A.YPZSL0,A.CFDW00,A.ZHL000,'Z','Y',SFZBY0,'0',A.PSLBID,A.ZFBL00,A.YBLB00,A.YYZF00,ZXCS00 from YS_CFMX00 A,BM_YPYF00 B,BM_PC0000 C where A.YPNM00= Vzdnm00 and A.CFLSH0=AS_YZCFID and A.YFID00=B.YFID00(+) and A.PCID00=C.PCID00(+); UPDATE YS_CFXXB0 SET CFZJE0=(select ROUND(SUM(LSDJ00*YPZSL0),2) from YF_MZCFMX where CFLSH0=AS_YZCFID)where CFLSH0=AS_YZCFID; UPDATE YF_MZCF00 SET CFZJE0=(select ROUND(SUM(LSDJ00*YPZSL0),2) from YF_MZCFMX where CFLSH0=AS_YZCFID)where CFLSH0=AS_YZCFID; end if; exception when OTHERS then Vzdnm00:='9999'; end; end if; --自动带出织袋处理完毕 --增加中药处方附加药品 begin select to_number(value0) into Vzyfjyp from xt_xtcs00 where name00='YF_ZYFJF_YPNM00'; if Vzyfjyp>0 then delete from YS_CFMX00 where CFLSH0=as_YZCFID and ypnm00=Vzyfjyp; delete from YF_MZCFMX where CFLSH0=as_YZCFID and ypnm00=Vzyfjyp; insert into YS_CFMX00 (CFID00, CFLSH0, YPNM00, YPMC00,csl000,YPZSL0,jldw00,CFDW00,ZHL000,LSDJ00,YPGG00,XMYFMC ) select SQ_YS_CFMX00_CFID00.NEXTVAL,as_YZCFID,Vzyfjyp,ypmc00,1,ZYTS00,mzfydw,mzfydw,'1',round(lsj000/k2j000,4),'','' from bm_yd0000 ,yf_mzcf00 where ypnm00=Vzyfjyp and CFLSH0=as_YZCFID; insert into YF_MZCFMX(CFID00,CFLSH0,YPNM00,YPMC00,YPGG00,LSDJ00,YPYFMC,PCMC00,CSL000,JLDW00,YYSJ00,YYSJDW,YFSMMC,ZB0000,ZJYFMC,XMYFMC,YPZSL0,CFDW00,ZHL000,CXBZ00,SFXS00,SFZBY0,THBZ00,PSLBID,ZFBL00,YBLB00,BZ0000,ZXCS00) select a.CFID00,a.CFLSH0,a.YPNM00,a.YPMC00,a.YPGG00,a.LSDJ00,b.YPYFMC,c.PCMC00,a.CSL000,a.JLDW00,a.YYSJ00,a.YYSJDW,a.YYZF00,a.ZB0000,b.YPYFMC,a.XMYFMC,a.YPZSL0,a.CFDW00,a.ZHL000,'Z','Y',SFZBY0,'0',a.PSLBID,a.ZFBL00,a.YBLB00,a.YYZF00,ZXCS00 from YS_CFMX00 a,BM_YPYF00 b,BM_PC0000 c where a.YPNM00= Vzyfjyp and a.CFLSH0=as_YZCFID and a.YFID00=b.YFID00(+) and a.PCID00=c.PCID00(+); Update ys_cfxxb0 set CFZJE0=(select round(sum(LSDJ00*YPZSL0),2) from YF_MZCFMX where CFLSH0=as_YZCFID)where CFLSH0=as_YZCFID; Update YF_MZCF00 set CFZJE0=(select round(sum(LSDJ00*YPZSL0),2) from YF_MZCFMX where CFLSH0=as_YZCFID)where CFLSH0=as_YZCFID; end if; exception when others then Vzyfjyp:=0; end; else --增加西药处方附加费 begin select to_number(value0) into Vxyfjyp from xt_xtcs00 where name00='YF_XCYFJF_YPNM00'; if Vxyfjyp>0 then delete from YS_CFMX00 where CFLSH0=as_YZCFID and ypnm00=Vxyfjyp; delete from YF_MZCFMX where CFLSH0=as_YZCFID and ypnm00=Vxyfjyp; insert into YS_CFMX00 (CFID00, CFLSH0, YPNM00, YPMC00,csl000,YPZSL0,jldw00,CFDW00,ZHL000,LSDJ00,YPGG00,XMYFMC ) select SQ_YS_CFMX00_CFID00.NEXTVAL,as_YZCFID,Vxyfjyp,ypmc00,null,1,mzfydw,mzfydw,'1',round(lsj000/k2j000,4),'','' from bm_yd0000 where ypnm00=Vxyfjyp; insert into YF_MZCFMX(CFID00,CFLSH0,YPNM00,YPMC00,YPGG00,LSDJ00,YPYFMC,PCMC00,CSL000,JLDW00,YYSJ00,YYSJDW,YFSMMC,ZB0000,ZJYFMC,XMYFMC,YPZSL0,CFDW00,ZHL000,CXBZ00,SFXS00,SFZBY0,THBZ00,PSLBID,ZFBL00,YBLB00,BZ0000,ZXCS00) select a.CFID00,a.CFLSH0,a.YPNM00,a.YPMC00,a.YPGG00,a.LSDJ00,b.YPYFMC,c.PCMC00,a.CSL000,a.JLDW00,a.YYSJ00,a.YYSJDW,a.YYZF00,a.ZB0000,b.YPYFMC,a.XMYFMC,a.YPZSL0,a.CFDW00,a.ZHL000,'Z','Y',SFZBY0,'0',a.PSLBID,a.ZFBL00,a.YBLB00,a.YYZF00,ZXCS00 from YS_CFMX00 a,BM_YPYF00 b,BM_PC0000 c where a.YPNM00= Vxyfjyp and a.CFLSH0=as_YZCFID and a.YFID00=b.YFID00(+) and a.PCID00=c.PCID00(+); Update ys_cfxxb0 set CFZJE0=(select round(sum(LSDJ00*YPZSL0),2) from YF_MZCFMX where CFLSH0=as_YZCFID)where CFLSH0=as_YZCFID; Update YF_MZCF00 set CFZJE0=(select round(sum(LSDJ00*YPZSL0),2) from YF_MZCFMX where CFLSH0=as_YZCFID)where CFLSH0=as_YZCFID; end if; exception when others then Vxyfjyp:=0; end; end if; --*******************************以上自动带出药品的处理*********************************-- --*******************************根据药品用法自动带出诊疗项目*********************************-- --初始化参数 select BRID00,GHID00,YSGZH0,GHKS00,ZYTS00 into Vbrid00,Vghid00,Vkdys00,Vkdksbh,Vzyts00 from YS_CFXXB0 where CFLSH0=as_YZCFID; if Vkdksbh=323 then select count(*) into Vcount0 from XT_YYXX00 where YYID00='227049'; --儿科要按标准收静脉滴注费 if Vcount0>0 then v_JMSYDZTZ:='N'; end if; end if; --非中药处方带出项目； if as_ZYCFPD='0' then --删除YJ_YWMX00,YJ_YW0000表含有该处方号未收费项目； delete from YJ_YWMX00 where YJDJH0 in (select YJDJH0 from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and DCBZ00 in ('1') and XMZT00 in ('0','1','5')); delete from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and DCBZ00 in ('1') and XMZT00 in ('0','1','5'); open CUR_MZ_DCXMGZ_ZB0000;--取组 loop fetch CUR_MZ_DCXMGZ_ZB0000 into Vlyxmid,Vzb0000,Vzxcs00,Vpcid00,Vyysj00,Vyfzt00; exit when CUR_MZ_DCXMGZ_ZB0000%notFOUND; --处方每组总个数用于采集方式中N的值； if Vlyxmid <> 0 then select count(CFID00) into Vbzypgs from YS_CFMX00 where CFLSH0=as_YZCFID and YFID00=Vlyxmid and nvl(ZB0000,CFID00)=Vzb0000; else select count(CFID00) into Vbzypgs from YS_CFMX00 where CFLSH0=as_YZCFID and YYZF00 LIKE '%加小壶%' and nvl(ZB0000,CFID00)=Vzb0000; end if; --取本组的CFID00 Vcfid00:=''; for CFID in CUR_CFID00 loop if ls_ZSDSYSFJY = 'Y' THEN--莆田大输液带出时填写寄药数量和寄药科室 select DXSYBZ INTO ls_DXSYBZ FROM BM_YD0000 WHERE YPNM00= CFID.YPNM00; if (ls_DXSYBZ='1') AND (CFID.CSL000>0) AND (CFID.ZXCS00>0) THEN select NVL(max(DCXMID),0) into VDcxmid from MZ_DCXMGZ where LYXMID=Vlyxmid and CSLY00='0'; Vjsxmks:=sf_ys_xmzxks(VDcxmid,Vkdksbh); UPDATE YF_MZCFMX SET JYSL00=ceil(CFID.CSL000*CFID.ZXCS00/CFID.ZHL000), JYKS00 = Vjsxmks WHERE CFID00= CFID.CFID00; END IF; END IF; --DECODE(d.DXSYBZ,''0'',0,''1'', Round(nvl(a.CSL000,0)*a.zxcs00/a.zhl000)) Vcfid00:=Vcfid00||','||CFID.CFID00; end loop; Vcfid00:=Vcfid00||','; --取带出项目 for DCXM in CUR_MZ_DCXMGZ_DCXMID loop --取出频次 Vsl0000:=0; Verrmsg:='BM_PC0000找不到PCID00='||to_char(Vpcid00)||'的记录'; select ZQCD00,ZQCS00,ZQDW00 into Vzqcd00,Vzqcs00,Vzqdw00 from BM_PC0000 where PCID00=Vpcid00; Verrmsg:=''; Vjsxmks:=sf_ys_xmzxks(DCXM.DCXMID,Vkdksbh); if dcxm.CJFS00='4' or dcxm.CJFS00='A' then if v_JMSYDZTZ in ('M','Y') and (Vzqdw00<>'周') then Vsl0000:=Vzxcs00; else Vsl0000:=ceil(Vzxcs00/Vzqcs00); end if; else select decode(dcxm.CJFS00,'2',ceil(Vzxcs00/Vzqcs00),Vzxcs00)*dcxm.SL0000*(decode(dcxm.CJFS00,'0',1,'1',Vbzypgs,'2',Vbzypgs-1,'3',CEIL((Vbzypgs-1)/Vbzypgs),'5',1))into Vsl0000 from dual where rownum=1; end if; --先全部按M的方式进行拆分，插入到医技主表 if Vsl0000>0 then if Vzqdw00='天' then Vzdsl00:=Vsl0000; Vcount0:=0; while Vsl0000>0 loop VcountSL:=0; if dcxm.CJFS00='A' then --VcountSL select count(*) into VcountSL from YJ_YW0000 where BRID00=Vbrid00 and KDRQ00>=to_char(sysdate,'YYYYMMDD') and APSJD0=to_char(sysdate+Vcount0*Vzqcd00,'YYYYMMDD') and ZLXMID=dcxm.DCXMID and DCBZ00 in('1','2'); end if; if VcountSL=0 then insert into YJ_YW0000 (ZLXMID, SL0000,DW0000,YJKSBH, KDYS00, KDKSBH, KDRQ00, KDSJ00, LRXM00,JZBZ00,XMZT00, YZCFID, DCBZ00, YJDJH0, BRID00, ZYGHID,MZZYBZ, CFID00,BZ0000, TSBSMC,BH0000,MC0000,APSJD0 ) select dcxm.DCXMID,decode(dcxm.CJFS00,'A',1,ceil(Vzdsl00/Vyysj00)),a.DW0000,Vjsxmks,Vkdys00,Vkdksbh ,to_char(sysdate+Vcount0*Vzqcd00,'YYYYMMDD') ,to_char(sysdate,'HH24:MI:SS'),as_LRXM00,as_JZBZ00,'0',as_YZCFID,'1',SQ_YJ_YW0000_YJDJH0.NEXTVAL, Vbrid00,Vghid00,'0',Vcfid00,--decode(dcxm.CJFS00,'4',null,Vcfid00), decode(dcxm.CJFS00,'4','每天一次','5','第二组起','A','每日一次','用药带出'),Vlyxmid,vtsbzbh,vtsbzmc ,decode(dcxm.CJFS00,'A',to_char(sysdate+Vcount0*Vzqcd00,'YYYYMMDD'),null) from VW_BM_ZLZD00 a where A.ZLXMID=dcxm.DCXMID; end if; Vsl0000:=Vsl0000-ceil(Vzdsl00/Vyysj00); Vcount0:=Vcount0+1; end loop; elsif Vzqdw00='周' then Vzdsl00:=Vsl0000; VcountSL:=0; if dcxm.CJFS00='A' then --VcountSL select count(*) into VcountSL from YJ_YW0000 where BRID00=Vbrid00 and KDRQ00>=to_char(sysdate,'YYYYMMDD') and APSJD0=to_char(sysdate,'YYYYMMDD') and ZLXMID=dcxm.DCXMID and DCBZ00 in('1','2'); end if; if VcountSL=0 then insert into YJ_YW0000 (ZLXMID, SL0000,DW0000,YJKSBH, KDYS00, KDKSBH, KDRQ00, KDSJ00, LRXM00,JZBZ00,XMZT00, YZCFID, DCBZ00, YJDJH0, BRID00, ZYGHID,MZZYBZ, CFID00,BZ0000, TSBSMC,BH0000,MC0000,APSJD0 ) select dcxm.DCXMID,decode(dcxm.CJFS00,'A',1,ceil(Vzdsl00/Vyysj00)),a.DW0000,Vjsxmks,Vkdys00,Vkdksbh,to_char(sysdate+Vcount0*Vzqcd00,'YYYYMMDD'), to_char(sysdate,'HH24:MI:SS'),as_LRXM00,as_JZBZ00,'0',as_YZCFID,'1',SQ_YJ_YW0000_YJDJH0.NEXTVAL, Vbrid00,Vghid00,'0',Vcfid00,--decode(dcxm.CJFS00,'4',null,Vcfid00), decode(dcxm.CJFS00,'4','每天一次','5','第二组起','A','每日一次','用药带出'),Vlyxmid,vtsbzbh,vtsbzmc ,decode(dcxm.CJFS00,'A',to_char(sysdate+Vcount0*Vzqcd00,'YYYYMMDD'),null) from VW_BM_ZLZD00 a,BM_YZPCMX b where A.ZLXMID=dcxm.DCXMID and b.PCID00=Vpcid00; end if; else VcountSL:=0; if dcxm.CJFS00='A' then --VcountSL select count(*) into VcountSL from YJ_YW0000 where BRID00=Vbrid00 and KDRQ00>=to_char(sysdate,'YYYYMMDD') and APSJD0=to_char(sysdate,'YYYYMMDD') and ZLXMID=dcxm.DCXMID and DCBZ00 in('1','2'); end if; if VcountSL=0 then insert into YJ_YW0000 ( ZLXMID, SL0000,DW0000, YJKSBH, KDYS00, KDKSBH, KDRQ00,KDSJ00, LRXM00, JZBZ00, XMZT00, YZCFID, DCBZ00, YJDJH0,BRID00, ZYGHID, MZZYBZ, CFID00,BZ0000,TSBSMC,BH0000,MC0000,APSJD0) select dcxm.DCXMID,Vsl0000,a.DW0000,Vjsxmks,Vkdys00,Vkdksbh,to_char(sysdate,'YYYYMMDD'), to_char(sysdate,'HH24:MI:SS'),as_LRXM00,as_JZBZ00,'0',as_YZCFID,'1',SQ_YJ_YW0000_YJDJH0.NEXTVAL, Vbrid00,Vghid00,'0',Vcfid00,--decode(dcxm.CJFS00,'4',null,Vcfid00), decode(dcxm.CJFS00,'4','每天一次','5','第二组起','A','每日一次','用药带出'),Vlyxmid,vtsbzbh,vtsbzmc ,decode(dcxm.CJFS00,'A',to_char(sysdate+Vcount0*Vzqcd00,'YYYYMMDD'),null) from VW_BM_ZLZD00 a where A.ZLXMID=dcxm.DCXMID; end if; end if; end if; end loop; end loop; close CUR_MZ_DCXMGZ_ZB0000; --*******************************以上根据药品用法自动带出项目********************************-- --*******************************根据皮试类别自动带出项目********************************-- select nvl(max(trim(value0)),'N') into LS_XTPSBCFDZ from XT_XTCS00 where NAME00='YS_XTPSBCFDZ'; LS_DCXMID:=0; if LS_XTPSBCFDZ='Y' then select nvl(max(DCXMID),0) into LS_DCXMID from MZ_DCXMGZ where CSLY00='1' and LYXMID in(select PSLBID from YF_MZCFMX where CFLSH0=as_YZCFID and PSLBID is not null); end if; select count(YJDJH0) into counteryps from YJ_YW0000 where ZYHGHH=ad_ZYGHID and DCBZ00='2' and XMZT00 in ('2','3','4') and YZCFID=as_YZCFID ; select count(YJDJH0) into counteryps from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and DCBZ00='2'and XMZT00 in ('2','3','4'); if counteryps=0 then--带出的项目都未收费 --删除YJ_YWMX00,YJ_YW0000表含有该处方号未收费项目； delete from YJ_YWMX00 where YJDJH0 in (select YJDJH0 from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and DCBZ00 in ('2') and XMZT00 in ('0','1','5')); delete from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and DCBZ00 in ('2') and XMZT00 in ('0','1','5'); Vcfid00:=''; open CUR_MZ_DCXMGZ_PSLBID; loop fetch CUR_MZ_DCXMGZ_PSLBID into Vlyxmid,Vzb0000,Vcfid00; exit when CUR_MZ_DCXMGZ_PSLBID%notFOUND; ls_COU:=0; if LS_XTPSBCFDZ='Y' then begin select YZCFID into ls_OLDCFID from YJ_YW0000 where ZYHGHH=ad_ZYGHID and DCBZ00='2' and ZLXMID=LS_DCXMID and XMZT00<>'6' and rownum=1; ls_cou:=1; exception when others then ls_COU:=0; end ; if ls_COU=1 then update YF_MZCF00 set PSZT00=(select PSZT00 from YF_MZCF00 where CFLSH0=ls_OLDCFID) where CFLSH0=as_YZCFID; end if; end if; if CUR_MZ_DCXMGZ_PSLBID%ROWcount=1 and ls_COU=0 then --20030907增加皮试带出药品 insert into YF_MZCFMX(CFID00,CFLSH0,YPNM00,YPMC00,YPGG00,LSDJ00, YPZSL0,CFDW00,ZHL000,CXBZ00,SFXS00,SFZBY0,THBZ00,BZ0000) select SQ_YS_CFMX00_CFID00.NEXTVAL,a.CFLSH0,c.YPNM00,b.YPMC00,b.YPGG00,b.LSJ000/b.K2J000*b.M2J000, c.YPZSL0/b.M2J000,b.MZFYDW,b.M2J000,'Z','Y','N','0','皮试带出药品' from YF_MZCFMX a,BM_YD0000 b,XT_PSDCYP c where a.CFLSH0=as_YZCFID and c.YPNM00=b.YPNM00 and a.PSLBID=c.PSID00; --Update YF_MZCF00 set CFZJE0=(select round(sum(LSDJ00*YPZSL0),2)into Vcfzje0 from YF_MZCFMX where CFLSH0=as_YZCFID)where CFLSH0=as_YZCFID; end if; --处方每组总个数用于采集方式中N的值； select count(CFID00) into Vbzypgs from YS_CFMX00 where CFLSH0=as_YZCFID and PSLBID=Vlyxmid and nvl(ZB0000,CFID00)=Vzb0000 and PSLBID is not null; --**************得到项目申请总次数的初始值*************-- --取出用药时间数、取出药品内码； select PCID00,YYSJ00,YPNM00 into Vpcid00,Vyysj00,Vypnm00 from YS_CFMX00 where CFLSH0=as_YZCFID and PSLBID=Vlyxmid and ROWNUM=1 and nvl(ZB0000,CFID00)=Vzb0000 and PSLBID is not null; --**************得到项目申请总次数的初始值************-- for PSDCXM in CUR_MZ_DCXMGZ_DCXMID_PS loop if ls_COU=0 then --取出医技接收科室； Vjsxmks:=sf_ys_xmzxks(PSDCXM.DCXMID,Vkdksbh); --医技主表(插入)； insert into YJ_YW0000(ZLXMID,SL0000,DW0000,YJKSBH,KDYS00,KDKSBH,KDRQ00,KDSJ00, LRXM00,JZBZ00,XMZT00,YZCFID,YPNM00,DCBZ00,YJDJH0,BRID00,ZYGHID,MZZYBZ,CFID00,BH0000,MC0000 ) select C.DCXMID,DECODE(C.CJFS00,'0',1,'1',Vbzypgs,'2',Vbzypgs-1,'3',CEIL((Vbzypgs-1)/Vbzypgs),'5',1), a.DW0000,Vjsxmks,Vkdys00,Vkdksbh,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'), as_LRXM00,as_JZBZ00,'0',as_YZCFID,Vypnm00,'2',SQ_YJ_YW0000_YJDJH0.nextval,Vbrid00,Vghid00,'0',','||Vcfid00||',' ,vtsbzbh,vtsbzmc from VW_BM_ZLZD00 a,MZ_DCXMGZ c where C.LYXMID=Vlyxmid and C.DCXMID=PSDCXM.DCXMID and A.ZLXMID=C.DCXMID and C.CJFS00 not in ('4'); --取出采集方式为每方每疗程一次的医技执行次数； begin select NVL(a.ZB0000,a.CFID00) into Vzdlczb from YS_CFMX00 a,MZ_DCXMGZ b where a.PSLBID=b.LYXMID and CFLSH0=as_YZCFID and a.PSLBID=Vlyxmid and b.CJFS00 in ('4') and ROWNUM=1 and YYSJ00=(select MAX(YYSJ00) from YS_CFMX00 where PSLBID=Vlyxmid and CFLSH0=as_YZCFID); exception when no_data_found then Vzdlczb:=null; end; if Vzb0000=Vzdlczb then begin select YYSJ00 into Vsl0000 from YS_CFMX00 where CFLSH0=as_YZCFID and NVL(ZB0000,CFID00)=Vzdlczb and ROWNUM=1; exception when no_data_found then Vsl0000:=null; end; --医技主表(插入)； if Vsl0000 is not null then insert into YJ_YW0000(ZLXMID,SL0000,DW0000,YJKSBH,KDYS00,KDKSBH,KDRQ00, KDSJ00,LRXM00,JZBZ00,XMZT00,YZCFID,DCBZ00,YJDJH0,BRID00,ZYGHID,MZZYBZ,BH0000,MC0000 ) select C.DCXMID,Vsl0000*C.SL0000,a.DW0000,Vjsxmks,Vkdys00,Vkdksbh,to_char(sysdate,'YYYYMMDD'), to_char(sysdate,'HH24:MI:SS'),as_LRXM00,as_JZBZ00,'0',as_YZCFID,'1',SQ_YJ_YW0000_YJDJH0.NEXTVAL,Vbrid00,Vghid00,'0' ,vtsbzbh,vtsbzmc from VW_BM_ZLZD00 a,MZ_DCXMGZ c where C.LYXMID=Vlyxmid and C.DCXMID=PSDCXM.DCXMID and A.ZLXMID=C.DCXMID and C.CJFS00 in ('4'); end if; end if; end if; end loop; end loop; close CUR_MZ_DCXMGZ_PSLBID; --确保合计金额没有错误 Update YF_MZCF00 set CFZJE0=(select round(sum(round(LSDJ00*YPZSL0,2)),2) from YF_MZCFMX where CFLSH0=as_YZCFID)where CFLSH0=as_YZCFID; end if; --*************以上根据皮试类别自动带出项目**************-- end if; --中药处方带出项目； if as_ZYCFPD='1' then --删除YJ_YWMX00表含有该处方号未收费项目； delete from YJ_YWMX00 where YJDJH0 in (select YJDJH0 from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and DCBZ00='1' and XMZT00 in ('0','1','5')); --删除YJ_YW0000表含有该处方号未收费项目； delete from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and DCBZ00='1' and XMZT00 in ('0','1','5'); open CUR_MZ_DCXMGZ_ZJYFID; loop fetch CUR_MZ_DCXMGZ_ZJYFID into Vlyxmid,Vzb0000; exit when CUR_MZ_DCXMGZ_ZJYFID%notFOUND; --处方每组总个数用于采集方式中N的值； select count(CFID00) into Vbzypgs from YS_CFMX00 where CFLSH0=as_YZCFID and ZJYFID=Vlyxmid; for ZYDCXM in CUR_MZ_DCXMGZ_DCXMID loop --取出医技接收科室； Vjsxmks:=sf_ys_xmzxks(ZYDCXM.DCXMID,Vkdksbh); insert into YJ_YW0000(ZLXMID,SL0000,DW0000,YJKSBH,KDYS00,KDKSBH,KDRQ00,KDSJ00, LRXM00,JZBZ00,XMZT00,YZCFID,DCBZ00,YJDJH0,BRID00,ZYGHID,MZZYBZ,BH0000,MC0000 ) select C.DCXMID,Vzyts00*C.SL0000*(DECODE(C.CJFS00,'0',1,'1',Vbzypgs,'2',Vbzypgs-1,'3',CEIL((Vbzypgs-1)/Vbzypgs),'5',1)), a.DW0000,Vjsxmks,Vkdys00,Vkdksbh,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'), as_LRXM00,as_JZBZ00,'0',as_YZCFID,'1',SQ_YJ_YW0000_YJDJH0.NEXTVAL,Vbrid00,Vghid00,'0' ,vtsbzbh,vtsbzmc from VW_BM_ZLZD00 a,MZ_DCXMGZ c where C.LYXMID=Vlyxmid and C.DCXMID=ZYDCXM.DCXMID and A.ZLXMID=C.DCXMID and C.CJFS00 not in ('4'); --取出采集方式为每方每疗程一次的医技执行次数； begin select NVL(a.ZB0000,999) into Vzdlczb from YS_CFMX00 a,MZ_DCXMGZ b where a.ZJYFID=b.LYXMID and CFLSH0=as_YZCFID and a.ZJYFID=Vlyxmid and b.CJFS00 in ('4') and ROWNUM=1; exception when no_data_found then Vzdlczb:=null; end; if Vzdlczb is not null then insert into YJ_YW0000(ZLXMID,SL0000,DW0000,YJKSBH,KDYS00,KDKSBH,KDRQ00,KDSJ00, LRXM00,JZBZ00,XMZT00,YZCFID,DCBZ00,YJDJH0,BRID00,ZYGHID,MZZYBZ,BH0000,MC0000 ) select C.DCXMID,Vzyts00*C.SL0000,a.DW0000,Vjsxmks,Vkdys00,Vkdksbh,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'), as_LRXM00,as_JZBZ00,'0',as_YZCFID,'1',SQ_YJ_YW0000_YJDJH0.NEXTVAL,Vbrid00,Vghid00,'0',vtsbzbh,vtsbzmc from VW_BM_ZLZD00 a,MZ_DCXMGZ c where C.LYXMID=Vlyxmid and C.DCXMID=ZYDCXM.DCXMID and A.ZLXMID=C.DCXMID and C.CJFS00 in ('4'); end if; end loop; end loop; close CUR_MZ_DCXMGZ_ZJYFID; end if; --删除第二张处方单每天一次的收费方式, 全部按每组一次收取 begin select SL0000 into LS_SL0000 from ( select ZLXMID,sum(SL0000) SL0000 from YJ_YW0000 a where ZYGHID=Vghid00 and MZZYBZ='0' and DCBZ00 in ('1','2') and XMZT00 in('0','1','5') and BZ0000='每天一次' and SL0000>0 and YZCFID0 group by ZLXMID order by SL0000 desc ) where rownum=1; exception when others then LS_SL0001:=-1; end; if LS_SL0001>LS_SL0000 and LS_SL0000>=0 then for CZLXMID in (select distinct ZLXMID from YJ_YW0000 where ZYGHID=Vghid00 and MZZYBZ='0' and YZCFID=as_YZCFID and DCBZ00 in ('1','2') and XMZT00 in('0','1','5') and BZ0000='每天一次' and SL0000>0 ) loop delete YJ_YW0000 where ZYGHID=Vghid00 and MZZYBZ='0' and YZCFID=as_YZCFID and DCBZ00 in ('1','2') and XMZT00 in('0','1','5') and BZ0000='每天一次' and SL0000>0 and ZLXMID=CZLXMID.ZLXMID and rownum<=LS_SL0000; end loop; else delete from YJ_YW0000 a where ZYGHID=Vghid00 and MZZYBZ='0' and YZCFID=as_YZCFID and DCBZ00 in ('1','2') and XMZT00 in('0','1','5') and BZ0000='每天一次' and SL0000>0 and exists (select 1 from YJ_YW0000 where ZYGHID=Vghid00 and MZZYBZ='0' and YZCFID0); end if; --YS_JMSYDZTZ ='Y' 如果只有一组药品，静脉滴注费1天按最高频次收(tid 3次)，如果有多组，静脉滴注费一天只收1次 -- ='N' 静脉滴注费一天固定收1次 -- ='M' 静脉滴注费一天内按最高频次收取 --处理每天一次的静脉滴注费 --2010.03.07 zhr 如果有精密输液器的话，静脉滴注费要优先按精密输液器的来收,算法做了较大调整 declare cursor c_kdrq00 is select TSBSMC,count(distinct CFID00) ZS0000,nvl(max(SL0000),0)SL0000,KDRQ00 from YJ_YW0000 a where ZYGHID=Vghid00 and MZZYBZ='0' and YZCFID=as_YZCFID and DCBZ00 in ('1','2') and XMZT00 in('0','1','5') and BZ0000='每天一次' and SL0000>0 group by KDRQ00,TSBSMC; begin for rq in c_kdrq00 loop Vzdsl00:=rq.SL0000;--每天一次的最高频次 if v_JMSYDZTZ='N' or (rq.ZS0000>1 and v_JMSYDZTZ='Y') then--处理每天一次的静滴费(包括输液器),病人可能当天还有其他的处方 Vzdsl00:=least(1,Vzdsl00); end if; --先处理精密输液 declare cursor c_cfid00 is select distinct CFID00,SL0000 from YJ_YW0000 a where ZYGHID=Vghid00 and MZZYBZ='0' and YZCFID=as_YZCFID and DCBZ00 in ('1','2') and XMZT00 in('0','1','5') and BZ0000='每天一次' and SL0000>0 and KDRQ00=rq.KDRQ00 and TSBSMC=rq.TSBSMC and exists (select 1 from YS_CFMX00 b where CFLSH0=as_YZCFID and instrb(a.CFID00,','||to_char(b.CFID00)||',')>0 and instrb(','||Vjmsyyf||',',','||YFID00||',')>0); begin for cf in c_cfid00 loop Vsl0000:=least(cf.SL0000,Vzdsl00); Vzdsl00:=Vzdsl00-Vsl0000; update YJ_YW0000 a set SL0000=Vsl0000 where ZYGHID=Vghid00 and MZZYBZ='0' and YZCFID=as_YZCFID and DCBZ00 in ('1','2') and XMZT00 in('0','1','5') and BZ0000='每天一次' and SL0000>0 and KDRQ00=rq.KDRQ00 and CFID00=cf.CFID00 ; update YJ_YW0000 a set SL0000=SL0000-Vsl0000 where ZYGHID=Vghid00 and MZZYBZ='0' and YZCFID=as_YZCFID and DCBZ00 in ('1','2') and XMZT00 in('0','1','5') and BZ0000='第二组起' and SL0000>0 and KDRQ00=rq.KDRQ00 and CFID00=cf.CFID00 ; end loop; end; --再处理普通输液 declare cursor c_cfid00 is select distinct CFID00,SL0000 from YJ_YW0000 a where ZYGHID=Vghid00 and MZZYBZ='0' and YZCFID=as_YZCFID and DCBZ00 in ('1','2') and XMZT00 in('0','1','5') and BZ0000='每天一次' and SL0000>0 and KDRQ00=rq.KDRQ00 and TSBSMC=rq.TSBSMC and not exists (select 1 from YS_CFMX00 b where CFLSH0=as_YZCFID and instrb(a.CFID00,','||to_char(b.CFID00)||',')>0 and instrb(','||Vjmsyyf||',',','||YFID00||',')>0); begin for cf in c_cfid00 loop Vsl0000:=least(cf.SL0000,Vzdsl00); Vzdsl00:=Vzdsl00-Vsl0000; update YJ_YW0000 a set SL0000=Vsl0000 where ZYGHID=Vghid00 and MZZYBZ='0' and YZCFID=as_YZCFID and DCBZ00 in ('1','2') and XMZT00 in('0','1','5') and BZ0000='每天一次' and SL0000>0 and KDRQ00=rq.KDRQ00 and CFID00=cf.CFID00; update YJ_YW0000 a set SL0000=SL0000-Vsl0000 where ZYGHID=Vghid00 and MZZYBZ='0' and YZCFID=as_YZCFID and DCBZ00 in ('1','2') and XMZT00 in('0','1','5') and BZ0000='第二组起' and SL0000>0 and KDRQ00=rq.KDRQ00 and CFID00=cf.CFID00 ; end loop; end; end loop; end; update YJ_YW0000 set TSBSMC='' where ZYGHID=Vghid00 and MZZYBZ='0' and YZCFID=as_YZCFID; --###########################特殊处理结束###########################################--- --删除YJ_YW0000表数量为0的项目; delete from YJ_YW0000 where ZYHGHH=ad_ZYGHID and YZCFID=as_YZCFID and DCBZ00 in ('1','2') and SL0000=0 and XMZT00 in('0','1','5') and MZZYBZ='0'; if Vdcxmcf='Y' then --所有带出项目都拆分成数量为1的诊疗单 for YJ in CUR_YJ_YW0000 loop select SL0000 into Vsl0000 from YJ_YW0000 where YJDJH0=yj.YJDJH0; WHILE Vsl0000>1 loop Vsl0000:=Vsl0000-1; insert into YJ_YW0000(ZLXMID,SL0000,DW0000,YJKSBH,KDYS00,KDKSBH,KDRQ00,KDSJ00, LRXM00,JZBZ00,XMZT00,YZCFID,DCBZ00,YJDJH0,BRID00,ZYGHID, MZZYBZ,CFID00,BZ0000,BH0000,MC0000,APSJD0 ) select ZLXMID,1,DW0000,YJKSBH,KDYS00,KDKSBH,decode(YS_CFFJFKDRQDT,'Y',to_char(sysdate,'YYYYMMDD'),KDRQ00),KDSJ00, LRXM00,JZBZ00,XMZT00,YZCFID,DCBZ00,SQ_YJ_YW0000_YJDJH0.NEXTVAL,BRID00,ZYGHID,MZZYBZ,CFID00,BZ0000 ,BH0000,MC0000,APSJD0 from YJ_YW0000 where YJDJH0=yj.YJDJH0; end loop; --删除YJ_YWMX00表含有该处方号未收费项目； delete from YJ_YWMX00 where YJDJH0=yj.YJDJH0 and SL0000<>1; --删除YJ_YW0000表含有该处方号未收费项目； delete from YJ_YW0000 where YJDJH0=yj.YJDJH0 and SL0000<>1; end loop; elsif Vdcxmcf='T' then--按组进行累加 for YJ in CUR_YJ_YW0002 loop select nvl(sum(SL0000),0) into Vsl0000 from YJ_YW0000 where ZYHGHH=ad_ZYGHID AND YZCFID=as_YZCFID AND DCBZ00='1' AND ZLXMID=yj.ZLXMID and MZZYBZ='0' and YJDJH0<>yj.YJDJH0 and nvl(CFID00,0)=nvl(yj.CFID00,0); if Vsl0000>0 then Update YJ_YW0000 set SL0000=SL0000+Vsl0000 where YJDJH0=yj.YJDJH0; --删除YJ_YWMX00表含有该处方号未收费项目； delete from YJ_YWMX00 where YJDJH0 in (select YJDJH0 from YJ_YW0000 where ZYHGHH=ad_ZYGHID AND YZCFID=as_YZCFID AND DCBZ00='1' AND ZLXMID=yj.ZLXMID and MZZYBZ='0' and YJDJH0<>yj.YJDJH0 and nvl(CFID00,'0')=nvl(yj.CFID00,'0')); --删除YJ_YW0000表含有该处方号未收费项目； delete from YJ_YW0000 where ZYHGHH=ad_ZYGHID AND YZCFID=as_YZCFID AND DCBZ00='1' AND ZLXMID=yj.ZLXMID and MZZYBZ='0' and YJDJH0<>yj.YJDJH0 and nvl(CFID00,'0')=nvl(yj.CFID00,'0'); end if; end loop; elsif Vdcxmcf='N' then--按处方进行累加 for YJ in CUR_YJ_YW0001 loop --修改cfid00 scfid00:=''; --for CF in (select distinct CFID00 from YJ_YW0000 where ZYHGHH=ad_ZYGHID AND YZCFID=as_YZCFID AND DCBZ00='1' AND ZLXMID=yj.ZLXMID and MZZYBZ='0') loop for CF in (select distinct A.CFID00 from YS_CFMX00 A,MZ_DCXMGZ C WHERE C.CSLY00='0' AND C.XMLB00='1' AND C.DCXMID=yj.ZLXMID AND A.YFID00=C.LYXMID AND A.CFLSH0=as_YZCFID ) loop scfid00:=scfid00||','||trim(CF.CFID00); end loop; scfid00:=scfid00||','; update YJ_YW0000 set CFID00=scfid00 where YJDJH0=yj.YJDJH0; select nvl(sum(SL0000),0) into Vsl0000 from YJ_YW0000 where ZYHGHH=ad_ZYGHID AND YZCFID=as_YZCFID AND DCBZ00='1' AND ZLXMID=yj.ZLXMID and MZZYBZ='0' and YJDJH0<>yj.YJDJH0; --合并记录 if Vsl0000>0 then Update YJ_YW0000 set SL0000=SL0000+Vsl0000,KDRQ00=to_char(sysdate,'YYYYMMDD') where YJDJH0=yj.YJDJH0; --删除YJ_YWMX00表含有该处方号未收费项目； delete from YJ_YWMX00 where YJDJH0 in (select YJDJH0 from YJ_YW0000 where ZYHGHH=ad_ZYGHID AND YZCFID=as_YZCFID AND DCBZ00='1' AND ZLXMID=yj.ZLXMID and MZZYBZ='0' and YJDJH0<>yj.YJDJH0); --删除YJ_YW0000表含有该处方号未收费项目； delete from YJ_YW0000 where ZYHGHH=ad_ZYGHID AND YZCFID=as_YZCFID AND DCBZ00='1' AND ZLXMID=yj.ZLXMID and MZZYBZ='0' and YJDJH0<>yj.YJDJH0; end if; end loop; end if; --给自动带出的诊疗单估价,插入医技明细表 for YJ in CUR_YJ_YW0000 loop insert into YJ_YWMX00( ID0000,YJDJH0,ZLXMID ) select SQ_YJ_YWMX00_ID0000.NEXTVAL,YJDJH0,ZLXMID from YJ_YW0000 where YJDJH0=yj.YJDJH0; SP_YJ_YWJJ00_NOCOMMIT(yj.YJDJH0,1); end loop; --省二偶尔输液类加药卡无法打印 发现yj_yw0000的 yzcfid=null cfid00 is not null ,现在加保护行赋值。 update YJ_YW0000 a set YZCFID=(select b.CFLSH0 from YS_CFXXB0 b,YS_CFMX00 c where b.GHH000=ad_ZYGHID and b.CFLSH0=c.CFLSH0 and INSTR(a.CFID00,c.CFID00)>0 and rownum=1) where ZYHGHH=ad_ZYGHID and YZCFID IS null and CFID00 IS NOT NULL; select cfxz00 into C_CFXZ00 from ys_cfxxb0 where cflsh0=as_YZCFID; IF C_CFXZ00<>'0' THEN select B.SJDJ00 into C_YPSJDJ from YS_CFMX00 A, BM_YD0000 B WHERE A.YPNM00=B.YPNM00 AND A.CFLSH0=as_YZCFID AND ROWNUM=1; IF C_YPSJDJ<2 THEN UPDATE YS_CFXXB0 SET CFDGS0='0' WHERE CFLSH0=as_YZCFID; UPDATE YF_MZCF00 SET CFDGS0='0' WHERE CFLSH0=as_YZCFID; ELSE UPDATE YS_CFXXB0 SET CFDGS0='1' WHERE CFLSH0=as_YZCFID; UPDATE YF_MZCF00 SET CFDGS0='1' WHERE CFLSH0=as_YZCFID; END IF; ELSE IF VEKCFJDFS='1' Then--'按科室' select count(1) into Vcount0 from BM_BMBM00 A,YS_CFXXB0 B WHERE B.CFLSH0=as_YZCFID and A.BMBH00=B.GHKS00 AND A.BMMC00 LIKE '%儿%'; if Vcount0>0 then C_BRNL00:=1; else C_BRNL00:=16; end if; else --按年龄 SELECT FLOOR((MONTHS_BETWEEN(SYSDATE,TO_DATE(BRCSRQ,'YYYYMMDD')))/12) INTO C_BRNL00 FROM BM_BRXXB0 WHERE BRID00 =(SELECT BRID00 FROM YS_CFXXB0 WHERE CFLSH0=as_YZCFID); end if; select count(1) into Vcount0 from bm_ghlbb0 a, sf_brxxb0 b where a.lbbh00=b.ghlb00 and a.SYXTFL in ('0','1') and b.ghh000=ad_ZYGHID and a.lbmc00 like '%急诊%'; IF (C_BRNL00<=15) and (VEKCFJDFS<>'2') THEN--儿科处方处理 '2'儿科 ‘3‘儿科加急诊 if Vcount0>0 then UPDATE YS_CFXXB0 SET CFDGS0='3' WHERE CFLSH0=as_YZCFID; UPDATE YF_MZCF00 SET CFDGS0='3' WHERE CFLSH0=as_YZCFID; else UPDATE YS_CFXXB0 SET CFDGS0='2' WHERE CFLSH0=as_YZCFID; UPDATE YF_MZCF00 SET CFDGS0='2' WHERE CFLSH0=as_YZCFID; end if; elsif (C_BRNL00<=14) and (VEKCFJDFS='2') then --儿科处方处理 '2'儿科 ‘3‘儿科加急诊 if Vcount0>0 then UPDATE YS_CFXXB0 SET CFDGS0='3' WHERE CFLSH0=as_YZCFID; UPDATE YF_MZCF00 SET CFDGS0='3' WHERE CFLSH0=as_YZCFID; else UPDATE YS_CFXXB0 SET CFDGS0='2' WHERE CFLSH0=as_YZCFID; UPDATE YF_MZCF00 SET CFDGS0='2' WHERE CFLSH0=as_YZCFID; end if; else if Vcount0>0 then UPDATE YS_CFXXB0 SET CFDGS0='4' WHERE CFLSH0=as_YZCFID; UPDATE YF_MZCF00 SET CFDGS0='4' WHERE CFLSH0=as_YZCFID; else UPDATE YS_CFXXB0 SET CFDGS0='5' WHERE CFLSH0=as_YZCFID; UPDATE YF_MZCF00 SET CFDGS0='5' WHERE CFLSH0=as_YZCFID; end if; END IF; END IF; --处理沙县的药事服务费MZYS-20171230-001 --ZDMC00='药事服务费',BZ0000='西药'时,MC0000定义的是西药药事服务费ZLXMID,BH0000定义的是SFXMID --ZDMC00='药事服务费',BZ0000='中药'时,MC0000定义的是中药药事服务费ZLXMID,BH0000定义的是SFXMID VZLXMID1:=0; VZLXMID2:=0; for c_ysfwxm in (select MC0000,BZ0000,PYSM00,WBSM00 from BM_TYZD00 where ZDMC00='药事服务费') loop if c_ysfwxm.BZ0000='门诊西药' then VZLXMID1:=c_ysfwxm.MC0000; begin --VBMKS001:=to_number(c_ysfwxm.PYSM00); --科室 VBMKS001:=','||c_ysfwxm.PYSM00||','; exception when others then VBMKS001:=',0,'; end; VYSXM001:=c_ysfwxm.WBSM00; --医生姓名 elsif c_ysfwxm.BZ0000='门诊中药' then VZLXMID2:=c_ysfwxm.MC0000; begin --VBMKS002:=to_number(c_ysfwxm.PYSM00); VBMKS002:=';'||c_ysfwxm.PYSM00||','; exception when others then VBMKS002:=',0,'; end; VYSXM001:=c_ysfwxm.WBSM00; --医生姓名 end if; end loop; VYPDLBH:=-1; VYFBMBH:=0; select count(*) into ls_COU from SF_BRXXB0 a,BM_GHLBB0 b where a.GHID00=Vghid00 and a.GHLB00=b.LBBH00 and GHDJ00>=0 and LBMC00<>'复诊号'; begin --into VYPDLBH,VYFBMBH --and CFLSH0=as_YZCFID select YPDLBH,YFBMBH,CFLSH0 into VYPDLBH,VYFBMBH,VCFLSH0 from ( select YPDLBH,YFBMBH,CFLSH0 from YS_CFXXB0 a,BM_YGBM00 b where GHID00=Vghid00 and TSBZBH is null --and GHKS00 not in(nvl(VBMKS001,0),nvl(VBMKS002,0)) and instr(VBMKS001,','||GHKS00||',')=0 and instr(VBMKS002,','||GHKS00||',')=0 and a.YSGZH0=b.YGBH00 and ls_COU>0 and nvl(instr(b.ZWXM00,nvl(VYSXM001,' ')),0)<=0 order by YPDLBH desc) where rownum=1 ; exception when others then VYPDLBH:=-1; VYFBMBH:=0; end; if VYFBMBH>0 then begin select YGBH00 into VFYR000 from (select * from BM_YGBM00 where BMBH00=VYFBMBH and CZBZ00<>'2' order by sys_guid()) where rownum < 2; exception when others then VFYR000:= Vkdys00; end; end if; --可以收费服务费的处方 2018.04.12 select count(*) into Vcount2 from YS_CFXXB0 a,BM_YGBM00 b where GHID00=Vghid00 and TSBZBH is null --and GHKS00 not in(nvl(VBMKS001,0),nvl(VBMKS002,0)) and instr(VBMKS001,','||GHKS00||',')=0 and instr(VBMKS002,','||GHKS00||',')=0 and a.YSGZH0=b.YGBH00 and nvl(instr(b.ZWXM00,nvl(VYSXM001,' ')),0)<=0 ; if Vcount2=0 then delete YJ_YWMX00 where YJDJH0 in (select YJDJH0 from YJ_YW0000 where ZYHGHH=ad_ZYGHID and MZZYBZ='0' and nvl(SFDJH0,0)=0 and BZ0000='药事服务' and ZLXMID in(VZLXMID1,VZLXMID2)); delete YJ_YWJJ00 where YJDJH0 in (select YJDJH0 from YJ_YW0000 where ZYHGHH=ad_ZYGHID and MZZYBZ='0' and nvl(SFDJH0,0)=0 and BZ0000='药事服务' and ZLXMID in(VZLXMID1,VZLXMID2)); delete YJ_YW0000 a where ZYHGHH=ad_ZYGHID and MZZYBZ='0' and nvl(SFDJH0,0)=0 and BZ0000='药事服务' and ZLXMID in(VZLXMID1,VZLXMID2); end if; if VYPDLBH in('0','1') and VZLXMID1>0 then --西药处方 --如果存在中药药事服务费,删除 select count(*) into Vcount0 from YJ_YW0000 a where ZYHGHH=ad_ZYGHID and MZZYBZ='0' and ZLXMID in(VZLXMID2) and not exists(select 1 from YJ_YW0000 where ZYGHID=a.ZYGHID and CXDJH0=a.YJDJH0 and ZLXMID=a.ZLXMID); if Vcount0>0 then delete YJ_YWMX00 where YJDJH0 in (select YJDJH0 from YJ_YW0000 where MZZYBZ='0' and ZYGHID=Vghid00 and ZLXMID=VZLXMID2 and XMZT00='1' and SFDJH0=0); delete YJ_YWJJ00 where YJDJH0 in (select YJDJH0 from YJ_YW0000 where MZZYBZ='0' and ZYGHID=Vghid00 and ZLXMID=VZLXMID2 and XMZT00='1' and SFDJH0=0); delete YJ_YW0000 where MZZYBZ='0' and ZYGHID=Vghid00 and ZLXMID=VZLXMID2 and XMZT00='1' and SFDJH0=0; end if; select count(*) into Vcount0 from YJ_YW0000 a where ZYHGHH=ad_ZYGHID and MZZYBZ='0' and ZLXMID in(VZLXMID1,VZLXMID2) and not exists(select 1 from YJ_YW0000 where ZYGHID=a.ZYGHID and CXDJH0=a.YJDJH0 and ZLXMID=a.ZLXMID); if Vcount0=0 then select SQ_YJ_YW0000_YJDJH0.nextval into VYJDJH0 from dual; insert into YJ_YW0000(ZLXMID,SL0000,DW0000,YJKSBH,KDYS00,KDKSBH,KDRQ00,KDSJ00, LRXM00,JZBZ00,XMZT00,YZCFID,DCBZ00,YJDJH0,BRID00,ZYGHID, MZZYBZ,CFID00,BZ0000,BH0000,MC0000 ) select VZLXMID1,1,'次',b.GHKS00,VFYR000,GHKS00,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'), as_LRXM00,'N','1',VCFLSH0,'0',VYJDJH0,b.BRID00,b.GHID00,'0','','药事服务' ,TSBZBH,(select MC0000 from BM_TSBZB0 where BH0000=b.TSBZBH and rownum=1) from YS_CFXXB0 b where b.CFLSH0=as_YZCFID; insert into YJ_YWMX00(ID0000,YJDJH0,ZLXMID ) select SQ_YJ_YWMX00_ID0000.nextval, YJDJH0, ZLXMID from YJ_YW0000 where YJDJH0=VYJDJH0; SF_YJ_YWJJ_NOCOMMIT(VYJDJH0,1); end if; elsif VYPDLBH='2' and VZLXMID2>0 then --中药处方 delete YJ_YWMX00 where YJDJH0 in (select YJDJH0 from YJ_YW0000 where MZZYBZ='0' and ZYGHID=Vghid00 and ZLXMID=VZLXMID1 and XMZT00='1' and SFDJH0=0); delete YJ_YWJJ00 where YJDJH0 in (select YJDJH0 from YJ_YW0000 where MZZYBZ='0' and ZYGHID=Vghid00 and ZLXMID=VZLXMID1 and XMZT00='1' and SFDJH0=0); delete YJ_YW0000 where MZZYBZ='0' and ZYGHID=Vghid00 and ZLXMID=VZLXMID1 and XMZT00='1' and SFDJH0=0; select nvl(sum(decode(ZLXMID,VZLXMID1,1,0)),0) XYCFFW, nvl(sum(decode(ZLXMID,VZLXMID2,1,0)),0) ZYCFFW into Vcount0, Vcount1 from YJ_YW0000 a where ZYGHID=Vghid00 and MZZYBZ='0' and ZLXMID in(VZLXMID1,VZLXMID2) and not exists(select 1 from YJ_YW0000 where ZYGHID=a.ZYGHID and CXDJH0=a.YJDJH0 and ZLXMID=a.ZLXMID); if Vcount0=0 and Vcount1=0 then select SQ_YJ_YW0000_YJDJH0.nextval into VYJDJH0 from dual; insert into YJ_YW0000(ZLXMID,SL0000,DW0000,YJKSBH,KDYS00,KDKSBH,KDRQ00,KDSJ00, LRXM00,JZBZ00,XMZT00,YZCFID,DCBZ00,YJDJH0,BRID00,ZYGHID, MZZYBZ,CFID00,BZ0000,BH0000,MC0000 ) select VZLXMID2,1,'次',b.GHKS00,VFYR000,GHKS00,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'), as_LRXM00,'N','1',VCFLSH0,'0',VYJDJH0,b.BRID00,b.GHID00,'0','','药事服务' ,TSBZBH,(select MC0000 from BM_TSBZB0 where BH0000=b.TSBZBH and rownum=1) from YS_CFXXB0 b where b.CFLSH0=as_YZCFID; insert into YJ_YWMX00(ID0000,YJDJH0,ZLXMID ) select SQ_YJ_YWMX00_ID0000.nextval, YJDJH0, ZLXMID from YJ_YW0000 where YJDJH0=VYJDJH0; SF_YJ_YWJJ_NOCOMMIT(VYJDJH0,1); end if; end if; --YS_QYYPYFGS , for MZYS-20190726-002 begin select Upper(trim(VALUE0)) into ls_QYYPYFGS from XT_XTCS00 where NAME00='YS_QYYPYFGS'; exception when others then ls_QYYPYFGS:='N'; end; if ls_QYYPYFGS='Y'then vYPYFGSTS:=' '; for CFMX in CUR_MZ_CFXX loop select count(*) into ls_COU1 from BM_YPYF00 where YFID00=CFMX.YFID00 and MZZYBZ in ('0','2'); if (ls_COU1<1)and(vYPYFGSTS=' ') then vYPYFGSTS:=vYPYFGSTS||CFMX.YPMC00; elsif (ls_COU1<1)and(vYPYFGSTS<>' ') then vYPYFGSTS:=vYPYFGSTS||'、'||CFMX.YPMC00; end if; end loop; if vYPYFGSTS<>' ' then if length(Verrmsg)>50 then Verrmsg:=substr(Verrmsg,1,46)||'...'; end if; Verrmsg:=vYPYFGSTS||'等药品用法不属于门诊药品用法，请确认'; raise E_ERROR0; end if; end if; exception when E_ERROR0 then --省二会卡住，所以现场改成50才能过 for MZYS-20140610-001 RAISE_APPLICATION_ERROR(-20010, substr(Verrmsg,1, 70)||'!*'); when others then Raise_Application_Error(-20001, substr(Verrmsg||NVL(SQLERRM, '原因不明出错!*'),1,50)); end SP_YS_YJXMZDDC; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_YS_YJXMZDDC