What is the correct joining logic between EKKO
, EKPO
and EKBE
tables ?
I need to get all the Good Receipts done on a Purchasing Order. Below is the logic I use currently:
select "required columns"
from EKKO
inner join EKPO on EKKO.EBELN = EKPO.EBELN
left join EKBE on EKPO.EBELN = EKBE.EBELN and EKPO.EBELP = EKBE.EBELP
where EKBE.BEWTP = 'E'
EKKO - Purchasing Document Header.
Header (main) table for the purchasing document - one entry for each purchasing document.
Key field | |
---|---|
EBELN | Purchasing Document Number |
EKPO - Purchasing Document Item
Items table of the purchasing document - many entries for one purchasing document are possible.
Key field | |
---|---|
EBELN | Purchasing Document Number |
EBELP | Item Number of Purchasing Document |
EKBE - History per Purchasing Document
History table - many entries for each of the purchasing document items are possible.
Key field | |
---|---|
EBELN | Purchasing Document Number |
EBELP | Item Number of Purchasing Document |
..... | Number of other fields |
It depends on what data exactly you want to read. As join of EKKO
and EKPO
is the join of 1 - N relation tables (and it is also true for the EKPO
and EKBE
tables), the data selected from the tables with less cardinality would be duplicated in the result set (in this case, for example, "required columns" from EKKO would be the same for every line in the result set, and from EKPO for every line in the result set per document position).
You can also just select all goods receipts entries for the purchasing document from EKBE
table without joins if you do not need to select additional data from EKKO / EKPO tables:
SELECT fields_you_need FROM ekbe INTO TABLE @DATA(lt_ekbe)
WHERE bewtp = 'E'
AND ebeln = 'XXXXXXXXXX'