Conditional aggregation of VBAK/VBAP fields into single row in infoset

Question

I encounter the following problem.

---

What I want to do

I want to create an infoset that would regroup, for a given Purchase Order, data from the VBAK table with several lines from the VBPA table that should be dispatched to different fields.

Example : for the following PO 111005229 I would like to retrieve in a first field the KUNNR field for which PARVW = 'ER' and in a second field the ADRNR field for which PARVW = 'BP'.

Desired output :

---

What I've tried

Joining both tables in SQ02

I tried to declare the 2 tables VBAK and VBAP while running the SQ02 TCode and select the desired fields :

This doesn't work :

• just like in SE16N, the query returns every line of a purchase order when a single line with filtered data is wanted ;
• when using the CHECK VBPA-PARVW = 'ER'. in the Record processing code part hoping that this would shrink the number of lines to 1 for a given PO, no value at all is returned.

Declaring only VBAK table in SQ02

I also tried to declare only the VBAK table in SQ02, create the additional field PERNR_ER that I want and proceed to join VBAK with VBPA with openSQL code related to these specific fields.

I created the additional fields PARVW_ER and PERNR_ER with the following code embedded :

SELECT PARVW
    INTO PARVW_ER
    FROM VBPA
    WHERE VBELN = VBPA~VBELN.
    AND PARVW = 'ER'.
ENDSELECT.

SELECT PERNR
    INTO PERNR_ER
    FROM VBPA
    WHERE VBELN = VBPA~VBELN.
    AND PARVW = 'ER'.
ENDSELECT.

which gives as an output

I also tried

TYPES: begin of TY_TABLE,
    PARVW LIKE VBPA-PARVW,
    PERNR LIKE VBPA-PERNR,
    END OF TY_TABLE.

DATA: WA_TABLE TYPE TY_TABLE,
      IT_TABLE  TYPE TABLE OF TY_TABLE.

SELECT PARVW PERNR
    APPENDING CORRESPONDING FIELDS OF TABLE IT_TABLE
    FROM VBPA
    WHERE VBELN = VBPA~VBELN.

LOOP AT IT_TABLE INTO WA_TABLE.
  IF WA_TABLE-PARVW = 'ER'.
    PARVW_ER = WA_TABLE-PARVW.
    PERNR_ER = WA_TABLE-PERNR.
  ENDIF.
ENDLOOP.

but it returned the same.

---

How should I proceed to get the expected result?

Answer 1

Use an INNER JOIN on the same table and then filter out the unnecessary rows by PARVW.

REPORT YYY.

TABLES: vbpa.

START-OF-SELECTION.
  vbpa = VALUE #( vbeln = '111005229' parvw = 'SP' kunnr = '100007760' adrnr = '9000002718' ).
  INSERT vbpa.
  vbpa = VALUE #( vbeln = '111005229' parvw = 'BP' kunnr = '100007760' adrnr = '38110' ).
  INSERT vbpa.
  vbpa = VALUE #( vbeln = '111005229' parvw = 'PY' kunnr = '100007760' adrnr = '38110' ).
  INSERT vbpa.
  vbpa = VALUE #( vbeln = '111005229' parvw = 'SH' kunnr = '100007760' adrnr = '38110' ).
  INSERT vbpa.
  vbpa = VALUE #( vbeln = '111005229' parvw = 'ER' pernr = '8071' ).
  INSERT vbpa.

  SELECT t1~vbeln, t2~pernr AS field1, t1~adrnr AS field2
    FROM vbpa AS t1
    INNER JOIN vbpa AS t2
    ON t1~vbeln = t2~vbeln
    INTO TABLE @DATA(l_tab_vbpa)
      WHERE t1~parvw = 'BP'
        AND t2~parvw = 'ER'.

  LOOP AT l_tab_vbpa ASSIGNING FIELD-SYMBOL(<str_vbpa>).
    WRITE: / <str_vbpa>-vbeln, <str_vbpa>-field1, <str_vbpa>-field2.
  ENDLOOP.

  ROLLBACK WORK.

Result

Test

111005229 00008071 38110

Answer 2

You can achieve this via this simple double FOR loop:

TYPES: BEGIN OF ty_res,
         vbeln  TYPE vbeln,
         field1 TYPE vbpa-pernr,
         field2 TYPE vbpa-adrnr,
       END OF ty_res,
       tt_res TYPE STANDARD TABLE OF ty_res WITH EMPTY KEY.

DATA(lt_vbpa) = VALUE tab_vbpa( ).

APPEND VALUE #( vbeln = '111005229' parvw = 'SP' kunnr = '100007760' adrnr = '9000002718' ) TO lt_vbpa.
APPEND VALUE #( vbeln = '111005229' parvw = 'BP' kunnr = '100007760' adrnr = '38110' ) TO lt_vbpa.
APPEND VALUE #( vbeln = '111005229' parvw = 'PY' kunnr = '100007760' adrnr = '38110' ) TO lt_vbpa.
APPEND VALUE #( vbeln = '111005229' parvw = 'SH' kunnr = '100007760' adrnr = '38110' ) TO lt_vbpa.
APPEND VALUE #( vbeln = '111005229' parvw = 'ER' pernr = '8071' ) TO lt_vbpa.

DATA(result) = VALUE tt_res( FOR ls_vbpa_bp IN lt_vbpa WHERE ( parvw = 'BP' )
                             FOR ls_vbpa_er IN lt_vbpa WHERE ( parvw = 'ER' )
( vbeln  = ls_vbpa_bp-vbeln
  field1 = ls_vbpa_er-pernr
  field2 = ls_vbpa_bp-adrnr ) ).

result itab contains your desired output.

However, this will work only in this special case where you have only one line with BP function and one with ER partner function, which are attached with proper PERNR/ADRNR values. In other cases you will need grouping.

Answer 3

This tip from @SandraRossi comment helped me :

I think you are not aware of conversions between external and internal format. For PARVW, if you are connected with English language, when you see ER, it means the internal value ZM (cf table TPAUM).

I did not know that even with transaction code SE16N, fields could display converted data, i.e. it displayed ER in my language.

So the solution was to simply filter on ZM :

SELECT PERNR
    INTO PERNR_ER
    FROM VBPA
    WHERE VBPA~VBELN = VBAK-VBELN AND PARVW = 'ZM'.
ENDSELECT.