create or replace procedure SP_YF_YBZSRZ_EDIT -- modification history -- Person Date Comments -- linshu 2024.06.05 create 医保追溯码日志操作; for YF9-20240607-001 -- linshu 2024.06.12 增加扫码退药功能; for YF9-20240611-002 -- linshu 2024.06.20 通用字典去空取参数值; for YF9-20240620-001 -- linshu 2024.06.26 限制退药医保追溯码日志数据重复; for YF9-20240626-002 -- linshu 2024.06.27 修正了判断是否已经写了医保退药上传日志条件BUG; for YF9-20240627-002 -- linshu 2024.06.28 修正了医保发药上传日志条件; for YF9-20240621-001 -- linsnu 2024.07.02 1.已有处方同类别未上传时改为追加数据 2.发药时修改YF_CFZSMX.FYTYBZ=1; for YF9-20240703-001 -- linshu 2024.12.19 没有扫码的药品也生成ZSM000=0 的医保上传数据; for YF9-20241218-002 -- linshu 2025.02.26 修正了处方全部都是零散处方药品不能生成医保上传日志; for YF9-20250227-001 -- linshu 2025.02.27 正了对处方里面全部都是零散药品不能生成医保上传日志; for YF9-20250307-001 -- linshu 2025.04.08 拆零写数量; for YF9-20250408-001 -- linshu 2025.04.14 1.生成没扫码的拆零数据有参数控制 2.调整了门诊退药生成数据; for YF9-20250411-005 -- linshu 2025.04.17 增加了住院退药功能; for YF9-20250414-005 -- linshu 2025.04.18 修正了住院退药YF_TYZSMX无数据报错问题; for YF9-20250418-003 -- linshu 2025.04.23 共享药房发药处方YF_YBZSRZ.LB0000 = G; for YF9-20250423-001 -- linshu 2025.04.27 修正了住院退药时报错问题; for YF9-20250427-001 -- linshu 2025.05.08 修正了病区扫码追溯码重复上传; for YF9-20250508-003 -- linshu 2025.05.15 限制只有发药确认或退药确认后才能生成YF_YBZSRZ; for YF9-20250515-001 -- linshu 2024.05.28 科室领药记账时就可以生成医保追溯码上传数据；for YF9-20250528-004 -- linshu 2025.06.06 1.共享处方退药 2.医保病人未结算 不生成YF_YBZSRZ; for YF9-20250604-003 -- linshu 2025.08.21 医保病人结算后才能生成医保追溯日志剔除掉共享处方 ( POPFLAG in number , --操作标志 0：发药 1：退药 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 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ) as --Vsysdate date; --日期变量 Vcounter number(5); Ecustom exception; V_BRXM00 BM_BRXXB0.BRXM00%type; V_ZYGHID number(10); --V_YZLB00 varchar2(2); VZSID00 YF_YBZSRZ.ZSID00%type; LS_ZSMQY BM_TYZD00.MC0000%type; V_CFLSH0 YF_YBZSRZ.CFLSH0%type; V_LB0000 YF_YBZSRZ.LB0000%type; V_CZYKS0 YF_MZCF00.YFBMBH%type; V_YPDLBH YF_MZCF00.YPDLBH%type; V_SCWSM0 BM_TYZD00.MC0000%type; V_CFZT00 YF_MZCF00.CFZT00%type; V_SFKSLY YF_MZCF00.SFKSLY%type; V_FBBH00 SF_BRXXB0.FBBH00%type; V_BRID00 SF_BRXXB0.BRID00%type; begin Pzxcgbz := 0; --Vsysdate:=sysdate; begin select nvl(trim(MC0000),'0') into LS_ZSMQY from BM_TYZD00 where ZDMC00='医保追溯码配置' and BH0000='YBZSMSFQY' and YXBZ00='1'; exception when others then LS_ZSMQY:='0'; end; if LS_ZSMQY='0' then Pzxcgbz := '1'; return; end if ; begin select nvl(trim(MC0000),'0') into V_SCWSM0 from BM_TYZD00 where ZDMC00='医保追溯码配置' and BH0000='SCWSMCLSJ' and YXBZ00='1'; exception when others then V_SCWSM0 := '0'; end; /* Pxxxx00 :=nvl(to_char(POPFLAG),'null')||nvl(to_char(Pcflsh0),'null')||','||nvl(to_char(PCZY000),'null')||','||nvl(to_char(Pcommit),'null'); SP_TransLog(Vsysdate,'SP_YF_YBZSRZ_EDIT',PCZY000,V_CZYKS0,Pxxxx00);*/ if PMZZYBZ = '0' then if POPFLAG = '0' then --门诊发药 select A.YFBMBH,A.YPDLBH,A.CFZT00,nvl(A.SFKSLY,'N'),B.FBBH00,B.BRID00 into V_CZYKS0,V_YPDLBH,V_CFZT00,V_SFKSLY,V_FBBH00,V_BRID00 from YF_MZCF00 A,SF_BRXXB0 B where A.GHID00 = B.GHID00 and A.CFLSH0 = PCFLSH0; if V_YPDLBH = '2' then --中药不产生医保上传数据 Pzxcgbz := '1'; return; end if; if ( V_CFZT00 <> '2' and V_SFKSLY = 'N' ) then --非科室领药的要发药确认后才生成 Pzxcgbz := '1'; return; end if; if V_SFKSLY = 'Y' and ( V_CFZT00 not in ('1','2') ) then --科室领药记账、发药后都可以生成 Pzxcgbz := '1'; return; end if; select BRXM00,GHID00,decode(nvl(WGCFBZ,'0'),'3','G','0') into V_BRXM00,V_ZYGHID,V_LB0000 from YF_MZCF00 where CFLSH0 = PCFLSH0; --医保病人结算后才能生成医保追溯日志 if ( nvl(trim(SF_XT_TYZDMC('医保追溯码配置','YF_YBJSHCSCZSRZ')),'0') = '1') and ( V_FBBH00 = '3') and ( V_LB0000 <> 'G' ) then select count(1) into Vcounter from SF_FYMX00 K where K.BRID00 = V_BRID00 and K.YJDJH0 = PCFLSH0 and K.JZDH00 > 0; if Vcounter = '0' then /* PTSXX00 := '医保病人未结算不能生成医保追溯码待上传数据！'; raise Ecustom;*/ Pzxcgbz := '1'; return; end if; end if; --沙县共享处方增加修改点 select count(1) into Vcounter from YF_MZCFMX A,BM_YD0000 B where A.ypnm00 = B.ypnm00 --and nvl(B.zsclbz,'0') in ('1','3') and A.CFLSH0 = PCFLSH0 and nvl(XSWZSM,'0') <> '1' and exists ( select 1 from YK_YPZSM0 C WHERE C.YPNM00 = B.YPNM00 ); if Vcounter = 0 then Pzxcgbz := '1'; return; end if; --没有可生成的码 select count(1) into Vcounter from YF_CFZSMX A where A.CFLSH0 = PCFLSH0 and SCZT00 = '0' and YZLB00 = '0' and not exists ( select 1 from YF_YZZSMX B where B.CFLSH0 = A.CFLSH0 and B.YZLB00 = '0' and B.ZSM000 = A.ZSM000 ); if Vcounter=0 then Pzxcgbz := '1'; return; end if; --上移 -- select BRXM00,GHID00,decode(nvl(WGCFBZ,'0'),'3','G','0') into V_BRXM00,V_ZYGHID,V_LB0000 from YF_MZCF00 where CFLSH0 = PCFLSH0; V_CFLSH0 := PCFLSH0; --V_LB0000 := '0'; --共享处方要全部扫码完成 if V_LB0000 = 'G' then select count(1) into Vcounter from (select X.CFLSH0,X.YPZSL0,( select nvl(sum(K.YPZSL0),0)/X.ZHL000 from YF_CFZSMX k where k.CFLSH0 = X.CFLSH0 and k.YPNM00 = X.YPNM00 and K.YPQLPC = X.CFID00 ) SMSL00 from YF_MZCFMX X,BM_YD0000 B where X.YPNM00 = B.YPNM00 and X.SFZBY0 <> 'Y' and exists( select 1 from YK_YPZSM0 C where C.YPNM00 = X.YPNM00 ) and nvl(XSWZSM,'0') <> '1' ) where YPZSL0 > SMSL00 and CFLSH0 = PCFLSH0; if Vcounter > 0 then return; end if; end if; --共享处方要全部扫码完成END elsif POPFLAG = '1' then select BRXM00,GHID00,BCCFH0,CFZT00 into V_BRXM00,V_ZYGHID,V_CFLSH0,V_CFZT00 from YF_MZCF00 where CFLSH0 = PCFLSH0; select count(1) into Vcounter from YF_CFZSMX where CFLSH0 = V_CFLSH0 and TYCFH0 = PCFLSH0; if ( Vcounter = 0 ) or ( V_CFZT00 not in ('5','6')) then Pzxcgbz := '1'; return; end if; --判断是否已经写了医保退药上传日志 select count(1) into Vcounter from YF_TYZSMX A where A.CXDH00 = V_CFLSH0 and A.CFLSH0 = PCFLSH0 and A.MZZYBZ = '0' and YZLB00 = '0' and not exists ( select 1 from YF_YZZSMX B,YF_YBZSRZ C where B.ZSID00 = C.ZSID00 and B.MZZYBZ = '0' and B.CFLSH0 = V_CFLSH0 and c.lb0000 = '1' and B.ZSM000 = A.ZSM000 ); if Vcounter = 0 then Pzxcgbz := '1'; return; end if; V_LB0000 := '1'; end if; begin select ZSID00 into vZSID00 from YF_YBZSRZ where MZZYBZ=PMZZYBZ and CFLSH0=V_CFLSH0 and LB0000=V_LB0000 and ZT0000='0'; exception when others then vZSID00 := 0; end; if vZSID00 = 0 then select SQ_YF_YBZSRZ_ZSID00.NEXTVAL into vZSID00 from dual; insert into YF_YBZSRZ(ZSID00,MZZYBZ,CFLSH0,BRXM00,ZYGHID,LB0000,ZT0000) values(vZSID00,PMZZYBZ,V_CFLSH0,V_BRXM00,V_ZYGHID,V_LB0000,'0'); end if; if POPFLAG = '0' then update YF_CFZSMX A set FYTYBZ = '1' where a.CFLSH0=PCFLSH0 and a.SCZT00 = '0' and A.MZZYBZ = '0' and FYTYBZ = '0' and not exists ( select 1 from YF_YZZSMX B where B.CFLSH0 = A.CFLSH0 and B.MZZYBZ = '0' and B.ZSM000 = A.ZSM000 ); --删除追溯码=0 --delete from YF_YZZSMX A where A.ZSID00 = vZSID00 and A.CFLSH0 = PCFLSH0 and A.MZZYBZ = '0' and A.ZSM000 = '0' and A.YZLB00 = '0' and A.ZT0000 = '0'; insert into YF_YZZSMX (ZSID00,MZZYBZ,YZLB00,CFLSH0,YPQLPC,ID0000,YPNM00,ZSM000,CZY000,ZT0000,YPZSL0) select vZSID00,a.MZZYBZ,a.YZLB00,a.CFLSH0,a.YPQLPC,SQ_YF_YZZSMX_ID0000.nextval,a.YPNM00,a.ZSM000,a.CZY000,'0',A.YPZSL0 from YF_CFZSMX A where a.CFLSH0=PCFLSH0 and a.SCZT00 = '0' and A.MZZYBZ = '0' and FYTYBZ = '1' and not exists ( select 1 from YF_YZZSMX B where B.CFLSH0 = A.CFLSH0 and B.MZZYBZ = '0' and B.ZSM000 = A.ZSM000 ); if V_SCWSM0 = '1' then --生成没扫码的拆零数据 insert into YF_YZZSMX (ZSID00,MZZYBZ,YZLB00,CFLSH0,YPQLPC,ID0000,YPNM00,ZSM000,CZY000,ZT0000) select vZSID00,'0','0',a.CFLSH0,A.CFID00,SQ_YF_YZZSMX_ID0000.nextval,a.YPNM00,'0',PCZY000,'0' from YF_MZCFMX A where A.CFLSH0 = PCFLSH0 and A.SFZBY0 <> 'Y' and A.CFID00 not in ( select B.YPQLPC from YF_YZZSMX B,YF_YBZSRZ C where B.ZSID00 = C.ZSID00 and C.LB0000 = '0' and C.CFLSH0 = PCFLSH0 and B.MZZYBZ = '0' and B.ZSM000 <> '0') and exists ( select 1 from YK_YPZSM0 C where c.ypnm00 = a.ypnm00 ) and not exists ( select 1 from YF_YZZSMX B where B.CFLSH0 = A.CFLSH0 and B.MZZYBZ = '0' and B.YPQLPC = A.CFID00 ); end if; elsif POPFLAG = '1' then /* insert into YF_YZZSMX (ZSID00,MZZYBZ,YZLB00,CFLSH0,YPQLPC,ID0000,YPNM00,ZSM000,CZY000,ZT0000,YPZSL0) select vZSID00,a.MZZYBZ,a.YZLB00,a.CFLSH0,a.YPQLPC,SQ_YF_YZZSMX_ID0000.nextval,a.YPNM00,a.ZSM000,a.CZY000,'0',A.YPZSL0 from YF_CFZSMX a where a.CFLSH0 = V_CFLSH0 and A.TYCFH0 = PCFLSH0 and A.MZZYBZ = '0' and FYTYBZ = '2' and YZLB00 = '0' and not exists ( select 1 from YF_YZZSMX B,YF_YBZSRZ C where B.ZSID00 = C.ZSID00 and B.MZZYBZ = '0' and B.CFLSH0 = V_CFLSH0 and c.lb0000 = '1' and B.ZSM000 = A.ZSM000 );*/ insert into YF_YZZSMX (ZSID00,MZZYBZ,YZLB00,CFLSH0,YPQLPC,ID0000,YPNM00,ZSM000,CZY000,ZT0000,YPZSL0) select vZSID00,a.MZZYBZ,a.YZLB00,a.CXDH00,a.CXID00,SQ_YF_YZZSMX_ID0000.nextval,a.YPNM00,a.ZSM000,a.CZY000,'0',A.TYZSL0 from YF_TYZSMX a where a.CFLSH0 = PCFLSH0 and A.CXDH00 = V_CFLSH0 and A.MZZYBZ = '0' and YZLB00 = '0' and not exists ( select 1 from YF_YZZSMX B,YF_YBZSRZ C where B.ZSID00 = C.ZSID00 and B.MZZYBZ = '0' and B.CFLSH0 = V_CFLSH0 and c.lb0000 = '1' and B.ZSM000 = A.ZSM000 ); end if; --住院退药 elsif PMZZYBZ = '1' then if POPFLAG = '1' then select count(1) into Vcounter from YF_TYZSMX B where B.CFLSH0 = PCFLSH0 and CXDH00 = V_CFLSH0; if Vcounter = 0 then Pzxcgbz := '1'; return; end if; select A.BRXM00,A.ZYID00,B.CXDH00 into V_BRXM00,V_ZYGHID,V_CFLSH0 from YF_YZYPSQ A,YF_TYZSMX B where A.CKDH00 = B.CFLSH0 and A.YPQLPC = B.YPQLPC and B.CFLSH0 = PCFLSH0 and rownum = 1; --判断是否已经写了医保退药上传日志 select count(1) into Vcounter from YF_TYZSMX A where A.CXDH00 = V_CFLSH0 and A.CFLSH0 = PCFLSH0 and A.MZZYBZ = '1' and YZLB00 = '1' and not exists ( select 1 from YF_YZZSMX B,YF_YBZSRZ C where B.ZSID00 = C.ZSID00 and B.MZZYBZ = '1' and B.CFLSH0 = V_CFLSH0 and c.lb0000 = '1' and B.ZSM000 = A.ZSM000 ); if Vcounter = 0 then Pzxcgbz := '1'; return; end if; begin select ZSID00 into vZSID00 from YF_YBZSRZ where MZZYBZ='1' and CFLSH0=V_CFLSH0 and LB0000='5' and ZT0000='0'; exception when others then vZSID00 := 0; end; if vZSID00 = 0 then select SQ_YF_YBZSRZ_ZSID00.NEXTVAL into vZSID00 from dual; insert into YF_YBZSRZ(ZSID00,MZZYBZ,CFLSH0,BRXM00,ZYGHID,LB0000,ZT0000) values(vZSID00,'1',V_CFLSH0,V_BRXM00,V_ZYGHID,'5','0'); end if; insert into YF_YZZSMX (ZSID00,MZZYBZ,YZLB00,CFLSH0,YPQLPC,ID0000,YPNM00,ZSM000,CZY000,ZT0000,YPZSL0) select vZSID00,a.MZZYBZ,a.YZLB00,a.CXDH00,a.CXID00,SQ_YF_YZZSMX_ID0000.nextval,a.YPNM00,a.ZSM000,a.CZY000,'0',A.TYZSL0 from YF_TYZSMX a where a.CFLSH0 = PCFLSH0 and A.CXDH00 = V_CFLSH0 and A.MZZYBZ = '1' and A.YZLB00 = '1' and not exists ( select 1 from YF_YZZSMX B,YF_YBZSRZ C where B.ZSID00 = C.ZSID00 and B.MZZYBZ = '1' and B.CFLSH0 = V_CFLSH0 and c.lb0000 = '5' and B.ZSM000 = A.ZSM000 ); end if; end if; --清理没有明细的主表数据YF_YBZSRZ select count(1) into Vcounter from YF_YZZSMX where ZSID00 = vZSID00; if Vcounter = 0 then delete from YF_YBZSRZ where ZSID00 = vZSID00; end if; if Popflag = 0 then SP_YF_RKZSM0_BACK( '0', PCFLSH0, PCZY000, pcommit, Pzxcgbz, Ptsxx00, Pxxxx00, V_CZYKS0 ); 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;