proc_deliv_planning.sql

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

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

Create Procedure Proc_Deliv_Plan
@Action_cd	char(1) = null,
@customer_id    CHAR(5) = NULL,
@vendor_id char(10) = null,
@hpn      varchar(18) = null,
@CPN	  varchar(30) = null,
@start_dt datetime = null,
@end_dt   datetime = null,
@buyer	  varchar(15) = null

as
begin
	create table #tmp_Deliv_list(
		vendor_id char(10)  null,
		vendor_nm varchar(120) null,
		HPN varchar(18) null,
		CPN varchar(30) null,
		Ord_Qty  numeric(18,2) null,
		Rcv_Qty  numeric(18,2) null,
		OS_Qty numeric(18,2) null,
		deliv_dt CHAR(10) NULL
		)
	    if(@action_cd ='D') --double check with vendor and hpn
	    begin
		insert into #tmp_Deliv_list(vendor_id,HPN,CPN,Ord_Qty,Rcv_Qty,OS_Qty,deliv_dt)
		select 
		a.id,
		b.HPN,
		b.CPN,
		sum(b.quantity) as Order_Amount,
		sum(b.received_Qty) as Rcv_Qty,
		sum(b.balance_Qty) as Os_Qty,
		(left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)) as delivery_dt
			from ord_mst a,ord_item b
		where a.pono = b.pono and a.id=@vendor_id and b.hpn like @hpn+'%'
			and(cast(convert(varchar(50),b.deli_day,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.deli_day,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))
		group by 
		 a.id,
		 b.hpn,
	         b.CPN,
		left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		 convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)
		 order by Delivery_dt,a.id	
		end
		else
		 if(@action_cd ='B') --double check with customer and  vendor
	    begin
		insert into #tmp_Deliv_list(vendor_id,HPN,CPN,Ord_Qty,Rcv_Qty,OS_Qty,deliv_dt)
		select 
		a.id,
		b.HPN,
		b.CPN,
		sum(b.quantity) as Order_Amount,
		sum(b.received_Qty) as Rcv_Qty,
		sum(b.balance_Qty) as Os_Qty,
		(left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)) as delivery_dt
			from ord_mst a,ord_item b
		where a.pono = b.pono and a.id=@vendor_id and b.hpn like '%'+@customer_id
			and(cast(convert(varchar(50),b.deli_day,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.deli_day,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))
		group by 
		 a.id,
		 b.hpn,
     		 b.CPN,
		left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		 convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)
		 order by Delivery_dt,a.id	
		end
		else
		 if(@action_cd ='F') --with all parameters
	    begin
		insert into #tmp_Deliv_list(vendor_id,HPN,CPN,Ord_Qty,Rcv_Qty,OS_Qty,deliv_dt)
		select 
		a.id,
		b.HPN,
		b.CPN,
		sum(b.quantity) as Order_Amount,
		sum(b.received_Qty) as Rcv_Qty,
		sum(b.balance_Qty) as Os_Qty,
		(left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)) as delivery_dt
			from ord_mst a,ord_item b
		where a.pono = b.pono and a.id=@vendor_id and b.hpn like '%'+@customer_id and b.hpn like @hpn+'%'
			and(cast(convert(varchar(50),b.deli_day,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.deli_day,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))
		group by 
		 a.id,
		 b.hpn,
	        b.CPN,
		left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		 convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)
		 order by Delivery_dt,a.id	
		end
		else if(@action_cd ='V') -- just with vendor ID
		begin
			insert into #tmp_Deliv_list(vendor_id,HPN,CPN,Ord_Qty,Rcv_Qty,OS_Qty,deliv_dt)
		select 
		a.id,
		b.HPN,
	      b.CPN,
		sum(b.quantity) as Order_Amount,
		sum(b.received_Qty) as Rcv_Qty,
		sum(b.balance_Qty) as Os_Qty,
		(left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)) as delivery_dt
			from ord_mst a,ord_item b
		where a.pono = b.pono and a.id=@vendor_id 
			and(cast(convert(varchar(50),b.deli_day,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.deli_day,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))
		group by 
		 a.id,
		 b.hpn,
	 	b.CPN,
		left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		 convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)
		 order by Delivery_dt,a.id
		end
		else
		  if(@action_cd = 'P') --just for with partnumber --
		begin
			insert into #tmp_Deliv_list(vendor_id,HPN,CPN,Ord_Qty,Rcv_Qty,OS_Qty,deliv_dt)
		select 
		a.id,
		b.HPN,
        	b.CPN,
		sum(b.quantity) as Order_Amount,
		sum(b.received_Qty) as Rcv_Qty,
		sum(b.balance_Qty) as Os_Qty,
		(left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)) as delivery_dt
			from ord_mst a,ord_item b
		where a.pono = b.pono and  b.hpn like @hpn+'%'
			and(cast(convert(varchar(50),b.deli_day,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.deli_day,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))
		group by 
		 a.id,
		 b.hpn,
	         b.CPN,
		left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		 convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)
		 order by Delivery_dt,a.id

		end
		else  if(@action_cd = 'S') --just for with Customer partnumber  (CPN)--
		begin
			insert into #tmp_Deliv_list(vendor_id,HPN,CPN,Ord_Qty,Rcv_Qty,OS_Qty,deliv_dt)
		select 
		a.id,
		b.HPN,
        	b.CPN,
		sum(b.quantity) as Order_Amount,
		sum(b.received_Qty) as Rcv_Qty,
		sum(b.balance_Qty) as Os_Qty,
		(left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)) as delivery_dt
			from ord_mst a,ord_item b
		where a.pono = b.pono and  b.cpn like @cpn+'%'
			and(cast(convert(varchar(50),b.deli_day,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.deli_day,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))
		group by 
		 a.id,
		 b.hpn,
	         b.CPN,
		left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		 convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)
		 order by Delivery_dt,a.id

		end
else  if(@action_cd = 'N') --just for with Customer  and customer partnumber  (CPN)--
		begin
			insert into #tmp_Deliv_list(vendor_id,HPN,CPN,Ord_Qty,Rcv_Qty,OS_Qty,deliv_dt)
		select 
		a.id,
		b.HPN,
        	b.CPN,
		sum(b.quantity) as Order_Amount,
		sum(b.received_Qty) as Rcv_Qty,
		sum(b.balance_Qty) as Os_Qty,
		(left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)) as delivery_dt
			from ord_mst a,ord_item b
		where a.pono = b.pono and  b.hpn like @hpn+'%' and b.cpn like @cpn+'%'
			and(cast(convert(varchar(50),b.deli_day,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.deli_day,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))
		group by 
		 a.id,
		 b.hpn,
	         b.CPN,
		left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		 convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)
		 order by Delivery_dt,a.id

		end

		else  if(@action_cd = 'M') --just for with Customer  and Vendor
		begin
			insert into #tmp_Deliv_list(vendor_id,HPN,CPN,Ord_Qty,Rcv_Qty,OS_Qty,deliv_dt)
		select 
		a.id,
		b.HPN,
        	b.CPN,
		sum(b.quantity) as Order_Amount,
		sum(b.received_Qty) as Rcv_Qty,
		sum(b.balance_Qty) as Os_Qty,
		(left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)) as delivery_dt
			from ord_mst a,ord_item b
		where a.pono = b.pono and  a.id =@vendor_id
			and(cast(convert(varchar(50),b.deli_day,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.deli_day,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))
		group by 
		 a.id,
		 b.hpn,
	         b.CPN,
		left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		 convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)
		 order by Delivery_dt,a.id

		end
		  else
		  if(@action_cd = 'C') --just for with partnumber --
		begin
			insert into #tmp_Deliv_list(vendor_id,HPN,CPN,Ord_Qty,Rcv_Qty,OS_Qty,deliv_dt)
		select 
		a.id,
		b.HPN,
	        b.CPN,
		sum(b.quantity) as Order_Amount,
		sum(b.received_Qty) as Rcv_Qty,
		sum(b.balance_Qty) as Os_Qty,
		(left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)) as delivery_dt
			from ord_mst a,ord_item b
		where a.pono = b.pono and  b.hpn like '%'+@Customer_id+'%'
			and(cast(convert(varchar(50),b.deli_day,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.deli_day,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))
		group by 
		 a.id,
		 b.hpn,
	         b.CPN,
		left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		 convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)
		 order by Delivery_dt,a.id

		end
		 else
		    if(@action_cd ='X') -- all---
		    begin
			insert into #tmp_Deliv_list(vendor_id,HPN,CPN,Ord_Qty,Rcv_Qty,OS_Qty,deliv_dt)
		select 
		a.id,
		b.HPN,
	        b.CPN,
		sum(b.quantity) as Order_Amount,
		sum(b.received_Qty) as Rcv_Qty,
		sum(b.balance_Qty) as Os_Qty,
		(left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)) as delivery_dt
			from ord_mst a,ord_item b
		where a.pono = b.pono 
			and(cast(convert(varchar(50),b.deli_day,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.deli_day,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))
		group by 
		 a.id,
		 b.hpn,
	   	b.CPN,
		left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		 convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)
		 order by Delivery_dt,a.id

		end
else
		    if(@action_cd ='Z') -- all---
		    begin
			insert into #tmp_Deliv_list(vendor_id,HPN,CPN,Ord_Qty,Rcv_Qty,OS_Qty,deliv_dt)
		select 
		a.id,
		b.HPN,
	        b.CPN,
		sum(b.quantity) as Order_Amount,
		sum(b.received_Qty) as Rcv_Qty,
		sum(b.balance_Qty) as Os_Qty,
		(left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)) as delivery_dt
			from ord_mst a,ord_item b
		where a.pono = b.pono 
			and(cast(convert(varchar(50),b.deli_day,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.deli_day,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))
		and upper(b.created_by) =upper(@buyer)
		group by 
		 a.id,
		 b.hpn,
	   	b.CPN,
		left(datepart(yy,b.deli_day),4)+
		(case  when  len(datepart(mm,b.deli_day))<2 then '0'+
		 convert(char(2),datepart(mm,b.deli_day)) else 
		 convert(char(2),datepart(mm,b.deli_day)) end)
		 order by Delivery_dt,a.id

		end		


		update  #tmp_Deliv_list
			set vendor_nm = b.vendor_Nm
		from #tmp_Deliv_list a, po_vendor_mstr b
		where a.vendor_id = b.vendor_id
		


declare @sql varchar(8000)

set @sql = 'select vendor_id as [Vendor ID],Vendor_nm as [Vendor Name],HPN as [Partnumber--HPN],CPN,'

 

select @sql = @sql + 'sum(case Deliv_dt when '''+Deliv_dt+''' 

                          then ord_qty else 0 end) as '''+rtrim(Deliv_dt)+'-Qty'+''',
			 sum(case Deliv_dt when '''+Deliv_dt+'''

                          then rcv_Qty else 0 end) as '''+rtrim(Deliv_dt)+'-Rcv'+''',
			  sum(case Deliv_dt when '''+Deliv_dt+'''

                          then OS_Qty else 0 end) as '''+rtrim(Deliv_dt)+'-O/S'+''','


  from (select distinct  top 5000 Deliv_dt from #tmp_Deliv_list order by Deliv_dt ) as a

 

select @sql = left(@sql,len(@sql)-1)  + ' 
from #tmp_Deliv_list 
group by 
vendor_id,
vendor_nm,
hpn,
CPN
order by vendor_id '

--print(@sql)
exec(@sql)


drop table #tmp_Deliv_list
end


/* 
exec Proc_Deliv_Plan 'Z','CS007','V0000105','','','2004-01-01','2005-02-01','gpzhang'

select b.id,sum(a.quantity) as Ord_qty,sum(a.received_qty) as Rcv_Qty,sum(a.balance_qty) as os_qty from ord_item a,ord_mst b where a.pono = b.pono 
and b.id ='V0000105'   and a.hpn ='430700163302-CS007' and datepart(yy,a.deli_day)=2004 and datepart(mm,a.deli_day)=1
group by b.id,a.hpn

*/

--select * From mat_mst where id ='V0000105'