CREATE OR REPLACE PROCEDURE SP_BQ_YPYZMX_ZYDZBL ( --过程参数和模式 p_mode00 in number,--0--插入,1--修改 p_yzid00 in number,--医嘱ID(分组号) p_yzmxid in number,--ID号，序列号(分组号) p_ypnm00 in number,--药品内部编号 p_ypmc00 in char,--药品名称 p_sfdzgg in char,--'0'是,'1'否 p_psid00 in char,--皮试ID 对应MZ_PS0000表 p_mcsl00 in number,--每次数量(剂量单位) p_lyl000 in number,--西成药短期医嘱领药量(发药单位)草药领药量和西成药长期医嘱领药量后台计算 p_xmyfmc in char,--草药细目用法名称 p_tqfs00 in char,--提取方式'0'正常提取，不收费'1'正常提取，收费'2'不提取，不收费 p_yzcllb in char,--'正常','嘱托','基数药','自备药','门诊带药','领药','出院带药' p_bz0000 in char,--备注 p_zqcyts in number,--周期草药贴数计算草药领药量 p_pcid00 in number,--频次ID计算长期医嘱一个周期领药量 p_yplbbh in char,--药品类别编号 p_clbz00 in char,--长短期 p_zqs000 in number,--周期数 p_ljid00 in number default 0, --路径ID p_rcid00 in number default 0, --路径的日程ID p_zxjdid in number default 0, --执行节点LJ_JDZXQK.BRZXJD p_YJYYLB in char default '', --越级用药类别 p_YJYYLY in char default '', --越级用药理由 p_jldw00 in varchar2 default '',--剂量单位bq_ypyzmx.jldw00 p_j2j000 in number default null,--总量单位转换率bq_ypyzmx.j2j000 p_zyfydw in varchar2 default '',--总量单位bq_ypyzmx.zyfydw p_z2j000 in number default null,--总量单位转换率bq_ypyzmx.z2j000 P_JHMXID in number default null,--诊疗计划明细ID bq_ypyzmx.JHMXID p_Zffsbz in char default ''--是否自费 ) AS -- Person Date Comments -- 阮炳辉 2014.12.11 create for DZBL-20141210-007 -- wuzy 2017.11.06 医嘱不判断预扣库存归档 update for ZYDZBL-20171009-001 -- wuzy 2018.06.05 过程加上日志 SP_TransLog for ZYDZBL-20180531-002 -- wuzy 2018.06.20 新增BQ_YPYZMX时，p_ljid00，P_JHMXID等字段一起保存 for ZYDZBL-20180615-003 -- wuzy 2018.07.02 新增BQ_YPYZMX时，路径的日程ID需要一起保存 for ZYDZBL-20180629-003 --linxw 2018.08.24 新增 zffsbz时，保存是否自费字段 for ZYDZBL-20180724-001 --caishb 2019.07.29 修正药品医嘱修改时出现重复‘高危’，‘基’，‘省基’的问题 for ZYDZBL-20190722-002 -- zouqc 2021.02.02 取消过滤‘高危’，‘基’，‘省基’等字眼，避免摆药时与药典显示名称不一致 for ZYDZBL-20210127-001 -- zouqc 2021.03.01 修改BQ_YPYZMX时YPMC00取值赋值 for ZYDZBL-20210224-002 -- caishb 2021.03.15 医嘱更新时ZFBL00为空时赋默认值 for ZYDZBL-20210315-002 --过程声明 v_zyid00 ZY_BRXXB0.zyid00%type;--住院ID VBRID00 BM_BRXXB0.BRID00%type;--病人ID v_ybfzxlb BM_BRXXB0.YBLB00%type;--医保分中心类别 v_ybzxlb IC_YBBRLB.YBZXLB%type;--医保中心类别 v_ybbrlb BM_BRXXB0.YBBRLB%type;--医保病人类别 v_fbbh00 BM_BRXXB0.FBBH00%type;--费别编号 v_zfbl00 BM_YBSFDY.ZFBL00%type;--自付比率 v_yblb00 BM_YBSFDY.YBLB00%type;--医保类别 v_ypmc00 BM_YD0000.YPMC00%type;--药品名称 V_YPLBBH BM_YD0000.LBBH00%type;--药品类别编号 v_ypgg00 BM_YD0000.YPGG00%type;--药品规格 v_jldw00 BM_YD0000.JLDW00%type;--剂量单位 v_j2j000 number(10);--剂量单位 v_zyfydw BM_YD0000.ZYFYDW%type;--住院发药单位 v_z2j000 BM_YD0000.Z2J000%type;--Z/J转换率 v_qzfsid BM_YD0000.QZFS00%type;--取整方式 v_dmbz00 BQ_YPYZ00.DMBZ00%type;--毒麻标识 v_psmc00 BM_PS0000.PSMC00%type;--皮试名称 v_lyl000 BQ_YPYZMX.LYL000%type;--领药量 v_zqcs00 BM_PC0000.ZQCS00%type;--周期次数 v_Count number(3); --记数器 v_ErrorStr VarChar2(5000); --错误提示 Ecustom EXCEPTION;--不是修改医嘱处理 ls_mbrcid BQ_YPYZMX.MBRCID%type; ls_brzxjd BQ_YPYZMX.BRZXJD%type; v_byfl00 BM_YD0000.BYFL00%type;--摆药分类 0,针剂;1,口服 v_ZS_CYDYXYYFBMBH varchar2(500);--出院带药西药药房部门编号 v_ZS_CYDYZCYYFBMBH varchar2(500);--出院带药针剂类中成药药房部门编号 v_ykkcsl YF_YPKCXX.YKKCSL%type;--预扣库存数量 v_yfbmbh YF_YPKCXX.YFBMBH%type;--药房部门编号 v_PSPC00 BQ_YPYZ00.PSPC00%type;--配送批次 v_pslyl0 BQ_YPYZMX.LYL000%type;--配送领药量 v_Count00 number(3); LS_LJID00 BQ_YPYZMX.LJID00%type; LS_JHMXID BQ_YPYZMX.JHMXID%type; BEGIN --过程主体 v_ErrorStr := 'SP_BQ_YPYZMX_ZYDZBL(' ||'p_mode00:'||To_Char(p_mode00)||'p_yzid00:'||To_Char(p_yzid00)||'p_yzmxid:'||To_Char(p_yzmxid) ||'p_ypnm00:'||To_Char(p_ypnm00)||'p_ypnm00:'||p_ypnm00||'p_sfdzgg:'||p_sfdzgg||'p_psid00:'||p_psid00 ||'p_mcsl00:'||To_Char(p_mcsl00)||'p_lyl000:'||To_Char(p_lyl000)||'p_xmyfmc:'||p_xmyfmc ||'p_tqfs00:'||p_tqfs00||'p_yzcllb:'||p_yzcllb||'p_bz0000:'||p_bz0000||')'; SP_TRANSLOG(sysdate,'SP_BQ_YPYZMX_ZYDZBL',0,0,v_ErrorStr); v_ErrorStr := ''; --住院ID select ZYID00,YFBMBH,PSPC00 into v_zyid00,v_yfbmbh,v_PSPC00 from BQ_YPYZ00 where YZID00 = p_yzid00; --病人ID select BRID00 into VBRID00 from ZY_BRXXB0 where ZYID00 = v_zyid00; --医保分中心类别和医保病人类别 select YBLB00,YBBRLB,FBBH00 into v_ybfzxlb,v_ybbrlb,v_fbbh00 from BM_BRXXB0 where BRID00 = VBRID00; --医保中心类别 select YBZXLB into v_ybzxlb from IC_YBBRLB where YBLB00 = v_ybfzxlb and FBBH00=v_fbbh00; --自付比率,--医保类别 begin select ZYZFBL,YBLB00 into v_zfbl00,v_yblb00 from VW_BM_YBSFDY where SFYP00 = 'Y' and YBZXLB = v_ybzxlb and YBBRLB = v_ybbrlb and SFXMID = p_ypnm00 and FBBH00 = v_fbbh00 and ROWNUM = 1; if v_yblb00 is null then v_yblb00 := '7'; end if; exception when No_Data_Found then begin v_zfbl00 := 1; v_yblb00 := '7'; end; end; --药品名称，药品规格，剂量单位，住院发药单位，Z/J转换率，毒麻标识,药平类别编号 select YPMC00,LBBH00,YPGG00,nvl(p_jldw00,JLDW00),nvl(p_j2j000,1),nvl(p_zyfydw,ZYFYDW),nvl(p_z2j000,Z2J000),QZFS00,Decode(MZDJ00||DPDJ00||decode(SJDJ00,'0','0','1'),'000','0','1'),BYFL00 into v_ypmc00,V_YPLBBH,v_ypgg00,v_jldw00,v_j2j000,v_zyfydw,v_z2j000,v_qzfsid,v_dmbz00,v_byfl00 from BM_YD0000 where YPNM00 = p_ypnm00; --药品皮试名称 begin select PSMC00 into v_psmc00 from BM_PS0000 where PSID00 = p_psid00; exception when No_Data_Found then v_psmc00 := null; end; --计算领药量 if p_yzcllb in ('嘱托','自备药','门诊带药') then v_lyl000 := 0; else if p_yplbbh = '2' then--计算草药医嘱领药量 v_lyl000 := SF_BQ_LYLJS0(v_qzfsid,p_mcsl00,v_z2j000,1,p_zqcyts); elsif p_clbz00 = '0' then--计算西成药长期医嘱领药量 begin select ZQCS00 into v_zqcs00 from BM_PC0000 where PCID00 = p_pcid00; exception when No_Data_Found then v_zqcs00 := 1; end; v_lyl000 := SF_BQ_LYLJS0(v_qzfsid,p_mcsl00,v_z2j000,v_zqcs00,p_zqs000); elsif p_clbz00 = '1' then--西成药短期医嘱领药量 v_lyl000 := p_lyl000; end if; if p_clbz00 = '1' and v_lyl000=0 then--西成药短期医嘱领药量 v_ErrorStr:='临嘱的领药量不能为0'; raise Ecustom; end if; if p_clbz00 = '0' and p_mcsl00=0 then--长期医嘱每次用量不能为0 v_ErrorStr:='长嘱的每次用量不能为0'; raise Ecustom; end if; if p_clbz00 = '1' and v_PSPC00 is not null then--西成药短期医嘱代配 begin select ZQCS00 into v_zqcs00 from BM_PC0000 where PCID00 = p_pcid00; exception when No_Data_Found then v_zqcs00 := 1; end; v_pslyl0 := SF_BQ_LYLJS0(v_qzfsid,p_mcsl00,v_z2j000,v_zqcs00,p_zqs000); if v_pslyl0>v_lyl000 then v_ErrorStr:='该医嘱属于"代配",领药量不能小于'||to_char(v_pslyl0)||v_zyfydw; raise Ecustom; end if; end if; end if; --判断预扣库存 20130530 阮炳辉 select nvl(sum(ykkcsl),0) into v_ykkcsl from VW_ZS_YPKCXX_DZBL where ypnm00=p_ypnm00 and yfbmbh=v_yfbmbh; select Count(1) into v_Count00 from XT_XTCS00 where NAME00='ZS_YZTJSFPDKC' and VALUE0='N'; if v_Count00<1 then if v_ykkcsl 0 then -- v_lyl000 := 0;--如果是免费皮试药品领药量为零 --end if; if p_mode00 = 0 then--0插入 select Count(1) into v_Count from BQ_YPYZMX where YZID00 = p_yzid00 and YPNM00 = p_ypnm00; if v_Count > 0 then v_ErrorStr:='同组医嘱'||v_ypmc00||'药品不能重复'; raise Ecustom; end if; --出院带药的西药或针剂类中成药的药房部门编号取系统参数 20130422 阮炳辉 if p_yzcllb='出院带药' or (p_yplbbh = 1 and v_byfl00='0') then select Count(1) into v_Count from BQ_YPYZMX where YZID00 = p_yzid00; if v_Count = 0 then select value0 into v_ZS_CYDYXYYFBMBH from xt_xtcs00 where name00='ZS_CYDYXYYFBMBH'; select value0 into v_ZS_CYDYZCYYFBMBH from xt_xtcs00 where name00='ZS_CYDYZCYYFBMBH'; if p_yplbbh = 0 and v_ZS_CYDYXYYFBMBH is not null then --出院带药西药药房部门编号 update BQ_YPYZ00 set yfbmbh = v_ZS_CYDYXYYFBMBH where YZID00 = p_yzid00; end if; else if p_yplbbh = 1 and v_byfl00='0' and v_ZS_CYDYZCYYFBMBH is not null then --出院带药针剂类中成药药房部门编号 update BQ_YPYZ00 set yfbmbh = v_ZS_CYDYZCYYFBMBH where YZID00 = p_yzid00; end if; end if; end if; begin select MBRCID,BRZXJD into LS_MBRCID,LS_BRZXJD from BQ_YPYZMX where YZID00=P_YZID00 and MBRCID>0 and BRZXJD>0 and rownum<=1; exception when NO_DATA_FOUND then LS_MBRCID := P_RCID00; LS_BRZXJD := P_ZXJDID; end; begin select ZRZCID,LJID00 into LS_MBRCID,LS_LJID00 from VW_ZY_LJZRZC_ZYDZBL where ZYID00=V_ZYID00 and JSFS00 = '执行中' and rownum<=1; exception when others then LS_LJID00 := 0; LS_MBRCID := 0; end; if nvl(LS_LJID00,0)>0 and nvl(LS_MBRCID,0)>0 then--路径病人 begin select JHMXID into LS_JHMXID--新增模板内的药品 from LJ_YZMX00 where ZYID00=V_ZYID00 and YPNM00 = P_YPNM00 and JHMXID>0 and LJID00=LS_LJID00 and ZRZCID=LS_MBRCID and BRZXJD=LS_BRZXJD and rownum<=1; exception when others then LS_JHMXID := 0; end; else LS_MBRCID:= P_RCID00; LS_BRZXJD:= P_ZXJDID; end if; insert into BQ_YPYZMX( YZID00, --医嘱ID(分组号) YZMXID, --ID号，序列号(分组号) YPNM00, --药品内部编号 SFDZGG, --'0'是,'1'否 YPMC00, --药品名称 YPGG00, --药品规格 PSID00, --皮试ID 对应MZ_PS0000表 PSMC00, --皮试名称(冗余) MCSL00, --每次数量(剂量单位的每次剂量) JLDW00, --剂量单位(J)(冗余) LYL000, --领药量(发药单位,临时、用药医嘱使用) ZYFYDW, --住院发药单位(Z)(住院药房维护)(冗余) Z2J000, --住院发药/基本单位转换率(默认药房) XMYFMC, --草药细目用法名称 TQFS00, --提取方式'0'正常提取，不收费'1'正常提取，收费'2'不提取，不收费 YZCLLB, --'正常','嘱托','基数药','自备','门诊处理','领药','出院带药' BZ0000, --备注 ZFBL00, --自付比率 YBLB00, --医保类别 QZFSID, --取整方式 DMBZ00, --毒麻标识 YPLBBH, --药品类别编号 LJID00, MBRCID, BRZXJD, YJYYLB, --越级用药类别 YJYYLY, --越级用药理由 J2J000, --剂量单位转换率 JHMXID, Zffsbz ) values( p_YZID00,-- SQ_BQ_YZMX00_YZMXID.NextVal,--YZMXID, p_ypnm00,-- p_sfdzgg,-- --p_ypmc00,--YPMC00, v_ypmc00, --药品名称 v_ypgg00,--YPGG00, p_PSID00,-- v_psmc00,--PSMC00, p_MCSL00,-- v_jldw00,--JLDW00, v_lyl000,-- v_zyfydw,--ZYFYDW, v_z2j000,--Z2J000, p_XMYFMC,-- p_TQFS00,-- p_YZCLLB,-- p_BZ0000,-- nvl(v_zfbl00,1), v_yblb00, v_qzfsid, v_dmbz00, V_YPLBBH, decode(LS_JHMXID,0,P_LJID00,LS_LJID00), decode(LS_BRZXJD,0,0,LS_MBRCID),--p_rcid00, LS_BRZXJD, p_YJYYLB, p_YJYYLY, v_j2j000, decode(P_JHMXID,0,LS_JHMXID,P_JHMXID), p_Zffsbz ); elsif p_mode00 = 1 then--1修改 --判断是否新开医嘱 select Count(1) into v_Count from BQ_YPYZ00 where YZID00 = p_yzid00 and YZZT00 in ('0','7'); if v_Count = 0 then v_ErrorStr:='不是新开医嘱，不能修改'; raise Ecustom; end if; if p_clbz00 = '1' and v_PSPC00 is not null then--西成药短期医嘱代配 begin select ZQCS00 into v_zqcs00 from BM_PC0000 where PCID00 = p_pcid00; exception when No_Data_Found then v_zqcs00 := 1; end; v_pslyl0 := SF_BQ_LYLJS0(v_qzfsid,p_mcsl00,v_z2j000,v_zqcs00,p_zqs000); if v_pslyl0>v_lyl000 then v_ErrorStr:='该医嘱属于"代配",领药量不能小于'||to_char(v_pslyl0)||v_zyfydw; raise Ecustom; end if; end if; --修改医嘱 update BQ_YPYZMX set YPNM00 = p_ypnm00, --药品内部编号 SFDZGG = p_sfdzgg, --'0'是,'1'否 -- REPLACE(REPLACE(REPLACE(p_ypmc00,'(高危)',''),'(基)',''),'(省基)',''), --药品名称 YPMC00 = p_ypmc00, YPGG00 = v_ypgg00, --药品规格 PSID00 = p_psid00, --皮试ID 对应MZ_PS0000表 PSMC00 = v_psmc00, --皮试名称(冗余) MCSL00 = p_mcsl00, --每次数量(剂量单位的每次剂量) JLDW00 = v_jldw00, --剂量单位(J)(冗余) LYL000 = v_lyl000, --领药量(发药单位,临时、用药医嘱使用) ZYFYDW = v_zyfydw, --住院发药单位(Z)(住院药房维护)(冗余) Z2J000 = v_z2j000, --住院发药/基本单位转换率(默认药房) XMYFMC = p_xmyfmc, --草药细目用法名称 TQFS00 = p_tqfs00, --提取方式'0'正常提取，不收费'1'正常提取，收费'2'不提取，不收费 YZCLLB = p_yzcllb, --'正常','嘱托','基数药','自备','门诊处理','领药','出院带药' BZ0000 = p_bz0000, --备注 ZFBL00 = nvl(v_zfbl00,1), --自付比率 YBLB00 = v_yblb00, --医保类别 QZFSID = v_qzfsid, --取整方式 DMBZ00 = v_dmbz00, --毒麻标识 YPLBBH = V_YPLBBH, --药品大类 YJYYLB = p_YJYYLB, --越级用药类别 YJYYLY = p_YJYYLY, --越级用药理由 J2J000 = v_j2j000 --剂量单位转换率 where YZMXID = p_yzmxid; end if; --同组有出院带药的，不能包含正常的 select count(1) into v_Count from bq_ypyzmx where yzid00=p_yzid00 and yzcllb='出院带药'; if v_Count>0 then select count(1) into v_Count from bq_ypyzmx where yzid00=p_yzid00 and yzcllb='正常'; if v_Count>0 then update bq_ypyzmx set yzcllb='出院带药',tqfs00='2' where yzid00=p_yzid00 and yzcllb='正常'; end if; end if; exception when Ecustom then Raise_Application_Error(-20001, substrb(v_ErrorStr||'！*'||SqlErrm,1,220)); when No_Data_Found then Raise_Application_Error(-20003, substrb('发生未找到数据错误！*返回信息:'||v_ErrorStr||SqlErrm,1,220)); when others then Raise_Application_Error(-20004, substrb(NVL(SqlErrm||v_ErrorStr, '原因不明出错')||'！*',1,220)); end;