create or replace procedure SP_KS_PDZDHB( -- modification history -- Person Date Comments -- qks 2013.09.11 create:科室盘点子单合并 -- chenyw 2014.03.28 按价格盘点会报错 -- qks 2015.03.21 按品种盘点,盘盈时,优先增加负数的批次库存 -- qks 2015.04.28 盘亏也修正实际数量为0 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; Vpdfs00 char(1); Vpdlr00 char(1); --是否参与盘点 Vlsh000 number; Vqrrq00 YF_YPPD00.QRRQ00%type; --确认日期 Vyfbmbh YF_YPPD00.YFBMBH%type; --药库部门编号 Vpdrs00 YF_YPPD00.PDRS00%type; --盘点人数 Vpdzt00 YF_YPPD00.PDZT00%type; --盘点状态 Vwlrpd0 XT_XTCS00.VALUE0%type; Vparams varchar2(255); --参数变量 Verrmsg varchar2(255); --错误提示变量 Ecustom exception; --错误变量 begin --(问题:账面数量保持哪个时刻的值比较合理？盘点过程中发药的处理) Verrmsg:=''; Pzxcgbz:=0; Vparams:='调用过程: SP_KS_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_KS_PDZDHB',Pczy000,Pczyks0,Pxxxx00); select QRRQ00,PDFS00,YFBMBH,PDRS00,PDZT00 into Vqrrq00,Vpdfs00,Vyfbmbh,Vpdrs00,Vpdzt00 from YF_YPPD00 where YPPDPC=Pyppdpc; if Vqrrq00 is not null then Verrmsg:='该盘点单已经被确认，请核对！'; raise Ecustom; end if; if Vpdzt00<>'1' then Verrmsg:='该盘点单不是“待录入”状态，请核对！'; raise Ecustom; end if; Update YF_YPPD00 set PDZT00='2' where PDZT00='1' and YPPDPC=Pyppdpc; --如果还有输入的药品未汇总，不能进行盘点确认操作 /*select to_char(max(PDZDH0)) into Verrmsg from YF_YPPDMX_TEMP where YPPDPC=Pyppdpc and SFQR00='0'; if Verrmsg is not null then Verrmsg:='盘点子单'||Verrmsg||'还未确认,请全部确认后再操作!'; raise Ecustom; end if;*/ --取出药品盘点数量没有录入情况下,是否参与盘点 参数 select nvl(max(trim(VALUE0)),'N') into Vpdlr00 from xt_xtcs00 where name00='WZ_YPPDWLR'; if Vpdlr00 = 'Y' then update YF_YPPDMX_TEMP set SJSL00=decode(SJSL00,null,0,SJSL00),JLSJSL=decode(JLSJSL,null,0,JLSJSL) where YPPDPC=Pyppdpc and (SJSL00 is null or JLSJSL is null); end if; --删除旧的记录 delete from YF_YPPDMX where YPPDPC=Pyppdpc; delete from YF_PDKCMX where YPPDPC=Pyppdpc; if Vpdfs00='0' then --按批次盘点 --保留盘点时的批次库存 insert into YF_PDKCMX (YPPDPC,KCLSH0,YKRKPC,YFBMBH,YPNM00,LSDJ00,SCPH00,YPSXRQ, YKKCSL,SJKCSL,SCCJBH,GJDJ00,SFYX00) select Pyppdpc,KCLSH0,YKRKPC,YFBMBH,YPNM00,LSDJ00,SCPH00,YPSXRQ, YKKCSL,SJKCSL,SCCJBH,GJDJ00,SFYX00 from KS_PCKCMX X where YFBMBH=Vyfbmbh and X.YKRKPC in (select YPRKPC from YF_YPPDMX_TEMP where YPPDPC=Pyppdpc and (SJSL00 is not null or JLSJSL is not null)); --数据异常判断 select nvl(max(LSH000),0) into Vlsh000 from YF_YPPDMX_TEMP a where not exists(select 1 from YF_PDKCMX c where a.YPRKPC=c.YKRKPC 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 YF_YPPDMX_TEMP where LSH000=Vlsh000; raise Ecustom; end if; --插入盘点的批次 insert into YF_YPPDMX( PDMXID, --盘点明细ID号-->SQ_YF_YPPDMX_PDMXID 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 SQ_YF_YPPDMX_PDMXID.NEXTVAL,Pyppdpc,x.YKRKPC, x.SCPH00, x.YPNM00, d.YPBM00, d.YPMC00, d.YPGG00,d.KCDW00 PDDW00,X.LSDJ00 PDDJ00, trunc(X.SJKCSL/D.K2J000) ZMSL00,X.SJKCSL ZMJLSL ,d.K2J000 ZHL000, X.GJDJ00 GJJ000, d.GJPJDJ, X.GJDJ00 PFJ000,null, null, d.ZJJLDW JLPDDW, d.ZJJL2J JLZHL0, round((X.SJKCSL-trunc(X.SJKCSL/D.K2J000)*K2J000)/d.ZJJL2J,4)JLZMSL, null, null from KS_PCKCMX X,BM_YD0000 D where X.YFBMBH=Vyfbmbh and X.YPNM00=D.YPNM00 and X.YKRKPC in (select YPRKPC from YF_YPPDMX_TEMP where YPPDPC=Pyppdpc and (SJSL00 is not null or JLSJSL is not null)); --修正账面原值 update YF_YPPDMX a set ZMGJJE=round(a.GJJ000*a.ZMJLSL/a.ZHL000,2), ZMLSJE=round(a.PDDJ00*a.ZMJLSL/a.ZHL000,2) where YPPDPC=Pyppdpc; --修正实际数量(注意：要求前台盘点时，单位不能进行切换，只能固定库存单位和计量单位，否则此处会出错) update YF_YPPDMX a set (SJSL00,JLSJSL)=(select nvl(sum(SJSL00),0),nvl(sum(JLSJSL),0) from YF_YPPDMX_TEMP where a.YPRKPC=YPRKPC and YPPDPC=Pyppdpc) where YPPDPC=Pyppdpc; --盘点人数大于1人的，需要把计量数量取整到库存单位 if nvl(Vpdrs00,1)>1 then update YF_YPPDMX a set SJSL00=SJSL00+trunc(JLSJSL*JLZHL0/ZHL000),JLSJSL=JLSJSL-trunc(JLSJSL*JLZHL0/ZHL000)*ZHL000/JLZHL0 where YPPDPC=Pyppdpc; end if; --修正盘点序号 update YF_YPPDMX a set PDXH00=(select PDXH00 from KS_YPKCXX where YFBMBH=Vyfbmbh and YPNM00=a.YPNM00), YKSL00=SJSL00*ZHL000+JLSJSL*JLZHL0-nvl(ZMJLSL,0), JLYKSL=0 where YPPDPC=Pyppdpc; elsif Vpdfs00='1' then --按品种盘点 --保留盘点时的批次库存 insert into YF_PDKCMX (YPPDPC,KCLSH0,YKRKPC,YFBMBH,YPNM00,LSDJ00,SCPH00,YPSXRQ, YKKCSL,SJKCSL,SCCJBH,GJDJ00,SFYX00) select Pyppdpc,KCLSH0,YKRKPC,YFBMBH,YPNM00,LSDJ00,SCPH00,YPSXRQ, YKKCSL,SJKCSL,SCCJBH,GJDJ00,SFYX00 from KS_PCKCMX X where YFBMBH=Vyfbmbh and X.YPNM00 in (select YPNM00 from YF_YPPDMX_TEMP where YPPDPC=Pyppdpc and (SJSL00 is not null or JLSJSL is not null)); --数据异常判断 select nvl(max(LSH000),0) into Vlsh000 from YF_YPPDMX_TEMP a where not exists(select 1 from YF_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 YF_YPPDMX_TEMP where LSH000=Vlsh000; raise Ecustom; end if; --盘盈, 负数批次库存记录需要写入 2015.03.17 --盘亏也修正实际数量为0 2015.04.28 insert into YF_YPPDMX( PDMXID, --盘点明细ID号-->SQ_YF_YPPDMX_PDMXID 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, --计量盈亏数量(余数) KCLSH0, ZMGJJE, ZMLSJE ) select SQ_YF_YPPDMX_PDMXID.NEXTVAL,Pyppdpc,x.YKRKPC, x.SCPH00, x.YPNM00, d.YPBM00, d.YPMC00, d.YPGG00,d.KCDW00 PDDW00,X.LSDJ00 PDDJ00, trunc(X.SJKCSL/D.K2J000) ZMSL00,X.SJKCSL ZMJLSL ,d.K2J000 ZHL000, X.GJDJ00 GJJ000, d.GJPJDJ, X.GJDJ00 PFJ000,null, null, d.ZJJLDW JLPDDW, d.ZJJL2J JLZHL0, round((X.SJKCSL-trunc(X.SJKCSL/D.K2J000)*K2J000)/d.ZJJL2J,4)JLZMSL, null, null, X.KCLSH0,round(x.GJDJ00*x.SJKCSL/d.K2J000,2),round(x.LSDJ00*x.SJKCSL/d.K2J000,2) from KS_PCKCMX X,BM_YD0000 D where X.YFBMBH=Vyfbmbh and X.YPNM00=D.YPNM00 and X.YPNM00 in (select YPNM00 from YF_YPPDMX_TEMP where YPPDPC=Pyppdpc and (SJSL00 is not null or JLSJSL is not null) -- and SJSL00>ZMSL00 ) and X.SJKCSL<0 and X.KCLSH0 not in (select max(to_number(KCLSH0))from KS_PCKCMX where YFBMBH=Vyfbmbh and YPNM00=X.YPNM00); insert into YF_YPPDMX( PDMXID, --盘点明细ID号-->SQ_YF_YPPDMX_PDMXID 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 SQ_YF_YPPDMX_PDMXID.NEXTVAL,a.* from (select Pyppdpc,null YPRKPC,null SCPH00,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 JLSJSL, null JLYKSL from KS_YPKCXX X,BM_YD0000 D where X.YFBMBH=Vyfbmbh and X.YPNM00=D.YPNM00 and X.YPNM00 in (select YPNM00 from YF_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) a; --按最后一批入库信息修正购进价，批发价和现行单价 --2012.08.01 by qks max(YPRKPC) --> max(YKRKPC) update YF_YPPDMX a set (GJJ000,PFJ000,PDDJ00)= (select X.GJDJ00,X.GJDJ00,X.LSDJ00 from KS_PCKCMX X,BM_YD0000 D where YFBMBH=Vyfbmbh and x.YPNM00=a.YPNM00 and d.YPNM00=a.YPNM00 and x.KCLSH0=(select max(to_number(KCLSH0)) from KS_PCKCMX h where h.YFBMBH=Vyfbmbh and h.YPNM00=a.YPNM00)) where YPPDPC=Pyppdpc and KCLSH0 is null; --修正账面数量 update YF_YPPDMX a set (ZMJLSL,ZMSL00,JLZMSL)= (select nvl(sum(x.SJKCSL),0), nvl(trunc(sum(X.SJKCSL)/a.ZHL000),0) ZMSL00, nvl(round((sum(X.SJKCSL)-trunc(sum(X.SJKCSL)/a.ZHL000)*a.ZHL000)/a.JLZHL0,4),0) from KS_PCKCMX X where YFBMBH=Vyfbmbh and X.YPNM00=a.YPNM00 and not exists (select 1 from YF_YPPDMX where YPPDPC=Pyppdpc and ypnm00=X.YPNM00 and KCLSH0=X.KCLSH0) ) where YPPDPC=Pyppdpc and KCLSH0 is null; --修正账面原值 update YF_YPPDMX a set (ZMGJJE,ZMLSJE)=(select sum(round(b.GJDJ00*b.SJKCSL/c.K2J000,2)), sum(round(b.LSDJ00*b.SJKCSL/c.K2J000,2)) from KS_PCKCMX b,BM_YD0000 c where YFBMBH=Vyfbmbh and b.YPNM00=a.YPNM00 and b.YPNM00=c.YPNM00 --and (b.KCLSH0=a.KCLSH0 and nvl(a.KCLSH0,0)>0 or nvl(a.KCLSH0,0)=0) and not exists (select 1 from YF_YPPDMX where YPPDPC=Pyppdpc and ypnm00=b.YPNM00 and KCLSH0=b.KCLSH0) ) where YPPDPC=Pyppdpc and KCLSH0 is null; --修正实际数量为0,原账面数为负数 2015.03.21 --盘亏也修正实际数量为0 2015.04.28 update YF_YPPDMX a set SJSL00=0,JLSJSL=0 where YPPDPC=Pyppdpc and KCLSH0>0 and exists (select 1 from YF_YPPDMX_TEMP where YPPDPC=Pyppdpc and ypnm00=a.YPNM00 and (SJSL00 is not null or JLSJSL is not null) --and SJSL00>ZMSL00 ); /* Update YF_YPPDMX_TEMP a set (DDSJSL,DDJLSL)=(select nvl(sum(-ZMSL00),0),nvl(sum(-JLZMSL),0) from YF_YPPDMX where YPPDPC=Pyppdpc and ypnm00=a.YPNM00 and KCLSH0>0) where YPPDPC=Pyppdpc and exists (select 1 from YF_YPPDMX where YPPDPC=Pyppdpc and YPNM00=a.YPNM00 and KCLSH0>0); --修正实际数量 update YF_YPPDMX a set (SJSL00,JLSJSL)=(select nvl(a.SJSL00,0)+nvl(sum(SJSL00-nvl(DDSJSL,0)),0),nvl(a.JLSJSL,0)+nvl(sum(JLSJSL-nvl(DDJLSL,0)),0) from YF_YPPDMX_TEMP b where a.YPNM00=YPNM00 and YPPDPC=Pyppdpc) where YPPDPC=Pyppdpc and KCLSH0 is null; */ update YF_YPPDMX a set (SJSL00,JLSJSL)=(select nvl(sum(SJSL00),0),nvl(sum(JLSJSL),0) from YF_YPPDMX_TEMP where a.YPNM00=YPNM00 and YPPDPC=Pyppdpc) where YPPDPC=Pyppdpc and KCLSH0 is null; --盘点人数大于1人的，需要把计量数量取整到库存单位 if nvl(Vpdrs00,1)>1 then update YF_YPPDMX a set SJSL00=SJSL00+trunc(JLSJSL*JLZHL0/ZHL000),JLSJSL=JLSJSL-trunc(JLSJSL*JLZHL0/ZHL000)*ZHL000/JLZHL0 where YPPDPC=Pyppdpc; end if; --修正盘点序号 update YF_YPPDMX a set PDXH00=(select PDXH00 from KS_YPKCXX where YFBMBH=Vyfbmbh and YPNM00=a.YPNM00), YKSL00=SJSL00*ZHL000+JLSJSL*JLZHL0-nvl(ZMJLSL,0), JLYKSL=0 where YPPDPC=Pyppdpc; elsif Vpdfs00='2' then --按价格盘点 insert into YF_PDKCMX (YPPDPC,YKRKPC,KCLSH0,YFBMBH,YPNM00,LSDJ00) select PYPPDPC,YKRKPC,KCLSH0,YFBMBH,YPNM00,LSDJ00 from KS_PCKCMX a where exists (select 1 from YF_YPPDMX_TEMP b where a.LSDJ00=b.PDDJ00 and a.GJDJ00=b.GJJ000 and a.YPNM00=b.YPNM00 and YPPDPC=PYPPDPC) and YFBMBH=Vyfbmbh; --数据异常判断 select nvl(max(LSH000),0) into Vlsh000 from YF_YPPDMX_TEMP a where not exists(select 1 from KS_PCKCMX b,YF_PDKCMX c where a.PDDJ00=b.LSDJ00 and a.GJJ000=b.GJDJ00 and a.YPNM00=b.YPNM00 and b.YKRKPC=c.YKRKPC 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 YF_YPPDMX_TEMP where LSH000=Vlsh000; raise Ecustom; end if; --保留盘点时的批次库存 update YF_PDKCMX y set (KCLSH0,YFBMBH,YPNM00,LSDJ00,SCPH00,YPSXRQ, YKKCSL,SJKCSL,SCCJBH,GJDJ00,SFYX00)= (select KCLSH0,YFBMBH,YPNM00,LSDJ00,SCPH00,YPSXRQ, YKKCSL,SJKCSL,SCCJBH,GJDJ00,SFYX00 from KS_PCKCMX X where YFBMBH=Vyfbmbh and x.YKRKPC=y.YKRKPC) where YPPDPC=Pyppdpc; --插入盘点的药品 insert into YF_YPPDMX( PDMXID, --盘点明细ID号-->SQ_YF_YPPDMX_PDMXID 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 SQ_YF_YPPDMX_PDMXID.NEXTVAL,a.* from (select Pyppdpc,null YPRKPC,null SCPH00, d.YPNM00, d.YPBM00, d.YPMC00, d.YPGG00,d.PDDW00,d.PDDJ00, null ZMSL00,null ZMJLSL,d.ZHL000,d.GJJ000, d.GJPJDJ,null PFJ000,nvl(sum(SJSL00),0),null YKSL00,d.JLPDDW,d.JLZHL0,null JLZMSL,nvl(sum(JLSJSL),0) JLSJSL,null JLYKSL from YF_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) a; --修正账面数量 update YF_YPPDMX a set (ZMJLSL,ZMSL00,JLZMSL)= (select nvl(sum(b.SJKCSL),0), nvl(trunc(sum(b.SJKCSL)/c.K2J000),0) ZMSL00, nvl(round((sum(b.SJKCSL)-trunc(sum(b.SJKCSL)/c.ZJJL2J)*c.K2J000)/c.ZJJL2J,4),0) from KS_PCKCMX b,BM_YD0000 c where YFBMBH=Vyfbmbh and a.YPNM00=b.YPNM00 and b.YPNM00=c.YPNM00 and a.PDDJ00=b.LSDJ00 and a.GJJ000=b.GJDJ00 group by c.K2J000,c.ZJJL2J) where YPPDPC=Pyppdpc; --修正账面原值 update YF_YPPDMX a set (ZMGJJE,ZMLSJE)= (select sum(round(b.GJDJ00*b.SJKCSL/c.ZJJL2J,2)), sum(round(b.LSDJ00*b.SJKCSL/c.ZJJL2J,2)) from KS_PCKCMX b,BM_YD0000 c where YFBMBH=Vyfbmbh and a.YPNM00=b.YPNM00 and b.YPNM00=c.YPNM00 and a.PDDJ00=b.LSDJ00 and a.GJJ000=b.GJDJ00 ) where YPPDPC=Pyppdpc; --盘点人数大于1人的，需要把计量数量取整到库存单位 if nvl(Vpdrs00,1)>1 then update YF_YPPDMX a set SJSL00=SJSL00+trunc(JLSJSL*JLZHL0/ZHL000),JLSJSL=JLSJSL-trunc(JLSJSL*JLZHL0/ZHL000)*ZHL000/JLZHL0 where YPPDPC=Pyppdpc; end if; --修正盘点序号 update YF_YPPDMX a set PDXH00=(select PDXH00 from KS_YPKCXX where YFBMBH=Vyfbmbh and YPNM00=a.YPNM00), YKSL00=SJSL00*ZHL000+JLSJSL*JLZHL0-nvl(ZMJLSL,0), JLYKSL=0 where YPPDPC=Pyppdpc; end if; Update YF_YPPD00 set ZDHBRQ=to_char(sysdate,'YYYYMMDD'),ZDHBSJ=to_char(sysdate,'HH24:MI:SS') where YPPDPC=Pyppdpc; 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; /