topic BSEG extract in Application Development and Automation Discussions

BSEG extract

Former Member — Fri, 20 Oct 2006 16:20:18 GMT

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.

Re: BSEG extract

RichHeilman — Fri, 20 Oct 2006 16:24:27 GMT

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

Re: BSEG extract

Former Member — Fri, 20 Oct 2006 16:26:38 GMT

Hi,

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

IF NOT IBKPF[] IS INITIAL.

SELECT BELNR

BUZEI

BSCHL

SHKZG

PRCTR

KOSTL

HKONT

DMBTR

WRBTR

SGTXT

FROM BSEG

INTO TABLE IBSEG

FOR ALL entries in IBKPF

WHERE BUKRS = IBKPF-BUKRS

AND BELNR = IBKPF-BELNR

AND GJAHR = IBKPF-GJAHR

AND HKONT in gl_acct.

ENDIF.

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

Re: BSEG extract

Former Member — Mon, 23 Oct 2006 13:09:23 GMT

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......

Re: BSEG extract

Former Member — Mon, 23 Oct 2006 20:04:17 GMT

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