viw_delay_po_item_list.sql

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

if object_id('dbo.Viw_Delay_PO_Item_List') is not null
drop view dbo.Viw_Delay_PO_Item_List
go

create view Viw_Delay_PO_Item_List
as
 select a.id as Vendor_ID,
 a.vendor_nm as Vendor_Name,
 b.pono as Order_Number,
 b.Item as Item_code,
 b.Itemseq as Item,
 b.HPN,
 b.CPN,
 b.MPN,
 b.material_nm as Description,
 b.Model,
 b.UoM,
 b.Quantity as PO_Qty,
 b.Received_Qty as Rcv_Qty,
 b.Balance_Qty as Outstd_Qty,
 b.deli_day as Deliv_dt,
  convert(int, getdate()-b.deli_day) as Delay_Days,
 b.iscom as Status,
 b.created_by as Created_By,
 b.Created_dt as Creation_dt
from ord_mst a,ord_item b where a.pono = b.pono
and b.quantity>Received_Qty and iscom='N' and b.deli_day<=getdate()

go