A sample code to get record count from BSEG based on the BKPF values?

[Former Member]

hi ABAP4 experts,

We are trying to know how many records in BSEG(Accounting Document Segment) based on the parameter values of BKPF(Accounting Document Header). The parameter values of BKPF we know are:

1. Company Code (BUKRS) range from Z100 to Z500

2. Document type (BLART) need to input so many ranges:

ZA

ZE

ZT

ZG

Y2

Y4

U4

U8

3. Doc.status (BSTAT) is not equal to 'S'

Based on the three conditions in BKPF, we would like to know record count in BSEG. We will be very appreciated if some experts here give us the sample code and we sure will give you reward points.

Thanks in advance!

[Former Member]

hi ABAP4 experts,

We are trying to know how many records in BSEG(Accounting Document Segment) based on the parameter values of BKPF(Accounting Document Header). The parameter values of BKPF we know are:

1. Company Code (BUKRS) range from Z100 to Z500

2. Document type (BLART) need to input so many ranges:

ZA

ZE

ZT

ZG

Y2

Y4

U4

U8

3. Doc.status (BSTAT) is not equal to 'S'

Based on the three conditions in BKPF, we would like to know record count in BSEG. We will be very appreciated if some experts here give us the sample code and we sure will give you reward points.

Thanks in advance!

[Former Member]

hi

first use the select on the BKPF table based on your condition and populate an internal table ..

populate the ranges..

RANGES R_BUKRS FOR BKPF-BUKRS.
RANGES R_BLART  FOR BKPF_BLART.


R_bukrs-SIGN = 'I'.
R_bukrs-OPTION = 'BT'.
R_bukrs-LOW = 'Z100'.
R_bukrs-HIGH = 'Z500'.
APPEND R_bukrs.

R_blart-SIGN = 'I'.
R_blart-OPTION = 'EQ'.
R_blart-LOW = 'ZA'.
APPEND R_blart.

R_blart-SIGN = 'I'.
R_blart-OPTION = 'EQ'.
R_blart-LOW = 'ZE'.
APPEND R_blart.

R_blart-SIGN = 'I'.
R_blart-OPTION = 'EQ'.
R_blart-LOW = 'ZT'.
APPEND R_blart.

R_blart-SIGN = 'I'.
R_blart-OPTION = 'EQ'.
R_blart-LOW = 'ZG'.
APPEND R_blart.

R_blart-SIGN = 'I'.
R_blart-OPTION = 'EQ'.
R_blart-LOW = 'Y2'.
APPEND R_blart.

R_blart-SIGN = 'I'.
R_blart-OPTION = 'EQ'.
R_blart-LOW = 'Y4'.
APPEND R_blart.

R_blart-SIGN = 'I'.
R_blart-OPTION = 'EQ'.
R_blart-LOW = 'U4'.
APPEND R_blart.

R_blart-SIGN = 'I'.
R_blart-OPTION = 'EQ'.
R_blart-LOW = 'U8'.
APPEND R_blart.

 
select bukrs blart bstat 
  into table itab1 
  from BKPF 
  where BUKRS in R_bukrs
  and BLART in R_blart
  and BSTAT NE 's'.

SELECT count(*) 
  into count 
 from BSEG
 FOR ALL ENTRIES in itab1.

regards

satesh

Message was edited by: Satesh R

Message was edited by: Satesh R

[Former Member]

hi Satesh,

How are itab1 and count defined in the code?

Thanks

[Former Member]

HI Kevin,

define itab1 like this,,

 data : begin of itab1 occurs 0,
        bukrs like bkpf-bukrs,
        blart like bkpf-blart,
        bstat like bkpf-bstat,
       end of itab1.

 data count type i.

this will do..

regards

satesh

[Former Member]

hi Satesh,

We are really appreciated your input and help! When we activated the code, get the following syntax error:

The addition "FOR ALL ENTRIES IN itab" is allowed only with a WHERE condition.

How to resolove the above syntax error? Also if we want to sum up the total amount of a field WRBTR (document currency) of BSEG based on the above selections, then what would the code look like?

Thanks a lot!

[Former Member]

Hi kevin,

1. u are right.

2. we have to give like this.

(see in bold)

SELECT count(*)

into count

from BSEG

FOR ALL ENTRIES in itab1

<b> where belnr = itab1-belnr

and bukrs = itab1-bukrs

and gjahr = itab1-gjahr.</b>

regards,

amit m.

[Former Member]

hi Amit,

Yes, you are right! But could you answer our 2nd question that if we want to sum up the total amount of WRBTR (document amount) of this BSEG based on the above selections, how the code would look like?

Thanks a lot!

[Former Member]

Hi again,

1. like this :

DATA : BEGIN OF sitab OCCURS 0,

wrbtr LIKE bseg-wrbtr,

END OF sitab.

LOOP AT itab1.

MOVE-CORRESPONDING itab1 TO sitab.

COLLECT sitab.

ENDLOOP.

regards,

amit m.

[Former Member]

hi Amit,

Yes, you are right that if we use Ferry's code, then get the following error, "Aggregate functions and the addition DISTINCT are not supported in field lists for pooled and cluster tables.".

However your code maybe not correct since itab1 is kind of a selection internal table and similar to BKPF other than BSEG that inside itab1, there is no field WRBTR. WRBTR only exists in BSEG. We would have to look for solution based on the following:

SELECT count(*)

into GL_count

from BSEG

FOR ALL ENTRIES in itab1

where belnr = itab1-belnr

and bukrs = itab1-bukrs

and gjahr = itab1-gjahr.

as a clue to see how to sum up the total amount of WRBTR of the BSEG table based on the above selections.

Thanks you all and I am giving you all the points, but to see who can give us a final solution for 10 points though.

Looking forward to your answer!

[Former Member]

hi Kevin,

for that you can do like this..

data : itab2 like table of BSEG with header line.
SELECT *
into TABLE itab
from BSEG
FOR ALL ENTRIES in itab1
where belnr = itab1-belnr
and bukrs = itab1-bukrs
and gjahr = itab1-gjahr.

loop at itab2.
 sum.
 GL_count = GL_count + 1.
endloop.
total = itab2-WRBTR.

but this wud have a poor performance..

regards

satesh

[Former Member]

Hi Kevin,

You can code like this.

data: WRBTR type BSEG-WRBTR.


SELECT count(*) sum ( WRBTR )
into (count, WRBTR) 
from BSEG
FOR ALL ENTRIES in itab1
where belnr = itab1-belnr
and bukrs = itab1-bukrs
and gjahr = itab1-gjahr.

Hope this will help.

Regards,

Ferry Lianto

[Former Member]

Hi again,

1. using SUM directly in sql,

won't work bcos

BSEG is a pooled table

and abap will give error as :

<b>Aggregate functions and the addition DISTINCT are not supported in field lists for pooled and cluster tables. </b>

regards,

amit m.

[Former Member]

Hi Kevin,

Apologies ... I do not have SAP system now therefore I can not do syntac check.

I believe you can code like this.

DATA: BEGIN OF ITAB2 OCCURS 0,
        BELNR LIKE BSEG-BELNR,
        BURKS LIKE BSEG-BURKS, 
        GJAHR LIKE BSEG-GJAHR,
        WRBTR LIKE BSEG-WRBTR.
DATA: END OF ITAB2.


DATA: WA_WRBTR TYPE BSEG-WRBTR,
      WA_COUNT TYPE I.


SELECT BELNR BURKS GJAHR WRBTR 
INTO TABLE ITAB2 
FROM BSEG
FOR ALL ENTRIES IN ITAB1
WHERE BELNR = ITAB1-BELNR
  AND BURKS = ITAB1-BURKS
  AND GJAHR = ITAB1-GJAHR.

LOOP AT ITAB2.
  WA_WRBTR = WA_WRBTR + ITAB2-WRBTR.
  WA_COUNT = WA_COUNT + 1.
ENDLOOP.


WRITE: / 'TOTAL BSEG-WRBTR:', WA_WRBTR,
         'TOTAL COUNT:', WA_COUNT.

Hope this will help.

Regards,

Ferry Lianto

[Former Member]

hi Ferry/Satesh,

Actually your two codes give the same effect, but now the count is different and incorrect now.

The correct count with the following code:

SELECT count(*)

into GL_count

from BSEG

FOR ALL ENTRIES in itab1

where belnr = itab1-belnr

and bukrs = itab1-bukrs

and gjahr = itab1-gjahr.

is correct and the count is 607, but now after using the following code:

SELECT BELNR BURKS GJAHR WRBTR

INTO TABLE ITAB2

FROM BSEG

FOR ALL ENTRIES IN ITAB1

WHERE BELNR = ITAB1-BELNR

AND BURKS = ITAB1-BURKS

AND GJAHR = ITAB1-GJAHR.

LOOP AT ITAB2.

WA_WRBTR = WA_WRBTR + ITAB2-WRBTR.

WA_COUNT = WA_COUNT + 1.

ENDLOOP.

WRITE: / 'TOTAL BSEG-WRBTR:', WA_WRBTR,

'TOTAL COUNT:', WA_COUNT.

The count is 397 which is incorrect.

How to resolve the above problem?

Looking forward to seeing your answers soon!

[Former Member]

Hi Kevin,

Do you know the key fields of table BSEG? I don't have SAP system now. I believe my code did not pass enough key field to count number of records. Therefore, system passed less records.

Perhaps like this,

DATA: BEGIN OF ITAB2 OCCURS 0,
        BELNR LIKE BSEG-BELNR,
        BURKS LIKE BSEG-BURKS, 
        GJAHR LIKE BSEG-GJAHR,
        <b>BUZEI LIKE BSEG-BUZEI,</b>
        WRBTR LIKE BSEG-WRBTR.
DATA: END OF ITAB2.
 
 
DATA: WA_WRBTR TYPE BSEG-WRBTR,
      WA_COUNT TYPE I.
 
 
SELECT BELNR BURKS GJAHR <b>BUZEI</b> WRBTR 
INTO TABLE ITAB2 
FROM BSEG
FOR ALL ENTRIES IN ITAB1
WHERE BELNR = ITAB1-BELNR
  AND BURKS = ITAB1-BURKS
  AND GJAHR = ITAB1-GJAHR.
 
LOOP AT ITAB2.
    WA_COUNT = WA_COUNT + 1.
    WA_WRBTR = WA_WRBTR + ITAB2-WRBTR.
ENDLOOP.
 
 
WRITE: / 'TOTAL BSEG-WRBTR:', WA_WRBTR,
         'TOTAL COUNT:', WA_COUNT

Also when you sum WRBTR, please include field to determine debit/credit sign.

Hope this will help.

Regards,

Ferry Lianto

[Former Member]

hi Ferry,

Yes, you are right! After we add BUZEI into the selection into itab2, it works with the correct count.

When you say "Also when you sum WRBTR, please include field to determine debit/credit sign.", there is a field SHKZG (Debit/Credit Indicator) with a value of either 'S' for debit or 'H' for credit, maybe we can use this field to determine the value of WRBTR is + or -, right? Then how would the code look like?

Thanks alot!

[Former Member]

hi Ferry,

We've already used the same code as yours to have the problem resolved and thanks alot!

[Former Member]

Hi Kevin,

You code like this.

DATA: BEGIN OF ITAB2 OCCURS 0,
        BELNR LIKE BSEG-BELNR,
        BURKS LIKE BSEG-BURKS, 
        GJAHR LIKE BSEG-GJAHR,
        BUZEI LIKE BSEG-BUZEI,
        WRBTR LIKE BSEG-WRBTR,
        <b>SHKZG like bseg-SHKZG.</b>
DATA: END OF ITAB2.
 
 
DATA: WA_WRBTR TYPE BSEG-WRBTR,
      WA_COUNT TYPE I.
 
 
SELECT BELNR BURKS GJAHR BUZEI WRBTR SHKZG
INTO TABLE ITAB2 
FROM BSEG
FOR ALL ENTRIES IN ITAB1
WHERE BELNR = ITAB1-BELNR
  AND BURKS = ITAB1-BURKS
  AND GJAHR = ITAB1-GJAHR.
 
LOOP AT ITAB2.
    WA_COUNT = WA_COUNT + 1.
        
    <b>IF ITAB2-SHKZG = 'S'. 
      WA_WRBTR = WA_WRBTR + ITAB2-WRBTR * -1.
    ELSE.
     WA_WRBTR = WA_WRBTR + ITAB2-WRBTR.
    ENDIF.</b>
 ENDLOOP. 
 
WRITE: / 'TOTAL BSEG-WRBTR:', WA_WRBTR,
         'TOTAL COUNT:', WA_COUNT.

Hope this will help.

Regards,

Ferry Lianto

[Former Member]

Glad to help you.

Regards,

Ferry Lianto

[Former Member]

hi Ferry,

If we just want to check how many records and calculate a total amount for a specific field, e.g., DMBTR in a system table, e.g., BSEG. Note: there is no any selection for this table BSEG, we just want to get the total record count in this table and also the total amount for a specific field e.g. DMBTR in this table.

Do we have to use an internal table to transfer all the records of BSEG into the internal table to get the result? Also would be appreciated if you can provide the code!