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.
Hi
I think you have done something incredible… It needs to be tried and seen, but I am thoroughly impressed even without that..
You deserve a great round of applause for this
Br. Ajay M
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
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.
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
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.
And almost forgot, you can also hide selection field using variant. You might want to include this in your blog.