Pushing data in packets from Function Module to BW

[Former Member]

Hi All,

I have been struggling with a problem for about a week and a half and I find myself at an in-pass. I firstly created a function module to pull data from SAP and join multiple tables so I could use it to report in Crystal. The problem was that it was so much data that I received errors due to processing time.

Thus to solve this problem I now use the function module to push data into a datasource so that it executes in the background.

The main problem I am experiencing is how to code a function module so that it will retrieve the data in segments then commit then retrieve next segment. At the moment the program works but if the data increases like it should I will run into the no more space in the internal table error.

Example of my code below. (much simplified to just demonstrate functionality). Sorry that it is still long but didn't want to leave out parts that might be important.

So how would one get the data to retrieve from the SAP tables and commit to BW in segments instead of like I do to retrieve all the data then pump into BW in segments.

FUNCTION ZBOBJ_COMMISSION.
*"  IMPORTING
*"     VALUE(I_REQUNR) TYPE  SRSC_S_IF_SIMPLE-REQUNR
*"     VALUE(I_DSOURCE) TYPE  SRSC_S_IF_SIMPLE-DSOURCE OPTIONAL
*"     VALUE(I_MAXSIZE) TYPE  SRSC_S_IF_SIMPLE-MAXSIZE OPTIONAL
*"     VALUE(I_INITFLAG) TYPE  SRSC_S_IF_SIMPLE-INITFLAG OPTIONAL
*"     VALUE(I_READ_ONLY) TYPE  SRSC_S_IF_SIMPLE-READONLY OPTIONAL
*"     VALUE(I_REMOTE_CALL) TYPE  SBIWA_FLAG DEFAULT SBIWA_C_FLAG_OFF
*"  TABLES
*"      I_T_SELECT TYPE  SRSC_S_IF_SIMPLE-T_SELECT OPTIONAL
*"      I_T_FIELDS TYPE  SRSC_S_IF_SIMPLE-T_FIELDS OPTIONAL
*"      E_T_DATA STRUCTURE  ZDGB_COMMISION_REPORT OPTIONAL
*"  EXCEPTIONS
*"      NO_MORE_DATA
*"      ERROR_PASSED_TO_MESS_HANDLER

  STATICS : counter type i.

** Example: DataSource for table SFLIGHT
  TABLES: ZDGB_COMMISION_REPORT.
** Auxiliary Selection criteria structure
  DATA: L_S_SELECT TYPE SRSC_S_SELECT.
** Maximum number of lines for DB table
  STATICS: S_S_IF TYPE SRSC_S_IF_SIMPLE,
** counter
          S_COUNTER_DATAPAKID LIKE SY-TABIX,
** cursor
          S_CURSOR TYPE CURSOR.
** Initialization mode (first call by SAPI) or data transfer mode
** (following calls) ?
  IF I_INITFLAG = SBIWA_C_FLAG_ON.
*
** Check DataSource validity
    CASE I_DSOURCE.
      WHEN 'ZBOBJ_COMMISSION'.
      WHEN OTHERS.
        IF 1 = 2. MESSAGE E009(R3). ENDIF.
** this is a typical log call. Please write every error message like this
        LOG_WRITE 'E'                  "message type
                  'R3'                 "message class
                  '009'                "message number
                  I_DSOURCE   "message variable 1
                  ' '.                 "message variable 2
        RAISE ERROR_PASSED_TO_MESS_HANDLER.
    ENDCASE.

    APPEND LINES OF I_T_SELECT TO S_S_IF-T_SELECT.

* Fill parameter buffer for data extraction calls
    S_S_IF-REQUNR    = I_REQUNR.
    S_S_IF-DSOURCE = I_DSOURCE.
    S_S_IF-MAXSIZE   = I_MAXSIZE.

    APPEND LINES OF I_T_FIELDS TO S_S_IF-T_FIELDS.

  ELSE.                 "Initialization mode or data extraction ?
*
    STATICS: st_itab TYPE ZDGB_COMMISION_REPORT OCCURS 0 WITH HEADER LINE.

    IF S_COUNTER_DATAPAKID = 0.


ORIGINAL - DIRECT CUSTOMER**********************************************************
      Select
             VBPA~KUNNR
            VBRK~VBTYP
            INTO CORRESPONDING FIELDS OF TABLE itab1
              FROM
                VBRK
                INNER JOIN VBRP ON VBRK~VBELN = VBRP~VBELN
                INNER JOIN MARA ON VBRP~MATNR = MARA~MATNR
                INNER JOIN ZCUWE ON ZCUWE~BOT_MAN = MARA~ZZBOTTMANU
                INNER JOIN VBPA ON VBPA~VBELN = VBRP~AUBEL

              WHERE              VBPA~PARVW in s_parvw.

      SORT ITAB1 BY KUNNR.      
      LOOP AT itab2.
        st_itab-BUKRS = itab2-BUKRS.
      ENDLOOP.
    endif.

    INSERT LINES OF st_itab TO S_S_IF-MAXSIZE INTO TABLE e_t_data.
    DELETE st_itab TO S_S_IF-MAXSIZE.



      IF lines( st_itab ) IS INITIAL.
        counter = counter + 1.
        if counter = 2.
          RAISE NO_MORE_DATA.
        endif.
      ENDIF.
          S_COUNTER_DATAPAKID = S_COUNTER_DATAPAKID + 1.


  ENDIF.        
ENDFUNCTION.

[roberto_vacca2]

Please could you tell us witch table goes oversize and in witch append instruction?

Thanks..

[Former Member]

Hi,

A lot of the code was taken out cause it is 12 pages. The table that will go to much is itab1 that pulls all the sales data from the tables for the rep types. Thus the data will increase exponentially over time and the internal table will fall over sooner or later. Thus i need a way to make this retrieve data in segments.... and write it to the internal table then run the rest of the code send it to BW and repeat until no more data left for selection.

[RaymondGiuseppi]

My delta-datasource FM usually use an OPEN CURSOR statement

    IF s_counter_datapakid = 0.
(...)
      OPEN CURSOR WITH HOLD s_cursor FOR
      SELECT (t_cursor)
        FROM qmfe
        WHERE fenum IN l_r_qmnum
          AND ( aedat IN l_r_aedat
             OR erdat IN l_r_aedat ).
(...)
    ENDIF.                            

* Fetch records into interface table.
*   named E_T_'Name of extract structure'.
    FETCH NEXT CURSOR s_cursor
               INTO CORRESPONDING FIELDS
               OF TABLE e_t_data
               PACKAGE SIZE s_s_if-maxsize.
(...)
* No data
    IF sy-subrc NE 0.
      CLOSE CURSOR s_cursor.
(...)

Regards,

Raymond

[Former Member]

Hi Raymond Giuseppi,

Thanks for the cursor code. I have seen this type of code before in some of the SAP BW funtion modules. Will give it a try and come back to you guys.

Thanks for all the help.

In connection with the table selecting on non-key value it is currently working. This select works but I can see the benefit of the way you are suggesting. Will when the program is working look into optimizing it with suggestions like yours. The select statement is currently pulling 450 000 records and using about (100mb) of space. The problem would become only apparent when the records increase in 1-2 years time. That is why i need to try some combinaiton of package size and cursor i think.

Thanks again, will get back to you on this one

[roberto_vacca2]

I'm not sure, but I think there's something wrong with your select instruction

Select

VBPA~KUNNR

VBRK~VBTYP

INTO CORRESPONDING FIELDS OF TABLE itab1

FROM

VBRK

INNER JOIN VBRP ON VBRK_{VBELN = VBRP}VBELN

INNER JOIN MARA ON VBRP_{MATNR = MARA}MATNR

INNER JOIN ZCUWE ON ZCUWE_{BOT_MAN = MARA}ZZBOTTMANU

INNER JOIN VBPA ON VBPA_{VBELN = VBRP}AUBEL

WHERE VBPA~PARVW in s_parvw.

You're making a join within vbrk vbrp mara zcuwe and vbpa using a non-key field such vbpa-parvw. Are you sure we're talking about an over-size problem?

You should separate INNER JOIN, using FOR ALL ENTRIES instruction, with range datas.