入库数量与采购数量有差异(存储过程)

/*说明：查入库数量与采购数量有差异的记录,正数表示超交,负数表示久交.stock_no:仓库号(16:是暂收仓).
注意: 暂收仓和入库单里没有填写采购单的部分都不进行计算.
格式：sp_dff 采购单号
日期：2008-09-09
作者：Sam
*/
if exists (select name from sysobjects where name='sp_deff' and type ='p')
drop procedure sp_deff
go
Create procedure sp_deff
@purNo varchar(20)
with encryption
as
begin
select a.*,pur.cg_qty, (a.sumQTY-Pur.cg_qty) as Variance from (
select matno,pno_tmp,unit_tmp, sum(ys_QTY) as sumQTY
from mat_ysmx_c
where pno_tmp is not null and stock_no not in('16')
group by matno,pno_tmp,unit_tmp ) a
, ( select pno,matno,sum(CG_QTY) AS CG_QTY from mat_cg_c where matno is not null GRoup by pno,matno ) pur
where a.pno_tmp=pur.pno and a.matno=pur.matno
/* and (a.sumQTY-PUR.cg_qty)<>0.000 */ and a.pno_tmp like @purNo+ '%'
order by a.pno_tmp
end
go

sp_deff CB080700285
sp_deff CC08060118

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/729024/viewspace-564848/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/729024/viewspace-564848/