反查订单号BOM物料预用量。(汇总明细 )

/*
说明：反查订单号BOM物料预用量。(汇总)
格式 ：exec pro_BOMFind 开始订单号，结束订单号，物料编号
Author:Sam
Builde Date:2009-04-16
Update Date:
*/

[@more@]if exists (select name from sysobjects where name ='pro_BOMfind' and type ='P' )
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/