CREATE OR REPLACE PROCEDURE SP_YK_YPTJYFSY ( Pyptjpc in char, --输入：药品调价批次 Pykbmbh in number, --输入：药库部门编号 Pczy000 in number , --操作员 Pczyks0 in number , --操作员科室 Pcommit in varchar2 default 'N' , --是否提交 Pzxcgbz out number , --执行成功标志 1成功 0失败 Ptsxx00 out varchar2 , --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 out varchar2 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ) -- MODIFICATION HISTORY -- Person Date Comments -- qks 2015.06.25 药品调价生成药房调价损益 ;C_PCKCMX增加条件:and (nvl(Vsfpctj,'0')='1' and YKRKPC=VYPRKPC or nvl(Vsfpctj,'0')<>'1'); -- zhangwz 2013.03.07 增加限制药库已出库药房未入库的单据在调价时进行提示，然后不让调价. -- zhangwz 2012.10.26 解决当指定药房、药库调价时只能调价药库不能调价药房的bug. -- zhangwz 2012.09.02 SP_YK_YPTJMX_EDIT 对于新零售价和新购进价的传入参数错误,更改之. -- zhanghr 2012.07.20 modify -- lintj 2012.06.15 modify -- linshu 2021.10.13 变量Vyxbmbh长度改为varchar2(2000);(现场转归档); for YK9-20211011-001 -- jlg 2025.03.11 如果存在药房退药已审核出库，但药库未确认入库的情况下，药库调价则提示并终止 YK9-20250305-001 -- jlg 2025.05.20 如果存在药房科室借药未归还的调价范围内的药品，那提示并终止 YK9-20250415-003 as Vdlsjgl XT_XTCS00.VALUE0%type; --多零售价管理 Vtjmxpc YK_YPTJMX.TJMXPC%type; -- Vztyfjg YK_YPTJ00.ZTYFJG%type; -- Vsfpctj YK_YPTJ00.SFPCTJ%type; -- Vtjfw00 YK_YPTJ00.TJFW00%type; -- Vyxbmbh varchar2(2000); --影响部门编号 Vsfqr00 YK_YPTJ00.SFQR00%type; --是否确认 Vypnm00 YK_YPTJMX.YPNM00%type; -- Vxlsdj0 YK_YPTJMX.XLSDJ0%type; -- Vxgjdj0 YK_YPTJMX.XPFDJ0%type; -- Vsfcytj YK_YPTJMX.SFCYTJ%type; -- VYPRKPC YK_YPTJMX.YPRKPC%type; -- Vcounter number; Vzxcgbz number; Ecustom exception; cursor CUR_YPTJMX is select F.YFBMBH,T.YPNM00,T.YPMC00,T.YPGG00,T.TJDW00,T.ZHL000, round(F.SJKCSL/T.ZHL000,3) ZMKC00,round(F.SJKCSL/T.ZHL000,3) SJKC00, F.GJDJ00,T.XPFDJ0,T.GJPJDJ,F.LSDJ00,T.XLSDJ0,T.BZ0000 from YK_YPTJMX T,YF_YPKCXX F where T.SFQR00='N' and T.YPNM00=F.YPNM00 and T.YPTJPC=Pyptjpc --and ((Vtjfw00='3' and exists (select 1 from YK_YPRKMX where YPRKPC=a.YPRKPC and SFZBYP='Y')) or Vtjfw00 in ('0','2')) and instrb(','||Vyxbmbh||',',','||to_char(YFBMBH)||',')>0; cursor C_YPTJMX is select * from YK_YPTJMX where SFQR00='N' AND YPTJPC=Pyptjpc and YSBZ00='1'; cursor C_PCKCMX is select SJKCSL,LSDJ00,GJDJ00,YFBMBH,decode(YKRKPC,0,KCLSH0,YKRKPC)YKRKPC from YF_PCKCMX a where YPNM00=Vypnm00 and (LSDJ00<>Vxlsdj0 or GJDJ00<>Vxgjdj0) and instrb(','||Vyxbmbh||',',','||to_char(YFBMBH)||',')>0 and ((Vtjfw00='3' and exists (select 1 from YK_YPRKMX where YPRKPC=a.YKRKPC and SFZBYP='Y')) or Vtjfw00 in ('0','2')) and (nvl(Vsfpctj,'0')='1' and YKRKPC=VYPRKPC or nvl(Vsfpctj,'0')<>'1'); cursor C_BMBH is select BMBH00 from BM_BMBM00 where BMXZ00 in ('2','3'); begin Pxxxx00:=nvl((Pyptjpc),'null')||','||nvl(to_char(Pykbmbh),'null')||','||nvl(to_char(Pczy000),'null')||','||nvl(to_char(Pczyks0),'null')||','||nvl(Pcommit,'null'); SP_TransLog(sysdate,'SP_YK_YPTJYFSY',Pczy000,Pczyks0,Pxxxx00); select SFQR00,nvl(ZTYFJG,'N'),SFPCTJ,TJFW00,YXBMBH into Vsfqr00,Vztyfjg,Vsfpctj,Vtjfw00,Vyxbmbh from YK_YPTJ00 where YPTJPC=Pyptjpc; --如果没有传影响部门编号，默认为全院 if Vyxbmbh is null then for bm in C_BMBH loop if Vyxbmbh is null then Vyxbmbh:=to_char(bm.BMBH00); else Vyxbmbh:=Vyxbmbh||','||bm.BMBH00; end if; end loop; end if; if Vztyfjg='T' then --Y:只调整药房，N调整所有库房，T只调整药库 return; end if; if Vtjfw00 in ('1') then --0全部,1只调药典价格,2只调各批次库存价格,3只调招标药各批次库存价格; return; end if; --有效性判断 if Vsfqr00='Y' then Ptsxx00 := '该次调价已经被确认过了,不能再确认!*'; raise Ecustom; end if; --药房盘点期间不允许调价 select count(*) into Vcounter from BM_BMBM00 where FYBZ00='3' and instrb(','||Vyxbmbh||',',','||to_char(BMBH00)||',')>0; if Vcounter>0 then Ptsxx00:='盘点期间不允许调价'; raise Ecustom; end if; --药库已经出库药房未入库时不进行调价 select count(*) into Vcounter from YK_YPQLD0 a,YK_YPQLMX b where a.YPQLDH=b.YPQLDH and a.QLZT00='1' and instrb(','||Vyxbmbh||',',','||to_char(a.CKQXBH)||',')>0 and a.CKLXBH='05' and exists (select 1 from YK_YPTJMX where YPTJPC=Pyptjpc and YSBZ00='1' and YPNM00=b.YPNM00) and b.cxbz00='Z'; if Vcounter>0 then Ptsxx00:='本次调价范畴内药品有药库已经出库但是药房尚未入库的药品,请先通知药房处理后再调价'; raise Ecustom; end if; --药房退库单未确认不能进行调价 select count(*) into Vcounter from YK_YPRKD0 a,YK_YPRKMX b where a.RKDH00 = b.RKDH00 and a.RKZT00 in ('0') --0未确认 and instrb(','||Vyxbmbh||',',','||to_char(a.GHDWNM)||',')>0 and a.RKLXBH='06' --药房退库 and exists(select 1 from YK_YPTJMX where YPTJPC = Pyptjpc and YSBZ00 = '1' and YPNM00 = b.YPNM00) and b.CXBZ00 = 'Z'; if Vcounter>0 then Ptsxx00:='本次调价范畴内药品有药房退药但药库未入库确认的药品,请先处理后再调价'; raise Ecustom; end if; --药房有未归还的科室借药单不能进行调价 select count(*) into Vcounter from YF_YPCKD0 a,YF_YPCKMX b where a.CKDH00 = b.CKDH00 and a.CKZT00 = '1' --已确认 and instrb(','||Vyxbmbh||',',','||to_char(a.YFBMBH)||',')>0 and a.CKLXBH = '08' and b.jysysl<>'0' --科室借药 and a.JYSFGH = 'N' --借药未归还 and exists(select 1 from YK_YPTJMX where YPTJPC = Pyptjpc and YSBZ00 = '1' and YPNM00 = b.YPNM00) and b.CXBZ00 = 'Z'; if Vcounter>0 then Ptsxx00:='本次调价范畴内药品中有科室借药而未归还的药品,请先处理后再调价'; raise Ecustom; end if; --产生药房调价损益 delete YK_YPTJMX where YPTJPC=Pyptjpc and YXBMBH in (select BMBH00 from BM_BMBM00 where BMXZ00 in ('2','3')) and YSBZ00='0'; --取出是否多零售价管理 select nvl(max(trim(VALUE0)),'N') into Vdlsjgl from XT_XTCS00 where name00='YF_SFDLSJGL'; if Vdlsjgl='N' then Ptsxx00:=Ptsxx00; --此处暂时有问题 /* for row in CUR_YPTJMX loop Vtjmxpc := null; SP_YK_YPTJMX_EDIT( 0, --操作标志 0：增加 1：修改 2：删除 Vtjmxpc, --调价批次 Vtjmxpc, --调价明细批次 row.YFBMBH, --影响部门 row.YPNM00, --药品内码 row.YPMC00, --药品名称 row.YPGG00, --药品规格 row.TJDW00, --调价单位 row.ZHL000, --转换率 row.ZMKC00, --账面库存 row.SJKC00, --实际库存 null, --扣率 row.GJDJ00, --原批发单价 row.XPFDJ0, --现批发单价 row.GJPJDJ, --购进平均单价 row.LSDJ00, --原零售单价 row.XLSDJ0, --现零售单价 row.BZ0000, --备注 null, --最高单价 null, --操作员 null, --操作员科室 'N', --是否提交 Vzxcgbz, --执行成功标志 1成功 0失败 Ptsxx00, --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00, --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 null, '0', '1' ); end loop; */ else --药房多零售价 for tjmx in C_YPTJMX loop Vypnm00:=tjmx.YPNM00; Vxlsdj0:=tjmx.XLSDJ0; Vxgjdj0:=tjmx.XPFDJ0; VYPRKPC:=tjmx.YPRKPC; for pcmx in C_PCKCMX loop Vtjmxpc := null; SP_YK_YPTJMX_EDIT( 0, --操作标志 0：增加 1：修改 2：删除 Pyptjpc, --调价批次 Vtjmxpc, --调价明细批次 pcmx.YFBMBH, --影响部门 tjmx.YPNM00, --药品内码 tjmx.YPMC00, --药品名称 tjmx.YPGG00, --药品规格 tjmx.TJDW00, --调价单位 tjmx.ZHL000, --转换率 round(pcmx.SJKCSL/tjmx.ZHL000,3), --账面库存 round(pcmx.SJKCSL/tjmx.ZHL000,3), --实际库存 null, --扣率 pcmx.GJDJ00, --原批发单价 Vxgjdj0 , --现批发单价 tjmx.GJPJDJ, --购进平均单价 pcmx.LSDJ00, --原零售单价 Vxlsdj0 , --现零售单价 tjmx.BZ0000, --备注 null, --最高单价 null, --操作员 null, --操作员科室 'N', --是否提交 Vzxcgbz, --执行成功标志 1成功 0失败 Ptsxx00, --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00, --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 pcmx.YKRKPC, -- '0', -- '0', -- tjmx.XPFDJ1, -- tjmx.YPFDJ1 -- ); end loop; if Vsfpctj='1' then--按批调价 update YK_YPTJMX set XPFDJ0=tjmx.XPFDJ0, XLSDJ0=tjmx.XLSDJ0, SFCYTJ='1' where YPTJPC=Pyptjpc and instrb(','||Vyxbmbh||',',','||to_char(YXBMBH)||',')>0 and YSBZ00='0' and YPNM00=tjmx.YPNM00 and YPRKPC=tjmx.YPRKPC; else update YK_YPTJMX set XPFDJ0=tjmx.XPFDJ0, XLSDJ0=tjmx.XLSDJ0, SFCYTJ='1' where YPTJPC=Pyptjpc and instrb(','||Vyxbmbh||',',','||to_char(YXBMBH)||',')>0 and YSBZ00='0' and YPNM00=tjmx.YPNM00; end if; end loop; end if; exception when Ecustom then raise_application_error(-20010,substrb(Ptsxx00||'！*',1,240)); when others then raise_application_error(-20001, '生成药房药品调价损益时发生错误！*返回信息:'||'调用过程: SP_YK_YPTJYFSY('||Pyptjpc||','||to_char(pykbmbh)||') ; '||sqlerrm); end;