proc_ap_amount_forecast_cal.sql

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

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



create    procedure Proc_AP_Amount_Forcast_Cal
@action_cd  int = null,				---0 by delivery date,--2 by creation date --3 by gr date
@vendor_id  varchar(10) = null,
@customer_id varchar(5) = null,
@hpn         varchar(18) = null,
@start_dt datetime = null,
@end_dt   datetime = null
as
begin
		
				
		create table #tmp_gtr_Record
		(
		vendor_id varchar(50),
		vendor_nm varchar(255),
		item      int,
		Order_Amount numeric(18,5),
		Currency   varchar(50),
		delivery_dt varchar(10),
		GR_Amount  numeric(18,5),
		payment    varchar(120)
		)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
		if(@action_cd =0) -- by Delivery Date
		begin
		if(isnull(@vendor_id,'')<>'') and (isnull(@hpn,'')<>'') 
		begin
		insert into #tmp_gtr_Record(vendor_id,order_amount,Delivery_dt,
		GR_Amount)
		select 
		a.id,
		sum(b.quantity*b.unitprice) as Order_Amount,
		(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,
		sum(b.received_qty*b.unitprice) as GR_Amount
		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,
		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(isnull(@customer_id,'')<>'') or(isnull(@hpn,'')<>'')
		begin
			insert into #tmp_gtr_Record(vendor_id,order_amount,Delivery_dt,
		GR_Amount)
		select 
		a.id,
		sum(b.quantity*b.unitprice) as Order_Amount,
		(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,
		sum(b.received_qty*b.unitprice) as GR_Amount
		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,
		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
		begin
			insert into #tmp_gtr_Record(vendor_id,order_amount,Delivery_dt,
		GR_Amount)
		select 
		a.id,
		sum(b.quantity*b.unitprice) as Order_Amount,
		(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,
		sum(b.received_qty*b.unitprice) as GR_Amount
		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,
		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
		end
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
		else if(@action_cd =1) -- by Creation Date
		begin
			if(isnull(@vendor_id,'')<>'') and (isnull(@hpn,'')<>'') 
		begin
		insert into #tmp_gtr_Record(vendor_id,order_amount,Delivery_dt,
		GR_Amount)
		select 
		a.id,
		sum(b.quantity*b.unitprice) as Order_Amount,
		(left(datepart(yy,b.created_dt),4)+
		(case  when  len(datepart(mm,b.created_dt))<2 then '0'+
		convert(char(2),datepart(mm,b.created_dt)) else 
		 convert(char(2),datepart(mm,b.created_dt)) end)) as delivery_dt,
		sum(b.received_qty*b.unitprice) as GR_Amount
		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.created_dt,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.created_dt,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))
		group by 
		 a.id,
		left(datepart(yy,b.created_dt),4)+
		(case  when  len(datepart(mm,b.created_dt))<2 then '0'+
		 convert(char(2),datepart(mm,b.created_dt)) else 
		 convert(char(2),datepart(mm,b.created_dt)) end)
		 order by Delivery_dt,a.id
		end
		else if(isnull(@customer_id,'')='') or(isnull(@hpn,'')<>'')
		begin
			insert into #tmp_gtr_Record(vendor_id,order_amount,Delivery_dt,
		GR_Amount)
		select 
		a.id,
		sum(b.quantity*b.unitprice) as Order_Amount,
		(left(datepart(yy,b.created_dt),4)+
		(case  when  len(datepart(mm,b.created_dt))<2 then '0'+
		convert(char(2),datepart(mm,b.created_dt)) else 
		 convert(char(2),datepart(mm,b.created_dt)) end)) as delivery_dt,
		sum(b.received_qty*b.unitprice) as GR_Amount
		from ord_mst a,ord_item b
		where a.pono = b.pono and b.hpn like '%'+@hpn
			and(cast(convert(varchar(50),b.created_dt,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.created_dt,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))
		group by 
		 a.id,
		left(datepart(yy,b.created_dt),4)+
		(case  when  len(datepart(mm,b.created_dt))<2 then '0'+
		 convert(char(2),datepart(mm,b.created_dt)) else 
		 convert(char(2),datepart(mm,b.created_dt)) end)
		 order by Delivery_dt,a.id

		end
		else if(isnull(@vendor_id,'')<>'') and (isnull(@hpn,'')='')
		begin
			insert into #tmp_gtr_Record(vendor_id,order_amount,Delivery_dt,
		GR_Amount)
		select 
		a.id,
		sum(b.quantity*b.unitprice) as Order_Amount,
		(left(datepart(yy,b.created_dt),4)+
		(case  when  len(datepart(mm,b.created_dt))<2 then '0'+
		convert(char(2),datepart(mm,b.created_dt)) else 
		 convert(char(2),datepart(mm,b.created_dt)) end)) as delivery_dt,
		sum(b.received_qty*b.unitprice) as GR_Amount
		from ord_mst a,ord_item b
		where a.pono = b.pono and a.id=@vendor_id
			and(cast(convert(varchar(50),b.created_dt,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.created_dt,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))
		group by 
		 a.id,
		left(datepart(yy,b.created_dt),4)+
		(case  when  len(datepart(mm,b.created_dt))<2 then '0'+
		 convert(char(2),datepart(mm,b.created_dt)) else 
		 convert(char(2),datepart(mm,b.created_dt)) end)
		 order by Delivery_dt,a.id
		
		end
		 else
		begin
			insert into #tmp_gtr_Record(vendor_id,order_amount,Delivery_dt,
		GR_Amount)
		select 
		a.id,
		sum(b.quantity*b.unitprice) as Order_Amount,
		(left(datepart(yy,b.created_dt),4)+
		(case  when  len(datepart(mm,b.created_dt))<2 then '0'+
		convert(char(2),datepart(mm,b.created_dt)) else 
		 convert(char(2),datepart(mm,b.created_dt)) end)) as delivery_dt,
		sum(b.received_qty*b.unitprice) as GR_Amount
		from ord_mst a,ord_item b
		where a.pono = b.pono --and b.hpn like '%'+@hpn
			and(cast(convert(varchar(50),b.created_dt,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.created_dt,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))
		group by 
 		a.id,
		left(datepart(yy,b.created_dt),4)+
		(case  when  len(datepart(mm,b.created_dt))<2 then '0'+
		 convert(char(2),datepart(mm,b.created_dt)) else 
		 convert(char(2),datepart(mm,b.created_dt)) end)
		 order by Delivery_dt,a.id

		end
		end
----------------------------------------------------------------------------------------------------------------------------------------------
		else if(@action_cd =2)--by gtr creation date
		begin
			create table #gtr_item_List
(
 vendor_id varchar(50) null,
 pono      varchar(30) null,
 HPN      varchar(50) null,
 Item     int NULL,
 in_quantity   numeric(18,3) null,
 unit_Price numeric(18,8) null,
 created_dt datetime null
)
if(isnull(@vendor_id,'')<>'') and((isnull(@hpn,'')<>'') or (isnull(@customer_id,'')<>''))
begin
	insert into #gtr_item_List(vendor_id,pono,hpn,item,in_quantity,unit_price,created_dt) 
	select a.vendorid,a.pono,b.itemno,NULL,b.Rec_Qty,null,b.Fir_date from base_Recdepot a,
	base_Recdepotc b 
	where a.add_id = b.single_Id
	and isnull(a.pono,'')<>''
	and a.vendorid =@vendor_id and b.itemno like '%'+@hpn
		and(cast(convert(varchar(50),b.fir_date,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.fir_date,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))

insert into #gtr_item_List(vendor_id,pono,hpn,item,in_quantity,unit_price,created_dt) 
	select a.vendorid,a.pono,b.itemno,NULL,b.Rec_Qty,null,b.Fir_date from stockmanage_LH..base_Recdepot a,
	stockmanage_LH..base_Recdepotc b 
	where a.add_id = b.single_Id
	and isnull(a.pono,'')<>''
	and a.vendorid =@vendor_id and b.itemno like '%'+@hpn
		and(cast(convert(varchar(50),b.fir_date,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.fir_date,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))


end
else if(isnull(@vendor_id,'')<>'' and (isnull(@hpn,'')='') and isnull(@customer_id,'')='')
begin
	insert into #gtr_item_List(vendor_id,pono,hpn,item,in_quantity,unit_price,created_dt) 
	select a.vendorid,a.pono,b.itemno,NULL,b.Rec_Qty,null,b.Fir_date from base_Recdepot a,
	base_Recdepotc b 
	where a.add_id = b.single_Id
	and isnull(a.pono,'')<>''
	and a.vendorid =@vendor_id 
	and(cast(convert(varchar(50),b.fir_date,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.fir_date,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))


insert into #gtr_item_List(vendor_id,pono,hpn,item,in_quantity,unit_price,created_dt) 
	select a.vendorid,a.pono,b.itemno,NULL,b.Rec_Qty,null,b.Fir_date from stockmanage_LH..base_Recdepot a,
	stockmanage_LH..base_Recdepotc b 
	where a.add_id = b.single_Id
	and isnull(a.pono,'')<>''
	and a.vendorid =@vendor_id and b.itemno like '%'+@hpn
		and(cast(convert(varchar(50),b.fir_date,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.fir_date,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))




end
else if(isnull(@vendor_id,'')='') and ((isnull(@hpn,'')<>'') or (isnull(@customer_id,'')<>''))
begin
	insert into #gtr_item_List(vendor_id,pono,hpn,item,in_quantity,unit_price,created_dt) 
	select a.vendorid,a.pono,b.itemno,NULL,b.Rec_Qty,null,b.Fir_date from base_Recdepot a,
	base_Recdepotc b 
	where a.add_id = b.single_Id
	and isnull(a.pono,'')<>''
	and b.itemno like '%'+@hpn
		and(cast(convert(varchar(50),b.fir_date,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.fir_date,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))

insert into #gtr_item_List(vendor_id,pono,hpn,item,in_quantity,unit_price,created_dt) 
	select a.vendorid,a.pono,b.itemno,NULL,b.Rec_Qty,null,b.Fir_date from stockmanage_LH..base_Recdepot a,
	stockmanage_LH..base_Recdepotc b 
	where a.add_id = b.single_Id
	and isnull(a.pono,'')<>''
	and a.vendorid =@vendor_id and b.itemno like '%'+@hpn
	and(cast(convert(varchar(50),b.fir_date,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.fir_date,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))



end
  else
	begin
	insert into #gtr_item_List(vendor_id,pono,hpn,item,in_quantity,unit_price,created_dt) 
	select a.vendorid,a.pono,b.itemno,NULL,b.Rec_Qty,null,b.Fir_date from base_Recdepot a,
	base_Recdepotc b 
	where a.add_id = b.single_Id	
	and isnull(a.pono,'')<>''
		and(cast(convert(varchar(50),b.fir_date,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),b.fir_date,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))

insert into #gtr_item_List(vendor_id,pono,hpn,item,in_quantity,unit_price,created_dt) 
	select a.vendorid,a.pono,b.itemno,NULL,b.Rec_Qty,null,b.Fir_date from stockmanage_LH..base_Recdepot a,
	stockmanage_LH..base_Recdepotc b 
	where a.add_id = b.single_Id
	and isnull(a.pono,'')<>''
	and a.vendorid =@vendor_id and b.itemno like '%'+@hpn
		and(cast(convert(varchar(50),b.fir_date,111) as datetime)