Unique GR document in Select query

[bharath_padmanabhan]

Experts,

I am fetching Goods receipt documents for a Purchase Order from EKBE table. I am fetching documents for movement types 101(GR) and 102(GR reversal). In case any 101 document has a respective 102 document against it, then I should not show this document no in report output. So I am interested only in GRs for which 102 has not been done.

Currently, I fetch both 101 and 102 from EKBE. I loop through the internal table for 101 documents to see if there is a respective 102 against it. Then I delete both the 101 and 102 documents in loop. I will get the unique 101 documents in hand after this loop and the code is working fine.

I would like to know if we can filter out the 101 and the respective 102 against it directly in the select query while fetching the data. If this is feasible, then I can remove the unnecessary loop on internal table logic.

Please let me know your thoughts.

Thanks,

Bharath

[raymond_giuseppi]

Try a subquery where clause, select 101 code where 102 doesn't exist for same key values.

[bharath_padmanabhan]

Dear Raymond,

Thanks you so much for your valuable reply. Somehow it didn't strike me that I can achieve this right away with a subquery. Apologies for posting a basic question in the forum.

The linkage between 101 and 102 documents in EKBE is based on fields BELNR and LFBNR. So BELNR field in 101 record will be present in LFBNR field in 102 record. So I wrote the query using NOT IN keyword and it works perfectly.

Those who are looking for the solution can refer the below query:
SELECT * FROM ekbe
INTO TABLE @DATA(lt_ekbe)
WHERE ( bwart EQ '101' AND
ebeln eq '4500000486' AND
belnr NOT IN ( select lfbnr from ekbe where ebeln = '4500000486' AND bwart = '102' ) ).
In my actual ALV report, I applied the subquery concept as below:
SELECT _ekbe~ebeln,
_ekbe~ebelp,
_ekbe~belnr AS po_belnr, "Good Receipt
_ekbe~lfbnr,
_ekbe~bwart,
_ekbe~bldat AS po_bldat, "Document Date
_ekbe~budat AS po_budat, "Posting Date
_mseg~cpudt_mkpf AS po_grnentrydate
FROM ekbe AS _ekbe
INNER JOIN @lt_ekko AS _lt_ekko ##DB_FEATURE_MODE[ITABS_IN_FROM_CLAUSE]
ON _ekbe~ebeln = _lt_ekko~ebeln AND
_ekbe~ebelp = _lt_ekko~ebelp
INNER JOIN mseg AS _mseg
ON _ekbe~belnr = _mseg~mblnr AND
_ekbe~gjahr = _mseg~gjahr
WHERE ( _ekbe~bwart = '101' AND
_ekbe~belnr NOT IN ( SELECT lfbnr FROM ekbe
WHERE bwart = '102' AND
ebeln = _ekbe~ebeln AND
ebelp = _ekbe~ebelp ) )
ORDER BY _ekbe~ebeln, _ekbe~ebelp
INTO TABLE @DATA(lt_ekbe).