CREATE OR REPLACE PROCEDURE EXPIRED_DRUGS_INTERCEPT(
IS_UPDATE IN NUMBER
)
IS
 E_TOOL EXCEPTION;
 V_ERROR VARCHAR(200);
 V_SL  GSP_SPTSTZD.Sl%TYPE;
 V_JS  GSP_SPTSTZD.Js%TYPE;
 V_LSS  GSP_SPTSTZD.Lss%TYPE;
 V_DWID GSP_SPTSTZD.DWID%TYPE;
 V_HWID  KC_SPPHHW.HWID%TYPE;
 V_SPID  KC_SPPHHW.SPID%TYPE;
 V_PHID  KC_SPPHHW.PHID%TYPE;
 
BEGIN
/*  ROLLBACK;*/
  DECLARE
  CURSOR emp_cursor IS 
    SELECT B.PHID, A.SL_KC, A.JS_KC, A.LSS_KC, C.SPID, D.HWID
       INTO V_PHID, V_SL, V_JS, V_LSS, V_SPID, V_HWID
       FROM KC_SPPHHW A, JC_PHWHB B, SPKFK C, JC_HWZD D
      WHERE A.PHID = B.PHID
        AND A.SL_KC > '0'
        AND KCZT = '1'
        AND B.YXQZ < SYSDATE
        AND C.SPID = A.SPID
        AND D.HWID = A.HWID;
   emp_record emp_cursor%ROWTYPE; 
  BEGIN
    OPEN emp_cursor;
    FETCH emp_cursor INTO emp_record;
      IF IS_UPDATE = 8 THEN --停售
          UPDATE KC_SPPHHW --锁定出库
           SET SL_KCK = 0
         WHERE HWID = V_HWID
           AND SPID = V_SPID
           AND PHID = V_PHID;
      
        UPDATE KC_SPPHHW
           SET KCZT = 8
         WHERE HWID = V_HWID
           AND SPID = V_SPID
           AND PHID = V_PHID;
           
        INSERT INTO GSP_SPTSTZD --插入商品停售通知单
          (RQ,
           RY_CZY,
           DJ_SORT,
           SPID,
           PHID,
           JYQK,
           YJ_CL,
           SL,
           JS,
           LSS,
           HWID,
           DWID)
        VALUES
          (sysdate,
           'SYSTEM',
           '',
           V_SPID,
           V_PHID,
           '商品过期',
           '停售',
           V_SL,
           V_JS,
           V_LSS,
           V_HWID,
           V_DWID);
           
        INSERT INTO gsp_spffxstzd --商品恢复销售通知单
          (RQ,
           Dj_Sort,
           SPID,
           PHID,
           HWID,
           bgsbh,
           jyqk,
           sjdbh,
           yj_cl,
           zgbjz,
           ry_czy,
           sl,
           js,
           lss,
           rq_ts)
        VALUES
          (sysdate,
           '',
           V_SPID,
           V_PHID,
           V_HWID,
           'bgsbh',
           'jyqk',
           'sjdbh',
           'yj_cl',
           'zgbjz',
           'SYSTEM',
           V_SL,
           V_JS,
           V_LSS,
           SYSDATE);
       END IF;
      COMMIT;
      CLOSE emp_cursor;
    END;
  EXCEPTION
  WHEN E_TOOL THEN
    V_ERROR := SQLCODE||SUBSTR(SQLERRM,1,180);
    DBMS_OUTPUT.put_line(V_ERROR);
END EXPIRED_DRUGS_INTERCEPT;

SELECT B.PHID, A.SL_KC, A.JS_KC, A.LSS_KC, C.SPID, D.HWID
       INTO V_PHID, V_SL, V_JS, V_LSS, V_SPID, V_HWID
       FROM KC_SPPHHW A, JC_PHWHB B, SPKFK C, JC_HWZD D
      WHERE A.PHID = B.PHID
        AND A.SL_KC > '0'
        AND KCZT = '1'
        AND B.YXQZ < SYSDATE
        AND C.SPID = A.SPID
        AND D.HWID = A.HWID;