CREATE OR REPLACE PROCEDURE SP_YS_CFTY( P_CFLSH0 in varchar2, --输入：处方流水号 PS_CFID00 in varchar2, --处方ID批次组合字符窜(格式 'xxxx*xxxx*xxxx...*') P_CZYBH0 in number, --操作员编码 P_SFCP in number, --是否需要复制被冲销的处方0--否，1，是 P_OUT_CFLSH0 out varchar2 --输出：复制的处方流水号 )AS V_CFLSH0 YF_MZCF00.CFLSH0%TYPE; V_CFLSH1 YF_MZCF00.CFLSH0%TYPE; V_CFID00 YF_MZCFMX.CFID00%TYPE; V_CFID01 YF_MZCFMX.CFID00%TYPE; V_CFID02 YF_MZCFMX.CFID00%TYPE; V_CFZJE0 YF_MZCF00.CFZJE0%TYPE; V_DQRQ00 YF_MZCF00.FYRQ00%TYPE; V_DQSJ00 YF_MZCF00.FYSJ00%TYPE; LS_CFLSH0 YF_MZCF00.CFLSH0%TYPE; V_COUNTER NUMBER; V_EMessage VARCHAR2(100); V_CFMXJE_SRWS NUMBER(1); --处方明细金额--舍入位数(小数位保留位数) V_CFZJE_SRWS NUMBER(1); --处方总金额--舍入位数(小数位保留位数) V_CFMXJE_CZFS NUMBER(1); --处方明细金额--取整方式0--四舍五入,1--向前取整 V_CFZJE_CZFS NUMBER(1); --处方总金额--取整方式0--四舍五入,1--向前取整 YS_SFXZYJSFYBNZJCX XT_XTCS00.VALUE0%TYPE; --是否限制已结算费用门诊医生平台不能直接冲销,得先取消结算才行 L NUMBER; I NUMBER; J NUMBER; E_NoEdit EXCEPTION; E_FYYJS0 EXCEPTION; --费用已结算 YS_CFTYXZ varchar2(10); LS_CFZT00 YF_MZCF00.CFZT00%type; LS_ERROR varchar2(200); -- MODIFICATION HISTORY -- Person Date Comments -- zhr 2007.07.11 复制处方时，应该同步复制CFDGS0信息 -- csf 2011.04.06 复制处方时，应该同步复制f2j000,另外退时候也应该写入f2j000 -- zhangwz 2011.11.23 增加参数 YS_SFXZYJSFYBNZJCX 控制已结算的费用不能直接冲销申请. by MZYS-20111123-001. -- zhangyc 2012.09.24 YF_MZCFMX增加字段记账金额JZJE00 by MZSF-20120713-001 -- dsm 2020.10.13 增加参数YS_CFTYXZ(门诊医生不能退费) for MZYS-20200929-002 BEGIN ls_cflsh0:=trim(p_cflsh0); begin select trim(value0) into YS_SFXZYJSFYBNZJCX from XT_XTCS00 where NAME00='YS_SFXZYJSFYBNZJCX'; exception when others then YS_SFXZYJSFYBNZJCX:='N'; end; begin select trim(value0) into YS_CFTYXZ from XT_XTCS00 where NAME00='YS_CFTYXZ'; exception when others then YS_CFTYXZ:='N'; end; if trim(YS_SFXZYJSFYBNZJCX)='Y' then begin select count(*) into V_COUNTER from YF_MZCF00 a,SF_BRFY00 b where a.CFLSH0=ls_CFLSH0 and a.DJH000=b.DJH000 and b.JZDH00+0>0; if V_COUNTER>0 then begin LS_ERROR:='要冲销的处方已经结算，不能直接冲销，请先收费处取消结算'; RAISE E_FYYJS0; end; end if; end; end if; if YS_CFTYXZ='Y' then select CFZT00 into LS_CFZT00 from YF_MZCF00 where CFLSH0=ls_cflsh0; if LS_CFZT00 in('1','2') then LS_ERROR:='对不起，已收费处方限制冲销'; RAISE E_FYYJS0; end if; end if; P_OUT_CFLSH0:=''; SELECT TO_CHAR(SYSDATE,'YYYYMMDD'),TO_CHAR(SYSDATE,'HH24:MI:SS') INTO V_DQRQ00,V_DQSJ00 FROM DUAL; ---------功能：对指定处方号产生负的处方，修改原处方状态、复制新处方 SELECT SF_YK_GETLSH('MZYF_MZCFH') INTO V_CFLSH0 FROM DUAL; INSERT INTO YF_MZCF00(CFLSH0, YFBMBH, GHID00, GHH000, BRID00, BRXM00, XB0000, CSRQ00, YSGZH0, YSXM00, SSKS00, GHKS00, ZYTS00, YPDLBH, CFXZ00, BZID00, BZMC00, CFZT00, CZY000, CFSRBZ, PFR000, FHR000, SRRQ00, SRSJ00, CFZJE0, BRDZ00, DJH000, FJFDJH, BZ0000, CYJE00, BCCFH0, TSBZBH) select V_CFLSH0, YFBMBH, GHID00, GHH000, BRID00, BRXM00, XB0000, CSRQ00, YSGZH0, YSXM00, SSKS00, GHKS00, ZYTS00, YPDLBH, CFXZ00, BZID00, BZMC00, '0', P_CZYBH0,CFSRBZ, PFR000, FHR000,V_DQRQ00,V_DQSJ00, -CFZJE0, BRDZ00, null , null, BZ0000, -CYJE00, CFLSH0, TSBZBH from YF_MZCF00 where CFLSH0=ls_CFLSH0; --被冲销处方流水号 --更新医生处方表的处方状态； UPDATE YS_CFXXB0 set CFZT00='2' where CFLSH0=ls_CFLSH0; IF P_SFCP=1 THEN --复制新的记录 SELECT SF_YK_GETLSH('MZYF_MZCFH') INTO V_CFLSH1 FROM DUAL; INSERT INTO YF_MZCF00(CFLSH0, YFBMBH, GHID00, GHH000, BRID00, BRXM00, XB0000, CSRQ00, YSGZH0, YSXM00, SSKS00, GHKS00, ZYTS00, YPDLBH, CFXZ00, BZID00, BZMC00, CFZT00, CZY000, CFSRBZ, PFR000, FHR000, SRRQ00, SRSJ00, CFZJE0, BRDZ00, DJH000, FJFDJH, CYJE00, BCCFH0, TSBZBH, CFDGS0) select V_CFLSH1, YFBMBH, GHID00, GHH000, BRID00, BRXM00, XB0000, CSRQ00, YSGZH0, YSXM00, SSKS00, GHKS00, ZYTS00, YPDLBH, CFXZ00, BZID00, BZMC00, '0' ,P_CZYBH0, '0' , PFR000, FHR000,V_DQRQ00,V_DQSJ00, CFZJE0, BRDZ00, 0 , 0 , CYJE00, LS_CFLSH0, TSBZBH,CFDGS0 from YF_MZCF00 where CFLSH0=LS_CFLSH0; --被冲销处方流水号 INSERT INTO YS_CFXXB0(CFLSH0, YFBMBH, GHID00, GHH000, BRID00, BRXM00, XB0000, DJH000, BRLB00, YSGZH0, SSKS00, GHKS00, YPDLBH, CFXZ00, ZYTS00, BZID00, CFZT00, FJFDJH, CZY000, SRRQ00, SRSJ00, CFMBMC, CFZJE0, BRDZ00, TSBZBH) select V_CFLSH1, YFBMBH, GHID00, GHH000, BRID00, BRXM00, XB0000, 0, BRLB00, YSGZH0, SSKS00, GHKS00, YPDLBH, CFXZ00, ZYTS00, BZID00, '1' , 0 ,P_CZYBH0,V_DQRQ00,V_DQSJ00, CFMBMC, CFZJE0, BRDZ00, TSBZBH FROM YS_CFXXB0 WHERE CFLSH0=ls_CFLSH0; --病人地址(主要用于毒麻处方) END IF; --I,记录出现'*'的上一次的位置, --J,计数器, --K,记录出现'*'的当前位置 I:=0; J:=1; SELECT INSTR(PS_CFID00,'*',1,J) INTO L FROM DUAL; --取出第J次出现'*'的位置 WHILE L>0 LOOP --取出处方明细批次组合字符窜中的处方批次 V_CFID00:=TO_NUMBER(SUBSTR(PS_CFID00,I+1,L-I-1)); -- SELECT SQ_YS_CFMX00_CFID00.NEXTVAL INTO V_CFID01 FROM DUAL; INSERT INTO YF_MZCFMX(CFID00, CFLSH0, YPNM00, YPMC00, YPGG00, GJDJ00, LSDJ00, PFDJ00, YPYFMC, SFZBY0, PCMC00, CSL000, JLDW00, YYSJ00, YYSJDW, YFSMMC, ZB0000, ZJYFMC, XMYFMC, YPZSL0, CFDW00, ZHL000, CXBZ00, CXCFID, SFXS00, YPSCCJ, YPSCPH, KPHSS0, HSKPPH, ZFBL00, YBLB00, YPDLBH, ZFJE00, QZFJE0, GFJE00, BZ0000, F2J000,JZJE00,ZFFSBZ) select V_CFID01,V_CFLSH0, YPNM00, YPMC00, YPGG00, GJDJ00, LSDJ00, PFDJ00, YPYFMC, SFZBY0, PCMC00, -CSL000, JLDW00, YYSJ00, YYSJDW, YFSMMC, ZB0000, ZJYFMC, XMYFMC, -YPZSL0, CFDW00, ZHL000, '-' , CFID00, SFXS00, YPSCCJ, YPSCPH, KPHSS0, HSKPPH, ZFBL00, YBLB00, YPDLBH, -ZFJE00, -QZFJE0, -GFJE00, BZ0000, F2J000,-NVL(JZJE00,0),ZFFSBZ FROM YF_MZCFMX WHERE CFID00=V_CFID00 and CXBZ00='Z'; --备注 if SQL%NOTFOUND then SELECT YPMC00 into V_EMessage FROM YF_MZCFMX WHERE CFID00=V_CFID00; Raise E_NoEdit; end if; IF P_SFCP=1 THEN --复制新的记录 SELECT SQ_YS_CFMX00_CFID00.NEXTVAL INTO V_CFID02 FROM DUAL; INSERT INTO YF_MZCFMX(CFID00, CFLSH0, YPNM00, YPMC00, YPGG00, GJDJ00, LSDJ00, PFDJ00, YPYFMC, SFZBY0, PCMC00, CSL000, JLDW00, YYSJ00, YYSJDW, YFSMMC, ZB0000, ZJYFMC, XMYFMC, YPZSL0, CFDW00, ZHL000, CXBZ00, SFXS00, YPSCCJ, YPSCPH, KPHSS0, HSKPPH, ZFBL00, YBLB00, YPDLBH, BZ0000, ZXCS00, F2J000,ZFFSBZ) select V_CFID02,V_CFLSH1, YPNM00, YPMC00, YPGG00, GJDJ00, LSDJ00, PFDJ00, YPYFMC, SFZBY0, PCMC00, CSL000, JLDW00, YYSJ00, YYSJDW, YFSMMC, ZB0000, ZJYFMC, XMYFMC, YPZSL0, CFDW00, ZHL000, 'Z' , SFXS00, YPSCCJ, YPSCPH, KPHSS0, HSKPPH, ZFBL00, YBLB00, YPDLBH, BZ0000, ZXCS00, F2J000 ,ZFFSBZ FROM YF_MZCFMX WHERE CFID00=V_CFID00 ; --备注 INSERT INTO YS_CFMX00(CFID00, CFLSH0, YPNM00, YPMC00, YPGG00, LSDJ00, YFID00, PCID00, CSL000, JLDW00, YYSJ00, ZQCS00, YYSJDW, YYZF00, ZB0000, PSLBID, SFZBY0, ZJYFID, XMYFMC, YPZSL0, CFDW00, ZHL000, ZFBL00, YBLB00, SFXS00, ZXCS00, F2J000) select V_CFID02,V_CFLSH1, YPNM00, YPMC00, YPGG00, LSDJ00, YFID00, PCID00, CSL000, JLDW00, YYSJ00, ZQCS00, YYSJDW, YYZF00, ZB0000, PSLBID, SFZBY0, ZJYFID, XMYFMC, YPZSL0, CFDW00, ZHL000, ZFBL00, YBLB00, SFXS00, ZXCS00, F2J000 FROM YS_CFMX00 WHERE CFID00=V_CFID00; --是否显示'Y'显示,'N'不显示，协定处方用 END IF; ---修改原出库单明细标志 UPDATE YF_MZCFMX SET CXBZ00='+' WHERE CFID00=V_CFID00; UPDATE YS_CFMX00 SET CXBZ00='+' WHERE CFID00=V_CFID00; I:=L; J:=J+1; SELECT INSTR(PS_CFID00,'*',1,J) INTO L FROM DUAL; --取出第J次出现'*'的位置 END LOOP; ---FOR WHILE ... LOOP OUTER --计算冲销处方金额 BEGIN SELECT VALUE0 INTO V_CFMXJE_SRWS FROM XT_XTCS00 WHERE NAME00='YF_CFMXJE_SRWS'; EXCEPTION WHEN OTHERS THEN V_CFMXJE_SRWS:=2; END; BEGIN SELECT VALUE0 INTO V_CFZJE_SRWS FROM XT_XTCS00 WHERE NAME00='YF_CFZJE_SRWS'; EXCEPTION WHEN OTHERS THEN V_CFZJE_SRWS:=2; END; BEGIN SELECT VALUE0 INTO V_CFMXJE_CZFS FROM XT_XTCS00 WHERE NAME00='YF_CFMXJE_CZFS'; EXCEPTION WHEN OTHERS THEN V_CFMXJE_CZFS:=0; END; BEGIN SELECT VALUE0 INTO V_CFZJE_CZFS FROM XT_XTCS00 WHERE NAME00='YF_CFZJE_CZFS'; EXCEPTION WHEN OTHERS THEN V_CFZJE_CZFS:=0; END; SELECT SUM(SF_YF_EXROUND(-YPZSL0*LSDJ00,V_CFMXJE_CZFS,V_CFMXJE_SRWS)) INTO V_CFZJE0 FROM YF_MZCFMX WHERE CFLSH0=V_CFLSH0 AND SFZBY0='N'; V_CFZJE0:=SF_YF_EXROUND(V_CFZJE0,V_CFZJE_CZFS,V_CFZJE_SRWS); --修改更正处方总金额 UPDATE YF_MZCF00 SET CFZJE0=-V_CFZJE0 WHERE CFLSH0=V_CFLSH0; UPDATE YF_MZCF00 SET CFZJE0=V_CFZJE0 WHERE CFLSH0=V_CFLSH1; P_OUT_CFLSH0:=V_CFLSH1; COMMIT; EXCEPTION WHEN E_NoEdit THEN RAISE_APPLICATION_ERROR(-20001, '药品:'||V_EMessage||'已经被退过,不能再退药！*'|| '调用SP_YS_CFTY('||ls_CFLSH0||','||PS_CFID00||','|| TO_CHAR(P_CZYBH0)||','||TO_CHAR(P_SFCP)||','||P_OUT_CFLSH0||')'||SQLERRM); ROLLBACK; WHEN E_FYYJS0 THEN RAISE_APPLICATION_ERROR(-20001, LS_ERROR||'！*'|| '调用SP_YS_CFTY('||ls_CFLSH0||','||PS_CFID00||','|| TO_CHAR(P_CZYBH0)||','||TO_CHAR(P_SFCP)||','||P_OUT_CFLSH0||')'||SQLERRM); ROLLBACK; WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20004, '退药操作时发生未找到数据错误！*'|| '调用SP_YS_CFTY('||ls_CFLSH0||','||PS_CFID00||','|| TO_CHAR(P_CZYBH0)||','||TO_CHAR(P_SFCP)||','||P_OUT_CFLSH0||')'||SQLERRM); ROLLBACK; WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20005,'退药时发生意外错误！*'|| '调用SP_YS_CFTY('||ls_CFLSH0||','||PS_CFID00||','|| TO_CHAR(P_CZYBH0)||','||TO_CHAR(P_SFCP)||','||P_OUT_CFLSH0||')'||SQLERRM); ROLLBACK; END ;