viw_order_overview.sql

来自「工厂采购系统,DELPHI+SQL SERVER,三层技术」· SQL 代码 · 共 16 行

if object_id('dbo.viw_Order_Overview') is not null
drop view dbo.viw_Order_Overview
go
create view viw_Order_Overview
as
select a.id as vendor_id,
a.vendor_nm,a.PRNO as PR_Number,
Max(a.post_date) as Creation_dt,
a.pono as Order_number, b.HPN,
b.CPN,b.mpn,b.material_nm  as Description,
sum(b.Quantity) as Qty,max(b.deli_day) as Deliv_dt,model,
b.uom
 from  ord_mst a,ord_item b
where a.pono = b.pono and isnull(b.hpn,'')<>''
group by a.pono,a.id,a.vendor_nm,a.PRNO,b.HPN,b.CPN,b.material_nm,b.mpn,b.material_nm,b.model,uom
order by HPN