create or replace procedure SP_BQ_KCYKMX( -- modification history -- Person Date Comments -- chenxx 2017.01.18 create 由SP_YF_KCYKMX BQHS9.0-20170114-007 -- chenxx 2017.01.18 宁化县医院，参数BQ_SFTGZTYPJXTQ值为Y时，跳过已停止药品继续提取 BQHS9.0-20170114-007 -- fuyc 2017.10.26 一个药品（同一个目录）有多个批次，在病区提取预扣的时候，可以按有效期时间短的先预扣，请使用药房新增参数YF_PCKCYKSX BQHS9.0-20171024-001 -- linyx 2020.01.16 修改查询*改为具体字段 for BQHS9.0-20200114-001 -- linyx 2020.03.11 修改预扣库存判断表YF_KCYKMX 改为 YF_PCCKMX BQHS9.0-20200311-002 --chenzh 2021.03.02 宁化县医院，药品库存不足，替换药品充足，仍然会提示缺药 -- --------- ---------- ------------------------------------------- Popflag in number , --0 预扣 1开单退预扣 2已记账退预扣 Plydjh0 in varchar2 , --来源单据号 Pmzzybz in varchar2 , --门诊住院标志 '0'门诊 '1'住院 Pyfbmbh in number , --药房部门编号 Pypnm00 in number , --药品内码 Pcksl00 in number , --出库数量(处方单位) Pcfdw00 in varchar2 , --处方单位 Pzhl000 in number , --转换率 Pczy000 in number , --操作员 Pczyks0 in number , --操作员科室 Pcommit in varchar2 default 'N' , --是否提交 Pzxcgbz out number , --执行成功标志 1成功 0失败 Ptsxx00 out varchar2 , --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 out varchar2 , --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 Pcklsj0 out number , --出库零售价 Pckgjj0 out number , --出库购进价 Pykrkpc in number default 0 , --药库入库批次,Pykrkpc>0情况：直接指定药库入库批次进行预扣 Pdjlx00 in varchar2 default '0' , --单据类型 0门诊处方 1 住院处方 2住院医嘱 3借药单 Pzgxpc0 in varchar2 default '0' , --只更新批次 1只更新YF_PCKCMX 0更新YF_PCKCMX和YF_YPKCXX Psfgxjg in varchar2 default '1' , --是否更新价格 1更新 0不更新 Pkcbztc in varchar2 default '0' , --库存不足退出 0报错提示 1不提示直接退出 pyzid00 in number --医嘱id 判断是否同组药品 ) as Vghh000 YF_MZCF00.GHH000%type; Vkclsh0 YF_PCKCMX.KCLSH0%type; Vlsdj00 YF_PCKCMX.LSDJ00%type; Vgjdj00 YF_PCKCMX.GJDJ00%type; Vykkcsl YF_PCKCMX.YKKCSL%type; Vsycksl YF_PCKCMX.SJKCSL%type; --剩余出库数量 Vbccksl YF_PCKCMX.SJKCSL%type; --本次出库数量 Vfygz00 XT_XTCS00.VALUE0%type; Vypmc00 BM_YD0000.YPMC00%type; Vk2j000 BM_YD0000.K2J000%type; vSCPH00 YF_PCKCMX.SCPH00%type; Vsysl00 YF_PCKCMX.YKKCSL%type; Vlydjh0 YF_KCYKMX.LYDJH0%type; Vykrkpc YF_PCKCMX.YKRKPC%type; Vztbz00 YF_YPKCXX.ZTBZ00%type; Vflag00 number; Vid0000 number; Vdqrq00 char(8); Vdqsj00 char(8); Vsjkcsl YF_PCKCMX.SJKCSL%type; Vsysdate date; Vcounter number(10); --计数器变量 Ecustom exception; --错误变量 Vcfbcsfjxyk varchar2(8); --处方保存是否进行预扣 Vsftgztypjxtq varchar2(8); --是否跳过暂停药品继续提取 Vtzztbz00 YF_YPKCXX.ZTBZ00%type; -- (select nvl(max(trim(VALUE0)),'0') from XT_XTCS00 where NAME00='YF_PCKCYKSX') 药房预扣批次库存明细记录的先后顺序设置：0先入先出;1药品失效日期、先入先出排序 默认0 cursor C_PCKC00 is select YKRKPC,KCLSH0,LSDJ00,decode(sign(YKKCSL-SJKCSL),1,SJKCSL,YKKCSL) YKKCSL,GJDJ00 from YF_PCKCMX where YFBMBH=Pyfbmbh and YPNM00=Pypnm00 and decode(sign(YKKCSL-SJKCSL),1,SJKCSL,YKKCSL)>0 and SFYX00='Y' and (Pykrkpc=0 or YKRKPC=Pykrkpc) order by decode ((select nvl(max(trim(VALUE0)),'0') from XT_XTCS00 where NAME00='YF_PCKCYKSX'),'1',to_number(YPSXRQ),'0'),to_number(KCLSH0); cursor C_KCYKMX is select KCLSH0,ID0000,YKSL00,JLYKSL, ZHL000 from YF_KCYKMX where LYDJH0 = Plydjh0 and MZZYBZ = Pmzzybz and DJLX00 = Pdjlx00; cursor C_JZYKMX is select KCLSH0,YKKCSL,FLAG00 from ( select KCLSH0,sum(JLYKSL) YKKCSL,0 FLAG00 from ( select KCLSH0,JLYKSL from YF_KCYKMX t where LYDJH0=to_char(Vlydjh0) and exists(select 1 from YF_MZCF00 a,YF_MZCFMX b where a.CFLSH0=b.CFLSH0 and b.CFID00=t.LYDJH0 and CFZT00='1') and Pdjlx00='0' and DJLX00='0' union select KCLSH0,JLYKSL from YF_KCYKMX t where LYDJH0 in (select to_char(CFID00) from YF_MZCF00 a,YF_MZCFMX b where CXCFID=Vlydjh0 and GHH000=Vghh000 and a.CFLSH0=b.CFLSH0) and exists(select 1 from YF_MZCF00 a,YF_MZCFMX b where a.CFLSH0=b.CFLSH0 and b.CFID00=t.LYDJH0 and CFZT00='1') and Pdjlx00='0' and DJLX00='0' ) group by KCLSH0 union select KCLSH0,sum(JLYKSL) YKKCSL,1 FLAG00 from ( select KCLSH0,JLYKSL from YF_PCCKMX t where LYDJH0=to_char(Vlydjh0) and exists(select 1 from YF_MZCF00 a,YF_MZCFMX b where a.CFLSH0=b.CFLSH0 and b.CFID00=t.LYDJH0 and CFZT00='2') and Pdjlx00='0' and DJLX00='0' union select KCLSH0,JLYKSL from YF_PCCKMX t where LYDJH0 in (select to_char(CFID00) from YF_MZCF00 a,YF_MZCFMX b where CXCFID=Vlydjh0 and GHH000=Vghh000 and a.CFLSH0=b.CFLSH0) and exists(select 1 from YF_MZCF00 a,YF_MZCFMX b where a.CFLSH0=b.CFLSH0 and b.CFID00=t.LYDJH0 and CFZT00='2') and Pdjlx00='0' and DJLX00='0' ) group by KCLSH0 --------------------------------------------------------------------------------------------------------------------------- union select KCLSH0,sum(JLYKSL) YKKCSL,0 FLAG00 from ( select KCLSH0,JLYKSL from YF_KCYKMX t where LYDJH0=to_char(Vlydjh0) and exists(select 1 from YF_YZYPSQ where YPQLPC=t.LYDJH0 and QLZT00='1') and Pdjlx00='2' and DJLX00='2' union select KCLSH0,JLYKSL from YF_KCYKMX t where LYDJH0 in (select to_char(YPQLPC) from YF_YZYPSQ a where CXPCID=Vlydjh0 and ZYID00=Vghh000) and exists(select 1 from YF_YZYPSQ where YPQLPC=t.LYDJH0 and QLZT00='1') and Pdjlx00='2' and DJLX00='2' ) group by KCLSH0 union select KCLSH0,sum(JLYKSL) YKKCSL,1 FLAG00 from ( select KCLSH0,JLYKSL from YF_PCCKMX t where LYDJH0=to_char(Vlydjh0) and exists(select 1 from YF_YZYPSQ where YPQLPC=t.LYDJH0 and QLZT00='2') and Pdjlx00='2' and DJLX00='2' union select KCLSH0,JLYKSL from YF_PCCKMX t where LYDJH0 in (select to_char(YPQLPC) from YF_YZYPSQ a where CXPCID=Vlydjh0 and ZYID00=Vghh000) and exists(select 1 from YF_YZYPSQ where YPQLPC=t.LYDJH0 and QLZT00='2') and Pdjlx00='2' and DJLX00='2' ) group by KCLSH0 ) order by FLAG00,to_number(KCLSH0); begin Pzxcgbz := 0; Vsysdate := sysdate; Vdqrq00 := to_char(sysdate,'yyyymmdd'); Vdqsj00 := to_char(sysdate,'hh24:mi:ss'); Pxxxx00 :=nvl(to_char(Plydjh0),'null')||','||nvl(to_char(Pmzzybz),'null')||','||nvl(to_char(Pyfbmbh),'null')||','|| nvl(to_char(Pypnm00),'null')||','||nvl(to_char(Pcksl00),'null')||','||nvl(to_char(Pzhl000),'null')||','|| nvl(to_char(Pczy000),'null')||','||nvl(to_char(Pczyks0),'null')||','||nvl(to_char(Pcommit),'null'); SP_TransLog(Vsysdate,'SP_BQ_KCYKMX',Pczy000,Pczyks0,Pxxxx00); --取出多零售价管理,发药规则 select nvl(max(trim(VALUE0)),'1') into Vfygz00 from XT_XTCS00 where NAME00='YF_DLSJFYGZ'; ------------首次遍历,判断此药品出库价格--------- --判断批次预扣 select YPMC00,K2J000 into Vypmc00,Vk2j000 from BM_YD0000 where YPNM00=Pypnm00; if Popflag = 0 then --select count(*) into Vcounter from YF_KCYKMX where LYDJH0=Plydjh0 and MZZYBZ=Pmzzybz; select count(*) into Vcounter from YF_PCCKMX where LYDJH0=Plydjh0 and MZZYBZ=Pmzzybz; if Vcounter > 0 then if Pdjlx00 = '2' then update YF_YZYPSQ set SFKCBZ='0' where YPQLPC=to_number(Plydjh0); end if; Pzxcgbz := 1; return; end if; select count(*),max(ZTBZ00) into Vcounter,Vztbz00 from YF_YPKCXX where YFBMBH=Pyfbmbh and YPNM00=Pypnm00; --判断该组药品中是否存在已暂停药品 select max(ZTBZ00) into Vtzztbz00 from YF_YZYPSQ A,YF_YPKCXX B where A.YZID00 = pyzid00 and A.YFBMBH = B.YFBMBH and A.YPNM00=B.YPNM00 and a.ypqlpc=Plydjh0; select nvl(max(trim(VALUE0)),'N') into Vsftgztypjxtq from XT_XTCS00 where NAME00='BQ_SFTGZTYPJXTQ'; if Vcounter=0 then Ptsxx00 :='药品['||Trim(Vypmc00)||']在药房隶属药品库中已被删除'; raise Ecustom; end if; if Vztbz00 = '1' then Ptsxx00 :='药品['||Trim(Vypmc00)||']已暂停使用'; if Vsftgztypjxtq = 'Y' then return; else raise Ecustom; end if; end if; if Vsftgztypjxtq = 'Y' and Vtzztbz00 = '1' then return; end if; if Pykrkpc >0 then select max(SCPH00) into Vscph00 from YF_PCKCMX where YFBMBH=Pyfbmbh and YPNM00=Pypnm00 and SFYX00='Y' and YKRKPC=Pykrkpc; Vscph00 := '(批号:'||vSCPH00|| ')'; else Vscph00 := ''; end if; select nvl(sum(SJKCSL),0),nvl(sum(least(YKKCSL,SJKCSL)),0) into Vsjkcsl,Vykkcsl from YF_PCKCMX where YFBMBH=Pyfbmbh and YPNM00=Pypnm00 and SFYX00='Y' and (Pykrkpc=0 or YKRKPC=Pykrkpc); if Vsjkcsl0 loop if Vsycksl-Vykkcsl <=0 then --本批次够出剩余出库量 Vbccksl:=Vsycksl; Vsycksl:=0; else Vbccksl:=Vykkcsl; Vsycksl:=Vsycksl-Vbccksl; end if; if Vfygz00='1' then --以低价格出库 if Vlsdj00Pcklsj0 then Pcklsj0:=Vlsdj00; Pckgjj0:=Vgjdj00; end if; end if; Vsysl00 := Vykkcsl - Vbccksl; fetch C_PCKC00 into Vykrkpc,Vkclsh0,Vlsdj00,Vykkcsl,Vgjdj00; end loop; close C_PCKC00; Pcklsj0:=round(Pcklsj0*Pzhl000/Vk2j000,4); Pckgjj0:=round(Pckgjj0*Pzhl000/Vk2j000,4); if Psfgxjg = '1' then if Pdjlx00 = '0' then update YF_MZCFMX a set LSDJ00=Pcklsj0, GJDJ00=Pckgjj0 where CFID00=Plydjh0 and not exists(select 1 from YF_MZCF00 where CFLSH0=a.CFLSH0 and CFZT00='1'); end if; if Pdjlx00 = '2' then update YF_YZYPSQ set LSDJ00=Pcklsj0, GJDJ00=Pckgjj0, SFKCBZ='0' where YPQLPC=to_number(Plydjh0); end if; end if; Pzxcgbz:=1; if pcommit='Y' then commit; end if; return; end if; while C_PCKC00%found and Vsycksl>0 loop if Vsycksl-Vykkcsl <=0 then --本批次够出剩余出库量 Vbccksl:=Vsycksl; Vsycksl:=0; else Vbccksl:=Vykkcsl; Vsycksl:=Vsycksl-Vbccksl; end if; if Vfygz00='1' then --以低价格出库 if Vlsdj00Pcklsj0 then Pcklsj0:=Vlsdj00; Pckgjj0:=Vgjdj00; end if; end if; update YF_PCKCMX set YKKCSL=YKKCSL-Vbccksl,ZHXGRQ=Vdqrq00,ZHXGSJ=Vdqsj00 where KCLSH0=Vkclsh0; Vsysl00 := Vykkcsl - Vbccksl; select SQ_YF_KCYKMX_ID0000.nextval into Vid0000 from dual; insert into YF_KCYKMX(ID0000, DJLX00, MZZYBZ, LYDJH0, YKSL00, JLYKSL, YKDW00, ZHL000, SYSL00, LSDJ00, GJDJ00, YFBMBH, YPNM00, KCLSH0, YKRKPC, YKRQ00, YKSJ00) values(Vid0000, Pdjlx00, Pmzzybz, Plydjh0, round(Vbccksl/Pzhl000,3), Vbccksl, Pcfdw00, Pzhl000, Vsysl00, round(Vlsdj00*Pzhl000/Vk2j000,4), round(Vgjdj00*Pzhl000/Vk2j000,4), Pyfbmbh, Pypnm00, Vkclsh0, Vykrkpc, Vdqrq00, Vdqsj00); insert into YF_PCCKMX(ID0000, DJLX00, MZZYBZ, LYDJH0, YKSL00, JLYKSL, YKDW00, ZHL000, SYSL00, LSDJ00, GJDJ00, YFBMBH, YPNM00, KCLSH0, YKRKPC, YKRQ00, YKSJ00) values(Vid0000, Pdjlx00, Pmzzybz, Plydjh0, round(Vbccksl/Pzhl000,3), Vbccksl, Pcfdw00, Pzhl000, Vsysl00, round(Vlsdj00*Pzhl000/Vk2j000,4), round(Vgjdj00*Pzhl000/Vk2j000,4), Pyfbmbh, Pypnm00, Vkclsh0, Vykrkpc, Vdqrq00, Vdqsj00); fetch C_PCKC00 into Vykrkpc,Vkclsh0,Vlsdj00,Vykkcsl,Vgjdj00; end loop; close C_PCKC00; if Pzgxpc0 = '0' then --update YF_YPKCXX SET YKKCSL=nvl(YKKCSL,0)-round(Pcksl00*Pzhl000,3), LSDJ00=Pcklsj0 where YFBMBH=Pyfbmbh and YPNM00=Pypnm00; update YF_YPKCXX SET LSDJ00=Pcklsj0,GJDJ00=Pckgjj0 where YFBMBH=Pyfbmbh and YPNM00=Pypnm00; end if; Pcklsj0:=round(Pcklsj0*Pzhl000/Vk2j000,4); Pckgjj0:=round(Pckgjj0*Pzhl000/Vk2j000,4); if Psfgxjg = '1' then if Pdjlx00 = '0' then update YF_MZCFMX a set LSDJ00=Pcklsj0, GJDJ00=Pckgjj0 where CFID00=Plydjh0 and not exists(select 1 from YF_MZCF00 where CFLSH0=a.CFLSH0 and CFZT00='1'); end if; if Pdjlx00 = '2' then update YF_YZYPSQ set LSDJ00=Pcklsj0, GJDJ00=Pckgjj0, SFKCBZ='0' where YPQLPC=to_number(Plydjh0); end if; end if; end if; if Popflag = 1 then update YF_YPKCXX SET YKKCSL=nvl(YKKCSL,0)+round(Pcksl00*Pzhl000,3) where YFBMBH=Pyfbmbh and YPNM00=Pypnm00; Vykkcsl :=round(Pcksl00*Pzhl000,3); for row in C_KCYKMX loop if row.JLYKSL <= Vykkcsl then update YF_PCKCMX set YKKCSL=YKKCSL+row.JLYKSL,ZHXGRQ=Vdqrq00,ZHXGSJ=Vdqsj00 where KCLSH0=row.KCLSH0; delete from YF_KCYKMX where ID0000=row.ID0000; delete from YF_PCCKMX where ID0000=row.ID0000; Vykkcsl := Vykkcsl - row.JLYKSL; else update YF_PCKCMX set YKKCSL=YKKCSL+Vykkcsl,ZHXGRQ=Vdqrq00,ZHXGSJ=Vdqsj00 where KCLSH0=row.KCLSH0; update YF_KCYKMX set YKSL00=round((round(row.YKSL00*row.ZHL000,3)-Vykkcsl)/ZHL000,4), JLYKSL=row.JLYKSL-Vykkcsl where ID0000=row.ID0000; Vykkcsl := 0; end if; exit when Vykkcsl = 0; end loop; /* if Pdjlx00 = '2' then update YF_YZYPSQ set SFKCBZ='1' where YPQLPC=to_number(Plydjh0); end if; */ end if; if Popflag = 2 then if Pdjlx00 = '0' then select to_char(CXCFID),GHH000 into Vlydjh0,Vghh000 from YF_MZCFMX a,YF_MZCF00 b where a.CFID00=to_number(Plydjh0) and a.CFLSH0=b.CFLSH0; end if; if Pdjlx00 = '2' then select to_char(CXPCID),ZYID00 into Vlydjh0,Vghh000 from YF_YZYPSQ where YPQLPC=to_number(Plydjh0); end if; update YF_YPKCXX SET YKKCSL=nvl(YKKCSL,0)-round(Pcksl00*Pzhl000,3) where YFBMBH=Pyfbmbh and YPNM00=Pypnm00; Vsycksl:=abs(round(Pcksl00*Pzhl000,3)); open C_JZYKMX; fetch C_JZYKMX into Vkclsh0, Vykkcsl, Vflag00; while C_JZYKMX%found and Vsycksl>0 loop if Vsycksl-Vykkcsl <=0 then --本批次够出剩余出库量 Vbccksl:=Vsycksl; Vsycksl:=0; else Vbccksl:=Vykkcsl; Vsycksl:=Vsycksl-Vbccksl; end if; select nvl(YKKCSL,0) into Vykkcsl from YF_PCKCMX where KCLSH0=Vkclsh0; Vsysl00 := Vykkcsl + Vbccksl; update YF_PCKCMX set YKKCSL=YKKCSL+Vbccksl,ZHXGRQ=Vdqrq00,ZHXGSJ=Vdqsj00 where KCLSH0=Vkclsh0; if Vflag00 = 0 then select ID0000 into Vid0000 from YF_KCYKMX where LYDJH0=Vlydjh0 and KCLSH0=Vkclsh0 and DJLX00=pdjlx00 and rownum=1; insert into YF_KCYKMX(ID0000, DJLX00, MZZYBZ, LYDJH0, YKSL00, JLYKSL, YKDW00, ZHL000, SYSL00, LSDJ00, GJDJ00, YFBMBH, YPNM00, KCLSH0, YKRKPC, YKRQ00, YKSJ00) select SQ_YF_KCYKMX_ID0000.nextval, Pdjlx00, Pmzzybz, Plydjh0, round(-Vbccksl/Pzhl000,3), -Vbccksl, YKDW00, Pzhl000, Vsysl00, LSDJ00, GJDJ00, YFBMBH, YPNM00, Vkclsh0, YKRKPC, Vdqrq00, Vdqsj00 from YF_KCYKMX where ID0000=Vid0000; delete from YF_PCCKMX where LYDJH0=Vlydjh0 and KCLSH0=Vkclsh0 and DJLX00=pdjlx00; else select ID0000 into Vid0000 from YF_PCCKMX where LYDJH0=Vlydjh0 and KCLSH0=Vkclsh0 and DJLX00=pdjlx00 and rownum=1; insert into YF_PCCKMX(ID0000, DJLX00, MZZYBZ, LYDJH0, YKSL00, JLYKSL, YKDW00, ZHL000, SYSL00, LSDJ00, GJDJ00, YFBMBH, YPNM00, KCLSH0, YKRKPC, YKRQ00, YKSJ00) select SQ_YF_KCYKMX_ID0000.nextval, Pdjlx00, Pmzzybz, Plydjh0, round(-Vbccksl/Pzhl000,3), -Vbccksl, YKDW00, Pzhl000, Vsysl00, LSDJ00, GJDJ00, YFBMBH, YPNM00, Vkclsh0, YKRKPC, Vdqrq00, Vdqsj00 from YF_PCCKMX where ID0000=Vid0000; delete from YF_KCYKMX where LYDJH0=Vlydjh0 and KCLSH0=Vkclsh0 and DJLX00=pdjlx00; end if; fetch C_JZYKMX into Vkclsh0, Vykkcsl, Vflag00; 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;