cancel
Showing results for 
Search instead for 
Did you mean: 

Link Quote to Invoice

lsauser
Participant
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

lsauser
Participant
0 Kudos

Ended up using the delivery num as my reference point without needing to go all the way to invoice. If delivery num existed then it wouldnt show those records.

Answers (1)

Answers (1)

Johan_H
Active Contributor
0 Kudos

Hi Nick,

When the link between Quote and invoice is not direct, this query is going to be very difficult to write. This is because the path from a quote to an invoice, can go different ways:

Quote-->Invoice

Quote-->Delivery-->Invoice

Quote-->Sales Order-->Delivery-->Invoice

Quote-->Sales Order-->Invoice

They are all linked in reverse order, and your follow question reveals that there can be multiple links from line to line.

So please first try to reverse the order of your tables in the FROM clause. To avoid multiple records per quote, you could try the solution I used in your other question, and combine all target document numbers into a single column.

Regards,

Johan

lsauser
Participant
0 Kudos

Hey johan.hakkesteegt ,

The problem with going backwards is i want to display quote information. I only need an invoice number to determine whether or not to show that record.

i.e show only open quotes and closed quotes that were converted to open sales orders

Regards,

Nick

Johan_H
Active Contributor
0 Kudos

Hi Nick,

One of your joins is a bit of a mess:

left Outer join INV1 T6 on T6.BaseEntry = T3.DocEntry and T6.BaseLine = T2.Linenum and T4.BaseType =15

When you correct it, you get results, though still with doubles.

Regards,

Johan