LIS User Exit MCS10001 and VBPA table (Roberto N. read me!)

[Former Member]

Hello fellow BW'ers

How do you tackle the problem of adding custom fields to SD extractions when the fields do not have a before/after image?

For example:

1) Extend 2LIS_11_VASCL structure with a text field for a partner like "Technical Analyst".

2) In SMOD, work on the user exit for MCS10001.

3) For each extracted row, include the matching text field from the VBPA table for "Technical analyst"

Now you have 2LIS_11_VASCL extended with your custom field.

HOWEVER, this will only be correct on your first order. Any order modification will NOT send a before/after image for this field:

1) Extraction:

Order 1, line 10, technical analyst = "", RecMode = ""

2) Change technical analyst in R/3

3) Extraction:

Order 1, line 10, technical analyst = "1", RecMode = "X"

Order 1, line 10, technical analyst = "1", RecMode = ""

This is incorrect! The reverse posting should be:

Order 1, line 10, technical analyst = "", RecMode = "X"

The problem is when technical analyst is retrieved from VBPA, the code is NOT checking the before/after images on this order.

Specifically, note 576886 addresses this issue:

<a href="https://service.sap.com/~form/handler?_APP=00200682500000002033&_EVENT=DISPLAY&_OBJECT=012006153200001957602002">SAP Note 576886</a>

The note advises us to:

<i>To display a delta, you must make sure that you can determine the status in the user exit before and after the document change.

This varies from case to case and depends on from which table the field is filled.

For example, you may occasionally have to obtain the information from the internal document tables (such as xvbap and yvbap).</i>

Also, the excellent Roberto Negro advises in his Blog:

<a href="/people/sap.user72/blog/2005/02/14/logistic-cockpit--when-you-need-more--first-option-enhance-it COCKPIT - WHEN YOU NEED MORE - First option: enhance it</a>

His text is:

<i>Even this procedure allows to manage delta records, but you must make sure that you can determine the status in the user exit before and after the document change (this varies from every peculiar situation and from which table the field is filled, e.g. internal document tables, such as XVBAP and YVBAP).</i>

So my long question boils down to:

HOW do I reference XVBPA or YVBPA in the user exit for MCS10001?

Personally I believe it's not possible, and Roberto and SAP Notes are wrong to even mention it. The user exit does not have the context to see those X,Y structures. The only structures available are MCVBAKB, MCVBUKB, MCVBAPB, MCVBUPB, MCVBKDB and unfortunately no MCVBPAB!

Thanks for your time and reading through my long Q. Good day!

[Former Member]

<BR/>A long time has passed since I asked on X and Y images ... a brief update:

<BR/>

<BR/>We decided that extending extractors in either user exit (MCS or RSAP) is not a great idea. A better way to retrieve data not included in standard extractors AND not available by extending LIS cockpit is to create a generic extractor from scratch. The trick is to hinge the delta mechanism of the generic extractor on the "change date" field VBAP-AEDAT and make sure that date field is always updated when something on the order is changed. Not a perfect solution but it gets the job done.

<BR/>

<BR/>For the example above regarding partners, I wrote a generic extractor for all the partners we wanted in BW:

<BR/>

<BR/>VBELN Sales Document

<BR/>POSNR Sales Document Item

<BR/>DLTDAT BW Date for Delta Extraction either Create or Mod Date

<BR/>CSSREP CSS Representative

<BR/>TECHREP Technical Representative

<BR/>SALESREP Sales Representative

<BR/>SOLDTO Sold-to Customer

<BR/>BILLTO Bill-to Customer

<BR/>PAYER Payer Customer

<BR/>SHIPTO Ship-to Customer

<BR/>BROKER Broker Customer

<BR/>AGENT Sales Agent Customer

<BR/>

<BR/>Where the delta mechanism depends on the VBAP-AEDAT field.

<BR/>

<BR/>Hope this helps. Regards, Peter

<BR/>

<BR/>R/3 extractor code follows:

<BR/>

<BR/>

FUNCTION ZBW_BIW_GET_DATA_ZLIS_11_IMPNR.
<BR/>*"----------------------------------------------------------------------
<BR/>*"*"Local interface:
<BR/>*"  IMPORTING
<BR/>*"     VALUE(I_REQUNR) TYPE  SRSC_S_IF_SIMPLE-REQUNR
<BR/>*"     VALUE(I_DSOURCE) TYPE  SRSC_S_IF_SIMPLE-DSOURCE OPTIONAL
<BR/>*"     VALUE(I_MAXSIZE) TYPE  SRSC_S_IF_SIMPLE-MAXSIZE OPTIONAL
<BR/>*"     VALUE(I_INITFLAG) TYPE  SRSC_S_IF_SIMPLE-INITFLAG OPTIONAL
<BR/>*"     VALUE(I_READ_ONLY) TYPE  SRSC_S_IF_SIMPLE-READONLY OPTIONAL
<BR/>*"  TABLES
<BR/>*"      I_T_SELECT TYPE  SRSC_S_IF_SIMPLE-T_SELECT OPTIONAL
<BR/>*"      I_T_FIELDS TYPE  SRSC_S_IF_SIMPLE-T_FIELDS OPTIONAL
<BR/>*"      E_T_DATA STRUCTURE  ZBW_BIW_S_ZLIS_11_ITEMPARTNRS OPTIONAL
<BR/>*"  EXCEPTIONS
<BR/>*"      NO_MORE_DATA
<BR/>*"      ERROR_PASSED_TO_MESS_HANDLER
<BR/>*"----------------------------------------------------------------------
<BR/>
<BR/>* Example: DataSource for table SFLIGHT
<BR/>*  TABLES: BDCPS.
<BR/>
<BR/>* Auxiliary Selection criteria structure
<BR/>  DATA: L_S_SELECT TYPE SRSC_S_SELECT.
<BR/>
<BR/>*for temp table work to get prices
<BR/>  DATA: BEGIN OF I_T_ORDER_ITEM_LIST OCCURS 0,
<BR/>          VBELN   TYPE VBELN_VA,
<BR/>          POSNR   TYPE POSNR_VA,
<BR/>          DLTDAT  TYPE Z_DDLTDATE,
<BR/>        END OF I_T_ORDER_ITEM_LIST,
<BR/>        I_T_DATA     LIKE LINE OF E_T_DATA.
<BR/>  FIELD-SYMBOLS: <I_FS_ORDER_ITEM> LIKE LINE OF I_T_ORDER_ITEM_LIST.
<BR/>
<BR/>* Maximum number of lines for DB table
<BR/>  STATICS: S_S_IF TYPE SRSC_S_IF_SIMPLE,
<BR/>
<BR/>* counter
<BR/>          S_COUNTER_DATAPAKID LIKE SY-TABIX,
<BR/>
<BR/>* cursor
<BR/>          S_CURSOR TYPE CURSOR.
<BR/>
<BR/>* Select ranges
<BR/>  RANGES: L_R_VBELN  FOR VBAP-VBELN,    "DOC
<BR/>          L_R_POSNR  FOR VBAP-POSNR,    "ITEM
<BR/>          L_R_DLTDAT FOR VBAP-ERDAT.    "DELTA DATE
<BR/>
<BR/>* Initialization mode (first call by SAPI) or data transfer mode
<BR/>* (following calls) ?
<BR/>  IF I_INITFLAG = SBIWA_C_FLAG_ON.
<BR/>
<BR/>************************************************************************
<BR/>* Initialization: check input parameters
<BR/>*                 buffer input parameters
<BR/>*                 prepare data selection
<BR/>************************************************************************
<BR/>
<BR/>* Invalid second initialization call -> error exit
<BR/>    IF NOT G_FLAG_INTERFACE_INITIALIZED IS INITIAL.
<BR/>
<BR/>      IF 1 = 2. MESSAGE E008(R3). ENDIF.
<BR/>      LOG_WRITE 'E'                    "message type
<BR/>                'R3'                   "message class
<BR/>                '008'                  "message number
<BR/>                ' '                    "message variable 1
<BR/>                ' '.                   "message variable 2
<BR/>      RAISE ERROR_PASSED_TO_MESS_HANDLER.
<BR/>    ENDIF.
<BR/>
<BR/>* Check DataSource validity
<BR/>    CASE I_DSOURCE.
<BR/>      WHEN 'ZLIS_11_ITEMPARTNRS'.
<BR/>      WHEN OTHERS.
<BR/>        IF 1 = 2. MESSAGE E009(R3). ENDIF.
<BR/>* this is a typical log call. Please write every error message likethis
<BR/>        LOG_WRITE 'E'                  "message type
<BR/>                  'R3'                 "message class
<BR/>                  '009'                "message number
<BR/>                  I_DSOURCE   "message variable 1
<BR/>                  ' '.                 "message variable 2
<BR/>        RAISE ERROR_PASSED_TO_MESS_HANDLER.
<BR/>    ENDCASE.
<BR/>
<BR/>    APPEND LINES OF I_T_SELECT TO S_S_IF-T_SELECT.
<BR/>
<BR/>* Fill parameter buffer for data extraction calls
<BR/>    S_S_IF-REQUNR    = I_REQUNR.
<BR/>    S_S_IF-DSOURCE   = I_DSOURCE.
<BR/>    S_S_IF-MAXSIZE   = I_MAXSIZE.
<BR/>    G_FLAG_INTERFACE_INITIALIZED = SBIWA_C_FLAG_ON.
<BR/>
<BR/>* Fill field list table for an optimized select statement
<BR/>* (in case that there is no 1:1 relation between InfoSource fields
<BR/>* and database table fields this may be far from beeing trivial)
<BR/>    APPEND LINES OF I_T_FIELDS TO S_S_IF-T_FIELDS.
<BR/>
<BR/>  ELSE.                 "Initialization mode or data extraction ?
<BR/>
<BR/>************************************************************************
<BR/>* Data transfer: First Call      OPEN CURSOR + FETCH
<BR/>*                Following Calls FETCH only
<BR/>************************************************************************
<BR/>
<BR/>* First data package -> OPEN CURSOR
<BR/>    IF G_COUNTER_DATAPAKID = 0.
<BR/>
<BR/>* Fill range tables BW will only pass down simple selection criteria
<BR/>* of the type SIGN = 'I' and OPTION = 'EQ' or OPTION = 'BT'.
<BR/>      LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT.
<BR/>        CASE l_s_select-fieldnm.
<BR/>          WHEN 'VBELN'.
<BR/>            MOVE-CORRESPONDING L_S_SELECT TO L_R_VBELN.
<BR/>            APPEND L_R_VBELN.
<BR/>          WHEN 'POSNR'.
<BR/>            MOVE-CORRESPONDING L_S_SELECT TO L_R_POSNR.
<BR/>            APPEND L_R_POSNR.
<BR/>          WHEN 'DLTDAT'.
<BR/>            MOVE-CORRESPONDING L_S_SELECT TO L_R_DLTDAT.
<BR/>            APPEND L_R_DLTDAT.
<BR/>          ENDCASE.
<BR/>      ENDLOOP.
<BR/>
<BR/>* Determine number of database records to be read per FETCH statement
<BR/>* from input parameter I_MAXSIZE. If there is a one to one relation
<BR/>* between DataSource table lines and database entries, this is trivial.
<BR/>* In other cases, it may be impossible and some estimated value has to
<BR/>* be determined.
<BR/>
<BR/>      OPEN CURSOR WITH HOLD S_CURSOR FOR
<BR/>        SELECT itm~VBELN AS VBELN itm~POSNR AS POSNR itm~AEDAT AS DLTDAT
<BR/>          FROM VBAP AS itm
<BR/>          WHERE itm~VBELN IN L_R_VBELN
<BR/>          AND itm~POSNR IN L_R_POSNR
<BR/>          AND itm~VBELN NE ''              "fix for blank docs in PR3 VBAP
<BR/>          AND (
<BR/>               ( itm~AEDAT EQ '00000000' AND itm~ERDAT IN L_R_DLTDAT ) OR
<BR/>               ( itm~AEDAT NE '00000000' AND itm~AEDAT IN L_R_DLTDAT )
<BR/>              ).
<BR/>
<BR/>    ENDIF.                             "First data package ?
<BR/>
<BR/>* Fetch records into temp table.
<BR/>    REFRESH: I_T_ORDER_ITEM_LIST.
<BR/>    FETCH NEXT CURSOR S_CURSOR
<BR/>               APPENDING CORRESPONDING FIELDS
<BR/>               OF TABLE I_T_ORDER_ITEM_LIST
<BR/>               PACKAGE SIZE S_S_IF-MAXSIZE.
<BR/>    IF SY-SUBRC <> 0.
<BR/>      CLOSE CURSOR S_CURSOR.
<BR/>      RAISE NO_MORE_DATA.
<BR/>    ENDIF.
<BR/>
<BR/>*add partner values onto output table.
<BR/>    LOOP AT I_T_ORDER_ITEM_LIST ASSIGNING <I_FS_ORDER_ITEM>.
<BR/>
<BR/>*new row for e_t_data
<BR/>      CLEAR: I_T_DATA.
<BR/>
<BR/>*add doc, item
<BR/>      MOVE-CORRESPONDING <I_FS_ORDER_ITEM> TO I_T_DATA.
<BR/>
<BR/>*SALESREP = Z5
<BR/>*CSSREP = Z2
<BR/>*TECHREP = Z3
<BR/>*SOLDTO = SP, OR AG
<BR/>*BILLTO = BP, OR RE
<BR/>*PAYER = PY, OR RG
<BR/>*SHIPTO = SH, OR WE
<BR/>*BROKER = Z7
<BR/>*AGENT = Z4
<BR/>
<BR/>*get sales rep
<BR/>      SELECT SINGLE PERNR
<BR/>        INTO I_T_DATA-SALESREP
<BR/>        FROM VBPA
<BR/>        WHERE VBELN = <I_FS_ORDER_ITEM>-VBELN
<BR/>        AND PARVW = 'Z5'
<BR/>        AND POSNR IN (
<BR/>          SELECT MAX( POSNR ) FROM VBPA
<BR/>          WHERE VBELN = <I_FS_ORDER_ITEM>-VBELN
<BR/>          AND PARVW = 'Z5' ).
<BR/>
<BR/>*get css
<BR/>      SELECT SINGLE PERNR
<BR/>        INTO I_T_DATA-CSSREP
<BR/>        FROM VBPA
<BR/>        WHERE VBELN = <I_FS_ORDER_ITEM>-VBELN
<BR/>        AND PARVW = 'Z2'
<BR/>        AND POSNR IN (
<BR/>          SELECT MAX( POSNR ) FROM VBPA
<BR/>          WHERE VBELN = <I_FS_ORDER_ITEM>-VBELN
<BR/>          AND PARVW = 'Z2' ).
<BR/>
<BR/>*get tech
<BR/>      SELECT SINGLE PERNR
<BR/>        INTO I_T_DATA-TECHREP
<BR/>        FROM VBPA
<BR/>        WHERE VBELN = <I_FS_ORDER_ITEM>-VBELN
<BR/>        AND PARVW = 'Z3'
<BR/>        AND POSNR IN (
<BR/>          SELECT MAX( POSNR ) FROM VBPA
<BR/>          WHERE VBELN = <I_FS_ORDER_ITEM>-VBELN
<BR/>          AND PARVW = 'Z3' ).
<BR/>
<BR/>*get SOLDTO
<BR/>      SELECT SINGLE KUNNR
<BR/>        INTO I_T_DATA-SOLDTO
<BR/>        FROM VBPA
<BR/>        WHERE VBELN = <I_FS_ORDER_ITEM>-VBELN
<BR/>        AND PARVW = 'AG'
<BR/>        AND POSNR IN (
<BR/>          SELECT MAX( POSNR ) FROM VBPA
<BR/>          WHERE VBELN = <I_FS_ORDER_ITEM>-VBELN
<BR/>          AND PARVW = 'AG' ).
<BR/>
<BR/>*get BILLTO
<BR/>      SELECT SINGLE KUNNR
<BR/>        INTO I_T_DATA-BILLTO
<BR/>        FROM VBPA
<BR/>        WHERE VBELN = <I_FS_ORDER_ITEM>-VBELN
<BR/>        AND PARVW = 'RE'
<BR/>        AND POSNR IN (
<BR/>          SELECT MAX( POSNR ) FROM VBPA
<BR/>          WHERE VBELN = <I_FS_ORDER_ITEM>-VBELN
<BR/>          AND PARVW = 'RE' ).
<BR/>
<BR/>*get SHIPTO
<BR/>      SELECT SINGLE KUNNR
<BR/>        INTO I_T_DATA-SHIPTO
<BR/>        FROM VBPA
<BR/>        WHERE VBELN = <I_FS_ORDER_ITEM>-VBELN
<BR/>        AND PARVW = 'WE'
<BR/>        AND POSNR IN (
<BR/>          SELECT MAX( POSNR ) FROM VBPA
<BR/>          WHERE VBELN = <I_FS_ORDER_ITEM>-VBELN
<BR/>          AND PARVW = 'WE' ).
<BR/>
<BR/>*get PAYER
<BR/>      SELECT SINGLE KUNNR
<BR/>        INTO I_T_DATA-PAYER
<BR/>        FROM VBPA
<BR/>        WHERE VBELN = <I_FS_ORDER_ITEM>-VBELN
<BR/>        AND PARVW = 'RG'
<BR/>        AND POSNR IN (
<BR/>          SELECT MAX( POSNR ) FROM VBPA
<BR/>          WHERE VBELN = <I_FS_ORDER_ITEM>-VBELN
<BR/>          AND PARVW = 'RG' ).
<BR/>
<BR/>*get BROKER
<BR/>      SELECT SINGLE KUNNR
<BR/>        INTO I_T_DATA-BROKER
<BR/>        FROM VBPA
<BR/>        WHERE VBELN = <I_FS_ORDER_ITEM>-VBELN
<BR/>        AND PARVW = 'Z7'
<BR/>        AND POSNR IN (
<BR/>          SELECT MAX( POSNR ) FROM VBPA
<BR/>          WHERE VBELN = <I_FS_ORDER_ITEM>-VBELN
<BR/>          AND PARVW = 'Z7' ).
<BR/>
<BR/>*get AGENT
<BR/>      SELECT SINGLE KUNNR
<BR/>        INTO I_T_DATA-BROKER
<BR/>        FROM VBPA
<BR/>        WHERE VBELN = <I_FS_ORDER_ITEM>-VBELN
<BR/>        AND PARVW = 'Z4'
<BR/>        AND POSNR IN (
<BR/>          SELECT MAX( POSNR ) FROM VBPA
<BR/>          WHERE VBELN = <I_FS_ORDER_ITEM>-VBELN
<BR/>          AND PARVW = 'Z4' ).
<BR/>
<BR/>*FINALLY, add this row to the output
<BR/>      APPEND I_T_DATA TO E_T_DATA.
<BR/>
<BR/>    ENDLOOP.
<BR/>
<BR/>    g_COUNTER_DATAPAKID = g_COUNTER_DATAPAKID + 1.
<BR/>
<BR/>  ENDIF.              "Initialization mode or data extraction ?
<BR/>
<BR/>ENDFUNCTION.

Edited by: Peter Bennetto on Apr 6, 2009 11:24 AM

[alexander_stettler]

Hi Peter Bennetto,

I also prefer your solution for the extraction of the vbpa table. Unfortunately I have a little problem with the field vbap-aedat. If I change or add partner roles in the transaction VA02 and safe my changes the field (vbap-aedat) wonu2019t be updated. So, what did you done to make sure that the aedat field is always updated?

Regards, Alex

[Former Member]

Hi Alex - Did you find a solution to generate a delta extractor for VBPA table? If yes can you please share with me?

Thanks

[alexander_stettler]

Hi BISAP10,

Until now we extracted all data (every day) and wrote these into an optimized DSO ...

This works well - but it isn't a really great solution (in my opinion). Thus I prefer the User Exit MV45AFZZ (userexit_save_document).

Greetings

Alex

[Former Member]

Hi Peter,

Did you able to resolve your issue. Are you able to change exit MCS10001?

Thanks

Samir

[Former Member]

Hi,

If you are able to define exactly which field is to be changed/corrected because of that situation, I would definitely use the transfer rules in BW, to erase the content of the first row from the next extraction in order to get:

Order 1, line 10, technical analyst = "", RecMode = "X"

Order 1, line 10, technical analyst = "1", RecMode = ""

Help to some Abap codes in the start routine, modify the technical analyst with 0RECORD_MODE content, or do it in the single routine for the targetted field.

Regards

Laurent

[Former Member]

Lauren,

If I can pseudocode what you are suggesting:

loop data_package

if data_package-recordmode = X

select * from current cube or ods where documentnumber = data_package-documentnumber

modify data_package-technical_analyst with existing technical_analyst

endif

endloop

I could see a problem when there are multiple rows in the extraction for the same document.

Interesting to think about though.

[Former Member]

Inside a user-exit, you can refer to variables in the main program in which user-exit is included. The way is create a field symbol and, in runtime, assing the value as '(main_program)variable'. I did it several times in my abaper times, but i'm afraid that now i don´t have any code sample for you, sorry.

[Former Member]

Hi Peter,

I would do the following:

loop at data_package where record_mode = 'X'.

clear data_package-<i><b>technical_analyst</b></i>.

modify data_package.

endloop.

Laurent

[Former Member]

Thank you Laurent however your suggestion is incorrect. It assumes that the previous value for technical_analyst is always blank. For example, if the previous value of technical_analyst was "2", then clearing the technical_analyst field on a delta data load will not correctly eliminate a row from the cube. Because the primary key of a cube includes all fields, the delta load must have correct values in all fields (not just blank in some).

Regards

[Former Member]

Hello Peter,

You are right, my suggestion would create 'new' old images ... Odd ...

I belive that you do not have the choice of using an ODS to capture the delta for that field, as you said.

Regards,

Laurent

[former_member185837]

Oscar's suggestion is defenetely the right one.

If you're enhancing the MCVBAP LIS communication structure, in order to deliver new fields to the Sales (11) LO DataSources, you can refer to the X and Y internal tables that are defined in the SAPMV45A main program.

In the case of partner data, for instance:

FIELD-SYMBOLS: <y1> TYPE table.
FIELD-SYMBOLS: <x1> TYPE table.

DATA tb_pa LIKE vbpavb OCCURS 0 WITH HEADER LINE.

[...]

ASSIGN ('(SAPMV45A)YVBPA[]') TO <y1>.
IF sy-subrc EQ 0.
    tb_pa[] = <y1>.
ENDIF.

[...]

ASSIGN ('(SAPMV45A)XVBPA[]') TO <x1>.
IF sy-subrc EQ 0.
    tb_pa[] = <x1>.
ENDIF.

[...]

Hope I'll find some time to post some more content about this matter in the code section of the SDN Wiki.

Cheers, [Davide|https://wiki.sdn.sap.com/wiki/display/profile/Davide+Cavallari]

Edited by: Davide Cavallari on Feb 18, 2008 2:19 PM

[Former Member]

Hello,

I have the same question.

Please, can somebody help.

Thanks in advanced,

Benjamin

[Former Member]

There is no graceful solution.

The partial solution is to:

1) Do not use MCS10001 (because it is delta relevant)

2) Use RSAP0001 instead (this only appends and is not delta relevant)

3) Send the extraction to an ODS (an ODS does not care about before, after images and the only primary key is document#/item#)

4) Extract from the ODS to an InfoCube (using delta extraction)

The only advantage here is you are using the change log of an ODS instead of the delta mechanism from R/3.

The disadvantage is you have doubled your data size in BW, from 1 cube to 1 cube and 1 ODS.

-Peter