cancel
Showing results for 
Search instead for 
Did you mean: 

Find Order # for an Invoice

std_schristopher
Explorer
0 Kudos

We just implemented SAP - Business One on 01/08/2024. I am creating a query to find all invoices that were produced for a given month.  The only thing I can't figure out is how to find the associated sales order number for an invoice.

I was told by our trainers that there was no way to do that because the sales order # is only stored as a comment on the invoice header.

Any help is greatly appreciated.

View Entire Topic
LoHa
Active Contributor

I found a little Bug

please try this

SELECT
	 [OINV].[CardCode]
	,[OINV].[CardName]
	,[OINV].[DocNum]
	,[INV1].[ItemCode]
	,[INV1].[Dscription]
	/*Try to get a SalesOrder DocNum*/
	,COALESCE([ORDR].[DocNum],[ORDRD].[DocNum],'0') AS [SalesOrder DocNum]
	,DLN1.DocEntry
	,RDR1.DocEntry
FROM
	[OINV]
	INNER JOIN [INV1] ON [INV1].[DocEntry] = [OINV].[DocEntry]
	/*Invoice -> DelNote -> SalesOrder*/
	LEFT JOIN [DLN1] ON [DLN1].DocEntry = [INV1].BaseEntry AND [DLN1].[LineNum] = [INV1].[BaseLine] AND [DLN1].[ObjType] = [INV1].[BaseType]
	LEFT JOIN [RDR1] ON [RDR1].DocEntry = [DLN1].BaseEntry AND [RDR1].[LineNum] = [DLN1].[BaseLine] AND [RDR1].[ObjType] = [DLN1].[BaseType]
	LEFT JOIN [ORDR] ON [ORDR].DocEntry = [RDR1].DocEntry
	/*Invoice -> SalesOrder*/
	LEFT JOIN [RDR1] [RDR1D] ON [RDR1D].[DocEntry] = [INV1].[BaseEntry] AND [RDR1D].[LineNum] = [INV1].[BaseLine] AND [RDR1D].[ObjType] = [INV1].[BaseType]
	LEFT JOIN [ORDR] [ORDRD] ON [ORDRD].[DocEntry] = [RDR1D].[DocEntry]
WHERE 
	[OINV].[DocDate] = '20240220'
	AND
	[OINV].[DocType] = 'I'

regards Lothar