prompt 正在创建过程 No.026 SP_YK_PDZDHB 盘点子单合并 create or replace procedure SP_YK_PDZDHB( -- MODIFICATION HISTORY -- Person Date Comments -- zhanghr 2012.07.11 create -- qks 2012.12.15 按品种盘点 : 增加条件or X.JLRKSL>0) -- qks 2013.06.29 修改YK_YPPDMX.ZMGJJE,ZMLSJE取值 -- qks 2014.03.25 modify -- wusq 2016.11.30 处理按价格盘点，修改对表YK_PDKCMX的操作 BY YK9-20161130-001 -- qks 2016.12.20 按品种盘点：1、按最后一批入库信息修正购进价，批发价和现行单价：增加条件 and g.RKDH00=h.RKDH00 --2、修正账面数量：增加条件and R.YKBMBH=Vykbmbh; for YK9-20161220-002 -- qks 2018.07.04 按批次盘点，修正SJGJJE,SJLSJE; Pyppdpc in number , --输入：药品盘点批次 Pczy000 in number , --操作员 Pczyks0 in number , --操作员科室 Pcommit in varchar2 default 'N' , --是否提交 Pzxcgbz out number , --执行成功标志 1成功 0失败 Ptsxx00 out varchar2 , --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 out varchar2 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 )AS Vcounte number; Vzrjrcj char(1); Vpdfs00 char(1); Vlsh000 number; Vqrrq00 YK_YPPD00.QRRQ00%TYPE; --确认日期 Vykbmbh YK_YPPD00.YKBMBH%TYPE; --药库部门编号 Vpdrs00 YK_YPPD00.PDRS00%TYPE; --盘点人数 Vpdmrwl XT_XTCS00.VALUE0%TYPE; -- Vparams varchar2(255); --参数变量 Verrmsg varchar2(255); --错误提示变量 Ecustom exception; --错误变量 BEGIN --(问题:账面数量保持哪个时刻的值比较合理？盘点过程中发药的处理) Verrmsg:=''; Pzxcgbz:=0; Vparams:='调用过程: SP_YK_PDZDHB('||TO_CHAR(Pyppdpc)||','||TO_CHAR(Pczy000)||','||TO_CHAR(Pczyks0)||') ; '; Pxxxx00 :=nvl(to_char(Pyppdpc),'null')||','||nvl(to_char(Pczy000),'null')||','||nvl(to_char(Pczyks0),'null')||','||nvl(Pcommit,'null'); SP_TransLog(sysdate,'SP_YK_PDZDHB',Pczy000,Pczyks0,Pxxxx00); select QRRQ00,PDFS00,YKBMBH,PDRS00 into Vqrrq00,Vpdfs00,Vykbmbh,Vpdrs00 from YK_YPPD00 where YPPDPC=Pyppdpc; if Vqrrq00 is not null then Verrmsg:='该盘点单已经被确认，请核对！'; raise Ecustom; end if; SELECT nvl(max(trim(VALUE0)),'N') into Vzrjrcj FROM XT_XTCS00 WHERE NAME00='YK_ZRJRCJ'; --删除旧的记录 delete from YK_YPPDMX where YPPDPC=Pyppdpc; delete from YK_PDKCMX where YPPDPC=Pyppdpc; --取出总量盘点实际库存不填写时按是否0处理 select nvl(max(Trim(VALUE0)),'N') into Vpdmrwl from XT_XTCS00 where NAME00='YF_YPPDWLR'; if Vpdmrwl='Y' then update YK_YPPDMX_TEMP set SJSL00=0,JLSJSL=0,YKSL00=-ZMSL00,JLYKSL=-JLZMSL where YPPDPC=Pyppdpc and SJSL00 Is null and JLSJSL is null; elsif Vpdmrwl='M' then --若实际数量没输入，同帐面库存 update YK_YPPDMX_TEMP set SJSL00=ZMSL00,JLSJSL=JLZMSL,YKSL00=0,JLYKSL=0 where YPPDPC=Pyppdpc and SJSL00 is null and JLSJSL is null; end if; if Vpdfs00='0' then --按批次盘点 --保留盘点时的批次库存 insert into YK_PDKCMX (YPPDPC,YPRKPC,RKDH00,FPH000,FPFLH0,YPNM00,YPMC00,YPGG00,RKDW00,ZHL000, RKSL00,JLRKSL,JLRKDW,JLZHL0,SFYX00,SYRQSJ,FPGJDJ,KL0000,GJDJ00,LSDJ00, PFDJ00,GJJE00,PCKCSL,SCPH00,YPSXRQ,YPTHPC,CXBZ00,CXRKPC,SFXYTJ,SCCJBH, SCCJZW,CFWZBH,CFWZMC,BZ0000,JHRQ00,SFZBYP,XXLSDJ,ZBXH00,YPJCL0,ZBQNM0, PZWH00,ZKJE00,FPRQ00,YPZCZ0,SCRQ00,BHID00,BH0000,CDID00,CDMC00,XMID00, GCBZID,SFYHGZ,WGQK00,YGJDJ0,ZTBZ00,ZTRQSJ,ZTCZY0,BCCSSL,ZCXSL0) select PYPPDPC,YPRKPC,RKDH00,FPH000,FPFLH0,YPNM00,YPMC00,YPGG00,RKDW00,ZHL000, RKSL00,JLRKSL,JLRKDW,JLZHL0,SFYX00,SYRQSJ,FPGJDJ,KL0000,GJDJ00,LSDJ00, PFDJ00,GJJE00,PCKCSL,SCPH00,YPSXRQ,YPTHPC,CXBZ00,CXRKPC,SFXYTJ,SCCJBH, SCCJZW,CFWZBH,CFWZMC,BZ0000,JHRQ00,SFZBYP,XXLSDJ,ZBXH00,YPJCL0,ZBQNM0, PZWH00,ZKJE00,FPRQ00,YPZCZ0,SCRQ00,BHID00,BH0000,CDID00,CDMC00,XMID00, GCBZID,SFYHGZ,WGQK00,YGJDJ0,ZTBZ00,ZTRQSJ,ZTCZY0,BCCSSL,ZCXSL0 from YK_YPRKMX X where X.YPRKPC in (select YPRKPC from YK_YPPDMX_TEMP where YPPDPC=Pyppdpc and (SJSL00 is not null or JLSJSL is not null)); --数据异常判断 select nvl(max(LSH000),0) into Vlsh000 from YK_YPPDMX_TEMP a where not exists(select 1 from YK_PDKCMX c where a.YPRKPC=c.YPRKPC and c.YPPDPC=PYPPDPC) and YPPDPC=PYPPDPC and (SJSL00 is not null or JLSJSL is not null) and rownum=1; if Vlsh000>0 then select '数据异常！'||YPMC00||' ['||YPGG00||'] YPNM='||to_char(YPNM00)||' 入库批次:'||to_char(YPRKPC)||' 找不到库存记录！' into Verrmsg from YK_YPPDMX_TEMP where LSH000=Vlsh000; raise Ecustom; end if; --插入盘点的批次 insert into YK_YPPDMX( YPPDPC, --药品盘点批次-->SQ_YF_YPPD00_YPPDPC YPRKPC, --药品入库批次 SCPH00, --生产批号 YPNM00, --药品内码 YPBM00, --药品编码，院内码 YPMC00, --药品名称 YPGG00, --规格 PDDW00, --盘点单位 PDDJ00, --盘点单价(盘点单位) ZMSL00, --账面数量(盘点单位) ZMJLSL, --账面剂量数量(总共) ZHL000, --转换率(盘点单位/剂量单位) GJJ000, --购进价(盘点单位,最近一批) GJPJDJ, --购进平均价(盘点单位) PFJ000, --批发价 SJSL00, --实际数量(盘点单位) YKSL00, --盈亏数量(盘点单位) JLPDDW, --计量盘点单位 JLZHL0, --计量转换率(计量/剂量) JLZMSL, --计量账面数量(余数) JLSJSL, --计量实际数量(余数) JLYKSL) --计量盈亏数量(余数) select Pyppdpc,x.YPRKPC, x.SCPH00, x.YPNM00, d.YPBM00, x.YPMC00, x.YPGG00,d.KCDW00 PDDW00,round(X.XXLSDJ*D.K2J000/X.ZHL000,4)PDDJ00, trunc(X.PCKCSL/D.K2J000) ZMSL00,X.PCKCSL ZMJLSL ,d.K2J000 ZHL000, decode(Vzrjrcj,'Y',ROUND(X.GJDJ00*D.K2J000/X.ZHL000,4),ROUND(X.FPGJDJ*D.K2J000/X.ZHL000,4)) GJJ000, d.GJPJDJ, round(X.PFDJ00*D.K2J000/X.ZHL000,4) PFJ000,null, null, d.ZJJLDW JLPDDW, d.ZJJL2J JLZHL0, round((X.PCKCSL-trunc(X.PCKCSL/D.K2J000)*K2J000)/d.ZJJL2J,4)JLZMSL, null, null from YK_YPRKD0 R,YK_YPRKMX X,BM_YD0000 D where R.RKDH00=X.RKDH00 and X.YPNM00=D.YPNM00 and X.YPRKPC in (select YPRKPC from YK_YPPDMX_TEMP where YPPDPC=Pyppdpc and (SJSL00 is not null or JLSJSL is not null)); --修正账面原值 update YK_YPPDMX a set ZMGJJE=round(a.GJJ000*round(a.ZMJLSL/a.ZHL000,3),2), ZMLSJE=round(a.PDDJ00*round(a.ZMJLSL/a.ZHL000,3),2) where YPPDPC=Pyppdpc; --修正实际数量(注意：要求前台盘点时，单位不能进行切换，只能固定库存单位和计量单位，否则此处会出错) update YK_YPPDMX a set (SJSL00,JLSJSL)=(select nvl(sum(SJSL00),0),nvl(sum(JLSJSL),0) from YK_YPPDMX_TEMP where a.YPRKPC=YPRKPC and YPPDPC=Pyppdpc) where YPPDPC=Pyppdpc; --盘点人数大于1人的，需要把计量数量取整到库存单位 if nvl(Vpdrs00,1)>1 then update YK_YPPDMX a set SJSL00=SJSL00+nvl(trunc(JLSJSL*JLZHL0/ZHL000),0), JLSJSL=JLSJSL-nvl(trunc(JLSJSL*JLZHL0/ZHL000),0)*ZHL000/JLZHL0 where YPPDPC=Pyppdpc; end if; --2018.07.04 修正SJGJJE,SJLSJE update YK_YPPDMX a set SJGJJE = Round(Round((nvl(a.SJSL00*a.ZHL000,0)+nvl(a.JLSJSL*a.JLZHL0,0))/a.ZHL000,3)*a.GJPJDJ,2), SJLSJE = Round(Round((nvl(a.SJSL00*a.ZHL000,0)+nvl(a.JLSJSL*a.JLZHL0,0))/a.ZHL000,3)*a.PDDJ00,2) where YPPDPC=Pyppdpc; --修正盘点序号 update YK_YPPDMX a set PDXH00=(select PDXH00 from YK_YPKCXX where YKBMBH=Vykbmbh and YPNM00=a.YPNM00), YKSL00=SJSL00-ZMSL00, JLYKSL=JLSJSL-JLZMSL where YPPDPC=Pyppdpc; elsif Vpdfs00='1' then --按品种盘点 --保留盘点时的批次库存 insert into YK_PDKCMX (YPPDPC,YPRKPC,RKDH00,FPH000,FPFLH0,YPNM00,YPMC00,YPGG00,RKDW00,ZHL000, RKSL00,JLRKSL,JLRKDW,JLZHL0,SFYX00,SYRQSJ,FPGJDJ,KL0000,GJDJ00,LSDJ00, PFDJ00,GJJE00,PCKCSL,SCPH00,YPSXRQ,YPTHPC,CXBZ00,CXRKPC,SFXYTJ,SCCJBH, SCCJZW,CFWZBH,CFWZMC,BZ0000,JHRQ00,SFZBYP,XXLSDJ,ZBXH00,YPJCL0,ZBQNM0, PZWH00,ZKJE00,FPRQ00,YPZCZ0,SCRQ00,BHID00,BH0000,CDID00,CDMC00,XMID00, GCBZID,SFYHGZ,WGQK00,YGJDJ0,ZTBZ00,ZTRQSJ,ZTCZY0,BCCSSL,ZCXSL0) select PYPPDPC,x.YPRKPC,x.RKDH00,x.FPH000,x.FPFLH0,x.YPNM00,x.YPMC00,x.YPGG00,x.RKDW00,x.ZHL000, x.RKSL00,x.JLRKSL,x.JLRKDW,x.JLZHL0,x.SFYX00,x.SYRQSJ,x.FPGJDJ,x.KL0000,x.GJDJ00,x.LSDJ00, x.PFDJ00,x.GJJE00,x.PCKCSL,x.SCPH00,x.YPSXRQ,x.YPTHPC,x.CXBZ00,x.CXRKPC,x.SFXYTJ,x.SCCJBH, x.SCCJZW,x.CFWZBH,x.CFWZMC,x.BZ0000,x.JHRQ00,x.SFZBYP,x.XXLSDJ,x.ZBXH00,x.YPJCL0,x.ZBQNM0, x.PZWH00,x.ZKJE00,x.FPRQ00,x.YPZCZ0,x.SCRQ00,x.BHID00,x.BH0000,x.CDID00,x.CDMC00,x.XMID00, x.GCBZID,x.SFYHGZ,x.WGQK00,x.YGJDJ0,x.ZTBZ00,x.ZTRQSJ,x.ZTCZY0,x.BCCSSL,x.ZCXSL0 from YK_YPRKMX X,YK_YPRKD0 R where R.RKDH00=X.RKDH00 and R.YKBMBH=Vykbmbh AND (X.RKSL00>0 or X.JLRKSL>0 or X.RKSL00=0 and X.bz0000 like '%导入%') AND X.CXBZ00='Z' AND R.RKZT00 not in ('0','4') and X.YPNM00 in (select YPNM00 from YK_YPPDMX_TEMP where YPPDPC=Pyppdpc and (SJSL00 is not null or JLSJSL is not null)); --数据异常判断 select nvl(max(LSH000),0) into Vlsh000 from YK_YPPDMX_TEMP a where not exists(select 1 from YK_PDKCMX c where a.YPNM00=c.YPNM00 and c.YPPDPC=PYPPDPC) and YPPDPC=PYPPDPC and (SJSL00 is not null or JLSJSL is not null) and rownum=1; if Vlsh000>0 then select '数据异常！'||YPMC00||' ['||YPGG00||'] YPNM='||to_char(YPNM00)||' 找不到库存记录！' into Verrmsg from YK_YPPDMX_TEMP where LSH000=Vlsh000; raise Ecustom; end if; insert into YK_YPPDMX( YPPDPC, --药品盘点批次-->SQ_YF_YPPD00_YPPDPC YPRKPC, --药品入库批次 SCPH00, --生产批号 YPNM00, --药品内码 YPBM00, --药品编码，院内码 YPMC00, --药品名称 YPGG00, --规格 PDDW00, --盘点单位 PDDJ00, --盘点单价(盘点单位) ZMSL00, --账面数量(盘点单位) ZMJLSL, --账面剂量数量(总共) ZHL000, --转换率(盘点单位/剂量单位) GJJ000, --购进价(盘点单位,最近一批) GJPJDJ, --购进平均价(盘点单位) PFJ000, --批发价 SJSL00, --实际数量(盘点单位) YKSL00, --盈亏数量(盘点单位) JLPDDW, --计量盘点单位 JLZHL0, --计量转换率(计量/剂量) JLZMSL, --计量账面数量(余数) JLSJSL, --计量实际数量(余数) JLYKSL) --计量盈亏数量(余数) select Pyppdpc,null,null,d.YPNM00, d.YPBM00, d.YPMC00, d.YPGG00,d.KCDW00,-1 PDDJ00, null ZMSL00 ,null ZMJLSL ,d.K2J000, -1 GJJ000, d.GJPJDJ, null PFJ000,null SJSL00, null YKSL00, d.ZJJLDW, d.ZJJL2J,null JLZMSL, null, null from YK_YPKCXX X,BM_YD0000 D where X.YKBMBH=Vykbmbh and X.YPNM00=D.YPNM00 and X.YPNM00 in (select YPNM00 from YK_YPPDMX_TEMP where YPPDPC=Pyppdpc and (SJSL00 is not null or JLSJSL is not null)) group by d.YPNM00, d.YPBM00, d.YPMC00, d.YPGG00,d.KCDW00,d.K2J000,d.GJPJDJ, d.ZJJLDW, d.ZJJL2J; --按最后一批入库信息修正购进价，批发价和现行单价 update YK_YPPDMX a set (GJJ000,PFJ000,PDDJ00)= (select decode(Vzrjrcj,'Y',round(X.GJDJ00*D.K2J000/X.ZHL000,4),round(X.FPGJDJ*D.K2J000/X.ZHL000,4)), round(X.PFDJ00*D.K2J000/X.ZHL000,4), round(X.XXLSDJ*D.K2J000/X.ZHL000,4) from YK_YPRKMX X,BM_YD0000 D where x.YPNM00=d.YPNM00 and x.YPRKPC=(select max(YPRKPC) from YK_YPRKD0 g,YK_YPRKMX h where g.YKBMBH=Vykbmbh and (h.RKSL00>0 or h.JLRKSL>0 or h.RKSL00=0 and h.bz0000 like '%导入%') AND h.CXBZ00='Z' AND g.RKZT00 not in ('0','4') and h.YPNM00=a.YPNM00 --2016.12.19 and g.RKDH00=h.RKDH00 )) where YPPDPC=Pyppdpc; --修正账面数量 update YK_YPPDMX a set (ZMJLSL,ZMSL00,JLZMSL)= (select nvl(sum(x.PCKCSL),0), nvl(trunc(sum(X.PCKCSL)/a.ZHL000),0) ZMSL00, nvl(round((sum(X.PCKCSL)-trunc(sum(X.PCKCSL)/a.ZHL000)*a.ZHL000)/a.JLZHL0,4),0) from YK_YPRKD0 R,YK_YPRKMX X where R.RKDH00=X.RKDH00 and X.YPNM00=a.YPNM00 and (X.RKSL00>0 or X.JLRKSL>0 or X.RKSL00=0 and X.bz0000 like '%导入%') AND X.CXBZ00='Z' AND R.RKZT00 not in ('0','4') and x.PCKCSL>0 --2016.12.19 and R.YKBMBH=Vykbmbh ) where YPPDPC=Pyppdpc; --修正账面原值 -- update YK_YPPDMX a set (ZMGJJE,ZMLSJE)=(select sum(round(b.GJJ000*b.PCKCSL/b.ZHL000,2)), -- sum(round(b.PDDJ00*b.PCKCSL/b.ZHL000,2)) -- from VW_YK_PDKCMX b where YKBMBH=Vykbmbh and b.YPNM00=a.YPNM00) -- where YPPDPC=Pyppdpc; --2013.06.29 update YK_YPPDMX a set (ZMGJJE,ZMLSJE)=(select sum(round(b.GJJ000*round(b.PCKCSL/b.ZHL000,3),2)), sum(round(b.PDDJ00*round(b.PCKCSL/b.ZHL000,3),2)) from VW_YK_PDKCMX b where YKBMBH=Vykbmbh and b.YPNM00=a.YPNM00) where YPPDPC=Pyppdpc; --修正实际数量 update YK_YPPDMX a set (SJSL00,JLSJSL)=(select nvl(sum(SJSL00),0),nvl(sum(JLSJSL),0) from YK_YPPDMX_TEMP where a.YPNM00=YPNM00 and YPPDPC=Pyppdpc) where YPPDPC=Pyppdpc; --盘点人数大于1人的，需要把计量数量取整到库存单位 if nvl(Vpdrs00,1)>1 then update YK_YPPDMX a set SJSL00=SJSL00+nvl(trunc(JLSJSL*JLZHL0/ZHL000),0), JLSJSL=JLSJSL-nvl(trunc(JLSJSL*JLZHL0/ZHL000),0)*ZHL000/JLZHL0 where YPPDPC=Pyppdpc; end if; --修正盘点序号 update YK_YPPDMX a set PDXH00=(select PDXH00 from YK_YPKCXX where YKBMBH=Vykbmbh and YPNM00=a.YPNM00), YKSL00=SJSL00-ZMSL00, JLYKSL=JLSJSL-JLZMSL where YPPDPC=Pyppdpc; elsif Vpdfs00='2' then --按价格盘点 insert into YK_PDKCMX (YPPDPC,YPRKPC,YPNM00,YPMC00,RKDW00,ZHL000,RKSL00,LSDJ00,GJJE00,PCKCSL) select PYPPDPC,YPRKPC,YPNM00,YPMC00,RKDW00,ZHL000,RKSL00,LSDJ00,GJJE00,PCKCSL from VW_YK_PDKCMX a where exists (select 1 from YK_YPPDMX_TEMP b where a.PDDJ00=b.PDDJ00 and a.GJJ000=b.GJJ000 and a.YPNM00=b.YPNM00 and YPPDPC=PYPPDPC) and YKBMBH=Vykbmbh; --数据异常判断 select nvl(max(LSH000),0) into Vlsh000 from YK_YPPDMX_TEMP a where not exists(select 1 from VW_YK_PDKCMX b,YK_PDKCMX c where a.PDDJ00=b.PDDJ00 and a.GJJ000=b.GJJ000 and a.YPNM00=b.YPNM00 and b.YPRKPC=c.YPRKPC and c.YPPDPC=PYPPDPC) and YPPDPC=PYPPDPC and (SJSL00 is not null or JLSJSL is not null) and rownum=1; if Vlsh000>0 then select '数据异常！药品:'||YPMC00||' ['||YPGG00||'] YPNM='||to_char(YPNM00)||' 零售价:'||to_char(PDDJ00)||' 购进价:'||to_char(GJJ000)||' 找不到库存记录！' into Verrmsg from YK_YPPDMX_TEMP where LSH000=Vlsh000; raise Ecustom; end if; --保留盘点时的批次库存 update YK_PDKCMX y set (RKDH00,FPH000,FPFLH0,YPNM00,YPMC00,YPGG00,RKDW00,ZHL000, RKSL00,JLRKSL,JLRKDW,JLZHL0,SFYX00,SYRQSJ,FPGJDJ,KL0000,GJDJ00,LSDJ00, PFDJ00,GJJE00,PCKCSL,SCPH00,YPSXRQ,YPTHPC,CXBZ00,CXRKPC,SFXYTJ,SCCJBH, SCCJZW,CFWZBH,CFWZMC,BZ0000,JHRQ00,SFZBYP,XXLSDJ,ZBXH00,YPJCL0,ZBQNM0, PZWH00,ZKJE00,FPRQ00,YPZCZ0,SCRQ00,BHID00,BH0000,CDID00,CDMC00,XMID00, GCBZID,SFYHGZ,WGQK00,YGJDJ0,ZTBZ00,ZTRQSJ,ZTCZY0,BCCSSL,ZCXSL0)= (select x.RKDH00,x.FPH000,x.FPFLH0,x.YPNM00,x.YPMC00,x.YPGG00,x.RKDW00,x.ZHL000, x.RKSL00,x.JLRKSL,x.JLRKDW,x.JLZHL0,x.SFYX00,x.SYRQSJ,x.FPGJDJ,x.KL0000,x.GJDJ00,x.LSDJ00, x.PFDJ00,x.GJJE00,x.PCKCSL,x.SCPH00,x.YPSXRQ,x.YPTHPC,x.CXBZ00,x.CXRKPC,x.SFXYTJ,x.SCCJBH, x.SCCJZW,x.CFWZBH,x.CFWZMC,x.BZ0000,x.JHRQ00,x.SFZBYP,x.XXLSDJ,x.ZBXH00,x.YPJCL0,x.ZBQNM0, x.PZWH00,x.ZKJE00,x.FPRQ00,x.YPZCZ0,x.SCRQ00,x.BHID00,x.BH0000,x.CDID00,x.CDMC00,x.XMID00, x.GCBZID,x.SFYHGZ,x.WGQK00,x.YGJDJ0,x.ZTBZ00,x.ZTRQSJ,x.ZTCZY0,x.BCCSSL,x.ZCXSL0 from YK_YPRKMX X where x.YPRKPC=y.YPRKPC) where YPPDPC=Pyppdpc; --插入盘点的药品 insert into YK_YPPDMX( YPPDPC, --药品盘点批次-->SQ_YF_YPPD00_YPPDPC YPRKPC, --药品入库批次 SCPH00, --生产批号 YPNM00, --药品内码 YPBM00, --药品编码，院内码 YPMC00, --药品名称 YPGG00, --规格 PDDW00, --盘点单位 PDDJ00, --盘点单价(盘点单位) ZMSL00, --账面数量(盘点单位) ZMJLSL, --账面剂量数量(总共) ZHL000, --转换率(盘点单位/剂量单位) GJJ000, --购进价(盘点单位,最近一批) GJPJDJ, --购进平均价(盘点单位) PFJ000, --批发价 SJSL00, --实际数量(盘点单位) YKSL00, --盈亏数量(盘点单位) JLPDDW, --计量盘点单位 JLZHL0, --计量转换率(计量/剂量) JLZMSL, --计量账面数量(余数) JLSJSL, --计量实际数量(余数) JLYKSL) --计量盈亏数量(余数) select Pyppdpc,null,null, d.YPNM00, d.YPBM00, d.YPMC00, d.YPGG00,d.PDDW00,d.PDDJ00, null,null ,d.ZHL000,d.GJJ000, d.GJPJDJ,null PFJ000,nvl(sum(SJSL00),0),null,d.JLPDDW,d.JLZHL0,null,nvl(sum(JLSJSL),0),null from YK_YPPDMX_TEMP d where YPPDPC=Pyppdpc group by d.YPNM00, d.YPBM00, d.YPMC00, d.YPGG00,d.PDDW00,d.PDDJ00,d.ZHL000,d.GJJ000, d.GJPJDJ,d.JLPDDW,d.JLZHL0; --修正账面数量 update YK_YPPDMX a set (ZMJLSL,ZMSL00,JLZMSL)= (select nvl(sum(b.ZMJLSL),0), nvl(trunc(sum(b.ZMJLSL)/b.ZHL000),0) ZMSL00, nvl(round((sum(b.ZMJLSL)-trunc(sum(b.ZMJLSL)/b.ZHL000)*b.ZHL000)/b.JLZHL0,4),0) from VW_YK_PDKCMX b where YKBMBH=Vykbmbh and a.YPNM00=b.YPNM00 and a.PDDJ00=b.PDDJ00 and a.GJJ000=b.GJJ000 group by b.ZHL000,b.JLZHL0) where YPPDPC=Pyppdpc; --修正账面原值 update YK_YPPDMX a set (ZMGJJE,ZMLSJE)= (select sum(round(b.GJJ000*round(b.PCKCSL/b.ZHL000,3),2)), sum(round(b.PDDJ00*round(b.PCKCSL/b.ZHL000,3),2)) from VW_YK_PDKCMX b where YKBMBH=Vykbmbh and a.YPNM00=b.YPNM00 and a.PDDJ00=b.PDDJ00 and a.GJJ000=b.GJJ000 ) where YPPDPC=Pyppdpc; --盘点人数大于1人的，需要把计量数量取整到库存单位 if nvl(Vpdrs00,1)>1 then update YK_YPPDMX a set SJSL00=nvl(SJSL00,0)+nvl(trunc(JLSJSL*JLZHL0/ZHL000),0), JLSJSL=nvl(JLSJSL,0)-nvl(trunc(JLSJSL*JLZHL0/ZHL000),0)*ZHL000/JLZHL0 where YPPDPC=Pyppdpc; end if; --修正盘点序号 update YK_YPPDMX a set PDXH00=(select PDXH00 from YK_YPKCXX where YKBMBH=Vykbmbh and YPNM00=a.YPNM00), YKSL00=nvl(SJSL00,0)-nvl(ZMSL00,0), JLYKSL=nvl(JLSJSL,0)-nvl(JLZMSL,0) where YPPDPC=Pyppdpc; end if; if pcommit='Y' then commit; end if; Pzxcgbz:=1; exception when no_data_found then rollback; raise_application_error(-20001,substrb('数据没有找到！*'||sqlerrm||','||Vparams,1,120)); when Ecustom then rollback; raise_application_error(-20010,substrb(Verrmsg||'！*'||Vparams,1,120)); when others then rollback; raise_application_error(-20266,substrb(nvl(sqlerrm, '原因不明出错')||'！*'||Vparams,1,120)); end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% prompt 正在创建过程 No.010 SP_YK_YPPDQR 药品盘点确认 create or replace procedure SP_YK_YPPDQR( -- MODIFICATION HISTORY -- Person Date Comments -- zhanghr 2012.07.13 zhanghr -- qks 2012.12.11 "预确认"后,SFQR00='M';update YK_PDKCMX增加条件YPPDPC=Pyppdpc -- qks 2013.06.29 修改YK_YPPDMX.SJGJJE,SJLSJE取值 -- linzy 2014.02.13 库存物资一级库房增加期初建账 -- qks 2014.08.04 修改YK_YPQLMX.JLSFSL时,如果有遇到小数,移到SFSL00字段(由于字段JLSFSL没有小数位引起) -- qks 2018.07.04 按批次盘点，修正SJGJJE,SJLSJE; -- --------- ------------- ---------------------------------------------------------- Pyppdpc in number , --药品盘点批次 Pczy000 in number , --操作员 Pczyks0 in number , --操作员科室 Psfyqr0 in varchar2 default 'N' , --是否预确认 Y预确认,N正式确认 预确认是为了计算账面盈亏数 Pcommit in varchar2 default 'N' , --是否提交 Pzxcgbz out number , --执行成功标志 1成功 0失败 Ptsxx00 out varchar2 , --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 out varchar2 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ) AS Vsfzbyp char(1); Vpdfs00 char(1); Vlsh000 number; Vyksl00 number; Vcksl00 number; Vrkdh00 YK_YPRKD0.RKDH00%type; Vyprkpc YK_YPRKMX.YPRKPC%type; Vypckpc YK_YPCKMX.YPCKPC%type; Vypqldh YK_YPQLD0.YPQLDH%type; Vsfqr00 YK_YPPD00.SFQR00%type; Vk2j000 BM_YD0000.K2J000%type; Vjldw00 BM_YD0000.JLDW00%type; Vdjdj00 BM_YD0000.GJJ000%type; Vkcdw00 BM_YD0000.KCDW00%type; Vsfsh00 XT_XTCS00.VALUE0%type; Vpfj000 BM_YD0000.PFJ000%type; Vgjj000 BM_YD0000.PFJ000%type; Vlsj000 BM_YD0000.PFJ000%type; Vqrrq00 YK_YPPD00.QRRQ00%TYPE; --确认日期 Vykbmbh YK_YPPD00.YKBMBH%TYPE; --药库部门编号 vRKLXBH BM_YKRKLX.RKLXBH%type; Vyprkmx YK_YPRKMX%rowtype; Vzrjrcj XT_XTCS00.VALUE0%type; Vpdmrwl XT_XTCS00.VALUE0%type; VSFQCJZ YK_YPPD00.SFQCJZ%type; Vsysdate date; --日期变量 Vcounter number; --计数器变 Ecustom exception; --错误变量 cursor C_YPPDMX_PY is --定义药品盘盈数据集游标 select * from YK_YPPDMX where YPPDPC=Pyppdpc and YKSL00*ZHL000+nvl(JLYKSL*JLZHL0,0)>0; cursor C_YPPDMX is --定义药品盘亏数据集游标 select * from YK_YPPDMX where YPPDPC=Pyppdpc and YKSL00*ZHL000+nvl(JLYKSL*JLZHL0,0)<0; begin Pzxcgbz := 0; Vsysdate := sysdate; Pxxxx00 :=nvl(to_char(Pyppdpc),'null')||','||nvl(to_char(Pczy000),'null')||','||nvl(to_char(Pczyks0),'null') ||','||nvl(Psfyqr0,'null')||','||nvl(Pcommit,'null'); SP_TransLog(Vsysdate,'SP_YK_YPPDQR',Pczy000,Pczyks0,Pxxxx00); select PDFS00,YKBMBH,SFQR00,nvl(SFQCJZ,'0') into Vpdfs00,Vykbmbh,Vsfqr00,VSFQCJZ from YK_YPPD00 where YPPDPC=Pyppdpc; --有效性判断 if Vsfqr00='Y' then Ptsxx00 := '该次盘点已经被确认过了,不能再确认!*'; raise Ecustom; end if; if VSFQCJZ='0' then vRKLXBH:='08'; elsif VSFQCJZ='1' then vRKLXBH:='21'; end if; --如果是期初建账 则不允许有盘亏数据 if VSFQCJZ='1' and Psfyqr0='N' then select count(*) into Vcounter from YK_YPPDMX where YPPDPC=Pyppdpc and YKSL00*ZHL000+nvl(JLYKSL*JLZHL0,0)<0; if Vcounter>0 then Ptsxx00:='期初建账有盈亏数据，可使用预确认核对数据！'; raise Ecustom; end if; end if; --如果全部未输入药品实际数量则删除本次盘点信息 select count(*) into Vcounter from YK_YPPDMX where YPPDPC=Pyppdpc and (SJSL00>=0 or JLSJSL>=0); if Vcounter=0 and Psfyqr0='N' then update BM_BMBM00 set FYBZ00='0' where BMBH00=(select YKBMBH from YK_YPPD00 where YPPDPC=Pyppdpc); delete YK_YPPD00 where YPPDPC=Pyppdpc; delete YK_YPPDMX where YPPDPC=Pyppdpc; commit; Ptsxx00 := '该次盘点未输入任何品种的实际数量，将被取消盘点过程!*'; raise Ecustom; end if; --取系统参数：入出库药会是否审核 select nvl(max(trim(VALUE0)),'N') into Vsfsh00 from XT_XTCS00 where NAME00='YK_RCKYKSFSH'; select nvl(max(trim(VALUE0)),'N') into Vzrjrcj from XT_XTCS00 where NAME00='YK_ZRJRCJ'; --取出总量盘点实际库存不填写时按是否0处理 select nvl(max(Trim(VALUE0)),'N') into Vpdmrwl from XT_XTCS00 where NAME00='YF_YPPDWLR'; if Vpdmrwl='Y' then update YK_YPPDMX set SJSL00=0,JLSJSL=0,YKSL00=-ZMSL00,JLYKSL=-JLZMSL where YPPDPC=Pyppdpc and SJSL00 Is null and JLSJSL is null; elsif Vpdmrwl='M' then --若实际数量没输入，同帐面库存 update YK_YPPDMX set SJSL00=ZMSL00,JLSJSL=JLZMSL,YKSL00=0,JLYKSL=0 where YPPDPC=Pyppdpc and SJSL00 is null and JLSJSL is null; end if; --盘点结果的处理：盘盈：进行入库操作，入库类型为'盘盈'，入库批次同最旧的库存>0的入库批次。 --- 盘亏：进行出库操作，出库类型为'盘亏'，按先进先出的原则出库。 -------------------------------1.盘点结果处理----------------------------------------- ---------------------1).盘盈入库-------------------------------------- select count(*) into Vcounter from YK_YPPDMX where YPPDPC=Pyppdpc and YKSL00*ZHL000+nvl(JLYKSL*JLZHL0,0)>0; if Vcounter>0 then if Psfyqr0='N' then --插入药品入库单主表一条记录（待确认） SP_YK_YPRKD0_EDIT(0,Vrkdh00,Vykbmbh,null,99999,vRKLXBH,null,null,null,null,to_char(Vsysdate,'YYYYMMDD'),to_char(Vsysdate,'HH24:MI:SS'), null,Pczy000,Pczyks0,'N',Pzxcgbz,Ptsxx00,Pxxxx00); if Pzxcgbz = 0 then Ptsxx00 := '药品入库操作错误(1)'; raise Ecustom; end if; end if; --对盘盈数据明细进行遍历，进行逐条记录详细入库操作 for pdmx in C_YPPDMX_PY loop --对盘盈数据明细进行遍历，进行逐条记录详细出库操作 --取出按药品效期批号倒排序 select YPRKPC into Vyprkpc from (select YPRKPC from VW_YK_PDKCMX where YKBMBH=Vykbmbh and YPNM00=pdmx.YPNM00 and ((Vpdfs00='0' and YPRKPC=pdmx.YPRKPC) or --按批次盘点 (Vpdfs00='1') or --按品种盘点 (Vpdfs00='2' and PDDJ00=pdmx.PDDJ00 and GJJ000=pdmx.GJJ000)) --按价格盘点 order by YPRKPC desc) where rownum=1; select X.* into Vyprkmx from YK_YPRKMX X where YPRKPC=Vyprkpc; --判断目前药品的中标状态 select SFZBYP,K2J000,JLDW00,KCDW00,GJJ000,PFJ000 into Vsfzbyp,Vk2j000,Vjldw00,Vkcdw00,Vdjdj00,Vpfj000 from BM_YD0000 where YPNM00=pdmx.YPNM00; if Vyprkmx.GJDJ00>0 then Vdjdj00:=ROUND(Vyprkmx.GJDJ00*Vk2j000/Vyprkmx.ZHL000,2); end if; --盘盈明细入库() if Psfyqr0='N' then Vyprkpc := null; SP_YK_YPRKMX_EDIT(0,Vrkdh00,Vyprkpc,Vyprkmx.FPH000,Vyprkmx.FPFLH0,pdmx.YPNM00,pdmx.YPMC00,pdmx.YPGG00, pdmx.PDDW00,pdmx.ZHL000,pdmx.YKSL00,pdmx.GJJ000,1,pdmx.PDDJ00,Vpfj000,Vyprkmx.SCPH00, Vyprkmx.YPSXRQ,Vyprkmx.SCCJBH,Vyprkmx.SCCJZW,Vyprkmx.CFWZBH,Vyprkmx.CFWZMC,'盘盈',null,'0',--Psfyhgz '0',Vsfzbyp,null,null,null,null,null,null,--Pfprq00 null,null,null,null,Pczy000,Pczyks0,'N',Pzxcgbz, Ptsxx00,Pxxxx00,'N',pdmx.PDDJ00,0,'Z',0,pdmx.JLYKSL, pdmx.JLPDDW,pdmx.JLZHL0); if Pzxcgbz = 0 then Ptsxx00 := '药品入库明细操作错误(1)'; raise Ecustom; end if; end if; update YK_PDKCMX set YKSL00=nvl(pdmx.YKSL00*pdmx.ZHL000,0)+nvl(pdmx.JLYKSL*pdmx.JLZHL0,0), JCSL00=PCKCSL+nvl(pdmx.YKSL00*pdmx.ZHL000,0)+nvl(pdmx.JLYKSL*pdmx.JLZHL0,0) where YPRKPC=Vyprkmx.YPRKPC and YPPDPC=Pyppdpc; end loop; --入库单审核 if Psfyqr0='N' then Update YK_YPRKD0 set RKZT00='0' where RKDH00=Vrkdh00 and RKZT00='4'; SP_YK_YPRKD0_AUDIT(Vrkdh00,Vykbmbh,to_char(Vsysdate,'YYYYMMDD'),to_char(Vsysdate,'HH24:MI:SS'),'N',Pczy000,Pczyks0,'N',Pzxcgbz,Ptsxx00,Pxxxx00); if Pzxcgbz = 0 then Ptsxx00 := '药品入库审核错误(1)'; raise Ecustom; end if; end if; end if; --------------------------2)盘亏出库----------------------------------------- select count(*) into Vcounter from YK_YPPDMX where YPPDPC=Pyppdpc and YKSL00*ZHL000+nvl(JLYKSL*JLZHL0,0)<0; if Vcounter>0 then --有盘亏数据,按先进先出的原则出库。通过定义两个游标循环来实现 --向药库药品请领单表YK_YPQLD0 插入一条记录 if Psfyqr0='N' then SP_YK_YPQLD0_EDIT(0,Vypqldh,Vykbmbh,'07',99999,null,'0',to_char(Vsysdate,'YYYYMMDD'),to_char(Vsysdate,'HH24:MI:SS'),Vsfsh00,null,null,null,Pczy000, Pczyks0,'N',Pzxcgbz,Ptsxx00,Pxxxx00,to_char(Vsysdate,'YYYYMMDD'),to_char(Vsysdate,'HH24:MI:SS'),Pczy000,null,null); if Pzxcgbz = 0 then Ptsxx00 := '药品请领操作错误(1)'; raise Ecustom; end if; --生成请领明细记录 insert into YK_YPQLMX( YPQLPC, --药品请领批次-->SQ_YK_YPQLMX_YPQLPC YPQLDH, --请领单号-->YK_YPQLD0 YPNM00, --药品内码 YPMC00, --药品名称 YPGG00, --规格 QLDW00, --请领单位 GJDJ00, --购进单价(请领单位)(最近一批) QLDJ00, --请领单价(请领单位) PFDJ00, --批发单位 ZHL000, --转换率(请领单位/剂量单位) QLSL00, --请领数量(请领单位) SFSL00, --实发数量(请领单位) JLQLSL, --计量请领数量(余数) JLQLDW, --计量请领单位 JLZHL0, --计量转换率(计量/剂量) JLSFSL, --计量实发数量(余数) BZ0000) --备注,如果为报损出库记录报损原因 select SQ_YK_YPQLMX_YPQLPC.nextval, Vypqldh, b.YPNM00, b.YPMC00, b.YPGG00, b.KCDW00, 0, 0, b.PFJ000, 0, 0, 0, 0, --计量请领数量(余数) b.ZJJLDW, --计量请领单位 0, --计量转换率(计量/剂量) 0, --计量实发数量(余数) '盘亏' from BM_YD0000 b where b.YPNM00 in (select YPNM00 from yk_yppdmx where yppdpc=Pyppdpc and YKSL00*ZHL000+nvl(JLYKSL*JLZHL0,0)<0); update YK_YPQLMX a set (GJDJ00,QLDJ00,ZHL000,JLZHL0)= (select GJJ000,PDDJ00,ZHL000,JLZHL0 from YK_YPPDMX where YPPDPC=Pyppdpc and YKSL00*ZHL000+nvl(JLYKSL*JLZHL0,0)<0 and YPNM00=a.ypnm00 and rownum=1) where YPQLDH=Vypqldh; update YK_YPQLMX a set (QLSL00,JLQLSL,SFSL00,JLSFSL)= (select -sum(YKSL00),-sum(JLYKSL),-sum(YKSL00),-sum(JLYKSL) --(select -sum(YKSL00),-sum(JLYKSL),-sum(YKSL00)+decode(sign(-mod(sum(JLYKSL),1)),1,-sum(round(JLYKSL*JLZHL0/ZHL000,3)),0),decode(sign(-mod(sum(JLYKSL),1)),1,0,-sum(JLYKSL)) from YK_YPPDMX where YPPDPC=Pyppdpc and YKSL00*ZHL000+nvl(JLYKSL*JLZHL0,0)<0 and YPNM00=a.ypnm00) where YPQLDH=Vypqldh; end if; ---对盘亏数据明细进行遍历，进行逐条记录详细出库操作 for pdmx in C_YPPDMX loop declare --定义药品入库批次游标，按药品效期批号排序(先进先出) cursor C_YPRKMX is select X.* from YK_YPRKMX X where YPRKPC in (select YPRKPC from VW_YK_PDKCMX where YKBMBH=Vykbmbh and YPNM00=pdmx.YPNM00 and ((Vpdfs00='0' and YPRKPC=pdmx.YPRKPC) or --按批次盘点 (Vpdfs00='1') or --按品种盘点 (Vpdfs00='2' and PDDJ00=pdmx.PDDJ00 and GJJ000=pdmx.GJJ000)) --按价格盘点 ) and PCKCSL>0 order by YPRKPC; begin Vyksl00 := -1*(pdmx.YKSL00*pdmx.ZHL000+nvl(pdmx.JLYKSL*pdmx.JLZHL0,0)); --药品出库数量(剂量单位) for rkmx in C_YPRKMX loop select SFZBYP,K2J000,JLDW00,PFJ000 into Vsfzbyp,Vk2j000,Vjldw00,Vpfj000 from BM_YD0000 where YPNM00=pdmx.YPNM00; select decode(Vzrjrcj,'Y',round(rkmx.GJDJ00*Vk2j000/rkmx.ZHL000,4),round(rkmx.FPGJDJ*Vk2j000/rkmx.ZHL000,4)) into Vgjj000 from dual; Vlsj000:=round(rkmx.XXLSDJ*Vk2j000/rkmx.ZHL000,4); --判断当前批次库存是否充足 if rkmx.PCKCSL>=Vyksl00 then --如果当前批次库存是否充足，则按出库数量出库 Vcksl00:=Vyksl00; else -- 如果当前批次库存不足，则按当前批次库存数量出库,即该批全部出库. Vcksl00:=rkmx.PCKCSL; end if; -----------向药库出库明细表 YK_YPCKMX插入出库批次-------------- if Psfyqr0='N' then Vypckpc := null; SP_YK_YPCKMX_EDIT(0,Vypqldh,Vypckpc,rkmx.YPRKPC,pdmx.YPNM00,pdmx.YPMC00,pdmx.YPGG00,pdmx.ZHL000, round(Vcksl00/pdmx.ZHL000),pdmx.PDDW00,round(Vgjj000*pdmx.ZHL000/Vk2j000,4),round(Vlsj000*pdmx.ZHL000/Vk2j000,4),round(Vpfj000*pdmx.ZHL000/Vk2j000,4),null,Vsfzbyp,'盘亏', Pczy000,Pczyks0,'N',Pzxcgbz,Ptsxx00,Pxxxx00,'Z',0, nvl((Vcksl00-round(Vcksl00/pdmx.ZHL000)*pdmx.ZHL000)/pdmx.JLZHL0, 0),pdmx.JLPDDW,pdmx.JLZHL0); if Pzxcgbz = 0 then Ptsxx00 := '药品出库明细操作错误(1)'; raise Ecustom; end if; end if; update YK_PDKCMX set YKSL00= -1*Vcksl00, JCSL00= PCKCSL-Vcksl00 where YPRKPC=rkmx.YPRKPC and YPPDPC=Pyppdpc; Vyksl00 :=Vyksl00-Vcksl00; exit when Vyksl00<=0; end loop; --出库未出完 if Vyksl00 > 0 then if Vpdfs00='0' then Ptsxx00 := '药品"'||pdmx.YPMC00||'['||pdmx.YPGG00||']" YPNM='||to_char(pdmx.YPNM00)||' 入库批次'||to_char(pdmx.YPRKPC)|| ' 出库数量'||to_char(pdmx.YKSL00)||pdmx.PDDW00||to_char(pdmx.JLYKSL)||pdmx.JLPDDW||' 无足够库存出库'; elsif Vpdfs00='1' then Ptsxx00 := '药品"'||pdmx.YPMC00||'['||pdmx.YPGG00||']" YPNM='||to_char(pdmx.YPNM00)|| ' 出库数量'||to_char(pdmx.YKSL00)||pdmx.PDDW00||to_char(pdmx.JLYKSL)||pdmx.JLPDDW||' 无足够库存出库'; elsif Vpdfs00='2' then Ptsxx00 := '药品"'||pdmx.YPMC00||'['||pdmx.YPGG00||']" YPNM='||to_char(pdmx.YPNM00)||' 零售价'||to_char(pdmx.PDDJ00)|| ' 购进价'||to_char(pdmx.GJJ000)||' 出库数量:'||to_char(pdmx.YKSL00)||pdmx.PDDW00||to_char(pdmx.JLYKSL)||pdmx.JLPDDW||' 无足够库存出库'; end if; raise Ecustom; end if; end; end loop; if Psfyqr0='N' then SP_YK_YPQLD0_AUDIT(Vypqldh,to_char(Vsysdate,'yyyymmdd'),to_char(Vsysdate,'hh24:mi:ss'),Pczy000,Pczyks0,'N',Pzxcgbz,Ptsxx00,Pxxxx00); if Pzxcgbz = 0 then Ptsxx00 := '药品请领确认错误(1)'; raise Ecustom; end if; end if; end if; --计算实际购进金额和零售金额 if Vpdfs00='0' then -- update YK_YPPDMX a set SJGJJE=round(a.GJJ000*(a.SJSL00*a.ZHL000+a.JLSJSL)/a.ZHL000,2), -- SJLSJE=round(a.PDDJ00*(a.SJSL00*a.ZHL000+a.JLSJSL)/a.ZHL000,2) -- where YPPDPC=Pyppdpc; update YK_YPPDMX a set SJGJJE=round(a.GJJ000*round((a.SJSL00*a.ZHL000+nvl(a.JLSJSL*a.JLZHL0,0))/a.ZHL000,3),2), SJLSJE=round(a.PDDJ00*round((a.SJSL00*a.ZHL000+nvl(a.JLSJSL*a.JLZHL0,0))/a.ZHL000,3),2) where YPPDPC=Pyppdpc; elsif Vpdfs00='1' then -- update YK_YPPDMX a set (SJGJJE,SJLSJE)=(select sum(round(round(decode(Vzrjrcj,'Y',b.GJDJ00,b.FPGJDJ)*d.K2J000/b.ZHL000,4)*(b.PCKCSL+nvl(b.YKSL00,0))/b.ZHL000,2)), -- sum(round(round(b.XXLSDJ*d.K2J000/b.ZHL000,4)*(b.PCKCSL+nvl(b.YKSL00,0))/b.ZHL000,2)) -- from YK_PDKCMX b,BM_YD0000 d where YPPDPC=Pyppdpc and b.YPNM00=a.YPNM00 and b.YPNM00=d.YPNM00) -- where YPPDPC=Pyppdpc; --2013.06.29 update YK_YPPDMX a set (SJGJJE,SJLSJE)=(select sum(round(decode('Y','Y',b.GJDJ00,b.FPGJDJ)*round((b.PCKCSL+nvl(b.YKSL00,0))/d.K2J000,3),2)), sum(round(b.XXLSDJ*round((b.PCKCSL+nvl(b.YKSL00,0))/d.K2J000,3),2)) from YK_PDKCMX b,BM_YD0000 d where YPPDPC=Pyppdpc and b.YPNM00=a.YPNM00 and b.YPNM00=d.YPNM00) where YPPDPC=Pyppdpc; elsif Vpdfs00='2' then -- update YK_YPPDMX a set (SJGJJE,SJLSJE)= -- (select sum(round(round(decode(Vzrjrcj,'Y',b.GJDJ00,b.FPGJDJ)*d.K2J000/b.ZHL000,4)*(b.PCKCSL+nvl(b.YKSL00,0))/b.ZHL000,2)), -- sum(round(round(b.XXLSDJ*d.K2J000/b.ZHL000,4)*(b.PCKCSL+nvl(b.YKSL00,0))/b.ZHL000,2)) -- from YK_PDKCMX b,BM_YD0000 d where YPPDPC=Pyppdpc and b.YPNM00=a.YPNM00 and b.YPNM00=d.YPNM00 and -- a.PDDJ00=round(b.XXLSDJ*d.K2J000/b.ZHL000,4) and -- a.GJJ000=round(decode(Vzrjrcj,'Y',b.GJDJ00,b.FPGJDJ)*d.K2J000/b.ZHL000,4)) -- where YPPDPC=Pyppdpc; update YK_YPPDMX a set (SJGJJE,SJLSJE)= (select sum(round(decode(Vzrjrcj,'Y',b.GJDJ00,b.FPGJDJ)*round((b.PCKCSL+nvl(b.YKSL00,0))/d.K2J000,3),2)), sum(round(b.XXLSDJ*round((b.PCKCSL+nvl(b.YKSL00,0))/d.K2J000,3),2)) from YK_PDKCMX b,BM_YD0000 d where YPPDPC=Pyppdpc and b.YPNM00=a.YPNM00 and b.YPNM00=d.YPNM00 and a.PDDJ00=round(b.XXLSDJ*d.K2J000/b.ZHL000,4) and a.GJJ000=round(decode(Vzrjrcj,'Y',b.GJDJ00,b.FPGJDJ)*d.K2J000/b.ZHL000,4)) where YPPDPC=Pyppdpc; end if; --合计账面数量和金额 update YK_YPPD00 set (ZMGJJE,ZMLSJE,SJGJJE,SJLSJE)= (select sum(ZMGJJE),sum(ZMLSJE),sum(SJGJJE),sum(SJLSJE) from YK_YPPDMX where YPPDPC=Pyppdpc) where YPPDPC=Pyppdpc; if Psfyqr0='N' then ---------------------------2.更新盘点表状态------------------------------------- update YK_YPPD00 set SFQR00='Y',QRRBH0=Pczy000, QRRQ00=to_char(Vsysdate,'YYYYMMDD'),QRSJ00=to_char(Vsysdate,'HH24:MI:SS'), LSYKJE=SJGJJE-ZMGJJE, GJYKJE=SJLSJE-ZMLSJE where YPPDPC=Pyppdpc; ----------------清除空的实际盘点数量--------------------- delete YK_YPPDMX where YPPDPC=Pyppdpc and SJSL00 is null; --------------------------3.更新药库盘点状态---------------------------------------- update BM_BMBM00 set FYBZ00='0' where BMBH00=(select YKBMBH from YK_YPPD00 where YPPDPC=Pyppdpc); end if; if Psfyqr0='Y' then update YK_YPPD00 set SFQR00='M' where YPPDPC=Pyppdpc and SFQR00='N'; end if; Pzxcgbz:=1; delete YK_PDKCMX where PCKCSL=0 and nvl(YKSL00,0)=0 and YPPDPC=Pyppdpc; 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; / show error; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%