select
pih.[PO Number],
pih.[Pre-Assigned No_] as [Invoice No],
pil.[Document No_],
pil.[Description] as [Reason For Discrepency],
pil.[Line No_],
pl.[No_] as [Item No],
pl.[Vendor Item No_],
pl.[Order Date],
pil.[Posting Date],
pil.[Expected Receipt Date],
pih.[Notes] as [Header Notes],
pil.[No_] as [G/L Account No],
pih.[Buy-from Vendor No_],
pih.[Buy-from Vendor Name],
Pil.Quantity as [Inv Qty From InvoiceLine],
pil.[Amount Including VAT] as [Inv Value From InvoiceLine],
pl.Quantity as [PO Quantity From Purchaseline],
pl.[Quantity Received] as [Received Qty From PurchaseLine],
pl.[Quantity Invoiced] as [Invoiced Qty From PurchaseLine] ,
pl.[Amount Including VAT] as [PO Value From PurchaseLine]
from
[Purch_ Inv_ Line] pil
inner join
Purch_ Inv_ Header] pih
on pil.[Document No_]= pih.[No_]
left join [Purchase Line] pl
on pih.[PO Number]=pl.[Document No_]
and pl.[Line No_]=pil.[Line No_]
where
PIL.[Document No_] in
(
select distinct pil.[Document No_] from
Purch_ Inv_ Line] pil
where piL.[No_] in ('700xxx','700xxx','17xxxxx') and pil.[Posting Date] >=getdate()-7
)
and piL.[Type]='1'