KSCHL for Purchasing DataSource

[guillermo_m]

Hi Gurus!

We are on BI 7 and we activated the BC for the dataource 2LIS_02_HDR, 2LIS_02_ITM and 2LIS_02_SCL.

Now the user is requesting me the field KSCHL in BW. I checked the extractors in LBWE in R/3 and this field doesn't appear in the extractors.

How can I get this field? Should I create a new generic datasource via FM? What are the fields to use in the FM to get this field?

Or... Should I enhance one of the 2LIS extractors to catch the KSCHL field?

Thanks in advance.

Regards.

[dennis_scoville4 · Accepted solution]

OUCH!!! A purchase order line item can have multiple condition records, of which KSCHL (condition type) is a part of. The R3/ECC tables for this are EKBZ and EKBZH and there are no standard content for these tables.

So, if your end users require this data, you're going to have to create a generic DataSource for it because trying to add this data to one of the standard content DataSources will cause issues (duplicate data for all fields except conditions). The fields that you should have in this DataSource are (key fields for both tables are in bold😞

Purchasing Document Number

Item Number of Purchasing Document

Step number

Condition counter

Transaction/event type, purchase order history

Fiscal Year

Accounting Document Number

Item in Material Document

Condition type

As for a field to use for generic delta, that's a good one because there really isn't a proper date to use on these tables.

Edited by: Dennis Scoville on Jul 15, 2009 1:19 PM

[guillermo_m · Accepted solution]

Thanks for this great reply Dennis. It's a great help for me.

Do you have an example of the FM to use in the generic extractor?

Thanks again.

Regard

[dennis_scoville4 · Accepted solution]

I have pasted a FM into this message that has code for generic delta based on two different fields, but it's too big to display as code (so everything is run togther). I hope it helps.

Here are a couple of documents you can look at as well for creating generic DataSources and generic deltas:

[How To...Create Generic Delta|https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/84bf4d68-0601-0010-13b5-b062adbb3e33?overridelayout=true]

[How To...Create Generic DataSource Which Uses Delta Queue|https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/d3219af2-0c01-0010-71ac-dbb4356cf4bf?overridelayout=true]

FUNCTION y_docu_flow.
*"----------------------------------------------------------------------
*"*"Local interface:
*"  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  YSDS_DOCU_FLOW OPTIONAL
*"  EXCEPTIONS
*"      NO_MORE_DATA
*"      ERROR_PASSED_TO_MESS_HANDLER
*"----------------------------------------------------------------------
*&----------------------------------------------------------------------
*& Global Data Declarations
*&----------------------------------------------------------------------

* Type for VBFA Data
  TYPES : BEGIN OF ty_vbfa            ,
            mandt   TYPE vbfa-mandt   ,
            vbelv   TYPE vbfa-vbelv   ,
            posnv   TYPE vbfa-posnv   ,
            vbeln   TYPE vbfa-vbeln   ,
            posnn   TYPE vbfa-posnn   ,
            vbtyp_n TYPE vbfa-vbtyp_n ,
            rfmng   TYPE vbfa-rfmng   ,
            meins   TYPE vbfa-meins   ,
            rfwrt   TYPE vbfa-rfwrt   ,
            waers   TYPE vbfa-waers   ,
            vbtyp_v TYPE vbfa-vbtyp_v ,
            plmin   TYPE vbfa-plmin   ,
            erdat   TYPE vbfa-erdat   ,
            aedat   TYPE vbfa-aedat   ,
          END   OF ty_vbfa            .

* Internal Tables and Selection Criteria Structure
  DATA : t_vbfa     TYPE STANDARD TABLE OF ty_vbfa ,
         l_s_select TYPE srsc_s_select             .

* Field Symbols
  FIELD-SYMBOLS : <fs_fields> TYPE rsfieldsel.

* Constants
  CONSTANTS : c_error_msgty TYPE rslogparms-msgty VALUE 'E'            ,
              c_error_msgid TYPE rslogparms-msgid VALUE 'YAERO_WTY_VSR',
              c_error_msgv1 TYPE rslogparms-msgv1 VALUE ' MESSAGE_V1 ' ,
              c_error_msgv2 TYPE rslogparms-msgv1 VALUE ' MESSAGE_V2 ' ,
              c_error_msgv3 TYPE rslogparms-msgv1 VALUE ' MESSAGE_V3 ' ,
              c_error_msgv4 TYPE rslogparms-msgv1 VALUE ' MESSAGE_V4 ' ,
              c_error_msgno TYPE rslogparms-msgno VALUE 999            ,
              c_alias(2)    TYPE c                VALUE 'A~'           ,
              c_vbfa        TYPE dntab-tabname    VALUE 'VBFA'         .

* Maximum Database Lines, Data Package Counter and Cursor
  STATICS : s_s_if              TYPE srsc_s_if_simple ,
            s_counter_datapakid TYPE sy-tabix         ,
            s_cursor            TYPE cursor           .

* Select Ranges
  RANGES : r_erdat FOR vbfa-erdat ,
           r_aedat FOR vbfa-aedat ,
           r_vbelv FOR vbfa-vbelv ,
           r_posnv FOR vbfa-posnv ,
           r_vbeln FOR vbfa-vbeln ,
           r_posnn FOR vbfa-posnn .

*&----------------------------------------------------------------------
*& Processing Logic
*&----------------------------------------------------------------------

* Refresh Internal Table and Clear work area
  REFRESH: t_vbfa   ,
           e_t_data .

* Check the Extraction Mode (Delta Initialization Or Delta)
  IF i_initflag EQ sbiwa_c_flag_on.

*   Determine DataSource Validity
    IF i_dsource NE 'YSDDS_DOCU_FLOW'.

*     Call Function to Write Log
      CALL FUNCTION 'RSAL_LOG_WRITE'
        EXPORTING
          i_msgty = c_error_msgty
          i_msgid = c_error_msgid
          i_msgno = c_error_msgno
          i_msgv1 = c_error_msgv1
          i_msgv2 = c_error_msgv2
          i_msgv3 = c_error_msgv3
          i_msgv4 = c_error_msgv4.

*     Raise Exception
      RAISE error_passed_to_mess_handler.

    ENDIF.                  " IF i_dsource NE 'YSDDS_DOCU_FLOW'.

*   Append the Selection Criteria
    APPEND LINES OF i_t_select TO s_s_if-t_select.

*   Fill parameter buffer for data extraction calls
    MOVE : i_requnr  TO s_s_if-requnr  ,
           i_dsource TO s_s_if-dsource ,
           i_maxsize TO s_s_if-maxsize .

*   Append Fields to be Selected
    APPEND LINES OF i_t_fields TO s_s_if-t_fields.

*   Add the Alias to Fields
    LOOP AT s_s_if-t_fields
      ASSIGNING <fs_fields>.

      CONCATENATE c_alias
                  <fs_fields>-fieldnm
             INTO <fs_fields>-fieldnm.

      CONDENSE <fs_fields>-fieldnm NO-GAPS.

    ENDLOOP.                " LOOP AT s_s_if-t_fields..

  ELSE.                     " IF i_initflag EQ sbiwa_c_flag_on

*   For First Data Package, Open Cursor
    IF s_counter_datapakid EQ 0.

*     Clear Ranges
      CLEAR : r_erdat ,
              r_aedat ,
              r_vbelv ,
              r_posnv ,
              r_vbeln ,
              r_posnn .
      .
*     Fill Selection Ranges
      LOOP AT s_s_if-t_select
        INTO l_s_select.

*       Determine Selection Range Field Name
        CASE l_s_select-fieldnm.

*         Created On Date
          WHEN 'ERDAT'.

            MOVE : l_s_select-sign   TO r_erdat-sign   ,
                   l_s_select-option TO r_erdat-option ,
                   l_s_select-low    TO r_erdat-low    ,
                   l_s_select-high   TO r_erdat-high   .

            APPEND r_erdat.

*           Fill the change date too
            MOVE r_erdat[] TO r_aedat[].

*         Predecessor Document Number
          WHEN 'VBELV'.

            MOVE : l_s_select-sign   TO r_vbelv-sign   ,
                   l_s_select-option TO r_vbelv-option ,
                   l_s_select-low    TO r_vbelv-low    ,
                   l_s_select-high   TO r_vbelv-high   .

            APPEND r_vbelv.

*         Predecessor Document Line Item
          WHEN 'POSNV'.

            MOVE : l_s_select-sign   TO r_posnv-sign   ,
                   l_s_select-option TO r_posnv-option ,
                   l_s_select-low    TO r_posnv-low    ,
                   l_s_select-high   TO r_posnv-high   .

            APPEND r_posnv.

*         Successor Document Number
          WHEN 'VBELN'.

            MOVE : l_s_select-sign   TO r_vbeln-sign   ,
                   l_s_select-option TO r_vbeln-option ,
                   l_s_select-low    TO r_vbeln-low    ,
                   l_s_select-high   TO r_vbeln-high   .

            APPEND r_vbeln.

*         Successor Document Line Item
          WHEN 'POSNN'.

            MOVE : l_s_select-sign   TO r_posnn-sign   ,
                   l_s_select-option TO r_posnn-option ,
                   l_s_select-low    TO r_posnn-low    ,
                   l_s_select-high   TO r_posnn-high   .

            APPEND r_posnn.

        ENDCASE.            " CASE l_s_select-fieldnm.

      ENDLOOP.              " LOOP AT s_s_if-t_select..

*     Open Cursor on View to Fetch data

*     If full load with Selection on Fiscal Year
      IF r_aedat[] IS INITIAL.

*       Full Extraction
        OPEN CURSOR
        WITH HOLD s_cursor FOR
      SELECT (s_s_if-t_fields)
        FROM vbfa      AS a
        JOIN pnwtyv    AS b
          ON a~vbelv   EQ b~sdvbeln
       WHERE a~vbelv   IN r_vbelv
         AND a~posnv   IN r_posnv
         AND a~vbeln   IN r_vbeln
         AND a~posnn   IN r_posnn
         AND a~rfmng   NE 0
         AND a~erdat   IN r_erdat
         AND b~sdvbeln NE space.

      ELSE.

*       Delta Extraction Using ERDAT and AEDAT
        OPEN   CURSOR
        WITH   HOLD s_cursor FOR
      SELECT   (s_s_if-t_fields)
        FROM   vbfa      AS a
        JOIN   pnwtyv    AS b
          ON   a~vbelv   EQ b~sdvbeln
       WHERE   a~vbelv   IN r_vbelv
         AND   a~posnv   IN r_posnv
         AND   a~vbeln   IN r_vbeln
         AND   a~posnn   IN r_posnn
         AND   a~rfmng   NE 0
         AND ( a~erdat   IN r_erdat
          OR   a~aedat   IN r_aedat )
         AND   b~sdvbeln NE space.

      ENDIF.                " IF r_aedat[] IS INITIAL

    ENDIF.                  " IF s_counter_datapakid..

*   Fetch Records into Interface Table
    FETCH NEXT CURSOR s_cursor
         APPENDING CORRESPONDING FIELDS
          OF TABLE t_vbfa
      PACKAGE SIZE s_s_if-maxsize.

*   No More Data, Raise Exception
    IF sy-subrc NE 0.

*     Close the Cursor
      CLOSE CURSOR s_cursor.

*     Raise Exception
      RAISE no_more_data.

    ELSE.

*     Move Extracted Data to Output Structure
      MOVE t_vbfa[] TO e_t_data[].

*     Release Memory
      FREE t_vbfa.

*     Sort Output Structure
      SORT e_t_data BY vbelv
                       posnv
                       vbeln
                       posnn
                       vbtyp_n.

*     Delete Duplicate Records from Output Structure
      DELETE ADJACENT DUPLICATES
        FROM e_t_data
   COMPARING vbelv
             posnv
             vbeln
             posnn
             vbtyp_n.

*     Increment Data Package Number
      s_counter_datapakid = s_counter_datapakid + 1.

    ENDIF.                  " IF sy-subrc NE 0

  ENDIF.                    " IF i_initflag EQ sbiwa_c_flag_on

ENDFUNCTION.               

[guillermo_m · Accepted solution]

Thanks a lot for all your help Dennis.

Greetings!