CREATE OR REPLACE PROCEDURE SP_BQ_YPYZQY--药品医嘱启用
( PYZID00  NUMBER, --医嘱号(用于单条医嘱启用)
  PZYID00  NUMBER, --病人的住院ID(用于全部药品医嘱启用)
  PCZY000  NUMBER  --操作员
)
as
  VPARAMS               VARCHAR2(255);  --参数
  VYPYZ00               BQ_YPYZ00%ROWTYPE;
  VKFSJFG               CHAR(8);  --医嘱时间分割点
  VZJSJFG               CHAR(8);  --医嘱时间分割点
  E_FZTYZ               EXCEPTION;--非使用中的医嘱
  VSYSDATE              DATE;
  VSCZXJL               varchar2(10);
  VError                varchar2(100);
  vcount number(5);
  cursor C_YPYZ00 IS select * from BQ_YPYZ00 where ZYID00=PZYID00 and YZZT00='5' and TZRQ00 is null;
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- qks         2009.11.06  tzys00,tzrq00,tzsj00,tzrxm0赋空
-- zhangwz     2012.03.19  对于医嘱的QYRQ00不进行改动,因为实际过程中这样的修改会造成和医嘱实时打印出来的数据不符 by BQ-20120217-002.
-- dsm         2015.07.29  增加参数BQ_QYDTZTYZSFSCZXJL控制是否删除启用当天暂停医嘱的当天执行记录 for BQ-20150318-001
-- dsm         2018.11.16  医生预停的医嘱(TZRQ00 is not null)，护士不能启用 for BQ-20181115-001
begin
  VSYSDATE:=SYSDATE;
  VPARAMS:='SP_BQ_YZZTQY('||TO_CHAR(PYZID00)||','||TO_CHAR(PZYID00)||','||TO_CHAR(PCZY000)||')';
  SELECT TRIM(VALUE0) INTO VKFSJFG FROM XT_XTCS00 WHERE NAME00='BQ_KFYZSJFG';
  SELECT TRIM(VALUE0) INTO VZJSJFG FROM XT_XTCS00 WHERE NAME00='BQ_ZJYZSJFG';
  select nvl(max(trim(VALUE0)),'Y') into VSCZXJL from XT_XTCS00 where Name00='BQ_QYDTZTYZSFSCZXJL' and value0='N';
  IF PYZID00>0 THEN--单条药品医嘱启用
    SELECT * INTO VYPYZ00 FROM BQ_YPYZ00 WHERE YZID00=PYZID00;
    IF VYPYZ00.YZZT00<>'5' THEN
      VError:='非暂停中的药品医嘱，不能启用！*';
      RAISE E_FZTYZ;
    END IF;
    if VYPYZ00.TZYS00 is not null or VYPYZ00.TZRQ00 is not null  THEN
      VError:='对不起，医生停止的医嘱不能启用！*';
      RAISE E_FZTYZ;
    END IF;
--    IF VYPYZ00.YZZT00='2' THEN今日停止今日启用，未使用的医嘱
      --今日停止今日启用,予以删除
      if VSCZXJL='Y' then
        DELETE FROM BQ_YZZXJL WHERE YZID00=PYZID00 AND ZT0000='3'AND QSRQ00>=TO_CHAR(VSYSDATE,'YYYYMMDD');
      end if;
      --停止间隔不少于1天,未使用的医嘱和已经使用的医嘱
     -- VError:='a@';
     -- select count(*)  into vcount from bq_yzzxjl　WHERE YZID00=PYZID00 AND ZT0000='3' AND (QSRQ00<TO_CHAR(VSYSDATE,'YYYYMMDD') or VSCZXJL='N');
     -- verror:=verror||'@'||vcount;
      select count(*)  into vcount  from bq_yzzxjl　WHERE YZID00=PYZID00 AND ZT0000='4' and jsrq00=TO_CHAR(VSYSDATE,'YYYYMMDD') and sjjsrq=TO_CHAR(VSYSDATE,'YYYYMMDD');
      if vcount=0 then
      UPDATE BQ_YZZXJL SET CZR000=PCZY000,
                           ZT0000='4',
                           JSRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'),
                           SJJSRQ=TO_CHAR(VSYSDATE,'YYYYMMDD'),
                           CZRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'),
                           CZSJ00=TO_CHAR(VSYSDATE,'HH24:MI:SS')
      WHERE YZID00=PYZID00 AND ZT0000='3' AND (QSRQ00<TO_CHAR(VSYSDATE,'YYYYMMDD') or VSCZXJL='N');
      end if;
      --VError:='a.1@';
      --还原医嘱状态
      UPDATE BQ_YPYZ00 SET YZZT00=DECODE(TQRQ00,NULL,'1',DECODE(SIGN(TO_DATE(TQRQ00,'YYYYMMDD')+1-TO_DATE(QYRQ00,'YYYYMMDD')),1,'2','1'))
      WHERE YZID00=PYZID00;
      --更改长嘱医嘱提取时间，短嘱的时间不做变更
      IF VYPYZ00.CLBZ00='0' THEN
        UPDATE BQ_YPYZ00 SET TQRQ00=DECODE(YZZT00,'1',DECODE(SIGN(TRUNC(VSYSDATE)-TO_DATE(QYRQ00,'YYYYMMDD')),1,TO_CHAR(VSYSDATE-1,'YYYYMMDD'),NULL),--未使用的医嘱
                                                  '2',DECODE(SIGN(TRUNC(VSYSDATE)-TO_DATE(TQRQ00,'YYYYMMDD')),1,TO_CHAR(VSYSDATE-1,'YYYYMMDD'),TQRQ00)),
                             TQSJ00=DECODE(YZZT00,'1',DECODE(SIGN(TRUNC(VSYSDATE)-TO_DATE(QYRQ00,'YYYYMMDD')),1,DECODE(KFBZ00,'Y',VKFSJFG,VZJSJFG),NULL),DECODE(KFBZ00,'Y',VKFSJFG,VZJSJFG)),
                             tzys00 = null,tzrq00 = null,tzsj00 = null,tzrxm0 = null
        WHERE YZID00=PYZID00;

        UPDATE BQ_YPYZMX M SET (TQRQ00,TQSJ00)=
                               (SELECT DECODE(Z.TQRQ00,NULL,NULL,GREATEST(NVL(M.TQRQ00,TO_CHAR(VSYSDATE,'YYYYMMDD')),TO_CHAR(VSYSDATE,'YYYYMMDD'))),
                                       DECODE(Z.TQRQ00,NULL,NULL,DECODE(KFBZ00,'Y',VKFSJFG,VZJSJFG))
                                FROM BQ_YPYZ00 Z
                                WHERE M.YZID00=Z.YZID00)
        WHERE YZID00=PYZID00 ;
      END IF;
      --NEW 3.17未使用过的医嘱重新核对
      IF (VYPYZ00.TQRQ00 IS NULL) AND (TO_CHAR(VSYSDATE,'YYYYMMDD')>VYPYZ00.SJQSRQ) THEN--从未使用的医嘱，重新核对
        UPDATE BQ_YPYZ00 SET --QYRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'),   by BQ-20120217-002
                             SJQSRQ=NULL,
                             SJQSSJ=NULL,
                             YZZT00='0',
                             HDR000=NULL
        WHERE  YZID00=PYZID00;
        DELETE FROM BQ_YPYZCF WHERE YZID00=PYZID00;
        SP_BQ_YPYZQT('1',PYZID00,VYPYZ00.HDR000,NULL,NULL,NULL,NULL,VYPYZ00.PXSJ00);
        --07.03.29 省二
        --UPDATE BQ_YPYZ00 SET QYRQ00=VYPYZ00.QYRQ00 WHERE YZID00=PYZID00;
      END IF;

--    END IF;
  ELSE--所有药品医嘱暂停
    --今日停止今日启用,予以删除
    if VSCZXJL='Y' then
      DELETE FROM BQ_YZZXJL Z WHERE ZT0000='3'AND QSRQ00>=TO_CHAR(VSYSDATE,'YYYYMMDD') AND
                                  EXISTS (SELECT 1 FROM BQ_YPYZ00 Y
                                          WHERE Y.YZID00=Z.YZID00 AND ZYID00=PZYID00 AND YZZT00='5' and TZRQ00 is null) ;
    end if;
    --停止间隔不少于1天,未使用的医嘱和已经使用的医嘱
    UPDATE BQ_YZZXJL Z SET CZR000=PCZY000,
                           ZT0000='4',
                           JSRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'),
                           SJJSRQ=TO_CHAR(VSYSDATE,'YYYYMMDD'),
                           CZRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'),
                           CZSJ00=TO_CHAR(VSYSDATE,'HH24:MI:SS')
    WHERE  ZT0000='3' AND  (QSRQ00<=TO_CHAR(VSYSDATE,'YYYYMMDD') or VSCZXJL='N' ) AND
           EXISTS (SELECT 1 FROM BQ_YPYZ00 Y
                   WHERE Y.YZID00=Z.YZID00 AND ZYID00=PZYID00 AND YZZT00='5' and TZRQ00 is null);
    --还原医嘱状态
    UPDATE BQ_YPYZ00 SET YZZT00=DECODE(TQRQ00,NULL,'1',DECODE(SIGN(TO_DATE(TQRQ00,'YYYYMMDD')+1-TO_DATE(QYRQ00,'YYYYMMDD')),1,'2','1'))
    WHERE  ZYID00=PZYID00 AND YZZT00='5' and TZRQ00 is null;
    FOR YPYZ00 IN C_YPYZ00 LOOP
      --更改长嘱医嘱提取时间，短嘱的时间不做变更
      IF YPYZ00.CLBZ00='0' THEN
        UPDATE BQ_YPYZ00 SET TQRQ00=DECODE(YZZT00,'1',DECODE(SIGN(TRUNC(VSYSDATE)-TO_DATE(QYRQ00,'YYYYMMDD')),1,TO_CHAR(VSYSDATE-1,'YYYYMMDD'),NULL),--未使用的医嘱
                                                  '2',DECODE(SIGN(TRUNC(VSYSDATE)-TO_DATE(TQRQ00,'YYYYMMDD')),1,TO_CHAR(VSYSDATE-1,'YYYYMMDD'),TQRQ00)),
                             TQSJ00=DECODE(YZZT00,'1',DECODE(SIGN(TRUNC(VSYSDATE)-TO_DATE(QYRQ00,'YYYYMMDD')),1,DECODE(KFBZ00,'Y',VKFSJFG,VZJSJFG),NULL),DECODE(KFBZ00,'Y',VKFSJFG,VZJSJFG)),
                             tzys00 = null,tzrq00 = null,tzsj00 = null,tzrxm0 = null
        WHERE YZID00=YPYZ00.YZID00;
        UPDATE BQ_YPYZMX M SET (TQRQ00,TQSJ00)=
                               (SELECT DECODE(Z.TQRQ00,NULL,NULL,GREATEST(NVL(M.TQRQ00,TO_CHAR(VSYSDATE,'YYYYMMDD')),TO_CHAR(VSYSDATE,'YYYYMMDD'))),
                                       DECODE(Z.TQRQ00,NULL,NULL,DECODE(KFBZ00,'Y',VKFSJFG,VZJSJFG))
                                FROM BQ_YPYZ00 Z
                                WHERE M.YZID00=Z.YZID00)
        WHERE YZID00=YPYZ00.YZID00 ;
      END IF;
      --NEW 3.17 未使用过的医嘱重新核对
      IF (YPYZ00.TQRQ00 IS NULL) AND (TO_CHAR(VSYSDATE,'YYYYMMDD')>YPYZ00.SJQSRQ) THEN--从未使用的医嘱，重新核对
        UPDATE BQ_YPYZ00 SET --QYRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'),  by BQ-20120217-002
                             SJQSRQ=NULL,
                             SJQSSJ=NULL,
                             YZZT00='0',
                             HDR000=NULL
        WHERE  YZID00=PYZID00;
        DELETE FROM BQ_YPYZCF WHERE YZID00=PYZID00;
        SP_BQ_YPYZQT('1',PYZID00,YPYZ00.HDR000,NULL,NULL,NULL,NULL,YPYZ00.PXSJ00);
        --07.03.29 省二
        --UPDATE BQ_YPYZ00 SET QYRQ00=YPYZ00.QYRQ00 WHERE YZID00=PYZID00;
      end if;
    end loop;
  end if;
  commit;
  exception
    when E_FZTYZ       then
      RAISE_APPLICATION_ERROR(-20001, VError||VPARAMS);
    when NO_DATA_FOUND then
      RAISE_APPLICATION_ERROR(-20010, '数据没有找到！*'||VPARAMS);
    when OTHERS then
      RAISE_APPLICATION_ERROR(-20020, substrb(VError||'@'||NVL(SQLERRM, '原因不明出错')||'！*'||VPARAMS,1,220));
end sp_bq_ypyzqy;