CREATE OR REPLACE TRIGGER TR_YF_MZCF00_AFUPD BEFORE INSERT OR DELETE OR UPDATE ON SD_MZ.YF_MZCF00 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW declare cursor c_MZCFMX is Select YPDLBH,YPNM00,YPMC00,YPGG00,CFDW00,ZHL000,GJDJ00,CXCFID,LSDJ00,SFSLYK,ROUND(SUM(YPZSL0),3) YPZSL0 FROM YF_MZCFMX WHERE CFLSH0=:NEW.CFLSH0 AND SFZBY0='N' Group BY YPDLBH,YPNM00,YPMC00,YPGG00,CFDW00,ZHL000,GJDJ00,CXCFID,LSDJ00,SFSLYK; /*SELECT YPNM00,ZHL000,YPZSL0,YPMC00,LSDJ00,CFID00,YPGG00,CFDW00,GJDJ00,CXCFID,SFSLYK,YPDLBH FROM YF_MZCFMX WHERE CFLSH0=:NEW.CFLSH0 AND SFZBY0='N'; */ v_MZCFMX c_MZCFMX%ROWTYPE; v_SJKCSL YF_YPKCXX.SJKCSL%TYPE; V_CKBMMC BM_BMBM00.BMMC00%TYPE; v_DLSJGL XT_XTCS00.VALUE0%TYPE; --多零售价管理 V_DQLSDJ YF_YPKCXX.LSDJ00%TYPE; --当前零售价 V_DQGJDJ YF_YPKCXX.GJDJ00%TYPE; --当前购进价 V_PCYKZL YF_PCKCMX.YKKCSL%TYPE; --批次预扣总数量 V_K2J000 BM_YD0000.K2J000%TYPE; V_KCLSH0 YF_PCKCMX.KCLSH0%TYPE; V_LSDJ00 YF_PCKCMX.LSDJ00%TYPE; V_PCKCSL YF_PCKCMX.YKKCSL%TYPE; V_YKKCSL YF_PCKCMX.YKKCSL%TYPE; V_SYCKSL YF_PCKCMX.SJKCSL%TYPE; --剩余出库数量 V_BCCKSL YF_PCKCMX.SJKCSL%TYPE; --本次出库数量 V_KCSL00 BM_YD0000.KCSL00%TYPE; V_KCJE00 BM_YD0000.KCJE00%TYPE; V_FYBZ00 BM_BMBM00.FYBZ00%TYPE; V_YKYXTS XT_XTCS00.VALUE0%TYPE; v_KHHCS0 XT_XTCS00.VALUE0%TYPE; V_SFSLYK YF_MZCFMX.SFSLYK%TYPE; V_SFZBYP BM_YD0000.SFZBYP%TYPE; V_ZY0000 YF_YPMXZ0.ZY0000%TYPE; v_SFPDKC XT_XTCS00.VALUE0%TYPE; --是否判断存 v_SFXGLB XT_XTCS00.VALUE0%TYPE; --毒嘛处方是否发药后自动修改类别 V_GJJ000 BM_YD0000.GJJ000%TYPE; VEKCFJDFS XT_XTCS00.VALUE0%TYPE; --儿科处方界定方式 C_YPSJDJ BM_YD0000.SJDJ00%TYPE; Vcount0 number(5); C_BRNL00 NUMBER(5); I_BRID00 BM_BRXXB0.BRID00%TYPE; v_BFXSCS XT_XTCS00.VALUE0%TYPE; V_FBBH00 SF_BRXXB0.FBBH00%TYPE; V_FBBHCS SF_BRXXB0.FBBH00%TYPE; V_TEMPSL number(15,6);--临时小数部分数据 YF_PDQJSFYXFY XT_XTCS00.value0%type;--盘点期间是否允许发药 ，Y允许 N不允许 L NUMBER; I NUMBER; J NUMBER; V_DQRQ00 CHAR(8); V_DQSJ00 CHAR(8); V_COUNT0 NUMBER(5); v_SFCFZYJJ CHAR(1);--是否处方转预交金 'Y'是 'N' 不是 VS_YGBH00 XT_XTRZ00.YGBH00%TYPE; VS_PROCESS XT_XTRZ00.JCID00%TYPE; V_errmsg varchar2(255); --错误提示变量 E_custom exception; --错误变量 E_NoDelete EXCEPTION; E_NoEdit EXCEPTION; LS_KCYKSL number; --扣除的预扣库存 LS_KCSJSL number; --扣除的实际库存库存 LS_ERRMSG varchar2(255);--输出错误提示 LS_ERRLB0 number; --输出类别 0 错误 1正常 LS_TSBZCFZDYF varchar2(10); LS_GXLSDJ varchar2(2);--是否更新单价 Y:是 N:否 V_ZY0001 YF_YPMXZ0.ZY0000%TYPE; -- MODIFICATION HISTORY 3.1 -- Person Date Comments -- JETHUA 2003.12.08 增加扣除批次库存 -- JETHUA 2003.12.08 增加库存不足提示 -- JETHUA 2004.03.25 增加药房零售价单独管理处理:记帐时扣除批次预扣库存 -- JETHUA 2004.03.25 关于所有门诊处方的操作,都由此触发器处理 -- JETHUA 2004.04.15 增加已记账退药 -- JETHUA 2004.03.23 数量增加小数位数 -- JETHUA 2004.05.07 置已记账标志后，重新计算总金额,(包括医生处方信息表) -- JETHUA 2004.05.14 现金病人单独计算零售价 -- JETHUA 2004.06.15 统一YF_YPMXZ0的结存单位为库存单位 -- JETHUA 2004.06.18 增加退药作废功能 -- JETHUA 2004.07.08 规范错误提示 -- JETHUA 2004.07.09 科室领要记帐更新库存信息表的零售单价;修改科室领药无法退药问题 -- JETHUA 2004.07.18 增加门诊总量预扣 -- JETHUA 2004.08.09 优化游标,不用'select *' -- JETHUA 2004.10.10 增加判断是否中标药品 -- JETHUA 2004.11.05 如药品隶属被删除,明确错误提示 -- JETHUA 2004.11.22 BUG:记账，药械金额为空无法保存 -- JETHUA 2004.11.22 BUG:记账，处方主表的总金额计算未四舍五入 -- JETHUA 2005.01.07 BUG:记账，现金记帐,记帐时不再更新处方零售价 -- JETHUA 2005-04-01 特殊费别需要审核，插入时，为未审核状态 -- JETHUA 2005.04.29 考虑到科室领药退药，允许1－》2进行退药 -- JETHUA 2005.06.20 如处方全为自备药，则金额全为零 -- JETHUA 2005.09.07 调整零售价计算方式，先承再除，避免单价不必要的变化 -- JETHUA 2005.09.29 允许门诊在同一处方内开相同药品 -- JETHUA 2005.09.29 德化医院要求摘要显示病人姓名 -- JETHUA 2005.10.21 判断主表大类编号 -- JETHUA 2006.04.28 增加发药是否判断库存不足 -- CSF 2007.01.08 bug:发药时候，相同药品出现触发器重复读现象，现把相同药品汇总处理 -- CSF 2007.03.07 bug:处方状态由0-6退费时候,只有条件ypnm00,而没有加cflsh0 -- CSF 2008.01.29 BUG:处方中的药品价格不存在于yf_pckcmx 中时,最后必须更新单价,此时如果同一药品有两条记录的时候 -----------------------------发生错误,现改用参数传入处理 -- yangh 2008.05.19 毒嘛处方是否在发药后自动修改类别YF_MZCF00.YPDLBH -- csf 2008.08.19 合并触发器 tr_yf_mzcf00_bfupddet -- CSF 2008.11.21 BUG:当抢药时 没有还原原药房的yf_ypkcxx.ykkcsl 没有扣除抢药药房的yf_ypkcxx.ykkcsl. -- CSF 2009.01.13 由于非门诊医生输入的处方没有处理cfdgs0，现在补充处理。 -- CSF 2009.02.17 门诊毒麻处方开的毒麻处方的cfdgs0改从ys_cfmx00取, -- CSF 2009.03.07 将删除部分的内容独立为 tr_yf_mzcf00_bdel -- CSF 2009.07.14 处方发药时候,BRID00=BM_BRXTB0.BRID00的处方，先入库后出库,记账时候的批次库存库存预扣移到发药后。 -- CSF 2009.07.30 科室领药处方及负数处方不做转预交金处理 -- CSF 2009.09.06 处方是否转入库，改由变量SPACK_YF_BRXX00.PACK_SFHY00来判断 -- CSF 2009.09.11 换药的处方退药，系统将自动做等额退预交金操作 -- qks 2009.12.23 --记帐-- 也同时修改YF_MZCFMX购进单价 -- zhangwz 2010.05.26 扩展参数YS_EKCFJDFS的值来控制儿科处方的界定，扩展后为:默认为0按年龄(<=15周岁) 1按科室 2按年龄(<=14周岁) -- CSF 2010.11.10 门诊处方发药、退药、取消退药写入yf_ypmxz0.jcdj00存在错误，现纠正。 -- zhangwz 2011.06.01 增加限制SF_BRXXB0.GHLB00=-99999(预挂号)不能开具处方 by YF-20110531-001. --laijg 2011.06.17 添加yf_mzcf00补打记录到操作日志xt_xtczrz --laijg 2011.06.30 修改yf_ypmxz0表crbmbh字段插入值 -- csf 2011.07.12 加参数YF_PDQJSFYXFY控制，盘点期间允许发药 -- csf 2011.12.08 购进价处理，单价格取自YF_YPKCXX,多价格取YF_PCKCMX -- csf 2012.02.01 如果单位为处方单位的库存与发药数量相比库存小于0.001则将库存直接改成0 (并在需求YF-20120203-002中直接归档) -- csf 2012.03.27 扣除库存改用过程SP_YF_PDYKKCSFZG，并处理临界点库存 YF-20120316-003 -- laijg 2013.02.21 写入药房药品明细账结存单价有误，按记账单位写入 by YF-20130220-001 -- dsm 2015.10.30 特殊病种处方不能抢药YS_TSBZCFZDYF for MZYS-20150922-001 --zhangyc 2016.03.02 修改诊疗带出处方冲销时,调价后不更新YF_MZCFMX.LSDJ00 by MZSF-20160301-001 -- dsm 2019.03.09 外购处方不能发药 for MZYS-20190309-001 -- dsm 2019.06.09 YF_YPMXZ00.KSBH00 for YF-20190507-001 -- dsm 2020.12.10 去SPACK_YF_BRXX00的使用 for YF-20201210-001 -- huangjy 2020.12.14 记账预扣库存时写入yf_mzcfmx.scph00,ypsxrq for YFPY-20201123-001 -- dsm 2020.12.25 YF_PARAMS多插入BRID00 for YF-20201225-001 -- huangjy 2021.06.23 c_MZCFMX.YPZSL0精确到小数点后3位，原两位 for YF-20210525-002 -- qiulf 2021.07.02 MZSF-20160301-001修改诊疗带出处方查询条件:NEW.BZ0000 in ('诊疗项目带出处方') 多了'项目'两个字。 by MZSF-20210623-001 -- dsm 2021.10.28 电子处方平台处方WGCFBZ=2的不能扣费 for MZYS-20211028-001 -- --------- ---------- -------------------------------------------------- begin V_errmsg:=''; LS_GXLSDJ:='Y';--是否更新单价 Y:是 N:否 --取出参数:某些费别在出发发药时状态为审核 JETHUA 2005.03.31 begin SELECT Trim(VALUE0) INTO v_BFXSCS FROM XT_XTCS00 WHERE NAME00='YF_BDYCFFB'; EXCEPTION WHEN OTHERS THEN v_BFXSCS:='N'; END; select nvl(max(trim(VALUE0)),'N') into YF_PDQJSFYXFY from XT_XTCS00 where NAME00='YF_PDQJSFYXFY'; select nvl(max(trim(VALUE0)),'0') into LS_TSBZCFZDYF from XT_XTCS00 where NAME00='LS_TSBZCFZDYF'; begin Vcount0:=to_number(LS_TSBZCFZDYF); exception when others then LS_TSBZCFZDYF:='0' ; end; Vcount0:=0; if nvl(:new.WGCFBZ,'0') in('1','2') and (:new.CFZT00='1' ) then --MZYS-20190309-001 V_errmsg:='外购处方，在医院不能记账或发药!'; RAISE E_custom; end if; delete YF_PARAMS where CFLSH0=:new.CFLSH0; if inserting or updating then insert into YF_PARAMS (CFLSH0,ZYGHID,YFBMBH,SFKSLY,MZZYBZ,BRID00) values(:new.CFLSH0,:new.GHID00,:new.YFBMBH,:new.SFKSLY,'0',:new.BRID00); end if; IF INSERTING THEN--增加 select count(*) into Vcount0 from SF_BRXXB0 where GHID00=:NEW.GHID00 and GHLB00=-99999; if Vcount0>0 then V_errmsg:='病人挂号为预挂号,不能开具处方!'; RAISE E_custom; end if; if LS_TSBZCFZDYF<>'0' and :new.YFBMBH=to_number(LS_TSBZCFZDYF) and nvl(:new.TSBZBH,' ')<>' ' then :new.SFYXQY:='N'; end if; IF v_BFXSCS<>'N' THEN SELECT Trim(B.FBBH00) INTO V_FBBH00 FROM SF_BRXXB0 B WHERE B.GHID00=:NEW.GHID00; I:=0; J:=1; SELECT INSTR(v_BFXSCS,',',1,J) INTO L FROM DUAL; --取出第J次出现'*'的位置 WHILE L>0 LOOP V_FBBHCS:=SUBSTR(v_BFXSCS,I+1,L-I-1); --RAISE E_NoEdit; --需要审核 IF V_FBBHCS=V_FBBH00 THEN :NEW.CFZT00:='4'; END IF; I:=L; J:=J+1; SELECT INSTR(v_BFXSCS,',',1,J) INTO L FROM DUAL; --取出第J次出现'*'的位置 --取最后一段 IF L=0 THEN V_FBBHCS:=SUBSTR(v_BFXSCS,I+1,LENGTH(v_BFXSCS)); IF V_FBBHCS=V_FBBH00 THEN :NEW.CFZT00:='4'; END IF; END IF; END LOOP; ---FOR WHILE ... LOOP OUTER END IF; --非门诊医生处方处理cfdgs0字段 -- 取出儿科处方界定方式 参数 begin select trim(value0) into VEKCFJDFS from xt_xtcs00 where name00='YS_EKCFJDFS'; exception when others then VEKCFJDFS:='0'; --默认按年龄 ‘1’按科室 end; IF :NEW.CFXZ00 <>'0' THEN begin select B.SJDJ00 into C_YPSJDJ from YF_MZCFMX A, BM_YD0000 B WHERE A.YPNM00=B.YPNM00 AND A.CFLSH0=:NEW.CFLSH0 AND ROWNUM=1 and SJDJ00<>'0'; EXCEPTION WHEN OTHERS THEN begin select B.SJDJ00 into C_YPSJDJ from YS_CFMX00 A, BM_YD0000 B WHERE A.YPNM00=B.YPNM00 AND A.CFLSH0=:NEW.CFLSH0 AND ROWNUM=1 and SJDJ00<>'0'; exception when others then C_YPSJDJ:='1'; end; END; IF C_YPSJDJ<2 THEN :NEW.CFDGS0:='0'; ELSE :NEW.CFDGS0:='1'; END IF; ELSE IF VEKCFJDFS='1' Then--'按科室' select count(1) into Vcount0 from BM_BMBM00 A WHERE BMBH00=:NEW.GHKS00 AND A.BMMC00 LIKE '%儿%'; if Vcount0>0 then C_BRNL00:=1; else C_BRNL00:=16; end if; else --按年龄 begin SELECT FLOOR((MONTHS_BETWEEN(SYSDATE,TO_DATE(BRCSRQ,'YYYYMMDD')))/12) INTO C_BRNL00 FROM BM_BRXXB0 WHERE BRID00 =:NEW.BRID00; exception when others then C_BRNL00:=30; end; end if; select count(1) into Vcount0 from bm_ghlbb0 a, sf_brxxb0 b where a.lbbh00=b.ghlb00 and b.ghh000=:NEW.GHH000 and a.lbmc00 like '%急诊%'; IF (C_BRNL00<=15) and (VEKCFJDFS<>'2') THEN--儿科处方处理 '2'儿科 ‘3‘儿科加急诊 if Vcount0>0 then :NEW.CFDGS0:='3'; else :NEW.CFDGS0:='2'; end if; elsif (C_BRNL00<=14) and (VEKCFJDFS='2') then --儿科处方处理 '2'儿科 ‘3‘儿科加急诊 if Vcount0>0 then :NEW.CFDGS0:='3'; else :NEW.CFDGS0:='2'; end if; else if Vcount0>0 then :NEW.CFDGS0:='4'; else :NEW.CFDGS0:='5'; end if; END IF; END IF; ELSIF UPDATING THEN--修改 IF ( ( (:OLD.CFZT00=:NEW.CFZT00) -- 已经确认的数据被修改 AND ((:OLD.CFZT00='1') OR (:OLD.CFZT00='2') OR (:OLD.CFZT00='3')) AND ( :OLD.FYRQ00<>NULL ) ) OR ((:OLD.CFZT00='1' OR :OLD.CFZT00='2') AND :NEW.CFZT00='3') --已经确认的数据被作废 ) THEN RAISE E_NoEdit; END IF; --取出是否多零售价管理 JETHUA 2004.03.24 begin SELECT Trim(VALUE0) INTO v_DLSJGL FROM XT_XTCS00 WHERE NAME00='YF_SFDLSJGL'; EXCEPTION WHEN OTHERS THEN v_DLSJGL:='N'; END; --取出门诊是否数量预扣 JETHUA 2004.07.18 begin SELECT Trim(VALUE0) INTO V_YKYXTS FROM XT_XTCS00 WHERE NAME00='YF_MZCFSLYKYXTS'; EXCEPTION WHEN OTHERS THEN V_YKYXTS:='0'; END; --取出客户化参数:0默认;1莆二;2泉二;3省二;4南安;5七院;6铁路医院 JETHUA 2005.09.29 --6铁路;7建阳;8广州大院;9泉三;10福清;11泉州人民;12闽侯县二院;13德化中医院 begin SELECT Trim(VALUE0) INTO v_KHHCS0 FROM XT_XTCS00 WHERE NAME00='YF_ZJKFDDDYGS'; EXCEPTION WHEN OTHERS THEN v_KHHCS0:='0'; END; --取出增加发药是否判断库存不足参数 JETHUA 2006.04.28 begin SELECT Trim(VALUE0) INTO v_SFPDKC FROM XT_XTCS00 WHERE NAME00='YF_FYSFPDKCBZ'; EXCEPTION WHEN OTHERS THEN v_SFPDKC:='Y'; END; --毒嘛处方是否发药后自动修改类别； begin SELECT Trim(VALUE0) INTO v_SFXGLB FROM XT_XTCS00 WHERE NAME00='YF_DMCFSFXGCFLB'; EXCEPTION WHEN OTHERS THEN v_SFXGLB:='Y'; END; IF :NEW.CFZT00='0' THEN if LS_TSBZCFZDYF<>'0' and :new.YFBMBH=to_number(LS_TSBZCFZDYF) and nvl(:new.TSBZBH,' ')<>' ' then :new.SFYXQY:='N'; end if; -- 取出儿科处方界定方式 参数 begin select trim(value0) into VEKCFJDFS from xt_xtcs00 where name00='YS_EKCFJDFS'; exception when others then VEKCFJDFS:='0'; --默认按年龄 ‘1’按科室 end; IF :NEW.CFXZ00 <>'0' THEN begin select B.SJDJ00 into C_YPSJDJ from YF_MZCFMX A, BM_YD0000 B WHERE A.YPNM00=B.YPNM00 AND A.CFLSH0=:NEW.CFLSH0 AND ROWNUM=1 and SJDJ00<>'0'; EXCEPTION WHEN OTHERS THEN select B.SJDJ00 into C_YPSJDJ from YS_CFMX00 A, BM_YD0000 B WHERE A.YPNM00=B.YPNM00 AND A.CFLSH0=:NEW.CFLSH0 AND ROWNUM=1 and SJDJ00<>'0'; END; IF C_YPSJDJ<2 THEN :NEW.CFDGS0:='0'; ELSE :NEW.CFDGS0:='1'; END IF; ELSE IF VEKCFJDFS='1' Then--'按科室' select count(1) into Vcount0 from BM_BMBM00 A WHERE BMBH00=:NEW.GHKS00 AND A.BMMC00 LIKE '%儿%'; if Vcount0>0 then C_BRNL00:=1; else C_BRNL00:=16; end if; else --按年龄 begin SELECT FLOOR((MONTHS_BETWEEN(SYSDATE,TO_DATE(BRCSRQ,'YYYYMMDD')))/12) INTO C_BRNL00 FROM BM_BRXXB0 WHERE BRID00 =:NEW.BRID00; exception when others then C_BRNL00:=30; end; end if; select count(1) into Vcount0 from bm_ghlbb0 a, sf_brxxb0 b where a.lbbh00=b.ghlb00 and b.ghh000=:NEW.GHH000 and a.lbmc00 like '%急诊%'; IF (C_BRNL00<=15) and (VEKCFJDFS<>'2') THEN--儿科处方处理 '2'儿科 ‘3‘儿科加急诊 if Vcount0>0 then :NEW.CFDGS0:='3'; else :NEW.CFDGS0:='2'; end if; elsif (C_BRNL00<=14) and (VEKCFJDFS='2') then --儿科处方处理 '2'儿科 ‘3‘儿科加急诊 if Vcount0>0 then :NEW.CFDGS0:='3'; else :NEW.CFDGS0:='2'; end if; else if Vcount0>0 then :NEW.CFDGS0:='4'; else :NEW.CFDGS0:='5'; end if; END IF; END IF; END IF; SELECT TO_CHAR(SYSDATE,'YYYYMMDD'),TO_CHAR(SYSDATE,'HH24:MI:SS') INTO V_DQRQ00,V_DQSJ00 FROM DUAL; ------------现金病人单独计算零售价--- IF (:OLD.SFXJJZ='0' AND :NEW.SFXJJZ='1') THEN --修改处方明细表中的单价为最新药典单价。 OPEN c_MZCFMX; FETCH c_MZCFMX INTO v_MZCFMX; WHILE c_MZCFMX%FOUND LOOP --SPACK_YF_BRXX00.PACK_BRID00:=:NEW.BRID00; --SPACK_YF_BRXX00.PACK_CFSRBZ:=:NEW.CFSRBZ; --SPACK_YF_BRXX00.PACK_SFKSLY:=:NEW.SFKSLY; --SPACK_YF_BRXX00.PACK_YFBMBH:=:NEW.YFBMBH; --SPACK_YF_BRXX00.PACK_ZYTS00:=:NEW.ZYTS00; --SPACK_YF_BRXX00.PACK_CFZT00:=:NEW.CFZT00; begin -- by MZSF-20160301-001 诊疗带出处方时，冲销时不在更新零售单价,否则会出现yf_ypkcxx.LSDJ00改变后会跟原冲销的单价不一致问题 if (:NEW.BZ0000 in ('诊疗项目带出处方','诊疗带出处方')) and (trim(:NEW.BCCFH0) is not null) and (:NEW.CFZJE0<0) then LS_GXLSDJ:='N';--是否更新单价 Y:是 N:否 end if; if LS_GXLSDJ='Y' then UPDATE YF_MZCFMX Q SET (GJDJ00,LSDJ00,PFDJ00)= (SELECT ROUND(X.GJDJ00*Q.ZHL000/D.K2J000,4), ROUND(X.LSDJ00*Q.ZHL000/D.K2J000,4), ROUND(D.PFJ000*Q.ZHL000/D.K2J000,4) FROM BM_YD0000 D,YF_YPKCXX X WHERE Q.YPNM00=D.YPNM00 AND Q.YPNM00=X.YPNM00 AND X.YFBMBH=:OLD.YFBMBH) WHERE CFLSH0=:NEW.CFLSH0 AND SFZBY0='N' AND Q.YPNM00=v_MZCFMX.YPNM00; end if; EXCEPTION WHEN OTHERS THEN V_errmsg:='药品: ['||Trim(v_MZCFMX.YPMC00)||'] 在药品隶属中已删除'; RAISE E_custom; END; FETCH c_MZCFMX INTO v_MZCFMX; END LOOP; CLOSE c_MZCFMX; --计算成药金额和药械金额 --如处方全为自备药，则金额全为零 SELECT COUNT(1) INTO V_COUNT0 FROM YF_MZCFMX WHERE CFLSH0=:NEW.CFLSH0 AND SFZBY0='N'; IF V_COUNT0=0 THEN :NEW.CYJE00:=0; :NEW.YXJE00:=0; :NEW.CFZJE0:=0; ELSE SELECT SUM(DECODE(YPDLBH,'1',round(LSDJ00*YPZSL0,2),0)), SUM(DECODE(YPDLBH,'3',round(LSDJ00*YPZSL0,2),0)), SUM(round(YPZSL0*LSDJ00,2)) INTO :NEW.CYJE00,:NEW.YXJE00,:NEW.CFZJE0 FROM YF_MZCFMX WHERE CFLSH0=:NEW.CFLSH0 AND SFZBY0='N'; END IF; END IF; IF :NEW.YFBMBH<>:OLD.YFBMBH THEN OPEN c_MZCFMX; FETCH c_MZCFMX INTO v_MZCFMX; WHILE c_MZCFMX%FOUND LOOP IF :NEW.SFKSLY='N' then--科室领药不做处理 AAA --抢药处理 分成几种情况1：未记帐发药抢药 2：已记帐发药抢药 IF :OLD.CFZT00='0' THEN--1：未记帐发药抢药 还原原有药房的yf_ypkcxx.ykkcsl 扣除新药房的yf_ypkcxx.ykkcsl AA IF V_MZCFMX.SFSLYK='Y' THEN-- --还原原有库存 UPDATE YF_YPKCXX SET YKKCSL=YKKCSL+(V_MZCFMX.YPZSL0*V_MZCFMX.ZHL000) WHERE YFBMBH=:OLD.YFBMBH AND YPNM00=V_MZCFMX.YPNM00; --判断库存是否足够并处理临界点的库存 SP_YF_PDYKKCSFZG(:new.YFBMBH,V_MZCFMX.YPNM00,V_MZCFMX.YPZSL0,V_MZCFMX.ZHL000,0,V_MZCFMX.YPMC00,LS_KCYKSL,LS_KCSJSL,LS_ERRMSG,LS_ERRLB0); if LS_ERRLB0=0 then Close c_MZCFMX; v_errmsg:=LS_ERRMSG; raise E_custom; end if; --扣除原有库存 --YKKCSL-(V_MZCFMX.YPZSL0*V_MZCFMX.ZHL000) UPDATE YF_YPKCXX SET YKKCSL=YKKCSL-LS_KCYKSL WHERE YFBMBH=:NEW.YFBMBH AND YPNM00=V_MZCFMX.YPNM00; --else 输入没有扣库存，所以不需处理。 END IF; ELSIF :OLD.CFZT00='1' THEN IF V_MZCFMX.SFSLYK='Y' THEN----还原原有药房的yf_ypkcxx.ykkcsl 扣除新药房的yf_ypkcxx.ykkcsl --还原原有库存 UPDATE YF_YPKCXX SET YKKCSL=YKKCSL+(V_MZCFMX.YPZSL0*V_MZCFMX.ZHL000) WHERE YFBMBH=:OLD.YFBMBH AND YPNM00=V_MZCFMX.YPNM00; --判断库存是否足够并处理临界点的库存 SP_YF_PDYKKCSFZG(:new.YFBMBH,V_MZCFMX.YPNM00,V_MZCFMX.YPZSL0,V_MZCFMX.ZHL000,0,V_MZCFMX.YPMC00,LS_KCYKSL,LS_KCSJSL,LS_ERRMSG,LS_ERRLB0); if LS_ERRLB0=0 then Close c_MZCFMX; v_errmsg:=LS_ERRMSG; raise E_custom; end if; --扣除原有库存 --YKKCSL-(V_MZCFMX.YPZSL0*V_MZCFMX.ZHL000) UPDATE YF_YPKCXX SET YKKCSL=YKKCSL-LS_KCYKSL WHERE YFBMBH=:NEW.YFBMBH AND YPNM00=V_MZCFMX.YPNM00; END IF; --多零售价，批次库存处理 IF v_DLSJGL='Y' then--A --还原原有药房的 yf_pckcmx.ykkcsl扣除新药房的yf_pckcmx.YKKCSL --取K2J000 SELECT K2J000 INTO V_K2J000 FROM BM_YD0000 WHERE YPNM00=v_MZCFMX.YPNM00; --原来药房退预扣 SP_YF_PCKCCK(:OLD.YFBMBH,V_MZCFMX.YPNM00,-v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,ROUND(V_MZCFMX.LSDJ00*V_K2J000/V_MZCFMX.ZHL000,4),:NEW.CFLSH0,V_DQRQ00,V_DQSJ00,0,ROUND(V_MZCFMX.GJDJ00*V_K2J000/V_MZCFMX.ZHL000,4)); --现在药房再新扣 SP_YF_PCKCYK(:NEW.YFBMBH,V_MZCFMX.YPNM00,V_MZCFMX.YPZSL0*V_MZCFMX.ZHL000,1,V_DQLSDJ,V_DQGJDJ); --如果预扣零售价不等处方零售价，因为此时病人已记账，不可以再改处方零售价，所以再退预扣 IF (ROUND(V_DQLSDJ,2)<>ROUND(V_MZCFMX.LSDJ00*V_K2J000/V_MZCFMX.ZHL000,2)) OR (ROUND(V_DQGJDJ,2)<>ROUND(V_MZCFMX.GJDJ00*V_K2J000/V_MZCFMX.ZHL000,2)) THEN SP_YF_PCKCCK(:NEW.YFBMBH,V_MZCFMX.YPNM00,-ROUND(V_MZCFMX.YPZSL0*V_MZCFMX.ZHL000,3),V_DQLSDJ,:NEW.CFLSH0,V_DQRQ00,V_DQSJ00,0,V_DQGJDJ); END IF; END IF;--A END IF;--AA END IF;--AAA FETCH c_MZCFMX INTO v_MZCFMX; END LOOP; CLOSE c_MZCFMX; END IF; ---------------------记帐－－－－－－－－－－－－ IF (:OLD.CFZT00='0' AND :NEW.CFZT00='1') THEN IF v_DLSJGL='Y' AND :NEW.SFKSLY='N' THEN OPEN c_MZCFMX; FETCH c_MZCFMX INTO v_MZCFMX; WHILE c_MZCFMX%FOUND LOOP --判断批次预扣库存是否足够 begin SELECT SUM(YKKCSL) INTO V_PCYKZL FROM YF_PCKCMX WHERE YFBMBH=:OLD.YFBMBH AND YPNM00=v_MZCFMX.YPNM00; EXCEPTION WHEN OTHERS THEN V_errmsg:='药品: ['||Trim(v_MZCFMX.YPMC00)||'] 在药品隶属中已删除'; RAISE E_custom; END; --取K2J000 SELECT K2J000,GJJ000 INTO V_K2J000,V_GJJ000 FROM BM_YD0000 WHERE YPNM00=v_MZCFMX.YPNM00; IF ROUND(V_PCYKZL/v_MZCFMX.ZHL000,3)-v_MZCFMX.YPZSL0<0 THEN V_TEMPSL:=ROUND((v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000-V_PCYKZL)/v_MZCFMX.ZHL000,4); if V_TEMPSL>0.001 then CLOSE c_MZCFMX; V_errmsg:='药品: ['||Trim(v_MZCFMX.YPMC00)||'] 无预扣库存可记账'; RAISE E_custom; end if; END IF; --预扣-------------- IF :NEW.SFXJJZ='1' THEN SP_YF_PCKCYK(:OLD.YFBMBH,v_MZCFMX.YPNM00,v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,1,V_DQLSDJ,V_DQGJDJ,:NEW.CFLSH0); ELSE SP_YF_PCKCYK(:OLD.YFBMBH,v_MZCFMX.YPNM00,v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,0,V_DQLSDJ,V_DQGJDJ,:NEW.CFLSH0); END IF; --修改处方价格,现金记账,不修改金额 IF :NEW.SFXJJZ='0' THEN --SPACK_YF_BRXX00.PACK_BRID00:=:NEW.BRID00; --SPACK_YF_BRXX00.PACK_CFSRBZ:=:NEW.CFSRBZ; --SPACK_YF_BRXX00.PACK_SFKSLY:=:NEW.SFKSLY; --SPACK_YF_BRXX00.PACK_YFBMBH:=:NEW.YFBMBH; --SPACK_YF_BRXX00.PACK_ZYTS00:=:NEW.ZYTS00; --SPACK_YF_BRXX00.PACK_CFZT00:=:NEW.CFZT00; --也同时修改YF_MZCFMX购进单价 UPDATE YF_MZCFMX SET LSDJ00=ROUND(V_DQLSDJ*v_MZCFMX.ZHL000/V_K2J000,4),GJDJ00=ROUND(V_DQGJDJ*v_MZCFMX.ZHL000/V_K2J000,4) WHERE CFLSH0=:NEW.CFLSH0 AND YPNM00=v_MZCFMX.YPNM00 ; END IF; FETCH c_MZCFMX INTO v_MZCFMX; END LOOP; CLOSE c_MZCFMX; ELSIF V_DLSJGL='Y' AND :NEW.SFKSLY='Y' THEN --科室领药不做科室批次预扣 OPEN c_MZCFMX; FETCH c_MZCFMX INTO v_MZCFMX; WHILE c_MZCFMX%FOUND LOOP --SPACK_YF_BRXX00.PACK_BRID00:=:NEW.BRID00; --SPACK_YF_BRXX00.PACK_CFSRBZ:=:NEW.CFSRBZ; --SPACK_YF_BRXX00.PACK_SFKSLY:=:NEW.SFKSLY; --SPACK_YF_BRXX00.PACK_YFBMBH:=:NEW.YFBMBH; --SPACK_YF_BRXX00.PACK_ZYTS00:=:NEW.ZYTS00; --SPACK_YF_BRXX00.PACK_CFZT00:=:NEW.CFZT00; --修改处方明细表中的单价为最新药典单价。 begin --诊疗项目带出处方实际执行本段代码 -- by MZSF-20160301-001 诊疗带出处方时，冲销时不在更新零售单价,否则会出现yf_ypkcxx.LSDJ00改变后会跟原冲销的单价不一致问题 if (:NEW.BZ0000 in ('诊疗项目带出处方')) and (trim(:NEW.BCCFH0) is not null) and (:NEW.CFZJE0<0) then LS_GXLSDJ:='N';--是否更新单价 Y:是 N:否 end if; if LS_GXLSDJ='Y' then UPDATE YF_MZCFMX Q SET (GJDJ00,LSDJ00,PFDJ00)= (SELECT ROUND(X.GJDJ00*Q.ZHL000/D.K2J000,4), ROUND(X.LSDJ00*Q.ZHL000/D.K2J000,4), ROUND(D.PFJ000*Q.ZHL000/D.K2J000,4) FROM BM_YD0000 D,YF_YPKCXX X WHERE Q.YPNM00=X.YPNM00 AND Q.YPNM00=D.YPNM00 AND X.YFBMBH=:OLD.YFBMBH) WHERE CFLSH0=:NEW.CFLSH0 AND SFZBY0='N' AND Q.YPNM00=v_MZCFMX.YPNM00; end if; EXCEPTION WHEN OTHERS THEN V_errmsg:='药品: ['||Trim(v_MZCFMX.YPMC00)||'] 在药品隶属中已删除'; RAISE E_custom; END; FETCH c_MZCFMX INTO v_MZCFMX; END LOOP; CLOSE c_MZCFMX; ELSE OPEN c_MZCFMX; FETCH c_MZCFMX INTO v_MZCFMX; WHILE c_MZCFMX%FOUND LOOP --SPACK_YF_BRXX00.PACK_BRID00:=:NEW.BRID00; --SPACK_YF_BRXX00.PACK_CFSRBZ:=:NEW.CFSRBZ; --SPACK_YF_BRXX00.PACK_SFKSLY:=:NEW.SFKSLY; --SPACK_YF_BRXX00.PACK_YFBMBH:=:NEW.YFBMBH; --SPACK_YF_BRXX00.PACK_ZYTS00:=:NEW.ZYTS00; --SPACK_YF_BRXX00.PACK_CFZT00:=:NEW.CFZT00; --修改处方明细表中的单价为最新药典单价。 IF (:NEW.SFXJJZ='0') THEN begin UPDATE YF_MZCFMX Q SET (GJDJ00,LSDJ00,PFDJ00)= (SELECT ROUND(A.GJDJ00*Q.ZHL000/D.K2J000,4), ROUND(A.LSDJ00*Q.ZHL000/D.K2J000,4), ROUND(D.PFJ000*Q.ZHL000/D.K2J000,4) FROM YF_YPKCXX A,BM_YD0000 D WHERE A.YFBMBH=:NEW.YFBMBH AND A.YPNM00=D.YPNM00 AND Q.YPNM00=D.YPNM00) WHERE CFLSH0=:NEW.CFLSH0 AND SFZBY0='N' AND Q.YPNM00=v_MZCFMX.YPNM00; --AND Q.CFID00=v_MZCFMX.CFID00; EXCEPTION WHEN OTHERS THEN V_errmsg:='药品: ['||Trim(v_MZCFMX.YPMC00)||']'||:new.BRID00||' 在药品隶属中已删除'; RAISE E_custom; END; END IF; FETCH c_MZCFMX INTO v_MZCFMX; END LOOP; CLOSE c_MZCFMX; END IF; --重新计算总金额 --计算成药金额和药械金额,现金记账已经计算过,这里不再计算 IF (:NEW.SFXJJZ='0') THEN --如处方全为自备药，则金额全为零 SELECT COUNT(1) INTO V_COUNT0 FROM YF_MZCFMX WHERE CFLSH0=:NEW.CFLSH0 AND SFZBY0='N'; IF V_COUNT0=0 THEN :NEW.CYJE00:=0; :NEW.YXJE00:=0; :NEW.CFZJE0:=0; ELSE SELECT SUM(DECODE(YPDLBH,'1',round(LSDJ00*YPZSL0,2),0)), SUM(DECODE(YPDLBH,'3',round(LSDJ00*YPZSL0,2),0)), SUM(round(YPZSL0*LSDJ00,2)) INTO :NEW.CYJE00,:NEW.YXJE00,:NEW.CFZJE0 FROM YF_MZCFMX WHERE CFLSH0=:NEW.CFLSH0 AND SFZBY0='N'; END IF; END IF; --医生处方信息表 UPDATE YS_CFXXB0 SET CFZJE0=:NEW.CFZJE0 WHERE CFLSH0=:NEW.CFLSH0; END IF; ------------------------------发药------------------------------- IF ((:OLD.CFZT00='1' AND :NEW.CFZT00='2') ) --发药 OR((:OLD.CFZT00='0' AND :NEW.CFZT00='5') AND :NEW.CFZJE0<0) THEN --退药 IF YF_PDQJSFYXFY='N' THEN SELECT FYBZ00 INTO V_FYBZ00 FROM BM_BMBM00 WHERE BMBH00=:OLD.YFBMBH; IF V_FYBZ00='3' THEN V_errmsg:='药房正在盘点,不能进行进出库确认'; RAISE E_custom; END IF; END IF; v_SFCFZYJJ:='N'; --病人是否存在自费消费账户 IF (:NEW.SFKSLY='N') then select count(*) into V_COUNT0 from BM_BRXTB0 where to_char(sysdate,'YYYYMMDD')>=QYRQ00 and to_char(sysdate,'YYYYMMDDHH24:MI:SS')>=QYRQ00||QYSJ00 and TZRQ00>=to_char(sysdate,'YYYYMMDD') and TZRQ00||TZSJ00>=to_char(sysdate,'YYYYMMDDHH24:MI:SS') and BRID00=:NEW.BRID00; IF (V_COUNT0>0 ) and (:NEW.CFZJE0>0) and (:new.SFKSLY='N')THEN select ZFBRID into I_BRID00 from BM_BRXTB0 where to_char(sysdate,'YYYYMMDD')>=QYRQ00 and to_char(sysdate,'YYYYMMDDHH24:MI:SS')>=QYRQ00||QYSJ00 and TZRQ00>=to_char(sysdate,'YYYYMMDD') and TZRQ00||TZSJ00>=to_char(sysdate,'YYYYMMDDHH24:MI:SS') and BRID00=:NEW.BRID00; --if SPACK_YF_BRXX00.PACK_SFHY00='Y' then if :new.SFHY00='Y' then SP_YF_CFZRK(:NEW.CFLSH0,:NEW.YFBMBH,:NEW.CZY000,I_BRID00,:new.DJH000); end if; v_SFCFZYJJ:='Y'; END IF; END IF; OPEN c_MZCFMX; FETCH c_MZCFMX INTO v_MZCFMX; WHILE c_MZCFMX%FOUND LOOP -- if (:OLD.CFZT00='1' AND :NEW.CFZT00='2') then --判断库存是否足够并处理临界点的库存 if v_MZCFMX.SFSLYK = 'Y' AND :NEW.CFZT00='2' then SP_YF_PDYKKCSFZG(:new.YFBMBH,V_MZCFMX.YPNM00,V_MZCFMX.YPZSL0,V_MZCFMX.ZHL000,1,V_MZCFMX.YPMC00,LS_KCYKSL,LS_KCSJSL,LS_ERRMSG,LS_ERRLB0); else SP_YF_PDYKKCSFZG(:new.YFBMBH,V_MZCFMX.YPNM00,V_MZCFMX.YPZSL0,V_MZCFMX.ZHL000,2,V_MZCFMX.YPMC00,LS_KCYKSL,LS_KCSJSL,LS_ERRMSG,LS_ERRLB0); end if; if LS_ERRLB0=0 then CLOSE c_MZCFMX; v_errmsg:=LS_ERRMSG; raise E_custom; end if; -- end if; ------判断库存是否充足--------- -- begin -- SELECT NVL(SJKCSL,0) INTO V_SJKCSL FROM YF_YPKCXX -- WHERE YFBMBH=:OLD.YFBMBH -- AND YPNM00=v_MZCFMX.YPNM00; -- EXCEPTION -- WHEN OTHERS THEN -- V_errmsg:='药品: ['||Trim(v_MZCFMX.YPMC00)||'] 在药品隶属中已删除'; -- RAISE E_custom; -- END; V_TEMPSL:=0; --IF (V_SJKCSL-ROUND(v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3))<0 AND v_SFPDKC='Y' THEN --V_TEMPSL:=round((v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000-v_SJKCSL)/v_MZCFMX.ZHL000,4); --IF V_TEMPSL>=0.001 THEN -- CLOSE c_MZCFMX; -- V_errmsg:='药品:'||RTRIM(v_MZCFMX.YPMC00||'库存:'||to_char(V_SJKCSL)||',出库数量:'||to_char(v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000))||',库存不足'; -- RAISE E_custom; -- END IF; -- END IF; --取K2J000 SELECT K2J000,SFZBYP INTO V_K2J000,V_SFZBYP FROM BM_YD0000 WHERE YPNM00=v_MZCFMX.YPNM00; ----------修改库存--------------发药时判断是否预扣,退药直接实际和预扣一起退 --如果单位为处方单位的库存与发药数量相比库存小于0.001则将库存直接改成0 -- IF (V_TEMPSL>0) AND (V_TEMPSL<0.001) THEN -- IF v_MZCFMX.SFSLYK = 'Y' AND :NEW.CFZT00='2' THEN -- UPDATE YF_YPKCXX SET -- SJKCSL=0 -- WHERE YFBMBH=:OLD.YFBMBH -- AND YPNM00=v_MZCFMX.YPNM00; -- ELSE -- UPDATE YF_YPKCXX SET -- YKKCSL=NVL(YKKCSL,0)-ROUND(v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3), -- SJKCSL=0 -- WHERE YFBMBH=:OLD.YFBMBH -- AND YPNM00=v_MZCFMX.YPNM00; -- END IF; -- ELSE IF v_MZCFMX.SFSLYK = 'Y' AND :NEW.CFZT00='2' THEN UPDATE YF_YPKCXX SET SJKCSL=NVL(SJKCSL,0)-ls_KCSJSL --ROUND(v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3) WHERE YFBMBH=:OLD.YFBMBH AND YPNM00=v_MZCFMX.YPNM00; ELSE UPDATE YF_YPKCXX SET YKKCSL=NVL(YKKCSL,0)-ls_KCYKSL,--ROUND(v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3), SJKCSL=NVL(SJKCSL,0)-ls_KCSJSL--ROUND(v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3) WHERE YFBMBH=:OLD.YFBMBH AND YPNM00=v_MZCFMX.YPNM00; END IF; -- END IF; --临时变量清0 V_TEMPSL:=0; --取库存和库存金额 SELECT ROUND(SJKCSL/V_K2J000,3),ROUND(ROUND(SJKCSL/V_K2J000,3)*LSDJ00,2) INTO V_KCSL00,V_KCJE00 FROM YF_YPKCXX WHERE YFBMBH=:NEW.YFBMBH AND YPNM00=v_MZCFMX.YPNM00; SELECT BMMC00 INTO V_CKBMMC FROM BM_BMBM00 WHERE BMBH00=:NEW.GHKS00; ------------多零售价管理 ,发药 IF v_DLSJGL='Y' THEN if v_SFCFZYJJ='Y' then --处方转预交金 由于记账时候没有扣除批次预扣库存，所以现在补扣。 IF :NEW.SFXJJZ='1' THEN SP_YF_PCKCYK(:OLD.YFBMBH,v_MZCFMX.YPNM00,v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,1,V_DQLSDJ,V_DQGJDJ); ELSE SP_YF_PCKCYK(:OLD.YFBMBH,v_MZCFMX.YPNM00,v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,0,V_DQLSDJ,V_DQGJDJ); END IF; end if; IF :NEW.CFZJE0<0 THEN SP_YF_PCKCCK(:NEW.YFBMBH,v_MZCFMX.YPNM00,ROUND(v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3),ROUND(v_MZCFMX.LSDJ00*V_K2J000/v_MZCFMX.ZHL000,4),:NEW.CFLSH0,V_DQRQ00,V_DQSJ00,1,ROUND(v_MZCFMX.GJDJ00*V_K2J000/v_MZCFMX.ZHL000,4)); ELSE IF :NEW.SFKSLY='Y' THEN --科室领药 SP_YF_PCKCCK(:NEW.YFBMBH,v_MZCFMX.YPNM00,ROUND(v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3),ROUND(v_MZCFMX.LSDJ00*V_K2J000/v_MZCFMX.ZHL000,4),:NEW.CFLSH0,V_DQRQ00,V_DQSJ00,0,ROUND(v_MZCFMX.GJDJ00*V_K2J000/v_MZCFMX.ZHL000,4)); ELSE IF :NEW.SFXJJZ='1' THEN --现金记账 SP_YF_PCKCCK(:NEW.YFBMBH,v_MZCFMX.YPNM00,ROUND(v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3),ROUND(v_MZCFMX.LSDJ00*V_K2J000/v_MZCFMX.ZHL000,4),:NEW.CFLSH0,V_DQRQ00,V_DQSJ00,2,ROUND(v_MZCFMX.GJDJ00*V_K2J000/v_MZCFMX.ZHL000,4)); ELSE SP_YF_PCKCCK(:NEW.YFBMBH,v_MZCFMX.YPNM00,ROUND(v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3),ROUND(v_MZCFMX.LSDJ00*V_K2J000/v_MZCFMX.ZHL000,4),:NEW.CFLSH0,V_DQRQ00,V_DQSJ00,1,ROUND(v_MZCFMX.GJDJ00*V_K2J000/v_MZCFMX.ZHL000,4)); END IF; END IF; END IF; ELSE ----------修改批次库存-------------- IF v_MZCFMX.YPZSL0>0 THEN --扣除批次库存 SP_YF_JSPCKC(:OLD.YFBMBH,v_MZCFMX.YPNM00,ROUND(v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3)); ELSE --冲销预扣库存’加回去' --增加批次库存 SP_YF_ZJPCKC(:OLD.YFBMBH,v_MZCFMX.YPNM00,-ROUND(v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3)); END IF; END IF; --德化医院要求摘要显示病人姓名 IF v_KHHCS0='13' THEN V_ZY0000:=:NEW.BRXM00; ELSE V_ZY0000:=V_CKBMMC; END IF; BEGIN SELECT BMMC00 INTO V_ZY0001 FROM BM_BMBM00 WHERE BMBH00=:new.LYKS00; EXCEPTION when others then V_ZY0001:=''; END; --------------出库记帐--------------------------- INSERT INTO YF_YPMXZ0( MXZLSH, --明细账流水号-->SQ_YF_YPMXZ0_MXZLSH YFBMBH, --药房部门编号 --YPCKPC, --药品出库批次-->YF_YPCKMX YPNM00, --药品内码 YPMC00, --药品名称 YPGG00, --规格 JZRQ00, --记账日期 JZSJ00, --记账时间 CRBMBH, --出入部门编号 ZY0000, --摘要(自动：出入部门名称) JZDW00, --记账单位 ZHL000, --转换率(记账单位/剂量单位) GJDJ00, --购进单价(记账单位) FCDJ00, --付出单价(零售价)(记账单位) FCSL00, --付出数量(记账单位) FCJE00, --付出金额(零售价) JCDJ00, --结存单价(结存金额/结存数量) JCSL00, --结存数量(记账单位)(药品总量) JCJE00, --结存金额(零售价)(药品总量) RCLXBH, --入出库类型编码:BM_YFRKLX,BM_YFCKLX RCLXMC, --入出库类型名称:BM_YFRKLX,BM_YFCKLX JZXZ00, --记账性质:'R',入库,'C',出库 SFZBYP, --中标药品 DJH000, --单据号 GJFCJE, --购进付出金额 KSBH00, ZY0001 ) VALUES (SQ_YF_YPMXZ0_MXZLSH.NEXTVAL, :NEW.YFBMBH, -- mod(v_MZCFMX.CFID00,1E17), v_MZCFMX.YPNM00, v_MZCFMX.YPMC00, v_MZCFMX.YPGG00, V_DQRQ00, V_DQSJ00, DECODE(:NEW.LYKS00,NULL,:NEW.GHKS00,:NEW.LYKS00), V_ZY0000, v_MZCFMX.CFDW00, v_MZCFMX.ZHL000, v_MZCFMX.GJDJ00, v_MZCFMX.LSDJ00, v_MZCFMX.YPZSL0, ROUND(v_MZCFMX.YPZSL0*v_MZCFMX.LSDJ00,2), -- DECODE(v_KCSL00,0,0,ROUND(v_KCJE00/v_KCSL00,4)), v_MZCFMX.LSDJ00, V_KCSL00, V_KCJE00, '09', '门诊处方', 'C', V_SFZBYP, :NEW.CFLSH0, ROUND(v_MZCFMX.YPZSL0*v_MZCFMX.GJDJ00,2), DECODE(:NEW.LYKS00,NULL,:NEW.GHKS00,:NEW.LYKS00), V_ZY0001 ); --判断药品大类编号 if v_SFXGLB='Y' then IF v_MZCFMX.YPDLBH='2' AND :NEW.YPDLBH<>'2' THEN :NEW.YPDLBH:='2'; END IF; IF v_MZCFMX.YPDLBH<>'2' AND :NEW.YPDLBH='2' THEN :NEW.YPDLBH:=v_MZCFMX.YPDLBH; END IF; else IF v_MZCFMX.YPDLBH='2' AND :NEW.YPDLBH<>'2' and :NEW.CFXZ00='0' THEN :NEW.YPDLBH:='2'; END IF; IF v_MZCFMX.YPDLBH<>'2' AND :NEW.YPDLBH='2' and :NEW.CFXZ00='0' THEN :NEW.YPDLBH:=v_MZCFMX.YPDLBH; END IF; end if; FETCH c_MZCFMX INTO v_MZCFMX; END LOOP; CLOSE c_MZCFMX; END IF; --------------------已记账退预扣---------------------- IF ((:OLD.CFZT00='0' AND :NEW.CFZT00='6') AND :NEW.CFZJE0<0) THEN OPEN c_MZCFMX; FETCH c_MZCFMX INTO v_MZCFMX; WHILE c_MZCFMX%FOUND LOOP --查询被冲处方是否数量预扣 SELECT SFSLYK INTO V_SFSLYK FROM YF_MZCFMX WHERE CFID00=v_MZCFMX.CXCFID; IF V_SFSLYK='Y' THEN UPDATE YF_YPKCXX SET YKKCSL=NVL(YKKCSL,0)-ROUND(v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3) WHERE YFBMBH=:OLD.YFBMBH AND YPNM00=v_MZCFMX.YPNM00; UPDATE YF_MZCFMX SET SFSLYK='Y' WHERE CFLSH0=:NEW.CFLSH0 AND YPNM00=v_MZCFMX.YPNM00;--CFID00=v_MZCFMX.CFID00; END IF; if :NEW.SFKSLY='N' then SELECT K2J000,SFZBYP INTO V_K2J000,V_SFZBYP FROM BM_YD0000 WHERE YPNM00=v_MZCFMX.YPNM00; SP_YF_PCKCCK(:NEW.YFBMBH,v_MZCFMX.YPNM00,ROUND(v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3),ROUND(v_MZCFMX.LSDJ00*V_K2J000/v_MZCFMX.ZHL000,4),:NEW.CFLSH0,V_DQRQ00,V_DQSJ00,0,ROUND(v_MZCFMX.GJDJ00*V_K2J000/v_MZCFMX.ZHL000,4)); end if; FETCH c_MZCFMX INTO v_MZCFMX; END LOOP; CLOSE c_MZCFMX; END IF; --------------------退费完，换药处方自动做负的入库，并退预交金---------------------- IF ( ( (:OLD.CFZT00='0') or (:OLD.CFZT00='5')) and (:NEW.CFZT00='6') and :NEW.CFZJE0<0) THEN --判断原正处方是否换药 select count(*) into v_count0 from YF_YPRKD0 where YPQLDH=:NEW.BCCFH0 and RKLXBH=12; if v_count0>0 then begin select ZFBRID into I_BRID00 from BM_BRXTB0 where BRID00=:NEW.BRID00; exception when others then RAISE_APPLICATION_ERROR(-20266,substrb('病人对应的自费账户没找到'||'！*',1,120)); end; SP_YF_CFZRK(:NEW.CFLSH0,:NEW.YFBMBH,:NEW.CZY000,I_BRID00,:new.DJH000); end if; END IF; --------------------已退药作废---------------------------------------------------------------- IF ((:OLD.CFZT00='5' AND :NEW.CFZT00='3') AND :NEW.CFZJE0<0) THEN IF YF_PDQJSFYXFY='N' THEN SELECT FYBZ00 INTO V_FYBZ00 FROM BM_BMBM00 WHERE BMBH00=:OLD.YFBMBH; IF V_FYBZ00='3' THEN V_errmsg:='药房正在盘点,不能进行进出库确认'; RAISE E_custom; END IF; END IF; OPEN c_MZCFMX; FETCH c_MZCFMX INTO v_MZCFMX; WHILE c_MZCFMX%FOUND LOOP ------判断库存是否充足--------- -- SELECT NVL(SJKCSL,0) INTO V_SJKCSL FROM YF_YPKCXX -- WHERE YFBMBH=:OLD.YFBMBH -- AND YPNM00=v_MZCFMX.YPNM00; V_TEMPSL:=0; -- IF (V_SJKCSL-ROUND(-v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3))<0 AND v_SFPDKC='Y' THEN -- V_TEMPSL:=round((-v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000-v_SJKCSL)/v_MZCFMX.ZHL000,4); -- IF V_TEMPSL>=0.001 THEN -- CLOSE c_MZCFMX; -- V_errmsg:='药品:'||RTRIM(v_MZCFMX.YPMC00||'库存:'||to_char(V_SJKCSL)||',出库数量:'||to_char(-v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000))||',库存不足'; -- RAISE E_custom; -- END IF; -- END IF; --判断库存是否足够并处理临界点的库存 SP_YF_PDYKKCSFZG(:new.YFBMBH,V_MZCFMX.YPNM00,-V_MZCFMX.YPZSL0,V_MZCFMX.ZHL000,2,V_MZCFMX.YPMC00,LS_KCYKSL,LS_KCSJSL,LS_ERRMSG,LS_ERRLB0); if LS_ERRLB0=0 then CLOSE c_MZCFMX; v_errmsg:=LS_ERRMSG; raise E_custom; end if; --取K2J000 SELECT K2J000 INTO V_K2J000 FROM BM_YD0000 WHERE YPNM00=v_MZCFMX.YPNM00; ----------修改库存-------------- --如果单位为处方单位的库存与发药数量相比库存小于0.001则将库存直接改成0 -- IF (V_TEMPSL>0) AND (V_TEMPSL<0.001) THEN -- UPDATE YF_YPKCXX SET -- YKKCSL=NVL(YKKCSL,0)-ROUND(-v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3), -- SJKCSL=0 -- WHERE YFBMBH=:OLD.YFBMBH -- AND YPNM00=v_MZCFMX.YPNM00; -- ELSE UPDATE YF_YPKCXX SET YKKCSL=NVL(YKKCSL,0)-ls_KCYKSL,--ROUND(-v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3), SJKCSL=NVL(SJKCSL,0)-ls_KCSJSL--ROUND(-v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3) WHERE YFBMBH=:OLD.YFBMBH AND YPNM00=v_MZCFMX.YPNM00; -- END IF; --临时变量清0 V_TEMPSL:=0; --取库存和库存金额 SELECT ROUND(SJKCSL/V_K2J000,3),ROUND(ROUND(SJKCSL/V_K2J000,3)*LSDJ00,2) INTO V_KCSL00,V_KCJE00 FROM YF_YPKCXX WHERE YFBMBH=:NEW.YFBMBH AND YPNM00=v_MZCFMX.YPNM00; SELECT BMMC00 INTO V_CKBMMC FROM BM_BMBM00 WHERE BMBH00=:NEW.GHKS00; ------------多零售价管理 ,发药 IF v_DLSJGL='Y' THEN SP_YF_PCKCCK(:NEW.YFBMBH,v_MZCFMX.YPNM00,ROUND(-v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3),ROUND(v_MZCFMX.LSDJ00*V_K2J000/v_MZCFMX.ZHL000,4),:NEW.CFLSH0,V_DQRQ00,V_DQSJ00,0,ROUND(v_MZCFMX.GJDJ00*V_K2J000/v_MZCFMX.ZHL000,4)); ELSE SP_YF_JSPCKC(:OLD.YFBMBH,v_MZCFMX.YPNM00,ROUND(-v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3)); END IF; --修改被冲处方 UPDATE YF_MZCFMX SET ZCXSL0=ZCXSL0+v_MZCFMX.YPZSL0 WHERE CFID00=v_MZCFMX.CXCFID; UPDATE YF_MZCFMX SET CXBZ00='Z' WHERE CFID00=v_MZCFMX.CXCFID AND ZCXSL0=0; --德化医院要求摘要显示病人姓名 IF v_KHHCS0='13' THEN V_ZY0000:=:NEW.BRXM00; ELSE V_ZY0000:=:NEW.GHKS00; END IF; BEGIN SELECT BMMC00 INTO V_ZY0001 FROM BM_BMBM00 WHERE BMBH00=:new.LYKS00; EXCEPTION when others then V_ZY0001:=''; END; --------------出库记帐--------------------------- INSERT INTO YF_YPMXZ0( MXZLSH, --明细账流水号-->SQ_YF_YPMXZ0_MXZLSH YFBMBH, --药房部门编号 --YPCKPC, --药品出库批次-->YF_YPCKMX YPNM00, --药品内码 YPMC00, --药品名称 YPGG00, --规格 JZRQ00, --记账日期 JZSJ00, --记账时间 CRBMBH, --出入部门编号 ZY0000, --摘要(自动：出入部门名称) JZDW00, --记账单位 ZHL000, --转换率(记账单位/剂量单位) GJDJ00, --购进单价(记账单位) FCDJ00, --付出单价(零售价)(记账单位) FCSL00, --付出数量(记账单位) FCJE00, --付出金额(零售价) JCDJ00, --结存单价(结存金额/结存数量) JCSL00, --结存数量(记账单位)(药品总量) JCJE00, --结存金额(零售价)(药品总量) RCLXBH, --入出库类型编码:BM_YFRKLX,BM_YFCKLX RCLXMC, --入出库类型名称:BM_YFRKLX,BM_YFCKLX JZXZ00, --记账性质:'R',入库,'C',出库 SFZBYP, --是否招标药 DJH000, --单据号 GJFCJE, KSBH00, ZY0001 ) VALUES (SQ_YF_YPMXZ0_MXZLSH.NEXTVAL, :NEW.YFBMBH, --mod(v_MZCFMX.CFID00,1E17), v_MZCFMX.YPNM00, v_MZCFMX.YPMC00, v_MZCFMX.YPGG00, V_DQRQ00, V_DQSJ00, DECODE(:NEW.LYKS00,NULL,:NEW.GHKS00,:NEW.LYKS00), V_CKBMMC, v_MZCFMX.CFDW00, v_MZCFMX.ZHL000, v_MZCFMX.GJDJ00, v_MZCFMX.LSDJ00, -v_MZCFMX.YPZSL0, -ROUND(v_MZCFMX.YPZSL0*v_MZCFMX.LSDJ00,2), -- DECODE(v_KCSL00,0,0,ROUND(v_KCJE00/v_KCSL00,4)), v_MZCFMX.LSDJ00, V_KCSL00, V_KCJE00, '09', '门诊处方', 'C', V_SFZBYP, :NEW.CFLSH0, -ROUND(v_MZCFMX.YPZSL0*v_MZCFMX.GJDJ00,2),DECODE(:NEW.LYKS00,NULL,:NEW.GHKS00,:NEW.LYKS00),V_ZY0001); FETCH c_MZCFMX INTO v_MZCFMX; END LOOP; CLOSE c_MZCFMX; END IF; END IF; --*******************数量预扣**************** -- insert 申请记录 --需要预扣, - -- update 申请记录 -- 申请记录作废'0'-->'3' --返回预扣, + -- delete 申请记录 --返回预扣, + -- -- update 退药记录 -- 退药记录,'0'-->'5','6' --返回预扣, - -- 退药记录作废'5'-->'3' --需要预扣 -- delete 退药记录 --不作处理 --正常处方作废,返回预扣 IF :OLD.CFZT00='0' AND :NEW.CFZT00='3' AND V_YKYXTS<>'0' THEN OPEN c_MZCFMX; FETCH c_MZCFMX INTO v_MZCFMX; WHILE c_MZCFMX%FOUND LOOP IF v_MZCFMX.SFSLYK='Y' THEN UPDATE YF_YPKCXX SET YKKCSL=NVL(YKKCSL,0)+ROUND(v_MZCFMX.YPZSL0*v_MZCFMX.ZHL000,3) WHERE YFBMBH=:OLD.YFBMBH AND YPNM00=v_MZCFMX.YPNM00; END IF; FETCH c_MZCFMX INTO v_MZCFMX; END LOOP; CLOSE c_MZCFMX; END IF; --考虑数据迁移，将删除部分独立触发器 /*ELSE IF ((:OLD.CFZT00='1') OR (:OLD.CFZT00='2')) THEN --已经确认的数据被删除 RAISE E_NoDelete; END IF; END IF;*/ ------------------- 添加yf_mzcf00补打记录操作日志 ------------------------ IF UPDATING THEN--修改 IF ((NVL(:OLD.CFBDCS,0)=0) AND (:NEW.CFBDCS=1)) OR (:NEW.CFBDCS>:OLD.CFBDCS) THEN Select userenv('SESSIONID') PROCESS Into VS_PROCESS from dual where rownum=1; Select nvl(max(YGBH00),0) Into VS_YGBH00 From XT_XTRZ00 Where JCID00=VS_PROCESS And LSH000=(select max(LSH000) from XT_XTRZ00 where JCID00=VS_PROCESS); --CZLX00 0新增 1删除 2修改 INSERT INTO XT_XTCZRZ(CZID00,CZLX00,CZRQ00,CZSJ00,CZRYBM,CZNR00,CZTABLE,DATAID) VALUES(SQ_XT_XTCZRZ_ID0000.NEXTVAL,2,TO_CHAR(SYSDATE,'YYYYMMDD'),TO_CHAR(SYSDATE,'HH24:MI:SS'),VS_YGBH00,'update:病人姓名:'||:OLD.BRXM00||';处方流水号:'||:OLD.CFLSH0||';补打次数:'||:NEW.CFBDCS,'YF_MZCF00',to_number(trim(substr(lpad(replace(:OLD.CFLSH0,' '),16,'0'),-10,10)))); END IF; END IF; EXCEPTION WHEN E_NoDelete THEN RAISE_APPLICATION_ERROR(-20120, '该记录已经被确认,不能再删除！*'); WHEN E_NoEdit THEN RAISE_APPLICATION_ERROR(-20119, '该记录已经被确认,不能再修改！*'); WHEN no_data_found THEN RAISE_APPLICATION_ERROR(-20001,substrb('数据没有找到！*'||SQLERRM||',',1,120)); WHEN E_custom THEN RAISE_APPLICATION_ERROR(-20010,substrb(V_errmsg||'！*',1,120)); WHEN OTHERS THEN --SPACK_YF_BRXX00.PACK_BRID00:=0; --SPACK_YF_BRXX00.PACK_CFSRBZ:='0'; RAISE_APPLICATION_ERROR(-20266,substrb(nvl(SQLERRM, '原因不明出错')||'！*',1,120)); END TR_YF_MZCF00_AFUPD; /