CREATE OR REPLACE PROCEDURE SD_HOSPITAL.SP_BQ_FJFYTF
( PYZID00  NUMBER,--医嘱号
  PTQDJH0  NUMBER,--提取单据号
  PZYID00  NUMBER,--住院ID
  PCZY000  NUMBER --操作员
)
AS
  counter        NUMBER(5);
  VRETURN        NUMBER(5);
  VKCXSL         NUMBER(12);
  VDQKS00        NUMBER(5);
  VCZYXM0        BM_YGBM00.ZWXM00%TYPE;
  VMXID00        ZY_FYMX00.MXID00%TYPE;
  Vid            NUMBER(2);
  E_idccfw       EXCEPTION;--ID号超出范围,请与程序员联系
  E_jlyycz       EXCEPTION;--记录已经存在
  E_YTF          EXCEPTION;--记录已经存在
  E_YSQTF        EXCEPTION;--记录已经存在
  E_TFCW         EXCEPTION;--记录已经存在
  vparams        varchar2(255);
  CURSOR C_FJFY00 IS
         SELECT ZYH000,  ZYID00  ,XMLB00  ,TQDJH0  ,SFDJH0  ,YZID00  ,ZXKS00  ,JFLBID  ,
                FBBH00,  SFXMID  ,XMMC00  ,CS0000  ,CXCS00  ,DW0000  ,SFJE00  ,ZFJE00  ,
                JZJE00,  GFJE00  ,CZY000  ,ZXRQ00  ,CZRQ00  ,CZSJ00  ,ID0000  ,CXBZ00
         FROM   BQ_FJF000
         WHERE  ZYID00=PZYID00 AND CXBZ00='+' AND CXCS00=0 and CS0000>0 AND XMLB00='0';
BEGIN
  vparams:='SP_BQ_FJFYTF('||TO_CHAR(PYZID00)||','||TO_CHAR(PTQDJH0)||','||TO_CHAR(PZYID00)||','||TO_CHAR(PCZY000)||')';
  --如果静滴冲销,加药自动跟着冲销
  UPDATE BQ_FJF000 F SET CXBZ00='+'
  WHERE  ZYID00=PZYID00 AND XMLB00='0'AND CXBZ00='Z' AND CXCS00=0 AND YZID00=0 AND TQDJH0=0 AND CS0000<0 AND
         exists (SELECT 1 FROM BQ_FJF000 J
                    WHERE J.ZYID00=PZYID00 AND F.SFDJH0=J.SFDJH0 AND F.ZXRQ00=J.ZXRQ00 AND
                          XMLB00='0' AND CXBZ00='+' AND CXCS00=0 AND YZID00=0 AND TQDJH0=0);
  SELECT ZWXM00 INTO VCZYXM0 FROM BM_YGBM00 WHERE YGBH00=PCZY000;
  FOR FJFY IN C_FJFY00 LOOP
      --判定是否已被冲销
      SELECT COUNT(*) INTO COUNTER FROM ZY_FYMX00
      WHERE  XMBH00=FJFY.SFXMID AND DJH000=FJFY.SFDJH0 AND XMSL00=FJFY.CS0000 AND CXBZ00='Z';
      IF COUNTER=0 THEN
        RAISE E_YTF;--无可冲费用,费用已冲
      END IF;
      SELECT COUNT(*) INTO COUNTER FROM ZY_FYMX00 M
      WHERE  XMBH00=FJFY.SFXMID AND DJH000=FJFY.SFDJH0 AND XMSL00=FJFY.CS0000 AND CXBZ00='Z' AND
             MXID00 NOT IN (SELECT MXID00 FROM YJ_YW0000 J,YJ_YWJJ00 W
                            WHERE  J.YJDJH0=W.YJDJH0 AND J.SFDJH0=FJFY.SFDJH0 AND ZYGHID=FJFY.ZYID00 AND MZZYBZ='1');
      IF COUNTER=0 THEN
        RAISE E_YSQTF;--已申请退费
      END IF;
      SELECT MXID00 INTO VMXID00 FROM ZY_FYMX00 M
      WHERE  XMBH00=FJFY.SFXMID AND DJH000=FJFY.SFDJH0 AND XMSL00=FJFY.CS0000 AND CXBZ00='Z' AND ROWNUM=1 AND
             MXID00 NOT IN (SELECT MXID00 FROM YJ_YW0000 J,YJ_YWJJ00 W
                            WHERE  J.YJDJH0=W.YJDJH0 AND J.SFDJH0=FJFY.SFDJH0 AND ZYGHID=FJFY.ZYID00 AND MZZYBZ='1');
      VRETURN:=SF_BQ_FJFYTF(FJFY.SFDJH0,FJFY.ZYID00,VMXID00,VCZYXM0,1,PCZY000);
      IF VRETURN<>1 THEN
        RAISE E_TFCW;
      END IF;
  END LOOP;
  UPDATE BQ_FJF000 SET CXCS00=CS0000 WHERE ZYID00=PZYID00 AND CXBZ00='+' AND XMLB00='0'AND CXCS00=0;
  COMMIT;
EXCEPTION
    WHEN E_YSQTF THEN
      RAISE_APPLICATION_ERROR(-20001,'已申请退费,请手工冲销！*');
    WHEN E_YTF THEN
      RAISE_APPLICATION_ERROR(-20002,'费用已冲,请手工冲销！*');
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20010, '数据没有找到*');
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20020, substrb(NVL(SQLERRM, '原因不明出错')||'！*'||vparams,1,220));
END SP_BQ_FJFYTF;