查采购入库差异量

最新推荐文章于 2024-01-08 20:29:23 发布

cuiweigk19423

最新推荐文章于 2024-01-08 20:29:23 发布

阅读量256

点赞数

/* 从采购单、入库单提取数据
采购单号，项目，物料编号，单位，采购量，入库量，入库单号
再从上面的表和退货表里提取
采购单号，项目，入库号，退货号，物料编号，单位，采购量，入库量，退货量
*********************************************************************
说明：采购单号，项目，物料编号，单位，采购量，入库量，退货量，差异量（正数：表示超交，负数：表示少交）
格式：sp_pur_input_deff '采购单号' --后缀含糊查询
作者：Sam
建立日期：2008-09-21
完成日期：2008-09-21
*/
if exists (select name from sysobjects where name='sp_pur_input_deff' and type='p')
drop procedure sp_pur_input_deff
go
create procedure sp_pur_input_deff
@PurNo varchar(12) --采购单号，后缀含糊查询
with encryption
as
begin
select suma.pno,suma.citem,suma.matNo,suma.cgUnit,sum(suma.cg_qty) as cgQty,sum(suma.rkqty) as rkQty,sum(suma.thqty) as thQty,sum(sumd.deffQty) as deffQty from
( select a.pno,a.citem,a.ysno as rkNo,t.ysno as thNO,a.matNo,a.cgunit,a.cg_qty,isnull(a.ys_qty,0) as rkQty,isnull(t.ys_qty,0) as thQty from
( select pur.*,mat_ysmx_c.ys_qty,mat_ysmx_c.ysno from
(select p.pno,p.citem,p.matNo,p.cgUnit,p.cg_qty
from mat_cg_c p
where pno like @purNo+'%' ) pur
left join mat_ysmx_c on pur.pno=mat_ysmx_c.pno_tmp and pur.citem=mat_ysmx_c.citem) a
left join mat_thmx_c as t on
a.ysno=t.pno_tmp and a.citem=t.citem
) as suma
join
( select pno,citem,matNo,cgUnit,sum(rkqty)-sum(cg_qty)-sum(thqty) as deffQty from
( select a.pno,a.citem,a.ysno as rkNo,t.ysno as thNo,a.matNo,a.cgunit,a.cg_qty,isnull(a.ys_qty,0) as rkQty,isnull(t.ys_qty,0) as thQty from
(select pur.*,mat_ysmx_c.ys_qty,mat_ysmx_c.ysno from
(select p.pno,p.citem,p.matNo,p.cgUnit,p.cg_qty
from mat_cg_c p
where pno like @PurNo+'%' ) pur
left join mat_ysmx_c on pur.pno=mat_ysmx_c.pno_tmp and pur.citem=mat_ysmx_c.citem) a
left join mat_thmx_c as t on
a.ysno=t.pno_tmp and a.citem=t.citem
) hh
group by pno,citem,matNo,cgUnit
) as sumD
on suma.pno=sumd.pno and suma.citem=sumd.citem
group by suma.pno,suma.citem,suma.matNo,suma.cgUnit
order by suma.pno,suma.citem
end
go