proc_part_outstd.sql

工厂采购系统,DELPHI+SQL SERVER,三层技术

if object_id('dbo.Proc_Part_Outstd') is not null
drop procedure  dbo.Proc_Part_Outstd
go

create procedure Proc_Part_Outstd
@HPN varchar(18) = null,
@CPN varchar(30) = null,
@dt1 datetime = null,
@dt2 datetime = null,
@sort int = null, --(1---sort by hpn,2-- sorty cpn)----
@filter int = null

as
begin

	create table #Parts_Outstd
		(
		  hpn varchar(18) not null,
	          cpn varchar(30) null,
		  Qty numeric(18,2) null,
		  Rcv_Qty numeric(18,2) null,
		  Outstd_Qty numeric(18,2) null,
		  Delivery_dt datetime null
		 )
if(isnull(@hpn,'')<>'') and(isnull(@cpn,'')='') 
begin

insert into #Parts_Outstd
select
 HPN,
CPN,
Sum(Quantity) as Qty,
sum(received_Qty) as Rcv_Qty,
sum(Balance_Qty) as Outstd_Qty,
Max(deli_day) as Delivery_dt
from ord_item
where isnull(hpn,'')<>'' and  hpn like @hpn+'%' and (deli_day  between @dt1 and @dt2)
group by cpn,hpn
end
else if(isnull(@hpn,'')='') and (isnull(@cpn,'')<>'') 
begin

insert into #Parts_Outstd
select HPN,
CPN,
Sum(Quantity) as Qty,
sum(received_Qty) as Rcv_Qty,
sum(Balance_Qty) as Outstd_Qty,
Max(deli_day) as Delivery_dt
from ord_item
where isnull(hpn,'')<>'' and  cpn like @cpn+'%' and (deli_day  between @dt1 and @dt2)
group by cpn,hpn
end
else if(isnull(@hpn,'')='') and (isnull(@cpn,'')='') 
begin
insert into #Parts_Outstd
select HPN,
CPN,
Sum(Quantity) as Qty,
sum(received_Qty) as Rcv_Qty,
sum(Balance_Qty) as Outstd_Qty,
Max(deli_day) as Delivery_dt

from ord_item
where isnull(hpn,'')<>'' and (deli_day  between @dt1 and @dt2)
group by cpn,hpn
end
else if(isnull(@hpn,'')<>'') and (isnull(@cpn,'')<>'') 
begin
insert into #Parts_Outstd
select HPN,
CPN,
Sum(Quantity) as Qty,
sum(received_Qty) as Rcv_Qty,
sum(Balance_Qty) as Outstd_Qty,
Max(deli_day) as Delivery_dt

from ord_item
where isnull(hpn,'')<>'' and  hpn like @hpn+'%' 
and cpn like @cpn+'%'
and (deli_day  between @dt1 and @dt2)
group by cpn,hpn

end
if(@sort=1) and(@filter=1)

select a.HPN,a.CPN,b.MPN,b.Description,b.Item_typ as [Mtl Type],a.Qty,
a.Rcv_Qty as [Rcv Qty],a.Outstd_Qty as [Outstd Qty],
a.Delivery_dt as [Delivery Date] 
 From #Parts_Outstd  a,baseitem b 
where a.hpn = b.itemno  and A.Outstd_qty>0
order by a.HPN
else if(@sort=2) and (@filter=1) 

select a.HPN,a.CPN,b.MPN,b.Description,b.Item_typ as [Mtl Type],a.Qty,a.Rcv_Qty as [Rcv Qty],a.Outstd_Qty as [Outstd Qty],
a.Delivery_dt as [Delivery Date] 
 From #Parts_Outstd  a,baseitem b
 where a.hpn = b.itemno and a.outstd_qty>0
order by a.CPN
else if(@sort=1) and (@filter=0) 
select a.HPN,a.CPN,b.MPN,b.Description,b.Item_typ as [Mtl Type],a.Qty,a.Rcv_Qty as [Rcv Qty],a.Outstd_Qty as [Outstd Qty],
a.Delivery_dt as [Delivery Date] 
 From #Parts_Outstd  a,baseitem b
 where a.hpn = b.itemno 
order by a.HPN
else
select a.HPN,a.CPN,b.MPN,b.Description,b.Item_typ as [Mtl Type],a.Qty,a.Rcv_Qty as [Rcv Qty],a.Outstd_Qty as [Outstd Qty],
a.Delivery_dt as [Delivery Date] 
 From #Parts_Outstd  a,baseitem b
 where a.hpn = b.itemno 
order by a.CPN

drop table #Parts_Outstd
end
go

/*
--Testing --

exec  Proc_Part_Outstd  '060815160720-CS007','LW20276470000','2001-01-01','2005-03-15',1,1
exec  Proc_Part_Outstd  '','LW20276470000','2001-01-01','2005-03-15',1,1
exec  Proc_Part_Outstd  '','','2001-01-01','2005-03-15',2,1
*/