proc_ap_amount_cal_rev1.0.sql

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

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

create procedure Proc_AP_Amount_Cal_Rev1
as
  Declare @vCol varchar(50), @Col_Count int

  begin
		create table #tb_Base_Item 
		(
		vendor_id varchar(50),
		vendor_nm varchar(255),
	        Order_Amount numeric(18,5),
		Currency   varchar(50),
		delivery_dt varchar(10),
		GR_Amount  numeric(18,5),
		payment    varchar(120)
		)

		insert into #tb_base_Item
		(vendor_id,vendor_nm)
		select a.id,b.vendor_nm from ord_mst a,po_vendor_mstr b where a.id = b.vendor_id
		group by a.id,b.vendor_nm
			
		update  #tb_Base_Item
			set currency = b.deft_currency,
			    payment = b.payterms
		from #tb_base_item a, po_vendor_mstr b where a.vendor_id = b.vendor_id

		
	        select  top 100 Itemseq =identity(int,1,1) ,a.id as vendor_id,sum(b.quantity*unitprice) as 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 deliv_dt
		
		into #PO_Item_Record from ord_mst a,ord_item b where a.pono = b.pono  and isnull(a.id,'')<>''
		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 a.id
		

    select @Col_Count=max(Col_Count) from (select count(*) as Col_Count from #PO_Item_Record group by vendor_id) a
    while @Col_Count>0
    begin
      set @vCol=cast(@Col_Count as varchar(50))
      Exec(' Alter table #TB_Base_Item add dt_'+@vCol+' numeric(18,2)')    
      Exec(' Update #TB_Base_Item set dt_'+@vCol+'=isnull(b.Amount,0)'+
           ' from #TB_Base_Item a, #PO_Item_Record b, (select Min(itemseq) as itemseq from #PO_Item_Record group by vendor_id) c '+
           ' where a.vendor_id=b.vendor_id and b.itemseq=c.itemseq ')
      Delete #PO_Item_Record from #PO_Item_Record b, (select Min(itemseq) as itemseq from #PO_Item_Record group by vendor_id) c
         where b.itemseq=c.itemseq
 
      Set @Col_Count=@Col_Count-1
    end
    select * from #TB_Base_Item 

  end

Drop table #PO_Item_Record
Drop table #Tb_base_Item

GO

/*
  exec Proc_AP_Amount_Cal_Rev1

*/