Total Sold by Date by Warehouse

[former_member632375]

Hello all,

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'

[bill_froelich]

I have update the primary tag for your question. SAP Inventory Manager refers to a mobile product based on the Agentry platform and does not appear to relate to your question. Please feel free to update the tag to another value if you feel this isn't the correct tag.

[former_member632375]

Figured it out!

SELECT T1.[ItemCode], (select (c.[OnHand]) from oitw c where c.itemcode=t1.itemcode and c.whscode=t1.whscode ) - SUM(T1.[openqty]), t1.whscode FROM [dbo].[Ordr] T0 INNER JOIN [dbo].[rdr1] T1 ON T0.DocEntry = T1.DocEntry WHERE (T1.[WhsCode] = '02') and T0.[DocdueDate] < [%0] and T1.[openqty] > '0' GROUP BY T1.[ItemCode], T1.[WhsCode]