Help with a query for Applied Transactions against Invoices

[Former Member]

All,

I'm working on a commission report based on payments against an invoice, as well as existing credits.

I want to obtain all applied transactions in a given date range for a given invoice(s).

I was able to figure out how to get a sum of payments for a date range from the query below (see QUERY 1) and it seems to work. However, It will not return an applied amount originating from a credit memo.

Query 2, below, returns an applied partial amount related to an invoice but gives no indication of it's origin, or that it is a applied from a credit.

What I would really want is a list of all the applied transactions in a date range, and their origin, if from a credit, a payment, etc. for a given invoice. In other words I would pretty much like to mimic the data in the applied transaction window that appears when you select "Applied Transactions" from a popup menu in the invoice window.

I hope I have made myself clear. Can anyone give me a direction or a hint?

Thanks,

Mark

*****************************************************

QUERY 1:

SELECT
T2.[DocNum] InvoiceNo,
isnull(sum(T1.[BfDcntSum]), 0.00) as PmntsToDateWithin
FROM HEARTWOOD.DBO.ORCT T0 INNER JOIN HEARTWOOD.DBO.RCT2 T1 ON T0.[DocEntry] = T1.[DocNum]
Left Outer Join HEARTWOOD.DBO.OINV T2 ON T1.[DocEntry] =T2.[DocEntry] And T0.[CardCode] =T2.[CardCode]
INNER JOIN HEARTWOOD.DBO.OSLP T3 ON T2.[SlpCode] = T3.[SlpCode]
INNER JOIN HEARTWOOD.DBO.OACT T4 ON T2.[CtlAccount]= T4.[AcctCode]
WHERE T2.[CANCELED] ='N' And T0.[Canceled] ='N'
and T0.DocDate between '1/1/2018' and '3/27/2018'
and isnull(T2.U_HWC_SSOrderID, '') <> ''
Group By T2.[DocNum]

**********************************************************************************

QUERY 2:

select t2.DocNum, t1.ReconNum, t1.ReconDate, t1.Total,
sum(t1.Total)-sum(t0.reconsum) as 'Open Amount',
t0.reconsum
from OITR t1
inner join ITR1 t0 on t1.reconnum= t0.reconnum
inner join OINV t2 on t2.TransId = t0.TransId
where t2.DocNum = 69351
and isnull(T2.U_HWC_SSOrderID, '') <> ''
group by t2.DocNum, t1.ReconNum, t1.ReconDate, t1.Total, t0.reconsum

[Johan_H]

Hi Mark,

Just a thought right off the bat about the first query: you are starting the FROM clause with the payments table ORCT. That means that all results have to be in payments.

If the basic premise of your query is to find out how an invoice was payed (be it through payment or credit note) you should start off with OINV and outer join all tables like OCRT and ORIN. It is also important that you outer join any and all other tables that are not joined only to OINV.

FROM OINV T2 
INNER JOIN OSLP T3 ON T2.[SlpCode] = T3.[SlpCode] /* <-- this is ok */
LEFT OUTER JOIN RCT2 T1 ON T2.[DocEntry] =T1.[DocEntry] 
INNER JOIN ORCT T1 ON T0.[DocEntry] = T1.[DocNum] And T2.[CardCode] = T0.[CardCode] /* <-- this is not ok, it should be outer join */

Regards,

Johan