Query for Delivery and Invoice with Items

[Former Member]

Hi Experts,

Good day!

Can someone help me in the below query? I am generating a report for the list of Deliveries with their corresponding invoice # and item details from delivery. Unfortunately I can generate any details.

Kindly see my query below:

SELECT T0.[DocDate], T0.[DocNum], T2.[DocDate], T0.[CardName], T1.[Quantity], T1.[ItemCode], T1.[Dscription],T3.[SlpName] FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry], OINV T2 INNER JOIN OSLP T3 ON T0.[SlpCode] = T3.[SlpCode] WHERE WHERE T0.DocDate between [%0] and [%1]

Below are the details that I want to show in the report.

• Posting Date (ODLN)
• Delivery Number (ODLN.DocNum)
• Invoice Number (OINV.DocNum)
• Customer Name in ODLN
• Quantity of Items (DLN1)
• Item Code (DLN1)
• Item Description (DLN1)
• Price (DLN1)
• Sales Employee Name (OSLP)

Thanks!

Ashley

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this:

SELECT T0.[DocDate], T0.[DocNum], T2.[DocDate], T0.[CardName], T1.[Quantity], T1.[ItemCode],

T1.[Dscription],T1.[Price],T3.[SlpName]

FROM ODLN T0

INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN INV1 T4 ON T4.BaseEntry = T1.DocEntry AND T4.BaseLine = T1.LineNum

INNER JOIN OINV T2 ON T2.DocEntry = T4.DocEntry

INNER JOIN OSLP T3 ON T0.[SlpCode] = T3.[SlpCode]

WHERE T0.DocDate between [%0] and [%1]

GROUP BY T0.[DocDate], T0.[DocNum], T2.[DocDate], T0.[CardName], T1.[Quantity], T1.[ItemCode],

T1.[Dscription],T3.[SlpName],T1.[Price]

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Thank you Nagarajan K!

[Former Member]

Hi Leigh,

Please check below Query.

SELECT T0.[DocDate], T0.[DocNum], T2.[DocDate], T0.[CardName], T1.[Quantity], T1.[ItemCode],

T1.[Dscription],T3.[SlpName] FROM ODLN T0 

INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN INV1 T4 ON T4.BaseEntry = T1.DocEntry AND T4.BaseLine = T1.LineNum

INNER JOIN OINV T2 ON T2.DocEntry = T4.DocEntry

INNER JOIN OSLP T3 ON T0.[SlpCode] = T3.[SlpCode]

WHERE T0.DocDate between [%0] and [%1]

GROUP BY T0.[DocDate], T0.[DocNum], T2.[DocDate], T0.[CardName], T1.[Quantity], T1.[ItemCode],

T1.[Dscription],T3.[SlpName]

Hope this helps

--

--

Regards::::

Atul Chakraborty

[former_member184146]

Hi,

     Try this

SELECT DISTINCT A.DocDate, A.[DocNum], D.DocNum, A.[CardName],b.ItemCode,b.Dscription,b.Quantity,b.Price,G.SlpName

FROM ODLN A

INNER JOIN DLN1 B ON A.DocEntry=B.DocEntry

LEFT OUTER JOIN INV1 C ON C.BaseRef=B.TrgetEntry AND C.BaseType=A.ObjType AND B.ItemCode=C.ItemCode

INNER JOIN OINV D ON C.DOCENTRY=D.DOCENTRY

inner join OCRD F ON F.CARDCODE=A.CARDCODE

INNER JOIN OSLP G ON G.SlpCode=F.SlpCode

GROUP BY

A.DocDate, A.[DocNum], D.DocNum, A.[CardName],b.ItemCode,b.Dscription,b.Quantity,b.Price,G.SlpName

ORDER BY A.[DocNum], D.DocNum

--Manish