Query for Delivery and Sales Invoice

[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 payment details. Unfortunately I can generate any details.

Kindly see my query below:

SELECT T0.[DocNum], T1.[DocNum], T0.[DocDate], T0.[CardName], T0.[DocTotal], T0.[NumAtCard], T0.[Comments],T2.[Comments], T2.[JrnlMemo] FROM ODLN T0 , OINV T1, ORCT T2 WHERE WHERE T0.DocDate between [%0] and [%1]

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

• Delivery Number (DocNum)
• Invoice Number (DocNum)
• Delivery Date
• Customer Name in ODLN
• Document Total in ODLN
• Document Remarks in ODLN
• Customer Reference Number in ODLN
• Document Remarks in ORCT
• Journal Remarks in ORCT

Thanks!

Ashley

[former_member209066 · Accepted solution]

Hi,

Please Check this

SELECT Distinct T0.[DocNum], T1.[DocNum], T0.[DocDate],

T0.[CardName], T0.[DocTotal], T0.[NumAtCard],

T0.[Comments],T2.[Comments], T2.[JrnlMemo]

FROM ODLN T0 LEFT OUTER JOIN INV1 A ON T0.DocEntry=A.BaseEntry and T0.ObjType=A.BaseType

LEFT OUTER JOIN OINV T1 ON A.DocEntry=T1.DocEntry LEFT OUTER JOIN RCT2 B ON T1.DocEntry=B.DocEntry

LEFT OUTER JOIN ORCT T2 ON B.DocNum=T2.DocNum

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

Thanks,

Nithi

[Former Member]

Hi All,

Thanks for all your feedback.

Ashley

[Former Member]

Hi Leigh,

Please check below Query.

SELECT T0.[DocNum], T1.[DocNum], T0.[DocDate], T0.[CardName], T0.[DocTotal], T0.[NumAtCard],

T0.[Comments],T2.[Comments], T2.[JrnlMemo] FROM ODLN T0

INNER JOIN DLN1 T3 ON T3.DocEntry = T0.DocEntry

LEFT JOIN  INV1 T4 ON T4.BaseEntry = T3.DocEntry AND T4.BaseLine = T3.LineNum

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

LEFT JOIN  ORCT T2 ON T2.DocNum = T1.ReceiptNum

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

GROUP BY T0.[DocNum], T1.[DocNum], T0.[DocDate], T0.[CardName], T0.[DocTotal], T0.[NumAtCard],

T0.[Comments],T2.[Comments], T2.[JrnlMemo]

Hope this helps

--

--

Regards::::

Atul Chakraborty

[kothandaraman_nagarajan]

Hi,

Try this query:

SELECT T0.[DocNum], T3.[DocNum], T0.[DocDate], T0.[CardName], T0.[DocTotal], T0.[Comments], T0.[NumAtCard], T5.[Comments], T5.[JrnlMemo] FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry left join  INV1 T2 on T2.[BaseEntry]  = T0.docentry and T2.[BaseLine]  =  T1.[LineNum] left JOIN OINV T3 ON T2.DocEntry = T3.DocEntry left join  RCT2 T4 on T4.docentry = T3.docentry left JOIN ORCT T5 ON T4.DocNum = T5.DocEntry WHERE   T0.[DocDate]  between [%0] and [%1]

Thanks & Regards,

Nagarajan

[former_member184146]

Hi,

     Try below query

SELECT A.[DocNum], D.[DocNum], A.[DocDate], A.[CardName],

A.[DocTotal], A.[NumAtCard], A.[Comments],E.[Comments], E.[JrnlMemo]

FROM ODLN A

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

LEFT OUTER JOIN INV1 C ON C.BaseRef=B.TrgetEntry

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

LEFT OUTER JOIN ORCT E ON E.DOCENTRY=D.DOCENTRY

GROUP BY  A.[DocNum], D.[DocNum], A.[DocDate], A.[CardName],

A.[DocTotal], A.[NumAtCard], A.[Comments],E.[Comments], E.[JrnlMemo]

--Manish