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.
Based on the customer requirements it was necessary to retrieve additional fields for sales documents items via the data source ‘2LIS_11_VAITM’.
|Sales Document: Partner|
|VBEGDAT||Contract start date|
|VENDDAT||Contract end date|
|VLAUFZ||Validity period of contract|
|VLAUFK||Validity period category of contract|
|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|
|VBAP||Sales Document: Item Data|
|VBPA||Sales Document: Partner|
|VBEGDAT||Add||Contract start date|
|VENDDAT||Add||Contract end date|
|VLAUFZ||Add||Validity period of contract|
|VLAUFK||Add||Validity period category of contract|
|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|
|VBAP||Sales Document: Item Data|
|Sales Document: Partner|
|VBEGDAT||X||Contract start date|
|VENDDAT||X||Contract end date|
|VLAUFZ||X||Validity period of contract|
|VLAUFK||X||Validity period category of contract|
|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|
|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.
|VBDAT_VEDA||DATS||8||Contract start date|
Contract end date
Validity period of contract
Validity period category of contract
Action at the end of contract
Date rule for action
Date for action
Assignment cancellation procedure/cancellation rule
Reason for Cancellation of Contract
Unit of validity period of contract
Sales Project Manager
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’.