SAP B1 Inventory costing report using IPF1 and OITW tables

[davidrobinson-1]

I wish to produce a query/report that shows the average cost price of ITEMS for the current inventory ONHAND.

As such, I wish to ignore the cost of inventory already sold.

I have written the following basic query looking at the landed cost table (IPF1) and the warehouse table (OITW): -

SELECT TOP 5 T0.DocEntry, T0.ItemCode, T0.Dscription, T0.Quantity, T0.WhsCode, T0.PriceFOB, T0.Currency, T0.PriceAtWH, T0.Rate, T0.FactNoCust, T1.OnHand, T1.OnOrder

FROM IPF1 T0

INNER JOIN OITW T1

ON T0.ItemCode = T1.ItemCode AND T0.WhsCode = T1.WhsCode

WHERE T0.ItemCode = 'V227TCS5'

ORDER BY T0.DocEntry DESC

DocEntry ItemCode Dscription Quantity WhsCode PriceFOB Currency PriceAtWH Rate FactNoCust OnHand OnOrder

1873 V227TCS5 Egg Pot Set 5 80.000000 MEL 27.640000 USD 77.190000 0.650000 81.540000 167.000000 80.000000

1864 V227TCS5 Egg Pot Set 5 80.000000 MEL 27.640000 USD 81.640000 0.690000 103.790000 167.000000 80.000000

1862 V227TCS5 Egg Pot Set 5 80.000000 MEL 27.640000 USD 82.910000 0.670000 100.990000 167.000000 80.000000

1848 V227TCS5 Egg Pot Set 5 80.000000 MEL 27.640000 USD 81.530000 0.730000 115.350000 167.000000 80.000000

1829 V227TCS5 Egg Pot Set 5 80.000000 MEL 27.640000 USD 75.860000 0.711000 95.160000 167.000000 80.000000

As my ONHAND is only 167 I only want to factor the last 2 landed costings in my average PriceAtWH.

I wish to run this for all my items with an ONHAND or ONORDER quantity above 0.

Any suggestions much appreciated.