CREATE OR REPLACE 
PROCEDURE sp_yf_jspckc(
P_YFBMBH       IN NUMBER,             --输入：药房部门编号
P_YPNM00       IN NUMBER,             --输入：药品内码
P_CKSL00       IN NUMBER,             --输入：出库数量
P_SCPH00       in varchar2  default ''--输入生产批号
)AS

CURSOR C_PCKC00 IS
SELECT X.PCKCSL,X.YPRKPC
  FROM YF_YPRKMX X,YF_YPRKD0 D
 WHERE X.RKDH00=D.RKDH00
   AND PCKCSL>0
   AND YFBMBH=P_YFBMBH
   AND YPNM00=P_YPNM00
  -- and (SCPH00=P_SCPH00 or SCPH00 is null)
  and (SCPH00=P_SCPH00 or (SCPH00 is null or nvl(P_SCPH00,' ')=' ') )
 ORDER BY SCPH00,decode(X.YPSXRQ,null,'1',X.YPSXRQ);  --让失效日期为空的先发

V_SYCKSL YF_YPRKMX.PCKCSL%TYPE;    --剩余出库数量
V_PCKCSL YF_YPRKMX.PCKCSL%TYPE;    --批次库存数量
V_BCCKSL YF_YPRKMX.PCKCSL%TYPE;    --本次出库数量
V_YPRKPC YF_YPRKMX.YPRKPC%TYPE;
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- JETHUA      2003.09.25  CREATE
-- JETHUA      2003.12.31  批次库存增加为0，不UPDATE
-- huangjy     2020.12.23  如果有批次，先扣除对应批次 for YF-20201216-001
-- ---------   ----------  -------------------------------------------
BEGIN
  OPEN C_PCKC00;
  FETCH C_PCKC00 INTO V_PCKCSL,V_YPRKPC;
  
  V_SYCKSL:=P_CKSL00;
  WHILE C_PCKC00%FOUND AND V_SYCKSL>0 LOOP
    V_BCCKSL:=V_SYCKSL;
    V_SYCKSL:=V_SYCKSL-V_PCKCSL;
    --批次库存不够或恰好够出
    IF V_BCCKSL>0 THEN
      IF V_SYCKSL>=0 THEN
        UPDATE YF_YPRKMX SET PCKCSL=0
         WHERE YPRKPC=V_YPRKPC;    
      ELSE    --批次库存够出
        UPDATE YF_YPRKMX SET PCKCSL=V_PCKCSL-V_BCCKSL
         WHERE YPRKPC=V_YPRKPC;   
      END IF;   
    END IF;
    FETCH C_PCKC00 INTO V_PCKCSL,V_YPRKPC;
  END LOOP;
  CLOSE C_PCKC00;
  --commit;
EXCEPTION                 
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20213, '扣除批次库存时发生意外错误!*返回信息:'||              
            '调用过程: SP_YF_JSPCKC('||TO_CHAR(P_YFBMBH)||','||TO_CHAR(P_YPNM00)||','||TO_CHAR(P_CKSL00)||') ; '||SQLERRM);              
    ROLLBACK;              
END;
/