Hello D365 community,
After quite some research and ultimately trial and error, I was able to replicate our daily on hand quantities and costs from D365's Inventory Aging Report using just the InventTrans table with this logic:
CREATE VIEW [dbo].[fact_inventory_transactions] AS
SELECT
t1.qty - t1.qtysettled AS qty,
CASE
WHEN t1.statusissue = 1 OR t1.statusreceipt = 1
THEN t1.costamountposted + t1.costamountadjustment
WHEN t1.statusissue = 2 OR t1.statusreceipt = 2
THEN t1.costamountphysical
ELSE NULL
END AS cost,
FROM inventtrans t1
WHERE
t1.statusissue IN (1, 2) OR t1.statusreceipt IN (1, 2);
For context, I'm not in finance -- I'm a data engineer working with Fabric SQL off linked D365 base tables.
I think I understand the logic behind on hand cost, but what is the logic behind on hand quantity? Why qty - qtysettled?
It feels wrong, but the numbers seem to match up to the inventory value aging report (as with cost).
Thank you!
Jamie
