Hi all. New to M3. Looking to build a report that shows the CustNum, InvNum, InvAmt. Payment Amt, Payment Date and Open Balance. It looks like ARS200 has most of this but I can't find the tables they live on. Can anyone give me some guidance?
Hi,
FSLEDG and FGLEDG.
bye
Hello, I checked both of those tables and neither have an open balance column on them. Can you elaborate?
Jason, this isn't as straightforward as it should be. You need to look at FGLEDG and compare matching voucher records with TRCD = 10 (AR invoices) against those with TRCD = 20 (AR payments) to calculate the open balance. There's a bit more to it, I think, if you have cash discounts or short pays, etc. Join to FSLEDG as needed to get supporting info.
In theory you should be able to SQL on FSLEDG with RECO<>9 and get a list of open invoices as per now.
However, the RECO is not 100% reliable (M3 does not always populate the RECO right). So querying FSLEDG for all records and sum(CUAM) with a group by CINO, CUNO etc is better.
If you need to make a cutoff date, i.e having the list by a certain date, you need to join FGLEDG and set the cutoff date on EGACDT in FGLEDG.
