On the AR Customer aging report, what table is the customer balance coming from, I can't get the numbers for current, 45-60,91-120 and 120+ to equal that total.
It can be found though GP->SmartList->sales->Customers->AR Customer aging.
I need to find the customer balance.
*This post is locked for comments
I would like to intrude a shed a light on this issue as it is important. There is no type of invoices as current or 0-30 bucket, it is a relative issue depending on the cut of date you define, which usually shows in the Historical Aged Trial Balance (Age, as of __ _ _ )
The cut of date you define will define in which range invoices will fall. Let's try to get this clear through a simple example,
For the standard aging periods, (0-30 , 31-60 , 61-90 , 90 and over )
Now, if you print the report and age your receivable as of (20/2/2014), the invoice will show under the 0-30 bucket. Meanwhile, if you age as of (14/8/2014) the invoice will show under the (90 and over) bucket.
Now taking into consideration that aging buckets are configurable, it means that different buckets can be defined by the business. As for the (current) point in the SQL script, it is primarily related to invoiced which has a due date that is prior of the cut of date (not today), remember that the cut of date is a parameter that you pass on the historical aged trial balance. As for Ms. Victoria script, it is considering the current date, since she used the getdate() function, so the invoices are being aged as of today.
That's why Mr. Frank said initially it depends on your business and how you configure the buckets. In case you required any further in-depths references for deeper understanding from a business and accounting perspective, please let me know.
Please never hesitate to share any further inquiries,
In my point of view, if you post a invoice with the date of 08/14/2014 and same due date 08/14/2014. So, for this case the due period is 0. If you run a aging report, this invoice will comes under the current period. Current due period is used for 0 date duration.
May be sometime user may pay the invoice amount before the invoice is created. So, this case the due date may fallen before the invoice date & the due period comes with negative. This is rare case. You can use only = 0(instead of <=0) for current period in the SQL view mentioned above.
Hope this helps!!!
Thank you for answering my question about the query. What I am having trouble with is why we need a <0 for the current as far as accounting goes...I have to explain this to them. Are these invoices that are posted for a earlier due date that have not been posted? if so, why are they not in the other buckets.
I am trying to figure out what current means from an accounting standpoint. I don't understand what kind of invoices these are.
Boss, the due date for current period comes with <=0. So, don't include 0 for any other periods. Have a look on the below.
case
when DATEDIFF(d, RM.DUEDATE, getdate()) between 1 and 44
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) between 1 and 44
and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end [1-44],
case
when DATEDIFF(d, RM.DUEDATE, getdate()) <= 0
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) <= 0
and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
else 0
end [Current],
Hope this helps and you understand now!!!
I added a bit to the Victoria Yuden query suggested above
case
when DATEDIFF(d, RM.DUEDATE, getdate()) between 0 and 44
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) between 0 and 44
and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end [0-44],
This is for stuff with due date days between 0 and 44, how come 'current' doesn't show up in there. its like it uses negative numbers
case
when DATEDIFF(d, RM.DUEDATE, getdate()) <= 0
and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) <= 0
and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
else 0
end [Current],
I am sorry I am having trouble understanding this. Are these invoices posted today? if so they should be in the 0-44 bucket. are these backdated invoices?
Mine says 0 to 0. that makes no sense! what due date?
By default the "current" is used for the aging period from 0 to 30 based on either due date or document date of your receivable setup.
To check with your system, go to your Receivable Management Setup window (Tools-->Setup-->Sales-->Receivables) and find the period for the "Current".
Hope this helps!!!
Frank, I need to understand what this 'current' bucket is. it is based on due date in our system
Depends on how it's defined in your Receivables Setup window. Sales>>Setup>>Receivables Setup. It may either be defined based on Document Date or Due Date.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (