CREATE OR REPLACE FUNCTION SF_YS_LYLJS0 ( p_ypnm00 in number,--药品内码 p_mcsl00 in number,--每次数量 p_z2j000 in number,--转换率 p_pcid00 in number,--频次id /贴数 p_zqs000 in number, --周期数 p_brid00 in number default 0 , --病人ID p_sftsbz in char default 'N', --是否特殊病种 p_f2j000 in number default 1 , --门诊开方单位转换率 p_ghid00 in number default 0 --挂号ID ) --函数返回NUMBER return number as --函数声明 v_return number(12,2);--函数返回值 v_zqcs00 number;--周期次数 v_zqs000 number;--周期数 v_ZQDW00 bm_pc0000.ZQDW00%type; v_HSZQCD bm_pc0000.HSZQCD%type; v_qzfs00 BM_YD0000.MZQZFS%type;--取整方式0每次取整 1每日取整 2每方取整 3不取整 4周期顺延 v_m2j000 bm_yd0000.m2j000%type; v_lbbh00 bm_yd0000.lbbh00%type; v_cfxzts number(5); --处方限制天数 v_dmcfxzts number(5); --毒麻处方限制天数 v_YBXZCFTS char(1); --医保病人是否限制处方最大天数 E_FBZYSFCZ EXCEPTION; v_K2J000 bm_yd0000.k2j000%type;--库存单位转化率 v_fbbh00 bm_brxxb0.fbbh00%type; v_ybzxlb ic_ybbrlb.ybzxlb%type; v_sfxz00 char(1); --是否限制 0:不限制 1：限制 v_sfzxzptybbr char(1);-- v_f2j000 bm_yd0000.f2j000%type; --门诊开方单位转换率 Vcounter number(10);--临时变量 Verrmsg varchar2(255) ;--错误提示变量 Ecustom exception ;--错误变量 ls_YPZSL0 YF_MZCFMX.YPZSL0%type; --总数量 YS_MFQZHZJXFDQZ XT_XTCS00.VALUE0%type;--参数变量 每方取整后，如果M2J000>K2J000 那么转换成K2J000后， --小数位在0.900-0.999 那么整数位+1 小数位在0.001-0.100 那么直接取整数部分。 lv_TSDMYPBZ char(1) ;--特殊毒麻药品标识 lv_TSDMYPXZTS number(5) ;--特殊毒麻药品限制天数 lv_XTCS_Value XT_XTCS00.VALUE0%type;--系统参数值 v_COUNT0 number(5);--临时变量 v_8BBAYPSFXZ varchar2(100);--参数变量 begin -- MODIFICATION HISTORY -- Person Date Comments -- YANGY 2008.03.26 限制一张处方的最大天数 -- YANGY 2008.04.08 医保病人不限制最大天数 -- CSF 2008.04.24 bug:取周期数时没有异常保护，导致错误，现加异常保护 -- YANGY 2008.05.13 ‘离休’病人也不限制最大天数 -- YANGY 2008.07.14 ‘泉州离休’病人也不限制最大天数 -- CSF 2008.11.03 普通医保病人处方天数限制最大天数，特殊医保病人和非医保病人不用限制。 -- YANGY 2009.03.24 门诊医生医生处方录入增加开方单位 -- qks 2009.06.17 新增参数YS_SFXZFNHYBBRZDTS:是否限制非农合医保病人的处方最大天数，>0是，=0否，默认为0 -- qks 2009.12.17 霞浦县医院:急诊处方（诊断上有急性的诊断名称，如急性胃肠炎）不超过3日用量（即不让开） -- csf 2010.03.01 bug:xt_yyxx.yyid00应该是字符型，不能按数字型处理。 -- CSF 2011.01.10 bm_brxxb0,BRID00=0的记录不存在的时候，取病人类别出现错误。 -- CSF 2011.05.25 当k2j000>m2j000时候，每方取整后，再转化成库存单位，如果零头是正负10%那么就近取整数 -- zhangwz 2011.08.01 客户在不启用参数 YS_CFXZZDTS 的情况下想控制毒麻处方开具天数,使用新参数 YS_DMCFXZZDTS 控制 by MZYS-20110711-001. -- zhangwz 2012.03.01 增加参数 YS_TSDMYPCFTSXZ 控制 特殊毒麻药品处方天数限制 by MZYS-20120227-001. -- zhangwz 2012.03.21 对药品的取整方式的读取从 QZFS00变更为 MZQZFS by MZYS-20120306-003。 -- zhangwz 2012.03.28 没有启用YS_CFXZZDTS时对特定费别的处方开具时进行最大天数的限制 by MZYS-20120208-001。 -- daihq 2013.09.12 增加参数YS_TSBZBRZDTS控制是否限制特殊病种病人处方最大天数 by MZYS-20130826-001 -- dsm 2013.09.25 涵江医院要求对需求MZYS-20110419-001进行修改，范围比例向下调整到60%，向上比例不变。 for MZYS-20130924-001 -- daihq 2013.11.08 增加参数YS_8BBAYPSFXZ控制是否限制指定费别才能不限制天数开8标备案药品,若有限制且不在指定费别内的最多只能开7天。 by MZYS-20131105-001 -- ---------- ---------- ---------------------------------------------------------------------- --函数主体 select nvl(max(trim(value0)),'N') into YS_MFQZHZJXFDQZ from XT_XTCS00 where NAME00='YS_MFQZHZJXFDQZ'; if p_mcsl00 is null then--每次数量 return 0; end if; --获取毒麻特殊药品处方天数 begin by MZYS-20120227-001. begin select trim(VALUE0) into lv_XTCS_Value from xt_xtcs00 where name00='YS_TSDMYPCFTSXZ'; exception when others then lv_XTCS_Value:='N'; end; lv_TSDMYPBZ:='N'; lv_TSDMYPXZTS:=0; if instr(';'||trim(lv_XTCS_Value)||';',';'||to_char(p_ypnm00)||',')>0 then --特殊毒麻药品 begin begin lv_TSDMYPBZ:='Y'; select substr(';'||trim(lv_XTCS_Value)||';', instr(';'||trim(lv_XTCS_Value)||';',';'||to_char(p_ypnm00)||',')+1, length(';'||trim(lv_XTCS_Value)||';')-instr(';'||trim(lv_XTCS_Value)||';',';'||to_char(p_ypnm00)||',')+0) into lv_XTCS_Value from dual; --去掉前面的药品 select substr(trim(lv_XTCS_Value),1, decode(sign(instr(trim(lv_XTCS_Value),';')),1,instr(trim(lv_XTCS_Value),';')-1,length(trim(lv_XTCS_Value))-1)) into lv_XTCS_Value from dual; --去掉后面的药品 select to_number(substr(trim(lv_XTCS_Value),instr(trim(lv_XTCS_Value),',')+1,length(trim(lv_XTCS_Value))-instr(trim(lv_XTCS_Value),','))) into lv_TSDMYPXZTS from dual; exception when others then lv_TSDMYPBZ:='N'; lv_TSDMYPXZTS:=0; end; end; end if; --获取毒麻特殊药品处方天数 end by MZYS-20120227-001. if p_z2j000 is null then--转换率 select m2j000,mzqzfs,lbbh00,k2j000 into v_m2j000,v_qzfs00,v_lbbh00,v_k2j000 from bm_yd0000 where ypnm00=p_ypnm00; else v_m2j000:=p_z2j000; select mzqzfs,lbbh00,k2j000 into v_qzfs00,v_lbbh00,v_k2j000 from bm_yd0000 where ypnm00=p_ypnm00; end if; if p_f2j000 is null then--门诊开方单位转换率 select f2j000 into v_f2j000 from bm_yd0000 where ypnm00=p_ypnm00; else v_f2j000:=p_f2j000; end if; if v_lbbh00='2' then v_zqcs00 := 1;--中药不按频次算药量 else if p_pcid00 is null then--周期次数 v_zqcs00 := 1; else begin select zqcs00,trim(ZQDW00),HSZQCD into v_zqcs00,v_ZQDW00,v_HSZQCD from bm_pc0000 where pcid00=p_pcid00; exception WHEN OTHERS THEN v_zqcs00 := 1; end; end if; end if; if p_zqs000 is null then--周期数 v_zqs000 := 1; else v_zqs000 := p_zqs000; end if; --霞浦县医院 select count(1) into Vcounter from xt_yyxx00 where yyid00='228766'; if (Vcounter>0) and (v_zqs000*v_HSZQCD > 3*24) then select count(1) into Vcounter from ys_brzdxx where ghid00=p_ghid00 and zdmc00 like '%急性%'; if Vcounter>0 then Verrmsg:='急性诊断用药时间不能超过3天'; Raise Ecustom; return 0; end if; end if; --获取特殊病种病人处方最大天数 begin select to_number(nvl(max(trim(VALUE0)),0)) into v_cfxzts from xt_xtcs00 where name00='YS_TSBZBRZDTS'; exception when others then v_cfxzts:=0; end; if (v_cfxzts > 0) and (p_sftsbz='Y') then if v_zqs000*v_HSZQCD > v_cfxzts*24 then raise E_FBZYSFCZ; return 0; end if; end if; --取出参数 莆二 begin select to_number(nvl(max(value0),0)) into v_cfxzts from xt_xtcs00 where name00='YS_SFXZFNHYBBRZDTS'; exception when others then v_cfxzts:=0; end; if (v_cfxzts>0) and (p_brid00>0) then select b.fbbh00, C.YBZXLB into v_fbbh00, v_ybzxlb from bm_brxxb0 b,IC_YBBRLB c,BM_YBBRLB d where b.YBLB00 = c.YBLB00 and b.FBBH00 = c.FBBH00 and b.YBBRLB =d.YBBRLB and D.YBZXLB = C.YBZXLB AND C.FBBH00=D.FBBH00 and b.brid00 = p_brid00; if (v_fbbh00=3) and (v_ybzxlb<>'@') and (v_lbbh00<>'2') and (p_pcid00 is not null) and (p_zqs000 is not null) then--周期次数 if v_zqs000*v_HSZQCD > v_cfxzts*24 then raise E_FBZYSFCZ; return 0; end if; end if; end if; --取出参数 是否只限制普通的医保病人 罗源县医院处方最大天数限制方式 begin select to_number(nvl(max(value0),0)) into v_cfxzts from xt_xtcs00 where name00='YS_SFZXZPTYBBRZDTS'; exception when others then v_cfxzts:=0; end; if (v_cfxzts>0) and (p_brid00>0) then --只限制普通的医保病人 select b.fbbh00, C.YBZXLB into v_fbbh00, v_ybzxlb from bm_brxxb0 b,IC_YBBRLB c,BM_YBBRLB d where b.YBLB00 = c.YBLB00 and b.FBBH00 = c.FBBH00 and b.YBBRLB =d.YBBRLB and D.YBZXLB = C.YBZXLB AND C.FBBH00=D.FBBH00 and b.brid00 = p_brid00; if (p_sftsbz='N') and (v_fbbh00=3) and (v_lbbh00<>'2') and (p_pcid00 is not null) and (p_zqs000 is not null) then--周期次数 if v_zqs000*v_HSZQCD > v_cfxzts*24 then raise E_FBZYSFCZ; return 0; end if; end if; else --限制所用病人 但医保病人是否限制按参数 select to_number(nvl(max(value0),0)) into v_cfxzts from xt_xtcs00 where name00='YS_CFXZZDTS'; if v_cfxzts>0 then v_sfxz00 := '1'; select nvl(max(trim(value0)),'Y') into v_YBXZCFTS from xt_xtcs00 where name00='YS_YBXZCFTS'; if (v_YBXZCFTS = 'N') and (p_brid00 <> 0) then select b.fbbh00, C.YBZXLB into v_fbbh00, v_ybzxlb from bm_brxxb0 b,IC_YBBRLB c,BM_YBBRLB d where b.YBLB00 = c.YBLB00 and b.FBBH00 = c.FBBH00 and b.YBBRLB =d.YBBRLB and D.YBZXLB = C.YBZXLB AND C.FBBH00=D.FBBH00 and b.brid00 = p_brid00; if (v_fbbh00=3) or (v_ybzxlb='3') or (v_ybzxlb='x') or (v_ybzxlb='4')then v_sfxz00 := '0'; --如果是医保病人，不限制 end if; end if; if (v_sfxz00 = '1') and (v_lbbh00<>'2') and (p_pcid00 is not null) and (p_zqs000 is not null) then--周期次数 if v_zqs000*v_HSZQCD > v_cfxzts*24 then raise E_FBZYSFCZ; return 0; end if; end if; end if; end if; --取出参数 莆田市涵江医院 by MZYS-20110711-001. begin select to_number(nvl(max(value0),0)) into v_dmcfxzts from xt_xtcs00 where name00='YS_DMCFXZZDTS'; exception when others then v_dmcfxzts:=0; end; if (v_dmcfxzts>0) and (p_brid00>0) then --VW_YS_DMYPSR VW_YS_FDMYPSR 判断毒麻处方不另起传入参数，以前台检索的视图的区别来进行. select count(*) into Vcounter from BM_YD0000 where YPNM00=p_ypnm00 and (MZDJ00<>'0' or DPDJ00<>'0' or SJDJ00<>'0' ); if (Vcounter>0) and (lv_TSDMYPBZ='N') and (v_lbbh00<>'2') and (p_pcid00 is not null) and (p_zqs000 is not null) then--周期次数 begin if v_zqs000*v_HSZQCD > v_dmcfxzts*24 then v_cfxzts:=v_dmcfxzts; --供提示显示用 raise E_FBZYSFCZ; return 0; end if; end; end if; end if; if (lv_TSDMYPBZ='Y') and (lv_TSDMYPXZTS>0) and (p_brid00>0) then --特殊毒麻药品处方天数限制 by MZYS-20120227-001 begin select count(*) into Vcounter from BM_YD0000 where YPNM00=p_ypnm00 and (MZDJ00<>'0' or DPDJ00<>'0' or SJDJ00<>'0' ); if (Vcounter>0) and (v_lbbh00<>'2') and (p_pcid00 is not null) and (p_zqs000 is not null) then--周期次数 begin if v_zqs000*v_HSZQCD > lv_TSDMYPXZTS*24 then v_cfxzts:=lv_TSDMYPXZTS; --供提示显示用 raise E_FBZYSFCZ; return 0; end if; end; end if; end; end if; --是否限制指定费别才能不限制天数开8标备案药品,若有限制且不在指定费别内的最多只能开7天。 begin select nvl(max(trim(value0)),'N') into v_8BBAYPSFXZ from xt_xtcs00 where name00='YS_8BBAYPSFXZ'; exception when others then v_8BBAYPSFXZ:='N'; end; if (v_8BBAYPSFXZ<>'N') and (p_brid00>0) then select count(*) into Vcounter from BM_YD0000 where YPNM00=p_ypnm00 and NDJCTS like '%8标备案%'; if (Vcounter>0) then -- select count(BRID00) into v_COUNT0 from BM_BRXXB0 where BRID00=p_brid00 and instr(','||v_8BBAYPSFXZ||',',','||FBBH00||',')>0; select count(a.BRID00) into v_COUNT0 from BM_BRXXB0 a,ic_ybbrlb b where a.BRID00=p_brid00 and a.fbbh00=b.fbbh00 and a.yblb00=b.yblb00 and instr(','||v_8BBAYPSFXZ||',',','||b.ybzxlb||',')>0; if (v_COUNT0 = 0) and (p_pcid00 is not null) and (p_zqs000 is not null) then if v_zqs000*v_HSZQCD > 7*24 then return -1; end if; end if; end if; end if; --取出参数 中医药大学国医堂门诊部 by MZYS-20120208-001. begin select trim(VALUE0) into lv_XTCS_Value from xt_xtcs00 where name00='YS_SFXZJTFBCFKJTS'; exception when others then lv_XTCS_Value:='N'; end; if (trim(lv_XTCS_Value)<>'N') and (p_ghid00>0) then begin v_cfxzts:=0; begin select instr(';'||trim(lv_XTCS_Value)||';',';'||to_char(FBBH00)||',') into Vcounter from SF_BRXXB0 a where a.GHID00=p_ghid00; exception when others then Vcounter:=0; end; if Vcounter>0 then begin select substr(';'||trim(lv_XTCS_Value)||';',Vcounter+1,length(';'||trim(lv_XTCS_Value)||';')-Vcounter+0) into lv_XTCS_Value from dual; --去掉前面的费别 select substr(trim(lv_XTCS_Value),1, decode(sign(instr(trim(lv_XTCS_Value),';')),1,instr(trim(lv_XTCS_Value),';')-1,length(trim(lv_XTCS_Value))-1)) into lv_XTCS_Value from dual; --去掉后面的费别 select to_number(substr(trim(lv_XTCS_Value),instr(trim(lv_XTCS_Value),',')+1,length(trim(lv_XTCS_Value))-instr(trim(lv_XTCS_Value),','))) into v_cfxzts from dual; exception when others then v_cfxzts:=0; end; end if; if (v_cfxzts>0) and (p_pcid00 is not null) and (p_zqs000 is not null) then--周期次数 begin if v_zqs000*v_HSZQCD > v_cfxzts*24 then v_cfxzts:=v_cfxzts; --供提示显示用 raise E_FBZYSFCZ; return 0; end if; end; end if; end; end if; if v_qzfs00 = '0' then--每次取整 v_return := ceil(p_mcsl00*v_f2j000/v_m2j000)*v_zqcs00*v_zqs000; elsif v_qzfs00 = '1' then--每日取整 v_return := ceil(p_mcsl00*v_f2j000/v_m2j000*v_zqcs00)*v_zqs000; elsif v_qzfs00 = '2' then--每方取整 if (YS_MFQZHZJXFDQZ='N') or (v_m2j000>=v_k2j000 ) or (v_lbbh00<>'0') then v_return := ceil(p_mcsl00*v_f2j000/v_m2j000*v_zqcs00*v_zqs000); else ls_ypzsl0:=abs(round(ceil(p_mcsl00*v_f2j000/v_m2j000*v_zqcs00*v_zqs000)*v_m2j000/v_k2j000,3)-ceil(ceil(p_mcsl00*v_f2j000/v_m2j000*v_zqcs00*v_zqs000)*v_m2j000/v_k2j000)); --if (ls_ypzsl0>=0.9 and ls_ypzsl0<=0.999 ) or (ls_ypzsl0>=0.001 and ls_ypzsl0<=0.1 ) then for MZYS-20130924-001 改成 下面的if条件 if (ls_ypzsl0>=0.9 and ls_ypzsl0<=0.999 ) or (ls_ypzsl0>=0.001 and ls_ypzsl0<=0.6 ) then if ls_ypzsl0>0.5 and ls_ypzsl0<=0.6 then --因为小数点在0.5以下的round会被四舍五入掉 v_return:=ceil(ceil(ceil(p_mcsl00*v_f2j000/v_m2j000*v_zqcs00*v_zqs000)*v_m2j000/v_k2j000)*v_k2j000/v_m2j000); else v_return:=ceil(round(ceil(p_mcsl00*v_f2j000/v_m2j000*v_zqcs00*v_zqs000)*v_m2j000/v_k2j000)*v_k2j000/v_m2j000); end if; if v_return=0 then --不足1的情况 v_return:=ceil(p_mcsl00*v_f2j000/v_m2j000*v_zqcs00*v_zqs000); end if; else v_return:=ceil(p_mcsl00*v_f2j000/v_m2j000*v_zqcs00*v_zqs000); end if; end if; elsif v_qzfs00 = '3' then--不取整 v_return := round(p_mcsl00*v_f2j000/v_m2j000*v_zqcs00*v_zqs000 + 0.0049,2); elsif v_qzfs00 = '4' then--周期顺延 v_return := ceil(p_mcsl00*v_f2j000/v_m2j000*v_zqcs00*v_zqs000); else--其他 v_return := p_mcsl00*v_f2j000/v_m2j000*v_zqcs00*v_zqs000; end if; --函数返回 return v_return; exception when E_FBZYSFCZ then Raise_Application_Error(-20001,substrb('用药时间超过'||to_char(v_cfxzts)||'天',1,220)); when Ecustom then raise_application_error(-20010,substrb(Verrmsg||'！*',1,220)); end; /