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
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;