Query for Invoices (paid/unpaid both) adjusted to incoming payment, CM, or External reconciliation

[krishnam_mathur]

Hi Team,

I need to make a query for a list of all the invoices(paid/unpaid both) along with the incoming payment, CM, or external reconciliation. I am able to get list of all invoices with incoming payment. But I need to add the CM adjustment or any external reconciliation.

SELECT t0.docentry, t0.cardcode as CardCode,t3.CardName,t4.GroupName,T0.DOCNUM AS InvoiceNo,T0.DOCDATE AS InvoiceDate,T0.DOCTOTAL AS InvoiceTotal, t0.DocDueDate,T2.DOCDATE AS IncPayDate, T1.SumApplied as Paid, (t0.doctotal)-(select sum(q.SumApplied) from rct2 q inner join oinv w on q.docentry = w.docentry where q.DocEntry = t1.DocEntry and q.DocNum <= t2.docnum) as Pending, T2.DOCNUM IncPayNo, t5.DocTotal FROM OINV T0 LEFT JOIN RCT2 T1 ON T0.DOCENTRY=T1.DOCENTRY AND T0.OBJTYPE=T1.INVTYPE LEFT JOIN ORCT T2 ON T2.DOCNUM=T1.DOCNUM inner join ocrd t3 on t3.CardCode=t0.CardCode inner join ocrg t4 on t4.GroupCode=t3.GroupCode where ( T0.[DocDate] >= '2019-01-01') and ( T0.[DocDate] <= '2019-12-12' ) and t0.docnum='2019008007' order by t0.docdate

Can anyone please help on this....?

[msundararaja_perumal]

Please use union all to accomodate multiple conditions and use stored procedure instead of direct query, thanks.

[jitin_chawla]

Hi, why do you want to have external reconciliation information in your query? do you want to say Internal reconciliation? the best way which i can think is to prepare a query based on the Invoice tables, Credit Memo tables, Payments table and Reconciliation table. The crux of all the reconciliation whether it is credit memo, payment or manual reconciliation you will find it that. Joining these would be much easier.