CREATE OR REPLACE PROCEDURE SP_BQ_YJYZZX_XZ
( PCZY000 IN NUMBER,  --操作员
  PZYID00 IN NUMBER,  --住院流水号
  PBQH000 IN NUMBER,  --病区号
  PYZMXID IN CHAR  ,  --医嘱明细串
  PZXRQ00 IN DATE  ,  --提取日期
  PBCTQID OUT NUMBER,  --本次提取ID
  Pcommit in char default 'Y', --是否提交
  PBRQFXX OUT VARCHAR2,   --病人欠费信息
  PQXQXQF in varchar2 default 'N',  --强行取消病人欠费
  PHZYZBZ in char default '0'    ,  --会诊标志
  PYSTJDZ in char default 'N'       --是否住院医生提交医嘱操作动作 Y 是
)
AS
  COUNTER                     NUMBER;
  I                           NUMBER(3);
  J                           NUMBER(3);
  INIT                        NUMBER(3);
  STRYZID                 VARCHAR2(255);
  VPARAMS                 VARCHAR2(255);
  VCZYXM0         BM_YGBM00.ZWXM00%TYPE;
  VSL0000         YJ_YW0000.SL0000%TYPE;
  VYJDJH0         YJ_YW0000.YJDJH0%TYPE;
  VYJFZH0         YJ_YW0000.YJFZH0%TYPE;
  VYZMXID         BQ_YJYZLS.YZMXID%TYPE;
  VZLXMID         BM_ZLZD00.ZLXMID%TYPE;
  VMCSL00         BQ_YJYZLS.MCSL00%TYPE;
  VCS0000         BQ_YJYZLS.CS0000%TYPE;
  VPCID00         BM_PC0000.PCID00%TYPE;
  VHSZQCD         BM_PC0000.HSZQCD%TYPE;
  VQYRQSJ         BQ_YJYZLS.QYRQSJ%TYPE;
  VTQRQSJ         BQ_YJYZLS.TQRQSJ%TYPE;
  VJZSJ00         CHAR(16);
  VQSRQSJ         CHAR(16);
  VZXKS00         BM_BMBM00.BMBH00%TYPE;
  VYZCLLB         BQ_YJYZLS.YZCLLB%TYPE;
  VKSH000         BM_BMBM00.BMBH00%TYPE;
  VJFLBID         BM_BRJFLB.JFLBID%TYPE;   --计费类别(帐户类别)
  VRETURN         NUMBER(10)           ;   --函数的返回值
  VJZRQSJ         DATE;                    --本次医嘱提取截至日期(含时间)
  VSYSDATE        DATE;
  VYZSJFG         CHAR(8)              ;   --医嘱时间分割点
  E_MYTQSJ        EXCEPTION;               --没有提取日期，无法提取
  E_YZSJFGCSCW    EXCEPTION;               --系统参数:医嘱时间分割点错误
  E_MYYZZX        EXCEPTION;               --没有医嘱可以提取;
  E_MYCFJL        EXCEPTION;               --数据不完整，部分医嘱没有拆分记录;
  E_HLXMCW        EXCEPTION;               --护理项目错误
  E_ZHLBCSCW      EXCEPTION;               --帐户类别参数错误
  E_YZZNDTTQ      EXCEPTION;               --医嘱只能当天提取
  E_CFQWT         EXCEPTION;               --医嘱拆分的触发器问题
  E_CSCW          EXCEPTION;               --参数错误
  E_QYSJDYRYSJ    EXCEPTION;               --医技启用时间小于病人入院时间
  VCYRQSJ         DATE;                    --病人预出院日期和时间
  VMYYZZX         CHAR(1);                 --没有医嘱执行
  VWHTQTS         NUMBER(5);               --医嘱允许往后提取的天数
  VMINIRQ         BQ_YJYZ00.QYRQ00%TYPE;
  VMAXMRQ         BQ_YJYZ00.QYRQ00%TYPE;
  sMSYZ00                   CHAR(1);
  V_YZSFZDHD            CHAR(1); --医嘱是否自动核对
  V_YZZXXZQF      CHAR(1); --单人非药医嘱执行时，是否允许选择是否欠费执行 Y：是 N：否 默认为N
  v_commit        char(1); --是否直接提交
  v_xzqf00        char(1); --是否选择欠费
  CURSOR C_YEYZ00 IS SELECT YEXM00,COUNT(*) YZSL00 FROM BQ_YJYZ00
                     WHERE  ZYID00=PZYID00 AND YEXM00 IS NOT NULL AND YZZT00 IN ('1','2') AND
                            ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM)
                     GROUP BY YEXM00 HAVING COUNT(*)>1 ;

-- MODIFICATION HISTORY
-- Person      Date        Comments
-- YANGY      2008.03.12   增加参数控制是否直接commit
-- YANGY      2008.04.12   描述医嘱病区执行出错
-- YANGY      2008.06.11   描述医嘱执行时填写执行时间
-- yangy        2008.06.26   病人医嘱执行完后，不要在电子公告上显示
-- yangy        2008.08.12   描述性医嘱执行第二天的会报错
-- YANGY      2009.07.30   医嘱执行可以选择是否允许欠费
-- YANGY      2009.08.01   强行取消欠费
-- Dsm        2012.03.01   会诊医嘱调用时多传参数PHZYZBZ  for ZYYS-20120223-001
-- zhangwz    2012.05.10   增加参数 PYSTJDZ 用于处理住院医生医嘱提交非药短嘱的时候直接生成YJ_YW0000表XMZT00=6的数据 by BQ-20120503-003
-- huangjy    2018.10.31   增加参数BQ_SFTQPDQYSJ控制是否提示启用时间小于病人入院时间  by BQ-20180928-001
BEGIN
  VSYSDATE:=SYSDATE;
  PBRQFXX := NULL;

  IF (NVL(PZYID00,0)<=0) OR (NVL(PBQH000,0)<=0) OR (PZXRQ00 IS NULL) OR (NVL(PCZY000,0)<=0) OR (PYZMXID IS NULL) THEN
    RAISE E_CSCW;--参数错误
  END IF;
  VPARAMS:='SP_BQ_YJYZZX_XZ('||NVL(TO_CHAR(PCZY000),'NULL')||','||NVL(TO_CHAR(PZYID00),'NULL')||','||
           NVL(TO_CHAR(PBQH000),'NULL')||','||PYZMXID||','||NVL(TO_CHAR(PZXRQ00,'YYYYMMDD'),'NULL')||',PBCTQID)';
  BEGIN
    SELECT TO_NUMBER(VALUE0) INTO VWHTQTS FROM XT_XTCS00 WHERE NAME00='BQ_YXWHTQTS_BR';
  EXCEPTION
    WHEN OTHERS THEN
    VWHTQTS:=99999;--不限定往后提取的天数
  END;
  IF PZXRQ00 IS NULL THEN
    RAISE E_MYTQSJ;--没有提取时间，无法提取
  ELSIF TRUNC(PZXRQ00)-TRUNC(VSYSDATE)>VWHTQTS THEN--提取时间限制
    RAISE E_YZZNDTTQ;
  END IF;
  SELECT ZWXM00 INTO VCZYXM0 FROM BM_YGBM00 WHERE YGBH00=PCZY000;
  SELECT COUNT(VALUE0) INTO COUNTER FROM XT_XTCS00 WHERE NAME00='BQ_FYYZSJFG';
  IF COUNTER<>1 THEN
    RAISE E_YZSJFGCSCW;--系统参数:医嘱时间分割点错误
  ELSE
    SELECT SUBSTR(VALUE0,1,8) INTO VYZSJFG FROM XT_XTCS00 WHERE NAME00='BQ_FYYZSJFG';
  END IF;
  --获得提取的截至日期时间(起始时间为当日)
  IF PZXRQ00 IS NOT NULL THEN
    VJZRQSJ:=TO_DATE(TO_CHAR(PZXRQ00,'YYYYMMDD')||VYZSJFG,'YYYYMMDDHH24:MI:SS');
    VJZSJ00:=TO_CHAR(VJZRQSJ,'YYYYMMDDHH24:MI:SS');--字符型
  END IF;

    select nvl(max(trim(value0)),'N') into V_YZSFZDHD from xt_xtcs00 where name00='ZYYS_YZSFZDHD';

  select nvl(trim(max(value0)),'N') into V_YZZXXZQF from xt_xtcs00 where name00='BQ_DRFYYZZXXZQF';
  if (V_YZZXXZQF = 'Y') and (PQXQXQF = 'N') then   --这种情况下，允许选择是否欠费执行
     v_commit := 'N';
     v_xzqf00 := 'Y';
  else
     v_commit := 'Y';
     v_xzqf00 := 'N';
  end if;

  IF (v_commit = 'Y') and (Pcommit = 'N') THEN
     v_commit := 'N';
  END IF;

  --获得本次提取ID
  SELECT SQ_BQ_ZBTQYZ_ID0000.NEXTVAL INTO PBCTQID FROM DUAL WHERE ROWNUM=1;
  DELETE FROM BQ_YJYZLS WHERE ID0000=PBCTQID;
  DELETE FROM BM_YPJJB0_TMP;
  IF v_commit='Y' THEN
    COMMIT;
    END IF;
  --将药品医嘱ID插入到临时表
    STRYZID:=TRIM(PYZMXID);
    IF LENGTH(STRYZID)>0 THEN
      J:=1;--都号基数起
      INIT:=1;--起始位
      LOOP
        I:=INSTR(PYZMXID,',',1,J);
        IF I=0 THEN--结束
          IF J=1 THEN --都没有找到逗号
            INSERT INTO BM_YPJJB0_TMP(YZYYP0)VALUES(TO_NUMBER(STRYZID));
          ELSE  --最后一次J>1
            INSERT INTO BM_YPJJB0_TMP(YZYYP0)VALUES(TO_NUMBER(SUBSTR(STRYZID,INIT,LENGTH(STRYZID)-INIT+1)));
          END IF;
          EXIT;--退出
        END IF;
        counter:=TO_NUMBER(SUBSTR(STRYZID,INIT,I-INIT));
        INSERT INTO BM_YPJJB0_TMP (YZYYP0) VALUES (TO_NUMBER(SUBSTR(STRYZID,INIT,I-INIT)));
        J:=J+1;
        INIT:=I+1; --变更起始位置
      END LOOP;
    END IF;
  --检验是否有未按规则拆分的医技医嘱
  SELECT COUNT(*) INTO COUNTER FROM BQ_YJYZ00 Z
  WHERE  Z.YZMXID IN (SELECT YZYYP0 FROM BM_YPJJB0_TMP) AND Z.YZZT00 IN ('1','2') AND
         NOT EXISTS (SELECT 1 FROM BQ_YJYZCF C WHERE C.YZMXID=Z.YZMXID);
  IF COUNTER>0 THEN
    RAISE E_CFQWT;--医嘱拆分的触发器问题
  END IF;


  SELECT COUNT(*) into COUNTER FROM BQ_YJYZ00 Z,ZY_BRXXB0 B
  WHERE  /*Z.YZMXID IN (SELECT YZYYP0 FROM BM_YPJJB0_TMP) AND*/ Z.YZZT00 IN ('1','2') AND  b.zyid00=PZYID00 and
  Z.ZYID00=b.ZYID00 and  Z.QYRQ00<b.RYRQ00 and exists (select 1 from XT_XTCS00 where NAME00='BQ_SFTQPDQYSJ' and upper(VALUE0)='Y');
  IF COUNTER>0 THEN
    RAISE E_QYSJDYRYSJ;--医技启用时间小于病人入院时间
  END IF;

  --处理嘱咐医嘱
  sMSYZ00 := '0';
  IF PZYID00>0 THEN
     SELECT COUNT(1) INTO  COUNTER  FROM BQ_YJYZ00  WHERE  ZYID00=PZYID00 AND YZZT00 IN ('1','2') AND ZLXMID=0 AND QYRQ00||QYSJ00<=to_char(PZXRQ00,'YYYYMMDD')||VYZSJFG AND
                             NVL(TQRQ00,TO_CHAR(VSYSDATE,'YYYYMMDD'))<=to_char(PZXRQ00,'YYYYMMDD')
                             and YZMXID IN (SELECT YZYYP0 FROM BM_YPJJB0_TMP);
         IF COUNTER>0 THEN

                sMSYZ00 := '1';
            UPDATE BQ_YJYZ00 Y SET  TQRQ00=TO_CHAR(PZXRQ00,'YYYYMMDD'),
                                    TQSJ00=VYZSJFG,
                                    YZZT00=DECODE(CLBZ00,'0','2','1','3'),
                                    TZRQ00=DECODE(CLBZ00,'0',NULL,'1',TO_CHAR(PZXRQ00,'YYYYMMDD')),
                                    TZSJ00=DECODE(CLBZ00,'0',NULL,'1',TO_CHAR(VSYSDATE,'HH24:MI:SS')),
                                    TZYS00=DECODE(CLBZ00,'0',NULL,'1',KZYS00),
                                    TZRXM0=DECODE(CLBZ00,'0',NULL,'1',VCZYXM0),
                                    HDRXM0=DECODE(HDRXM0,NULL,VCZYXM0,HDRXM0),
                                    ZXRQSC=DECODE(ZXRQSC,NULL,to_char(sysdate,'yyyymmdd'),ZXRQSC),
                                    ZXSJSC=DECODE(ZXSJSC,NULL,to_char(sysdate,'hh24:mi:ss'),ZXSJSC)

            WHERE  ZYID00=PZYID00 AND YZZT00 IN ('1','2') AND ZLXMID=0 AND QYRQ00||QYSJ00<=to_char(PZXRQ00,'YYYYMMDD')||VYZSJFG AND
                            NVL(TQRQ00,TO_CHAR(VSYSDATE,'YYYYMMDD'))<=to_char(PZXRQ00,'YYYYMMDD')
                            and YZMXID IN (SELECT YZYYP0 FROM BM_YPJJB0_TMP);
         END IF;
  END IF;


  --开始计算执行的次数
    --预出院病人的出院时间小于提取的截至时间，取预出院时间
    SELECT TO_DATE(CYDJRQ||CYDJSJ,'YYYYMMDDHH24:MI:SS') INTO VCYRQSJ FROM ZY_BRXXB0 WHERE ZYID00=PZYID00;
    IF VCYRQSJ IS NOT NULL THEN
      VJZRQSJ:=LEAST(VCYRQSJ,VJZRQSJ);
      VJZSJ00:=TO_CHAR(VJZRQSJ,'YYYYMMDDHH24:MI:SS');--字符型
    END IF;
    --处理成人的护理医嘱
    SELECT COUNT(*) INTO COUNTER FROM BQ_YJYZ00
    WHERE ZYID00=PZYID00 AND YEXM00 IS NULL AND YZZT00 IN ('1','2') AND ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM)AND ROWNUM=1;
    IF COUNTER>0 THEN
      SELECT COUNT(*) INTO COUNTER FROM BQ_YJYZ00
      WHERE ZYID00=PZYID00 AND YEXM00 IS NULL AND YZZT00 IN ('1','2') AND ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM)AND ROWNUM<4;
      IF COUNTER>2 THEN
        COUNTER:=COUNTER;
        --RAISE E_HLXMCW;--护理项目错误
      ELSIF COUNTER=2 THEN
        SELECT trim(MIN(QYRQ00)),trim(MAX(QYRQ00)) INTO VMINIRQ,VMAXMRQ FROM BQ_YJYZ00
        WHERE  ZYID00=PZYID00 AND YEXM00 IS NULL AND YZZT00 IN ('1','2') AND ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM);
        IF VMINIRQ=VMAXMRQ THEN
            COUNTER:=COUNTER;
            --RAISE E_HLXMCW;--护理项目错误
        ELSE
          --设置停止日期
          SELECT MIN(QSRQ00||QSSJ00)INTO VQSRQSJ FROM BQ_YJYZCF
          WHERE YZMXID=(SELECT YZMXID FROM   BQ_YJYZ00
                        WHERE  ZYID00=PZYID00 AND YEXM00 IS NULL AND
                               YZZT00 IN ('1','2') AND  QYRQ00=VMAXMRQ AND
                               ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM));
          /*
          UPDATE BQ_YJYZ00 SET TZRQ00=SUBSTR(VQSRQSJ,1,8),
                               TZSJ00=SUBSTR(VQSRQSJ,9,8)
          WHERE  ZYID00=PZYID00 AND YEXM00 IS NULL AND YZZT00 IN ('1','2') AND
                 ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM) AND QYRQ00=VMINIRQ;
          */
          --改变病人的护理状态
          UPDATE ZY_BRXXB0 SET HLJBID=(SELECT ZLXMID FROM BQ_YJYZ00
                                       WHERE  ZYID00=PZYID00 AND YZZT00 IN ('1','2') AND YEXM00 IS NULL AND
                                              ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM) AND QYRQ00=VMAXMRQ)
          WHERE ZYID00=PZYID00;
        END IF;
      ELSIF COUNTER=1 THEN--更改病人的护理级别
        UPDATE ZY_BRXXB0 SET HLJBID=(SELECT ZLXMID FROM BQ_YJYZ00
                                     WHERE  ZYID00=PZYID00 AND YZZT00 IN ('1','2') AND YEXM00 IS NULL AND
                                            ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM))
        WHERE ZYID00=PZYID00;
      END IF;
      --处理婴儿的护理医嘱
      FOR YEYZ IN C_YEYZ00 LOOP
        IF YEYZ.YZSL00>2 THEN
          --RAISE E_HLXMCW;--护理项目错误
          COUNTER:=COUNTER;
        ELSIF YEYZ.YZSL00=2 THEN
          SELECT trim(MIN(QYRQ00)),MAX(QYRQ00) INTO VMINIRQ,VMAXMRQ FROM BQ_YJYZ00
          WHERE  ZYID00=PZYID00 AND YEXM00=YEYZ.YEXM00 AND YZZT00 IN ('1','2') AND
                 ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM);
          IF VMINIRQ=VMAXMRQ THEN
            --RAISE E_HLXMCW;
            COUNTER:=COUNTER;
          ELSE
            SELECT trim(MIN(QSRQ00||QSSJ00)) INTO VQSRQSJ FROM BQ_YJYZCF
            WHERE YZMXID=(SELECT YZMXID FROM   BQ_YJYZ00
                          WHERE  ZYID00=PZYID00 AND YEXM00=YEYZ.YEXM00 AND
                                 YZZT00 IN ('1','2') AND  QYRQ00=VMAXMRQ AND
                                 ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM));
            --设置停止日期
            /*
            UPDATE BQ_YJYZ00 SET TZRQ00=SUBSTR(VQSRQSJ,1,8),
                                 TZSJ00=SUBSTR(VQSRQSJ,9,8)
            WHERE  ZYID00=PZYID00 AND YEXM00=YEYZ.YEXM00 AND YZZT00 IN ('1','2') AND
                   ZLXMID IN (SELECT ZLXMID FROM VW_BQ_HLYZXM) AND QYRQ00=VMINIRQ;
            */
          END IF;
        END IF;
      END LOOP;
    END IF;
/*
    SELECT COUNT(1) INTO COUNTER
    FROM BQ_YJYZ00 WHERE  ZYID00=PZYID00 AND YZZT00 IN ('1','2')
    AND ZLXMID=0 AND QYRQ00||QYSJ00<=TO_CHAR(VSYSDATE,'YYYYMMDDHH24:MI:SS') AND
    NVL(TQRQ00,TO_CHAR(VSYSDATE,'YYYYMMDD'))<=TO_CHAR(VSYSDATE,'YYYYMMDD');
*/
    --IF sMSYZ00 = '1' THEN
    --  RETURN;
    --END IF;

    --插入单病人(非本病区执行)的短嘱医技项目
      INSERT INTO BQ_YJYZLS ( ID0000,  YZMXID,  ZYID00,  ZLXMID,  MCSL00,  LBBH00,
                              CLBZ00,  PCID00,  HSZQCD,  ZXKS00,  YZCLLB,  CS0000,
                              QYRQSJ,
                              TQRQSJ,
                              JZRQSJ
                            )
                       SELECT PBCTQID,Y.YZMXID,  ZYID00,  ZLXMID,  MCSL00, YZLB00,
                              CLBZ00,  PCID00, Y.HSZQCD,  ZXKS00,  YZCLLB, TS0000,--周期数
                              F.QSRQSJ,  --有效起始日期
                              F.QSRQSJ,  --VJZRQSJ ,
                              VJZRQSJ
--                              F.QSRQSJ+TS0000*Y.HSZQCD/24,
--                              F.QSRQSJ+TS0000*Y.HSZQCD/24
                       FROM VW_BQ_YJYZ00_WTZ Y,VW_BQ_YJYZCF F
                       WHERE ZYID00=PZYID00 --AND ZXKS00 NOT IN (SELECT KSH000 FROM BM_BQKS00 WHERE BQH000=PBQH000)
                       AND BQH000=PBQH000 AND CLBZ00='1'
                       AND YZZT00 IN ('1','2') AND Y.YZMXID=F.YZMXID
                       AND GREATEST(NVL(Y.CLRQSJ,F.QSRQSJ),F.QSRQSJ)<LEAST(VJZRQSJ,NVL(TZRQSJ,VJZRQSJ))
                       AND Y.YZMXID IN (SELECT YZYYP0 FROM BM_YPJJB0_TMP);
                         --医嘱提取有效时间段[START,END)
    --插入单病人(非本病区执行)的长嘱医技项目
        INSERT INTO BQ_YJYZLS ( ID0000,  YZMXID,  ZYID00,  ZLXMID,  MCSL00,  LBBH00,
                                CLBZ00,  PCID00,  HSZQCD,  ZXKS00,  YZCLLB,
                                QYRQSJ,
                                TQRQSJ,
                                JZRQSJ
                                )
                         SELECT PBCTQID,Y.YZMXID,  ZYID00,  ZLXMID,  MCSL00, YZLB00,
                                CLBZ00,  PCID00, Y.HSZQCD,  ZXKS00,  YZCLLB,
                                F.QSRQSJ,  --有效起始日期
                                GREATEST(NVL(Y.CLRQSJ,F.QSRQSJ),F.QSRQSJ),--上次提取的截至日期
                                LEAST(VJZRQSJ,NVL(TZRQSJ,VJZRQSJ))
                         FROM VW_BQ_YJYZ00_WTZ Y,VW_BQ_YJYZCF F
                         WHERE ZYID00=PZYID00 --AND ZXKS00 NOT IN (SELECT KSH000 FROM BM_BQKS00 WHERE BQH000=PBQH000)
                         AND BQH000=PBQH000 AND CLBZ00='0'
                         AND YZZT00 IN ('1','2') AND Y.YZMXID=F.YZMXID
                         AND GREATEST(NVL(Y.CLRQSJ,F.QSRQSJ),F.QSRQSJ)<LEAST(VJZRQSJ,NVL(TZRQSJ,VJZRQSJ))
                         AND Y.YZMXID IN (SELECT YZYYP0 FROM BM_YPJJB0_TMP);
                         --医嘱提取有效时间段[START,END)
  --数据准备完毕
  --********************开始插入申领单*******************************--
  --判定是否有医嘱
  SELECT COUNT(*) INTO COUNTER FROM BQ_YJYZLS WHERE ID0000=PBCTQID;
  IF COUNTER=0 THEN
    --处理没有执行到的医嘱,包括予出院，定期应用，QOD等
    --判定是否存在没有执行到的医嘱
    SELECT COUNT(*) INTO COUNTER
    FROM VW_BQ_YJYZ00_WTZ Y,VW_BQ_YJYZCF F,VW_BQ_ZYBRXX B
    WHERE Y.ZYID00=B.ZYID00  AND Y.YZMXID=F.YZMXID AND
          B.DQBQ00=PBQH000 AND SUBSTR(BRZT00,1,1)='2' AND B.ZYID00=PZYID00 AND
          Y.BQH000=PBQH000 AND Y.ZYID00=PZYID00 AND Y.YZZT00 IN ('1','2') AND
          GREATEST(NVL(Y.CLRQSJ,F.QSRQSJ),F.QSRQSJ)<LEAST(VJZRQSJ,NVL(TZRQSJ,VJZRQSJ),NVL(B.CYRQSJ,VJZRQSJ)) AND
          Y.YZMXID IN (SELECT YZYYP0 FROM BM_YPJJB0_TMP);
    IF COUNTER>0 THEN
      --修改医嘱的提取时间,定期应用的医嘱要自动停止
      UPDATE BQ_YJYZ00 Z SET TQRQ00=(SELECT DECODE(P.ZQDW00,'小时',TQRQ00,TO_CHAR(PZXRQ00,'YYYYMMDD')) FROM VW_BQ_PC0000 P WHERE Z.PCID00=P.PCID00),
                             TQSJ00=(SELECT DECODE(P.ZQDW00,'小时',TQSJ00,TRIM(VYZSJFG)) FROM VW_BQ_PC0000 P WHERE Z.PCID00=P.PCID00),
                             YZZT00=DECODE(CLBZ00,'1','3','0',DECODE(TZRQ00,NULL,'2',DECODE(SIGN(VJZRQSJ-TO_DATE(TZRQ00||TZSJ00,'YYYYMMDDHH24:MI:SS')),-1,'2','3')))
      WHERE                  ZYID00=PZYID00 AND BQH000=PBQH000 AND CLBZ00='0' AND
                             YZMXID IN (SELECT Y.YZMXID FROM VW_BQ_YJYZ00_WTZ Y,VW_BQ_YJYZCF F,VW_BQ_ZYBRXX B
                                        WHERE Y.ZYID00=B.ZYID00  AND Y.YZMXID=F.YZMXID AND
                                              B.DQBQ00=PBQH000 AND SUBSTR(BRZT00,1,1)='2' AND B.ZYID00=PZYID00 AND
                                              Y.BQH000=PBQH000 AND Y.ZYID00=PZYID00 AND Y.YZZT00 IN ('1','2') AND
                                              GREATEST(NVL(Y.CLRQSJ,F.QSRQSJ),F.QSRQSJ)<LEAST(VJZRQSJ,NVL(TZRQSJ,VJZRQSJ),NVL(B.CYRQSJ,VJZRQSJ)) AND
                                              Z.YZMXID IN (SELECT YZYYP0 FROM BM_YPJJB0_TMP)
                                        )AND
                             NOT EXISTS (SELECT 1 FROM BQ_YJYZLS B WHERE B.ID0000=PBCTQID AND Z.YZMXID=B.YZMXID);
      DELETE FROM BM_YPJJB0_TMP;
      IF v_commit='Y' THEN
        COMMIT;
        END IF;
      RETURN;--结束
    ELSIF sMSYZ00= '0' THEN
      RAISE E_MYYZZX;--没有医嘱执行
    END IF;
  END IF;


  --提取后医嘱不在公告里面显示
  if V_YZSFZDHD='Y' THEN
     --判定是否仍然有新开的医嘱
     select count(*) into COUNTER from (select yzid00 from bq_ypyz00 where zyid00=PZYID00 and yzzt00='1' and rownum=1
                                      union all select yzmxid from bq_yjyz00 where zyid00=PZYID00 and yzzt00='1' and rownum=1);
     if COUNTER=0 then
        Update xt_dzgg00 set YXZZRQ=to_char(sysdate,'YYYYMMDD'),YXZZSJ=to_char(sysdate,'hh24:mi:ss')
        where  zyid00=PZYID00 and CZLB00='1' and YXZZRQ='99991231';
     end if;
     --判定是否仍然有停止医嘱
     select count(*) into COUNTER from (select yzid00 from bq_ypyz00 where zyid00=PZYID00 and yzzt00='3' and tzrxm0 is null and tzrq00=to_char(sysdate,'yyyymmdd') and rownum=1
                                        union all select yzmxid from bq_yjyz00 where zyid00=PZYID00 and yzzt00='3' and tzrxm0 is null and tzrq00=to_char(sysdate,'yyyymmdd') and rownum=1);
     if COUNTER=0 then
        Update xt_dzgg00 set YXZZRQ=to_char(sysdate,'YYYYMMDD'),YXZZSJ=to_char(sysdate,'hh24:mi:ss')
        where  zyid00=PZYID00 and CZLB00='2' and YXZZRQ='99991231';
     end if;

    END IF;

  --计算已经启用长嘱的计算次数(启用时间点，上次提取截至时间点，本次截至提取时间点)
  UPDATE BQ_YJYZLS Y SET CS0000=(SELECT SF_BQ_YZZXCS(Y.QYRQSJ,Y.TQRQSJ,Y.JZRQSJ,P.HSZQCD,P.ZQCS00,P.ZQDW00)
                                 FROM   VW_BQ_PC0000 P
                                 WHERE  Y.PCID00=P.PCID00 )
         WHERE ID0000=PBCTQID AND CLBZ00='0'; --已经启用的长期医嘱
  --短嘱次数(小时的医嘱要*周期次数)
  UPDATE BQ_YJYZLS Y SET CS0000=(SELECT DECODE(P.ZQDW00,'小时',CS0000*ZQCS00,CS0000)
                                 FROM   VW_BQ_PC0000 P
                                 WHERE  Y.PCID00=P.PCID00 )
  WHERE ID0000=PBCTQID AND CLBZ00='1';
  --删除计算次数等于小于0的记录
  DELETE BQ_YJYZLS WHERE (CS0000<=0 OR CS0000 IS NULL ) AND ID0000=PBCTQID;
  --对于小时频次的医嘱，将执行次数合并到每次数量
  UPDATE BQ_YJYZLS Y SET (MCSL00,CS0000)=(SELECT DECODE(P.ZQDW00,'小时',MCSL00*CS0000,MCSL00),
                                                 DECODE(P.ZQDW00,'小时',1,CS0000)
                                          FROM   VW_BQ_PC0000 P WHERE  Y.PCID00=P.PCID00)
  WHERE ID0000=PBCTQID;
  DELETE FROM BM_YPJJB0_TMP;
  --END OF 整理临时表
  --COMMIT;
  --处理有医嘱但未生成医技单 WAITING COMPLETION
  SELECT COUNT(*) INTO COUNTER FROM BM_BRJFLB WHERE SYBZ00='1' AND SFYX00='Y';
  IF COUNTER<>1 THEN
    RAISE E_ZHLBCSCW;--帐户类别参数错误
  ELSE
    SELECT JFLBID INTO VJFLBID FROM BM_BRJFLB WHERE SYBZ00='1' AND SFYX00='Y';
  END IF;
  --按病人逐个生成执行单
  SP_BQ_YJYZZX_YGBR00(PCZY000,PZYID00,PBQH000,PZXRQ00,PBCTQID,VCZYXM0,VJFLBID,v_xzqf00,v_commit,PBRQFXX,v_xzqf00,PHZYZBZ,PYSTJDZ);
  DELETE FROM BQ_YJYZLS WHERE ID0000=PBCTQID ;
  IF (v_commit='Y') or ((PBRQFXX is null) and (Pcommit = 'Y')) THEN
    COMMIT;
    END IF;
  EXCEPTION
    WHEN E_MYTQSJ      THEN
      RAISE_APPLICATION_ERROR(-20001, substrb('没有执行时间，不能执行非药医嘱！*'||VPARAMS,1,180));
    WHEN E_QYSJDYRYSJ  THEN
      RAISE_APPLICATION_ERROR(-20001, substrb('医技启用时间小于病人入院时间！*'||VPARAMS,1,180));
    WHEN E_MYCFJL      THEN
      RAISE_APPLICATION_ERROR(-20002, substrb('数据不完整！*部分医嘱没有拆分记录'||VPARAMS,1,180));
    WHEN E_YZSJFGCSCW  THEN
      RAISE_APPLICATION_ERROR(-20003, substrb('系统参数:医嘱时间分割点错误！*'||VPARAMS,1,180));
    WHEN E_MYYZZX      THEN           --没有医嘱可以提取;
      RAISE_APPLICATION_ERROR(-20004, substrb('没有非药医嘱可以执行！*'||VPARAMS,1,180));
    WHEN E_HLXMCW      THEN    --护理项目错误
      RAISE_APPLICATION_ERROR(-20005, substrb('病人有多个护理级别，请纠正后后再执行！*'||VPARAMS,1,180));
    WHEN E_YZZNDTTQ   THEN
      RAISE_APPLICATION_ERROR(-20080, substrb('医嘱只能提前执行'||TO_CHAR(VWHTQTS)||'天！*'||VPARAMS,1,180));
    WHEN E_ZHLBCSCW    THEN
      RAISE_APPLICATION_ERROR(-20006,substrb('帐户类别参数错误，终止执行非药医嘱！*'||VPARAMS,1,180));
    WHEN E_CFQWT      THEN
      RAISE_APPLICATION_ERROR(-20007, substrb('因触发器TR_BQ_YJYZ00_BUPD失效造成的数据不完整！*'||VPARAMS,1,180));
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20010, substrb('数据没有找到，终止执行非药医嘱！*'||VPARAMS,1,180));
    WHEN OTHERS        THEN
      RAISE_APPLICATION_ERROR(-20020, substrb(NVL(SQLERRM, '原因不明出错')||'！*'||VPARAMS,1,180));
--      RAISE_APPLICATION_ERROR(-20020, NVL(SUBSTRB(SQLERRM,180),'原因不明出错')||'！*'||VPARAMS);
    ROLLBACK;
END SP_BQ_YJYZZX_XZ;