proc_get_suggest_item_list.sql

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

if object_id('dbo.Proc_GET_Suggest_Item') is not null
drop procedure dbo.Proc_Get_Suggest_Item
go
create procedure Proc_Get_Suggest_Item
as
begin
select item,material_nm as description  into #Target_Item from mat_mst
where 
((cpn not like 'lw%')
and (cpn not like 'TV%'
))
group by item,material_nm



select item, max(created_dt) creation_dt into #last_item
from ord_item  where item in(select item from #target_item)
group by item order by  item,max(created_dt)

select a.vendor_id,a.vendor_nm,a.item,a.HPN,a.Description as[Description],
b.creation_dt as [Last_PO_Date]
 from viw_PO_ITEM_List_All a,#last_item  b
where a.item = b.item and a.created_dt = b.creation_dt
and a.item in(select item from #Target_Item)
order by b.item,b.creation_dt

drop table #last_item
drop table #Target_Item

end
go

/*

exec Proc_Get_Suggest_Item

*/