We have three warehouses and manage sales orders out of two of those warehouses (one warehouse is just overflow inventory and we do not ship sales orders out of it).
I am trying to build a query that shows me open items on sales order by date vs current on hand for a specific warehouse. I need to limit by date for sales orders because we have sales orders in our system that do not need to ship until far in the future. I'd like my query to tell me, for warehouse "02" we are oversold on item X since we have open sales orders that need to ship this week, but only have X amount on hand, therefor we need to look to the other warehouses to transfer that inventory.
My query below tells me how what is sold in a specific warehouse, with the warehouse inventory, but it is not limiting it by date. It is showing me everything that is sold to that warehouse. Can someone help? Thank you!
SELECT distinct T2.[ItemCode], T2.[OnHand]- T2.[IsCommited] FROM RDR1 T0 INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode] INNER JOIN OITW T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN ORDR T3 ON T0.[DocEntry] = T3.[DocEntry] WHERE T3.[DocDueDate] >[%0] and T2.[WhsCode] = '02'