CREATE OR REPLACE PROCEDURE SD_HOSPITAL.SP_YF_YPSLJHSC( P_YKBMBH IN NUMBER, --输入：药库部门编号 P_YFBMBH IN NUMBER, --输入：药房部门编号 P_CZYBH0 IN NUMBER, --输入：操作员编码 P_LYRBH0 IN NUMBER, --输入：领药人编码 P_LBBH00 IN VARCHAR2, --输入：药品类别编号 P_YLBH00 IN VARCHAR2, --输入：药理编号 P_JXBH00 IN VARCHAR2, --输入：剂型编号 P_CGJHDH OUT VARCHAR2, --输出：申领计划单号 P_MESSAGE OUT VARCHAR2 --输出：申领计划单条数信息 )AS V_COUNTER NUMBER; --记数 V_DQRQ00 CHAR(8); V_DQSJ00 CHAR(8); V_SFDYSX XT_XTCS00.VALUE0%TYPE; ls_error varchar2(2000); -- MODIFICATION HISTORY -- Person Date Comments -- JETHUA 2003.06.25 如上限下限都为零则不应提取 -- JETHUA 2003.07.01 取消一条单据十条的显示 -- JETHUA 2003.09.28 错误提示截取200字节，避免错误 -- daihq 2014.06.26 生成的计划单状态改为3 for YF-20140626-001 -- csf 2015.04.09 原从VW_YF_YPKCXX取数据现改成从YF_YPKCXX F,VW_YF_YKWTZYPKCXX K取数据，以保证提取的数据有对应的药库隶属且没有被停止的药品。 for YF-20150409-003 -- csf 2015.05.13 暂停药品不提取 for YF-20150513-001 -- chenhn 2016.05.30 设置默认数量是否取库存上限值 for YF-20160516-001 -- --------- ---------- -------------------------------------------------- BEGIN P_CGJHDH:='0'; P_MESSAGE:=''; --取请领数量是否等于库存上限值参数 BEGIN SELECT VALUE0 INTO V_SFDYSX FROM XT_XTCS00 WHERE NAME00='YF_QLSLSFDYKCSX'; EXCEPTION WHEN OTHERS THEN V_SFDYSX:='N'; END; SELECT TO_CHAR(SYSDATE,'YYYYMMDD'),TO_CHAR(SYSDATE,'HH24:MI:SS') INTO V_DQRQ00,V_DQSJ00 FROM DUAL; ---判断是否有需要申领的药品 SELECT COUNT(YFBMBH) INTO V_COUNTER FROM YF_YPKCXX F,VW_YF_YKWTZYPKCXX K WHERE F.YPNM00=K.YPNM00 AND K.YKBMBH=P_YKBMBH AND F.YFBMBH=P_YFBMBH -- AND F.SJKCSL<=F.KCXX00 AND K.LBBH00 LIKE P_LBBH00 || '%' AND K.YLBH00 LIKE P_YLBH00 || '%' AND K.JXBH00 LIKE P_JXBH00 || '%' AND F.ZTBZ00=0; IF V_COUNTER>0 THEN DECLARE CURSOR C_YF_YPSLJH IS SELECT F.YPNM00, K.YPMC00, K.YPGG00, K.KCDW00, K.K2J000, K.GJJ000, K.LSJ000, F.KCSX00, (F.SJKCSL/k.k2j000) sjkcsl FROM YF_YPKCXX F,VW_YF_YKWTZYPKCXX K WHERE F.YPNM00=K.YPNM00 AND K.YKBMBH=P_YKBMBH AND F.YFBMBH=P_YFBMBH -- AND F.SJKCSL<=F.KCXX00 AND K.LBBH00 LIKE P_LBBH00 || '%' AND K.YLBH00 LIKE P_YLBH00 || '%' AND K.JXBH00 LIKE P_JXBH00 || '%' --- AND NOT EXISTS (SELECT 1 FROM YF_YPKCXX X WHERE X.YPNM00=F.YPNM00 AND X.KCSX00=0 AND X.KCXX00=0) AND F.ZTBZ00=0; V_YF_YPSLJH C_YF_YPSLJH%ROWTYPE; BEGIN ---AAA P_CGJHDH:=SF_YK_GETLSH('YKYPQLDH'); P_MESSAGE:=P_CGJHDH; INSERT INTO YK_YPQLD0( YPQLDH, --药品请领单号-->SQ_YK_YPQLD0_YPQLDH YKBMBH, --药库部门编号 CKQXBH, --出库去向编号:出库去向-->VW_YK_YPCKFX CKLXBH, --出库类型编码 -->BM_YKCKLX QLRQ00, --请领日期 QLSJ00, --请领时间 CZYBH0, --操作员编码 LYRBH0, --领药人编号 QLZT00) --请领状态 --'0'请领状态 --'1'药库出库 --'2'领用部门入库 VALUES( P_CGJHDH, P_YKBMBH, P_YFBMBH, '05', V_DQRQ00, V_DQSJ00, P_CZYBH0, P_LYRBH0, '3'); --'0'); OPEN C_YF_YPSLJH; FETCH C_YF_YPSLJH INTO V_YF_YPSLJH; WHILE C_YF_YPSLJH%FOUND LOOP INSERT INTO YK_YPQLMX( YPQLPC, --药品请领批次-->SQ_YK_YPQLMX_YPQLPC YPQLDH, --请领单号-->YK_YPQLD0 YPNM00, --药品内码 YPMC00, --药品名称 YPGG00, --规格 QLDW00, --请领单位 ZHL000, --转换率(请领单位/剂量单位) GJDJ00, --购进单价(请领单位)(最近一批) QLDJ00, --请领单价(请领单位) QLSL00) --请领数量(请领单位) VALUES( SQ_YK_YPQLMX_YPQLPC.NEXTVAL, P_CGJHDH, V_YF_YPSLJH.YPNM00, V_YF_YPSLJH.YPMC00, V_YF_YPSLJH.YPGG00, V_YF_YPSLJH.KCDW00, V_YF_YPSLJH.K2J000, V_YF_YPSLJH.GJJ000, V_YF_YPSLJH.LSJ000, -- ROUND((V_YF_YPSLJH.KCSX00-V_YF_YPSLJH.SJKCSL)/V_YF_YPSLJH.K2J000) -- (SELECT KCSX00 FROM VW_YF_YPSLSRTS1 B WHERE YPNM00=V_YF_YPSLJH.YPNM00 AND B.YFBMBH=P_YFBMBH AND B.SJKC00<=B.KCXX00 ) V_YF_YPSLJH.SJKCSL ); IF V_SFDYSX='Y' THEN --默认请领数量值等于库存上限值 ls_error:='1@'; UPDATE YK_YPQLMX A SET A.QLSL00=(SELECT KCSX00 FROM VW_YF_YPSLSRTS1 B WHERE YPNM00=a.YPNM00 AND B.YFBMBH=P_YFBMBH -- AND B.SJKC00<=B.KCXX00 AND rownum=1) WHERE trim(A.YPQLDH)=trim(P_CGJHDH) and ypnm00=V_YF_YPSLJH.YPNM00; ls_error:='2@'; ls_error:=V_YF_YPSLJH.YPNM00; END IF; FETCH C_YF_YPSLJH INTO V_YF_YPSLJH; END LOOP; ---FOR WHILE ... LOOP OUTER CLOSE C_YF_YPSLJH; END; ---AAA P_MESSAGE:=SUBSTR(P_MESSAGE,2,LENGTH(P_MESSAGE));--去掉前面的‘，’ END IF; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20201,trim(substrb('生成申领计划单时发生意外错误!*返回信息:'|| '调用过程: SP_YF_YPSLJHSC('||TO_CHAR(P_YKBMBH)||ls_error|| ','||TO_CHAR(P_YFBMBH)||','||TO_CHAR(P_CZYBH0)|| ','||TO_CHAR(P_LYRBH0)||','||P_LBBH00||','||P_YLBH00||','||P_YLBH00|| ','||P_CGJHDH||') ; '||SQLERRM,1,200))); ROLLBACK; END; ---------------------------------------------- CREATE OR REPLACE VIEW VW_YF_YPKCXX (ykbmbh, yfbmbh, ypnm00, ypbm00, pysm00, wbsm00, pysmty, ypmc00, ypgg00, ypdw00, kcdw00, jldw00, zyfydw, mzfydw, k2j000, z2j000, m2j000, lsj000, gjj000, zhl000, kcsx00, kcxx00, ykkcsl, sjkcsl, kcje00, pdxh00, lbbh00, ylbh00, jxbh00, lxbh00, pddw00, pdzhl0, gzdj00, ztbz00, sccjbh, sfztpd, tzrq00, cfwz00, cfwzmc, bmpysm, sjkc00, ghdwmc) AS SELECT D.YKBMBH, F.YFBMBH, F.YPNM00, D.YPBM00, D.PYSM00, D.WBSM00, substr(SF_YJ_getPYSM(D.YPTYM0),1,30), D.YPMC00, D.YPGG00, D.KCDW00, D.KCDW00, D.JLDW00, D.ZYFYDW, D.MZFYDW, D.K2J000, D.Z2J000, D.M2J000, F.LSDJ00, D.GJJ000, D.K2J000, F.KCSX00, F.KCXX00, ROUND(F.YKKCSL/D.K2J000,3), ROUND(F.SJKCSL/D.K2J000,3), ROUND(ROUND(F.SJKCSL/D.K2J000,3)*F.LSDJ00,2), F.PDXH00, D.LBBH00, D.YLBH00, D.JXBH00, F.LXBH00, F.PDDW00, F.ZHL000, F.GZDJ00, F.ZTBZ00, --暂停标志 D.SCCJBH, F.SFZTPD, D.TZRQ00, F.CFWZ00, (SELECT CFWZMC from BM_YPCFWZ WHERE BMBH00=F.YFBMBH AND CFWZBH=F.CFWZ00 AND ROWNUM=1) CFWZMC, substr((select sf_yf_YPBMPYSMLJ(F.YPNM00) from dual),1,80) BMYPSM, ROUND(F.SJKCSL/D.K2J000,3), (select GHDWMC FROM BM_YPGHDW C WHERE C.GHDWNM=D.GHDWNM AND ROWNUM=1) GHDWMC FROM YF_YPKCXX F,BM_YD0000 D WHERE F.YPNM00=D.YPNM00;