Im trying to get a list of invoices and vouchers AND include the date that it was actually paid.
-- AR
SELECT TOP 100 * FROM dbo.RM20101 r ORDER BY r.GLPOSTDT DESC
SELECT TOP 100 * FROM dbo.RM30101 r ORDER BY r.GLPOSTDT DESC
-- Paid date = POSTDATE ? or GLPOSTDT?
-- AP
SELECT TOP 100 * FROM PM20000 p ORDER BY p.DOCDATE DESC
SELECT TOP 100 * FROM PM30200 p ORDER BY p.DOCDATE DESC
I guess its not quite that easy since there could be multiple apply records assocaited with an invoice? So then I started looking a bit deeper, and found this.
https://victoriayudin.com/2008/10/22/sql-view-ap-apply-in-gp/
So where the last join happens for P2, eg.
left outer join
(select VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE
from PM20000
where DOCTYPE > 3 and VOIDED = 0
union all
select VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE
from PM30200
where DOCTYPE > 3 and VOIDED = 0)
This is the group that the payment date is pulled from, the docdate from either PM2000 or PM30200
Is it safe to say that if a voucher is in the history (PM30200) table, then its been "paid" in full?
However, what I need now is something similar for the AR side. Could I do something similar, except using the RM20101 and RM30101 tables?
