Using SAP Query Functionality for BI Data Source Enhancement

Scenario

This article shows how simply created SAP Queries can be used when it is required to enhance a SAP BW data source with additional fields.

SAP Query is a tool that allows to create reports even without programming. SAP queries are based on InfoSets – these are the areas that provide special views of a logical data base and define which fields of the database can be used in queries. SAP queries can be shared with any users via assigning InfoSets to user groups.

Business Case

Based on the customer requirements it was necessary to retrieve additional fields for sales documents items via the data source ‘2LIS_11_VAITM’.

Table	Field	Description
VBPA		Sales Document: Partner
	KUNNR	Customer Number
	PERNR	Personnel Number
	PARVW	Partner Function
VEDA		Contract Data
	VBEGDAT	Contract start date
	VENDDAT	Contract end date
	VLAUFZ	Validity period of contract
	VLAUFK	Validity period category of contract
	VINSDAT	Installation date
	VAKTSCH	Action at end of contract
	VASDR	Date rule for action
	VASDA	Date for action
	VKUESCH	Assignment cancellation procedure/cancellation rule
	VKUEGRU	Reason for Cancellation of Contract
	VLAUEZ	Unit of validity period of contract

To reduce the costs of the data source development the decision to get additional fields via SAP Query was made. Extraction logic was defined in BAdI ‘IF_EX_RSU5_SAPI_BADI’ implementation.

Step 1. Creation of User Group

Before creation of a Query and an InfoSet, a User group ‘BW’ BW Queries was created to have a possibility to share the query with other users.

To create a User group, it is necessary to run transaction ‘SQ03’ and choose a client-specific query area.

Then enter the name of a User Group and push Create. Enter the description and click Save.

Step 2. Creation of InfoSet

To build a query with required fields it was necessary to create an InfoSet that would join different tables and define fields for SAP Query. In the example these were the following tables and fields:

Table	Field	Key field / Additional field, Key / Add	Description
VBAK			Sales Document: Header Data
	VBELN	Key	Sales Document
VBAP			Sales Document: Item Data
	VBELN	Key	Sales Document
VBPA			Sales Document: Partner
	VBELN	Key	Sales Document
	KUNNR	Add	Customer Number
	PERNR	Add	Personnel Number
	PARVW	Add	Partner Function
VEDA			Contract Data
	VBELN	Key	Sales Document
	VBEGDAT	Add	Contract start date
	VENDDAT	Add	Contract end date
	VLAUFZ	Add	Validity period of contract
	VLAUFK	Add	Validity period category of contract
	VINSDAT	Add	Installation date
	VAKTSCH	Add	Action at the end of contract
	VASDR	Add	Date rule for action
	VASDA	Add	Date for action
	VKUESCH	Add	Assignment cancellation procedure/cancellation rule
	VKUEGRU	Add	Reason for Cancellation of Contract
	VLAUEZ	Add	Unit of validity period of contract

Thus, to create an InfoSet in transaction ‘SQ02’ the following steps were taken. First, a client-specific query area was chosen.

The InfoSet name ‘ZBW_LIS_FIELDS’, for example, was entered. Then Create button was clicked and an InfoSet description and a basis table were entered.

The result was the following:

To insert other tables, it is necessary to go to Edit -> Insert Table or use the icon on the toolbar

The result should be the following:

Other required tables were added using the same procedure:

When all tables were added to the InfoSet, Back button was clicked and Include all tables field option was chosen.

All fields from all tables are to be included in relevant field groups.

To retrieve some additional fields that are not present in the chosen tables, Extras button on the toolbar is available. In our example, it was required to add End-user and Project Manager fields. Thus adding ‘ZZENDUSER’ and ‘ZZPRJMNGR’ in the Extras tab.

These fields get values from fields ‘VBPA-KUNNR’ and ‘VBPA-PERNR’ accordingly.

To fill them in, the following logic was inserted in the Code tab, then the InfoSet was saved and activated.

    FIELD-SYMBOLS:  TYPE any,
                    TYPE any,
                        TYPE any,
                        TYPE any,
                        TYPE any,
                        TYPE any,
                     TYPE any,
                        TYPE any.
* Assigning field symbol to the selected data
    ASSIGN ('%dtab[]') TO .
* skip check for all VBPA header data
    IF vbpa-posnr NE '0000'.
* do not iclude into result records like SO Item 0010 and SO Item Partner 0020
      IF vbap-posnr NE vbpa-posnr.
        CHECK 1 = 0.
* ELSE statement is only executed when SP Item 0010 and SO Partner Item 0010
* it means business re-defined header partners in item level
* then for this partner function we have to delete record for this SO Item
* that is read from SO Partner Header
      ELSE.
        LOOP AT  ASSIGNING .
          ASSIGN COMPONENT 'VBELN' OF STRUCTURE  TO .
          ASSIGN COMPONENT 'POSNR' OF STRUCTURE  TO .
          ASSIGN COMPONENT 'PARVW' OF STRUCTURE  TO .
          ASSIGN COMPONENT 'POSNR001' OF STRUCTURE  TO .
          IF (  = vbap-vbeln
                    AND  = vbap-posnr
                    AND  = vbpa-parvw
                    AND  = '0000' ).
            DELETE .
          ENDIF.
        ENDLOOP.
      ENDIF.
    ENDIF.
* skip check for all VEDA header data
    IF veda-vposn NE '0000'.
* do not iclude into result records like SO Item 0010 and SO VEDA (Contract) 0020
      IF vbap-posnr NE veda-vposn.
        CHECK 1 = 0.
* ELSE statement is only executed when SP Item 0010 and SO VEDA (Contract) Item 0010
* it means business re-defined header contract in item level
* then for SO Item we have to delete records for this SO VEDA (Contract) Header (VPOSN='0000')
      ELSE.
        LOOP AT  ASSIGNING .
          ASSIGN COMPONENT 'VBELN' OF STRUCTURE  TO .
          ASSIGN COMPONENT 'POSNR' OF STRUCTURE  TO .
          ASSIGN COMPONENT 'VPOSN' OF STRUCTURE  TO .
          IF (  = vbap-vbeln
                    AND  = vbap-posnr
                    AND  = '0000' ).
            DELETE .
          ENDIF.
        ENDLOOP.
      ENDIF.
    ENDIF.
* Fill Sales Manager
    CASE vbpa-parvw.
      WHEN 'ZU'.
*  BREAK-POINT.
        LOOP AT  ASSIGNING .
          ASSIGN COMPONENT 'VBELN' OF STRUCTURE  TO .
          ASSIGN COMPONENT 'POSNR' OF STRUCTURE  TO .
          ASSIGN COMPONENT 'PARVW' OF STRUCTURE  TO .
          ASSIGN COMPONENT 'zzenduser' OF STRUCTURE  TO .
          ASSIGN COMPONENT 'POSNR001' OF STRUCTURE  TO .
          IF (  = vbap-vbeln
                    AND  = vbap-posnr
                    AND  = 'AG' ).
             = vbpa-kunnr.
             = vbpa-posnr.
          ELSEIF (  = vbap-vbeln
                    AND  = vbap-posnr ).
            DELETE .
          ENDIF.
        ENDLOOP.
        CHECK 1 = 0.
      WHEN 'ZA'.
        LOOP AT  ASSIGNING .
          ASSIGN COMPONENT 'VBELN' OF STRUCTURE  TO .
          ASSIGN COMPONENT 'POSNR' OF STRUCTURE  TO .
          ASSIGN COMPONENT 'PARVW' OF STRUCTURE  TO .
          ASSIGN COMPONENT 'zzprjmngr' OF STRUCTURE  TO .
          ASSIGN COMPONENT 'POSNR001' OF STRUCTURE  TO .
          IF (  = vbap-vbeln
                    AND  = vbap-posnr
                    AND  = 'AG' ).
             = vbpa-pernr.
             = vbpa-posnr.
          ELSEIF (  = vbap-vbeln
                    AND  = vbap-posnr ).
            DELETE .
          ENDIF.
        ENDLOOP.
        CHECK 1 = 0.
      WHEN 'AG'.
* do nothing, but keep it
      WHEN OTHERS.
        CHECK 1 = 0.
    ENDCASE.

To assign the InfoSet to a specific user group it is necessary to click a relevant button and choose a user group that was created in the previous step – ‘BW’ in our case.

Step 3. Creation of Query

For Query creation run transaction ‘SQ01’ and choose a client-specific query area.

Enter the query’s name (‘Z_BW_LIS_ADD_F’), click Create and select a required InfoSet – ‘ZBW_LIS_FIELDS‘, for example.

The title was filled and the number of columns was defined. Then it is necessary to click on the Basic List.

Tick all fields that are required to be received from the query in each source table and the additional fields area.

Table	Field	List fields	Selection fields	Description
VBAK				Sales Document: Header Data
	VBELN	X	X	Sales Document
VBAP				Sales Document: Item Data
	VBELN	X		Sales Document
VBPA				Sales Document: Partner
	VBELN	X		Sales Document
	KUNNR	X		Customer Number
	PERNR	X		Personnel Number
	PARVW	X		Partner Function
VEDA				Contract Data
	VBELN	X		Sales Document
	VBEGDAT	X		Contract start date
	VENDDAT	X		Contract end date
	VLAUFZ	X		Validity period of contract
	VLAUFK	X		Validity period category of contract
	VINSDAT	X		Installation date
	VAKTSCH	X		Action at the end of contract
	VASDR	X		Date rule for action
	VASDA	X		Date for action
	VKUESCH	X		Assignment cancellation procedure/cancellation rule
	VKUEGRU	X		Reason for Cancellation of Contract
	VLAUEZ	X		Unit of validity period of contract
—				Additional Fields
	ZZENDUSER	X		End-user party
	ZZPRJMNGR	X		Sales Project Manager

To use a created query in the extractor it is necessary to insert and adapt the ABAP program (or its part) that is automatically generated while query creation. The Program name can be found in the Display report name menu.

To check the ABAP report it is necessary to open it in transaction ‘SE38’ and find an automatically generated function module with suffix ‘EXTR’ at the end of its name.

Exactly this function module will be used in the extractor ‘2LIS_11_VAITM’ to retrieve additional Contract and Business Partner information to SAP BW.

Step 4. Adding required fields as append of sales document items structure

Adding required fields can be done via structure enhancement: transaction ‘SE11’ -> structure ‘MCVBAP’.

A new append structure ‘ZBW_S_MCVBAPUSR_APP’ BW :: LIS Extractor Append Structure (see SMOD MCS10001) with contract data and business partners fields was added.

Field	Type	Data type	Length	Description
ZZBEGDAT	VBDAT_VEDA	DATS	8	Contract start date
ZZENDDAT	VNDAT_VEDA	DATS	8	Contract end date
ZZLAUFZ	VLAUF_VEDA	NUMC	3	Validity period of contract
ZZLAUFK	VLAUK_VEDA	CHAR	2	Validity period category of contract
ZZINSDAT	VIDAT_VEDA	DATS	8	Installation date
ZZAKTSCH	VASCH_VEDA	CHAR	4	Action at the end of contract
ZZASDR	VASDR	CHAR	2	Date rule for action
ZZASDA	VASDA	DATS	8	Date for action
ZZKUESCH	VKUES_VEDA	CHAR	4	Assignment cancellation procedure/cancellation rule
ZZKUEGRU	VKGRU_VEDA	CHAR	2	Reason for Cancellation of Contract
ZZLAUEZ	VLAUE_VEDA	CHAR	1	Unit of validity period of contract
ZZPRJMNGR	ZBW_PRJMNGR	NUMC	8	Sales Project Manager
ZZENDUSER	ZBW_ENDUSER	CHAR	10	End-user party
ZZUNSUB_MATNR	ZBW_UNSUB_MATNR	CHAR	18	Original MATNR

The result:

Step 5. Adding required field to extract structure

To enhance the extractor, it is necessary to make some maintenance in the transaction LBWE -> Maintenance of extract structure ‘MC11VA0ITM’ of the data source ‘2LIS_11_VAITM’.

‘MCVBAP’ was chosen from the dropdown list and all fields that had been added in Step 4 were moved to Append structure ‘ZBW_S_MCVBAPUSR_APP’ from Selection criteria to Pool.

The changes were saved and activated.

To check the extract structure of the data source ‘2LIS_11_VAITM’ Sales Orders Item, it is necessary to go to ‘SE11’ -> structure ‘MC11VA0ITM’ and find the required fields.

Step 6. Implementation of extract logic in BAdI

To fill in all additional fields that were added to the extract structure ‘MC11VA0ITM’ (Step 5) it is necessary to create the implementation of BAdI ‘IF_EX_RSU5_SAPI_BADI’. This BAdI is used for the data source enhancement.

Thus, in transaction ‘SE19’ implementation ‘ZRSU5_BADI_11_VAITM’ was created.

For example, the name of implementing class ‘ZCL_IM_RSU5_BADI_11_VAITM’.

Logic can be implemented in the ‘DATA_TRANSFORM’ method.

As mentioned above, to get the data via SAP Query it is necessary to use the function module ‘/1BCDWB/IQ000000010022EXTR’ in the extractor. But note that for transporting purposes this function module should be wrapped into Z-namespace – that will be explained in a separate article.

In our case this is the function module ‘ZBW_FG_LIS_NETSALES_NEW_EXTR’.