proc_ap_amount_forecast_cal.sql

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

		>=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

-----------------------------------------------------------------------------------------------------------------------
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 vendor_id,pono,hpn,item,in_quantity,unit_Price,created_dt from gtr_item 
	where vendor_id =@vendor_id and hpn like '%'+@hpn
	and isnull(pono,'')<>''
		and(cast(convert(varchar(50),created_dt,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),created_dt,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 vendor_id,pono,hpn,item,in_quantity,unit_Price,created_dt from gtr_item 
	where vendor_id =@vendor_id --and hpn like '%'+@hpn
	and isnull(pono,'')<>''
		and(cast(convert(varchar(50),created_dt,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),created_dt,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 vendor_id,pono,hpn,item,in_quantity,unit_Price,created_dt from gtr_item 
	where  hpn like '%'+@hpn
	and isnull(pono,'')<>''
		and(cast(convert(varchar(50),created_dt,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),created_dt,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 vendor_id,pono,hpn,item,in_quantity,unit_Price,created_dt from gtr_item 
	where 	(cast(convert(varchar(50),created_dt,111) as datetime)
		>=cast(convert(varchar(50),@start_dt,111) as datetime))
		and
		(cast(convert(varchar(50),created_dt,111) as datetime)
		<=(cast(convert(varchar(50),@end_dt,111) as datetime)))
	and isnull(pono,'')<>''
end

 update #gtr_item_List
		set unit_Price = isnull(b.unitprice,0)
 	from #gtr_item_List a,ord_item b
where (a.pono =b.pono and a.hpn = b.hpn) or(a.pono = b.pono and a.item = b.item)
	and isnull(a.pono,'')<>''

---------------------------------------------------------------------------------------------------------------------------------------


		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.in_quantity*b.unit_Price) 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.in_quantity*b.unit_Price) as GR_Amount
		from ord_mst a,#gtr_item_List 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 if (isnull(@vendor_id,'')='') and (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.in_quantity*b.unit_Price) 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.in_quantity*b.unit_Price) as GR_Amount
		from ord_mst a,#gtr_item_List 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(@customer_id,'')='') and(isnull(@hpn,'')='')
		begin
			insert into #tmp_gtr_Record(vendor_id,order_amount,Delivery_dt,
		GR_Amount)
		select 
		a.id,
		sum(b.in_quantity*b.unit_Price) 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.in_quantity*b.unit_Price) as GR_Amount
		from ord_mst a,#gtr_item_List 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.in_quantity*b.unit_Price) 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.in_quantity*b.unit_Price) as GR_Amount
		from ord_mst a,#gtr_item_List 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

	end

-----------------------------------------------------PO Outstanding AP Amount--- by delivery  date---------------------------------------------------------------------------------------------------
else if(@action_cd = 3) 
begin
		truncate table #tmp_gtr_record
    	       if(isnull(@vendor_id,'')<>'')  -- by vendor_id
		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,0
		from ord_mst a, ord_item b
		where  b.iscom ='N' and  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
		else if(isnull(@vendor_id,'')='') ---------------by all 
		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,0
		from ord_mst a, ord_item b 
		where   b.iscom ='N' and 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
-----------------------------------------------------PO Outstanding AP Amount--- by creation date---------------------------------------------------------------------------------------------------
else if(@action_cd = 4) 
begin
		truncate table #tmp_gtr_record
    	       if(isnull(@vendor_id,'')<>'')  -- by vendor_id
		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,0
		from ord_mst a, ord_item b
		where  b.iscom ='N' and  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 if(isnull(@vendor_id,'')='') ---------------by all 
		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,0
		from ord_mst a, ord_item b
		where b.iscom ='N' and  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
end


update #tmp_gtr_Record
	set vendor_nm = b.vendor_nm,
	    Currency  = b.deft_Currency,
	    payment  = b.payterms
from #tmp_gtr_Record a,po_vendor_mstr b
where a.vendor_id = b.vendor_id


declare @sql varchar(8000)

set @sql = 'select vendor_id,Vendor_nm,Currency,payment,'

 

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

                          then Order_Amount else 0 end) as '''+delivery_dt+''','


  from (select distinct  top 1000 delivery_dt from #tmp_gtr_Record order by delivery_dt ) as a

 

select @sql = left(@sql,len(@sql)-1)  + ' 
from #tmp_gtr_Record 
group by 
vendor_id,
vendor_nm,
currency,
payment

order by vendor_id
'

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


drop table #tmp_gtr_Record
end
go


/* 
exec Proc_AP_Amount_Forcast_Cal 2,'V0000105','','','2004-01-01','2005-12-1'

select sum(a.balance_qty*a.unitprice) as amount from ord_item a,ord_mst b where a.pono = b.pono 
and b.id ='V0000104' and a.iscom ='N'  and datepart(yy,a.deli_day)=2004 and datepart(mm,a.deli_day)=10

*/