CREATE OR REPLACE PROCEDURE SP_BQ_PSJGCL --皮试处理
( PYZMXID  NUMBER, --皮试医嘱明细号
  PCZY000  NUMBER, --操作员
  PBQH000  NUMBER,
  PPSJG00  CHAR    --皮试结果'+','-'
)
AS
  YPYZ00         BQ_YPYZ00%ROWTYPE;
  VCZYXM0        BM_YGBM00.ZWXM00%TYPE;
  VPSTGBZ        BQ_YPYZMX.PSTGBZ%TYPE;
  VYPNM00        BQ_YPYZMX.YPNM00%TYPE;
  VPARAMS               VARCHAR2(255);  --参数
  COUNTER        NUMBER;
  VSYSDATE       DATE;
  ls_yyid00      xt_yyxx00.yyid00%type;
-- MODIFICATION HISTORY      
-- Person      Date         Comments
-- ZHR         2008.05.22   皮试结果同步写入YZNR00字段
BEGIN
  VSYSDATE:=SYSDATE;
  VPARAMS:='SP_BQ_PSJGCL('||TO_CHAR(PYZMXID)||','||TO_CHAR(PCZY000)||','||TO_CHAR(PBQH000)||','||PPSJG00||')';
  SELECT YZID00,PSTGBZ,YPNM00 INTO YPYZ00.YZID00,VPSTGBZ,VYPNM00 FROM BQ_YPYZMX WHERE YZMXID=PYZMXID;
  SELECT COUNT(*) INTO COUNTER FROM BM_PSJGFZ WHERE YPNM00=VYPNM00 AND ROWNUM=1;
  SELECT * INTO YPYZ00 FROM BQ_YPYZ00 WHERE YZID00=YPYZ00.YZID00;
  SELECT ZWXM00 INTO VCZYXM0 FROM BM_YGBM00 WHERE YGBH00=PCZY000;
  select yyid00 into ls_yyid00 from xt_yyxx00 ;  
  IF VPSTGBZ IS NULL and ls_yyid00<>'229112' THEN--皮试未执行,执行皮试  ,龙岩人民医院皮试特殊处理，皮试费用已通过用法带出
    SP_BQ_PSYZCL(PYZMXID,PCZY000,PBQH000);
  END IF;
  UPDATE BQ_YPYZMX SET PSTGBZ=PPSJG00,PSBGR0=PCZY000 WHERE YZMXID=PYZMXID;
  --关联的皮试医嘱要更改阴阳性
  UPDATE BQ_YPYZMX M SET YPMC00=DECODE(SUBSTRB(YPMC00,LENGTHB(RTRIM(YPMC00))-1,1),PPSJG00,YPMC00,TRIM(YPMC00)||'('||TRIM(PPSJG00)||')'),
                         YZNR00='('||TRIM(PPSJG00)||')'||decode(substrb(YZNR00,1,3),'(-)',substrb(YZNR00,4,100),'(+)',substrb(YZNR00,4,100),YZNR00)
  WHERE M.YZID00 in  (SELECT Z.YZID00 FROM BQ_YPYZ00 Z
                WHERE Z.ZYID00=YPYZ00.ZYID00 AND
                      Z.YZZT00 IN('2','1') AND Z.YZID00<>YPYZ00.YZID00  ) 
        and M.YPNM00 IN (SELECT XGYPNM FROM VW_BQ_PSYPDY WHERE PSYPNM=VYPNM00);
  --WHERE EXISTS (SELECT 1 FROM BQ_YPYZ00 Z
  --              WHERE M.YZID00=Z.YZID00 AND Z.ZYID00=YPYZ00.ZYID00 AND
  --                    Z.YZZT00 IN('2','1') AND Z.YZID00<>YPYZ00.YZID00 AND
  --                    M.YPNM00 IN (SELECT XGYPNM FROM VW_BQ_PSYPDY WHERE PSYPNM=VYPNM00)  );
  --更改相关医嘱的PSXGID字段以便少领一个单位的药
  UPDATE BQ_YPYZMX M SET PSXGID=PYZMXID
  WHERE M.YZID00 in (SELECT Z.YZID00 FROM BQ_YPYZ00 Z
                     WHERE  Z.ZYID00=YPYZ00.ZYID00 AND Z.YZZT00 IN('2','1') AND Z.YZID00<>YPYZ00.YZID00) 
        AND M.YPNM00=VYPNM00 and M.YPNM00 IN (SELECT XGYPNM FROM VW_BQ_PSYPDY WHERE PSYPNM=VYPNM00) AND ROWNUM=1;
  --WHERE EXISTS (SELECT 1 FROM BQ_YPYZ00 Z
  --              WHERE M.YZID00=Z.YZID00 AND Z.ZYID00=YPYZ00.ZYID00 AND
  --                    Z.YZZT00 IN('2','1') AND Z.YZID00<>YPYZ00.YZID00 AND
  --                    M.YPNM00 IN (SELECT XGYPNM FROM VW_BQ_PSYPDY WHERE PSYPNM=VYPNM00)
  --             ) AND M.YPNM00=VYPNM00 AND ROWNUM=1;
  IF (PPSJG00='+'AND COUNTER=0)OR (PPSJG00='-'AND COUNTER=1) THEN
  --皮试未通过，除脱敏注射用法的医嘱外，其余的医嘱取消
  --(注意：有的正式用药和皮试用药可能是不同的药品内码)
    UPDATE BQ_YPYZ00 Z SET YZZT00='4',--DECODE('1','4',YZZT00),
                           TZRXM0=VCZYXM0,
                           TZYS00=KZYS00,
                           TZRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'),
                           TZSJ00=TO_CHAR(VSYSDATE,'HH24:MI:SS')
    WHERE  ZYID00=YPYZ00.ZYID00 AND YZID00<>YPYZ00.YZID00 AND YZZT00 IN ('1') AND
           EXISTS (SELECT 1 FROM BQ_YPYZMX M WHERE Z.YZID00=M.YZID00 AND (YPNM00 IN
                   (SELECT XGYPNM FROM BM_PSYPDY WHERE PSYPNM=VYPNM00) OR YPNM00=VYPNM00));
  /*ELSIF PPSJG00='-' THEN
    --关联的医嘱要更改阴阳性
    UPDATE BQ_YPYZMX M SET YPMC00=TRIM(YPMC00)||'('||TRIM(PPSJG00)||')'
    WHERE EXISTS (SELECT 1 FROM BQ_YPYZ00 Z
                  WHERE M.YZID00=Z.YZID00 AND Z.ZYID00=YPYZ00.ZYID00 AND
                        Z.YZZT00 IN('4') AND M.YPNM00=VYPNM00 AND Z.YZID00<>YPYZ00.YZID00);
     --启用相关过敏医嘱
    UPDATE BQ_YPYZ00 Z SET YZZT00='1',--DECODE('1','4',YZZT00),
                           TZRXM0=VCZYXM0,
                           TZRQ00=TO_CHAR(VSYSDATE,'YYYYMMDD'),
                           TZSJ00=TO_CHAR(VSYSDATE,'HH24:MI:SS')
    WHERE  ZYID00=YPYZ00.ZYID00 AND YZID00<>YPYZ00.YZID00 AND YZZT00 IN ('4') AND
           EXISTS (SELECT 1 FROM BQ_YPYZMX M WHERE Z.YZID00=M.YZID00 AND YPNM00=VYPNM00);*/
  END IF;
  COMMIT;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20010, '数据没有找到！*'||VPARAMS);
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20020, substrb(NVL(SQLERRM, '原因不明出错')||'！*'||VPARAMS,1,220));
END SP_BQ_PSJGCL;