Skip to Content

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

Capture1.JPG

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

   DATA: X_BUDAT LIKE BKPFBUDAT,
      X_MONAT LIKE BKPFMONAT,
      X_BELNR LIKE BSEGBELNR,
      I_WRBTR LIKE wmto_samount,
      lv_amount1 LIKE wmto_samount.

data: begin of I_BKPF occurs 0,
        bukrs  like bkpfbukrs,
        belnr  like bkpfbelnr,
        gjahr  like bkpfgjahr,
      end of I_BKPF.

Capture0.JPG

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

Capture2.JPG

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

Capture4.JPG

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

Capture5.JPG

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

Capture6.JPG

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

Capture7.JPG

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 sysubrc ne 0.
   MESSAGE ‘You are not authorized to FB03.’ Type ‘E’.
endif.

AUTHORITY-CHECK OBJECT ‘F_BKPF_BUK’
            ID ‘BUKRS’ FIELD S_BUKRSLOW
            ID ‘ACTVT’ FIELD ’03’.
if sysubrc 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 sysubrc = 0.
  LOOP AT I_BKPF.
    S_BELNRSIGN = ‘I’.
    S_BELNROPTION = ‘EQ’.
    S_BELNRLOW = I_BKPFBELNR.
    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.

Capture10.JPG

Capture10a.JPG

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”.

Capture12.JPG

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 = bsegbelnr
  AND   BUKRS = bsegbukrs
  AND   GJAHR = bseggjahr.
*Modify signs.
  IF BSEGSHKZG = ‘H’.
    BSEGDMBTR = 0 BSEGDMBTR.
    BSEGWRBTR = 0 BSEGWRBTR.
  ENDIF.
*Make sure amount is in correct decimal.
  CLEAR: lv_amount1, I_WRBTR.
  MOVE BSEGWRBTR 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 BSEGWRBTR .

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

Capture11.JPG

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

Capture13.JPG

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.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Christian Ortner

    Hi,

    why don’t you use logical database BRF or BRM as datasource in the infoset? BKPF and BSEG (amongst others) are already joined there, logical databases as a whole are included in the authorization check,….

    So whats the advantage of doing that in the way you’ve described?

    BR Christian

    (0) 
    1. Alan Love Post author

      With Logical Database, you cannot choose which field as the mandatory field. You can’t choose which field should be the selection fields. You may end up in a dump due to long running time or hang up the whole SAP.

      (0) 
      1. Christian Ortner

        Hi,

        you argued “You may end up in a dump due to long running time or hang up the whole SAP.”

        But thats not a problem of logical database / own defined data source usage. Its more about user behaviour, report design in general,…

        By the way, you can define mandatory fields in LDB’s the same way as you did it in Step 3. Just move the field value of this own defined mandatory selection entry to the selection criteria (eg BUKRS) the logical database requires.

        You can choose (additional) selection fields when using LDB’s.

        As per now, I still can’t see the advantage of your solution compared to the usage of SAP standard logical databases.

        BR Christian

        (0) 
        1. Alan Love Post author

          Not sure how you do it, perhaps you can share how you do in step 3, without showing 2 company code field to user. You add additional selection field like company code, you end up having 2 company code field in selection screen. Of course, the way out is to write some code in selection screen output to supress the unwanted selection fields.

          There are people out there who wants to customize the selection screen, but just could not do it with LDB.

          If you still prefer LDB is your choice. There are folks out there will like a customized join for BKPF and BSEG and have end up creating a new program with more effort. Perhaps you can write a blog on LDB with customized selection screen output.

          Just to share another way of doing this.

          (0) 

Leave a Reply