Announcements
Hi,
Would anyone know what tables in SmartList Builder are being used in the Great Plains Customer>Past Due Customers Smartlist?
Deb
*This post is locked for comments
Breaking down a stock SmartList can be tricky, or if you know of a way to do it let me know :)
As for the aging tables in Receivables Management all you need to look at is the RM00103 table. Depending on the structure of your aging buckets, you might be using more than we are and they might have different labels but you can easily change this to match your infrastructure. This returns records for customers who's total balance in <> 0. You might want to try removing the where clause because you might find unapplied payments and credits, helps with cleaning up the aging.
CREATE VIEW [dbo].[Simple_Aging_View]
AS
SELECT TOP 100000
r1.CUSTNMBR AS CustomerNumber,
r2.CUSTNAME AS CustomerName,
r2.CUSTCLAS AS CustomerClass,
r1.CUSTBLNC AS Total_Due,
r1.AGPERAMT_1 AS Current_Period,
r1.AGPERAMT_2 AS '0-30 Days',
r1.AGPERAMT_3 AS '31-60 Days',
r1.AGPERAMT_4 AS '61 and over'
FROM RM00103 r1
left join RM00101 r2 ON r2.CUSTNMBR = r1.CUSTNMBR
WHERE r1.CUSTBLNC <> 0
ORDER BY r2.CUSTNAME ASC
Hi Deb,
The "friendly names" for the tables Tom is using are the RM Customer MSTR (RM00101) and Customer Master Summary (RM00103).
Regards,
Kirk
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
Jump in, show your community spirit, and win prizes!
Expanding mentorship, skilling, and AI innovation
These are the community rock stars!
Stay up to date on forum activity by subscribing.
Dan Liebl 2
Shravan Attelli 1
