Skip to Content
avatar image
Former Member

Query - Sales Orders linked to Deliveries

Hello all,

I have come up with the below query that displays details of sales orders due for delivery on a selected future date (user input). I would like to add an extra column that displays delivery numbers (ODLN.DocNum) in case the sales order has been copied to an A/R Reserve invoice and then to a Delivery. NOTE: we use AR Reserve invoices for future deliveries and AR Invoices for same day deliveries.

SELECT T0.[DocDueDate], T0.[CardName],T0.[DocNum],T3.[SuppCatNum], T0.[CardName], T1.[Dscription], T3.[FrgnName],T3.[U_Item_Colour],  T1.[Quantity], T2.[SlpName],T5.[descript] FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode INNER JOIN OTER T5 ON T4.Territory = T5.territryID WHERE T0.[DocDueDate] = "%0"

My attempt to improve the above query by adding OINV and linking RDR1.TrgetEntry = OINV.DocNum has not been very successful so far as the results are pulling out wrong data - Old Deliveries. Kindly assist improve the below query to pull out correct data.

SELECT distinct T0.[DocDueDate], T0.[CardName],T0.[DocNum],T3.[SuppCatNum], T0.[CardName], T1.[Dscription], T3.[FrgnName],T3.[U_Item_Colour],  T1.[Quantity], T2.[SlpName],T5.[descript],t6.docnum,t8.docnum FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode full outer JOIN OITM T3 ON T1.ItemCode = T3.ItemCode left JOIN OCRD T4 ON T0.CardCode = T4.CardCode left JOIN OTER T5 ON T4.Territory = T5.territryID right JOIN OINV T6 ON T6.docnum = T1.trgetentry left JOIN INV1 T7 ON T7.baseentry = t0.docnum left JOIN ODLN T8 ON T4.CardCode = T8.CardCode left join dln1 t9 on  t9.baseentry=t6.docnum WHERE T0.[DocDueDate] =[%0] and t9.trgetentry is null

Thank you in advance.

Henry

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 11, 2012 at 05:57 PM

    Hi Henry,

    Try:

    SELECT distinct T0.[DocDueDate], T0.[CardName],T0.[DocNum],T3.[SuppCatNum], T0.[CardName],

    T1.[Dscription], T3.[FrgnName], T1.[Quantity],

    T2.[SlpName],T5.[descript],T7.docnum 'Invoice #',T9.docnum 'Delivery#', T0.DocNum 'SO#'

    FROM ORDR T0

    INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

    LEFT JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

    INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

    INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode

    LEFT JOIN OTER T5 ON T4.Territory = T5.territryID

    LEFT join dln1 T8 on T8.baseentry = t0.docentry and t1.linenum = T8.baseline

    LEFT join odln T9 on T9.docentry = T8.docentry

    LEFT JOIN INV1 T6 ON T6.BaseEntry=T9.DocEntry

    LEFT JOIN OINV T7 ON T7.DocEntry=T6.DocEntry

    WHERE  T0.[DocDueDate] =[%0] and t8.trgetentry is null

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Henry Njagi

      Try This

      SELECT distinct T0.[DocDueDate], T0.[CardName],T0.[DocNum],  T0.[CardName],

      T1.[Dscription], a5.docnum,a3.docnum

      FROM ORDR T0

      INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

      inner join dln1 a2 on a2.baseentry = t0.docentry and t1.linenum = a2.baseline

      inner join odln a3 on a3.docentry = a2.docentry

      LEFT JOIN INV1 a4 ON a4.BaseEntry=a3.DocEntry

      LEFT JOIN OINV a5 ON a5.DocEntry=a4.DocEntry

      WHERE T0.[DocDueDate] =[%0]

      Regards

      Kennedy

  • Oct 11, 2012 at 04:41 PM

    Hi  Henry Njagi

    Try This

    SELECT distinct T0.[DocDueDate], T0.[CardName],T0.[DocNum],T3.[SuppCatNum], T0.[CardName],

    T1.[Dscription], T3.[FrgnName],T3.[U_Item_Colour],  T1.[Quantity],

    T2.[SlpName],T5.[descript],a5.docnum,a3.docnum

    FROM ORDR T0 

    INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

    INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

    full outer JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

    left JOIN OCRD T4 ON T0.CardCode = T4.CardCode

    left JOIN OTER T5 ON T4.Territory = T5.territryID

    inner join dln1 a2 on a2.baseentry = t0.docentry and t1.linenum = a2.baseline

    inner join odln a3 on a3.docentry = a2.docentry

    LEFT JOIN INV1 a4 ON a4.BaseEntry=a3.DocEntry

    LEFT JOIN OINV a5 ON a5.DocEntry=a4.DocEntry

    WHERE T0.[DocDueDate] =[%0]

    Hope helpful 😊

    Regards

    Kennedy

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 12, 2012 at 08:54 AM

    Try to start with this:

    Select

      OO.[DocDueDate], OO.[CardName],OO.[DocNum],

      T3.[SuppCatNum], O.[Dscription], T3.[FrgnName], T3.[U_Item_Colour], O.[Quantity],

      T2.[SlpName],T5.[descript],DD.docnum Delivery,II.docnum Invoice

    From RDR1 O inner join ORDR OO on O.DocEntry=OO.DocEntry

       INNER JOIN OSLP T2 ON OO.SlpCode = T2.SlpCode

       full outer JOIN OITM T3 ON O.ItemCode = T3.ItemCode

       left JOIN OCRD T4 ON OO.CardCode = T4.CardCode

       left JOIN OTER T5 ON T4.Territory = T5.territryID

       left outer join DLN1 D on D.Basetype=17 and D.BaseEntry=O.DocEntry and D.BaseLine=O.LineNum

       left outer join ODLN DD on D.DocEntry=DD.DocEntry

       left outer join Inv1 I on (I.Basetype=15 and I.BaseEntry=D.DocEntry and I.BaseLine=D.LineNum)

                              or (I.Basetype=17 and I.BaseEntry=O.DocEntry and I.BaseLine=O.LineNum)

       left outer join OINV II on I.DocEntry=II.DocEntry

    Add comment
    10|10000 characters needed characters exceeded

    • Try This

      SELECT distinct T0.[DocDueDate], T0.[CardName],T0.[DocNum],  T0.[CardName],

      T1.[Dscription], a5.docnum,a3.docnum

      FROM ORDR T0

      INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

      LEFT JOIN INV1 a4 ON a4.BaseEntry=t0.DocEntry

      LEFT JOIN OINV a5 ON a5.DocEntry=a4.DocEntry

      inner join dln1 a2 on a2.baseentry = t0.docentry and t1.linenum = a2.baseline

      inner join odln a3 on a3.docentry = a2.docentry

      WHERE T0.[DocDueDate] =[%0]

      Regards

      Kennedy

  • avatar image
    Former Member
    Oct 16, 2012 at 03:35 PM

    Try:

    SELECT distinct T0.[DocDueDate], T0.[CardName],T0.[DocNum],  T0.[CardName],

    T1.[Dscription], T3.docnum,T5.docnum

    FROM ORDR T0

    INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

    INNER JOIN INV1 T2 ON T2.BaseEntry=t0.DocEntry and t2.BaseType = '17'

    INNER JOIN OINV T3 ON T3.DocEntry=T2.DocEntry AND T3.IsIns = 'Y'

    LEFT join dln1 T4 on T4.baseentry = t0.docentry and t1.linenum = T4.baseline AND BaseType = '13'

    LEFT join odln T5 on T5.docentry = T4.docentry

    WHERE T0.[DocDueDate] =[%0]

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Gordon,

      Thank you very much. OINV.IsIns='Y' (Reserve Invoices only) did the trick. I modified your query slightly in the bold fonts area as below

      SELECT distinct T0.[DocDueDate], T0.[CardName],T0.[DocNum],  T0.[CardName],

      T1.[Dscription], T3.docnum,T5.docnum,t3.docstatus

      FROM ORDR T0

      INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

      INNER JOIN INV1 T2 ON T2.BaseEntry=t0.DocEntry and t2.BaseType = '17'

      INNER JOIN OINV T3 ON T3.DocEntry=T2.DocEntry AND T3.IsIns = 'Y'

      full outer join dln1 T4 on T4.baseentry = t3.docentry

      full outer join odln T5 on T5.docentry = T4.docentry

      WHERE T0.[DocDueDate] ='%0'

      I then further added the remaining tables as below to pull out all the fields I wanted.

      SELECT distinct T0.[DocDueDate], T0.[CardName],T0.[DocNum],T3.[SuppCatNum], T0.[CardName],

      T1.[Dscription], T3.[FrgnName], T1.[Quantity],

      T2.[SlpName],T5.[descript],T7.docnum 'Invoice #',T9.docnum 'Delivery#', T0.DocNum 'SO#'

      FROM ORDR T0

      INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

      LEFT JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

      full JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

      full JOIN OCRD T4 ON T0.CardCode = T4.CardCode

      LEFT JOIN OTER T5 ON T4.Territory = T5.territryID

      full join inv1 t6 on t6.baseentry=t0.docentry and t6.basetype='17'

      full join oinv t7 on t7.docentry=t6.docentry and t7.isins='y'

      full outer join dln1 t8 on t8.baseentry = t7.docentry

      full outer join odln t9 on t9.docentry=t8.docentry

      WHERE  T0.[DocDueDate] =[%0]

      Thanks and kind regards,

      Henry