-- Start of DDL Script for Procedure SD_HOSPITAL.SP_YK_YPZLPD_LN -- Generated 9-八月-2012 19:08:51 from SD_HOSPITAL@NJXYY CREATE OR REPLACE PROCEDURE sp_yk_ypzlpd_ln( p_YPPDPC IN NUMBER, --输入: 药品盘点批次 P_QRRBH0 IN NUMBER --输入: 确认入编号 )AS V_COUNTER NUMBER; -- 记数器 V_YKBMBH YK_YPPD00.YKBMBH%TYPE; V_RKDH00 YK_YPRKD0.RKDH00%TYPE; V_YPQLDH YK_YPQLD0.YPQLDH%TYPE; V_SFQR00 YK_YPPD00.SFQR00%TYPE; V_YPMC00 BM_YD0000.YPMC00%TYPE; V_K2J000 BM_YD0000.K2J000%TYPE; V_JLDW00 BM_YD0000.JLDW00%TYPE; V_GJDJ00 BM_YD0000.GJJ000%TYPE; V_KCDW00 BM_YD0000.KCDW00%TYPE; V_SFSH00 XT_XTCS00.VALUE0%TYPE; V_PFJ000 BM_YD0000.PFJ000%TYPE; V_KCJLSL YK_YPPDMX.zmsl00%type; V_YKJLCE YK_YPPDMX.zmsl00%type; V_YKJLSY YK_YPPDMX.zmsl00%type; V_LYJLSL YK_YPPDMX.zmsl00%type; V_YKSLXG YK_YPPDMX.YKSL00%type; v_ZYKSL0 YK_YPPDMX.YKSL00%type; v_CSSL00 YK_YPPDMX.YKSL00%type; V_SFSYJL varchar(2); V_DQRQ00 CHAR(8); V_DQSJ00 CHAR(8); V_SFZBYP CHAR(1); bz0000 number; E_NoEdit EXCEPTION; E_NoEnouth EXCEPTION; E_NoDatafound EXCEPTION; -- MODIFICATION HISTORY -- Person Date Comments -- chenyw 2009.07.27 创建一个盘亏为零（主要处理除不尽，用计量辅助盘点）的一个盘点过程 -- --------- ---------- ------------------------------------------- BEGIN -----盘点结果的处理：盘盈：进行入库操作，入库类型为'盘盈'，入库批次同最旧的库存>0的入库批次。 ----- 盘亏：进行出库操作，出库类型为'盘亏'，按先进先出的原则出库。 ---------------------------------1.盘点结果处理----------------------------------------- --变量赋值 SELECT TO_CHAR(SYSDATE,'YYYYMMDD'),TO_CHAR(SYSDATE,'HH24:MI:SS') INTO V_DQRQ00,V_DQSJ00 FROM DUAL; SELECT YKBMBH,SFQR00 INTO V_YKBMBH,V_SFQR00 FROM YK_YPPD00 WHERE YPPDPC=P_YPPDPC; --有效性判断 IF V_SFQR00='Y' THEN RAISE E_NoEdit; END IF; --如果全部未输入药品实际数量则删除本次盘点信息 SELECT COUNT(YPPDPC) INTO V_COUNTER FROM YK_YPPDMX WHERE YPPDPC=P_YPPDPC AND SJSL00>=0; IF V_COUNTER=0 THEN UPDATE BM_BMBM00 SET FYBZ00='0' WHERE BMBH00=(SELECT YKBMBH FROM YK_YPPD00 WHERE YPPDPC=P_YPPDPC); DELETE YK_YPPD00 WHERE YPPDPC=P_YPPDPC; DELETE YK_YPPDMX WHERE YPPDPC=P_YPPDPC; COMMIT; RAISE E_NoDatafound; END IF; V_RKDH00:=SF_YK_GETLSH('YKRKDH'); --取系统参数：入出库药会是否审核 BEGIN SELECT VALUE0 INTO V_SFSH00 FROM XT_XTCS00 WHERE NAME00='YK_RCKYKSFSH'; EXCEPTION WHEN OTHERS THEN V_SFSH00:='N'; END; -----------------------1).盘盈入库-------------------------------------- SELECT COUNT(YPPDPC) INTO V_COUNTER FROM YK_YPPDMX WHERE YPPDPC=P_YPPDPC AND YKSL00>0 AND SJSL00>=0; IF V_COUNTER>0 THEN --有盘盈数据 --入库操作 ----插入药品入库单主表一条记录（待确认） INSERT INTO YK_YPRKD0( RKDH00, --入库单号(先使用六位,不足六位前面补'0') YKBMBH, --药库部门编号 CZYBH0, --操作员编码 QRRBH0, --确认入编号 GHDWNM, --供货单位编码 -->BM_YPGHDW RKLXBH, --入库类型编码 -->BM_YKRKLX JHRQ00, --进货日期 JHSJ00) --进货时间,格式00:00:00 VALUES( V_RKDH00, V_YKBMBH, P_QRRBH0, P_QRRBH0, 99999, '08', --盘盈 V_DQRQ00, V_DQSJ00); ---对盘盈数据明细进行遍历，进行逐条记录详细入库操作 DECLARE CURSOR C_YPPDMX_PY IS --定义药品盘盈数据集游标 SELECT * FROM YK_YPPDMX WHERE YPPDPC=P_YPPDPC AND YKSL00>0 AND SJSL00>=0; V_YPPDMX_PY C_YPPDMX_PY%ROWTYPE; BEGIN ---AAA OPEN C_YPPDMX_PY; --打开游标 FETCH C_YPPDMX_PY INTO V_YPPDMX_PY; WHILE C_YPPDMX_PY%FOUND LOOP --对盘盈数据明细进行遍历，进行逐条记录详细出库操作 DECLARE CURSOR C_YPRKMX_PY IS --定义药品入库批次游标，按药品效期批号排序(先进先出) SELECT X.* FROM YK_YPRKMX X,YK_YPRKD0 R WHERE X.YPNM00=V_YPPDMX_PY.YPNM00 AND ROUND(X.LSDJ00,2)=ROUND(V_YPPDMX_PY.PDDJ00/V_YPPDMX_PY.ZHL000*X.ZHL000,2) AND UPPER(X.CXBZ00)='Z' AND R.RKDH00=X.RKDH00 AND R.RKZT00 NOT IN('0','4') ORDER BY YPSXRQ,SCPH00; V_YPRKMX_PY C_YPRKMX_PY%ROWTYPE; BEGIN ---BBB OPEN C_YPRKMX_PY; FETCH C_YPRKMX_PY INTO V_YPRKMX_PY; --取第一条记录 --判断目前药品的中标状态 JETHUA 2002.11.26 SELECT SFZBYP,K2J000,JLDW00,KCDW00,GJJ000,PFJ000 INTO V_SFZBYP,V_K2J000,V_JLDW00,V_KCDW00,V_GJDJ00,V_PFJ000 FROM BM_YD0000 WHERE YPNM00=V_YPPDMX_PY.YPNM00; IF V_YPRKMX_PY.GJDJ00>0 THEN V_GJDJ00:=ROUND(V_YPRKMX_PY.GJDJ00/V_YPRKMX_PY.ZHL000*V_K2J000,2); END IF; ---------盘盈明细入库(批号等相关信息同游标取出的第一条记录) INSERT INTO YK_YPRKMX( YPRKPC, --药品入库批次-->SQ_YK_YPRKMX_YPRKPC RKDH00, --入库单号-->YK_YPRKD0 FPH000, --发票号 FPFLH0, --发票附联号 YPNM00, --药品内码-->BM_YD0000 YPMC00, --药品名称 YPGG00, --规格 RKDW00, --入库单位 ZHL000, --转换率(入库单位/剂量单位) RKSL00, --入库数量(入库单位) FPGJDJ, --发票购进单价(入库单位) KL0000, --扣率 check (KL0000>0 and KL0000<=1) GJDJ00, --购进单价=发票购进单价*扣率 LSDJ00, --零售单价(入库单位) XXLSDJ, --零售单价(入库单位) PFDJ00, --批发单价(入库单位) GJJE00, --购进金额=购进单价*入库数量 --(可手工调整与发票一致） PCKCSL, --批次库存数量(剂量单位) SCPH00, --生产批号 YPSXRQ, --失效日期 SCCJBH, --厂家编码 -->BM_YPSCCJ SCCJZW, --厂家名称(中文) CFWZBH, --存放位置编码 -->BM_YPCFWZ CFWZMC, --存放位置名称 BZ0000, --备注 SFZBYP) --是否中标 JETHUA 2002.11.26 VALUES( SQ_YK_YPRKMX_YPRKPC.NEXTVAL, V_RKDH00, V_YPRKMX_PY.FPH000, V_YPRKMX_PY.FPFLH0, V_YPPDMX_PY.YPNM00, V_YPPDMX_PY.YPMC00, V_YPPDMX_PY.YPGG00, V_KCDW00, V_K2J000, ROUND(V_YPPDMX_PY.YKSL00*V_YPPDMX_PY.ZHL000/V_K2J000,3), V_GJDJ00, 1, ROUND(V_YPRKMX_PY.GJDJ00/V_YPRKMX_PY.ZHL000*V_K2J000,2), V_YPPDMX_PY.PDDJ00, V_YPPDMX_PY.PDDJ00, V_PFJ000, ROUND(V_YPPDMX_PY.YKSL00*V_YPPDMX_PY.ZHL000/V_K2J000*V_GJDJ00,2), ROUND(V_YPPDMX_PY.YKSL00*V_YPPDMX_PY.ZHL000,3), V_YPRKMX_PY.SCPH00, V_YPRKMX_PY.YPSXRQ, V_YPRKMX_PY.SCCJBH, V_YPRKMX_PY.SCCJZW, V_YPRKMX_PY.CFWZBH, V_YPRKMX_PY.CFWZMC, '盘盈', V_SFZBYP); CLOSE C_YPRKMX_PY; END; ---BBB FETCH C_YPPDMX_PY INTO V_YPPDMX_PY; END LOOP; ---FOR WHILE ... LOOP OUTER CLOSE C_YPPDMX_PY; END; ---AAA --入库确认(库存的更新与记账由触发器完成) IF TRIM(V_SFSH00)='Y' THEN UPDATE YK_YPRKD0 SET RKZT00='3',QRRBH0=P_QRRBH0, QRRQ00=V_DQRQ00,QRSJ00=V_DQSJ00 WHERE RKDH00=V_RKDH00; ELSE UPDATE YK_YPRKD0 SET RKZT00='1',QRRBH0=P_QRRBH0, QRRQ00=V_DQRQ00,QRSJ00=V_DQSJ00 WHERE RKDH00=V_RKDH00; END IF; END IF; ----------------------------2)盘亏出库----------------------------------------- SELECT COUNT(YPPDPC) INTO V_COUNTER FROM YK_YPPDMX WHERE YPPDPC=P_YPPDPC AND YKSL00<0 AND SJSL00>0; IF V_COUNTER>0 THEN --有盘亏数据,按先进先出的原则出库。通过定义两个游标循环来实现 V_YPQLDH:=SF_YK_GETLSH('YKYPQLDH'); --向药库药品请领单表YK_YPQLD0 插入一条记录 IF TRIM(V_SFSH00)='Y' THEN INSERT INTO YK_YPQLD0( YPQLDH, --药品请领单号-->SQ_YK_YPQLD0_YPQLDH YKBMBH, --药库部门编号 CKQXBH, --出库去向编号:出库去向-->VW_YK_YPCKFX CKLXBH, --出库类型编码 -->BM_YKCKLX QLRQ00, --请领日期 QLSJ00, --请领时间 CZYBH0, --操作员编码 QRRBH0, --确认入编号 QLZT00, --请领状态 --'0'请领状态 --'1'药库出库 --'2'领用部门入库 QRRQ00, --确认日期 QRSJ00, --确认时间,格式00:00:00 SFSH00) VALUES( V_YPQLDH, V_YKBMBH, 99999, '07', --盘亏 V_DQRQ00, V_DQSJ00, P_QRRBH0, P_QRRBH0, '0', V_DQRQ00, V_DQSJ00, 'N'); ELSE INSERT INTO YK_YPQLD0( YPQLDH, --药品请领单号-->SQ_YK_YPQLD0_YPQLDH YKBMBH, --药库部门编号 CKQXBH, --出库去向编号:出库去向-->VW_YK_YPCKFX CKLXBH, --出库类型编码 -->BM_YKCKLX QLRQ00, --请领日期 QLSJ00, --请领时间 CZYBH0, --操作员编码 QRRBH0, --确认入编号 QLZT00, --请领状态 --'0'请领状态 --'1'药库出库 --'2'领用部门入库 QRRQ00, --确认日期 QRSJ00, --确认时间,格式00:00:00 SFSH00) VALUES( V_YPQLDH, V_YKBMBH, 99999, '07', --盘亏 V_DQRQ00, V_DQSJ00, P_QRRBH0, P_QRRBH0, '0', V_DQRQ00, V_DQSJ00, 'Y'); END IF; --生成请领明细记录 INSERT INTO YK_YPQLMX( YPQLPC, --药品请领批次-->SQ_YK_YPQLMX_YPQLPC YPQLDH, --请领单号-->YK_YPQLD0 YPNM00, --药品内码 YPMC00, --药品名称 YPGG00, --规格 QLDW00, --请领单位 GJDJ00, --购进单价(请领单位)(最近一批) QLDJ00, --请领单价(请领单位) PFDJ00, --批发单位 ZHL000, --转换率(请领单位/剂量单位) QLSL00, --请领数量(请领单位) SFSL00, --实发数量(请领单位) BZ0000) --备注,如果为报损出库记录报损原因 SELECT SQ_YK_YPQLMX_YPQLPC.NEXTVAL, V_YPQLDH, b.YPNM00, b.YPMC00, b.YPGG00, b.JLDW00, b.GJJ000, 1, b.PFJ000, 1, 0, 0, '盘亏' FROM (select distinct ypnm00 from yk_yppdmx where yppdpc=P_yppdpc and YKSL00<0 AND SJSL00>=0) a, BM_YD0000 b WHERE a.YPNM00=b.YPNM00; update yk_ypqlmx a set (qldj00,zhl000)=(select pddj00,zhl000 from yk_yppdmx where yppdpc=P_YPPDPC and yksl00<0 and sjsl00>=0 and ypnm00=a.ypnm00 and rownum=1) where YPQLDH=V_YPQLDH; update yk_ypqlmx a set (qlsl00,sfsl00)=(select -sum(yksl00),-sum(yksl00) from yk_yppdmx where yppdpc=P_YPPDPC and yksl00<0 and sjsl00>=0 and ypnm00=a.ypnm00) where YPQLDH=V_YPQLDH; ---对盘亏数据明细进行遍历，进行逐条记录详细出库操作 DECLARE CURSOR C_YPPDMX IS --定义药品盘亏数据集游标 SELECT * FROM YK_YPPDMX WHERE YPPDPC=P_YPPDPC AND YKSL00<0 AND SJSL00>=0; V_YPPDMX C_YPPDMX%ROWTYPE; BEGIN ---AAA OPEN C_YPPDMX; --打开游标 FETCH C_YPPDMX INTO V_YPPDMX; WHILE C_YPPDMX%FOUND LOOP --对盘亏数据明细进行遍历，进行逐条记录详细出库操作 DECLARE CURSOR C_YPRKMX IS --定义药品入库批次游标，按药品效期批号排序(先进先出) SELECT X.* FROM YK_YPRKMX X,YK_YPRKD0 R WHERE X.YPNM00=V_YPPDMX.YPNM00 AND ROUND(X.XXLSDJ,2)=ROUND(V_YPPDMX.PDDJ00*X.ZHL000/V_YPPDMX.ZHL000,2) AND X.PCKCSL>0 AND UPPER(X.CXBZ00)='Z' AND R.RKDH00=X.RKDH00 AND R.RKZT00 NOT IN('0','4') AND R.YKBMBH=V_YKBMBH ORDER BY YPSXRQ,SCPH00; V_YPRKMX C_YPRKMX%ROWTYPE; V_CKSL NUMBER; V_LYSL NUMBER; BEGIN ---BBB OPEN C_YPRKMX; FETCH C_YPRKMX INTO V_YPRKMX; V_CKSL:=-V_YPPDMX.YKSL00; --药品出库数量(盘点单位) SELECT SFZBYP,K2J000,JLDW00,PFJ000 INTO V_SFZBYP,V_K2J000,V_JLDW00,V_PFJ000 FROM BM_YD0000 WHERE YPNM00=V_YPPDMX.YPNM00; WHILE (C_YPRKMX%FOUND) AND (V_CKSL>0) LOOP ---判断当前批次库存是否充足 IF ROUND(V_YPRKMX.PCKCSL/V_YPPDMX.ZHL000,3)>=V_CKSL THEN --如果当前批次库存是否充足，则按出库数量出库 V_LYSL:=V_CKSL; ELSE -- 如果当前批次库存不足，则按当前批次库存数量出库,即该批全部出库. V_LYSL:=ROUND(V_YPRKMX.PCKCSL/V_YPPDMX.ZHL000,3); END IF; -----------向药库出库明细表 YK_YPCKMX插入出库批次-------------- ---------触发器TR_YK_YPCKMX_AFINT将修改库存与记账------------- INSERT INTO YK_YPCKMX( YPCKPC, --药品出库批次-->SQ_YK_YPCKMX_YPCKPC YPQLDH, --请领单号-->YK_YPQLD0 YPRKPC, --药品入库批次--> YK_YPRKMX YPNM00, --药品内码 YPMC00, --药品名称 YPGG00, --规格 CKSL00, --出库数量(出库单位) CKDW00, --出库单位 ZHL000, --转换率(出库单位/剂量单位) GJDJ00, --购进单价(出库单位) LSDJ00, --零售单价(出库单位) PFDJ00, --批发单价(出库单位) SFZBYP, --是否中标药品 BZ0000) --备注 VALUES( SQ_YK_YPCKMX_YPCKPC.NEXTVAL, V_YPQLDH, V_YPRKMX.YPRKPC, V_YPRKMX.YPNM00, V_YPRKMX.YPMC00, V_YPRKMX.YPGG00, V_LYSL, V_YPPDMX.PDDW00, V_YPPDMX.ZHL000, ROUND(V_YPRKMX.GJDJ00/V_YPRKMX.ZHL000*V_YPPDMX.ZHL000,4), V_YPPDMX.PDDJ00, ROUND(V_YPRKMX.PFDJ00/V_YPRKMX.ZHL000*V_YPPDMX.ZHL000,4), V_SFZBYP, '盘亏'); V_CKSL:=V_CKSL-V_LYSL; FETCH C_YPRKMX INTO V_YPRKMX; END LOOP; ---FOR WHILE ... LOOP INNER CLOSE C_YPRKMX; --出库未出完 IF V_CKSL>0 THEN V_YPMC00:=V_YPPDMX.YPMC00; bz0000:=1; CLOSE C_YPPDMX; RAISE E_NoEnouth; END IF; END; ---BBB FETCH C_YPPDMX INTO V_YPPDMX; END LOOP; ---FOR WHILE ... LOOP OUTER CLOSE C_YPPDMX; END; ---AAA END IF; -- UPDATE YK_YPQLD0 SET QLZT00='1' WHERE YPQLDH=V_YPQLDH; ---------------------------1判断实际为零的情况，库存是不是为零------------------- SELECT COUNT(YPPDPC) INTO V_COUNTER FROM YK_YPPDMX WHERE YPPDPC=P_YPPDPC AND SJSL00=0 and ZMJLSL-YKSL00*ZHL000<>0; IF V_COUNTER>0 THEN --有盘亏数据,按先进先出的原则出库。通过定义两个游标循环来实现 V_YPQLDH:=SF_YK_GETLSH('YKYPQLDH'); --向药库药品请领单表YK_YPQLD0 插入一条记录 IF TRIM(V_SFSH00)='Y' THEN INSERT INTO YK_YPQLD0( YPQLDH, --药品请领单号-->SQ_YK_YPQLD0_YPQLDH YKBMBH, --药库部门编号 CKQXBH, --出库去向编号:出库去向-->VW_YK_YPCKFX CKLXBH, --出库类型编码 -->BM_YKCKLX QLRQ00, --请领日期 QLSJ00, --请领时间 CZYBH0, --操作员编码 QRRBH0, --确认入编号 QLZT00, --请领状态 --'0'请领状态 --'1'药库出库 --'2'领用部门入库 QRRQ00, --确认日期 QRSJ00, --确认时间,格式00:00:00 SFSH00) VALUES( V_YPQLDH, V_YKBMBH, 99999, '07', --盘亏 V_DQRQ00, V_DQSJ00, P_QRRBH0, P_QRRBH0, '0', V_DQRQ00, V_DQSJ00, 'N'); ELSE INSERT INTO YK_YPQLD0( YPQLDH, --药品请领单号-->SQ_YK_YPQLD0_YPQLDH YKBMBH, --药库部门编号 CKQXBH, --出库去向编号:出库去向-->VW_YK_YPCKFX CKLXBH, --出库类型编码 -->BM_YKCKLX QLRQ00, --请领日期 QLSJ00, --请领时间 CZYBH0, --操作员编码 QRRBH0, --确认入编号 QLZT00, --请领状态 --'0'请领状态 --'1'药库出库 --'2'领用部门入库 QRRQ00, --确认日期 QRSJ00, --确认时间,格式00:00:00 SFSH00) VALUES( V_YPQLDH, V_YKBMBH, 99999, '07', --盘亏 V_DQRQ00, V_DQSJ00, P_QRRBH0, P_QRRBH0, '0', V_DQRQ00, V_DQSJ00, 'Y'); END IF; --生成请领明细记录 INSERT INTO YK_YPQLMX( YPQLPC, --药品请领批次-->SQ_YK_YPQLMX_YPQLPC YPQLDH, --请领单号-->YK_YPQLD0 YPNM00, --药品内码 YPMC00, --药品名称 YPGG00, --规格 QLDW00, --请领单位 GJDJ00, --购进单价(请领单位)(最近一批) QLDJ00, --请领单价(请领单位) PFDJ00, --批发单位 ZHL000, --转换率(请领单位/剂量单位) QLSL00, --请领数量(请领单位) SFSL00, --实发数量(请领单位) BZ0000) --备注,如果为报损出库记录报损原因 SELECT SQ_YK_YPQLMX_YPQLPC.NEXTVAL, V_YPQLDH, b.YPNM00, b.YPMC00, b.YPGG00, b.JLDW00, b.GJJ000, 1, b.PFJ000, 1, 0, 0, '盘亏' FROM (select distinct ypnm00 from yk_yppdmx where yppdpc=P_yppdpc AND SJSL00=0 and ZMJLSL-YKSL00*ZHL000<>0) a, BM_YD0000 b WHERE a.YPNM00=b.YPNM00; update yk_ypqlmx a set (qldj00,zhl000)=(select pddj00,ZHL000 from yk_yppdmx where yppdpc=P_YPPDPC AND SJSL00=0 and ZMJLSL-YKSL00*ZHL000<>0 and ypnm00=a.ypnm00 and rownum=1) where YPQLDH=V_YPQLDH; update yk_ypqlmx a set (qlsl00,sfsl00)=(select -sum(YKSL00),-sum(YKSL00) from yk_yppdmx where yppdpc=P_YPPDPC AND SJSL00=0 and ZMJLSL-YKSL00*ZHL000<>0 and ypnm00=a.ypnm00) where YPQLDH=V_YPQLDH; ---对盘亏数据明细进行遍历，进行逐条记录详细出库操作 DECLARE CURSOR C_YPPDMX IS --定义药品盘亏数据集游标 SELECT * FROM YK_YPPDMX WHERE YPPDPC=P_YPPDPC AND SJSL00=0 and ZMJLSL+YKSL00*ZHL000<>0; V_YPPDMX C_YPPDMX%ROWTYPE; BEGIN ---AAA OPEN C_YPPDMX; --打开游标 FETCH C_YPPDMX INTO V_YPPDMX; WHILE C_YPPDMX%FOUND LOOP --对盘亏数据明细进行遍历，进行逐条记录详细出库操作 V_SFSYJL:='N'; v_CSSL00:=0; DECLARE CURSOR C_YPRKMX IS --定义药品入库批次游标，按药品效期批号排序(先进先出) SELECT X.* FROM YK_YPRKMX X,YK_YPRKD0 R WHERE X.YPNM00=V_YPPDMX.YPNM00 AND ROUND(X.XXLSDJ,2)=ROUND(V_YPPDMX.PDDJ00*X.ZHL000/V_YPPDMX.ZHL000,2) AND X.PCKCSL>0 AND UPPER(X.CXBZ00)='Z' AND R.RKDH00=X.RKDH00 AND R.RKZT00 NOT IN('0','4') AND R.YKBMBH=V_YKBMBH ORDER BY YPSXRQ,SCPH00; V_YPRKMX C_YPRKMX%ROWTYPE; V_CKSL NUMBER; V_LYSL NUMBER; BEGIN ---BBB OPEN C_YPRKMX; FETCH C_YPRKMX INTO V_YPRKMX; V_YKJLCE:=V_YPPDMX.ZMJLSL+V_YPPDMX.YKSL00*V_YPPDMX.ZHL000; --药品出库数量(盘点单位) V_YKJLSY:=V_YPPDMX.ZMJLSL; v_ZYKSL0:=-V_YPPDMX.YKSL00; V_CKSL:=-V_YPPDMX.YKSL00; -- raise_application_error(-20010,'1111111='||V_CKSL); IF V_YKJLCE<0 THEN V_YKSLXG:=V_YPPDMX.YKSL00+0.001; V_CKSL:=-V_YKSLXG; v_ZYKSL0:=-V_YKSLXG; update YK_YPPDMX set YKSL00=V_YKSLXG where yppdpc=P_YPPDPC AND SJSL00=0 and ZMJLSL-YKSL00*ZHL000<>0 and ypnm00=V_YPPDMX.YPNM00; update yk_ypqlmx set qlsl00=qlsl00-0.001,sfsl00=sfsl00-0.001 where YPQLDH=V_YPQLDH and ypnm00=V_YPPDMX.YPNM00; END IF; SELECT SFZBYP,K2J000,JLDW00,PFJ000 INTO V_SFZBYP,V_K2J000,V_JLDW00,V_PFJ000 FROM BM_YD0000 WHERE YPNM00=V_YPPDMX.YPNM00; WHILE (C_YPRKMX%FOUND) AND ((V_CKSL>0) or V_YKJLSY>0) LOOP ---判断当前批次库存是否充足 --- raise_application_error(-20010,'2222='||V_CKSL); IF ROUND(V_YPRKMX.PCKCSL/V_YPPDMX.ZHL000,3)>=V_CKSL AND V_YPRKMX.PCKCSL>=V_YKJLSY THEN --如果当前批次库存是否充足，则按出库数量出库 V_LYSL:=V_CKSL; V_LYJLSL:=V_YKJLSY-V_LYSL*V_YPPDMX.ZHL000; ELSIF ROUND(V_YPRKMX.PCKCSL/V_YPPDMX.ZHL000,3)>=V_CKSL AND V_YPRKMX.PCKCSL0 THEN V_YPMC00:=V_YPPDMX.YPMC00; bz0000:=V_CKSL; CLOSE C_YPPDMX; RAISE E_NoEnouth; END IF; END ; ---BBB FETCH C_YPPDMX INTO V_YPPDMX; END LOOP; ---FOR WHILE ... LOOP OUTER CLOSE C_YPPDMX; END ; ---AAA END IF; UPDATE YK_YPQLD0 SET QLZT00='1' WHERE YPQLDH=V_YPQLDH; -----------------------------2.更新盘点表状态------------------------------------- UPDATE YK_YPPD00 SET SFQR00='Y',QRRBH0=P_QRRBH0, QRRQ00=V_DQRQ00,QRSJ00=V_DQSJ00 WHERE YPPDPC=P_YPPDPC; ------------------清除空的实际盘点数量--------------------- DELETE YK_YPPDMX WHERE YPPDPC=P_YPPDPC AND SJSL00 IS NULL; ----------------------------3.更新药库盘点状态---------------------------------------- UPDATE BM_BMBM00 SET FYBZ00='0' WHERE BMBH00=(SELECT YKBMBH FROM YK_YPPD00 WHERE YPPDPC=P_YPPDPC); COMMIT; EXCEPTION WHEN E_NoDatafound THEN RAISE_APPLICATION_ERROR(-20001, '该次盘点未输入任何品种的实际数量，将被取消盘点过程!*返回信息:'|| '调用过程: SP_YK_YPZLPD('||TO_CHAR(p_YPPDPC)||','|| TO_CHAR(P_QRRBH0)||') ; '||SQLERRM); WHEN E_NoEdit THEN RAISE_APPLICATION_ERROR(-20002, '该次盘点已经被确认过了,不能再确认!*返回信息:'|| '调用过程: SP_YK_YPZLPD('||TO_CHAR(p_YPPDPC)||','|| TO_CHAR(P_QRRBH0)||') ; '||SQLERRM); WHEN E_NoEnouth THEN RAISE_APPLICATION_ERROR(-20002, '药品['||V_YPMC00||']无足够库存出库!*返回信息:='||bz0000|| '调用过程: SP_YK_YPZLPD('||TO_CHAR(p_YPPDPC)||','|| TO_CHAR(P_QRRBH0)||') ; '||SQLERRM); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20003, '盘点确认时发生意外错误!*返回信息:'|| '调用过程: SP_YK_YPZLPD('||TO_CHAR(p_YPPDPC)||','|| TO_CHAR(P_QRRBH0)||') ; '||SQLERRM); ROLLBACK; END; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_YK_YPZLPD_LN