Dynamics Community Forum Thread Details

Inventory Aging report - On-hand value in SQL

Hi all!

Does anyone know how to calculate On-hand value from the Inventory Aging report using SQL tables?

I was trying with inventtrans, inventsum, inventsettlement but I can't get the numbers to match

Categories:

Hi there! Good morning, evening, or afternoon - depending on where you are :) Hope you are well today!

To calculate the On-hand value from the Inventory Aging report using SQL tables in Dynamics 365, you need to ensure that you're correctly combining data from the relevant tables and applying the appropriate logic. Here's a step-by-step guide to help you:
1. Understand the Key Tables

InventTrans: Contains inventory transactions, including receipts, issues, and adjustments.
InventSum: Holds aggregated on-hand inventory quantities and values.
InventSettlement: Tracks settlements between inventory transactions, which are crucial for reconciling financial and physical inventory values.

2. Basic SQL Query Structure

Start by joining the InventTrans and InventSum tables to get the on-hand quantity and value for each item. Use the InventDimId and ItemId fields as the linking keys.

Example:
SELECT
    it.ItemId,
    it.InventDimId,
    SUM(CASE WHEN it.TransType = 'Receipt' THEN it.Qty ELSE -it.Qty END) AS OnHandQty,
    SUM(CASE WHEN it.TransType = 'Receipt' THEN it.CostAmountPhysical ELSE -it.CostAmountPhysical END) AS OnHandValue
FROM
    InventTrans it
JOIN
    InventSum isum ON it.ItemId = isum.ItemId AND it.InventDimId = isum.InventDimId
WHERE
    it.StatusIssue = 0 -- Only include open transactions
GROUP BY
    it.ItemId, it.InventDimId;
3. Incorporate Settlements

Use the InventSettlement table to adjust the on-hand value based on settled transactions. This ensures that the financial value matches the physical inventory.

Example:
SELECT
    it.ItemId,
    it.InventDimId,
    SUM(it.Qty) AS OnHandQty,
    SUM(it.CostAmountPhysical + COALESCE(iset.SettlementAmount, 0)) AS OnHandValue
FROM
    InventTrans it
LEFT JOIN
    InventSettlement iset ON it.TransRecId = iset.TransRecId
WHERE
    it.StatusIssue = 0
GROUP BY
    it.ItemId, it.InventDimId;
4. Consider Inventory Closing

If inventory closing has been performed, ensure that you account for adjustments made during the closing process. These adjustments are reflected in the CostAmountAdjustment field in InventTrans.

5. Validate Against the Report

Compare the results of your SQL query with the Inventory Aging report to identify any discrepancies. Ensure that your query includes all relevant dimensions (e.g., site, warehouse) and filters.

For more detailed guidance, you can refer to the official documentation on Inventory Aging reports.

Quick Links