Payment File Query
I'm trying to generate a query to compile all payment listing. While I'm able to retrieve most of the vendor information from the table OCRD & OCPR and invoice payment amount from OVPM or VPM2, the listing generated will show every single line of invoice paid. Is it possible to show a total transaction by the vendor itself? Is there any other table available to retrieve the information from? Thanks in advance for any helps.
Try Below Query.
Select OVPM.Docnum as[Pymnt No.],
OVPM.CardCode [Vendor Code],
OVPM.CardName [Vendor Name],
OVPM.DocTotal as [Total Amount]
from OVPM Left outer Join OCRD ON OVPM.Cardcode = OCRD.Cardcode
Where OCRD.CardType = 'S'
and OVPM.Canceled <> 'Y'
and OVPM.DocDate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
Hope this helps.