Query Report Tips Part 3 – Efficient Data selection with BSEG Joining BKPF

Lots of folks out there must be wondering how to join BSEG with BKPF in infoset and query reports. Normally, system just give you an error which does not allow you to join. Let me show you how to do this in another way.

Step 1: Create Infoset SQ02

– Create a infoset with just Direct read table BSEG

Step 2: Declare additional variables in the code section and DATA area.

   DATA: X_BUDAT LIKE BKPF–BUDAT,
      X_MONAT LIKE BKPF–MONAT,
      X_BELNR LIKE BSEG–BELNR,
      I_WRBTR LIKE wmto_s–amount,
      lv_amount1 LIKE wmto_s–amount.

data: begin of I_BKPF occurs 0,
        bukrs  like bkpf–bukrs,
        belnr  like bkpf–belnr,
        gjahr  like bkpf–gjahr,
      end of I_BKPF.

Step 3: Create Additional Selection Field “S_BUKRS” as Mandatory and as a Single Field.

Step 4: Create Additional Selection Field “S_BUDAT” as Mandatory.

Step 5: Create Additional Selection Field “S_MONAT”. (Optional)

Step 6: Create Additional Selection Field “S_BELNR” as a hidden field”. This is the document no to be linked to BSEG.

Step 7: Put your cursor on the 1st selection field “S_BUKRS” and click on the code icon as shown below.

Step 8: Paste below code in as part of your authorization checks. Save the code.

   AUTHORITY-CHECK OBJECT ‘S_TCODE’
            ID ‘TCD’ FIELD ‘FB03’.
if sy–subrc ne 0.
   MESSAGE ‘You are not authorized to FB03.’ Type ‘E’.
endif.

AUTHORITY-CHECK OBJECT ‘F_BKPF_BUK’
            ID ‘BUKRS’ FIELD S_BUKRS–LOW
            ID ‘ACTVT’ FIELD ’03’.
if sy–subrc ne 0.
   MESSAGE ‘You are not authorized to Company Code.’ Type ‘E’.
endif.

Step 9: Now put cursor on the selection field “S_BELNR” and click on the Code Icon like step 7, Paste below code in and save.

   CLEAR: I_BKPF, S_BELNR.
REFRESH: I_BKPF, S_BELNR.
SELECT BUKRS BELNR GJAHR
       INTO TABLE I_BKPF
       FROM BKPF
       WHERE BUKRS IN S_BUKRS
        AND   BUDAT IN S_BUDAT
        AND   MONAT IN S_MONAT.
if sy–subrc = 0.
  LOOP AT I_BKPF.
    S_BELNR–SIGN = ‘I’.
    S_BELNR–OPTION = ‘EQ’.
    S_BELNR–LOW = I_BKPF–BELNR.
    APPEND S_BELNR.
    ENDLOOP.
ELSE.
  MESSAGE ‘No Data Exists…’ Type ‘E’.
ENDIF.

Step 10: Go back to Extras tab, and click on create icon. Create additional field “BUDAT” to show Posting date field from BKPF.

Step 11: Add an extra field like above step. Field is “WAERS” and Like reference is  BKPF-WAERS. Sequence of Code = 2

Step 12: Add other extra fields like Posting Period (BKPF-MONAT), etc.

Step 13: In the same Extra Tab, create Code. Once inside, you might want to give a sequence number 29 to field “Sequence of Code Section”.

Step 13: Paste below code inside and Save. if you have other extra fields from BKPF, be sure to modify the Clear and select statement from BKPF table.

CLEAR:  BUDAT, WAERS.
SELECT SINGLE BUDAT WAERS
  INTO (BUDAT, WAERS) FROM BKPF
  WHERE BELNR = bseg–belnr
  AND   BUKRS = bseg–bukrs
  AND   GJAHR = bseg–gjahr.
*Modify signs.
  IF BSEG–SHKZG = ‘H’.
    BSEG–DMBTR = 0 – BSEG–DMBTR.
    BSEG–WRBTR = 0 – BSEG–WRBTR.
  ENDIF.
*Make sure amount is in correct decimal.
  CLEAR: lv_amount1, I_WRBTR.
  MOVE BSEG–WRBTR TO lv_amount1.
    CALL FUNCTION ‘CURRENCY_AMOUNT_SAP_TO_DISPLAY’
    EXPORTING
      currency        = WAERS
      amount_internal = lv_amount1
    IMPORTING
      amount_display  = I_WRBTR
    EXCEPTIONS
      internal_error  = 1
      OTHERS          = 2.
MOVE I_WRBTR TO BSEG–WRBTR .

Step 14: Once done, you should have something like below:

Step 15: Now you can pull all the relevant fields you want to show in report to field groups.

Step 16: Save and Generate your infoset when you are done. Assign it to a User group.

Step 17: Create your query with SQ01 and you just need to select the List field.

Step 18: Execute your report and Good Luck.