on 11-03-2017 7:55 PM
Hi Gurus,
I am trying to get a query where a particular style has stocks in SAP. i we have released few piece out of it then i need to see remaining balance of stock left like a carry forward balance. below mentioned query takes full stocks every time when we have new sales order under same customer. Purpose of this query is to see order is filled completely or not and if not then what style is not filled under what sales order of the same customer.
Query is as below:
SELECT
T0.[DocDueDate],
T0.[CardName],
T0.[NumAtCard],
T0.[DocNum],
T1.[ItemCode],
T1.[Dscription],
T3.[OnHand],
T3.[WhsCode],
T1.[Quantity] as 'PO QTY',
T1.OpenQty AS 'To Release'
FROM
ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN OITW T3 ON T2.[ItemCode] = T3.[ItemCode]
WHERE
T0.[CardCode] = 'c00192' and
T0.[DocStatus] = 'O' and
T3.[WhsCode] = '161' and
T0.[NumAtCard] Like '%%818237%%'
GROUP BY
T0.[DocDueDate], T0.[CardName], T0.[NumAtCard], T0.[DocNum], T1.[ItemCode], T1.[Dscription], T3.[OnHand], T3.[WhsCode], T1.[Quantity], T1.OpenQty
ORDER BY T0.[DocDueDate], T0.[NumAtCard], T0.[DocNum]
Thanks
Shahzad.
Hi Shahzad,
If I understood correct, this is what you need:
SELECT
T0.[DocDueDate],
T0.[CardName],
T0.[NumAtCard],
T0.[DocNum],
T1.[ItemCode],
T1.[Dscription],
((T3.[OnHand] - T3.[IsCommited]) + T1.OpenQty) AS 'Available',
T3.[WhsCode],
T1.[Quantity] as 'PO QTY',
T1.OpenQty AS 'To Release'
FROM
ORDR T0
INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
INNER JOIN OITW T3 ON T2.[ItemCode] = T3.[ItemCode]
WHERE
T0.[CardCode] = 'c00192' and
T0.[DocStatus] = 'O' and
T3.[WhsCode] = '161' and
T0.[NumAtCard] Like '%818237%'
GROUP BY
T0.[DocDueDate],
T0.[CardName],
T0.[NumAtCard],
T0.[DocNum],
T1.[ItemCode],
T1.[Dscription],
T3.[OnHand],
T3.[IsCommited],
T3.[WhsCode],
T1.[Quantity],
T1.OpenQty
ORDER BY
T0.[DocDueDate],
T0.[NumAtCard],
T0.[DocNum]
Hope it helps.
Kind Regards,
Diego Lother
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Diego Thanks for your response. but i am little bit off with what i require. Please see attached data.what i need is
Sr #1 has 270 is stock and 5 to release but when this style repeats at row # 15 (where SO # changed) it should show in stock 265 but what i am getting is, same numbers instead of remaining balance when style gets repeated in new sales order.
Thanks
Hi Shazhad,
Try the query below:
SELECT
T0.[DocDueDate],
T0.[CardName],
T0.[NumAtCard],
T0.[DocNum],
T1.[ItemCode],
T1.[Dscription],
T3.[WhsCode],
T3.[OnHand] - SUM(OpenQty) OVER (Partition By T1.ItemCode ORDER BY DocDueDate, LineNum, NumAtCard, DocNum) + SUM(T1.OpenQty) AS 'Available',
SUM(T1.[Quantity]) AS 'PO QTY',
SUM(T1.OpenQty) AS 'To Release'
FROM
ORDR T0
INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
INNER JOIN OITW T3 ON T2.[ItemCode] = T3.[ItemCode] AND T1.[WhsCode] = T3.[WhsCode]
WHERE
T0.[CardCode] = 'c00192' and
T0.[DocStatus] = 'O' AND
T1.[LineStatus] = 'O' AND
T0.[CANCELED] = 'N' AND
T1.[WhsCode] = '01' AND
T0.[NumAtCard] Like '%818237%'
GROUP BY
T0.[DocDueDate],
T0.[CardName],
T0.[NumAtCard],
T0.[DocNum],
T1.[ItemCode],
T1.[Dscription],
T3.[OnHand],
T3.[WhsCode],
T1.[LineNum],
T1.OpenQty
ORDER BY
T0.[DocDueDate],
T1.[LineNum],
T0.[NumAtCard],
T0.[DocNum]
I think will fit your purpose.
Hope it helps.
Kind Regards,
Diego Lother
User | Count |
---|---|
106 | |
12 | |
10 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.