Hello everybody,
I am looking for a BAQ report to show Aging Payables in Epicor. Basically like the report I have added which we are using for Account Receivables.
BvZ_Aging_Report.baq (31.4 KB)
I am not very skilled with Epicor BAQ, so I am hoping to find a (similar) report for Aged Payables. So far I have no luck finding one, and I am much abliged if anyone can send me a copy of a Aged Payables BAQ report.
Many thanks in advance.
There’s an out-of-the-box BAQ for aged payables called: zHomepage_APAgedPayables
Perhaps start by copying that one (you can’t edit because its a system BAQ) and making any changes you require.
Hi David,
Thank you for your reply. I have searched, but we do not have zHomepage_APAgedPayables in the list of system query’s.
Wow. I had assumed a system BAQ would be on everyone’s environment. Are you including shared queries in your search?
Not doubting you… just a little shocked.
That is the first thing I do 
. But check below screenshot.
.
Is it possible you send me a copy?
Perhaps they’re country specific? Or perhaps they’re linked to a specific module? Dunno.
I exported it (below) if you’re able to pull it into a test environment to see if it functions. There was a securityID in place (finance), so not sure if that is going to come over with it and block anything.
zHomepage_APAgedPayables.baq (39.0 KB)
Thanks, but I get a error message when importing.
I think that is the reason we do not have this BAQ.
Well, I don’t know that its a new query… its just my instance version may be later than yours so it won’t allow you to use it.
Let me take some screen shots, if you wish to recreate it.
with [APInvSchedLastDueDate] as
(select
[APInvSched].[Company] as [APInvSched_Company],
[APInvSched].[VendorNum] as [APInvSched_VendorNum],
[APInvSched].[InvoiceNum] as [APInvSched_InvoiceNum],
(max( APInvSched.PayDueDate )) as [Calculated_LastPaymentDueDate]
from Erp.APInvSched as APInvSched
group by [APInvSched].[Company],
[APInvSched].[VendorNum],
[APInvSched].[InvoiceNum])
select
[APInvHed].[Company] as [APInvHed_Company],
[Vendor].[VendorID] as [Vendor_VendorID],
[Vendor].[Name] as [Vendor_Name],
[Vendor].[GroupCode] as [Vendor_GroupCode],
[VendGrup].[GroupDesc] as [VendGrup_GroupDesc],
[APInvHed].[InvoiceNum] as [APInvHed_InvoiceNum],
[APInvHed].[LegalNumber] as [APInvHed_LegalNumber],
[APInvHed].[PrePayment] as [APInvHed_PrePayment],
[Country].[Description] as [Country_Description],
[APInvHed].[InvoiceDate] as [APInvHed_InvoiceDate],
[APInvHed].[DueDate] as [APInvHed_DueDate],
[APInvHed].[ApplyDate] as [APInvHed_ApplyDate],
[APInvSchedLastDueDate].[Calculated_LastPaymentDueDate] as [Calculated_LastPaymentDueDate],
(APInvHed.InvoiceBal) as [Calculated_AgedAmount],
(case
when (Constants.Today - APInvSchedLastDueDate.Calculated_LastPaymentDueDate >= AgingRptFmt.AgeDays1 and Constants.Today - APInvSchedLastDueDate.Calculated_LastPaymentDueDate < AgingRptFmt.AgeDays2) then AgingRptFmt.AgeLabels1
when (Constants.Today - APInvSchedLastDueDate.Calculated_LastPaymentDueDate >= AgingRptFmt.AgeDays2 and Constants.Today - APInvSchedLastDueDate.Calculated_LastPaymentDueDate < AgingRptFmt.AgeDays3) then AgingRptFmt.AgeLabels2
when (Constants.Today - APInvSchedLastDueDate.Calculated_LastPaymentDueDate >= AgingRptFmt.AgeDays3 and Constants.Today - APInvSchedLastDueDate.Calculated_LastPaymentDueDate < AgingRptFmt.AgeDays4) then AgingRptFmt.AgeLabels3
when (Constants.Today - APInvSchedLastDueDate.Calculated_LastPaymentDueDate >= AgingRptFmt.AgeDays4 and Constants.Today - APInvSchedLastDueDate.Calculated_LastPaymentDueDate < AgingRptFmt.AgeDays5) then AgingRptFmt.AgeLabels4
when (Constants.Today - APInvSchedLastDueDate.Calculated_LastPaymentDueDate >= AgingRptFmt.AgeDays5) then AgingRptFmt.AgeLabels5
else AgingRptFmt.AgeLabels6
end) as [Calculated_AgedColumn],
(APInvHed.Rpt1InvoiceBal) as [Calculated_Rpt1AgedAmount],
(APInvHed.Rpt2InvoiceBal) as [Calculated_Rpt2AgedAmount],
(APInvHed.Rpt3InvoiceBal) as [Calculated_Rpt3AgedAmount]
from Erp.APSyst as APSyst
inner join Erp.AgingRptFmt as AgingRptFmt on
APSyst.Company = AgingRptFmt.Company
and APSyst.FmtCode = AgingRptFmt.FmtCode
inner join Erp.APInvHed as APInvHed on
AgingRptFmt.Company = APInvHed.Company
and ( APInvHed.OpenPayable = true and APInvHed.Posted = true and APInvHed.InvoiceDate <= Constants.Today and APInvHed.InvoiceBal <> 0 )
inner join Erp.Vendor as Vendor on
APInvHed.Company = Vendor.Company
and APInvHed.VendorNum = Vendor.VendorNum
left outer join Erp.Country as Country on
Vendor.Company = Country.Company
and Vendor.CountryNum = Country.CountryNum
left outer join Erp.VendGrup as VendGrup on
Vendor.Company = VendGrup.Company
and Vendor.GroupCode = VendGrup.GroupCode
inner join APInvSchedLastDueDate as APInvSchedLastDueDate on
APInvSchedLastDueDate.APInvSched_Company = APInvHed.Company
and APInvSchedLastDueDate.APInvSched_VendorNum = APInvHed.VendorNum
and APInvSchedLastDueDate.APInvSched_InvoiceNum = APInvHed.InvoiceNum
Query List:
CTE Query:
TOP LEVEL QUERY:
Criteria on APInvHed:
Display and Calculated field details would be in the above posted query code.
I’m guessing here… but by the name of the query (having “Homepage” in it). I wonder if it is related to EDD which did not come default in 10.2.500?
ahh… that would make sense. Thanks Ernie!
Many thanks for the help everyone.
I have also figured out why I could not see this BAQ. It was a security issue on the Finance BAQs. I have asked our system manager to fix this so I can access the Finance BAQs.
@itsme : Thank you for the BAQ, but there is also a error message due to security access.
Interesting. I assumed you’d still be able to SEE the query in the search, just not access it and/or use it. But I guess the security doesn’t even allow it to show in your search. Good to know!
