shipment arrival dates by column

[marcolde]

hi all,

we have various purchase orders in our system and 2 different warehouses where stock is arriving.

i want to have query which shows the arrival date on a certain date regardless of the warehouse.

but i am stuck at the point that i get the output i want but only per warehouse.sap-1.png

the quantity in WH 305 which is shown in column next delivery qty appears again in column next delivery qty 1 even though it isnt the next delivery qty. i bascially just want to see one line per product with the arriving quantity.

my querie is as below

/*SELECT FROM OITM T0*/ declare @ITEM as nvarchar(20) /* WHERE T0.ItemCode = */ SET @ITEM = /* T0.ItemCode */ '[%0]' /*SELECT FROM OITW T1*/ declare @WHS as nvarchar(8) /* WHERE T1.WhsCode = */ SET @WHS = /* T1.WhsCode */ '[%1]' SELECT T1.[WhsCode], T1.[ItemCode], T0.[ItemName], T0.[FrgnName] 'Season Code', T0.[SalPackUn] 'Pack Size', T0.[U_SubCat], T1.[OnHand], T1.[IsCommited] 'Commited', T1.[OnOrder], T1.[OnHand] - T1.[IsCommited] 'Available NOW', T0.[AvgPrice], T1.[OnHand] * T0.[AvgPrice] 'Value' , (select Min(T11.ShipDate) from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O') 'Next Delivery Date', (select Top 1 T11.OpenQty * T11.NumPerMsr from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O' order by T11.ShipDate) 'Next Delivery Qty', (select Top 1 T10.DocNum from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O' order by T11.ShipDate) 'Next Delivery PO', case when (select COUNT(*) from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O') > 1 then (select Top 1 a.ShipDate from (select Top 2 T11.ShipDate from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O' order by T11.ShipDate) a order by a.ShipDate desc) end 'Next Delivery Date 1', case when (select COUNT(*) from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O') > 1 then (select Top 1 a.OpenQty * a.NumPerMsr from (select Top 2 T11.OpenQty, T11.NumPerMsr, T11.ShipDate from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O' order by T11.ShipDate) a order by a.ShipDate desc) end 'Next Delivery Qty 1', case when (select COUNT(*) from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O') > 1 then (select Top 1 a.DocNum from (select Top 2 T10.DocNum, T11.ShipDate from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O' order by T11.ShipDate) a order by a.ShipDate desc) end 'Next Delivery PO 1', case when (select COUNT(*) from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O') > 2 then (select Top 1 a.ShipDate from (select Top 3 T11.ShipDate from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O' order by T11.ShipDate) a order by a.ShipDate desc) end 'Next Delivery Date 2', case when (select COUNT(*) from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O') > 2 then (select Top 1 a.OpenQty * a.NumPerMsr from (select Top 3 T11.OpenQty, T11.NumPerMsr, T11.ShipDate from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O' order by T11.ShipDate) a order by a.ShipDate desc) end 'Next Delivery Qty 2', case when (select COUNT(*) from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O') > 2 then (select Top 1 a.DocNum from (select Top 3 T10.DocNum, T11.ShipDate from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O' order by T11.ShipDate) a order by a.ShipDate desc) end 'Next Delivery PO 2', (select Top 1 T10.Price from ITM1 T10 where T10.ItemCode = T0.ItemCode and T10.PriceList = 6) 'Retail Inc GST', (select Top 1 T10.Price from ITM1 T10 where T10.ItemCode = T0.ItemCode and T10.PriceList = 2) 'Wholesale ex GST' FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode WHERE T0.[InvntItem] = 'Y' and T0.[QryGroup1] ='Y' and T0.ItemCode Like '%' + @ITEM + '%' and T1.WhsCode like '%' + @WHS + '%' ORDER BY 1,2

[marcolde]

hi all,

we have various purchase orders in our system and 2 different warehouses where stock is arriving.

i want to have query which shows the arrival date on a certain date regardless of the warehouse.

but i am stuck at the point that i get the output i want but only per warehouse.sap-1.png

the quantity in WH 305 which is shown in column next delivery qty appears again in column next delivery qty 1 even though it isnt the next delivery qty. i bascially just want to see one line per product with the arriving quantity.

my querie is as below

/*SELECT FROM OITM T0*/ declare @ITEM as nvarchar(20) /* WHERE T0.ItemCode = */ SET @ITEM = /* T0.ItemCode */ '[%0]' /*SELECT FROM OITW T1*/ declare @WHS as nvarchar(8) /* WHERE T1.WhsCode = */ SET @WHS = /* T1.WhsCode */ '[%1]' SELECT T1.[WhsCode], T1.[ItemCode], T0.[ItemName], T0.[FrgnName] 'Season Code', T0.[SalPackUn] 'Pack Size', T0.[U_SubCat], T1.[OnHand], T1.[IsCommited] 'Commited', T1.[OnOrder], T1.[OnHand] - T1.[IsCommited] 'Available NOW', T0.[AvgPrice], T1.[OnHand] * T0.[AvgPrice] 'Value' , (select Min(T11.ShipDate) from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O') 'Next Delivery Date', (select Top 1 T11.OpenQty * T11.NumPerMsr from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O' order by T11.ShipDate) 'Next Delivery Qty', (select Top 1 T10.DocNum from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O' order by T11.ShipDate) 'Next Delivery PO', case when (select COUNT(*) from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O') > 1 then (select Top 1 a.ShipDate from (select Top 2 T11.ShipDate from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O' order by T11.ShipDate) a order by a.ShipDate desc) end 'Next Delivery Date 1', case when (select COUNT(*) from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O') > 1 then (select Top 1 a.OpenQty * a.NumPerMsr from (select Top 2 T11.OpenQty, T11.NumPerMsr, T11.ShipDate from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O' order by T11.ShipDate) a order by a.ShipDate desc) end 'Next Delivery Qty 1', case when (select COUNT(*) from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O') > 1 then (select Top 1 a.DocNum from (select Top 2 T10.DocNum, T11.ShipDate from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O' order by T11.ShipDate) a order by a.ShipDate desc) end 'Next Delivery PO 1', case when (select COUNT(*) from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O') > 2 then (select Top 1 a.ShipDate from (select Top 3 T11.ShipDate from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O' order by T11.ShipDate) a order by a.ShipDate desc) end 'Next Delivery Date 2', case when (select COUNT(*) from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O') > 2 then (select Top 1 a.OpenQty * a.NumPerMsr from (select Top 3 T11.OpenQty, T11.NumPerMsr, T11.ShipDate from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O' order by T11.ShipDate) a order by a.ShipDate desc) end 'Next Delivery Qty 2', case when (select COUNT(*) from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O') > 2 then (select Top 1 a.DocNum from (select Top 3 T10.DocNum, T11.ShipDate from OPOR T10 inner JOIN POR1 T11 on T10.DocEntry = T11.DocEntry where T11.ItemCode = T1.ItemCode and T11.WhsCode = T1.WhsCode and LineStatus = 'O' order by T11.ShipDate) a order by a.ShipDate desc) end 'Next Delivery PO 2', (select Top 1 T10.Price from ITM1 T10 where T10.ItemCode = T0.ItemCode and T10.PriceList = 6) 'Retail Inc GST', (select Top 1 T10.Price from ITM1 T10 where T10.ItemCode = T0.ItemCode and T10.PriceList = 2) 'Wholesale ex GST' FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode WHERE T0.[InvntItem] = 'Y' and T0.[QryGroup1] ='Y' and T0.ItemCode Like '%' + @ITEM + '%' and T1.WhsCode like '%' + @WHS + '%' ORDER BY 1,2