Invoice Date in Delivery Order Query

[kedalenechong]

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

[kothandaraman_nagarajan]

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

[kedalenechong]

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

[kothandaraman_nagarajan]

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

[narayanis]

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

[jitin_chawla]

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