Introduction:
MM03 is very common and frequently used T-Code in ECC for Material Master Data. All the Information of Material is present over there. There is a path where user can maintain a long text/comments for the material.
This text can be very long even more than 5000 or 10000 characters or more.
Below is the path of Long Text in ECC
MM03 => Additional Data => Basic Data Text.
The long Text can be stored in Multiple Language.
Requirement/ Issue Observed:
In our Project we had requirement to bring the Long Text from MM03 => Basic Data Text where business maintains very Long texts in the given Space.
Requirement was to bring any new changes that happens in the Long Text Field or newly created long texts as Delta in a BW Extractor.
Please Note: - The SAP standard extractor 0MATERIAL_TEXT data source does not bring this text, it brings the material description which is boxed in blue color in above screenshot.
Investigation:
When any new Long Text is created in MM03 for existing material, new material or any modification happens in existing long text of a material, the changes for that Material will be saved in STXH table with Date created (TDFDATE) and Date Changed on (TDLDATE), and the long text will be stored in STXL table in SAP compresses format.
STXH is a standard SAP Table which is used to store STXD SAPscript text file header data.
Here for our example, two entries are present for two language, German (D) and English (E). The Field ‘Number of Lines’ represent how many lines are present in the Long Text. Here its 18 lines for German Text and 13 Lines for English Text. Each line has a length of 132 char.
STXL is a standard SAP Table which is used to store STXD SAPscript text file lines data
The data is stored in SAP compressed format.
Now the question is how to read the data of STXL table which stores the Long Text?
The STXL table will be read by the SAP Standard Function Module READ_TEXT, by putting the required information from STXH table. This is Shown Below
Solution:
Keeping the requirement in mind the best solution we thought of is creating a Generic Delta Data-source which will be based on Function Module which calls the SAP Standard Function Module READ_TEXT inside. This new Function Module should be delta enabled to bring all the newly created and changes records in Basic Data Text/Long Text. (The Material for which changes happens in text, the whole text comes in the result).
As the created & changed date and time are stored in STXH table, we have created a data source with delta on Time-stamp (created and changed) from STXH table and then that value will be passed through Function Module READ_TEXT to get the Long Text from STXL in readable format.
Creating Data-source:
New Structure
Select all required fields from STXH table and insert three additional field
- ZLINE: To sequence the number of lines
- ZTEXT: To store the Long Text
- TSTAMPS: Delta Specific field for Data-source
Below are the Date and time fields present in STXH table
Date created (TDFDATE), and Time Created (TDFTIME)
Changed On (TDLDATE) and Last Changed At (TDLTIME).
Create a data-source with Delta specific field as TSTAMPS.
To get delta on both created and changed date and time, the timestamp field TSTAMPS will record any changes in created date & Time or Changed Date & Time. This logic will be written in the Function Module.
Function Module:
Below is the Code of the Function Module:-
We have copied the standard FM code for Delta enabled generic data-source model and modified according to our requirement
https://archive.sap.com/documents/docs/DOC-12232
*The below part is to get delta on created Date/Time and Changes Date/Time in UTC Timestamp “TSTAMP”
loop at s_s_if-t_select into l_s_select where fieldnm = 'TSTAMPS'.
tstamp = l_s_select-low.
convert time stamp tstamp time zone sy-zonlo
into date startdate time starttime.
l_r_date-low = startdate.
l_r_date-sign = 'I'.
l_r_date-option = 'EQ'.
clear l_r_date-high.
append l_r_date.
l_r_time-low = starttime.
l_r_time-sign = 'I'.
l_r_time-option = 'GE'.
clear l_r_time-high.
append l_r_time.
endloop.
* Select statement from table STXH with conditions
open cursor with hold s_cursor for
select (s_s_if-t_fields)
from stxh
where
tdobject in l_r_tdobject
and tdname in l_r_tdname
and tdfdate in l_r_tdfdate
and (
( tdfdate in l_r_date and tdftime in l_r_time ) or ( tdfdate > startdate )
or
( tdldate in l_r_date and tdltime in l_r_time ) or ( tdldate > startdate )
).
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 it_stxh
package size s_s_if-maxsize.
if sy-subrc <> 0.
close cursor s_cursor.
raise no_more_data.
endif.
if it_stxh[] is not initial.
unassign <fs_stxh>.
clear w_t_data.
* Populating final value in E_T_DATA
*Getting all lines of text for each Text ID, Name and Texts: application object and putting it into the READ_TEXT function Module to read from the STXL table.
loop at it_stxh assigning <fs_stxh>.
clear textheader.
clear ztext.
*data: text type ztext.
call function 'READ_TEXT'
exporting
object = <fs_stxh>-tdobject
id = <fs_stxh>-tdid
language = <fs_stxh>-tdspras
name = <fs_stxh>-tdname
importing
header = textheader
tables
lines = textlines
exceptions
id = 1
language = 2
name = 3
not_found = 4
object = 5
reference_check = 6
wrong_access_to_archive = 7
others = 8.
clear lv_lines.
loop at textlines assigning <fs_lines>.
lv_lines = lv_lines + 1.
* Concatenating date and time into timestamp
concatenate <fs_stxh>-tdfdate <fs_stxh>-tdftime into tstamp1.
concatenate <fs_stxh>-tdldate <fs_stxh>-tdltime into tstamp2.
* When changed date is not present use the created date for Delta(TSTAMPS).
if ( tstamp2 is initial or tstamp2 eq '00000000' ) .
move tstamp1 to w_t_data-tstamps.
else.
move tstamp2 to w_t_data-tstamps.
endif.
move : <fs_stxh>-mandt to w_t_data-mandt,
<fs_stxh>-tdobject to w_t_data-tdobject,
<fs_stxh>-tdname to w_t_data-tdname,
<fs_stxh>-tdid to w_t_data-tdid,
<fs_stxh>-tdspras to w_t_data-tdspras,
<fs_lines>-tdline to w_t_data-ztext,
<fs_stxh>-tdfdate to w_t_data-tdfdate,
<fs_stxh>-tdftime to w_t_data-tdftime,
<fs_stxh>-tdldate to w_t_data-tdldate,
<fs_stxh>-tdltime to w_t_data-tdltime,
<fs_stxh>-tdtxtlines to w_t_data-tdtxtlines,
lv_lines to w_t_data-zline.
append w_t_data to e_t_data.
endloop.
endloop.
endif.
Explanation of Code:
In the code part “tstamp = l_s_select-low” it will have the delta timestamp from last run. In case of Init. or Full load it will be empty and bring all the records.
This is then converted into date (startdate) and time (starttime).
Then this date and time field will use in “where” condition while extracting data from STXH table In such a way so that any change in date or time or any new creation in date or time will be captured. See select statement in the code.
The extract will be then passed one by one to the Function Module READ_TEXT to get the text from STXL table.
Result of the Extractor
The above is the result of the extractor
No. of Lines: It is from Table STXH.
ZLINE: Shows the line of the text in correct Sequence.
ZTEXT: It stores the Long Text in multiple lines, as BW Extractor field limit of 255 char. Here the length is of 132 Char length (to sync with length coming from FM READ_TEXT).
Note:
The results of text (ZTEXT) is stored in the multiple lines in datasource identifiable with the line column ZLINE (sequence). If the requirement in BW is to bring the long text in one single line then after extracting in BW you can use XXL Attributes to store this data. But XXL Attributes has a limitation 1333 length.
In our Project we had requirement to bring this extraction data into Data-Lake. We have used SAP Data Services to combine the text of multiple lines in one single line and then loaded in Data-Lake.
References:-
https://archive.sap.com/documents/docs/DOC-11764
- SAP Managed Tags:
