How to create Loyalty generic datasources

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.

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

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.

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.

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.

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.

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.

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 …”

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.

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

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.

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.

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.

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

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

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

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_MEMBER–GUID,
MEMB_ID type LOYD_MEM_MEMBER–MEMB_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_MEMS–GUID,
MEMS_TYPE     type LOYD_MSH_MEMS–MEMS_TYPE,
STATUS        type LOYD_MSH_MEMS–STATUS,
STATUS_REASON type LOYD_MSH_MEMS–STATUS_REASON,
LAST_TXN_DATE type LOYD_MSH_MEMS–LAST_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_SPECATTR–REF_GUID,
PROCESS_TYPE     type LOYD_MA_SPECATTR–PROCESS_TYPE,
AMOUNT           type LOYD_MA_SPECATTR–AMOUNT,
CURRENCY         type LOYD_MA_SPECATTR–CURRENCY,
RETAIL_STORE_ID  type LOYD_MA_SPECATTR–RETAIL_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_GENATTR–GUID,
PARTNSHIP_EXT_ID type LOYD_MA_GENATTR–PARTNSHIP_EXT_ID,
ACTIVITY_DATE    type LOYD_MA_GENATTR–ACTIVITY_DATE,
PROGRAM_GUID     type LOYD_MA_GENATTR–PROGRAM_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_PROJECT–GUID,
EXTERNAL_ID type CGPL_PROJECT–EXTERNAL_ID,
OBJECT_TYPE type CGPL_PROJECT–OBJECT_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_loyalty–MEMBER_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_loyalty–MEMBERSHIP_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_loyalty–ACTIVITY_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_loyalty–ACTIVITY_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_genattr–PROGRAM_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 sy–subrc = 0.

<fs_loyalty>–MEMB_ID     = wa_loyd_mem_member–MEMB_ID.

endif.

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

if sy–subrc = 0.

<fs_loyalty>–MEMS_TYPE     = wa_loyd_msh_mems–MEMS_TYPE.
<fs_loyalty>–STATUS        = wa_loyd_msh_mems–STATUS.
<fs_loyalty>–STATUS_REASON = wa_loyd_msh_mems–STATUS_REASON.
<fs_loyalty>–LAST_TXN_DATE = wa_loyd_msh_mems–LAST_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 sy–subrc = 0.

<fs_loyalty>–PROCESS_TYPE    = wa_loyd_ma_specattr–PROCESS_TYPE.
<fs_loyalty>–TICKET_NUMBER   = wa_loyd_ma_specattr–TRAN_SEQ_NUM.
<fs_loyalty>–AMOUNT          = wa_loyd_ma_specattr–AMOUNT.
<fs_loyalty>–CURRENCY        = wa_loyd_ma_specattr–CURRENCY.
<fs_loyalty>–RETAIL_STORE_ID = wa_loyd_ma_specattr–RETAIL_STORE_ID.

endif.

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

if sy–subrc = 0.

<fs_loyalty>–PARTNSHIP_EXT_ID = wa_loyd_ma_genattr–PARTNSHIP_EXT_ID.
<fs_loyalty>–ACTIVITY_DATE    = wa_loyd_ma_genattr–ACTIVITY_DATE.
<fs_loyalty>–PROGRAM_GUID     = wa_loyd_ma_genattr–PROGRAM_GUID.

endif.

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

if sy–subrc = 0.

<fs_loyalty>–PROGRAM_ID   = wa_cgpl_project–EXTERNAL_ID.
<fs_loyalty>–PROGRAM_TYPE = wa_cgpl_project–OBJECT_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.