eban table

[aris_hidalgo]

hello experts,

I am trying to modify a select statement where instead of using cooi-matnr I want to use eban-matnr. The problem is how can I join the table EBAN with PRPS and COOI? Do I really need to add eban using join? or can I make another select statement?I have pasted below the select statement that I am trying to modify. Thanks guys and take care!

SELECT a_{psphi b}refbn b~lifnr

FROM prps AS a INNER JOIN cooi AS b

ON a_{objnr = b}objnr

INTO CORRESPONDING FIELDS OF it_hdr

WHERE a~psphi IN so_psphi

AND a~pbukr = pa_bukrs

AND a~erdat LE v_budat

AND b~refbn IN so_ebeln

AND b~lifnr IN so_lifnr

AND b~matnr IN so_matnr

AND b~rfart = 'P'.

[aris_hidalgo]

ooops, here is the complete code:

SELECT a_{psphi b}refbn b~lifnr

FROM prps AS a INNER JOIN cooi AS b

ON a_{objnr = b}objnr

INTO CORRESPONDING FIELDS OF it_hdr

WHERE a~psphi IN so_psphi

AND a~pbukr = pa_bukrs

AND a~erdat LE v_budat

• AND a~erdat in so_augdt

AND b~refbn IN so_ebeln

AND b~lifnr IN so_lifnr

AND b~matnr IN so_matnr

AND b~rfart = 'P'.

SELECT SINGLE name1 FROM lfa1 INTO it_hdr-name1

WHERE lifnr = it_hdr-lifnr.

if not pa_augdt is initial.

• IF NOT so_augdt[] IS INITIAL.

CLEAR ekko.

SELECT SINGLE * FROM ekko

WHERE ebeln = it_hdr-refbn

• AND bedat IN so_augdt.

and bedat LE pa_augdt.

IF sy-subrc EQ 0.

APPEND it_hdr.

CLEAR it_hdr.

ENDIF.

ELSE.

APPEND it_hdr.

CLEAR it_hdr.

ENDIF.

ENDSELECT.

[Former Member]

SELECT a_{psphi b}refbn b~lifnr

FROM ( prps AS a INNER JOIN cooi AS b

ON a_{objnr = b}objnr ) INNER JOIN eban

ON b_{matnr = eban}matnr

INTO CORRESPONDING FIELDS OF it_hdr

WHERE a~psphi IN so_psphi

AND eban~lifnr IN so_lifnr

AND a~pbukr = pa_bukrs

AND a~erdat LE v_budat

AND b~refbn IN so_ebeln

AND b_{lifnr EQ eban}lifnr

AND b~matnr IN so_matnr

AND b~rfart = 'P'.

[aris_hidalgo]

Hello wenceslaus,

I tried using your code but I still seem not to find any records when I type a certain matnr in the selection screen. But it works fine when I use other selection instead of putting the material number.

[Former Member]

SELECT a_{psphi b}refbn b~lifnr

FROM ( prps AS a INNER JOIN cooi AS b

ON a_{objnr = b}objnr )

-

---

u can try this join eban and c on the same as matnr is common .

-

---

<<<<<<<<<<<<<<<<<<<<<<

as b INNER JOIN eban as c on

ON b_{matnr = c}matnr

*************************

INTO CORRESPONDING FIELDS OF it_hdr

WHERE a~psphi IN so_psphi

AND eban~lifnr IN so_lifnr

AND a~pbukr = pa_bukrs

AND a~erdat LE v_budat

AND b~refbn IN so_ebeln

AND b_{lifnr EQ eban}lifnr

AND b~matnr IN so_matnr

AND b~rfart = 'P'.

[aris_hidalgo]

Hello Kan,

Should I change the b_{matnr in so_matnr to c}matnr in so_matnr?Also, will joining cooi and eban on matnr affect the selection of records?Thanks!

[Former Member]

The common fields are EBAN( banfn , bnfpo)

And COOI(refbn,rfpos) for the two tables here is the main join criteria.

And matnr is in both the tables, in addition to the above So u can go for it .

The flow as per ur code is from COOI to EBAN so no need t change so_matnr.

In case u don’t get a hit try that option.

hi ,

Joining cooi and eban will not affect the code just check for a particular record in cooi table the first criteria as u need to look into items as well .

Initially select the record for minimum range in ur select options okay.

Then next check the same in the table cooi and eban.

Don’t worry as both the tables are transparent table.

let us know after joining the eban with cooi if u ra able to retrieve the correct record .

vijay.

[aris_hidalgo]

If its not too much, can you please give me a sample code.Thanks so much!