CREATE OR REPLACE PROCEDURE SP_YF_PCKCYK( P_YFBMBH IN NUMBER, --输入：药房部门编号 P_YPNM00 IN NUMBER, --输入：药品内码 P_CKSL00 IN NUMBER, --输入：出库数量 P_SFXJJZ IN NUMBER DEFAULT 0, --输入：是否现金记账，不做价格调整 P_CKLSJ0 OUT NUMBER ,--输入：出库零售价 P_CKGJJ0 OUT NUMBER, --输出购进价 P_CFLSH0 in char DEFAULT ' ', P_DJLX00 in varchar2 default '0',--单据类型 0门诊处方 1 住院处方 2住院医嘱 3借药单 P_CFID00 in number default 0, P_MZZYBZ in char default '0' )AS CURSOR C_PCKC00 IS SELECT KCLSH0,SCPH00,LSDJ00,YKKCSL,GJDJ00,YPSXRQ,YKRKPC FROM YF_PCKCMX WHERE YFBMBH=P_YFBMBH AND YPNM00=P_YPNM00 ORDER BY TO_NUMBER(KCLSH0); V_KCLSH0 YF_PCKCMX.KCLSH0%TYPE; V_KCLSH1 YF_PCKCMX.KCLSH0%TYPE; V_SCPH00 YF_PCKCMX.SCPH00%TYPE; V_LSDJ00 YF_PCKCMX.LSDJ00%TYPE; V_GJDJ00 YF_PCKCMX.GJDJ00%TYPE; V_YKKCSL YF_PCKCMX.YKKCSL%TYPE; V_SYCKSL YF_PCKCMX.SJKCSL%TYPE; --剩余出库数量 V_BCCKSL YF_PCKCMX.SJKCSL%TYPE; --本次出库数量 V_DQLSDJ YF_YPKCXX.LSDJ00%TYPE; --当前零售价 V_DQGJDJ YF_YPKCXX.GJDJ00%TYPE; --当前购进单价 V_YPMC00 BM_YD0000.YPMC00%TYPE; V_YPGG00 BM_YD0000.YPGG00%TYPE; V_KCDW00 BM_YD0000.KCDW00%TYPE; V_K2J000 BM_YD0000.K2J000%TYPE; V_GJJ000 BM_YD0000.GJJ000%TYPE; V_KCSL00 BM_YD0000.KCSL00%TYPE; V_KCJE00 BM_YD0000.KCJE00%TYPE; V_KCGJJE BM_YD0000.KCJE00%TYPE; V_DQRQ00 CHAR(8); V_DQSJ00 CHAR(8); V_SUM000 NUMBER; v_FYGZ00 XT_XTCS00.VALUE0%TYPE; --多零售价:发药规则 v_YYCS00 XT_XTCS00.VALUE0%TYPE; --用户参数 V_params varchar2(255); --参数变量 V_errmsg varchar2(255); --错误提示变量 E_custom EXCEPTION; --错误变量 V_YPSXRQ YF_PCKCMX.YPSXRQ%type; V_YKRKPC YF_PCKCMX.YKRKPC%TYPE; -- MODIFICATION HISTORY -- Person Date Comments -- JETHUA 2004.03.24 CREATE:批次库存预扣 -- JETHUA 2004.05.19 不论价格是否不同，都进行库存调整 -- JETHUA 2004.06.15 统一YF_YPMXZ0的结存单位为库存单位 -- JETHUA 2004.07.07 增加批次库存判断 -- JETHUA 2004.07.08 规范错误提示 -- JETHUA 2004.12.14 莆田不判断预扣库存 -- JETHUA 2005.08.04 取消是否高价出库参数功能， -- 首先取预扣数大于0的最小批次单价，如果预扣都为零则取最大批次单价 -- JETHUA 2005.08.09 取最小批次BUG:MIN(KCLSH0)改为MIN(TO_NUMBER(KCLSH0)) -- CSF 2008.03.25 恢复最高价出库参数功能 -- CSF 2008.08.19 当kclsh0装换成number类型时候，必须再转为char型，并且原有的kclsh0必须加trim来顾虑空格 -- CSF 2008.09.27 修改bug:char变量对比两边应同时加trim过过滤空格 -- csf 2011.12.02 批次概念由原来的LSDJ00,SCPH00调整为LSDJ00,SCPH00,GJDJ00 YF-20111212-002 -- huangjy 2020.12.14 扣库存时写入yf_mzcfmx.scph00,ypsxrq for YFPY-20201123-001 -- dsm 2024.06.05 写入YF_KCYKMX for YF-20240530-001 -- huangjy 2024.07.16 写入YF_KCYKMX增加SCPH00,YPSXRQ for YF-20240716-001 -- --------- ---------- ------------------------------------------- BEGIN V_errmsg:=''; V_params:='调用过程: SP_YF_PCKCYK('||TO_CHAR(P_YFBMBH)||','||TO_CHAR(P_YPNM00)||','||TO_CHAR(P_CKSL00)||') ; '; SELECT TO_CHAR(SYSDATE,'YYYYMMDD'),TO_CHAR(SYSDATE,'HH24:MI:SS') INTO V_DQRQ00,V_DQSJ00 FROM DUAL; --取出多零售价管理,发药规则 JETHUA 2004.03.24 BEGIN SELECT Trim(VALUE0) INTO v_FYGZ00 FROM XT_XTCS00 WHERE NAME00='YF_DLSJFYGZ'; EXCEPTION WHEN OTHERS THEN v_FYGZ00:='1'; END; --取用户个性化参数 JETHUA 2004.03.24 BEGIN SELECT Trim(VALUE0) INTO v_YYCS00 FROM XT_XTCS00 WHERE NAME00='YF_ZJKFDDDYGS'; EXCEPTION WHEN OTHERS THEN v_YYCS00:='0'; END; ------------首次遍历,判断此药品出库价格--------- --判断批次预扣 SELECT YPMC00 INTO V_YPMC00 FROM BM_YD0000 WHERE YPNM00=P_YPNM00; SELECT SUM(SJKCSL) INTO V_SUM000 FROM YF_PCKCMX WHERE YFBMBH=P_YFBMBH AND YPNM00=P_YPNM00; V_SUM000:=nvl(V_SUM000,0); --莆田不判断预扣库存 and v_YYCS00<>'1' IF V_SUM0000); EXCEPTION WHEN OTHERS THEN SELECT LSDJ00,GJDJ00 INTO V_DQLSDJ,V_DQGJDJ FROM YF_PCKCMX WHERE YFBMBH=P_YFBMBH AND YPNM00=P_YPNM00 AND trim(KCLSH0) IN (SELECT to_char(MAX(TO_NUMBER(KCLSH0))) FROM YF_PCKCMX WHERE YFBMBH=P_YFBMBH AND YPNM00=P_YPNM00 ); END; OPEN C_PCKC00; FETCH C_PCKC00 INTO V_KCLSH0,V_SCPH00,V_LSDJ00,V_YKKCSL,V_GJDJ00,V_YPSXRQ,V_YKRKPC; V_SYCKSL:=P_CKSL00; V_DQLSDJ:=V_LSDJ00; V_DQGJDJ:=V_GJDJ00; WHILE C_PCKC00%FOUND AND V_SYCKSL>0 LOOP IF V_SYCKSL-V_YKKCSL <=0 THEN --本批次够出剩余出库量 V_BCCKSL:=V_SYCKSL; V_SYCKSL:=0; ELSE V_BCCKSL:=V_YKKCSL; V_SYCKSL:=V_SYCKSL-V_BCCKSL; END IF; ---- IF v_FYGZ00='1' THEN --以低价格出库 IF V_LSDJ00V_DQLSDJ THEN V_DQLSDJ:=V_LSDJ00; V_DQGJDJ:=V_GJDJ00; END IF; END IF; FETCH C_PCKC00 INTO V_KCLSH0,V_SCPH00,V_LSDJ00,V_YKKCSL,V_GJDJ00,V_YPSXRQ,V_YKRKPC; END LOOP; CLOSE C_PCKC00; ------------第二次遍历,进行实际预扣--------- --取批次库存流水号,用于之后批次库存的调整 SELECT to_char(MIN(TO_NUMBER(KCLSH0))) INTO V_KCLSH1 FROM YF_PCKCMX WHERE YFBMBH=P_YFBMBH AND YPNM00=P_YPNM00 AND LSDJ00=V_DQLSDJ; OPEN C_PCKC00; FETCH C_PCKC00 INTO V_KCLSH0,V_SCPH00,V_LSDJ00,V_YKKCSL,V_GJDJ00,V_YPSXRQ,V_YKRKPC; V_SYCKSL:=P_CKSL00; WHILE C_PCKC00%FOUND AND V_SYCKSL>0 LOOP IF V_SYCKSL-V_YKKCSL <=0 THEN --本批次够出剩余出库量V_DQLSDJ V_BCCKSL:=V_SYCKSL; V_SYCKSL:=0; ELSE V_BCCKSL:=V_YKKCSL; V_SYCKSL:=V_SYCKSL-V_BCCKSL; END IF; --判断零售价是否相同 OR P_SFXJJZ=1 考虑到退预扣，暂不考虑 --IF V_DQLSDJ=V_LSDJ00 THEN IF trim(V_KCLSH0)=trim(V_KCLSH1) THEN UPDATE YF_PCKCMX SET YKKCSL=YKKCSL-V_BCCKSL WHERE KCLSH0=V_KCLSH1; ELSE --零售价不同,进行批次库存调整 --增加出库价格批次库存的实际库存数量 UPDATE YF_PCKCMX SET SJKCSL=SJKCSL+V_BCCKSL WHERE trim(KCLSH0)=trim(V_KCLSH1); --减少此价格批次库存的实际和预扣库存数量 UPDATE YF_PCKCMX SET SJKCSL=SJKCSL-V_BCCKSL, YKKCSL=YKKCSL-V_BCCKSL WHERE KCLSH0=V_KCLSH0; --删除零库存记录 DELETE YF_PCKCMX WHERE YFBMBH=P_YFBMBH AND YPNM00=P_YPNM00 AND SJKCSL=0; --取基本信息 SELECT YPMC00,YPGG00,KCDW00,K2J000,GJJ000 INTO V_YPMC00,V_YPGG00,V_KCDW00,V_K2J000,V_GJJ000 FROM BM_YD0000 WHERE YPNM00=P_YPNM00; --取库存和库存金额 SELECT SUM(ROUND(SJKCSL/V_K2J000,3)),ROUND(SUM(ROUND(SJKCSL/V_K2J000,3)*LSDJ00),2), ROUND(SUM(ROUND(SJKCSL/V_K2J000,3)*GJDJ00),2) INTO V_KCSL00,V_KCJE00,V_KCGJJE FROM YF_PCKCMX WHERE YFBMBH=P_YFBMBH AND YPNM00=P_YPNM00; --记帐 -- IF V_DQLSDJ-V_LSDJ00>0 THEN --报盈 IF (ROUND((V_DQLSDJ-V_LSDJ00)*V_BCCKSL/V_K2J000,2)>0) OR (ROUND((V_DQGJDJ-V_GJDJ00)*V_BCCKSL/V_K2J000,2)>0) THEN --报盈 INSERT INTO YF_YPMXZ0( MXZLSH, --明细账流水号-->SQ_YF_YPMXZ0_MXZLSH YFBMBH, --药房部门编号 YPNM00, --药品内码 YPMC00, --药品名称 YPGG00, --规格 JZRQ00, --记账日期 JZSJ00, --记账时间 CRBMBH, --出入部门编号 ZY0000, --摘要(自动：出入部门名称) JZDW00, --记账单位 ZHL000, --转换率(记账单位/剂量单位) GJDJ00, --购进单价(记账单位) SLDJ00, --收入单价(零售价)(记账单位) SLSL00, --收入数量(记账单位) SLJE00, --收入金额(零售价) JCDJ00, --结存单价(结存金额/结存数量) JCSL00, --结存数量(记账单位)(药品总量) JCJE00, --结存金额(零售价)(药品总量) RCLXBH, --入出库类型编码:BM_YFRKLX,BM_YFCKLX RCLXMC, --入出库类型名称:BM_YFRKLX,BM_YFCKLX JZXZ00, --记账性质:'R',入库,'C',出库 JCGJJE, GJSLJE ) VALUES (SQ_YF_YPMXZ0_MXZLSH.NEXTVAL, P_YFBMBH, P_YPNM00, V_YPMC00, V_YPGG00, V_DQRQ00, V_DQSJ00, 66666, '处方调盈', V_KCDW00, V_K2J000, V_GJJ000, V_DQLSDJ, 0, ROUND((V_DQLSDJ-V_LSDJ00)*V_BCCKSL/V_K2J000,2), DECODE(v_KCSL00,0,0,ROUND(v_KCJE00/v_KCSL00*V_K2J000,4)), V_KCSL00, V_KCJE00, '07', '调价报盈', 'R', V_KCGJJE, ROUND((V_DQGJDJ-V_GJDJ00)*V_BCCKSL/V_K2J000,2)); ELSIF (ROUND((V_DQLSDJ-V_LSDJ00)*V_BCCKSL/V_K2J000,2)<0) OR (ROUND((V_DQGJDJ-V_GJDJ00)*V_BCCKSL/V_K2J000,2)<0) THEN --报亏 INSERT INTO YF_YPMXZ0( MXZLSH, --明细账流水号-->SQ_YF_YPMXZ0_MXZLSH YFBMBH, --药房部门编号 YPNM00, --药品内码 YPMC00, --药品名称 YPGG00, --规格 JZRQ00, --记账日期 JZSJ00, --记账时间 CRBMBH, --出入部门编号 ZY0000, --摘要(自动：出入部门名称) JZDW00, --记账单位 ZHL000, --转换率(记账单位/剂量单位) GJDJ00, --购进单价(记账单位) FCDJ00, --发出单价(零售价)(记账单位) FCSL00, --发出数量(记账单位) FCJE00, --发出金额(零售价) JCDJ00, --结存单价(结存金额/结存数量) JCSL00, --结存数量(记账单位)(药品总量) JCJE00, --结存金额(零售价)(药品总量) RCLXBH, --入出库类型编码:BM_YFRKLX,BM_YFCKLX RCLXMC, --入出库类型名称:BM_YFRKLX,BM_YFCKLX JZXZ00, --记账性质:'R',入库,'C',出库 JCGJJE, --购进结存金额 GJFCJE --购进付出金额 ) VALUES (SQ_YF_YPMXZ0_MXZLSH.NEXTVAL, P_YFBMBH, P_YPNM00, V_YPMC00, V_YPGG00, V_DQRQ00, V_DQSJ00, 66666, '处方调亏', V_KCDW00, V_K2J000, V_GJJ000, V_DQLSDJ, 0, -ROUND((V_DQLSDJ-V_LSDJ00)*V_BCCKSL/V_K2J000,2), DECODE(v_KCSL00,0,0,ROUND(v_KCJE00/v_KCSL00*V_K2J000,4)), V_KCSL00, V_KCJE00, '06', '调价报亏', 'C', v_KCGJJE, -ROUND((V_DQGJDJ-V_GJDJ00)*V_BCCKSL/V_K2J000,2) ); END IF; END IF; --记录SCPH00，YPSXRQ YFPY-20201123-001 if (P_CFLSH0<>' ') and (P_CFLSH0 is not null) then UPDATE YF_MZCFMX SET SCPH00=V_SCPH00,YPSXRQ=V_YPSXRQ,KCLSH0=V_KCLSH1 WHERE CFLSH0=P_CFLSH0 and YPNM00=P_YPNM00 ; end if; if nvl(P_CFID00,0)>0 then insert into YF_KCYKMX(ID0000, DJLX00, MZZYBZ, LYDJH0, YKSL00, YKDW00, ZHL000, SYSL00, LSDJ00, GJDJ00, YFBMBH, YPNM00, KCLSH0, YKRKPC, CFLSH0, SCPH00, YPSXRQ) values( SQ_YF_KCYKMX_ID0000.nextval, P_djlx00, P_MZZYBZ, P_CFID00, V_BCCKSL, 'JLDW00', 1, V_SYCKSL, V_LSDJ00,V_GJDJ00, P_YFBMBH, P_YPNM00, V_KCLSH0, nvl(V_YKRKPC,0), P_CFLSH0, V_SCPH00,V_YPSXRQ); end if; FETCH C_PCKC00 INTO V_KCLSH0,V_SCPH00,V_LSDJ00,V_YKKCSL,V_GJDJ00,V_YPSXRQ,V_YKRKPC; END LOOP; CLOSE C_PCKC00; P_CKLSJ0:=V_DQLSDJ; P_CKGJJ0:=V_DQGJDJ; SP_YF_CLLSL0(P_YFBMBH,P_YPNM00); --ROLLBACK; EXCEPTION WHEN no_data_found THEN RAISE_APPLICATION_ERROR(-20001,substrb('数据没有找到！*'||SQLERRM||','||V_params,1,120)); WHEN E_custom THEN RAISE_APPLICATION_ERROR(-20010,substrb(V_errmsg||'！*'||V_params,1,120)); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20266,substrb(nvl(SQLERRM, '原因不明出错')||'！*'||V_params,1,120)); END;