Payment File Query

[Former Member]

Hi all,

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.

[Former Member · Accepted solution]

Hi,

first of all you can find outgoing payment by vendor in outgoing payment form. You need to go to find mode and search by vendor. And add appropriate fields from form settings.

You can also use system query under queries section name: cheque for payments in date cross selection report. You can also amend the query as per your need.

If you have a any specific requirements then post your query here to amend it accordingly.

Hope it helps.

Regards,

Chintan

[Former Member · Accepted solution]

Hi Chintan,

Please refer to my query below. When I tried to run the query, the results that came out does not tally with the actual payment posted for that day, please advise.

SELECT T1.[SumApplied], T0.[CardCode], T0.[CardName] FROM OCRD T0 , VPM2 T1 INNER JOIN OVPM T2 ON T1.[DocNum] = T2.[DocEntry] WHERE T2.[DocDate] between [%0] and [%1]

[Former Member · Accepted solution]

Hi,

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.

Regards,

Chintan

[Former Member · Accepted solution]

Hi Chintan,

Yes it helps out alot, thank you. However the date posting query formula (OVPM.DocDate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))) is automatically fixed by the computer date. How should we amend it as a user input field whenever this query is run? Thanks.

[Former Member · Accepted solution]

Hi,

Instead of last line

and OVPM.DocDate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

you should have

and OVPM.DocDate Between [%0] and [%1]

[kothandaraman_nagarajan]

Hi,

Please post your query here to check and advice.

Thanks & Regards,

Nagarajan

[Former Member]

Hi Nagarajan,

Please refer to my query below. When I tried to run the query, the results that came out does not tally with the actual payment posted for that day, please advise.

SELECT T1.[SumApplied], T0.[CardCode], T0.[CardName] FROM OCRD T0 , VPM2 T1 INNER JOIN OVPM T2 ON T1.[DocNum] = T2.[DocEntry] WHERE T2.[DocDate] between [%0] and [%1]