Hello Experts,
Please look at the following query and suggest changes.
Requested format:
Item# | ATP until WH Arrival Date | WH Arrival Date | ATP until next Arrival Date | Next WH Arrival Date
SELECT T1.[ItemCode] //Item#
,(SELECT SUM(M0.[OnHand]) FROM OITW M0 WHERE M0.[ItemCode] = T0.[ItemCode] and M0.[WhsCode] IN('21') - SELECT(SUM(T2.[Quantity]) from ORDR T2 where T2.[DocDueDate] <= T0.[U_WhsArrivalDate])) AS 'ATP until WH Arrival date' // Instock - sum(Open So where duedate <= date1 (T0.[U_WhsArrivalDate])
,T0.[U_WhsArrivalDate] AS 'WH Arrival Date' //date1
,(SELECT SUM(M0.[OnHand]) FROM OITW M0 WHERE M0.[ItemCode] = T0.[ItemCode] and M0.[WhsCode] IN('21') - SELECT(SUM(T2.[Quantity]) FROM ORDR T2 WHERE T2.[DocDueDate] <= MIN(T0.[U_WhsArrivalDate])) + SELECT SUM(T0.[Quantity]*T0.[Price]) FROM POR1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T4.ItemCode INNER JOIN OPOR A2 ON T0.[DocEntry] = A2.[DocEntry] WHERE T1.[ItmsGrpCod] = T4.[ItmsGrpCod] and A2.[CANCELED] ='N' and A2.[DocStatus] ='O' and A0.[LineStatus] ='O' and A0.[WhsCode] in ('21') AND T0.[U_WhsArrivalDate] < MIN(T0.[U_WhsArrivalDate]) AND A0.[U_Container])<> '' AS 'ATP until next Arrival Date'
,CASE MIN(T0.[U_WhsArrivalDate]) WHEN '' THEN GETDATE()+60 ELSE NextWHArrivalDate END AS 'NextWHArrivalDate'
FROM POR1 T0 INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode], ORDR T2 INNER JOIN RDR1 T3 ON T2.[DocEntry] = T3.[DocEntry] INNER JOIN OITB T4 ON T1.[ItmsGrpCod] = T4.[ItmsGrpCod]
<br>
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.