Solved

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

  • 25 January 2024
  • 5 replies
  • 65 views

Userlevel 5
Badge

I would like to pull a Purchase Receipt line for an item and know what the avg cost was at that time (so I can see the change in avg cost over time). What table has this info and how do I get there from the POReceiptLine table?

Version: 22.210.0020 

 

Thank you!

icon

Best answer by jamesh 25 January 2024, 19:24

View original

5 replies

Userlevel 6
Badge +6

Hi @Michael Hansen !

For this, I would create a GI.  

Given your version of Acumatica (22.210.0020), here's a general approach you can take:

1. Identify the Relevant Tables:

  • POReceiptLine: This table contains details about the receipt lines. You might already have the necessary details like inventory ID and receipt date from this table.
  • INItemCostHist: This is the historical table that stores the inventory cost data. This table can be used to track the historical average costs.
  • INCostStatus: This table holds information about the current inventory cost status.

2. Associate Data:

  1. From POReceiptLine to INItemCostHist:

    • Use the InventoryID from POReceiptLine to identify the item in the INItemCostHist.
    • Filter the INItemCostHist records by the InventoryID and the date range you are interested in. This will give you the historical cost data for that item.
  2. Understanding Average Cost:

    • The INItemCostHist table should provide fields for the quantity on hand and the total cost, which can be used to calculate the average cost at different points in time.
    • You might need to consider the transactions in chronological order to understand how each receipt affected the average cost.
  3. Correlating with Receipt Date:

    • You will need to correlate the receipt date from POReceiptLine with the cost history data in INItemCostHist to analyze how each purchase receipt impacted the average cost.
Userlevel 5
Badge

Thank you Jamesh!

 

INItemCostHist is by period, I need this by transaction. I have multiple receipts in the same period, which should have different avg costs. Do we know of a table who holds this data by transaction? I need to get this data out of Acumatica:

Recepit #1 - Item #1 - Unit Cost = $6 - Avg Cost = $5
Receipt #2 - Item #1 - Unit Cost = $7 - Avg Cost = $5.50

etc..

These reciepts might even be on the same day. I thought INItemCostStatus would have what I need, it takes in the receipt number and the inventory ID, but it doesn’t have an AVG COST field to grab.

 

Userlevel 6
Badge +6

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. 

Userlevel 5
Badge

Thank you Jamesh!

Userlevel 6
Badge +6

My pleasure:) 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved