I'm trying to join ORDR, ODLN and OINV in a SAP Business One query, but I can't seem to find a field that they share in common.
There must be some record somewhere that links one to another.
Are they linked via a separate table? Or am I missing something obvious?
I am using SAP HANA as my DB, so queries in HANA are preferred rather than MSSQL.
First, credit to Eralper for their answer, as the link contained in it helped me find the solution I was looking for. However, their solution does not include an explanation and does not quite give the result that is being looked for.
The main information for a Sales Order in SAP is stored in two tables, ORDR and RDR1. ORDR has one line for each Sales Order, while RDR1 has one line for each product row on the Sales Order.
Delivery Notes and Invoices (and basically any document in SAP) follow this pattern.
Why is this important to this question? Because the column that contains the data to link Sales Orders, Delivery Notes and Invoices is in RDR1 (or the similar variant). It's name is TrgetEntry.
As there is a row for each product on a Sales Order, we can't simply do a join, as any Sales Order that has more than one product will appear multiple times in the result. The following query uses grouping to show a table that has a line for each Sales Order, and has the needed information to link it to Delivery Notes.
SELECT T0."DocEntry" AS "SO DE", T0."DocNum" AS "Sales Order Number", T1."TrgetEntry" AS "SO TE", COUNT(T0."DocNum") AS "Rows"
FROM ORDR T0
LEFT JOIN RDR1 T1 ON T0."DocEntry" = T1."DocEntry"
GROUP BY T0."DocEntry", T0."DocNum", T1."TrgetEntry"
By just changing the table names, similar queries can be created for Delivery Notes and Invoices.
Then you can use the TrgetEntry and DocEntry to link the various results.
The final code I use to show Sales Orders, their related Deliveries and Invoices is the following:
SELECT S0."SalesOrderNumber", S1."DeliveryNumber", S2."DocNum" AS "InvoiceNumber", S0."Rows", S2."DocTotal"
FROM (SELECT T0."DocEntry" AS "SO_DE", T0."DocNum" AS "SalesOrderNumber", T1."TrgetEntry" AS "SO_TE", COUNT(T0."DocNum") AS "Rows"
FROM ORDR T0
LEFT JOIN RDR1 T1 ON T0."DocEntry" = T1."DocEntry"
GROUP BY T0."DocEntry", T0."DocNum", T1."TrgetEntry") S0
LEFT JOIN (SELECT T0."DocEntry" AS "DN_DE", T0."DocNum" AS "DeliveryNumber", T1."TrgetEntry" AS "DN_TE"
FROM ODLN T0
LEFT JOIN DLN1 T1 ON T0."DocEntry" = T1."DocEntry"
GROUP BY T0."DocEntry", T0."DocNum", T1."TrgetEntry") S1 ON S0."SO_TE" = S1."DN_DE"
LEFT JOIN OINV S2 ON S1."DN_TE" = S2."DocEntry"