create or replace procedure SP_YF_YPSLRKQR( -- modification history -- Person Date Comments -- lintj 2012.06.19 modify SP_YF_YPRKD0_AUDIT -- qks 2012.08.23 JLZHL0为NULL引起入出库数量合计为NULL -- qks 2012.10.19 药房之间申领药品 -- zhangwz 2012.10.31 YF_YPCKMX.GJDJ00 and LSDJ00 统一变更为存储库存单位的 GJDJ00 and LSDJ00 ,不和YF_YPCKMX.CKDW00 挂钩起来 进行相应的修改. -- qks 2013.08.17 增加入参P_SFZDFF -- qks 2013.12.03 写入YF_YPKCXX表去掉重复的记录 -- qks 2014.04.10 物资申领入库也自动隶属 -- qks 2014.10.29 供应室要求判断是否属于首次启用批次 -- chenzh 2016.11.07 把隶属的药品全部在错误中显示出来； -- --------- ---------- ------------------------------------------- p_YPQLDH in char, --输入 药品请领单号 p_YFBMBH in char, --输入 药房部门编号 P_QRRBH0 in number, --输入 确认人编号 p_YPRKDH OUT char, --输出 药品入库单号 P_SFZDFF in varchar2 default 'N' --是否自动分发隶属 )as V_count number; --记数器 V_RKDH00 YF_YPRKD0.RKDH00%type; V_QLZT00 YK_YPQLD0.QLZT00%type; v_KHHCS0 XT_XTCS00.VALUE0%type; --客户化参数:0默认;8广州大院 v_SFGYTB XT_XTCS00.VALUE0%type; --是否各分院同步 v_YYID00 XT_YYXX00.YYID00%type; --医院ID V_YKBMBH YK_YPQLD0.YKBMBH%type; V_CKDH00 YK_YPQLD0.CKDH00%type; V_BMXZ00 BM_BMBM00.BMXZ00%type; V_SJKCSL YF_PCKCMX.SJKCSL%type; V_DQRQ00 char(8); V_DQSJ00 char(8); V_SFYFSQ char(1); --是否药房之间申领药品 Y是,N否 E_NoConfirm exception; --重复确认错误 E_NoBaseRecord exception; --药房隶属药品没有记录 Vzxcgbz number; Vtsxx00 varchar2(200); Vxxxx00 varchar2(200); V_wlsyp0 varchar2(200); cursor C_YFRKMX IS select * from YF_YPRKMX where RKDH00=V_RKDH00; cursor C_WLSYP is select ypnm00,ypmc00 from YK_YPQLMX A where not exists(select 1 from YF_YPKCXX B where B.YFBMBH=P_YFBMBH and A.YPNM00=B.YPNM00) and YPQLDH=P_YPQLDH; begin --取出客户化参数:0默认;8广州大院 select nvl(max(trim(VALUE0)),'0') into v_KHHCS0 from XT_XTCS00 where NAME00='YF_ZJKFDDDYGS'; --取出客户化参数:0默认;8广州大院 select nvl(max(trim(VALUE0)),'N') into v_SFGYTB from XT_XTCS00 where NAME00='YF_DBLinK'; select to_char(sysdate,'yyyymmdd'),to_char(sysdate,'hh24:mi:ss') into V_DQRQ00,V_DQSJ00 from dual; ---判断是否已经被确认 select QLZT00,YKBMBH,CKDH00 into V_QLZT00,V_YKBMBH,V_CKDH00 from YK_YPQLD0 where YPQLDH=P_YPQLDH; if V_QLZT00<>'1' then raise E_NoConfirm; end if; ---判断是否属于药房之间申领 --select count(1) into V_count from bm_bmbm00 where bmbh00=V_YKBMBH and bmxz00 in ('2','3'); select max(BMXZ00) into V_BMXZ00 from bm_bmbm00 where bmbh00=V_YKBMBH; --V_BMXZ00 if V_BMXZ00 in ('2','3') then V_SFYFSQ := 'Y'; end if; if P_SFZDFF = 'Y' or V_BMXZ00 in ('c','k') then insert into YF_YPKCXX (YFBMBH,YPNM00,LSDJ00,PDDW00,ZHL000,FYDW00,F2J000,GJDJ00) select distinct p_YFBMBH,A.YPNM00,C.LSJ000,C.ZYFYDW,C.Z2J000,C.ZYFYDW,C.Z2J000,C.GJJ000 from YK_YPQLMX A,BM_YD0000 c where a.YPNM00=c.YPNM00 and A.CXBZ00='Z' and YPQLDH=P_YPQLDH and not exists (select 1 from YF_YPKCXX where YFBMBH=P_YFBMBH and YPNM00=A.YPNM00); end if; ---判断是否在药房隶属药品表中是否有记录 select count(YPNM00) into V_count from YK_YPQLMX A where exists(select 1 from YF_YPKCXX B where B.YFBMBH=P_YFBMBH and A.YPNM00=B.YPNM00) and YPQLDH=P_YPQLDH; --把未隶属的药品显示出来； for mycusr in C_WLSYP loop V_wlsyp0 :=V_wlsyp0|| mycusr.ypmc00||','; end loop; if V_count=0 then raise E_NoBaseRecord; end if; --1,插入药房入库单YF_YPRKD0 ,药房入库明细YF_YPRKMX (待确认) select SF_YK_GETLSH('YFYPRKDH') into V_RKDH00 from dual; insert into YF_YPRKD0( RKDH00, --入库单号-->SQ_YF_YPRKD0_RKDH00 YFBMBH, --药房部门编号 RKBMBH, --入库部门编号 RKLXBH, --入库类型编码 -->BM_YFRKLX YPQLDH, --请领单号-->YK_YPQLD0 RKZT00, --入库状态，'0'未确认,'1'已确认 CZY000, --操作员 LYR000, --领药人 QRR000, --确认人 RKRQ00, --入库日期 RKSJ00, --入库时间 QRRQ00, --确认日期 QRSJ00) --确认时间 select V_RKDH00, CKQXBH, YKBMBH, '05', --'药库发药' YPQLDH, '0', P_QRRBH0, LYRBH0, P_QRRBH0, V_DQRQ00, V_DQSJ00, V_DQRQ00, V_DQSJ00 from YK_YPQLD0 where YPQLDH=P_YPQLDH; if (V_SFYFSQ='Y') and (V_CKDH00 is not null) then insert into YF_YPRKMX( YPRKPC, --药品入库批次-->SQ_YF_YPRKMX_YPRKPC RKDH00, --入库单号YF_YPRKD0_RKDH00 YPNM00, --药品内码 YPMC00, --药品名称 YPGG00, --规格 RKSL00, --入库数量(入库单位) RKDW00, --入库单位 ZHL000, --转换率(入库单位/剂量单位) GJDJ00, --购进单价(入库单位) LSDJ00, --零售单价(入库单位) SCPH00, --批号 YPSXRQ, --药品失效日期 SCCJBH, --生产厂家编号 CXBZ00, --冲销标志: --'Z'正常,'-'冲销记录,'+'被冲记录 YKRKPC) select SQ_YF_YPRKMX_YPRKPC.NEXTVAL, V_RKDH00, YPNM00, YPMC00, YPGG00, CKSL00, CKDW00, ZHL000, GJDJ00, LSDJ00, SCPH00, YPSXRQ, SCCJBH, 'Z', YPRKPC from (select A.YPNM00, A.YPMC00, A.YPGG00, A.CKSL00, A.CKDW00, A.ZHL000, A.GJDJ00, A.LSDJ00, B.SCPH00, B.YPSXRQ, B.SCCJBH, B.YPRKPC from YF_YPCKMX A,YK_YPRKMX B,BM_YD0000 C where A.YKRKPC=B.YPRKPC and A.CXBZ00='Z' and A.CKDH00=V_CKDH00 and A.YPNM00=C.YPNM00 order by A.YPCKPC); elsif v_SFGYTB='N' then insert into YF_YPRKMX( YPRKPC, --药品入库批次-->SQ_YF_YPRKMX_YPRKPC RKDH00, --入库单号YF_YPRKD0_RKDH00 YPNM00, --药品内码 YPMC00, --药品名称 YPGG00, --规格 RKSL00, --入库数量(入库单位) RKDW00, --入库单位 ZHL000, --转换率(入库单位/剂量单位) GJDJ00, --购进单价(入库单位) LSDJ00, --零售单价(入库单位) SCPH00, --批号 YPSXRQ, --药品失效日期 SCCJBH, --生产厂家编号 CXBZ00, --冲销标志: --'Z'正常,'-'冲销记录,'+'被冲记录 YKRKPC) select SQ_YF_YPRKMX_YPRKPC.NEXTVAL, V_RKDH00, YPNM00, YPMC00, YPGG00, CKSL00, CKDW00, ZHL000, GJDJ00, LSDJ00, SCPH00, YPSXRQ, SCCJBH, 'Z', YPRKPC from (select A.YPNM00, A.YPMC00, A.YPGG00, SF_YF_YPSLZH(a.CKSL00*a.ZHL000+nvl(a.JLCKSL*a.JLZHL0,0),a.YPNM00,1) CKSL00, SF_YF_YPSLZH(a.CKSL00*a.ZHL000+nvl(a.JLCKSL*a.JLZHL0,0),a.YPNM00,0) CKDW00, SF_YF_YPSLZH(a.CKSL00*a.ZHL000+nvl(a.JLCKSL*a.JLZHL0,0),a.YPNM00,2) ZHL000, round(a.GJDJ00*c.K2J000/a.ZHL000,4) GJDJ00,round(a.LSDJ00*c.K2J000/a.ZHL000,4) LSDJ00,B.SCPH00, B.YPSXRQ, B.SCCJBH,B.YPRKPC from YK_YPCKMX A,YK_YPRKMX B,BM_YD0000 c where A.YPRKPC=B.YPRKPC and a.YPNM00=c.YPNM00 and A.CXBZ00='Z' and YPQLDH=P_YPQLDH ORDER BY A.YPCKPC); ELSE insert into YF_YPRKMX( YPRKPC, --药品入库批次-->SQ_YF_YPRKMX_YPRKPC RKDH00, --入库单号YF_YPRKD0_RKDH00 YPNM00, --药品内码 YPMC00, --药品名称 YPGG00, --规格 RKSL00, --入库数量(入库单位) RKDW00, --入库单位 ZHL000, --转换率(入库单位/剂量单位) GJDJ00, --购进单价(入库单位) LSDJ00, --零售单价(入库单位) SCPH00, --批号 YPSXRQ, --药品失效日期 SCCJBH, --生产厂家编号 CXBZ00) --冲销标志: --'Z'正常,'-'冲销记录,'+'被冲记录 select SQ_YF_YPRKMX_YPRKPC.NEXTVAL, V_RKDH00, A.YPNM00, A.YPMC00, a.YPGG00, SF_YF_YPSLZH(a.SFSL00*a.ZHL000+nvl(a.JLSFSL*a.JLZHL0,0),a.YPNM00,1) CKSL00, SF_YF_YPSLZH(a.SFSL00*a.ZHL000+nvl(a.JLSFSL*a.JLZHL0,0),a.YPNM00,0) QLDW00, SF_YF_YPSLZH(a.SFSL00*a.ZHL000+nvl(a.JLSFSL*a.JLZHL0,0),a.YPNM00,2) ZHL000, round(a.GJDJ00*c.K2J000/a.ZHL000,4),round(a.QLDJ00*c.K2J000/a.ZHL000,4), '', '', '' , 'Z' from YK_YPQLMX A,BM_YD0000 c where a.YPNM00=c.YPNM00 and A.CXBZ00='Z' and YPQLDH=P_YPQLDH; end if; --2, -----确认入库单 SP_YF_YPRKD0_AUDIT(V_RKDH00,V_DQRQ00,V_DQSJ00,P_QRRBH0,null,'N',Vzxcgbz,Vtsxx00,Vxxxx00); if Vzxcgbz = 0 then raise_application_error(-20210,'入库单确认出错'); end if; --3, 更改药库药品请领单表YK_YPQLD0(QLZT00)--请领状态'0'请领状态'1'药库出库'2'领用部门入库 update YK_YPQLD0 SET QLZT00='2' where YPQLDH=P_YPQLDH; p_YPRKDH:=V_RKDH00; if V_BMXZ00 in ('c','k') then for YFRKMX in C_YFRKMX loop select nvl(sum(SJKCSL),0) into V_SJKCSL from YF_PCKCMX where YFBMBH=p_YFBMBH and YPNM00=YFRKMX.YPNM00 --and YKRKPC=YFRKMX.YKRKPC and SCPH00=YFRKMX.SCPH00 and YPSXRQ=YFRKMX.YPSXRQ; if YFRKMX.RKSL00*YFRKMX.ZHL000=V_SJKCSL then Update YF_YPRKMX set BZ0000=substrb('新批次'||BZ0000,1,100) where YPRKPC=YFRKMX.YPRKPC; end if; end loop; end if; exception when E_NoConfirm then raise_application_error(-20202, trim(substrb('该申领单已经被其他用户确认过，不能重复确认!*返回信息:'|| '调用过程: SP_YF_YPSLRKQR('||p_YPQLDH||','||p_YFBMBH|| ','||to_char(P_QRRBH0)||') ; '||sqlerrm,1,200))); when E_NoBaseRecord then raise_application_error(-20203, trim(substrb(V_wlsyp0||'该申领单有药品未加入到药房隶属药品目录中,请先维护药房隶属药品目录!*返回信息:'|| '调用过程: SP_YF_YPSLRKQR('||p_YPQLDH||','||p_YFBMBH|| ','||to_char(P_QRRBH0)||') ; '||sqlerrm,1,200))); when others then raise_application_error(-20204, trim(substrb(V_wlsyp0||'申领入库确认时发生意外错误!*返回信息:'|| '调用过程: SP_YF_YPSLRKQR('||p_YPQLDH||','||p_YFBMBH|| ','||to_char(P_QRRBH0)||') ; '||sqlerrm,1,200))); end;