Skip to Content
0
Aug 14, 2020 at 07:15 AM

Link Quote to Invoice

71 Views

Hi all,

Im trying to get my query to display Quotes with linked invoice numbers (if they exist or not) where I will then filter from there.

I have the following query but for some reason i cant get an invoice number to show for any of the records, some of which i know exist. I've change the join around a few times but hasnt seem to fixed it...im sure its simple but im not seeing it.

SELECT DISTINCT T0.[DocNum], T0.[CardName], T0.[DocDate], T0.[DocTotal],T0.[DocStatus], T3.DocNum,T7.DocNum FROM OQUT T0  INNER JOIN QUT1 T1 ON T1.DocEntry = T0.DocEntry LEFT JOIN RDR1 T2 on T2.[BaseEntry] = T1.[DocEntry] AND T2.[BaseLine] = T1.[LineNum] LEFT JOIN ORDR T3 ON T2.[DocEntry] = T3.[DocEntry] 
left outer join DLN1 T4 on T4.BaseEntry = T2.DocEntry and T4.BaseLine = T2.Linenum
left outer join ODLN T5 on T4.DocEntry = T5.DocEntry
left Outer join INV1 T6 on T6.BaseEntry = T3.DocEntry and T6.BaseLine = T2.Linenum and T4.BaseType = 15
OR (T6.Basetype=17 and T6.BaseEntry=T2.DocEntry and T6.BaseLine=T2.LineNum)
left join OINV T7 ON T7.DocEntry = T6.DocEntry WHERE T0.[Series] = 78 AND  T0.[CANCELED] = 'N' ORDER BY T0.[DocDate] DESC

Any help will be appreciated as always.

Just a follow up question as well out of curiosity. This query will show multiple lines for some records because the sales order has been modified and doesn't match the original quote. Is there a way to circumvent this?

Regards,

Nick