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.

sap_query_query_areas_001

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

sap_query_query_areas_002

Step 2. Creation of InfoSet

To build a query with required fields it was necessary to create an InfoSet that joins 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 done. At first a client-specific query area was chosen.

sap_query_query_areas_001

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.

sap_query_query_areas_003

The result was the following:

sap_query_query_areas_004

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

sap_query_infoset_005

The result should be the following:

sap_query_infoset_006

Other required tables were added using the same procedure:

sap_query_infoset_007

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

sap_query_infoset_008

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

sap_query_infoset_009

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. Therefore add ‘ZZENDUSER’ and ‘ZZPRJMNGR’ in the Extras tab.

sap_query_infoset_010

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

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

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

sap_query_infoset_012

Step 3. Creation of Query

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

sap_query_query_areas_001

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

sap_query_group_013

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

sap_query_group_014

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

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

sap_query_group_016
sap_query_group_017

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.

sap_query_group_018

This exactly 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’.

sap_query_018

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.

sap_query_019

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:

sap_query_020

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

sap_query_021

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

sap_query_022

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.

sap_query_023

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

sap_query_024

Logic can be implemented in the ‘DATA_TRANSFORM’ method.

sap_query_025

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

sap_query_026

Leave a Comment

Contact Us

Please do not hesitate to contact us, so one of our experts will get back to you asap!

Not readable? Change text. captcha txt

Start typing and press Enter to search