Long text extraction via READ_TEXT FM
Reading Long text is a tough task for a BW consultant while loading to BW system. We have a SAP provided FM READ_TEXT for this purpose. The general understanding about this FM is it decrypts the text stored in various objects of SAP stored in encrypted format in tables STXH and STXL. We recently got such kind of requirement. We had to pull data for Sales Order Text.
I surfed over net, came up with a solution as there was no many document scripting the complete approach as I needed, I have used few documents as reference for this whole development as mentioned in the bibliography below.
Scenario:
Our Project is based on SAP Retail and POSDM. The Data generated by the SAP ECC is pulled further by Informatica system and it gets reported in Micro-strategy Tool.
We have implemented below modules:
- SAP FI-GL
- SAP Purchasing
- SAP Inventory Management
- SAP Sales Orders
- SAP Delivery
- SAP Billing
- SAP Agency Business
- SAP POSDM
The major part of a SAP BW consultant in our project is to handle the extractions side of SAP; it may be any standard or custom DS.
Issue:
We came across a requirement where we needed the Long text stored for multiple lines and reasons related to a Sales Order.
The requirement stated that the Informatica system needs all active line of Texts for all the Sales orders. This text is not the description. It’s the long text updated via transaction VA02, VA22 and similar. This gets updated to STXH and STXL tables and related table.
Since the text is in encrypted format, even if we create a generic extractor on these table combinations i.e. views, we will not be able to extract required texts from these tables.
SAP has provided a Standard function Module for extracting such type of texts. They are separated with text IDs, Names and Object types.
Solution applied:
We will consider below example for our explanation of the way I used for this extraction. I used a generic DS based on FM for this purpose.
Step 1: Create a structure named say ZTEST_TEXT, with below attributes and details.
Step 2: Update the enhancement category for avoiding warning and activate it.
Step 3: Copy the FM Group RSAX to make it editable to for example ZRSAX_TEXTLG
Step 4: Copy only FM RSAX_BIW_GET_DATA_SIMPLE to say ZZZZRSAX_BIW_GET_DATA_SIMPLE
Step 5: Maintain the FM ZZZZRSAX_BIW_GET_DATA_SIMPLE in original language as its base FM was in German so it will also you to maintain it in German.
Step 5.5: Activate and Include LZRSAX_TEXTLGTOP.
Step 6: Update the Code as below and as mentioned in the BOLD Font.
FUNCTION ZZZZRSAX_BIW_GET_DATA_SIMPLE.
*”———————————————————————-
*”*”Local Interface:
*” IMPORTING
*” VALUE(I_REQUNR) TYPE SRSC_S_IF_SIMPLE-REQUNR
*” VALUE(I_DSOURCE) TYPE SRSC_S_IF_SIMPLE-DSOURCE OPTIONAL
*” VALUE(I_MAXSIZE) TYPE SRSC_S_IF_SIMPLE-MAXSIZE OPTIONAL
*” VALUE(I_INITFLAG) TYPE SRSC_S_IF_SIMPLE-INITFLAG OPTIONAL
*” VALUE(I_READ_ONLY) TYPE SRSC_S_IF_SIMPLE-READONLY OPTIONAL
*” VALUE(I_REMOTE_CALL) TYPE SBIWA_FLAG DEFAULT SBIWA_C_FLAG_OFF
*” TABLES
*” I_T_SELECT TYPE SRSC_S_IF_SIMPLE-T_SELECT OPTIONAL
*” I_T_FIELDS TYPE SRSC_S_IF_SIMPLE-T_FIELDS OPTIONAL
*” E_T_DATA STRUCTURE ZTEST_TEXT OPTIONAL
*” EXCEPTIONS
*” NO_MORE_DATA
*” ERROR_PASSED_TO_MESS_HANDLER
*”———————————————————————-
* Example: DataSource for table SFLIGHT
TABLES: ZTEST_TEXT.
* Auxiliary Selection criteria structure
DATA: L_S_SELECT TYPE SRSC_S_SELECT,
I_T_DATA1 TYPe STANDARD TABLE OF ZTEST_TEXT,
lt_text_lines TYPe STANDARD TABLE OF TLINE,
lr_text_lines TYPE TLINE.
FIELD-SYMBOLS: <FS_ZDS_TEXT_ORDER> TYPE ZTEST_TEXT.
* Maximum number of lines for DB table
STATICS: S_S_IF TYPE SRSC_S_IF_SIMPLE,
* counter
S_COUNTER_DATAPAKID LIKE SY–TABIX,
* cursor
S_CURSOR TYPE CURSOR.
* Select ranges
RANGES: SDNO FOR ZTEST_TEXT–VBELN.
* Initialization mode (first call by SAPI) or data transfer mode
* (following calls) ?
IF I_INITFLAG = SBIWA_C_FLAG_ON.
************************************************************************
* Initialization: check input parameters
* buffer input parameters
* prepare data selection
************************************************************************
* Check DataSource validity
CASE I_DSOURCE.
WHEN ‘ZDS_TEXT_ORDER’.
WHEN OTHERS.
IF 1 = 2. MESSAGE E009(R3). ENDIF.
* this is a typical log call. Please write every error message like this
LOG_WRITE ‘E’ “message type
‘R3’ “message class
‘009’ “message number
I_DSOURCE “message variable 1
‘ ‘. “message variable 2
RAISE ERROR_PASSED_TO_MESS_HANDLER.
ENDCASE.
APPEND LINES OF I_T_SELECT TO S_S_IF–T_SELECT.
* Fill parameter buffer for data extraction calls
S_S_IF–REQUNR = I_REQUNR.
S_S_IF–DSOURCE = I_DSOURCE.
S_S_IF–MAXSIZE = I_MAXSIZE.
* Fill field list table for an optimized select statement
* (in case that there is no 1:1 relation between InfoSource fields
* and database table fields this may be far from beeing trivial)
APPEND LINES OF I_T_FIELDS TO S_S_IF–T_FIELDS.
ELSE. “Initialization mode or data extraction ?
************************************************************************
* Data transfer: First Call OPEN CURSOR + FETCH
* Following Calls FETCH only
************************************************************************
* First data package -> OPEN CURSOR
IF S_COUNTER_DATAPAKID = 0.
* Fill range tables BW will only pass down simple selection criteria
* of the type SIGN = ‘I’ and OPTION = ‘EQ’ or OPTION = ‘BT’.
** LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT WHERE FIELDNM = ‘CARRID’.
** MOVE-CORRESPONDING L_S_SELECT TO L_R_CARRID.
** APPEND L_R_CARRID.
** ENDLOOP.
**
** LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT WHERE FIELDNM = ‘CONNID’.
** MOVE-CORRESPONDING L_S_SELECT TO L_R_CONNID.
** APPEND L_R_CONNID.
** ENDLOOP.
LOOP AT S_S_IF–T_SELECT INTO L_S_SELECT WHERE FIELDNM = ‘VBELN’.
MOVE-CORRESPONDING L_S_SELECT TO SDNO.
APPEND SDNO.
ENDLOOP.
* Determine number of database records to be read per FETCH statement
* from input parameter I_MAXSIZE. If there is a one to one relation
* between DataSource table lines and database entries, this is trivial.
* In other cases, it may be impossible and some estimated value has to
* be determined.
SELECT MANDT TDNAME TDSPRAS TDID
FROM STXL
into table I_T_DATA1
where TDOBJECT = ‘VBBK’.
LOOP AT I_T_DATA1 ASSIGNING <FS_ZDS_TEXT_ORDER>.
CALL FUNCTION ‘READ_TEXT’
EXPORTING
CLIENT = <FS_ZDS_TEXT_ORDER>–MANDT
ID = <FS_ZDS_TEXT_ORDER>–TDID
LANGUAGE = <FS_ZDS_TEXT_ORDER>–TDSPRAS
NAME = <FS_ZDS_TEXT_ORDER>–VBELN
OBJECT = ‘VBBK’
TABLES
LINES = lt_text_lines
EXCEPTIONS
ID = 1
LANGUAGE = 2
NAME = 3
NOT_FOUND = 4
OBJECT = 5
REFERENCE_CHECK = 6
WRONG_ACCESS_TO_ARCHIVE = 7
OTHERS = 8.
READ TABLE lt_text_lines INTO lr_text_lines INDEX 1.
<FS_ZDS_TEXT_ORDER>–TXTLG = lr_text_lines–TDLINE(132).
ENDLOOP.
SORT I_T_DATA1.
E_T_DATA[] = I_T_DATA1[] .
* OPEN CURSOR WITH HOLD S_CURSOR FOR
*** SELECT (S_S_IF-T_FIELDS) FROM SFLIGHT
*** WHERE CARRID IN L_R_CARRID AND
*** CONNID IN L_R_CONNID.
ENDIF. “First data package ?
*
** Fetch records into interface table.
** named E_T_’Name of extract structure’.
* FETCH NEXT CURSOR S_CURSOR
* APPENDING CORRESPONDING FIELDS
* OF TABLE E_T_DATA
* PACKAGE SIZE S_S_IF-MAXSIZE.
**
* IF SY-SUBRC <> 0.
* RAISE NO_MORE_DATA.
* ENDIF.
*
*
S_COUNTER_DATAPAKID = S_COUNTER_DATAPAKID + 1.
ENDIF.
“Initialization mode or data extraction ?
ENDFUNCTION.
Step 7: Create a DS with name based on FM as below ZDS_TEXT_ORDER (mentioned in the code.
Step 8: Open RSA3 for DS ZDS_TEXT_ORDER along with its selection or you can also keep it blank and hit on F8 or extraction.
Below is the result:
Documents used as a reference:
Hi,
this post is very good and I have implemented this datasource. The problem is that although it works from the RSA3, doesn’t run from BW side. The “RAISE no_more_data” is missing so the datasource never finish in BW side.
below my code that is running ok (in my case, text from SAP CRM tables):
*"*"Interfase local
*" IMPORTING
*" VALUE(I_REQUNR) TYPE SRSC_S_IF_SIMPLE-REQUNR
*" VALUE(I_DSOURCE) TYPE SRSC_S_IF_SIMPLE-DSOURCE OPTIONAL
*" VALUE(I_MAXSIZE) TYPE SRSC_S_IF_SIMPLE-MAXSIZE OPTIONAL
*" VALUE(I_INITFLAG) TYPE SRSC_S_IF_SIMPLE-INITFLAG OPTIONAL
*" VALUE(I_READ_ONLY) TYPE SRSC_S_IF_SIMPLE-READONLY OPTIONAL
*" VALUE(I_REMOTE_CALL) TYPE SBIWA_FLAG DEFAULT SBIWA_C_FLAG_OFF
*" TABLES
*" I_T_SELECT TYPE SRSC_S_IF_SIMPLE-T_SELECT OPTIONAL
*" I_T_FIELDS TYPE SRSC_S_IF_SIMPLE-T_FIELDS OPTIONAL
*" E_T_DATA OPTIONAL
*" E_T_SOURCE_STRUCTURE_NAME OPTIONAL
*" EXCEPTIONS
*" NO_MORE_DATA
*" ERROR_PASSED_TO_MESS_HANDLER
*"----------------------------------------------------------------------
* TABLES: zorderh_text.
* Auxiliary Selection criteria structure
DATA: l_s_select TYPE srsc_s_select,
i_t_data1 TYPE STANDARD TABLE OF zorderh_text,
lt_text_lines TYPE STANDARD TABLE OF tline,
lr_text_lines TYPE tline.
FIELD-SYMBOLS: <fs_zds_text_orderadm> TYPE zorderh_text.
* Maximum number of lines for DB table
STATICS: s_s_if TYPE srsc_s_if_simple,
* counter
s_counter_datapakid LIKE sy-tabix,
* cursor
s_cursor TYPE cursor.
* Select ranges
RANGES: sdno FOR zorderh_text-guid.
* Initialization mode (first call by SAPI) or data transfer mode
* (following calls) ?
IF i_initflag = sbiwa_c_flag_on.
************************************************************************
* Initialization: check input parameters
* buffer input parameters
* prepare data selection
************************************************************************
* Check DataSource validity
CASE i_dsource.
WHEN 'ZDS_TEXT_ORDERADM'.
WHEN OTHERS.
IF 1 = 2. MESSAGE e009(r3). ENDIF.
* this is a typical log call. Please write every error message like this
log_write 'E' "message type
'R3' "message class
'009' "message number
i_dsource "message variable 1
' '. "message variable 2
RAISE error_passed_to_mess_handler.
ENDCASE.
APPEND LINES OF i_t_select TO s_s_if-t_select.
* Fill parameter buffer for data extraction calls
s_s_if-requnr = i_requnr.
s_s_if-dsource = i_dsource.
s_s_if-maxsize = i_maxsize.
* Fill field list table for an optimized select statement
* (in case that there is no 1:1 relation between InfoSource fields
* and database table fields this may be far from beeing trivial)
APPEND LINES OF i_t_fields TO s_s_if-t_fields.
ELSE. "Initialization mode or data extraction ?
************************************************************************
* Data transfer: First Call OPEN CURSOR + FETCH
* Following Calls FETCH only
************************************************************************
* First data package -> OPEN CURSOR
IF s_counter_datapakid = 0.
* Fill range tables BW will only pass down simple selection criteria
* of the type SIGN = 'I' and OPTION = 'EQ' or OPTION = 'BT'.
" LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT WHERE FIELDNM = 'CARRID'.
" MOVE-CORRESPONDING L_S_SELECT TO L_R_CARRID.
" APPEND L_R_CARRID.
" ENDLOOP.
" LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT WHERE FIELDNM = 'CONNID'.
" MOVE-CORRESPONDING L_S_SELECT TO L_R_CONNID.
" APPEND L_R_CONNID.
" ENDLOOP.
* Determine number of database records to be read per FETCH statement
* from input parameter I_MAXSIZE. If there is a one to one relation
* between DataSource table lines and database entries, this is trivial.
* In other cases, it may be impossible and some estimated value has to
* be determined.
LOOP AT g_t_select INTO l_s_select WHERE fieldnm = 'GUID'.
MOVE-CORRESPONDING l_s_select TO sdno.
APPEND sdno.
ENDLOOP.
OPEN CURSOR WITH HOLD s_cursor FOR
SELECT mandt tdname AS guid tdspras tdid
FROM stxl
WHERE tdobject = 'CRM_ORDERH'.
ENDIF. "First data package ?
* Fetch records into interface table.
* named E_T_'Name of extract structure'.
FETCH NEXT CURSOR s_cursor
APPENDING CORRESPONDING FIELDS
OF TABLE i_T_DATA1
PACKAGE SIZE S_S_IF-MAXSIZE.
IF sy-subrc <> 0.
CLOSE CURSOR s_cursor.
RAISE no_more_data.
ENDIF.
LOOP AT i_t_data1 ASSIGNING <fs_zds_text_orderadm>.
CALL FUNCTION 'READ_TEXT'
EXPORTING
client = <fs_zds_text_orderadm>-mandt
id = <fs_zds_text_orderadm>-tdid
language = <fs_zds_text_orderadm>-tdspras
name = <fs_zds_text_orderadm>-guid
object = 'CRM_ORDERH'
TABLES
lines = lt_text_lines
EXCEPTIONS
id = 1
language = 2
name = 3
not_found = 4
object = 5
reference_check = 6
wrong_access_to_archive = 7
OTHERS = 8.
READ TABLE lt_text_lines INTO lr_text_lines INDEX 1.
<fs_zds_text_orderadm>-txtlg = lr_text_lines-tdline(132).
ENDLOOP.
SORT i_t_data1.
e_t_data[] = i_t_data1[] .
s_counter_datapakid = s_counter_datapakid + 1.
ENDIF. "Initialization mode or data extraction ?
ENDFUNCTION.
Dear Both,
This is a very useful and informative doc that i came across. Thanks a lot for sharing.
It helped me and i could complete my development in less time.
Regards,
Sujay D.