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.
Chintan Panchal replied
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.