Find Order # for an Invoice

[std_schristopher]

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.

[LoHa]

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

[LoHa]

Hi try this

SELECT
	 [OINV].[CardCode]
	,[OINV].[CardName]
	,[OINV].[DocNum]
	,[INV1].[ItemCode]
	,[INV1].[Dscription]
	/*Try to get a SalesOrder DocNum*/
	,COALESCE([ORDR].[DocNum],[ORDRD].[DocNum],'No SalesOrder as Base') AS [SalesOrder DocNum]
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 = [INV1].BaseEntry AND [RDR1].[LineNum] = [INV1].[BaseLine] AND [RDR1].[ObjType] = [INV1].[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] = GETDATE()

regards Lothar

[ANKIT_CHAUHAN]

Hi std_schristopher,

Refer to the columns BaseEntry, BaseType and BaseLine in INV1 table.

If BaseType = 17, that means your Invoice is created against a Sales Order.

BaseEntry is the DocEntry of the Sales Order document.

Hope it helps!

Kind regards,

ANKIT CHAUHAN

SAP Business One Support

[ANKIT_CHAUHAN]

Hi std_schristopher,

In addition to that, these columns are visible on the screen as well on rows.

Enable the columns Base Type, Base Ref., Base Key and Base Row using Form Settings.

Hope it helps!

Kind regards,

ANKIT CHAUHAN

SAP Business One Support

[Johan_Hakkesteegt]

Hi,

The problem is that technically you can base an invoice on:

• nothing
• (multiple) Quotation
• (multiple) Sales Order
• (multiple) Delivery

A delivery can be based on:

• nothing
• (multiple) Quotation
• (multiple) Sales Order

etc.

In general you would create one Sales Order, then you would copy that transaction to one Delivery Note, which you would then finally copy to one Invoice. However, the system allows you to base documents on certain rows and even quantities. Technically speaking you base the Invoice on base Document rows. A single Invoice can combine multiple deliveries, which in turn can cover multiple Sales Orders. It is also possible to copy a single Sale Order to multiple different Delivery Notes, each of which in turn can be copied to multiple Invoices. Because of this complexity there is no simple way to show the Sales Order an Invoice was based on.

The system allows you to navigate to Base and Target documents:

The 'Base Document...' button will open the transaction the Invoice was based on. Please note that it navigates by the chosen row. If you did not specifically select a row, the first row will be assumed.

For the purpose of a query, you must always work your way back from the invoice rows table INV1. The BaseEntry, BaseLine and BaseType fields together determine where to go next DLN1, RDR1 or QUT1. Each of which have their own BaseEntry, BaseLine and BaseType fields. In short, you can use a simple query, but it will only work if you work with the system absolutely consistently. One single deviation, and your query will no longer be reliable. Alternatively you can use a complicated query, like the one @LoHa has posted here. This query may become slow at some point once you have accumulated enough transaction data.

Regards,

Johan