Our financial officer requested a GI/report that allows him to see the amount of money we spent on goods for each month for each branch.
I have gone around and around with APPayment, APTran, and APInvoice tables and I haven’t been able to find a way that allows me to get
Payment Date (when the check was printed), Payment amount, which inventory items this payment was associated with (I was using the APTran table for this), and Branch (based on the APTran table)
It seems like the inventory ID dissapears as soon as a payment has a closed status. I need the inventory ID in order to filter out which items are not goods or cost of good inventory items.
Unless there is another approach that I am not utilizing.
Solved
Financial Data on Cost Of Goods Paid
Best answer by abhimanyuprajapati52
You’re running into this because payments in Acumatica are not tied to inventory items. Once a bill is paid and closed, the payment record does not retain InventoryID — it only settles the AP document.
If the goal is to report Cost of Goods by month and branch, the correct approach is to build the GI from GLTran and filter on your COGS accounts. That reflects the true financial cost by branch and financial period.
If instead the goal is to report vendor spending on inventory items, then use APTran filtered where InventoryID is not null, and group by invoice date (not payment date). Payments cannot reliably be used for item-level reporting.
You’re running into this because payments in Acumatica are not tied to inventory items. Once a bill is paid and closed, the payment record does not retain InventoryID — it only settles the AP document.
If the goal is to report Cost of Goods by month and branch, the correct approach is to build the GI from GLTran and filter on your COGS accounts. That reflects the true financial cost by branch and financial period.
If instead the goal is to report vendor spending on inventory items, then use APTran filtered where InventoryID is not null, and group by invoice date (not payment date). Payments cannot reliably be used for item-level reporting.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.