on 01-10-2019 2:53 PM
We have and use reports and queries to show open Sales Orders.
What i need now is to see what batches is allocated to those open sales orders, so looking for a query that shows the Sales order and then also populates the batches and quantities linked to the open sales order
would like to have it added to this query
SELECT T0.[DocNum], T0.[DocDate], T0.[DocDueDate], t2.slpname, t3.u_name, T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[OpenQty], T1.[Quantity], T1.[Price] FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode INNER JOIN OUSR T3 ON T0.[UserSign] = T3.[USERID]
WHERE T0.[DocDueDate] < (getdate() +6000) AND T1.[LineStatus] = 'O'
order by T0.[DocDueDate]
Hi,
Try this query,
SELECT T1.[DocNum], T1.[CardName], T2.[ItemCode], T2.[Dscription], T2.[Quantity], case when T0.[Direction] = '2' then 'Allocatedqty' end, T0.[Quantity], T0.[BatchNum] FROM IBT1 T0 left join ORDR T1 on T0.[BaseType] = T1.[ObjType] left JOIN RDR1 T2 ON T1.DocEntry = T2.DocEntry WHERE T0.[Direction] = '2' and T0.[BaseNum] = T1.[DocNum] and T1.Docdate between [%0] and [%1]
GROUP BY T1.[DocNum], T1.[CardName], T2.[ItemCode], T2.[Dscription], T2.[Quantity], T0.[Direction], T0.[Quantity], T0.[BatchNum]
order by T1.[DocNum]
Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
97 | |
10 | |
8 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.