Skip to Content
Mar 01, 2022 at 06:12 AM

Sales and AR Invoice


Hi Experts,

can I have a query where I get the list of sales order and it should show the invoices against it and blank if no invoices.I would like all the sales orders open as well as closed

I Already got a query from Jithin showing all the sales order and invoice against it, but How can I get sales order with no invoices also

SELECT Distinct t0.DocNum 'Order Number'
		      , T0.DocDate 'Order Doc Date'
			  , T2.CardCode 'Customer Code'
			  , T2.CardName 'Customer Name'
			  , T2.NumAtCard 'Customer reference number'
			  , t1.ItemCode
			  , t1.Dscription
			  , T3.linestatus 'Order Row Status'
              , (t1.PriceBefDi*t1.rate)
              , t3.linetotal 
              , t3.vatsum 
              , t3.GTotal 
              , t1.linetotal 
              , t1.vatsum 
              , t1.GTotal
			  , T1.ActDelDate
			  , T0. DocDueDate
			  , T1.Project
			  , T1.SlpCode
			  , T2.DocStatus 'Invoice Status'
			  , T2.DocNum 'Invoice Number'
			  , T2.DocTotal 'Invoice Amount'
     INNER JOIN INV1 T1 ON T1.BaseEntry = T0.DocEntry AND T1.BaseType = '17'  
     INNER JOIN OINV T2 ON T2.DocEntry = T1.DocEntry  
     INNER JOIN RDR1 T3 on t0.DocEntry = t3.DocEntry AND T1.BaseLine = T3.LineNum
ORDER BY t0.DocNum