Skip to Content

Invoice Date in Delivery Order Query

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

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

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 31, 2016 at 07:54 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 31, 2016 at 01:50 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 01, 2016 at 09:35 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 01, 2016 at 01:19 PM

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

    Add comment
    10|10000 characters needed characters exceeded