cancel
Showing results for 
Search instead for 
Did you mean: 

Query to get sales order, Delivery, Invoice

Former Member
0 Kudos
8,600

Hi All

I am having some trouble with a query to link a sales order to its target documents.

Basically the users want to see the postion of their stock...

so i need the quantities from the sales order, deliveries and invoices..as well as returns and credit memo's

I have the below so far, which works great, if user goes Sales Order > Delivery > Invoice.

Select

t0.docnum as 'Sales Order',

T1.ItemCOde as 'SO Item',

T1.Quantity as 'SO QTY',

T1.InvQty as 'SO QTY (MT)',

t3.docnum as 'Delivery Note',

T2.ItemCode as 'Del Item',

T2.Quantity as 'Del Qty',

T2. InvQty as 'Del Qty (MT)',

T5.DocNum as 'Invoice',

T4.ItemCode as 'INV Item',

T4.Quantity as 'INV QTY',

T4.InvQty as 'INV Qty (MT)',

t1.LineStatus as 'SO LINE Open/Closed'

FROM ordr t0

inner join rdr1 t1 on t0.docentry = t1.docentry

inner join dln1 t2 on t2.baseentry = t0.docentry and t1.linenum = t2.baseline

inner join odln t3 on t3.docentry = t2.docentry

LEFT JOIN INV1 T4 ON T4.BaseEntry=T3.DocEntry and T4.BaseEntry = T0.DocEntry

LEFT JOIN OINV T5 ON T5.DocEntry=T4.DocEntry

WHERE

t0.Docnum = 1553

However...from the same sales order, the user copies direction to an invoice.

I am struggling to join the RDR1 to both the DLN1 and INV1..

Hoping someone can help me.

Thank you.

Jerusha

Accepted Solutions (1)

Accepted Solutions (1)

kothandaraman_nagarajan
Active Contributor

Hi,

Try this complete 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', 
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

Note: Change selection criteria as per you need.

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Hello Nagarajan

Thank you soo much. This is brilliant...

Regards,

Jerusha

pvsbprasad
Active Contributor
0 Kudos

Hi,

Have you found any difference in my  answer  and Mr.

Regards,

Prasad

Former Member
0 Kudos

I Like this Query

Can you make the same for production ?

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Please create new discussion for your query requirement.

Thank you.

former_member634100
Participant
0 Kudos

Hello kothandaraman.nagarajan ,

This query does not show the Sales orders with no delivery and Sales orders with delivery but no invoice. Do you have a suggestions to pull those Sales orders as well?

Answers (4)

Answers (4)

pvsbprasad
Active Contributor
0 Kudos

Hi Jerusha,

Please close the thread,if you got the answer

Regards,

Prasad

Former Member
0 Kudos

Hi Jerusha,

Please check below.

t0.docnum as 'Sales Order',

T1.ItemCOde as 'SO Item',

T1.Quantity as 'SO QTY',

T1.InvQty as 'SO QTY (MT)',

t3.docnum as 'Delivery Note',

T2.ItemCode as 'Del Item',

T2.Quantity as 'Del Qty',

T2. InvQty as 'Del Qty (MT)',

T5.DocNum as 'Invoice',

T4.ItemCode as 'INV Item',

T4.Quantity as 'INV QTY',

T4.InvQty as 'INV Qty (MT)',

t1.LineStatus as 'SO LINE Open/Closed'

FROM ordr t0

inner join rdr1 t1 on t0.docentry = t1.docentry

inner join dln1 t2 on t2.baseentry = t1.docentry and t1.linenum = t2.baseline

inner join odln t3 on t3.docentry = t2.docentry

LEFT JOIN INV1 T4 ON T4.BaseEntry=T2.DocEntry and T4.linenum = t2.baseline

LEFT JOIN OINV T5 ON T5.DocEntry=T4.DocEntry

WHERE

t0.Docnum = 1553

Hope this helps

--

--

Regards::::

Atul Chakraborty

pvsbprasad
Active Contributor
0 Kudos

Try this

Select distinct T1.linenum,

t0.docnum as 'Sales Order',

T1.ItemCOde as 'SO Item',

T1.Quantity as 'SO QTY',

T1.InvQty as 'SO QTY (MT)',

t3.docnum as 'Delivery Note',

T2.ItemCode as 'Del Item',

T2.Quantity as 'Del Qty',

T2. InvQty as 'Del Qty (MT)',

T5.DocNum as 'Invoice',

T4.ItemCode as 'INV Item',

T4.Quantity as 'INV QTY',

T4.InvQty as 'INV Qty (MT)',

t1.LineStatus as 'SO LINE Open/Closed'

FROM ordr t0

inner join rdr1 t1 on t0.docentry = t1.docentry

inner join dln1 t2 on t2.baseentry = t0.docentry and t1.linenum = t2.baseline

inner join odln t3 on t3.docentry = t2.docentry

LEFT JOIN INV1 T4 ON T4.BaseEntry=T3.DocEntry and t2.linenum = t4.baseline

LEFT JOIN OINV T5 ON T5.DocEntry=T4.DocEntry

WHERE

t0.Docnum = 1553

Regards,

Prasad

nikunjmehta2290
Participant

This query is perfect. Thank you.

former_member209066
Active Contributor
0 Kudos

Hi,

Please Check this query

Select Distinct A.DocNum as 'SO NO',B.ItemCode as 'SO Item',B.Quantity as 'SO Qty' ,

D.DocNum as 'DO NO',C.ItemCode as 'DO Item',C.Quantity as 'DO Qty',

F.DocNum as 'AR NO',E.ItemCode as 'AR Item',E.Quantity as 'AR Qty' 

From ORDR A INNER JOIN RDR1 B ON A.DocEntry=B.DocEntry

INNER JOIN DLN1 C ON B.DocEntry=C.BaseEntry and A.ObjType=C.BaseType

INNER JOIN ODLN D ON C.DocEntry=D.DocEntry

INNER JOIN INV1 E ON C.DocEntry=E.BaseEntry and D.ObjType=E.BaseType

INNER JOIN OINV F ON E.DocEntry=F.DocEntry

Union All

Select Distinct A.DocNum as 'SO NO',B.ItemCode as 'SO Item',B.Quantity as 'SO Qty' ,

'' as 'DO NO',''as 'DO Item','' as 'DO Qty',

F.DocNum as 'AR NO',E.ItemCode as 'AR Item',E.Quantity as 'AR Qty' 

From ORDR A INNER JOIN RDR1 B ON A.DocEntry=B.DocEntry

INNER JOIN INV1 E ON B.DocEntry=E.BaseEntry and A.ObjType=E.BaseType

INNER JOIN OINV F ON E.DocEntry=F.DocEntry

Thanks,

Nithi

Former Member
0 Kudos

Hi Nithi

Thanks so much for this.

When i run the 2 selects seperately...I get results..

however when I run it all at once i get this error:

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to numeric.

thank you

Jerusha

former_member209066
Active Contributor
0 Kudos

Hi,

Please Check this query

Select Distinct A.DocNum as 'SO NO',B.ItemCode as 'SO Item',B.Quantity as 'SO Qty' ,

D.DocNum as 'DO NO',C.ItemCode as 'DO Item',C.Quantity as 'DO Qty',

F.DocNum as 'AR NO',E.ItemCode as 'AR Item',E.Quantity as 'AR Qty'

From ORDR A INNER JOIN RDR1 B ON A.DocEntry=B.DocEntry

INNER JOIN DLN1 C ON B.DocEntry=C.BaseEntry and A.ObjType=C.BaseType

INNER JOIN ODLN D ON C.DocEntry=D.DocEntry

INNER JOIN INV1 E ON C.DocEntry=E.BaseEntry and D.ObjType=E.BaseType

INNER JOIN OINV F ON E.DocEntry=F.DocEntry

Union All

Select Distinct A.DocNum as 'SO NO',B.ItemCode as 'SO Item',B.Quantity as 'SO Qty' ,

'0' as 'DO NO',''as 'DO Item','0' as 'DO Qty',

F.DocNum as 'AR NO',E.ItemCode as 'AR Item',E.Quantity as 'AR Qty'

From ORDR A INNER JOIN RDR1 B ON A.DocEntry=B.DocEntry

INNER JOIN INV1 E ON B.DocEntry=E.BaseEntry and A.ObjType=E.BaseType

INNER JOIN OINV F ON E.DocEntry=F.DocEntry

Thanks,

Nithi