I am in a SaaS environment. I am trying to reverse engineer the aged receivable report. The reason is that i am trying to create my own query and dashboard for over due invoices. However, my query totals do not match up with the aged receivables report.
In the screenshot below shows an excel document that shows my query results. Just below the excel window is the aged receivable report from Epicor. You can see that the over 120 days matched exactly, but not the others. I cannot figure out what I’m missing.
What i noticed is that alot of invoices do not show up on my query comparing to what shows up on the aging report. I cannot figure out why. I barely have any criteria on the BAQ. See my design:
So my next question is, where does the aging report pull data from? Looking up the data definition does not help as it links to tables that are unknown.
I wonder if this BAQ is something what you are doing, try importing it and check - I remember I have a few around. See how this one compares.
With [InvoiceDetail] AS
(select
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
[InvcHead].[InvoiceDate] as [InvcHead_InvoiceDate],
[InvcHead].[DueDate] as [InvcHead_DueDate],
[InvcHead].[InvoiceAmt] as [InvcHead_InvoiceAmt],
[InvcHead].[InvoiceBal] as [InvcHead_InvoiceBal],
[InvcHead].[Company] as [InvcHead_Company],
[InvcHead].[CustNum] as [InvcHead_CustNum],
(DATEDIFF(Day, Invchead.DueDate, GETDATE())) as [Calculated_DaysOverdue],
(CASE
WHEN DaysOverdue < 0 THEN InvcHead.InvoiceBal
ELSE 0
END) as [Calculated_Future],
(CASE
WHEN DaysOverdue >= 0 AND DaysOverdue <=30 THEN InvcHead.InvoiceBal
ELSE 0
END) as [Calculated_Current],
(CASE
WHEN DaysOverdue >= 31 AND DaysOverdue <=60 THEN InvcHead.InvoiceBal
ELSE 0
END) as [Calculated_M1],
(CASE
WHEN DaysOverdue >= 61 AND DaysOverdue <=90 THEN InvcHead.InvoiceBal
ELSE 0
END) as [Calculated_M2],
(CASE
WHEN DaysOverdue >= 91 AND DaysOverdue <=120 THEN InvcHead.InvoiceBal
ELSE 0
END) as [Calculated_M3],
(CASE
WHEN DaysOverdue >= 121 THEN InvcHead.InvoiceBal
ELSE 0
END) as [Calculated_M4]
from Erp.InvcHead as InvcHead
inner join Erp.Customer as Customer on
InvcHead.Company = Customer.Company
And
InvcHead.CustNum = Customer.CustNum
where (InvcHead.InvoiceBal <> 0))
select
[InvoiceDetail].[InvcHead_Company] as [InvcHead_Company],
[InvoiceDetail].[InvcHead_CustNum] as [InvcHead_CustNum],
[InvoiceDetail].[Customer_CustID] as [Customer_CustID],
[InvoiceDetail].[Customer_Name] as [Customer_Name],
(sum( InvoiceDetail.InvcHead_InvoiceBal )) as [Calculated_TotalOS],
(SUM(InvoiceDetail.Calculated_Current)) as [Calculated_Curr],
(SUM(Calculated_Future)) as [Calculated_Fut],
(SUM(InvoiceDetail.Calculated_M1)) as [Calculated_TLM1],
(SUM(InvoiceDetail.Calculated_M2)) as [Calculated_TLM2],
(SUM(InvoiceDetail.Calculated_M3)) as [Calculated_TLM3],
(SUM(InvoiceDetail.Calculated_M4)) as [Calculated_TLM4],
(InvoiceDetail.Customer_CustID + ':' + InvoiceDetail.Customer_Name) as [Calculated_DBT]
from InvoiceDetail as InvoiceDetail
group by [InvoiceDetail].[InvcHead_Company],
[InvoiceDetail].[InvcHead_CustNum],
[InvoiceDetail].[Customer_CustID],
[InvoiceDetail].[Customer_Name]
Epicor’s standard aging report has two options… one to age based on the invoice date… and one that is aged based on DUE date… so you can get two completely different sets of results. ALSO, if you use any of the complicated payment terms options and using the DUE Date calculation, it probably gets even more complicated to calculate the aging.
In your BAQ, you do not seem to be looking at the Invoice payment schedule… table = InvcSched:
