Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

No. of records

Former Member
0 Kudos

Hello All,

I am selecting the Doc #s from BKPF and there by accessing the Line items from BSEG for that particular record:

BUKRS - US90, BELNR - 0000001, GJAHR - 2004

BUKRS - US90, BELNR - 0000002, GJAHR - 2004

When i access the BSEG the BELNR - 0000001 has 50 lineitems and BELNR - 0000002 has 30 entries.

So, on the list screen i need to display

US90 -


0000001 -


2004 -


50 (No need to display the lineitems)

US90 -


0000002 -


2004 -


30

Total No. of records - 80

Display each Document as one line document but with the Total Line items.

Regards,

- Sravan

8 REPLIES 8

Former Member
0 Kudos

You can start with this:


REPORT ztest.

DATA: bkpf TYPE bkpf,
      bseg TYPE bseg.

DATA: count TYPE i.

SELECT * FROM bkpf UP TO 100 ROWS.
  SELECT COUNT(*) FROM bseg
    INTO count
    WHERE bukrs = bkpf-bukrs
    AND   belnr = bkpf-belnr
    AND   gjahr = bkpf-gjahr.

  WRITE: /001 bkpf-bukrs, bkpf-belnr, bkpf-gjahr, count.

ENDSELECT.

Rob

0 Kudos

Alternate approach to avoide nested selects.

REPORT ztest.

types: begin of itabtype .

include structure bkpf .

types: count type i ,

end of itabtype .

DATA: bkpf_itab TYPE standard table of itabtype,

bkpf_wa like line of bkpf_itab ,

bseg TYPE bseg.

DATA: count TYPE i.

SELECT * FROM bkpf into corresponding fields of table bkpf_itab where <your where conditions> .

loop at bkpf_itab into bkpf_wa .

SELECT COUNT(*) FROM bseg

INTO count

WHERE bukrs = bkpf_wa-bukrs

AND belnr = bkpf_wa-belnr

AND gjahr = bkpf_wa-gjahr.

move: count to bkpf_wa-count .

modify bkpf_itab from bkpf_wa index sy-tabix.

clear bkpf_wa .

endloop .

Regards

Raja

0 Kudos

Hello All,

Thank you for your postings.

The output required on the List screen:

Step1:Document Listing

CompanyCode -- Fy -- Doc No -- Changeon Dt

(BWFI_AEDAT-BUKRS) -(GJAHR) -- (BELNR) -- (AEDAT)

US90 -- 2004 - 0000001 - 31-01-2006

US90 -- 2004 - 0000002 - 31-01-2006

Step2: Line Item count

With this info access the BSEG table to get the Line Items

CompanyCode -- Fy -- Doc No -- Changeon Dt -- LineItemCnt

US90 -- 2004 - 0000001 - 31-01-2006 -- 300

US90 -- 2004 - 0000002 - 31-01-2006 -- 010

  • At this point we should not print all the (300 or 10) line items but should give a count only.

Step3: Line item by Clearing T-Code

From Step 2 consider only those lineitems that have Clearing Document (AUGBL and AUGDT). From the AUGDT pick only the 4 digit Fy and access the BKPF table with the Company code, new AUGBL, and 4 digit GJAHR to get the T-Code and USnam.

I have completed the code and in Step2 all the Line Items are getting printed instead of only one line per document.

Code:

Step1-

select bukrs

aedat

belnr

gjahr

from bwfi_aedat

into table t_bwfi_aedat

where bukrs in s_bukrs and

belnr in s_belnr and

gjahr in s_gjahr and

aedat in s_aedat.

sort t_bwfi_aedat by bukrs belnr aedat gjahr.

loop at t_bwfi_aedat.

t_line1 = t_line1 + 1.

write:/001 '|',

002 t_bwfi_aedat-bukrs,

017 '|',

018 t_bwfi_aedat-belnr,

035 '|',

036 t_bwfi_aedat-gjahr,

049 '|',

050 t_bwfi_aedat-aedat,

075 '|'.

at end of aedat.

uline.

format color 2.

write:/02 'Total count by Changed on Date', t_bwfi_aedat-aedat,

'-', 70 t_line1.

format color off.

clear t_line1.

skip.

endat.

at last.

format color 2.

write:/02 'Total count by Company Code -',70 d_line.

format color off.

skip.

format color 3.

write:/02 'Grand total count -',70 d_line.

format color off.

endat.

endloop.

Step2:

if not t_bwfi_aedat[] is initial.

select bukrs

augdt

belnr

gjahr

augbl

into table t_bseg_bwfiaedat1

from bseg

for all entries in t_bwfi_aedat

where bukrs = t_bwfi_aedat-bukrs and

belnr = t_bwfi_aedat-belnr and

gjahr = t_bwfi_aedat-gjahr.

    • Gather docs which were changed

t_bseg_bwfiaedat2[] = t_bseg_bwfiaedat1[].

delete t_bseg_bwfiaedat2 where augbl is initial.

loop at t_bseg_bwfiaedat2.

t_bseg_bwfiaedat2-gjahr = t_bseg_bwfiaedat2-augdt(4).

modify t_bseg_bwfiaedat2.

endloop.

    • Get the doc headers from BKPF which were changed

if not t_bseg_bwfiaedat2[] is initial.

select bukrs gjahr belnr tcode usnam

into table t_bkpf

from bkpf

for all entries in t_bseg_bwfiaedat2

where bukrs = t_bseg_bwfiaedat2-bukrs and

belnr = t_bseg_bwfiaedat2-augbl and

gjahr = t_bseg_bwfiaedat2-gjahr.

endif.

sort t_bwfi_aedat by bukrs aedat belnr.

loop at t_bseg_bwfiaedat1.

t_line1 = t_line1 + 1.

read table t_bwfi_aedat with key bukrs = t_bseg_bwfiaedat1-bukrs

belnr = t_bseg_bwfiaedat1-belnr

gjahr = t_bseg_bwfiaedat1-gjahr.

t_bseg_bwfiaedat3-aedat = t_bwfi_aedat-aedat.

move-corresponding t_bseg_bwfiaedat1 to t_bseg_bwfiaedat3.

append t_bseg_bwfiaedat3.

endloop.

sort t_bseg_bwfiaedat3 by bukrs aedat belnr gjahr.

loop at t_bseg_bwfiaedat3.

LI_COUNT = LI_COUNT + 1.

CC_COUNT = CC_COUNT + 1.

GT_COUNT = GT_COUNT + 1.

DOC_CNT = DOC_CNT + 1.

at end of belnr.

write:/001 '|',

002 t_bseg_bwfiaedat3-bukrs,

016 '|',

017 t_bseg_bwfiaedat3-gjahr,

030 '|',

031 t_bseg_bwfiaedat3-belnr,

048 '|',

049 t_bseg_bwfiaedat3-aedat,

065 '|',

066 li_count,

075 '|'.

endat.

at end of aedat.

uline.

format color 2.

write:/02 'Total Line Items by Changed on Date - ', 70 li_count.

format color off.

clear li_count.

skip.

endat.

at end of bukrs.

format color 2.

write:/02 'Company code count', 70 CC_COUNT.

format color off.

clear CC_COUNT.

skip.

endat.

at last.

  • write:/02 'Total count by Company Code - ', cc_count.

  • skip.

format color 3.

write:/02 'Grand total count - ', 70 gt_count.

format color off.

clear gt_count.

endat.

at end of belnr.

move-corresponding t_bseg_bwfiaedat3 to t_tcode.

t_tcode-count = doc_cnt.

v_gjahr = t_bseg_bwfiaedat3-augdt(4).

read table t_bkpf with key

bukrs = t_bseg_bwfiaedat3-bukrs

belnr = t_bseg_bwfiaedat3-augbl

gjahr = v_gjahr.

if sy-subrc eq 0.

t_tcode-tcode = t_bkpf-tcode.

t_tcode-usnam = t_bkpf-usnam.

append t_tcode.

endif.

endat.

endloop.

    • Print Line Item by clearing T-Code

sort t_tcode by bukrs aedat tcode.

skip 2.

format color 1.

write:/02 'Line Item by Clearing T-Code

'.

format color off.

format color 1.

uline.

write:/01 '|',

02 text-020,

07 '|',

08 text-022,

14 '|',

15 text-021,

26 '|',

27 text-023,

38 '|',

39 text-024,

45 '|',

46 text-025,

57 '|',

58 text-026,

63 '|',

64 text-027,

75 '|'.

format color off.

uline.

loop at t_tcode.

tc_count = tc_tcount + 1.

dt_count = dt_count + 1.

gt_count = gt_count + 1.

write:/001 '|',

002 t_tcode-bukrs,

007 '|',

008 t_tcode-gjahr,

014 '|',

015 t_tcode-belnr,

026 '|',

027 t_tcode-aedat,

038 '|',

039 t_tcode-count,

045 '|',

046 t_tcode-augbl,

057 '|',

058 t_tcode-tcode,

063 '|',

064 t_tcode-usnam,

75 '|'.

uline.

skip.

at end of tcode.

format color 2.

write:/02 'Total on Line items by T-Code',70 tc_count.

format color off.

clear tc_count.

skip.

endat.

at end of aedat.

format color 2.

write:/02 'Total on line items by Changed on Date',70 dt_count.

format color off.

clear dt_count.

skip.

endat.

at last.

format color 3.

write:/02 'Grand Total', 70 gt_count.

format color off.

clear gt_count.

skip.

endat.

endloop.

Any pointers on how to avoid printing all the Line items only the entries from BSEG need to be picked.

Regards,

- Sravan

0 Kudos

Hi

you can try to use a code like this:

SELECT bukrs aedat belnr gjahr FROM bwfi_aedat INTO TABLE t_bwfi_aedat

WHERE bukrs IN s_bukrs AND

belnr IN s_belnr AND

gjahr IN s_gjahr AND

aedat IN s_aedat.

SORT t_bwfi_aedat BY bukrs aedat belnr gjahr.

LOOP AT t_bwfi_aedat.

t_line1 = t_line1 + 1.

d_line = d_line + 1.

WRITE:/001 '|',

002 t_bwfi_aedat-bukrs,

017 '|',

018 t_bwfi_aedat-belnr,

035 '|',

036 t_bwfi_aedat-gjahr,

049 '|',

050 t_bwfi_aedat-aedat,

075 '|'.

  • Remember this event is triggered whe the value of fields bukrs and

  • aedat is changing.

AT END OF aedat.

ULINE.

FORMAT COLOR 2.

WRITE:/02 'Total count by Changed on Date', t_bwfi_aedat-aedat,

'-', 70 t_line1.

FORMAT COLOR OFF.

CLEAR t_line1.

SKIP.

ENDAT.

AT LAST.

FORMAT COLOR 2.

WRITE:/02 'Total count by Company Code -',70 d_line.

FORMAT COLOR OFF.

SKIP.

FORMAT COLOR 3.

WRITE:/02 'Grand total count -',70 d_line.

FORMAT COLOR OFF.

ENDAT.

ENDLOOP.

CHECK sy-subrc = 0.

  • Step2:

TYPES: BEGIN OF ty_bseg,

bukrs TYPE bukrs,

belnr TYPE belnr_d,

gjahr TYPE gjahr,

augdt TYPE augdt,

augbl TYPE augbl,

aedat TYPE sy-datum,

END OF ty_bseg.

DATA t_bseg_bwfiaedat1 TYPE SORTED TABLE OF ty_bseg WITH NON-UNIQUE KEY

bukrs belnr gjahr.

*Step2:

SELECT bukrs augdt belnr gjahr augbl INTO TABLE t_bseg_bwfiaedat1

FROM bseg FOR ALL ENTRIES IN t_bwfi_aedat

WHERE bukrs = t_bwfi_aedat-bukrs AND

belnr = t_bwfi_aedat-belnr AND

gjahr = t_bwfi_aedat-gjahr.

LOOP AT t_bwfi_aedat.

li_count = 0.

LOOP AT t_bseg_bwfiaedat1 WHERE bukrs = t_bwfi_aedat-bukrs

AND belnr = t_bwfi_aedat-belnr

AND gjahr = t_bwfi_aedat-gjahr

LI_COUNT = li_count + 1.

IF t_bseg_bwfiaedat1-augdt IS INITIAL.

DELETE t_bseg_bwfiaedat1.

ELSE.

t_bseg_bwfiaedat1-aedat = t_bwfi_aedat-aedat.

  • You shouldn't use the fyscal yaer to find out the clearing document,

  • infact a document can be posted on date with different year than

  • fyscal year. It's possible to post a document on 2 Gennuary 2006 for

  • fyscal year 2005. So it should be better to use the clearing date,

  • infact it's the posting date for clearing document.

  • Anyway this situation depends on your FI customizing.

IF st_bkpf-bukrs <> t_bseg_bwfiaedat1-bukrs OR

st_bkpf-belnr <> t_bseg_bwfiaedat1-augbl OR

st_bkpf-budat <> t_bseg_bwfiaedat1-augdt.

SELECT bukrs gjahr belnr tcode usnam INTO st_bkpf

FROM bkpf WHERE bukrs = t_bseg_bwfiaedat1-bukrs AND

belnr = t_bseg_bwfiaedat1 AND

budat = t_bseg_bwfiaedat1-augdt.

EXIT.

ENDSELECT.

t_bseg_bwfiaedat1-tcode = ST_BKPF-TCODE.

t_bseg_bwfiaedat1-USNAM = ST_BKPF-USNAM.

MODIFY t_bseg_bwfiaedat1.

ENDIF.

ENDLOOP.

ae_count = li_count + ae_count.

cc_count = li_count + cc_count.

gt_count = li_count + gt_count.

doc_cnt = li_count + doc_cnt.

WRITE:/001 '|',

002 t_bwfi_aedat-bukrs,

016 '|',

017 t_bwfi_aedat-gjahr,

030 '|',

031 t_bwfi_aedat-belnr,

048 '|',

049 t_bwfi_aedat-aedat,

065 '|',

066 li_count,

075 '|'.

  • Remember this event is triggered whe the value of fields bukrs and

  • aedat is changing.

AT END OF aedat.

ULINE.

FORMAT COLOR 2.

WRITE:/02 'Total Line Items by Changed on Date - ', 70 li_count.

FORMAT COLOR OFF.

CLEAR ae_count.

SKIP.

ENDAT.

AT END OF bukrs.

FORMAT COLOR 2.

WRITE:/02 'Company code count', 70 cc_count.

FORMAT COLOR OFF.

CLEAR cc_count.

SKIP.

ENDAT.

AT LAST.

FORMAT COLOR 3.

WRITE:/02 'Grand total count - ', 70 gt_count.

FORMAT COLOR OFF.

CLEAR gt_count.

ENDAT.

ENDLOOP.

  • Here you have only cleared items.

    • Print Line Item by clearing T-Code

SORT t_bseg_bwfiaedat1 BY bukrs aedat tcode.

SKIP 2.

FORMAT COLOR 1.

WRITE:/02 'Line Item by Clearing T-Code'.

FORMAT COLOR OFF.

FORMAT COLOR 1.

ULINE.

WRITE:/01 '|',

02 text-020,

07 '|',

08 text-022,

14 '|',

15 text-021,

26 '|',

27 text-023,

38 '|',

39 text-024,

45 '|',

46 text-025,

57 '|',

58 text-026,

63 '|',

64 text-027,

75 '|'.

FORMAT COLOR OFF.

ULINE.

LOOP AT t_bseg_bwfiaedat1.

tc_count = tc_tcount + 1.

dt_count = dt_count + 1.

gt_count = gt_count + 1.

WRITE:/001 '|',

002 t_bseg_bwfiaedat1-bukrs,

007 '|',

008 t_bseg_bwfiaedat1-gjahr,

014 '|',

015 t_bseg_bwfiaedat1-belnr,

026 '|',

027 t_bseg_bwfiaedat1-aedat,

038 '|',

039 t_bseg_bwfiaedat1-count,

045 '|',

046 t_bseg_bwfiaedat1-augbl,

057 '|',

058 t_bseg_bwfiaedat1-tcode,

063 '|',

064 t_bseg_bwfiaedat1-usnam,

75 '|'.

ULINE.

SKIP.

AT END OF tcode.

FORMAT COLOR 2.

WRITE:/02 'Total on Line items by T-Code',70 tc_count.

FORMAT COLOR OFF.

CLEAR tc_count.

SKIP.

ENDAT.

AT END OF aedat.

FORMAT COLOR 2.

WRITE:/02 'Total on line items by Changed on Date',70 dt_count.

FORMAT COLOR OFF.

CLEAR dt_count.

SKIP.

ENDAT.

AT LAST.

FORMAT COLOR 3.

WRITE:/02 'Grand Total', 70 gt_count.

FORMAT COLOR OFF.

CLEAR gt_count.

SKIP.

ENDAT.

ENDLOOP.

Max

0 Kudos

Yes - I was pretty sure someone would catch me on that; however, doing the select inside a loop takes just about exactly the same time:


REPORT ztest_nesting.

DATA: start TYPE i,
      stop  TYPE i,
      dif   TYPE i.

PERFORM sel_1.         "To take care of any buffering
PERFORM sel_1.
PERFORM sel_2.
*&---------------------------------------------------------------------*
*&      Form  sel_1
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM sel_1.

  DATA: bkpf TYPE bkpf,
        bseg TYPE bseg.
  DATA: count TYPE i.

  GET RUN TIME FIELD start.
  SELECT * FROM bkpf UP TO 100 ROWS.
    SELECT COUNT(*) FROM bseg
      INTO count
      WHERE bukrs = bkpf-bukrs
      AND   belnr = bkpf-belnr
      AND   gjahr = bkpf-gjahr.

  ENDSELECT.
  GET RUN TIME FIELD stop.
  dif = stop - start.
  WRITE: /001 'Time for nested select:', dif, 'microseconds'.

ENDFORM.                                                    " sel_1
*&---------------------------------------------------------------------*
*&      Form  sel_2
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM sel_2.

  TYPES: BEGIN OF itabtype .
          INCLUDE STRUCTURE bkpf .
  TYPES: count TYPE i ,
  END OF itabtype .


  DATA: bkpf_itab TYPE STANDARD TABLE OF itabtype,
  bkpf_wa LIKE LINE OF bkpf_itab ,
  bseg TYPE bseg.

  DATA: count TYPE i.

  GET RUN TIME FIELD start.
  SELECT * FROM bkpf
    INTO CORRESPONDING FIELDS OF TABLE bkpf_itab
    UP TO 100 ROWS.

  LOOP AT bkpf_itab INTO bkpf_wa .
    SELECT COUNT(*) FROM bseg
    INTO count
    WHERE bukrs = bkpf_wa-bukrs
    AND belnr = bkpf_wa-belnr
    AND gjahr = bkpf_wa-gjahr.
    MOVE: count TO bkpf_wa-count .
    MODIFY bkpf_itab FROM bkpf_wa INDEX sy-tabix.
    CLEAR bkpf_wa .
  ENDLOOP .
  GET RUN TIME FIELD stop.
  dif = stop - start.
  WRITE: /001 'Time for select in nested loop:', dif, 'microseconds'.

ENDFORM.                                                    " sel_2

I'm sure there are faster ways to do this, but...

Rob

0 Kudos

how to count no.of record as cpudt(date) in bkpf. pls help

Former Member
0 Kudos

HI sravan

loop at i_bkpf.

clear i_bseg.

refresh i_bseg.

count = 0.

select * into i_bseg from bseg where belnr = i_bkf-belnr.

count = sy-dbcnt.

write: i_bkpf-burks, i_bkpf-belnr, i_bkpf-gjahr , count.

endloop.

regards

kishore

Former Member
0 Kudos

hi,

in the data i've described the entire structure for the internal table for sample.. you may just take the required fields ..

data : i_bkpf like table of bkpf with header line,count type i , total type i.
data : begin of i_bseg occurs 0,
        bukrs like bseg-bukrs,
        gjahr like bseg-gjahr,
        belnr like bseg-belnr,
       end of i_bseg.       

select single * into i_bkpf from bkpf.

if i_bkpf is not initial.
select bukrs gjahr belnr
into table i_bseg 
from bseg
for all entries in i_bkpf
where bukrs = i_bkpf-bukrs.

loop at i_bseg.
count = count + 1.
total = sy-index.
  at end of belnr .
   write : i_bseg-bukrs ,  i_bseg-belnr ,  i_bseg-gjahr , count.
   clear count.
end at.
  at last.
  write : 'total No. of records. ' , total.
  endat.  
endloop.

hope this will work.

regards

satesh