create or replace procedure SP_YF_RKZSM0_BACK -- modification history -- Person Date Comments -- linshu 2024.06.05 create 回写药库入库追溯码; -- linshu 2024.06.21 生产日期为空的，取失效日期前三个月; for YF9-20240621-003 -- linshu 2024.06.27 不重复生成YK_RKZSM0数据，生产日期为空的，取失效日期前三年; for YF9-20240627-002 -- linshu 2024.07.01 修正了同一个处方同一个药品有多个批次的情况，不能回写药库入库追溯码问题; for YF9-20240621-001 -- linshu 2024.07.04 增加日志; for YF9-20240703-001 -- linshu 2024.07.19 增加了住院扫码回写药库入库追溯码功能; for YF9-20240712-002 -- linshu 2024.07.26 增加了门诊处方同一药品有多条记录报错问题; for YF9-20240726-002 -- linshu 2024.07.31 修正一条追溯码被拆零成多个出库批次出库不能回写药库入库追溯码问题; for YF9-20240731-001 ( PMZZYBZ in varchar2 , --0:门诊 1：住院 PCFLSH0 in varchar2 , --门诊：处方流水号 住院：出库单号 PCZY000 in number , --操作员 Pcommit in varchar2 default 'N' , --是否提交 Pzxcgbz out number , --执行成功标志 1成功 0失败 Ptsxx00 out varchar2 , --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 out varchar2 , --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 PCZYKS0 in number default 0 , --操作员科室 PZYID00 in number default 0 , --住院ID PYPQLPC in number default 0 --YF_YZYPSQ.YPQLPC ) as Vsysdate date; --日期变量 Vcount number(14,4); --计数器变量 Ecustom exception; V_YKRKPC number(16); V_YFBMBH YF_MZCF00.YFBMBH%type; V_YPRKMX YK_YPRKMX%rowtype; V_SCRQ00 YK_YPRKMX.SCRQ00%type; V_ZSL000 number(5); V_SL0000 number(5); V_CZYKS0 YF_YPCKD0.YFBMBH%type; V_XXlOG0 varchar2(2000); begin Pzxcgbz := 0; Vsysdate := sysdate; Ptsxx00:=''; V_XXlOG0 :='PMZZYBZ='||nvl(to_char(PMZZYBZ),'null')||';'||'PCFLSH0='||nvl(to_char(PCFLSH0),'null')||';'||'PCZY000='||nvl(to_char(PCZY000),'null')||';'|| 'Pcommit='||nvl(to_char(Pcommit),'null')||';'||'PCZYKS0='||nvl(to_char(PCZYKS0),'0')||';'||'PZYID00='||nvl(to_char(PZYID00),'0')||';'|| 'PYPQLPC='||nvl(to_char(PYPQLPC),'0'); SP_TransLog(Vsysdate,'SP_YF_RKZSM0_BACK',PCZY000,PCZYKS0,V_XXlOG0); if PMZZYBZ = '0' then --门诊 select YFBMBH into V_YFBMBH from YF_MZCF00 where CFLSH0 = PCFLSH0; for cur_ypmxz0 in ( select * from YF_YPMXZ0 A where A.RCLXBH= '09' and A.DJH000 = PCFLSH0 and (a.ypnm00,a.ypckpc) in ( select b.ypnm00,b.ypqlpc from YF_YZZSMX b where not exists (select 1 from YK_RKZSM0 C WHERE C.ZSM000 = B.ZSM000 ) )) loop select decode(floor(a.fcsl00*a.zhl000/b.k2j000),0,1,floor(a.fcsl00*a.zhl000/b.k2j000)) into V_ZSL000 from YF_YPMXZ0 A,BM_YD0000 b where a.ypnm00 = b.ypnm00 and A.RCLXBH = '09' and A.DJH000 = PCFLSH0 and A.YPNM00 = cur_ypmxz0.YPNM00 and A.YKRKPC = cur_ypmxz0.YKRKPC and A.YPCKPC = cur_ypmxz0.YPCKPC; V_SL0000 := 0; --追溯码循环 for cur_zsm in ( select b.zsm000, b.ypnm00,b.ypqlpc from YF_YZZSMX b where b.ypnm00 = cur_ypmxz0.ypnm00 and b.ypqlpc = cur_ypmxz0.ypckpc and b.cflsh0 = PCFLSH0 and not exists (select 1 from YK_RKZSM0 C WHERE C.ZSM000 = B.ZSM000 ) ) loop --防止重复插入 select count(1) into Vcount from YK_RKZSM0 where zsm000 = cur_zsm.zsm000; V_SL0000 := V_SL0000 + 1; if (Vcount = 0) and (V_SL0000 <= V_ZSL000)then select * into V_YPRKMX from YK_YPRKMX A where A.YPRKPC = cur_ypmxz0.YKRKPC and A.YPNM00 = cur_zsm.Ypnm00; if V_YPRKMX.SCRQ00 is null then if V_YPRKMX.YPSXRQ is not null then select to_char(add_months(to_date(V_YPRKMX.YPSXRQ,'YYYYMMDD'), -36),'YYYYMMDD') into V_SCRQ00 FROM DUAL; else V_SCRQ00 := V_YPRKMX.SCRQ00; end if; else V_SCRQ00 := V_YPRKMX.SCRQ00; end if; insert into YK_RKZSM0 ( YKRKPC, YPNM00, ZSM000, ZSMLY0, LYKF00, CZY000, SCPH00, SCRQ00, YXQ000, ZT0000, SFLB00 ) values ( V_YPRKMX.YPRKPC, cur_zsm.YPNM00, cur_zsm.ZSM000, '0', V_YFBMBH, PCZY000, V_YPRKMX.Scph00, V_SCRQ00, V_YPRKMX.YPSXRQ, '0', '3' ); end if; end loop; end loop; elsif nvl(PYPQLPC,0) > 0 then for cur_CKMX in ( select A.YPQLPC,A.YPNM00,A.ZSM000,A.CFLSH0 from YF_CFZSMX A where A.YPQLPC = PYPQLPC and MZZYBZ = '1' and YZLB00 = '1' and not exists (select 1 from YK_RKZSM0 C WHERE C.ZSM000 = A.ZSM000 )) loop --获取药品的入库批次 select nvl(A.YKRKPC,0),B.YFBMBH into V_YKRKPC,V_CZYKS0 from YF_YPCKMX A,YF_YPCKD0 B where A.CKDH00 = B.CKDH00 and A.CKDH00 = cur_CKMX.CFLSH0 and A.YPQLPC = PYPQLPC and YPNM00 = cur_CKMX.Ypnm00 and rownum = 1; if V_YKRKPC > 0 then select * into V_YPRKMX from YK_YPRKMX A where A.YPRKPC = V_YKRKPC and A.YPNM00 = cur_CKMX.Ypnm00; if V_YPRKMX.SCRQ00 is null then if V_YPRKMX.YPSXRQ is not null then select to_char(add_months(to_date(V_YPRKMX.YPSXRQ,'YYYYMMDD'), -36),'YYYYMMDD') into V_SCRQ00 FROM DUAL; else V_SCRQ00 := V_YPRKMX.SCRQ00; end if; else V_SCRQ00 := V_YPRKMX.SCRQ00; end if; insert into YK_RKZSM0 ( YKRKPC, YPNM00, ZSM000, ZSMLY0, LYKF00, CZY000, SCPH00, SCRQ00, YXQ000, ZT0000, SFLB00 ) values ( V_YKRKPC, cur_CKMX.YPNM00, cur_CKMX.ZSM000, '0', V_CZYKS0, PCZY000, V_YPRKMX.Scph00, V_SCRQ00, V_YPRKMX.YPSXRQ, '0', '3' ); end if; end loop; elsif nvl(PZYID00,0) > 0 then for cur_YZYPSQ in ( select CKDH00 from YF_YZYPSQ B where B.ZYID00 = PZYID00 and B.QLZT00 = '2' ) loop for cur_CKMX in ( select A.YPQLPC,A.YPNM00,A.ZSM000,A.CFLSH0 from YF_CFZSMX A where A.CFLSH0 = cur_YZYPSQ.CKDH00 and A.ZYID00 = PZYID00 and MZZYBZ = '1' and YZLB00 = '1' and not exists (select 1 from YK_RKZSM0 C WHERE C.ZSM000 = A.ZSM000 )) loop --获取药品的入库批次 select nvl(A.YKRKPC,0),B.YFBMBH into V_YKRKPC,V_CZYKS0 from YF_YPCKMX A,YF_YPCKD0 B where A.CKDH00 = B.CKDH00 and A.CKDH00 = cur_CKMX.CFLSH0 and A.YPQLPC = cur_CKMX.YPQLPC and YPNM00 = cur_CKMX.YPNM00 and rownum = 1; if V_YKRKPC > 0 then select * into V_YPRKMX from YK_YPRKMX A where A.YPRKPC = V_YKRKPC and A.YPNM00 = cur_CKMX.Ypnm00; if V_YPRKMX.SCRQ00 is null then if V_YPRKMX.YPSXRQ is not null then select to_char(add_months(to_date(V_YPRKMX.YPSXRQ,'YYYYMMDD'), -36),'YYYYMMDD') into V_SCRQ00 FROM DUAL; else V_SCRQ00 := V_YPRKMX.SCRQ00; end if; else V_SCRQ00 := V_YPRKMX.SCRQ00; end if; insert into YK_RKZSM0 ( YKRKPC, YPNM00, ZSM000, ZSMLY0, LYKF00, CZY000, SCPH00, SCRQ00, YXQ000, ZT0000, SFLB00 ) values ( V_YKRKPC, cur_CKMX.YPNM00, cur_CKMX.ZSM000, '0', V_CZYKS0, PCZY000, V_YPRKMX.Scph00, V_SCRQ00, V_YPRKMX.YPSXRQ, '0', '3' ); end if; end loop; end loop; else for cur_CKMX in ( select A.YPQLPC,A.YPNM00,A.ZSM000,A.CFLSH0 from YF_CFZSMX A where A.CFLSH0 = PCFLSH0 and MZZYBZ = '1' and YZLB00 = '1' and not exists (select 1 from YK_RKZSM0 C WHERE C.ZSM000 = A.ZSM000 )) loop --获取药品的入库批次 select nvl(A.YKRKPC,0),B.YFBMBH into V_YKRKPC,V_CZYKS0 from YF_YPCKMX A,YF_YPCKD0 B where A.CKDH00 = B.CKDH00 and A.CKDH00 = PCFLSH0 and A.YPQLPC = cur_CKMX.YPQLPC and YPNM00 = CUR_CKMX.YPNM00 and rownum = 1; if V_YKRKPC > 0 then select * into V_YPRKMX from YK_YPRKMX A where A.YPRKPC = V_YKRKPC and A.YPNM00 = cur_CKMX.Ypnm00; if V_YPRKMX.SCRQ00 is null then if V_YPRKMX.YPSXRQ is not null then select to_char(add_months(to_date(V_YPRKMX.YPSXRQ,'YYYYMMDD'), -36),'YYYYMMDD') into V_SCRQ00 FROM DUAL; else V_SCRQ00 := V_YPRKMX.SCRQ00; end if; else V_SCRQ00 := V_YPRKMX.SCRQ00; end if; insert into YK_RKZSM0 ( YKRKPC, YPNM00, ZSM000, ZSMLY0, LYKF00, CZY000, SCPH00, SCRQ00, YXQ000, ZT0000, SFLB00 ) values ( V_YKRKPC, cur_CKMX.YPNM00, cur_CKMX.ZSM000, '0', V_CZYKS0, PCZY000, V_YPRKMX.Scph00, V_SCRQ00, V_YPRKMX.YPSXRQ, '0', '3' ); end if; end loop; end if; Pzxcgbz:=1; if pcommit='Y' then commit; end if; exception when no_data_found then raise_application_error(-20001,substrb('没有找到数据！*'||sqlerrm,1,240)); when Ecustom then raise_application_error(-20010,substrb(Ptsxx00||'！*',1,240)); when others then raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240)); end;