For those of you who have already tried to get data out of Loyalty component in SAP CRM, this short document is maybe too late. For those of you just starting, please, keep in mind you are really lucky!

As a short introduction we have to say that for BW consultants used to work with SAP ERP, working with SAP CRM for the first time will require a short period of adjustment. You will have to get used with new objets, new names and a different table structure. The good news for you is: some of us have already walked this path!

This document will describe how to create a SAP CRM Loyalty generic datasource with the main fields necessary to perform key analysis related to loyalty in SAP NetWeaver BW (SAP BW). The goal is to explain step by step how to perform the following activities:

  • Create a view based on SAP CRM Loyalty tables;
  • Create a generic datasource based on that view;
  • Include new fields from other tables in the generic datasource;
  • Create an ABAP routine to populate the new fields.

We will use the following SAP CRM Loyalty tables:

:

  • LOYD_PT_TXN – Loyalty Management Point Account Transaction;
  • LOYD_MEM_MEMBER – Loyalty Menagement : Database Table for Member;
  • LOYD_MSH_MEMS – Loyalty Membership Table;
  • LOYD_MA_SPECATTR – Table for member activity specific attributes;
  • LOYD_MA_GENATTR – Table to store member activity generic attributes;
  • CGPL_PROJECT – Project Planning: Attribute Table for Project Headers.

Step 1) Create a View based on LOYD_PT_TXN table:

First we must create a view with the main loyalty tables, here we will use only the LOYD_PT_TXN table, which stores main loyalty transactional data, as  amount of points, transaction reason, point type, among others. Fields from other tables will be inserted later by the Appended structure.

In the SAP CRM system, go to transaction SE11 to create the view. Select the “View” option and enter a technical name for your view. In this case, our view will be the “ZCRM_LOY_01.” Click Create.

/wp-content/uploads/2013/08/1_278733.png

Select the view type. In this case we are creating a database view.

/wp-content/uploads/2013/08/2_278734.png

In view maintenance screen, in tab “Table/Join Conditions”, we will include the tables used to do the join and the relationship between them. Once we are using only one table, the relationship will be done through the GUID field.

/wp-content/uploads/2013/08/3_278756.png

In “View Fields” tab select the fields you would like to include in the view through the “Table Fields” button. We will select all fields from LOYD_PT_TXN.

/wp-content/uploads/2013/08/4_278757.png

Save and activate the view.

Step 2) Create a generic datasource based on view ZCRM_LOY_01:

Go to transaction RSO2, select the “Transaction Data” option and enter the generic datasource technical name. Here we will use the name ZCRM_LOY_TRANS. Click Create.

/wp-content/uploads/2013/08/5_278758.png

In the “Create Datasource” screen, click the “Extraction from View” botton, enter the application component in which the datasource will be located, the descriptions and the view that the datasource will be based. Save the datasource.

/wp-content/uploads/2013/08/6_278760.png

Select the fields that are visible and which are hidden. Hidden fields are not available for extraction. In our case we will keep all fields from ZCRM_LOY_TRANS datasource visible.

Select the fields that will be available for selection in the InfoPackage during extraction for PSA. Once again, we will select all fields.

Save the datasource.

Now we will include the following fields from other SAP CRM loyalty tables that will be used in the datasource:

Field

Short Description

Table

Component Type

MEMB_ID

Business Partner Number

LOYD_MEM_MEMBER

BU_PARTNER

STATUS

Status

LOYD_MSH_MEMS

LOY_MSH_STATUS

STATUS_REASON

Status Reason

LOYD_MSH_MEMS

LOY_MSH_STATUS_REASON

LAST_TXN_DATE

Date

LOYD_MSH_MEMS

LOY_MEMS_DATE

MEMS_TYPE

Membership Types

LOYD_MSH_MEMS

LOY_MSH_MEMS_TYPE

ACTIVITY_DATE

Storing Time Stamp

LOYD_MA_GENATTR

LOY_MA_TIMESTAMP

EXTERNAL_ID

Project Planning: External Indentifier of an Element

CGPL_PROJECT

CGPL_EXTID

PROGRAM_TYPE

Object Type in Project Planning

CGPL_PROJECT

CGPL_OBJECT_TYPE

PROGRAM_GUID

Object GUID

LOYD_MA_GENATTR

LOY_FRW_GUID

TICKET_NUMBER

Ticket Number

LOYD_MA_SPECATTR

LOY_MA_TKT_NUM

AMOUNT

Amount for the activity

LOYD_MA_SPECATTR

LOY_MA_AMOUNT

CURRENCY

Currency

LOYD_MA_SPECATTR

LOY_MA_CURR

RETAIL_STORE_ID

Retail Store ID

LOYD_MA_SPECATTR

LOY_RETAIL_STORE_ID

PARTNSHIP_EXT_ID

MEMBER ACTIVITY PARTNERSHIP EXTERNAL ID

LOYD_MA_GENATTR

LOY_MA_PART_EXT_ID

In t-code RSO2, enter the datasource name and choose display.

/wp-content/uploads/2013/08/7_278761.png

Double-click the extraction structure to view the datasource structure.

We will now add the new fields in this structure. Change to edit mode and click on “Append Structure …”

/wp-content/uploads/2013/08/8_278771.png

Enter a name for the Appended Structure and select Continue.

Enter the name of the fields in “Component” column and the component type of each one in the “Component Type” column.

/wp-content/uploads/2013/08/9_278772.png

Save the modified structure. Return to the extraction structure and check that the inserted fields now appear in the structure.

/wp-content/uploads/2013/08/10_278773.png

Now we need to unhide the fields modified in the extraction structure, otherwise it will not appear on the extraction.

Go to RSA6, locate the ZCRM_LOY_TRANS datasource on the application component hierarchy and choose change datasource.

/wp-content/uploads/2013/08/11_278774.png

All fields from ZCRM_LOY_TRANS datasource are displayed. The modified fields have a tick mark in “Hide Field” column. Remove the tick mark in the column so the fields become visible on the extraction. Save the datasource.

Step 3) Populate new fields using a BADI:

Now we will populate the appended fields via BADI.

I recommend that you see SAP Note 691154 which brings information about user exits with BADI interfaces.

The first step is to check if there is already a BADI RSU5_SAPI_BADI definition on your system. To do this, go to SE18 and enter “RSU5_SAPI_BADI” at “BAdI Name” field and choose Display. If the BADI definition already exists you will see the details of the BADI, otherwise you will need to create this definition.

To create the BADI definition, enter “RSU5_SAPI_BADI” at “BAdI Name” field and choose Create.

/wp-content/uploads/2013/08/12_278775.png

Enter Implementation Name as ZRSU5_SAP_BADI and choose OK.

Enter a description for the implementation and click activate. Once activated the BADI implementation is created and we will populate the fields.

In t-code SE19, enter the name RSU5_SAP_BADI on “Classic BAdI” and click on display.

/wp-content/uploads/2013/08/13_278776.png

To access the methods section, go to the tab “Interfaces” and double-click on the Implementing class name.

/wp-content/uploads/2013/08/14_278777.png

In the Methods tab, create a new method with the name of the generic datasource created, in our case ZCRM_LOY_TRANS.

/wp-content/uploads/2013/08/15_278778.png

Click the “Parameters” button and enter the parameters as follows:

/wp-content/uploads/2013/08/16_278779.png

Returning to methods screen, double click on the method ZCRM_LOY_TRANS to enter the ABAP code:

method ZCRM_LOY_TRANS.

***Declare the structures with the fields of the tables we are using:

types: begin of ty_loyd_mem_member,
GUID   
type LOYD_MEM_MEMBERGUID,
MEMB_ID
type LOYD_MEM_MEMBERMEMB_ID,
end of ty_loyd_mem_member.


***Declare an internal table and working area with the above types:


data: tl_loyd_mem_member TYPE STANDARD TABLE OF ty_loyd_mem_member,
wa_loyd_mem_member
type ty_loyd_mem_member.

types: begin of ty_loyd_msh_mems,
GUID         
type LOYD_MSH_MEMSGUID,
MEMS_TYPE    
type LOYD_MSH_MEMSMEMS_TYPE,
STATUS       
type LOYD_MSH_MEMSSTATUS,
STATUS_REASON
type LOYD_MSH_MEMSSTATUS_REASON,
LAST_TXN_DATE
type LOYD_MSH_MEMSLAST_TXN_DATE,
end of ty_loyd_msh_mems.

data: tl_loyd_msh_mems type STANDARD TABLE OF ty_loyd_msh_mems,
wa_loyd_msh_mems
type ty_loyd_msh_mems,
t_loyalty
type table of ZOXCD00132.

types: begin of ty_loyd_ma_specattr,
REF_GUID        
type LOYD_MA_SPECATTRREF_GUID,
PROCESS_TYPE    
type LOYD_MA_SPECATTRPROCESS_TYPE,
AMOUNT          
type LOYD_MA_SPECATTRAMOUNT,
CURRENCY         type LOYD_MA_SPECATTRCURRENCY,
RETAIL_STORE_ID 
type LOYD_MA_SPECATTRRETAIL_STORE_ID,
end of ty_loyd_ma_specattr.

data: tl_loyd_ma_specattr type STANDARD TABLE OF ty_loyd_ma_specattr,
wa_loyd_ma_specattr
type ty_loyd_ma_specattr.

types: begin of ty_loyd_ma_genattr,
GUID            
type LOYD_MA_GENATTRGUID,
PARTNSHIP_EXT_ID
type LOYD_MA_GENATTRPARTNSHIP_EXT_ID,
ACTIVITY_DATE   
type LOYD_MA_GENATTRACTIVITY_DATE,
PROGRAM_GUID    
type LOYD_MA_GENATTRPROGRAM_GUID,
end of ty_loyd_ma_genattr.

data: tl_loyd_ma_genattr type STANDARD TABLE OF ty_loyd_ma_genattr,
wa_loyd_ma_genattr
type ty_loyd_ma_genattr.

types: begin of ty_cgpl_project,
GUID       
type CGPL_PROJECTGUID,
EXTERNAL_ID
type CGPL_PROJECTEXTERNAL_ID,
OBJECT_TYPE
type CGPL_PROJECTOBJECT_TYPE,
end of ty_cgpl_project.

data: tl_cgpl_project type STANDARD TABLE OF ty_cgpl_project,
wa_cgpl_project
type ty_cgpl_project.

FIELD-SYMBOLS: <fs_loyalty> type ZOXCD00132.

***Copy the incoming data into another internal table. C_T_DATA contains all the extracted records.


t_loyalty[]
= c_t_data[].

clear: tl_loyd_mem_member, tl_loyd_msh_mems.

if not c_t_data is initial.

***Read the table fields for all t_loyalty entries:


select GUID MEMB_ID
into table tl_loyd_mem_member
from LOYD_MEM_MEMBER
FOR ALL ENTRIES IN t_loyalty
where GUID = t_loyaltyMEMBER_GUID.

sort tl_loyd_mem_member by GUID.

select GUID MEMS_TYPE STATUS STATUS_REASON LAST_TXN_DATE
into table tl_loyd_msh_mems
from LOYD_MSH_MEMS
FOR ALL ENTRIES IN t_loyalty
where GUID = t_loyaltyMEMBERSHIP_GUID.

sort tl_loyd_msh_mems by GUID.

select REF_GUID PROCESS_TYPE AMOUNT CURRENCY RETAIL_STORE_ID
into table tl_loyd_ma_specattr
from LOYD_MA_SPECATTR
FOR ALL ENTRIES IN t_loyalty
where REF_GUID = t_loyaltyACTIVITY_GUID.

sort tl_loyd_ma_specattr by REF_GUID.

select GUID PARTNSHIP_EXT_ID ACTIVITY_DATE PROGRAM_GUID
into table tl_loyd_ma_genattr
FROM LOYD_MA_GENATTR
FOR ALL ENTRIES IN t_loyalty
where GUID = t_loyaltyACTIVITY_GUID.

sort tl_loyd_ma_genattr by GUID.

if not tl_loyd_ma_genattr[] is initial.

select GUID EXTERNAL_ID OBJECT_TYPE
into table tl_cgpl_project
from CGPL_PROJECT
for all entries in tl_loyd_ma_genattr
where GUID = tl_loyd_ma_genattrPROGRAM_GUID.

endif.
 

***Once the new fields are populated, associate them again to c_t_data, which will be passed on to BW system.


sort tl_cgpl_project by GUID.

loop at c_t_data ASSIGNING <fs_loyalty>.

read table tl_loyd_mem_member into wa_loyd_mem_member
with key GUID = <fs_loyalty>MEMBER_GUID BINARY SEARCH.

if sysubrc = 0.

<fs_loyalty>MEMB_ID     = wa_loyd_mem_memberMEMB_ID.

endif.

read table tl_loyd_msh_mems into wa_loyd_msh_mems
with key GUID = <fs_loyalty>MEMBERSHIP_GUID BINARY SEARCH.

if sysubrc = 0.

<fs_loyalty>MEMS_TYPE     = wa_loyd_msh_memsMEMS_TYPE.
<fs_loyalty>
STATUS        = wa_loyd_msh_memsSTATUS.
<fs_loyalty>
STATUS_REASON = wa_loyd_msh_memsSTATUS_REASON.
<fs_loyalty>
LAST_TXN_DATE = wa_loyd_msh_memsLAST_TXN_DATE.

endif.

read table tl_loyd_ma_specattr into wa_loyd_ma_specattr
with key REF_GUID = <fs_loyalty>ACTIVITY_GUID BINARY SEARCH.

if sysubrc = 0.

<fs_loyalty>PROCESS_TYPE    = wa_loyd_ma_specattrPROCESS_TYPE.
<fs_loyalty>
TICKET_NUMBER   = wa_loyd_ma_specattrTRAN_SEQ_NUM.
<fs_loyalty>
AMOUNT          = wa_loyd_ma_specattrAMOUNT.
<fs_loyalty>
CURRENCY        = wa_loyd_ma_specattrCURRENCY.
<fs_loyalty>
RETAIL_STORE_ID = wa_loyd_ma_specattrRETAIL_STORE_ID.

endif.

read table tl_loyd_ma_genattr into wa_loyd_ma_genattr
with key GUID = <fs_loyalty>ACTIVITY_GUID BINARY SEARCH.

if sysubrc = 0.

<fs_loyalty>PARTNSHIP_EXT_ID = wa_loyd_ma_genattrPARTNSHIP_EXT_ID.
<fs_loyalty>
ACTIVITY_DATE    = wa_loyd_ma_genattrACTIVITY_DATE.
<fs_loyalty>
PROGRAM_GUID     = wa_loyd_ma_genattrPROGRAM_GUID.

endif.

read table tl_cgpl_project into wa_cgpl_project
with key GUID = <fs_loyalty>PROGRAM_GUID.

if sysubrc = 0.

<fs_loyalty>PROGRAM_ID   = wa_cgpl_projectEXTERNAL_ID.
<fs_loyalty>
PROGRAM_TYPE = wa_cgpl_projectOBJECT_TYPE.

endif.

endloop.

endif.

endmethod.

Save the code. The ABAP code in the above method will be executed when we run the infopackage to load the data from the datasource.

You can now test the extraction at transaction RSA3 and see if everything is working accordingly.     


To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Pedro MorΓ³n

    I think i missed something, I have done your steps but the Badi is never call, is active and so but, when calling the extraction only a “standard” select is launched and the append  fields are always empty.??

    By other hand I dont get the need for a join on the same table by the same field.. ??

    I think it will go with different tables

    LOYD_MSH_MEMSGUID = LOYD_PT_TXN-MEMBERSHIP_GUID

    (0) 

Leave a Reply