How do I see an old AVG cost on a Purchase Recept

Hi @Michael Hansen !

Yes, you’re correct.  In your original post you asked how to get it from that table. Unfortunately that table only provides it for the FinPeriod. 
 

If you’d like to see this data as the transactions come into the system, a transaction-by-transaction basis (rather than by period), you’ll want to focus on tables that track individual inventory transactions and their impact on inventory cost. The INItemCostHist table is indeed period-focused and won’t give you the granularity you need. Instead, consider the following tables:

    1.    INTran Table: This table records individual inventory transactions, including receipts. Each record represents a transaction and includes fields for the item, quantity, unit cost, etc.
    2.    INCostStatus Table (or INItemCostStatus): You mentioned this table. It tracks the quantity on hand and total cost for each inventory item, but as you noted, it doesn’t directly provide average cost. However, it can be used in conjunction with INTran to calculate the average cost at the time of each transaction.

Here’s how you might approach this:

    •    Use the INTran table to get the details of each receipt transaction.
    •    Use the INCostStatus table to get the total cost and quantity on hand before and after each transaction.
    •    Calculate the average cost for each transaction based on the data from these tables.

At this point if you’re a PCS customer, it might even be easier to pull this from SQL directly:

A simplified version of the SQL might look like this:

SELECT 
    INTran.RefNbr AS ReceiptNumber,
    INTran.InventoryID,
    INTran.TranCost,
    INTran.Qty,
    (INCostStatus.TotalCost - INTran.TranCost) / (INCostStatus.QtyOnHand - INTran.Qty) AS PreviousAvgCost,
    INCostStatus.TotalCost / INCostStatus.QtyOnHand AS NewAvgCost
FROM 
    INTran
JOIN 
    INCostStatus ON INTran.InventoryID = INCostStatus.InventoryID
WHERE 
    INTran.TranType = 'RCT' -- Receipt transaction type

Consider the following:

    •    Transaction Timing: You’re interested in the average cost immediately before and after each receipt. This requires careful timing in your joins and calculations. You might need to consider the sequence of transactions within the same day or period to accurately calculate the average cost before and after each receipt, especially across many vendors.
    •    Data Volume and Performance: Depending on the volume of data and the specific setup of your Acumatica instance, these queries can be complex and potentially slow. It’s essential to test them thoroughly.
    •    Costing Method: The exact calculation for average cost may vary depending on the specific costing method in use (like FIFO, LIFO, etc.). Ensure that your calculation aligns with the costing method your organization uses.

As always, due to the complexity and potential for impacting critical financial data, consider working with an Acumatica VAR or experienced consultant or a database specialist, especially for custom queries or reports. I hope this helps.