CREATE OR REPLACE PROCEDURE SP_YF_BQZSMSM_EDIT ( Popflag in number , --操作标志 0：增加 1：发药删除 2:退药 4:退药删除 PZSM000 in varchar2 , --追溯码 PRQ0000 in varchar2 , Pczy000 in number , --操作员 Pczyks0 in number , --操作员科室 pcommit in varchar2 , --是否提交 Pzxcgbz out number , --执行成功标志 1成功 0失败 Ptsxx00 out varchar2 , --执行结果提示信息，成功的情况下也可能有警告信息 PCJFL00 in varchar2 default '1' , --0即时传(35051)，1出院传 PBQBMBH in number default '0' , --病区部门编号 0 是全部 POTHER1 in varchar2 default '' --药品内码 ) -- modification history -- Person Date Comments -- linshu 2025.03.14 create 病区追溯码扫码; for YF9-20250314-002 -- linshu 2025.03.31 扫码时，随机分配各个请领批次的码出库数量(剂量单位) ; for YF9-20250331-001 -- linshu 2025.04.08 修正YF_CFZSMX.YZLB00 = 1; for YF9-20250408-001 -- linshu 2025.04.09 增加入参PBQBMBH; for YF9-20250409-001 -- linshu 2025.04.10 因沙县有分院模式，一个标识码对于两院区同种药身上，在摆药扫码时，会显示两个药品名称，报“无维护标识码问题”; for YF9-20250410-001 -- linshu 2025.04.17 修正的出库数量的计算; for YF9-20250416-003 -- linshu 2025.04.18 病区发药删除时数据回写到拆零库; for YF9-20250418-003 -- linshu 2025.04.23 修正YF_CFZSMX.YPZLS0,YF_CFZSMX.ZYID00赋值; for YF9-20250423-002 -- linshu 2025.04.23 场景分类的值有参数获取; for YF9-20250430-001 -- linshu 2025.05.16 场景分类YF_CFZSMX.CJFL00默认值改为1; for YF9-20250516-001 -- linshu 2025.05.16 不再写入YF_CFZSMX.YPCKPC for YF9-20250516-004 -- linshu 2025.05.20 已经使用过了不能再发药发码; for YF9-20250515-003 -- huangjy 2025.05.22 增加入参POTHER1 for YF9-20250519-001 -- linshu 2025.06.04 标识码有函数获取; for YF9-20250604-002 -- linshu 2025.06.06 修正：当删除追溯码时，其对应的数据需回退到拆零库中; for YF9-20250605-001 -- linshu 2025.06.20 追溯码未匹配到未扫码的药品时进行提示; for YF9-20250619-002 -- linshu 2025.08.26 限制医嘱所扫的追溯码对应的药品内码(YK_RKZSM0.YPNM00=YF_MZCFMX.YPNM00) for YF9-20250826-002 as Ecustom exception; Vcount number(2); --计数器变量 V_YPRKMX YK_YPRKMX%rowtype; LS_SCRQ00 YK_YPRKMX.SCRQ00%type; LS_CKDH00 YF_YPCKD0.CKDH00%type; LS_YPQLPC YF_YZYPSQ.YPQLPC%type; --LS_YPCKPC YF_YPCKMX.YPCKPC%type; LS_YPNM00 BM_YD0000.YPNM00%type; LS_YKRKPC YF_YPCKMX.YKRKPC%type; LS_YPMC00 BM_YD0000.YPMC00%type; LS_JLZSL0 YF_RKZSM0.JLZSL0%type; LS_ZSMSYL YF_RKZSM0.SYJLSL%type; LS_ZT0000 YF_RKZSM0.ZT0000%type; LS_SFCLBZ YF_CFZSMX.SFCLBZ%type; --拆零标志：0非拆零，1拆零 LS_YSMSL0 YF_CFZSMX.MCKSL0%type; --YF_YPQLPC 已扫码数量 LS_WSMSL0 YF_CFZSMX.MCKSL0%type; --YF_YPQLPC 未扫码数量 LS_FLAG00 varchar2(1); --标识 是否有生成扫码数据 LS_CYDYCS BM_TYZD00.MC0000%type; LS_SFCL00 YF_CFZSMX.YPZSL0%type; --除于是否有拆零 LS_CKSL00 YF_CFZSMX.YPZSL0%type; --码出库数量 LS_ZSID00 YF_YZZSMX.ZSID00%type; LS_YBJKID YF_YBZSRZ.YBJKID%type; LS_ZYID00 YF_YZYPSQ.ZYID00%type; LS_CJFL00 BM_TYZD00.MC0000%type; LS_YSYSL0 YF_CFZSMX.YPZSL0%type; ls_YPNM01 varchar2(12) ; LS_RKYPNM YK_RKZSM0.YPNM00%type; LS_RKYPMC BM_YD0000.YPMC00%type; begin Pzxcgbz := 0; LS_FLAG00 := '0'; if Popflag = 0 then --扫码增加 --是否包含出院带药 begin select nvl(trim(MC0000),'0') into LS_CYDYCS from BM_TYZD00 where ZDMC00='医保追溯码配置' and BH0000='BQSMCYDY' and YXBZ00='1'; exception when others then LS_CYDYCS := '0'; end; begin select nvl(trim(MC0000),'0') into LS_CJFL00 from BM_TYZD00 where ZDMC00='医保追溯码配置' and BH0000='CJFL' and YXBZ00='1'; exception when others then LS_CJFL00 := '1'; end; ls_YPNM01:=trim(substrb(SF_SF_GETXMLNOTE('YPNM00',POTHER1),1,10)); if ls_YPNM01 is null then begin select A.YPNM00,B.YPMC00 into LS_YPNM00,LS_YPMC00 from YK_YPZSM0 A,BM_YD0000 B where A.YPNM00 = B.YPNM00 and A.PZBSM0 = SF_YF_GETPZBSM(PZSM000) and b.TZRQ00 is null and exists ( select 1 from YF_YPKCXX C where C.YPNM00 = A.YPNM00 and C.YFBMBH = Pczyks0 and rownum = 1 ); exception when others then Ptsxx00 := '追溯码:'||PZSM000||' 没有对应到药品标识码，请核实是否已做追溯码标识编护！'; return; end; else begin select b.YPNM00,B.YPMC00 into LS_YPNM00,LS_YPMC00 from BM_YD0000 B where b.YPNM00=to_number(ls_YPNM01) and b.TZRQ00 is null and exists ( select 1 from YF_YPKCXX C where C.YPNM00 = B.YPNM00 and C.YFBMBH = Pczyks0 and rownum = 1 ); exception when others then Ptsxx00 := '追溯码:'||PZSM000||' 没有对应到药品标识码，请核实是否已做追溯码标识编护！'; return; end; end if; select nvl(A.ZSMZHL,A.K2J000) into LS_JLZSL0 from BM_YD0000 A where A.YPNM00 = LS_YPNM00; begin select sum(nvl(YPZSL0,LS_JLZSL0)-nvl(TYZSL0,0)) into LS_YSYSL0 from YF_CFZSMX where ZSM000=PZSM000 and YPNM00=LS_YPNM00; exception when others then LS_YSYSL0:=0; end; if LS_YSYSL0>=LS_JLZSL0 then Ptsxx00 := '该药品追溯码数量已经使用完'; return; --raise Ecustom; end if; begin select A.CKDH00,A.YPQLPC into LS_CKDH00,LS_YPQLPC from VW_YF_YZZSYP A where A.QRRQ00 = PRQ0000 and A.YFBMBH = Pczyks0 and A.ZSMSL0 > A.SMSL00 and A.YPNM00 = LS_YPNM00 and exists ( select 1 from YF_YZYPSQ B where B.YPQLPC = A.YPQLPC and B.YPNM00 = A.YPNM00 and ( PBQBMBH = 0 or B.BQBMBH = PBQBMBH) and (( LS_CYDYCS <> '1' and B.YZXZ00 <> '3' ) or ( LS_CYDYCS = '1' ))) --and not exists ( select 1 from YF_YZYPSQ B where B.YPQLPC = A.YPQLPC and B.YPNM00 = A.YPNM00 and B.YZXZ00 = '3' ) and rownum = 1; exception when others then Ptsxx00 := '追溯码:'||PZSM000||' 没有对应到未扫码的出库单药品！'; return; end; select nvl(A.ZSMZHL,A.K2J000) into LS_JLZSL0 from BM_YD0000 A where A.YPNM00 = LS_YPNM00; --判断拆零库里是不是有此追溯码，有追溯码就从拆零库里取剩余数量 select count(1) into Vcount from YF_RKZSM0 A where A.ZSM000 = PZSM000 and A.SYJLSL > 0; if Vcount > 0 then select A.SYJLSL into LS_ZSMSYL from YF_RKZSM0 A where A.ZSM000 = PZSM000; if LS_ZSMSYL = 0 then Ptsxx00 := '追溯码:'||PZSM000||' 拆零库剩余数量为0,不能再出库！'; return; end if; LS_SFCLBZ := '1'; else LS_ZSMSYL := LS_JLZSL0; LS_SFCLBZ := '0'; end if; for Cur_CKMX in ( select A.CKDH00,A.YPQLPC,A.YPZSL0,(A.YPZSL0*A.ZHL000) CKJLSL,A.YPNM00 from YF_YZYPSQ A,BM_YD0000 B,YF_YPCKD0 C where A.CKDH00 = C.CKDH00 and A.YPNM00 = B.YPNM00 and A.YPNM00 = LS_YPNM00 and C.QRRQ00 = PRQ0000 and C.CKLXBH = '03' and (A.CXBZ00 = 'Z' or (A.CXBZ00='+' and A.YPZSL0 > A.ZCXSL0)) and B.LBBH00 in ('0','1') and C.YFBMBH= PCZYKS0 and ( PBQBMBH = 0 or A.BQBMBH = PBQBMBH) and (( LS_CYDYCS <> '1' and A.YZXZ00 <> '3' ) or ( LS_CYDYCS = '1' )) ) loop --验证YK_RKZSM0.YPNM00=YF_YZYPSQ.YPNM00 select nvl(max(YPNM00),0) into LS_RKYPNM from YK_RKZSM0 A where A.ZSM000 = PZSM000; if LS_RKYPNM > 0 then if LS_RKYPNM <> Cur_CKMX.YPNM00 then select YPMC00 into LS_YPMC00 from BM_YD0000 A where A.YPNM00 = Cur_CKMX.YPNM00; select YPMC00 into LS_RKYPMC from BM_YD0000 A where A.YPNM00 = LS_RKYPNM; Ptsxx00 := '扫码的药品:【'||LS_RKYPMC||'】与医嘱药品【'||LS_YPMC00||'】不是同一种药品！'; return; end if; end if; select sum(nvl(X.YPZSL0,0)) into LS_YSMSL0 from YF_CFZSMX X where X.CFLSH0 = Cur_CKMX.CKDH00 and X.YPQLPC = Cur_CKMX.YPQLPC; LS_WSMSL0 := Cur_CKMX.CKJLSL - nvl(LS_YSMSL0,0); LS_SFCL00 := mod(LS_WSMSL0,LS_JLZSL0);---余数有大于0，且最多只能操作这个数 --获取住院ID select A.ZYID00 into LS_ZYID00 from YF_YZYPSQ A where A.YPQLPC = Cur_CKMX.YPQLPC and A.CKDH00 = Cur_CKMX.Ckdh00; if LS_WSMSL0 >= LS_JLZSL0 then --未扫数量大于追溯码单位 --判断一下是不是追溯码还没被拆过 if LS_ZSMSYL = LS_JLZSL0 then insert into YF_CFZSMX(ID0000,MZZYBZ,YZLB00,CFLSH0,YPQLPC,YPNM00,ZSM000,FYTYBZ,CZY000,SFLB00,ZYID00,SFCLBZ,YPZSL0,CJFL00) Values(SQ_YF_CFZSMX_ID0000.nextval,'1','1',Cur_CKMX.CKDH00,Cur_CKMX.YPQLPC,LS_YPNM00,PZSM000,'1',Pczy000,'3',LS_ZYID00,LS_SFCLBZ,LS_JLZSL0,nvl(LS_CJFL00,'1')); LS_ZSMSYL := 0; goto cmd; --跳出循环 else if LS_SFCL00 > 0 then LS_CKSL00 := LS_ZSMSYL; if LS_ZSMSYL>LS_SFCL00 then --剩余数量大于可拆零数量，也只出可拆零数量 LS_CKSL00 := LS_SFCL00; end if; insert into YF_CFZSMX(ID0000,MZZYBZ,YZLB00,CFLSH0,YPQLPC,YPNM00,ZSM000,FYTYBZ,CZY000,SFLB00,ZYID00,SFCLBZ,YPZSL0,CJFL00) Values(SQ_YF_CFZSMX_ID0000.nextval,'1','1',Cur_CKMX.CKDH00,Cur_CKMX.YPQLPC,LS_YPNM00,PZSM000,'1',Pczy000,'3',LS_ZYID00,LS_SFCLBZ,LS_CKSL00,nvl(LS_CJFL00,'1')); LS_ZSMSYL := LS_ZSMSYL - LS_CKSL00; if LS_ZSMSYL = 0 then goto cmd; --跳出循环; end if; --continue; end if; end if; else if LS_WSMSL0>0 then /* if LS_JLZSL0 = LS_WSMSL0 then LS_SFCLBZ := 0; else LS_SFCLBZ := 1; end if;*/ LS_CKSL00 := LS_ZSMSYL; if LS_ZSMSYL>LS_WSMSL0 then --剩余数量大于可拆零数量，也只出可拆零数量 LS_CKSL00 := LS_WSMSL0; end if; insert into YF_CFZSMX(ID0000,MZZYBZ,YZLB00,CFLSH0,YPQLPC,YPNM00,ZSM000,FYTYBZ,CZY000,SFLB00,ZYID00,SFCLBZ,YPZSL0,CJFL00) Values(SQ_YF_CFZSMX_ID0000.nextval,'1','1',Cur_CKMX.CKDH00,Cur_CKMX.YPQLPC,LS_YPNM00,PZSM000,'1',Pczy000,'3',LS_ZYID00,LS_SFCLBZ,LS_CKSL00,LS_CJFL00); LS_ZSMSYL := LS_ZSMSYL - LS_CKSL00; LS_FLAG00 := '1'; if LS_ZSMSYL = 0 then goto cmd; --跳出循环; end if; end if; end if; end loop; --没匹配成功直接退出 if LS_FLAG00 = '0' then Ptsxx00 := '追溯码:'||PZSM000||' 没有匹配到未扫码的药品！'; return; end if; <> if LS_ZSMSYL = 0 then LS_ZT0000 := '2'; elsif LS_ZSMSYL < LS_JLZSL0 then LS_ZT0000 := '1'; else LS_ZT0000 := '0'; end if; if Vcount > 0 then --拆零库里取数量 update YF_RKZSM0 A set A.SYJLSL = LS_ZSMSYL,A.ZT0000 = LS_ZT0000 where A.ZSM000 = PZSM000 and A.YPNM00 = LS_YPNM00; else if LS_ZSMSYL > 0 then --生成YF_RKZSM0 insert into YF_RKZSM0(CLLSH0,YFBMBH,PYCKBH,YFRKPC,KCLSH0,YPNM00,ZSM000,ZSMLY0,JLZSL0,SYJLSL,ZT0000,CZY000) Values(SQ_YF_RKZSM0_CLLSH0.NEXTVAL,PCZYKS0,0,0,0,LS_YPNM00,PZSM000,1,LS_JLZSL0,LS_ZSMSYL,LS_ZT0000,PCZY000); end if; end if; --回写追溯码 select count(1) into Vcount from YK_RKZSM0 where ZSM000 = PZSM000; if Vcount = 0 then --获取药品的入库批次 begin select nvl(A.YKRKPC,0) into LS_YKRKPC from YF_YPCKMX A where A.CKDH00 = LS_CKDH00 and A.YPQLPC = LS_YPQLPC and YPNM00 = LS_YPNM00 and rownum = 1; exception when others then select nvl(max(A.YKRKPC),0) into LS_YKRKPC from YF_YPCKMX A where A.CKDH00 = LS_CKDH00 and YPNM00 = LS_YPNM00; end; if LS_YKRKPC > 0 then select * into V_YPRKMX from YK_YPRKMX A where A.YPRKPC = LS_YKRKPC and A.YPNM00 = LS_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 LS_SCRQ00 FROM DUAL; else LS_SCRQ00 := V_YPRKMX.SCRQ00; end if; else LS_SCRQ00 := V_YPRKMX.SCRQ00; end if; insert into YK_RKZSM0 ( YKRKPC, YPNM00, ZSM000, ZSMLY0, LYKF00, CZY000, SCPH00, SCRQ00, YXQ000, ZT0000, SFLB00 ) values ( LS_YKRKPC, LS_YPNM00, PZSM000, '6', Pczyks0, PCZY000, V_YPRKMX.Scph00, LS_SCRQ00, V_YPRKMX.YPSXRQ, '0', '3' ); end if; end if; elsif Popflag = 1 then --删除 select nvl(MAX(A.ZSID00),0) into LS_ZSID00 from YF_YZZSMX A where A.MZZYBZ = '1' and A.YZLB00 = '1' and A.ZSM000 = PZSM000 and not exists(select 1 from YF_YZZSMX where YPQLPC=A.YPQLPC and ZSM000=A.ZSM000 and ZT0000='1') and exists ( select 1 from YF_YPCKD0 N,YF_YZYPSQ X where N.CKDH00 = X.CKDH00 and (X.BQBMBH = PBQBMBH or PBQBMBH='0') and N.QRRQ00 = PRQ0000 and N.CKLXBH = '03' and N.YFBMBH=PCZYKS0 and x.YPQLPC=A.YPQLPC and N.CKDH00 = A.CFLSH0 ); if LS_ZSID00 > 0 then --判断是否已做销售出库上传 select nvl(max(A.YBJKID),0) into LS_YBJKID from YF_YBZSRZ A where A.ZSID00 = LS_ZSID00 and A.LB0000 = '4' and not exists(select 1 from YF_YZZSMX where ZSM000=PZSM000 and ZT0000='1' and ZSID00 = A.ZSID00 ) and exists ( select 1 from YF_YPCKD0 N,YF_YZYPSQ X where N.CKDH00 = X.CKDH00 and (X.BQBMBH = PBQBMBH or PBQBMBH='0') and N.QRRQ00 = PRQ0000 and N.CKLXBH = '03' and N.YFBMBH=PCZYKS0 and N.CKDH00 = A.CFLSH0 ); if LS_YBJKID > 0 then Ptsxx00 := '发药追溯码已上传,不能实行删除！'; raise Ecustom; else --删除医保追溯日志 delete YF_YZZSMX A where A.MZZYBZ = '1' and A.YZLB00 = '1' and ZSM000 = PZSM000 and exists ( select 1 from YF_YPCKD0 N,YF_YZYPSQ X where N.CKDH00 = X.CKDH00 and (X.BQBMBH = PBQBMBH or PBQBMBH='0') and N.QRRQ00 = PRQ0000 and N.CKLXBH = '03' and N.YFBMBH=PCZYKS0 and N.CKDH00 = A.CFLSH0 and X.YPQLPC=A.YPQLPC ); for cur_zsid in ( select distinct(ZSID00) ZSID00,CFLSH0 from YF_YBZSRZ A where A.ZSID00 = LS_ZSID00 and A.LB0000 = '4' and exists ( select 1 from YF_YPCKD0 N,YF_YZYPSQ X where N.CKDH00 = X.CKDH00 and (X.BQBMBH = PBQBMBH or PBQBMBH='0') and N.QRRQ00 = PRQ0000 and N.CKLXBH = '03' and N.YFBMBH=PCZYKS0 and N.CKDH00 = A.CFLSH0 )) loop select count(1) into Vcount from YF_YZZSMX A where A.MZZYBZ = '1' and A.YZLB00 = '1' and A.ZSID00 = cur_zsid.ZSID00; if Vcount = 0 then delete from YF_YBZSRZ A where A.CFLSH0 = cur_zsid.CFLSH0 and A.ZSID00 = cur_zsid.ZSID00 and A.LB0000 = '0'; end if; end loop; end if; end if; --回写到拆零库 --判断一下是否追溯码在拆零库 LS_CKSL00 select nvl(sum(YPZSL0-nvl(TYZSL0,0)),0) into LS_CKSL00 from YF_CFZSMX A where A.ZSM000 = PZSM000 and A.FYTYBZ = '1' and A.YZLB00 = '1' and not exists(select 1 from YF_YZZSMX where YPQLPC=A.YPQLPC and ZSM000=A.ZSM000 and ZT0000='1') and exists (select 1 from YF_YZYPSQ X where (X.BQBMBH = PBQBMBH or PBQBMBH='0') and X.FYRQ00 = PRQ0000 and X.CKDH00 is not null and X.YFBMBH=PCZYKS0 and x.YPQLPC=A.YPQLPC and X.YPQLPC = A.YPQLPC and ( X.CXBZ00 = 'Z' or (X.CXBZ00='+' AND X.YPZSL0>X.ZCXSL0))); select count(1) into Vcount from YF_RKZSM0 A where A.ZSM000 = PZSM000 and A.YFBMBH = PCZYKS0; if Vcount > 0 then update YF_RKZSM0 A set A.SYJLSL = A.SYJLSL + LS_CKSL00 where A.ZSM000 = PZSM000 and A.YFBMBH = PCZYKS0; else SP_YF_RKZSM0_EDIT(0,PZSM000,0,0,'0',PCZY000,PCZYKS0,'N',Pzxcgbz,Ptsxx00,'2'); update YF_RKZSM0 A set A.SYJLSL = LS_CKSL00 where A.ZSM000 = PZSM000 and A.YFBMBH = PCZYKS0; end if; delete from YF_CFZSMX A where A.ZSM000 = PZSM000 and A.FYTYBZ = '1' and A.YZLB00 = '1' and exists (select N.CKDH00 from YF_YPCKD0 N,YF_YZYPSQ X where N.CKDH00 = X.CKDH00 and (X.BQBMBH = PBQBMBH or PBQBMBH='0') and N.QRRQ00 = PRQ0000 and N.CKLXBH = '03' and N.YFBMBH=PCZYKS0 and x.YPQLPC=A.YPQLPC); elsif Popflag = 2 then --退药扫码 update YF_CFZSMX set FYTYBZ = '2',TYSMRQ=to_char(sysdate,'YYYYMMDD'),TYSMSJ = to_char(sysdate,'HH24:MI:SS') where MZZYBZ = '1' and YZLB00 = '4' and ZSM000 = PZSM000; elsif Popflag = 3 then --退药扫码删除 update YF_CFZSMX set FYTYBZ = '1',TYSMRQ=null,TYSMSJ = null where MZZYBZ = '1' and YZLB00 = '1' and ZSM000 = PZSM000; 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;