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’.
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’.
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
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.
MESSAGE ‘No Data Exists…’ Type ‘E’.
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.
IF BSEG–SHKZG = ‘H’.
BSEG–DMBTR = 0 – BSEG–DMBTR.
BSEG–WRBTR = 0 – BSEG–WRBTR.
*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’
currency = WAERS
amount_internal = lv_amount1
amount_display = I_WRBTR
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.