Daily Outgoing Payment Report

[Former Member]

Hello,

We have a Query shown below that allows us to view Incoming payments made each day to an invoice.

SELECT T2.CardCode [BP Acct#], T2.CardName [BP Company Name], T2.DocNum [Invoice #], T1.DocDate [Receipt Date], T2.DocTotal [Invoice Sum], T0.DcntSum, T0.SumApplied AS 'Paid to Invoice' FROM [dbo].[RCT2] T0 INNER JOIN [dbo].[ORCT] T1 ON T1.DocNum = T0.DocNum INNER JOIN [dbo].[OINV] T2 ON T2.DocEntry = T0.DocEntry WHERE T1.DocDate = '[%0]'

We would like to modify this query so that the last column of information shows the Outgoing payments made. Meaning, any amount of money that we owe a customer and therefore apply to their outstanding invoice or as a credit memo, etc.

Can someone show us how to do this modification?

Thanks!

[Former Member · Accepted solution]

Mike,

Try this, this is your query, and it works perfect on my DB

1) SELECT T2.CardCode BP, T2.CardName CompanyName, T2.DocNum Invoice, T1.DocDate ReceiptDate, T2.DocTotal InvoiceSum, T0.DcntSum, T0.SumApplied AS 'Credit to Invoice'

FROM dbo.VPM2 T0 INNER JOIN dbo.OVPM T1 ON T1.DocNum = T0.DocNum INNER JOIN dbo.OPCH T2 ON T2.DocEntry = T0.DocEntry

if this query give you results, the problem could it be in the WHERE clause "where T0.DocDate = '[%0]'

Because you are not inserting a valid value in the '[%0]', maybe you are writing a date that doesnt existe in the "Existing Values" buttom.

Slds,.

Esteban Martinez

[Former Member]

Hi Mike,

You can copy that query and change the table name contain "RCT" to "VPM and "INV" to "PCH". The description of each field should be changed accordingly.

Gordon

[Former Member]

You mean change RCT2 and ORCT to VPM or just change the RCT part of the name to VPM...so VPM2 and OVPM.

Also, change OINV to OPCH or PCH?

Thanks,

Mike

[Former Member]

RCT2 -> VPM2, ORCT -> OVPM etc.

[former_member583013]

SELECT T2.CardCode [BP Acct#\], T2.CardName [BP Company Name\], T2.DocNum [Invoice #\], T1.DocDate [Receipt Date\], T2.DocTotal [Invoice Sum\], T0.DcntSum, T0.SumApplied AS 'Paid to Invoice' FROM dbo.VPM2 T0 INNER JOIN dbo.OVPM T1 ON T1.DocNum = T0.DocNum INNER JOIN dbo.OPCH T2 ON T2.DocEntry = T0.DocEntry WHERE T1.DocDate = '[%0\]'

[Former Member]

Hi,

I just used this query --

SELECT T2.CardCode [BP Acct#], T2.CardName [BP Company Name], T2.DocNum [Invoice #], T1.DocDate [Receipt Date], T2.DocTotal [Invoice Sum], T0.DcntSum, T0.SumApplied AS 'Credit to Invoice' FROM [dbo].[VPM2] T0 INNER JOIN [dbo].[OVPM] T1 ON T1.DocNum = T0.DocNum INNER JOIN [dbo].[OPCH] T2 ON T2.DocEntry = T0.DocEntry WHERE T1.DocDate = '[%0]'

but it doesn't seem to work.

When I look up existing values, there are records there but then when I execute the Query no records are retrieved?

Thanks,

Mike

[Former Member]

Hi Mike,

I have checked your last post. Your query works fine in our database.

You can run a query select DocEntry, SumApplied, AppliedFC

from rct2 where DocEntry = '[%0]' to see which field in your RCT2 table contains your payment data.

Gordon

[Former Member]

You may also change the date selection in a range to check at least you can get some data.

[Former Member]

Yes, tried different date selections but didn't get anything even though all dates show at least 1 record retrieved.

In your earlier posting, I tried to enter that Query to see which table contains the data but not sure if I entered it correctly -- this is what I pasted:

SELECT DocEntry, SumApplied, AppliedFC from rct2 where DocEntry = '[%0]'

Does that make sense?

Thanks,

Mike

[Former Member]

You need to replace the selection with fixed invoice# like 'xxxxx' instead of '[%0]'

[Former Member]

Hi --

I think the Query is searching for the wrong thing or looking in the wrong table.

We are trying to find a listing of refunds that have been applied either to an invoice or credit memo.

So it has the following headers:

BP Acct #...BP Company Name...Doc # (Invoice or Credit Memo)...Date...Invoice Sum...Discount...Refund Amt

Does this make more sense?

Thanks!

Mike

[Former Member]

In this case, you need table "RIN" instead of "PCH". Try it now.