Has anyone been able to create a BAQ that returns the same results as the AP Advance Payment Balance Report? I found the field PODetail.AdvancePayBal but the value in this field does not match the Epicor report. I was asked to create a dashboard of this reports results along with additional information but i’m stuck just getting the report to balance to my BAQ.
Thanks in advance…
Hi Ron,
I was just building a BAQ for this and using the fields in your comment tied out to the report. I am curious, does you company make partial payments in advance?
Hi Ryan, Yes that is something that would happen on occasion.
Did you ever find a solution for this? I wonder if that is what caused it. The canned report doesn’t display the correct Advanced Balance amount with the correct PO line for us so the BAQ I have is much more useful. I am currently trying to figure out why we have one scenario where a closed PO is still on the report and balances to the GL. The $0 invoice that closed it has no tranGLC that would normal reverse it out of the advanced billing account.
After working on this for some time we moved on without ever getting it to work.
If you didn’t try it, you could join APInvDtl to the PODetail and then pull in the Ext Amounts from the invoice and they should tie out.
select
[Vendor].[VendorID] as [Vendor_VendorID],
[PODetail].[PONUM] as [PODetail_PONUM],
[PODetail].[POLine] as [PODetail_POLine],
[PODetail].[PartNum] as [PODetail_PartNum],
[PODetail].[AdvancePayBal] as [PODetail_AdvancePayBal],
[PODetail].[OrderQty] as [PODetail_OrderQty],
[PODetail].[PUM] as [PODetail_PUM],
[PODetail].[UnitCost] as [PODetail_UnitCost],
[PODetail].[ExtCost] as [PODetail_ExtCost],
(Case
When PODetail.OpenLine = ‘TRUE’ then ‘OPEN’
else ‘CLOSED’
End) as [Calculated_Open],
[APInvDtl].[InvoiceNum] as [APInvDtl_InvoiceNum],
[APInvDtl].[InvoiceLine] as [APInvDtl_InvoiceLine],
[APInvDtl].[ExtCost] as [APInvDtl_ExtCost]
from Erp.PODetail as PODetail
inner join Erp.POHeader as POHeader on
PODetail.Company = POHeader.Company
and PODetail.PONUM = POHeader.PONum
inner join Erp.Vendor as Vendor on
POHeader.Company = Vendor.Company
and POHeader.VendorNum = Vendor.VendorNum
inner join Erp.APInvDtl as APInvDtl on
PODetail.Company = APInvDtl.Company
and PODetail.PONUM = APInvDtl.PONum
and PODetail.POLine = APInvDtl.POLine
where (PODetail.AdvancePayBal <> 0)
Thank You, much appreciated. I’ll have a look at that.
You’re welcome, I threw in all the ext price fields so you can see what works for you. Hopefully, it does.