Skip to Content
Former Member
Nov 22, 2013 at 09:09 AM

Finished Goods aged inventory report



I've been trying to generate a report to find any finished goods that have been placed in our finished goods warehouse that are still there 2 weeks after they were placed there. Sounds simple...but isn't.

What I'm finding based on my query (below) is that it is taking into account earlier receipts of the same product which may still have an inventory value - it doesn't dedicate the stock quantity to the actual production order that delivered it to inventory. I'd appreciate it if someone could provide some pointers in this especially on deriving the criteria. Bare in mind that some of these items are bespoke manufactured and some are bought in for resale.

Select 'Manufactured',t0.docnum, t2.docnum, t0.DocDate[Transaction Date], t1.itemcode, t1.Quantity, t1.Dscription [itemname], t4.OnHand [onhand WAR_003], t6.CardName [Customer/Supplier],

t3.avgprice, t3.AvgPrice*t1.Quantity [Cost]

from oign t0

left join ign1 t1 on t0.docentry = t1.docentry

left join (select max(t0.docnum) [docnum], t1.itemcode from oign t0 left join ign1 t1 on t0.docentry = t1.docentry group by t0.docnum, t1.itemcode) as t2 on t1.itemcode = t2.itemcode

left join oitm t3 on t1.itemcode = t3.ItemCode

left join oitw t4 on t3.ItemCode = t4.itemcode and t4.WhsCode = 'WAR_003'

left join owor t5 on t1.BaseRef = t5.DocNum

left join ocrd t6 on t5.cardcode = t6.CardCode

where t4.onhand > '0'

and t0.docnum = t2.docnum

and t0.UpdateDate <= getdate ()-14

and t0.datasource = 'O'

and t3.ItmsGrpCod in (206, 148, 161)

and t1.Quantity = t4.OnHand

Union all

select 'bought in', t5.docnum, null,max(t5.docdate), t0.itemcode, t4.Quantity, t0.itemname,t3.onhand , t5.CardName, t0.avgprice, t0.AvgPrice*t4.Quantity

from oitm t0

left join oitw t3 on t0.itemcode = t3.ItemCode and t3.WhsCode = 'war_003'

left join pdn1 t4 on t0.itemcode = t4.ItemCode

left join opdn t5 on t4.docentry = t5.docentry


t3.onhand > '0'

and t0.ItmsGrpCod in (206, 148, 161)

and t5.DocDate <= getdate ()-14

and t4.Quantity = t3.OnHand

Group by t0.itemcode, t0.itemname, t4.Quantity, t0.DfltWH, t0.AvgPrice, t0.onhand, (t0.onhand * t0.avgprice), t5.DocDate, t0.FrgnName, t3.OnHand, t4.DocEntry, t5.DocNum, t5.CardName

Order by t0.docnum desc;