proc_testing_ap_gtr.sql

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

if object_id('dbo.Proc_AP_GTR_CAL') IS NOT NULL
drop procedure dbo.Proc_AP_GTR_CAL
go
create procedure Proc_AP_GTR_CAL
@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)
		)
		
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
)

	insert into #gtr_item_List(vendor_id,pono,hpn,item,in_quantity,unit_price,created_dt) 
	select vendor_id,pono,HPN,NULL,Rec_Qty,null,creation_dt from viw_GTR_WITH_HPN

	where vendor_id = 'V0000444'--------------test 
	and isnull(pono,'')<>''		
	and (datepart(yy,creation_dt) >=2004 
		and datepart(mm,creation_dt)>=1)

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 ='V0000444'
	and (datepart(yy,b.fir_date) >=datepart(yy,@start_dt) 
		and datepart(mm,b.fir_date)>=datepart(mm,@start_dt))
		and (datepart(yy,b.fir_date) <=datepart(yy,@end_dt) 
		and datepart(mm,b.fir_date)<=datepart(mm,@end_dt)) 
	


	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 = 'V0000444'--------------test
		and (datepart(yy,created_dt) >=2004 
		and datepart(mm,created_dt)>=1)
		
	and isnull(pono,'')<>''


	
 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,'')<>''

		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,
		(convert(char(4),datepart(yy,b.created_dt))+'/'+convert(char(2),datepart(mm,b.created_dt)))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 ='v0000444'
		and (datepart(yy,created_dt) >=2004 
		and datepart(mm,created_dt)>=1)
		group by 
		a.id,(convert(char(4),datepart(yy,b.created_dt))+'/'+convert(char(2),datepart(mm,b.created_dt)))
		order by (convert(char(4),datepart(yy,b.created_dt))+'/'+convert(char(2),datepart(mm,b.created_dt)))
		
		
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 delivery_dt from #tmp_gtr_Record ) 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
		drop table #gtr_item_List

end

/*


exec Proc_AP_GTR_CAL '2004-01-01','2004-12-10'

*/