I’m trying to build a BAQ that would perform the following:
Calculate the total PO amount - (debit memos that have been applied to AP invoices linked to that PO, these debit memos are not linked directly to the PO)
This would give me the true amount of the PO after reducing the total amount of debit memo applied to it.
I would need data from PO header as the display , and an additional calculated field which would total up all debit memo amount applied to invoices that are linked to the PO. (currently stuck at this stage, more caffeine required)
my current BAQ looks like this, which seems a bit lost in nowhere as I’m still thinking of how to set the calculated field up.
I’m noticing that for debit memos in our system, the PO number is stored in the APInvHed.REFPONum field, and the APInvDtl.PONum for those invoices are set to 0. That may be why you aren’t getting any rows returned.
So you could try joining on APInvHed.REFPONum rather than APInvDtl.PONum.
Also, you may need to have 2 subqueries. One for the APTran totals grouped on APInvHed.REFPONum and one for your PO totals. Then you can join them both in a top level query and do your final calculation there.
Thank you very much for your sharing!
I found out that most of our debit memos are not linked to any PO, they are by vendor instead.
Upon making the payment, they would then utilize this debit memo to offset whichever AP invoices, as long as it is from the same vendor.
If this is a 1 to 1 scenario, I might be able to build a BAQ to retrieve those data.
However, I found out that one debit memo might be applied along with several invoices as a whole, this is where it became tricky for my scenario
