CREATE OR REPLACE PROCEDURE SP_YK_YPRKD0_AUDIT -- MODIFICATION HISTORY -- Person Date Comments -- lintj 2012.05.09 create -- lintj 2012.07.02 增加计量入库相关信息 -- Laisl 2012.10.06 修改确认的状态Vrkzt00，原先为4改为0 -- zhangwz 2012.11.16 对BM_YD0000.GJPJDJ的update GJPJDJ=decode(GJZSL0,0,0,round(GJZJE0/GJZSL0,4)) 变更成 GJPJDJ=decode(GJZSL0,0,0,round(GJZJE0*K2J000/GJZSL0,4)). -- qks 2012.11.19 新增参数 YF_YPDBDQR：药房退库单输入后,出库单是否由药库一起确认；同批不同价的退库单确认是，不跟原单进行合并。 -- zhangwz 2012.11.21 入库确认时增加对 YK_YPRKMX.SYRQSJ 进行修改。 -- zhangwz 2012.11.23 V_CKDH00 的定义由 YF_YPCKD0.CKDH00 变更为 YK_YPRKD0.BZ0000 . -- qks 2012.12.21 暂不判断：退库后批次库存不能大于本批次入库数量，请修改退库数量 -- qks 2013.09.26 VW_YK_GHDW00-->VW_YK_KSGHDW -- qks 2013.10.08 库存物资:"退费入库"入库类型(bm_ykrklx.RKLXBH='31') -- qks 2013.10.14 库存物资:bm_ykrklx.RKLXBH in ('06','31'),写YK_YPMXZ0,使用负数出库方式处理; -- qks 2013.10.15 库存物资:科室物资退库确认减少科室库存; -- qks 2013.10.29 库存物资:科室物资退库确认不调用SP_YF_YPCKD0_AUDIT -- qks 2014.04.21 库存物资:检验科当二级库房管理 -- qks 2014.06.10 库存物资:供应室仓库科室三级库存退库,ZY0000=ZY0000||(科室); -- qks 2016.03.11 保存到账页表中,重取结存批发金额(量采价); by YK9-20160313-003 -- qks 2016.12.14 福清市医院反馈：药房录入的退库单,药库退款确认后,没有减少药房库存问题; by YK9-20161215-001 -- qks 2017.05.19 退库部门属于“非药房”科室退药，进行“退库单确认”时会报错; by YK9-20170519-001 -- liwm 2018.02.28 药库入库添加消息通知 for YK9-20180124-001 -- liwm 2018.05.22 修改药库入库单确认时消息发送机制 for YK9-20180517-002 -- qks 2019.06.13 修正bug:购进单价为0导致修改药典表加成率出错; for KCWZ9-20190612-001 -- qks 2019.09.24 参数YF_YPDBDQR功能扩展,增加了M值：由药房调出科室和退到药库一起确认; for YF9-20190919-001 -- qks 2019.11.21 新增参数WZ_WZDJTBSF控制：物资入库确认,是否同步修改物资字典及对应耗材收费字典的单价，1是 0否,默认0; for KCWZ9-20191024-002 -- qks 2019.12.19 由于HIS5升级HIS9,库存物资期初建账入库单确认速度很慢,现改为库存为0明细入库记录不生成明细账; for YK9-20191219-001 -- qks 2020.01.17 退库：如果属于不同科室,则不增加原批次库存; for YK9-20200117-001 -- qks 2020.02.05 当参数WZ_WZDJTBSF启用时，要求同步修改耗材收费字典的收费上限和下限金额; for KCWZ9-20191221-001 -- qks 2020.02.12 退库单的购进单价跟原入库批次的购进单价不等,则不增加原批次库存; for YK9-20200212-002 -- luowf 2020.04.26 退库药品与原药品不相同，请重新输入退库单!*提示增加显示药品、规格、编码 for YK9-20200420-001 -- qks 2020.04.28 修正：如果上线前药房库存是通过盘点入库,进行退库确认会报错; for YK9-20200427-001 -- linshu 2021.08.11 修正：取固定加成率参数YK_YPJCL0_DEFAULT开启的情况下，不允许在确认时修改加成率 by YK9-20210716-001 ( Prkdh00 in varchar2 , --入库单号 Pykbmbh in number , --药库部门编号 Pqrrq00 in char , --确认日期 Pqrsj00 in char , --确认时间 Psfrz00 in char default 'N' , --是否入账 Pczy000 in number , --操作员 Pczyks0 in number , --操作员科室 Pcommit in varchar2 default 'N' , --是否提交 Pzxcgbz out number , --执行成功标志 1成功 0失败 Ptsxx00 out varchar2 , --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 out varchar2 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ) as Vsysdate date; --日期变量 Vcounter number(14,4); --计数器变量 Vrcksh0 XT_XTCS00.VALUE0%type; Vzrjrcj XT_XTCS00.VALUE0%type; Vykdjgl XT_XTCS00.VALUE0%type; Vsfgxyd XT_XTCS00.VALUE0%type; Vzjypls XT_XTCS00.VALUE0%type; Vrkzt00 YK_YPRKD0.RKZT00%type; --入库状态 Vrklxbh YK_YPRKD0.RKLXBH%type; Vzkje00 YK_YPRKD0.ZKJE00%type; Vghdwnm YK_YPRKD0.GHDWNM%type; Vzhl000 YK_YPRKMX.ZHL000%type; Vjlzhl0 YK_YPRKMX.JLZHL0%type; Vrksl00 BM_YD0000.KCSL00%TYPE; Vpckcsl BM_YD0000.KCSL00%TYPE; Vypnm01 BM_YD0000.YPNM00%TYPE; Vypnm02 BM_YD0000.YPNM00%TYPE; Vkcsl00 BM_YD0000.KCSL00%TYPE; Vkcjlsl YF_YPKCXX.SJKCSL%TYPE; Vkcje00 BM_YD0000.KCJE00%TYPE; Vkcgjje BM_YD0000.KCGJJE%TYPE; Vkcpfje BM_YD0000.KCPFJE%TYPE; Vsfzfdj BM_YD0000.SFZFDJ%TYPE; Vzfzgdj BM_YD0000.ZFZGDJ%TYPE; Vcxbz00 YK_YPRKMX.CXBZ00%TYPE; Vghdwmc YK_YPMXZ0.ZY0000%TYPE; Vrklxmc YK_YPMXZ0.RCLXMC%TYPE; Vmxzlsh YK_YPMXZ0.MXZLSH%TYPE; Vgjdj00 YK_YPMXZ0.GJDJ00%TYPE; Vslje00 YK_YPMXZ0.SLJE00%TYPE; Vjcdj00 YK_YPMXZ0.JCDJ00%TYPE; Vjcgjdj YK_YPMXZ0.JCGJDJ%TYPE; Vgjslje YK_YPMXZ0.GJSLJE%TYPE; Vdbdqr0 XT_XTCS00.VALUE0%TYPE; --药房退库单输入后,出库单是否由药库一起确认 VLSDJ01 YK_YPRKMX.LSDJ00%TYPE; VLSDJ02 YK_YPRKMX.LSDJ00%TYPE; V_CKDH00 YK_YPRKD0.BZ0000%TYPE; Vbmxz00 BM_BMBM00.BMXZ00%TYPE; Vbmxz01 BM_BMBM00.BMXZ00%TYPE; Vyprcpc KS_YPRCMX.YPRCPC%type; Vrkdh01 YK_YPRKMX.RKDH00%TYPE; Vrkdh02 YK_YPRKMX.RKDH00%TYPE; Vgjdj01 YK_YPRKMX.GJDJ00%TYPE; Vgjdj02 YK_YPRKMX.GJDJ00%TYPE; vYPMC01 YK_YPRKMX.YPMC00%type; vYPMC02 YK_YPRKMX.YPMC00%type; vYPGG01 YK_YPRKMX.YPGG00%type; vYPGG02 YK_YPRKMX.YPGG00%type; vYPBM01 BM_YD0000.YPBM00%type; vYPBM02 BM_YD0000.YPBM00%type; Vkswztk number(1); --是否科室物资退库 Ecustom exception; --错误变量 vGDJCL0 XT_XTCS00.VALUE0%TYPE; --取固定加成率参数 --cursor c_YPRKMX is select * from YK_YPRKMX where RKDH00 = Prkdh00; --2019.12.19 cursor c_YPRKMX is select * from YK_YPRKMX where RKDH00 = Prkdh00 and ( RKSL00*ZHL000+nvl(JLRKSL*JLZHL0,0)<>0 or RKSL00*ZHL000+nvl(JLRKSL*JLZHL0,0)=0 and instrb(nvl(BZ0000,' '),'导入')=0); Vrkqrfs xt_xtcs00.value0%type; ---入库确认是否发送消息 Vzwxm00 bm_ygbm00.zwxm00%type; Vxxnr00 xt_xttsxx.xxnr00%type; Vbmmc00 bm_bmbm00.bmmc00%type; Vwzdjtb xt_xtcs00.value0%type; cursor CUR_ypqlmx_zhzyy is select YPMC00||'('||YPGG00||')' YPMC00 from YK_YPRKMX where RKDH00=Prkdh00; Vykdyyfbh BM_TYZD00.BZDYBH%type; --当前登录药房对应的药房编号 begin Vxxnr00 :=''; select nvl(max(BMMC00),'') into Vbmmc00 from BM_BMBM00 where BMBH00=Pykbmbh; select nvl(max(ZWXM00),'0') into Vzwxm00 from BM_YGBM00 where YGBH00=Pczy000; select nvl(max(VALUE0),'N') into Vrkqrfs from XT_XTCS00 where NAME00 = 'YK_RKQRSFFSXX'; Pzxcgbz := 0; Vsysdate := sysdate; Pxxxx00 :=nvl(to_char(Prkdh00),'null')||','||nvl(to_char(Pykbmbh),'null')||','||nvl(to_char(Pqrrq00),'null')||','|| nvl(to_char(Pqrsj00),'null')||','||nvl(to_char(Pczy000),'null')||','||nvl(to_char(Pczyks0),'null')||','||nvl(to_char(Pcommit),'null'); SP_TransLog(Vsysdate,'SP_YK_YPRKD0_AUDIT',Pczy000,Pczyks0,Pxxxx00); --取参数：入出库药是否审核 select nvl(max(trim(VALUE0)),'N') into Vrcksh0 from XT_XTCS00 where NAME00='YK_RCKYKSFSH'; --取参数：折让是否计入差价 select nvl(max(trim(VALUE0)),'N') into Vzrjrcj from XT_XTCS00 where NAME00='YK_ZRJRCJ'; --取出药库是否多价格管理参数 select nvl(max(trim(VALUE0)),'N') into Vykdjgl from XT_XTCS00 where NAME00='YK_YF_LSJSFDLGL'; --多价格模式下入库确认是否更新药典单价 select nvl(max(trim(VALUE0)),'Y') into Vsfgxyd from XT_XTCS00 where NAME00='YK_DJGMSXRKSFGXYDDJ'; --取出药品增加隶属 --select nvl(max(trim(VALUE0)),'N') into Vzjypls from XT_XTCS00 where NAME00='YK_SFQYLSYP'; select nvl(max(trim(VALUE0)),'N') into Vdbdqr0 from XT_XTCS00 where NAME00='YF_YPDBDQR'; select RKZT00,RKLXBH,GHDWNM,ZKJE00,trim(BZ0000) into Vrkzt00,Vrklxbh,Vghdwnm,Vzkje00,V_CKDH00 from YK_YPRKD0 where RKDH00 = Prkdh00; select max(BMXZ00) into Vbmxz00 from BM_BMBM00 where BMBH00=Pykbmbh; --物资入库或调价时,当单价有发生变更,是否同步修改物资字典及对应耗材收费字典的单价，1是 0否 select nvl(max(trim(VALUE0)),'0') into Vwzdjtb from XT_XTCS00 where NAME00='WZ_WZDJTBSF' and Vbmxz00='c'; --取出固定加成率 select nvl(max(trim(VALUE0)),'0') into vGDJCL0 from XT_XTCS00 where NAME00='YK_YPJCL0_DEFAULT'; if substrb(vGDJCL0,1,1) = '1' then if instr(','||vGDJCL0||',',','||to_char(Pykbmbh)||',') > 0 then vGDJCL0 := '1'; else vGDJCL0 := '0'; end if; end if; --库存物资:检验科当二级库房管理 if Vbmxz00 = 'c' and Vrklxbh in ('06') then --select count(1) into Vkswztk from bm_bmbm00 where bmbh00=Vghdwnm and bmxz00<>'k'; select count(1) into Vkswztk from bm_bmbm00 where bmbh00=Vghdwnm; if (Vkswztk=1) and (V_CKDH00 is not null) then select count(1) into Vcounter from YF_YPCKD0 where CKDH00=V_CKDH00; if Vcounter>0 then Vkswztk := 0; end if; end if; end if; if Vrkzt00 = '0' then --if (Vrkzt00 = '0' or Vrkzt00 = '4' and Vrklxbh='06') then if (Vrklxbh<>'10') and (Vykdjgl='N') then update YK_YPRKMX X set (X.LSDJ00,X.XXLSDJ)=(select round(D.LSJ000*X.ZHL000/D.K2J000,4),round(D.LSJ000*X.ZHL000/D.K2J000,4) from BM_YD0000 D where X.YPNM00=D.YPNM00) where RKDH00=Prkdh00; end if; for row in c_YPRKMX loop ----------修改库存-------------- --if Vzjypls = 'Y' then update YK_YPKCXX set KCSL00=NVL(KCSL00,0)+row.PCKCSL, --库存数量(剂量单位) KCJE00=NVL(KCJE00,0)+round(row.LSDJ00 * row.RKSL00, 2) + round(nvl(row.LSDJ00*row.JLRKSL*row.JLZHL0/row.ZHL000, 0), 2), --库存金额 KCGJJE=NVL(KCGJJE,0)+row.GJJE00 --库存购进金额(购进价) where YPNM00=row.YPNM00 and YKBMBH=Pykbmbh; --update BM_YD0000 set QYRQ00 = nvl(QYRQ00,to_char(SYSDATE,'YYYYMMDD')) where YPNM00 = row.YPNM00; --else update BM_YD0000 set KCSL00=nvl(KCSL00,0)+row.PCKCSL, --库存数量(剂量单位) KCJE00=nvl(KCJE00,0)+round(row.LSDJ00 * row.RKSL00, 2) + round(nvl(row.LSDJ00*row.JLRKSL*row.JLZHL0/row.ZHL000, 0), 2), --库存金额 KCGJJE=nvl(KCGJJE,0)+row.GJJE00, --库存购进金额(购进价) KCPFJE=nvl(KCPFJE,0)+round(row.PFDJ00*row.RKSL00,2) + round(nvl(row.PFDJ00*row.JLRKSL*row.JLZHL0/row.ZHL000, 0), 2), --库存批发金额 GJZJE0=nvl(GJZJE0,0)+row.GJJE00, --购进总金额(每次购进自动增长) GJZSL0=nvl(GJZSL0,0)+round(row.RKSL00*row.ZHL000,3)+round(nvl(row.JLRKSL*row.JLZHL0, 0), 3) --购进总数量(每次购进自动增长)(剂量单位) where YPNM00 = row.YPNM00; update BM_YD0000 set GJPJDJ=decode(GJZSL0,0,0,round(GJZJE0*K2J000/GJZSL0,4)), --购进平均价(购进总金额/购进总数量) QYRQ00 = nvl(QYRQ00,to_char(SYSDATE,'YYYYMMDD')) where YPNM00 = row.YPNM00; --end if; update YK_YPRKMX set SYRQSJ=to_char(sysdate,'YYYYMMDDHH24:MI:SS') where YPRKPC=row.YPRKPC; -----------退库入库，增加原批次库存-----------, if Vrklxbh in ('06','31') AND row.YPTHPC > 0 THEN --2020.04.28 select count(1) into Vcounter from YK_YPRKMX where YPRKPC=row.YPTHPC; if Vcounter>0 then select ZHL000, JLZHL0 into Vzhl000, Vjlzhl0 from YK_YPRKMX where YPRKPC=row.YPTHPC; select RKSL00*Vzhl000+nvl(JLRKSL,0)*nvl(Vjlzhl0,0), PCKCSL+row.PCKCSL into Vrksl00,Vpckcsl from YK_YPRKMX where YPRKPC=row.YPTHPC; -- if Vpckcsl > Vrksl00 then -- Ptsxx00 := '退库后批次库存不能大于本批次入库数量，请修改退库数量!*'; -- raise Ecustom; -- end if; --增加判断 select YPNM00,LSDJ00,RKDH00,GJDJ00 into Vypnm01,VLSDJ01,Vrkdh01,Vgjdj01 from YK_YPRKMX where YPRKPC=row.YPRKPC; select YPNM00,LSDJ00,RKDH00,GJDJ00 into Vypnm02,VLSDJ02,Vrkdh02,Vgjdj02 from YK_YPRKMX where YPRKPC=row.YPTHPC; IF Vypnm01<>Vypnm02 THEN Ptsxx00 := '退库药品与原药品不相同，请重新输入退库单!*'||chr(13)||chr(10)||'原药品：'||vYPMC01 ||' 规格'|| vYPGG01 ||' 编码'|| vYPBM01|| '内码'|| Vypnm01 ||chr(13)||chr(10)||'退库药品：'||vYPMC02|| ' 规格'|| vYPGG02 ||' 编码'|| vYPBM02|| '内码'|| Vypnm02 ||chr(13)||chr(10); --Ptsxx00 := '退库药品与原药品不相同，请重新输入退库单!*'||chr(13)||chr(10)||'原药品：'||vYPMC01 ||' 规格'|| vYPGG01 ||' 编码'|| vYPBM01||chr(13)||chr(10)||'退库药品：'||vYPMC02|| ' 规格'|| vYPGG02 ||' 编码'|| vYPBM02 ||chr(13)||chr(10); raise Ecustom; end if; --增加判断：按批次退库，如果原库存已被冲销，增不增加原批次库存 2005.11.23 --零售单价不一致，不增加原批次库存 2012.11.19; --退库单的购进单价跟原入库批次的购进单价不等,则不增加原批次库存 2020.02.12 if VLSDJ01=VLSDJ02 and Vgjdj01=Vgjdj02 then select CXBZ00 into Vcxbz00 from YK_YPRKMX where YPRKPC=row.YPTHPC; --如果属于不同科室,则不增加原批次库存 2020.01.17 select count(1) into Vcounter from YK_YPRKD0 a,YK_YPRKD0 b where a.RKDH00=Vrkdh01 and b.RKDH00=Vrkdh02 and a.YKBMBH=b.YKBMBH; if Vcxbz00='Z' and Vcounter>0 then --退库单批次库存清零 update YK_YPRKMX set PCKCSL=0 where YPRKPC=row.YPRKPC; --增加原批次库存 update YK_YPRKMX set PCKCSL=Vpckcsl,SYRQSJ=to_char(sysdate,'YYYYMMDDHH24:MI:SS') where YPRKPC=row.YPTHPC; end if; end if; end if; end if; ----------更新药典产地------------------------------- if (Vrklxbh='01' or Vrklxbh='02') then update BM_YD0000 set CDID00=nvl(row.CDID00,CDID00) where YPNM00=row.YPNM00; --2019.11.21 for KCWZ9-20191024-002 if Vwzdjtb='1'then --select '购进价:'||to_char(GJJ000)||'->'||to_char(round(decode(Vzrjrcj,'Y',row.GJDJ00,row.FPGJDJ)*K2J000/row.ZHL000,4))||';'|| -- '零售价:'||to_char(LSJ000)||'->'||to_char(round(row.LSDJ00*K2J000/row.ZHL000,4)) -- into Vcznr00 from BM_YD0000 where YPNM00=row.YPNM00 and -- (GJJ000<>round(decode(Vzrjrcj,'Y',row.GJDJ00,row.FPGJDJ)*K2J000/row.ZHL000,4) or LSJ000<>round(row.LSDJ00*K2J000/row.ZHL000,4)); update BM_YD0000 set GJJ000=round(decode(Vzrjrcj,'Y',row.GJDJ00,row.FPGJDJ)*K2J000/row.ZHL000,4), LSJ000=round(row.LSDJ00*K2J000/row.ZHL000,4) where YPNM00=row.YPNM00 and (GJJ000<>round(decode(Vzrjrcj,'Y',row.GJDJ00,row.FPGJDJ)*K2J000/row.ZHL000,4) or LSJ000<>round(row.LSDJ00*K2J000/row.ZHL000,4)); update BM_YYSFXM set SFJE00=row.LSDJ00,SFXX00=row.LSDJ00,SFSX00=row.LSDJ00 where SFXMID=(select SFXMID from BM_WZQTXX where YPNM00=row.YPNM00 and rownum=1) and SFJE00<>row.LSDJ00 and JSXM00='2'; end if; if (Vykdjgl = 'Y') and (Vsfgxyd = 'Y') then update BM_YD0000 set GJJ000=round(decode(Vzrjrcj,'Y',row.GJDJ00,row.FPGJDJ)*K2J000/row.ZHL000,4), LSJ000=round(row.LSDJ00*K2J000/row.ZHL000,4), SFZBYP=nvl(row.SFZBYP,'N') where YPNM00=row.YPNM00; --当零售价大于购进价且不是固定加成率的，再更新加成率 if ( row.LSDJ00>=row.GJDJ00 ) and ( vGDJCL0 = '0' ) then update BM_YD0000 set YPJCL0=DECODE(row.GJDJ00,0,0,round(row.LSDJ00/row.GJDJ00,2)-1) where YPNM00=row.YPNM00; end if; end if; end if; ----------记账------------------ if Vghdwnm=99999 then Vghdwmc:='盘盈入库'; elsif Vrklxbh in ('06','31') then --退库 select BMMC00 into Vghdwmc from VW_YK_YPCKFX where BMBH00=Vghdwnm; else select GHDWMC into Vghdwmc from VW_YK_KSGHDW where GHDWNM=Vghdwnm; end if; select RKLXMC into Vrklxmc from BM_YKRKLX where RKLXBH=Vrklxbh; select ROUND(KCSL00/K2J000,3),KCJE00,KCGJJE,KCPFJE,SFZFDJ,ZFZGDJ into Vkcsl00,Vkcje00,Vkcgjje,Vkcpfje,Vsfzfdj,Vzfzgdj from BM_YD0000 where YPNM00=row.YPNM00; --if Vzjypls='Y' then select round(X.KCSL00/D.K2J000,3),X.KCJE00,X.KCGJJE,x.KCSL00 into Vkcsl00,Vkcje00,Vkcgjje,Vkcjlsl --此处有错 也要把v_KCGJJE取出 from YK_YPKCXX X,BM_YD0000 D where X.YPNM00=D.YPNM00 and X.YPNM00=row.YPNM00 and X.YKBMBH=Pykbmbh; --END IF; --2016.03.11 重取结存批发金额(量采价) select nvl(sum(round(PCKC00*PFDJ00,2)),0)+round(row.PFDJ00*row.RKSL00,2)+round(nvl(row.PFDJ00*row.JLRKSL*row.JLZHL0/row.ZHL000, 0), 2) into Vkcpfje from VW_YK_YPPCKC where YPNM00=row.YPNM00 and YKBMBH=Pykbmbh; Vmxzlsh := null; select decode(Vzrjrcj,'Y',row.GJDJ00,row.FPGJDJ) into Vgjdj00 from dual; select decode(Vrklxbh,'10',0,round(row.LSDJ00*row.RKSL00, 2)+round(nvl(row.LSDJ00*row.JLRKSL*row.JLZHL0/row.ZHL000, 0), 2)) into Vslje00 from dual; select decode(Vkcsl00,0,0,ROUND(Vkcje00/Vkcsl00,4)) into Vjcdj00 from dual; select decode(Vkcsl00,0,0,ROUND(Vkcgjje/Vkcsl00,4)) into Vjcgjdj from dual; --select round(row.RKSL00*decode(Vzrjrcj,'Y',row.GJDJ00,row.FPGJDJ),2) into Vgjslje from dual; select round((row.RKSL00+ROUND(nvl(row.JLRKSL*row.JLZHL0,0)/row.ZHL000,3))*decode(Vzrjrcj,'Y',row.GJDJ00,row.FPGJDJ),2) into Vgjslje from dual; if Vbmxz00 = 'c' and Vrklxbh in ('06','31') then SP_YK_YPMXZ0_EDIT( 0, --操作标志 0：增加 1：修改 2：删除 Prkdh00, --单据号 Vmxzlsh, --明细账流水号 Pykbmbh, --药库部门编号 row.YPRKPC, --药品入库批次 null, --药品出库批次 row.YPNM00, --药品内码 row.YPMC00, --药品名称 row.YPGG00, --药品规格 Pqrrq00, --记账日期 Pqrsj00, --记账时间 Vghdwnm, --出入部门编号 Vghdwmc, --摘要(自动：方向+出入部门名称) SF_YF_YPSLZH(nvl(row.RKSL00*row.ZHL000,0)+nvl(row.JLRKSL*row.JLZHL0,0),row.YPNM00,0), SF_YF_YPSLZH(nvl(row.RKSL00*row.ZHL000,0)+nvl(row.JLRKSL*row.JLZHL0,0),row.YPNM00,2), Vgjdj00, --购进单价(库存单位) row.PFDJ00, --批发单价(库存单位) null, --收入单价(零售价)(库存单位) null, null, --收入金额(零售价) row.LSDJ00, --付出单价(零售价)(记账单位)'; -SF_YF_YPSLZH(nvl(row.RKSL00*row.ZHL000,0)+nvl(row.JLRKSL*row.JLZHL0,0),row.YPNM00,1), --付出数量(记账单位)'; -Vslje00, --付出金额(零售价)'; Vjcdj00, --结存单价(结存金额/结存数量) Vkcjlsl, --结存数量(剂量单位) Vkcje00, --结存金额(零售价)(药品总量) Vjcgjdj, --结存购进单价(结存购进金额/结存数量) Vkcgjje, --结存购进金额(购进价)(药品总量) Vkcpfje, --结存批发金额 Vrklxbh, --入出库类型编码:BM_YKRKLX,BM_YKCKLX Vrklxmc, --入出库类型名称:BM_YKRKLX,BM_YKCKLX 'C', --记账性质:'R',入库,'C',出库 Vzkje00, -- null, --账页结存数量(记账单位)(药品总量) null, --账页结存金额(零售价)(药品总量) null, --账页结存购进金额(购进价)(药品总量) Vsfzfdj, --是否政府定价 Vzfzgdj, --政府最高定价 row.SFZBYP, --是否中标药品 row.SCPH00, --生产批号 row.YPSXRQ, --失效日期 row.PZWH00, --批准文号 row.ZBQNM0, --中标期 0, -- -Vgjslje, -- Pczy000, --操作员 Pczyks0, --操作员科室 'N', --是否提交 Pzxcgbz, --执行成功标志 1成功 0失败 Ptsxx00, --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ); --供应室仓库科室三级库存退库 if Vkswztk=1 then select count(1) into Vcounter from bm_bmbm00 where bmbh00=Vghdwnm and bmxz00 in ('c','k'); if Vcounter>0 then Update YK_YPMXZ0 set ZY0000=ZY0000||'(科室)' where MXZLSH=Vmxzlsh; end if; end if; else SP_YK_YPMXZ0_EDIT( 0, --操作标志 0：增加 1：修改 2：删除 Prkdh00, --单据号 Vmxzlsh, --明细账流水号 Pykbmbh, --药库部门编号 row.YPRKPC, --药品入库批次 null, --药品出库批次 row.YPNM00, --药品内码 row.YPMC00, --药品名称 row.YPGG00, --药品规格 Pqrrq00, --记账日期 Pqrsj00, --记账时间 Vghdwnm, --出入部门编号 Vghdwmc, --摘要(自动：方向+出入部门名称) --row.RKDW00, --记账单位 SF_YF_YPSLZH(nvl(row.RKSL00*row.ZHL000,0)+nvl(row.JLRKSL*row.JLZHL0,0),row.YPNM00,0), --row.ZHL000, --转换率(记账单位/剂量单位) SF_YF_YPSLZH(nvl(row.RKSL00*row.ZHL000,0)+nvl(row.JLRKSL*row.JLZHL0,0),row.YPNM00,2), Vgjdj00, --购进单价(库存单位) row.PFDJ00, --批发单价(库存单位) row.LSDJ00, --收入单价(零售价)(库存单位) --row.RKSL00+round(nvl(row.JLRKSL*row.JLZHL0/row.ZHL000, 0), 3), --收入数量(记账单位) SF_YF_YPSLZH(nvl(row.RKSL00*row.ZHL000,0)+nvl(row.JLRKSL*row.JLZHL0,0),row.YPNM00,1), Vslje00, --收入金额(零售价) null, --付出单价(零售价)(记账单位)'; null, --付出数量(记账单位)'; null, --付出金额(零售价)'; Vjcdj00, --结存单价(结存金额/结存数量) Vkcjlsl, --结存数量(剂量单位) Vkcje00, --结存金额(零售价)(药品总量) Vjcgjdj, --结存购进单价(结存购进金额/结存数量) Vkcgjje, --结存购进金额(购进价)(药品总量) Vkcpfje, --结存批发金额 Vrklxbh, --入出库类型编码:BM_YKRKLX,BM_YKCKLX Vrklxmc, --入出库类型名称:BM_YKRKLX,BM_YKCKLX 'R', --记账性质:'R',入库,'C',出库 Vzkje00, -- null, --账页结存数量(记账单位)(药品总量) null, --账页结存金额(零售价)(药品总量) null, --账页结存购进金额(购进价)(药品总量) Vsfzfdj, --是否政府定价 Vzfzgdj, --政府最高定价 row.SFZBYP, --是否中标药品 row.SCPH00, --生产批号 row.YPSXRQ, --失效日期 row.PZWH00, --批准文号 row.ZBQNM0, --中标期 Vgjslje, -- 0, -- Pczy000, --操作员 Pczyks0, --操作员科室 'N', --是否提交 Pzxcgbz, --执行成功标志 1成功 0失败 Ptsxx00, --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ); end if; --科室物资退库确认减少科室库存 if Vbmxz00 = 'c' and Vrklxbh in ('06') then --select count(1) into Vkswztk from bm_bmbm00 where bmbh00=Vghdwnm and bmxz00<>'k'; if Vkswztk=1 then Vyprcpc := null; SP_KS_YPRCMX_EDIT ( 0 , --操作标志 0：增加 1：修改 2：删除 Vyprcpc , --药品入出库批次; Vghdwnm , --科室部门编号; Prkdh00 , --单据号; '08' , --入出库类型编码; '库房退库' , --入出库类型名称; 'C' , --入出性质:''R'',入库,''C'',出库; Pykbmbh , --入出部门编号 --row.YPRKPC , --药库入库批次 row.YPTHPC , --药品入库批次 row.ypnm00 , --药品内码; row.ypmc00 , --药品名称; row.ypgg00 , --规格; row.rksl00 , --入出数量; row.rkdw00 , --入出库单位; row.zhl000 , --转换率(入出库单位/剂量单位); row.gjdj00 , --购进单价(库存单位); row.lsdj00 , --零售单价(库存单位); row.pfdj00 , --批发单价(库存单位); null , --明细流水号; row.scph00 , --生产批号; row.ypsxrq , --失效日期; row.sccjbh , --生产厂家编号; row.bz0000 , --备注; Pczy000 , --操作员 Pczyks0 , --操作员科室 'N' , --是否提交 Pzxcgbz , --执行成功标志 1成功 0失败 Ptsxx00 , --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ); end if; end if; end loop; --会计无须审核，直接入帐 if Vrcksh0='N' then SP_YK_YPMXZ0_AUDIT(Prkdh00,Pqrrq00,Pqrsj00,Pczy000,Pczyks0,'N',Pzxcgbz,Ptsxx00,Pxxxx00); if Pzxcgbz = 0 then Ptsxx00 := '药品明细账审核出错(1)'; raise Ecustom; end if; end if; --科室物资退库确认 if Vkswztk=1 then SP_KS_RKMXQR(Prkdh00,Vghdwnm,'08',Pqrrq00,Pqrsj00,Pczy000,Pczyks0,'N',Pzxcgbz,Ptsxx00,Pxxxx00,'C','库房退库'); if Pzxcgbz = 0 then Ptsxx00 := '科室物资退库确认出错'; raise Ecustom; end if; end if; end if; if Psfrz00 = 'Y' then update YK_YPRKD0 set RKZT00='1',--QRRQ00=to_char(Vsysdate,'yyyymmdd'),QRSJ00=to_char(Vsysdate,'hh24:mi:ss'),QRRBH0=Pczy000, SFSH00='Y',SHRQ00=to_char(Vsysdate,'yyyymmdd'),SHSJ00=to_char(Vsysdate,'hh24:mi:ss'),SHRBH0=Pczy000 where RKDH00=Prkdh00 and RKZT00<>'1'; if sql%notfound then Ptsxx00:='入库单状态已经发生变化，请刷新后再重试'; raise Ecustom; end if; SP_YK_YPMXZ0_AUDIT(Prkdh00,Pqrrq00,Pqrsj00,Pczy000,Pczyks0,'N',Pzxcgbz,Ptsxx00,Pxxxx00); if Pzxcgbz = 0 then Ptsxx00 := '药品明细账审核出错(2)'; raise Ecustom; end if; else if Vrcksh0='Y' then update YK_YPRKD0 set RKZT00='3',QRRQ00=to_char(Vsysdate,'yyyymmdd'),QRSJ00=to_char(Vsysdate,'hh24:mi:ss'),QRRBH0=Pczy000 where RKDH00=Prkdh00 and RKZT00<>'3'; if sql%notfound then Ptsxx00:='入库单状态已经发生变化，请刷新后再重试'; raise Ecustom; end if; else update YK_YPRKD0 set RKZT00='1',QRRQ00=to_char(Vsysdate,'yyyymmdd'),QRSJ00=to_char(Vsysdate,'hh24:mi:ss'),QRRBH0=Pczy000, SFSH00='Y',SHRQ00=to_char(Vsysdate,'yyyymmdd'),SHSJ00=to_char(Vsysdate,'hh24:mi:ss'),SHRBH0=Pczy000 where RKDH00=Prkdh00 and RKZT00<>'1'; if sql%notfound then Ptsxx00:='入库单状态已经发生变化，请刷新后再重试'; raise Ecustom; end if; end if; end if; --科室物资退库确认不调用SP_YF_YPCKD0_AUDIT --and (nvl(Vkswztk,0)=0) --2016.12.14 出现了V_CKDH00为空情况 --if (Vdbdqr0 = 'Y') and (Vrklxbh in ('06','31')) and (V_CKDH00 is not null) and (Psfrz00<>'Y') and (nvl(Vkswztk,0)=0) then if (Vdbdqr0 = 'Y' or Vdbdqr0 = 'M') and (Vrklxbh in ('06','31')) and (Psfrz00<>'Y') and (nvl(Vkswztk,0)=0) then select nvl(max(BMXZ00),'0') into Vbmxz01 from bm_bmbm00 where bmbh00=Vghdwnm; if V_CKDH00 is not null or Vbmxz01 in ('2','3') then if V_CKDH00 is not null then select count(1) into Vcounter from yf_ypckd0 where ckdh00=V_CKDH00; if Vcounter=0 then V_CKDH00 := null; end if; end if; if V_CKDH00 is null then select SF_YK_GETLSH('YF_CKDH00') into V_CKDH00 from dual; insert into YF_YPCKD0( CKDH00, --出库单号 SQ_YF_YPCKD0_CKDH00 YFBMBH, --药房部门编号 CKBMBH, --出库部门编号 CKLXBH, --出库类型编码 -->BM_YFCKLX CKZT00, --出库状态,'0'未确认，'1'已确认 CZY000, --操作员 QRR000, --确认人 对应BM_YGBM00表 SRRQ00, --输入日期 SRSJ00, --输入时间 QRRQ00, --确认日期 QRSJ00, --确认时间 BZ0000) --备注 select V_CKDH00, GHDWNM, YKBMBH, '05', '0', CZYBH0, Pczy000, to_char(Vsysdate,'yyyymmdd'), to_char(Vsysdate,'hh24:mi:ss'), null, null, Prkdh00 from YK_YPRKD0 where RKDH00=Prkdh00; insert into YF_YPCKMX( YPCKPC, --药品出库批次 SQ_YF_YPCKMX_YPCKPC CKDH00, --出库单号 YF_YPCKD0_CKDH00 YPNM00, --药品内码 对应BM_YD0000表 YPMC00, --药品名称 YPGG00, --规格 CKDW00, --出库单位 GJDJ00, --购进单价(出库单位) LSDJ00, --零售单价(出库单位) CKSL00, --出库数量(出库单位) SCPH00, --生产批号 ZHL000, YKRKPC) --转换率(出库单位/剂量单位) select SQ_YF_YPCKMX_YPCKPC.NEXTVAL, V_CKDH00, a.YPNM00, a.YPMC00, a.YPGG00, decode(sign(nvl(a.JLRKSL,0)),0,a.RKDW00,a.JLRKDW), a.GJDJ00, a.LSDJ00, decode(sign(nvl(a.JLRKSL,0)),0,a.RKSL00,round((a.RKSL00*a.ZHL000+a.JLRKSL*a.JLZHL0)/a.JLZHL0,3)), a.SCPH00, decode(sign(nvl(a.JLRKSL,0)),0,a.ZHL000,a.JLZHL0), a.YPTHPC --YPRKPC from YK_YPRKMX a,BM_YD0000 b where a.RKDH00=Prkdh00 and a.CXBZ00='Z' and a.YPNM00=b.YPNM00; end if; SP_YF_YPCKD0_AUDIT(V_CKDH00,Pqrrq00,Pqrsj00,Pczy000,null,'N',Pzxcgbz,Ptsxx00,Pxxxx00); if Pzxcgbz = 0 then --raise_application_error(-20210,'出库单确认出错！*'); Ptsxx00 := '出库单确认出错(3)'; raise Ecustom; end if; Update YK_YPRKD0 set BZ0000=substrb(BZ0000||'药房出库单:'||V_CKDH00,1,100) where RKDH00=Prkdh00; end if; end if; --发消息的处理，原先的思路不对，应该是一个药库对应一个药房的概念,不能只配置一个药库的编号 --获取当前科室对应 select nvl(max(BZDYBH),'-1') into Vykdyyfbh from BM_TYZD00 where ZDMC00 = '药库系统-药库药房消息对应设置' and LBBZ00 = '0' and MC0000 = to_char(Pczyks0); if Vrkqrfs <> 'N' and Vykdyyfbh <>'-1' then for myCursor in CUR_ypqlmx_zhzyy loop Vxxnr00 :=Vxxnr00||';'||myCursor.YPMC00; end loop; insert into XT_XTTSXX(XH0000,XXBH00,XTDM00,XXZT00,XXNR00,YXQ000,CZRQ00,CZSJ00, CZYBH0,CZYXM0,CZYKS0,XXXZ00,FSDX00,XXLX00,JSKSBH,JSYSZ0,JSRY00,ZT0000) select SQ_XT_XTTSXX_XH0000.nextval,SQ_XT_XTTSXX_XXBH00.nextval,'3', to_char(sysdate,'YYYYMMDD')||Vbmmc00||'入库药品,入库单号:('||Prkdh00||')', '药库入库药品如下:'||Vxxnr00,to_char(sysdate+7,'YYYYMMDD'),to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'hh24:mm:ss'), pczy000,Vzwxm00,Pczyks0,'0','0','0',Vykdyyfbh,'-1','-1','0' from dual; 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;