Community
Application Development and Automation 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
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

BSEG extract

Go to solution
Former Member

‎2006 Oct 20 5:20 PM

0 Likes
2,189

BSEG is a cluster table and I need to improve the performance of the extract as given below. This is really taking a long time to run. Any suggestions as to how I can improve the performance?

SELECT BELNR

BUZEI

BSCHL

SHKZG

PRCTR

KOSTL

HKONT

DMBTR

WRBTR

SGTXT

FROM BSEG

INTO TABLE IBSEG

FOR ALL entries in IBKPF

WHERE BELNR = IBKPF-BELNR

AND HKONT in gl_acct.

1 ACCEPTED SOLUTION
Read only

Go to solution
Former Member

‎2006 Oct 20 5:26 PM

0 Likes
1,233

Hi,

In the IBKPF ..if you have BUKRS GJAHR use them in your where clause..

<b>IF NOT IBKPF[] IS INITIAL.</b>

SELECT BELNR

BUZEI

BSCHL

SHKZG

PRCTR

KOSTL

HKONT

DMBTR

WRBTR

SGTXT

FROM BSEG

INTO TABLE IBSEG

FOR ALL entries in IBKPF

<b>WHERE BUKRS = IBKPF-BUKRS

AND BELNR = IBKPF-BELNR

AND GJAHR = IBKPF-GJAHR</b>

AND HKONT in gl_acct.

<b>ENDIF.</b>

If it is not there in the internal table IBKPF..Add them...

Also check the internal table IBKPF is not initial before using it for BSEG.

Thanks,

Naren

Hi,

In the IBKPF ..if you have BUKRS GJAHR use them in your where clause..

<b>IF NOT IBKPF[] IS INITIAL.</b>

SELECT BELNR

BUZEI

BSCHL

SHKZG

PRCTR

KOSTL

HKONT

DMBTR

WRBTR

SGTXT

FROM BSEG

INTO TABLE IBSEG

FOR ALL entries in IBKPF

<b>WHERE BUKRS = IBKPF-BUKRS

AND BELNR = IBKPF-BELNR

AND GJAHR = IBKPF-GJAHR</b>

AND HKONT in gl_acct.

<b>ENDIF.</b>

If it is not there in the internal table IBKPF..Add them...

Also check the internal table IBKPF is not initial before using it for BSEG.

Thanks,

Naren

Reply
4 REPLIES 4
Read only

Go to solution
RichHeilman
Developer Advocate
Developer Advocate

‎2006 Oct 20 5:24 PM

0 Likes
1,233

Add BUKRS to the where clause, this should help. If you don't current have BUKRS in the ibkpf internal table, you should add it.


<b>sort ibkpf ascending bukrs belnr.</b>

SELECT BELNR BUZEI BSCHL SHKZG
       PRCTR KOSTL HKONT DMBTR
       WRBTR SGTXT 
         FROM BSEG
             INTO TABLE IBSEG
                 FOR ALL entries in IBKPF
                     WHERE<b> bukrs = ibkpf-bukrs</b>
                       and BELNR = IBKPF-BELNR
                       AND HKONT in gl_acct.

Regards,

Rich Heilman

Read only

Go to solution
Former Member

‎2006 Oct 20 5:26 PM

0 Likes
1,234

Hi,

In the IBKPF ..if you have BUKRS GJAHR use them in your where clause..

<b>IF NOT IBKPF[] IS INITIAL.</b>

SELECT BELNR

BUZEI

BSCHL

SHKZG

PRCTR

KOSTL

HKONT

DMBTR

WRBTR

SGTXT

FROM BSEG

INTO TABLE IBSEG

FOR ALL entries in IBKPF

<b>WHERE BUKRS = IBKPF-BUKRS

AND BELNR = IBKPF-BELNR

AND GJAHR = IBKPF-GJAHR</b>

AND HKONT in gl_acct.

<b>ENDIF.</b>

If it is not there in the internal table IBKPF..Add them...

Also check the internal table IBKPF is not initial before using it for BSEG.

Thanks,

Naren

Read only

Go to solution
Former Member

‎2006 Oct 23 2:09 PM

0 Likes
1,233

Hi,

Normally accessing BSEG always performance problem. I will prefer BSIS and BSAD insted of BSEG. I have seen all fields in your select query is avial in BSIS.

please check out and let us know.

.....Poorna......

Read only

Go to solution
Former Member

‎2006 Oct 23 9:04 PM

0 Likes
1,233

Here is a program that I wrote for extracting

data from BSEG. There was a part which had

some "summarized extract", which I have not posted.

***************************


REPORT   zbseg_extract
         LINE-SIZE 100
         LINE-COUNT 65
         NO STANDARD PAGE HEADING.

TYPE-POOLS : sscr.                      

TYPES : BEGIN OF ty_bkpf,
         bukrs      TYPE bkpf-bukrs,         "COMPANY CODE(4)
         belnr      TYPE bkpf-belnr,         "DOCUMENT NUMBER(10)
         gjahr      TYPE bkpf-gjahr,         "FISCAL YEAR(4)
         blart      TYPE bkpf-blart,         "DOC TYPE(2)
         bldat      TYPE bkpf-bldat,         "DOC DATE(10)
         budat      TYPE bkpf-budat,         "POSTING DATE(10)
         monat      TYPE bkpf-monat,                        "PERIOD(2)
         usnam      TYPE bkpf-usnam,         "USER NAME(12)
         tcode      TYPE bkpf-tcode,                        "TCODE(20)
         xblnr      TYPE bkpf-xblnr,         "REF DOC #(16)
         stjah      TYPE bkpf-stjah,         "REVERSE DOC FICAL YR(4)
         bktxt      TYPE bkpf-bktxt,         "DOC HEADER TEXT(25)
         waers      TYPE bkpf-waers,         "CURRENCY KEY(5)
         kursf      TYPE bkpf-kursf,         "EXCHG RATE(12)
         bstat      TYPE bkpf-bstat,         "DOC STATUS(1)
        END OF ty_bkpf.

TYPES : BEGIN OF ty_bseg,
         bukrs      TYPE bkpf-bukrs,         "COMPANY CODE
         belnr      TYPE bkpf-belnr,         "DOCUMENT NUMBER
         gjahr      TYPE bkpf-gjahr,         "FISCAL YEAR
         buzei      TYPE bseg-buzei,         "FI LINE ITEM(3)
         augdt      TYPE bseg-augdt,         "CLEARING DATE(10)
         augcp      TYPE bseg-augcp,         "CLEARING ENTRY DATE(10)
         augbl      TYPE bseg-augbl,         "DOC # OF CLEARING DOC(10)
         bschl      TYPE bseg-bschl,         "POSTING KEY(2)
         koart      TYPE bseg-koart,         "ACCOUNT TYPE(1)
         shkzg      TYPE bseg-shkzg,         "DR/CR INDIC(1)
         dmbtr      TYPE bseg-dmbtr,         "AMT IN LOCAL CURRENCY(16)
*        sgtxt      TYPE bseg-sgtxt,         "ITEM TEXT(50)
         bewar      TYPE bseg-bewar,         "TRANSACTION TYPE(3)
         kostl      TYPE bseg-kostl,         "COST CENTER(10)
         saknr      TYPE bseg-saknr,         "G/L ACCOUNT NUMBER(10)
         hkont      TYPE bseg-hkont,         "GENERAL LEDGR ACCT(10)
        END OF ty_bseg.

* Itab of file names
TYPES: BEGIN OF ty_file,
        file LIKE rlgrap-filename,
       END OF ty_file.

* Result of system command
TYPES: BEGIN OF ty_result,
        line(132) TYPE c,
       END OF ty_result.

TYPES: BEGIN OF ty_sum_bkpf,
         bukrs TYPE bkpf-bukrs, "Company Code
         belnr TYPE bkpf-belnr, "DOCUMENT NUMBER
         gjahr TYPE bkpf-gjahr, "Fiscal year
         blart TYPE bkpf-blart, "Document type
         monat TYPE bkpf-monat, "Period
       END OF ty_sum_bkpf,

       BEGIN OF ty_sum_bseg,
         bukrs TYPE bseg-bukrs, "COMPANY CODE
         belnr TYPE bseg-belnr, "DOCUMENT NUMBER
         gjahr TYPE bseg-gjahr, "FISCAL YEAR
         hkont TYPE bseg-hkont, "GENERAL LEDGR ACCT
         dmbtr TYPE bseg-dmbtr, "AMT IN LOCAL CURRENCY
       END OF ty_sum_bseg.

DATA: t_bkpf TYPE TABLE OF ty_bkpf,
      t_bseg TYPE TABLE OF ty_bseg,
      t_file TYPE TABLE OF ty_file,
      t_result TYPE TABLE OF ty_result,
      t_sum_bkpf TYPE TABLE OF ty_sum_bkpf,
      t_sum_bseg TYPE TABLE OF ty_sum_bseg.


DATA   wa_file LIKE LINE OF t_file.
DATA   wa_result LIKE LINE OF t_result.
DATA   w_cmd(132) TYPE c.  "unix command
DATA   gv_current_file LIKE rlgrap-filename.

FIELD-SYMBOLS: <bkpf> TYPE ty_bkpf,
               <bseg> TYPE ty_bseg,
               <bkpf_s> TYPE ty_sum_bkpf,
               <bseg_s> TYPE ty_sum_bseg.


DATA: gc_bkpf       TYPE cursor,
      wa_out(1000)  TYPE c,
      lv_belnr      LIKE bseg-belnr,
      lv_kursf(16)  TYPE c,
      lv_dmbtr(20)  TYPE c,
      w_monat       LIKE bkpf-monat,
      lv_dcpfm      LIKE usr01-dcpfm,  "Decimal point format
      w_bkpf_cnt(9) TYPE n,            "# of records from BKPF
      w_bseg_cnt(9) TYPE n,            "# of records from BSEG
      w_max         TYPE i VALUE 1000,
      gv_error(1)   TYPE c,
      w_current_size(9) TYPE n,
      gv_file_ct(8)    TYPE n VALUE '1',  "initial value of file suffix
      wa_bkpf       TYPE bkpf,                 
      lv_flag.                                 

DATA : ls_restriction  TYPE sscr_restrict,
       ls_opt_list     TYPE sscr_opt_list,
       ls_ass          TYPE sscr_ass.

CONSTANTS c_pipe(1)  TYPE c VALUE '|'.
CONSTANTS: c_x(1)    TYPE c VALUE 'X',
           c_option(2)      VALUE 'EQ',
           c_sign(1)        VALUE 'I',
           c_yes(1)         VALUE '1',
           c_no(1)          VALUE '0',
           c_txt(4)  TYPE c VALUE '.TXT',
           c_gz(3)   TYPE c VALUE '.gz',
           c_selgrp(3)      VALUE 'SEL',
           c_pargrp(3)      VALUE 'PAR',
           c_cmd1(15) TYPE c VALUE 'gzip -v -S .gz',
           c_cmd2(8)  TYPE c VALUE '2>&1',
           c_noint(10)      VALUE 'NOINTERVLS',
           c_s(1)           VALUE 'S',
           c_name(7)        VALUE 'S_BLART',
           c_modgrp(3)      VALUE 'MOD'.

SELECTION-SCREEN BEGIN OF BLOCK blk WITH FRAME TITLE text-t03.
  PARAMETERS p_detail RADIOBUTTON GROUP rad USER-COMMAND rem.
  PARAMETERS p_sum    RADIOBUTTON GROUP rad.
SELECTION-SCREEN END   OF BLOCK blk.

SELECTION-SCREEN BEGIN OF BLOCK one WITH FRAME TITLE text-t01.

PARAMETERS p_bukrs LIKE bkpf-bukrs OBLIGATORY default 'US15'.
*  SELECT-OPTIONS
*             s_belnr FOR  lv_belnr.
PARAMETERS p_gjahr LIKE bkpf-gjahr OBLIGATORY default sy-datum(4).
PARAMETERS p_monat LIKE bkpf-monat OBLIGATORY
                   MODIF ID par default sy-datum+4(2).
SELECT-OPTIONS
           s_monat FOR w_monat
                   MODIF ID sel.
SELECT-OPTIONS                           
           s_blart FOR wa_bkpf-blart     
           NO INTERVALS MODIF ID mod.   
SELECTION-SCREEN SKIP.
PARAMETERS p_file  LIKE rlgrap-filename OBLIGATORY
                   DEFAULT text-001.
SELECTION-SCREEN END OF BLOCK one.

SELECTION-SCREEN SKIP.
SELECTION-SCREEN BEGIN OF BLOCK two WITH FRAME TITLE text-t02.
PARAMETERS p_size  TYPE i OBLIGATORY
                   DEFAULT w_max.
PARAMETERS p_count TYPE i OBLIGATORY   "Count on the number of
                   DEFAULT '20000'.    "BSEG records per file
PARAMETERS p_zip   TYPE c  AS CHECKBOX DEFAULT c_x.
SELECTION-SCREEN END OF BLOCK two.

INITIALIZATION.
  CLEAR w_bkpf_cnt.
  CLEAR w_bseg_cnt.
  CLEAR p_monat.
  REFRESH s_monat.
  REFRESH t_file.

* Restrict the select-options S_BLART
  CLEAR ls_opt_list.
  ls_opt_list-name = c_noint.
  ls_opt_list-options-eq = c_x.
  APPEND ls_opt_list TO ls_restriction-opt_list_tab.

  CLEAR ls_ass.
  ls_ass-kind    = c_s.
  ls_ass-name    = c_name.
  ls_ass-sg_main = c_sign.
  ls_ass-sg_addy = space.
  ls_ass-op_main = c_noint.
  ls_ass-op_addy = c_noint.
  APPEND ls_ass    TO ls_restriction-ass_tab.

  CLEAR ls_opt_list.
  CLEAR ls_ass.

  CALL FUNCTION 'SELECT_OPTIONS_RESTRICT'
    EXPORTING
      restriction            = ls_restriction
    EXCEPTIONS
      too_late               = 1
      repeated               = 2
      selopt_without_options = 3
      selopt_without_signs   = 4
      invalid_sign           = 5
      empty_option_list      = 6
      invalid_kind           = 7
      repeated_kind_a        = 8
      OTHERS                 = 9.

AT SELECTION-SCREEN.
  IF p_detail EQ c_x.
    if s_blart[] is initial.
      MESSAGE e257(F0) WITH text-013 text-014.
    endif.
  ELSEIF p_sum EQ c_x.
  ENDIF.

START-OF-SELECTION.
  REFRESH t_bkpf.
  REFRESH t_sum_bkpf.            

  CLEAR   lv_dcpfm.

* First file name
  CONCATENATE p_file gv_file_ct c_txt INTO gv_current_file.

  OPEN DATASET gv_current_file IN TEXT MODE FOR OUTPUT ENCODING DEFAULT.
  IF sy-subrc NE 0.
    gv_error = c_x.
    FORMAT COLOR COL_NEGATIVE.
    WRITE : text-002, gv_current_file.
    STOP.
  ELSE.
    CLEAR wa_out.
    CONCATENATE text-010 gv_current_file
                INTO wa_out SEPARATED BY space.
    TRANSFER wa_out TO gv_current_file LENGTH 1000.

    CLEAR wa_out.
    IF p_detail EQ c_x.
      CONCATENATE text-h01 text-h02 text-h03
             INTO wa_out SEPARATED BY c_pipe.
    else.
      MOVE text-s01 TO wa_out.
    endif.
    TRANSFER wa_out TO gv_current_file LENGTH 1000.

  ENDIF.

  IF sy-sysid = c_yrp.
    REFRESH s_monat.
    s_monat-sign = c_sign.
    s_monat-option = c_option.
    s_monat-low = p_monat.
    APPEND s_monat.
    CLEAR: p_monat, s_monat.
  ENDIF.

  IF p_detail EQ c_x.
    perform do_detailed_extract.
  ELSEIF p_sum EQ c_x.
    perform do_summarized_extract.
  endif.

  CLOSE DATASET gv_current_file.

  IF NOT p_zip IS INITIAL.
* zip the file just created.
    CLEAR w_cmd.
    REFRESH t_result.
    CONCATENATE c_cmd1 gv_current_file c_cmd2
           INTO w_cmd SEPARATED BY space.
    CALL 'SYSTEM' ID 'COMMAND' FIELD w_cmd
    ID 'TAB' FIELD t_result[].
    WRITE : /1 w_cmd COLOR COL_POSITIVE INTENSIFIED OFF.
    LOOP AT t_result INTO wa_result.
      WRITE /5 wa_result
         COLOR COL_POSITIVE INTENSIFIED ON.
    ENDLOOP.
    CONCATENATE gv_current_file c_gz INTO gv_current_file.
  ENDIF.
* Retain last file name
  CLEAR wa_file.
  MOVE gv_current_file TO wa_file.
  APPEND wa_file TO t_file.
  CLEAR wa_file.


END-OF-SELECTION.
  IF gv_error IS INITIAL.
*   Write out the selection screen parameters
    WRITE:/ text-004 COLOR COL_HEADING,
            p_bukrs  COLOR COL_TOTAL INTENSIFIED OFF,
          / text-005 COLOR COL_HEADING,
            p_gjahr  COLOR COL_TOTAL INTENSIFIED OFF,
          / text-011 COLOR COL_HEADING,
            s_monat-low COLOR COL_TOTAL INTENSIFIED OFF,
          / text-006 COLOR COL_HEADING.
*   Display all files generated
    CLEAR wa_file.
    LOOP AT t_file INTO wa_file.
      IF sy-tabix = 1.
        WRITE: 15 wa_file COLOR COL_TOTAL INTENSIFIED OFF.
      ELSE.
        WRITE: /15 wa_file COLOR COL_TOTAL INTENSIFIED OFF.
      ENDIF.
      CLEAR wa_file.
    ENDLOOP.

*   # of records processed from BKPF, BSEG
    SKIP 2.
    WRITE: / text-007,
                w_bkpf_cnt COLOR COL_POSITIVE INTENSIFIED OFF,
           / text-008,
                w_bseg_cnt COLOR COL_POSITIVE INTENSIFIED OFF.
  ELSE.
    WRITE text-009 COLOR COL_NEGATIVE.
  ENDIF.

*&---------------------------------------------------------------------*
*&      Form  do_detailed_extract
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM do_detailed_extract .
*---------------------------------------------------------*
*                Detailed Extract                         *
*---------------------------------------------------------*
  OPEN CURSOR gc_bkpf FOR
       SELECT bukrs
              belnr
              gjahr
              blart
              bldat
              budat
              monat
              usnam
              tcode
              xblnr
              stjah
              bktxt
              waers
              kursf
              bstat
         FROM bkpf
        WHERE bukrs EQ p_bukrs
*       AND belnr IN s_belnr
          AND gjahr EQ p_gjahr
          AND blart IN s_blart             
          AND monat IN s_monat.
  CLEAR w_max.
  w_max  = p_size.

  CLEAR w_current_size.

  DO.
    FETCH NEXT   CURSOR gc_bkpf
          INTO   TABLE  t_bkpf
         PACKAGE SIZE   w_max.

    IF sy-subrc NE 0.
      CLOSE CURSOR gc_bkpf.
      EXIT.
    ELSEIF sy-subrc EQ 0.
      REFRESH t_bseg.
      IF NOT t_bkpf[] IS INITIAL.
        SELECT bukrs
               belnr
               gjahr
               buzei
               augdt
               augcp
               augbl
               bschl
               koart
               shkzg
               dmbtr
*              sgtxt
               bewar
               kostl
               saknr
               hkont
          FROM bseg
          INTO TABLE t_bseg
           FOR ALL ENTRIES IN t_bkpf
         WHERE bukrs EQ t_bkpf-bukrs
           AND belnr EQ t_bkpf-belnr
           AND gjahr EQ t_bkpf-gjahr.
        IF sy-subrc EQ 0.
*       PREPARE THE EXTRACT
          LOOP AT t_bkpf ASSIGNING <bkpf>.
            w_bkpf_cnt = w_bkpf_cnt + 1.
            LOOP AT t_bseg ASSIGNING <bseg>
                          WHERE bukrs EQ <bkpf>-bukrs
                            AND belnr EQ <bkpf>-belnr
                            AND gjahr EQ <bkpf>-gjahr.
              CLEAR wa_out.
              WRITE <bkpf>-kursf TO lv_kursf.
              WRITE <bseg>-dmbtr TO lv_dmbtr.
              CONCATENATE <bkpf>-bukrs
                          <bkpf>-belnr
                          <bkpf>-gjahr
                          <bseg>-buzei
                          <bkpf>-blart
                          <bkpf>-bldat
                          <bkpf>-budat
                          <bkpf>-monat
                          <bkpf>-usnam
                          <bkpf>-tcode
                          <bkpf>-xblnr
                          <bkpf>-stjah
                          <bkpf>-bktxt
                          <bkpf>-waers
                          lv_kursf
                          <bkpf>-bstat
                          <bseg>-augdt
                          <bseg>-augcp
                          <bseg>-augbl
                          <bseg>-bschl
                          <bseg>-koart
                          <bseg>-shkzg
                          lv_dmbtr
*                         <bseg>-sgtxt
                          <bseg>-bewar
                          <bseg>-kostl
                          <bseg>-saknr
                          <bseg>-hkont
                     INTO wa_out SEPARATED BY c_pipe.
              REPLACE ',' IN wa_out WITH ''.
              TRANSFER wa_out TO gv_current_file LENGTH 1000.
              w_bseg_cnt = w_bseg_cnt + 1.
*           Logic to split files as per Count on sel. screen
              w_current_size = w_current_size + 1.
              IF w_current_size EQ p_count.
                CLEAR w_current_size.
                CLEAR wa_out.
*             Denote end of current file
                CONCATENATE text-003 gv_file_ct INTO wa_out.
                TRANSFER wa_out TO gv_current_file.
                CLOSE DATASET gv_current_file.

                IF NOT p_zip IS INITIAL.
*             zip the file just closed.
                  CLEAR w_cmd.
                  REFRESH t_result.
                  CONCATENATE c_cmd1 gv_current_file c_cmd2
                         INTO w_cmd SEPARATED BY space.
                  CALL 'SYSTEM' ID 'COMMAND' FIELD w_cmd
                  ID 'TAB' FIELD t_result[].
                  WRITE /1 w_cmd  COLOR COL_POSITIVE INTENSIFIED OFF.
                  LOOP AT t_result INTO wa_result.
                    WRITE /5 wa_result
                       COLOR COL_POSITIVE INTENSIFIED ON.
                  ENDLOOP.
                CONCATENATE gv_current_file c_gz INTO gv_current_file.
                ENDIF.
*             Retain name of current file
                CLEAR wa_file.
                MOVE gv_current_file TO wa_file.
                APPEND wa_file TO t_file.
                CLEAR wa_file.

                CLEAR: wa_out, gv_current_file.
               gv_file_ct = gv_file_ct + 1.    "increment file counter
                CONDENSE gv_file_ct.
                CONCATENATE p_file gv_file_ct c_txt
                       INTO gv_current_file.
*             Open next file
                OPEN DATASET gv_current_file IN TEXT MODE
                                    FOR OUTPUT ENCODING DEFAULT.
                IF sy-subrc NE 0.
                  gv_error = c_x.
                  FORMAT COLOR COL_NEGATIVE.
                  WRITE : text-002, gv_current_file.
                  STOP.
                ELSE.
                  CLEAR wa_out.
                  CONCATENATE text-010 gv_current_file
                              INTO wa_out SEPARATED BY space.
                  TRANSFER wa_out TO gv_current_file LENGTH 1000.
                  CLEAR wa_out.
                  CONCATENATE text-h01 text-h02 text-h03
                              INTO wa_out SEPARATED BY c_pipe.
                  TRANSFER wa_out TO gv_current_file LENGTH 1000.
                ENDIF.
*             End of file split logic
              ENDIF.
            ENDLOOP.
            DELETE t_bseg WHERE bukrs EQ <bkpf>-bukrs
                            AND belnr EQ <bkpf>-belnr
                            AND gjahr EQ <bkpf>-gjahr.
          ENDLOOP.
          CLEAR wa_out.
          CONCATENATE text-003 gv_file_ct INTO wa_out.
          TRANSFER wa_out TO gv_current_file.
        ENDIF. "END OF SY-SUBRC CHECK
      ENDIF.
      REFRESH: t_bkpf,  t_bseg.
    ENDIF. " ELSEIF SY-SUBRC EQ 0.
  ENDDO.   " DO

ENDFORM.                    " do_detailed_extract