I’m trying to create an AP Aged Invoice BAQ with a parameter for AsOfDate and the criteria of… ApplyDate <= AsOfDate and (ClosedDate > AsOfDate or OpenPayable = true)
The problem is I cannot find a ClosedDate on the APInvHed table like there is on the AR InvcHead table.
If I cannot find a ClosedDate the only other thing I can think of is try to determine when the AP Invoice was closed by when the last payment matched the invoice amount.
I’ve already looked at the zHomepage_APAgedPayables system BAQ but that BAQ is using Constrants.Today for the Date which works fine without a ClosedDate… however, this doesn’t work if you need an AsOfDate in the past.
Does anyone know if there is a ClosedDate for AP Invoice on the APInv tables or another table that I’m missing or any suggestions how to determine the ClosedDate?
Just to be clear this is for Accounts Payable Aging… I was already able to create a AR Aged Invoice BAQ because I was able to find the ClosedDate on the AR InvcHead table.
Would APInvHed.ChangeDate work if it is closed?
I have used APTran - summarize by InvoiceNum and look for Max(APTran.TranDate)
Balance of invoice will be Invoice Amount - Sum(APTran.TranAmt)
Join this to the APHead - should get you closed date for all APInvHed.InvoiceBal = 0
So I used a query to validate the APInvHed.ChangeDate vs the APTran.TranDate and I found some records where the ChangeDate was before the Tran Date… this is because the TranDate is set from the Payment Date which can be in the future.
Correction…
I did some testing on the Aged Payables system report with payments that were made in the future and the Aged Payables system report uses the APTran.TranDate (not the ChangeDate) for the AsOfDate for the cutoff date.
I was able to make the BAQ match the Aged Payables report by using the APTran table in a subquery with max(APTran.TranDate) to filter the top level query and get a total payment amount.
However, I needed to use the calculation below in a calculated field for the Total Payment Amount to get the correct balance when using a AgedAsOf date in the past. Then I set a relationship of APInvHed.InvoiceAmt <> TotalPaymentAmount field in the top level query.
(sum(
case
when APTran.TranAmt is null then 0
when APTran.TranDate > @AgedAsOf then 0
else APTran.TranAmt
end
)) as [Calculated_TotalPaymentAmt]
1 Like
