/*
说明:反查订单号BOM物料预用量。(汇总)
格式 :exec pro_BOMFind 开始订单号,结束订单号,物料编号
Author:Sam
Builde Date:2009-04-16
Update Date:
*/
begin
drop procedure pro_bomFind
end
go
create PROCEDURE pro_BOMFind (
@begin_Odr_NO varchar(20)='%', --开始订单号
@end_odr_no varchar(20)='%', --结束订单号
@matNo varchar(30)='%' -- 物料编号
)
as
begin
if exists (select 1 from sysobjects where name='tmp_bomFind' and type='u')
drop table tmp_bomFind
select odr_no,matNo,mat_nm,isnull(UQty_qty,0) as Mat_Qty,Kcdw,JG_Sign='-' into tmp_bomFind from (
select odr_no ,sum(isnull(mat_tQty,0)) as UQty_Qty ,kcdw ,matNo ,mat_NM from odr_sampleBom
where ( odr_no between @begin_odr_no and @end_odr_no ) and matNO like @matNO
group by odr_no,kcdw,matNo,mat_nm
union all
--加工明细材料BOM表。
select r.odr_no,r.Qty,r.kcdw,r.matNO_jg ,m.mat_nm from
( select b.odr_no,sum(isnull(b.qty,0)) as Qty, a.kcdw,b.matNo_jg from odr_sampleBom_jg b ,odr_sampleBom a
where a.flowNo=b.flowNo and ( b.odr_no between @begin_odr_NO and @end_odr_no) and matNO_jg like @matNO
group by b.odr_no,a.kcdw,b.matNO_jg
) r, mat_material m
where r.matNo_jg=m.matNo
) Co
SELECT * FROM TMP_bomFind order by odr_no,matNo
end
go
exec pro_bomFind 'A0906-010','A0906-029','BLL-0009-%'
/*
说明:反查订单号BOM物料预用量。(明细)
格式 :exec pro_BOMFind_detail 开始订单号,结束订单号,物料编号
Author:Sam
Builde Date:2009-04-17
Update Date:
表结构:订单号,物料编号,物料名称,用量,单位,是否加工原料(T:是,F:否)
*/
if exists (select name from sysobjects where name ='pro_BOMfind_detail' and type ='P' )
begin
drop procedure pro_bomFind_detail
end
go
create PROCEDURE pro_BOMFind_detail (
@begin_Odr_NO varchar(20)='%', --开始订单号
@end_odr_no varchar(20)='%', --结束订单号
@matNo varchar(30)='%' -- 物料编号
)
as
begin
/*
declare @matNo varchar(20)
declare @odr_no varchar(20)
set @matNO ='BLL-0009-%'
set @odr_No= 'A0906-018'
*/
if exists (select 1 from sysobjects where name='tmp_bomFind' and type='u')
drop table tmp_bomFind
select odr_no,matNo,mat_nm,isnull(Mat_tqty,0) as Mat_Qty,Kcdw,JG_sign into tmp_bomFind from (
select odr_no ,mat_tQty ,kcdw ,matNo ,mat_NM ,JG_sign='F' from odr_sampleBom
where ( odr_no between @begin_odr_no and @end_odr_no ) and matNO like @matNO
-- group by odr_no,kcdw,matNo,mat_nm
union all
--加工明细材料BOM表。
select r.odr_no,r.Qty,r.kcdw,r.matNO_jg ,m.mat_nm,JG_sign='T' from
( select b.odr_no,b.qty as Qty, a.kcdw,b.matNo_jg from odr_sampleBom_jg b ,odr_sampleBom a
where a.flowNo=b.flowNo and ( b.odr_no between @begin_odr_NO and @end_odr_no) and matNO_jg like @matNO
-- group by b.odr_no,a.kcdw,b.matNO_jg
) r, mat_material m
where r.matNo_jg=m.matNo
) tt
select * from tmp_bomFind order by odr_no,matNo
end
exec pro_bomFind_detail 'A0906-010','A0906-029','BLL-0009-%'
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/729024/viewspace-1020797/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/729024/viewspace-1020797/