Skip to Content
0

Invoice Date in Delivery Order Query

Oct 31, 2016 at 06:53 AM

36

avatar image

Hi all

Please help correct this Query to show AR Invoice posting date in Delivery Order Query

My results shows too many duplicate records.


SELECT T0.[DocNum], T0.Docentry, T0.[DocType], T0.[CANCELED], T0.[DocStatus], T0.[DocDate], T0.[CardCode], T1.[ItemCode], T1.[LineStatus], T1.[Quantity], T1.[OpenQty], T1.[BaseRef], T1.[BaseType], T1.[TargetType], T1.[TrgetEntry], T2.DocDate FROM [dbo].[ODLN] T0 INNER JOIN [dbo].[DLN1] T1 ON T0.[DocEntry] = T1.[DocEntry] inner join inv1 T2 on T2.Docentry = T1.TrgetEntry

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

5 Answers

avatar image
Former Member Oct 31, 2016 at 08:02 AM
0

Hi,

Try adding group by at the end of the query.

SELECT T0.[DocNum], T0.Docentry, T0.[DocType], T0.[CANCELED], T0.[DocStatus], T0.[DocDate], T0.[CardCode], T1.[ItemCode], T1.[LineStatus], T1.[Quantity], T1.[OpenQty], T1.[BaseRef], T1.[BaseType], T1.[TargetType], T1.[TrgetEntry], T2.DocDate FROM [dbo].[ODLN] T0 INNER JOIN [dbo].[DLN1] T1 ON T0.[DocEntry] = T1.[DocEntry] inner join inv1 T2 on T2.Docentry = T1.TrgetEntry group by T0.[DocNum], T0.Docentry, T0.[DocType], T0.[CANCELED], T0.[DocStatus], T0.[DocDate], T0.[CardCode], T1.[ItemCode], T1.[LineStatus], T1.[Quantity], T1.[OpenQty], T1.[BaseRef], T1.[BaseType], T1.[TargetType], T1.[TrgetEntry], T2.DocDate

Regards

Narayani

Share
10 |10000 characters needed characters left characters exceeded
Jitin Chawla
Oct 31, 2016 at 07:54 AM
0

Hi,

Check this :

SELECT distinct T0.Docentry, t0.[DocNum], T0.[DocType], T0.[CANCELED], T0.[DocStatus], T0.[DocDate], T0.[CardCode], T1.[ItemCode], T1.[LineStatus],

T1.[Quantity], T1.[OpenQty], T1.[BaseRef], T1.[BaseType], T1.[TargetType], T1.[TrgetEntry], T2.DocDate

FROM [dbo].[ODLN] T0 innerJOIN [dbo].[DLN1] T1 ON T0.[DocEntry]=T1.[DocEntry]

inner join inv1 T2 on T2.Docentry=T1.TrgetEntry --and t2.BaseEntry = t0.DocEntry

ORDER BY t0.[DocEntry]

The rows in the DLN1 will be displayed multiple times.

Regards,

Jitin

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Oct 31, 2016 at 01:50 PM
0

Hi,

Try this query,

SELECT T1.[DocEntry], T0.[DocNum], T0.[DocType], T0.[CANCELED], T0.[DocStatus], T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T3.[DocNum] 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]

Thanks

Share
10 |10000 characters needed characters left characters exceeded
Kedalene Chong Nov 01, 2016 at 09:35 AM
0

Thanks Jitin and Nagarajan

I managed to get the correct Invoice Date from T3.DocDate modifying Nagarajan's Query with no duplicate Delivery row records.

Kedalene

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Nov 01, 2016 at 01:19 PM
0

If you got answer, please accept to close this thread.

Share
10 |10000 characters needed characters left characters exceeded