viw_return_po_list.sql

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

if object_id('dbo.Viw_Return_PO_List') is not null
drop view dbo.Viw_Return_PO_List
go
Create view Viw_Return_PO_List
as
select
 a.UnP_No as [Doc_Number],
 a.Dn_no as [DN_Number],
 b.Itemno as HPN,
 c.CPN,
 c.mpn,
 c.Description,
 b.unp_Qty as [Return_Qty],
 b.unit as UoM,
 b.Depot as [Warehouse],
 a.cause as [Return_Reasons],
 a.VendorID as [Vendor_ID],
 b.Remark,
 b.fir_oper as [Created_By],
 b.fir_date as [Creation_Date],
 b.cher as [Updated_By],
 b.Che_date as [Update_Date]
 
 from Base_UntreadPo a,Base_UntreadPoc b,baseitem c
where a.add_id = b.single_id and b.itemno = c.itemno
and a.vendorid in(select vendor_id from po_vendor_mstr)

union
  all
select
 a.UnP_No as [Doc_Number],
 a.Dn_no as [DN_Number],
 b.Itemno as HPN,
 c.CPN,
 c.mpn,
 c.Description,
 b.unp_Qty as [Return_Qty],
 b.unit as UoM,
 b.Depot as [Warehouse],
 a.cause as [Return_Reasons],
 a.VendorID as [Vendor_ID],
 b.Remark,
 b.fir_oper as [Created_By],
 b.fir_date as [Creation_Date],
 b.cher as [Updated_By],
 b.Che_date as [Update_Date]
 
 from Base_UntreadPo_LH a,Base_UntreadPoc_LH b,baseitem c
where a.add_id = b.single_id and b.itemno = c.itemno
and a.vendorid in(select vendor_id from po_vendor_mstr)