Skip to Content
0

Query to track orders, delivery and invoices with batch numbers

Feb 12, 2017 at 10:32 PM

426

avatar image

Hi experts, may someone please assist in adding batch numbers to this query for me.

SELECT T1.DocEntry as 'Link', T1.[DocNum] as 'SO No.', T1.[DocDate] as 'SO Date', T1.DocStatus as ' SO Status', T1.[CardName] as 'Customer Name', T9.SlpName, T10.firstName AS 'SO Owner', T8.FrgnName AS 'Part No.' ,
T0.[Dscription] as 'Part Name', T0.[Quantity] as 'SO Qty',T0.[Price] as 'Sales Price', T0.[OpenQty], T0.[OpenSum], T3.DocNum as 'Delivery Doc Num', T2.[Quantity] as 'Deliverd Quantity',
T12.DocNum AS 'Return No', T12.DocDate as 'Return Date', T11.Quantity as 'Retuen Qty' ,
T5.DocNum as 'Invoice No', T5.DocDate as 'Invoice Date', T5.DocStatus as 'Invoice Status' , T4.Quantity as 'Invoice Qty', T5.DocTotal,T5.PaidToDate as 'Applied Amt',
T7.DocNum as 'Credit Note No.', T7.DocDate as 'Credit Note date',T6.Quantity as 'Credit Note Qty'

FROM RDR1 T0 INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry
left outer join DLN1 T2 on T2.BaseEntry = T0.DocEntry and T2.BaseLine = T0.Linenum
left outer join ODLN T3 on T2.DocEntry = T3.DocEntry
left Outer join INV1 T4 on T4.BaseEntry = T3.DocEntry and T4.BaseLine = T2.Linenum and T4.BaseType = 15
OR (T4.Basetype=17 and T4.BaseEntry=T0.DocEntry and T4.BaseLine=T0.LineNum)
LEFT outer join RDN1 T11 on T11.BaseEntry = T2.DocEntry and T11.BaseLine = T2.LineNum
LEFT outer join ORDN T12 on T11.DocEntry = T12.DocEntry

left outer join OINV T5 on T5.DocEntry = T4.DocEntry
left Outer join RIN1 T6 on T6.BaseEntry = T5.DocEntry and T6.BaseLine = T4.Linenum
left outer join ORIN T7 on T6.DocEntry = T7.DocEntry
left outer join OITM T8 on T0.ItemCode = T8.ItemCode
left outer join OSLP T9 on T9.SlpCode = T1.SlpCode
left outer join OHEM T10 on T10.empID = T1.OwnerCode

WHERE T1.[DocDate] >=[%0] and T1.[DocDate] <=[%1]
Group by T1.DocEntry, T1.DocNum,T1.DocDate,T1.DocStatus,T1.CardName, T9.SlpName,T10.firstName,T8.FrgnName,T0.[Dscription] , T0.[Quantity] ,T0.[Price], T0.[OpenQty], T0.[OpenSum], T3.DocNum , T2.[Quantity] ,
T5.DocNum , T5.DocDate, T5.DocStatus , T4.Quantity, T5.DocTotal,T5.PaidToDate ,
T7.DocNum , T7.DocDate ,T6.Quantity,T12.DocNum,T12.DocDate,T11.Quantity

Thank you in advance

Tichaona

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Gaurav Bali Feb 13, 2017 at 11:26 AM
0

Hi Tichaona Gaza

Try below Query.

SELECT T1.DocEntry as 'Link', T1.[DocNum] as 'SO No.', T1.[DocDate] as 'SO Date', T1.DocStatus as ' SO Status', T1.[CardName] as 'Customer Name', T9.SlpName, T10.firstName AS 'SO Owner', T8.FrgnName AS 'Part No.' ,
T0.[Dscription] as 'Part Name', T0.[Quantity] as 'SO Qty',T0.[Price] as 'Sales Price', T0.[OpenQty], T0.[OpenSum], T3.DocNum as 'Delivery Doc Num', T2.[Quantity] as 'Deliverd Quantity',
T13.BatchNum 'Batch Number',T13.Quantity 'Batch Quantity',T12.DocNum AS 'Return No', T12.DocDate as 'Return Date', T11.Quantity as 'Retuen Qty' ,
T5.DocNum as 'Invoice No', T5.DocDate as 'Invoice Date', T5.DocStatus as 'Invoice Status' , T4.Quantity as 'Invoice Qty', T5.DocTotal,T5.PaidToDate as 'Applied Amt',
T7.DocNum as 'Credit Note No.', T7.DocDate as 'Credit Note date',T6.Quantity as 'Credit Note Qty'

FROM RDR1 T0 INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry
left outer join DLN1 T2 on T2.BaseEntry = T0.DocEntry and T2.BaseLine = T0.Linenum
left outer join ODLN T3 on T2.DocEntry = T3.DocEntry
left Outer join INV1 T4 on T4.BaseEntry = T3.DocEntry and T4.BaseLine = T2.Linenum and T4.BaseType = 15
OR (T4.Basetype=17 and T4.BaseEntry=T0.DocEntry and T4.BaseLine=T0.LineNum)
LEFT outer join RDN1 T11 on T11.BaseEntry = T2.DocEntry and T11.BaseLine = T2.LineNum
LEFT outer join ORDN T12 on T11.DocEntry = T12.DocEntry

left outer join OINV T5 on T5.DocEntry = T4.DocEntry
left Outer join RIN1 T6 on T6.BaseEntry = T5.DocEntry and T6.BaseLine = T4.Linenum
left outer join ORIN T7 on T6.DocEntry = T7.DocEntry
left outer join OITM T8 on T0.ItemCode = T8.ItemCode
left outer join OSLP T9 on T9.SlpCode = T1.SlpCode
left outer join OHEM T10 on T10.empID = T1.OwnerCode
left outer join IBT1 T13 on T2.DocEntry = T13.BaseEntry and T13.BaseType = 15 and T2.ItemCode = T13.ItemCode and T2.LineNum = T13.BaseLinNum

WHERE T1.[DocDate] >=[%0] and T1.[DocDate] <=[%1]
Group by T1.DocEntry, T1.DocNum,T1.DocDate,T1.DocStatus,T1.CardName, T9.SlpName,T10.firstName,T8.FrgnName,T0.[Dscription] , T0.[Quantity] ,T0.[Price], T0.[OpenQty],
T0.[OpenSum], T3.DocNum , T2.[Quantity] ,
T5.DocNum , T5.DocDate, T5.DocStatus , T4.Quantity, T5.DocTotal,T5.PaidToDate ,
T7.DocNum , T7.DocDate ,T6.Quantity,T12.DocNum,T12.DocDate,T11.Quantity,T13.BatchNum

Please Note Delivery Quantity and Batch quantity might differ according to number of selected Batches.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks a lot Gaurav. You only missed T13.Quantity in the GROUP by clause.

0
avatar image
Former Member Mar 21 at 09:05 AM
0

Hi,

The above query showing canceled so and delivery ar invoice. could you please do the changes for the canceled documents.

Share
10 |10000 characters needed characters left characters exceeded