Synthesize export files for FM_SETS_FIPEX1 using LSMW
Groups are a cumbersome object to load as there is no program to be called from LSMW for direct input or for creating BDCs. The program itself relies on objects that fall outside the normal access of BDCs and the hierarchical format of the data makes loading them with a batch all but impossible even if there were an easy way.
The FM_SETS_FIPEX1 comes with export and import functions and the same functions work for FM_SETS_FUND1, FM_SETS_FICTR1, FM_SETS_FUNDPRG1 and FM_SETS_FUNCTION1. The export files contain the information that denotes what type of group has been extracted and that can be adjusted as well.
These import and export functions would work perfectly if the source data were coming from a similar SAP system. More likely, the source data comes from a decades-old legacy system passed through a query into a format that is in no way related to the data SAP expects to import.
Like any software it is possible to reverse-engineer an extract file and to write new software that will synthesize an extract file based on the source data. The easiest way to start is by creating some dummy data in the system and then exporting it to create a sample file.
The exported data
R702 user: JKAUFFMANN date: 20121207 time: 170514 client: 100 host: unsapd1e sys-id: D1E op-sys: Linux db-sys: ORACLE dc-sys: HSTSO 03111000LEVELAA1 STSHD 03111000LEVELAA1 BFMIT RFIPEX X JKAUFFMANN E T800Y Elevel aa first instance E HSTSO 03111000LEVELAA2 STSHD 03111000LEVELAA2 BFMIT RFIPEX X JKAUFFMANN E STSBS 17007610 17007610 000000000100000 STSBS 17022010 17022010 000000000200000 T800Y Elevel aa second instance E HSTSO 03111000LEVELA1 STSHD 03111000LEVELA1 SFMIT RFIPEX X JKAUFFMANN E STSSG 03111000LEVELAA1 0000000001 STSSG 03111000LEVELAA2 0000000002 T800Y ELevel A first instance E HSTSO 03111000LEVALA2 STSHD 03111000LEVALA2 BFMIT RFIPEX X JXAUFFMANN E STSBS 69121010S 69121010S 000000000100000 STSBS 69131020S 69131020S 000000000200000 STSBS 69131030S 69131030S 000000000300000 T800Y Eleval a second instance E HSTSO 03111000TESTJIM STSHD 03111000TESTJIM SFMIT RFIPEX X JKAUFFMANN JKAUFFMANN E STSBS 11001000 11001000 000000000200000 STSSG 03111000LEVELA1 0000000003 STSSG 03111000LEVALA2 0000000004 T800Y ETest of data on all levels. E X |
The exported data consists of a flat representation of hierarchical data. Each line is 255 characters long and there are no delimiters, so it's safe to assume that the format is offset-length. This means that SAP knows, based on the type of record contained in that line, where each field begins and how long the field is.
In addition to understanding how the file is formatted it is important to understand how SAP stores the data in tables. Comparing the exported data to the hierarchical dummy data gives some clues as to how the records are organized, but not what all of the fields in the data mean. Comparing the exported sample data to the data stored in SAP tables provides the remaining clues necessary to reverse engineer the data.
The tables used to store the data are the same as those used by SAP to store sets. They are SETHEADER, SETNODE and SETLEAF. By looking for the sample data in those tables it is possible to see how the data is stored in SAP. With a little effort the connection between the exported data and the SAP tables can be intuited.
The subgroups are stored in SAP the same way that groups are. Each has a name and a description that is stored in the SETHEADER table. Additionally the type of group, the table and the field are stored in this table and these are shown in the export file just once at the top of the file. Each group is stored in the export file in the HSTSO lines. The contents of the group are stored between the HSTSO lines and the E line that follows.
The type of group is also stored in the SETHEADER table. From the sample export file it can be intuited that the Basic groups have no subgroups and the Single-dimension groups have subgroups.
The SETNODE table holds the relationships between groups and subgroups. These hierarchical relationships are stored similarly in the export file in the STSSG lines and therein the relationship between the table and the export file can be seen.
The SETLEAF table holds the items that are in a group. These values are stored in the STSBS lines of the export file in a 1 to 1 relationship. The export file--just like the table--has value from and value to ranges with a single value. The FM_SETS_FIPEX1 transaction does not support ranges, but the items are stored in the form of ranges with a single value.
Handling the source data
The ABAP code to handle this mostly recycles the existing tables to create the internal tables used to store the data until it can be exported. After the parameters and their descriptions are declared the variables for storing the source data between the time it is read and then transformed into its hierarchical structure. These are simple internal tables based on the tables within SAP that are used to store Sets and Groups.
data: l_SCLASS like SETHEADER-SETCLASS, l_TNAME like SETHEADER-TABNAME, l_FNAME like SETHEADER-FIELDNAME. data: it_SETHEADER type standard table of SETHEADER initial size 0, lSETHEADER like SETHEADER, cFilename type string. data: isAdded(1) type c, nLineID type i, cIsExported(1) type c. data: wa_SETLEAF like SETLEAF, it_SETLEAF type standard table of SETLEAF initial size 0, wa_SETNODE like SETNODE, wa_SETNODE1 like SETNODE, wa_SETNODE2 like SETNODE, wa_SETNODE3 like SETNODE, wa_SETNODE4 like SETNODE, wa_SETNODE5 like SETNODE, it_SETNODE type standard table of SETNODE initial size 0, wa_SETHEADERT like SETHEADERT, it_SETHEADERT type standard table of SETHEADERT initial size 0. types: begin of exportfile, DataLine(498) type c, end of exportfile. data: wa_ExportFile type exportfile, it_ExportFile type standard table of exportfile initial size 0.
The source data is assumed to come in the form of a single, non-relational flat file with the headers shown below. In this format Groups and Subgroups point to their child Subgroups and the hierarchical form of the data is built that way. The last field is a flag that determines if the record is an item in the Group or a Subgroup.
Source Fields
GROUPS Source
SUBCLASS C(004) FM Area
ITEM C(030) Item
DESCRIPTION C(040) Description
PARENT C(030) Parent
ISGROUP C(001) Is a Group?
The Begin of Processing area holds the ABAP code that determines what type of Group is to be loaded. A set of radio buttons is used to make the determination when performing the conversion step.
if r_group1 eq 'X'. "Commitment group. write: / '*** Processing data for Commitment Group. ***' color 7. l_SCLASS = '0311'. l_TNAME = 'FMIT'. l_FNAME = 'RFIPEX'. elseif r_group2 eq 'X'. write: / '*** Processing data for Funded Program Group. ***' color 7. l_SCLASS = '0315'. l_TNAME = 'FMIT'. l_FNAME = 'RMEASURE'. elseif r_group3 eq 'X'. write: / '*** Processing data for Funds Center Group. ***' color 7. l_SCLASS = '0312'. l_TNAME = 'FMIT'. l_FNAME = 'RFISTL'. else. write: / 'No Group type has been selected for processing.' color 6. write: / 'Please select a Group type on previous screen. ' color 6. exit. endif.
The LSMW object creates a BDC with an entry for each synthetic export file created. Each Group, including all of its Subgroups is contained in a single synthetic export file.
The source data that was used as a template had the Subgroups listed first. Based on this the tool was developed to trigger the creation of the BDC entry on the first non-Subgroup entry (the first Item in the Group). This ensures that just one BDC entry is created for each Group. The export file is synthesized during the End of Processing step.
translate GROUPS-ITEM to UPPER CASE.
translate GROUPS-PARENT to UPPER CASE.
if GROUPS-ISGROUP eq 'X' and p_Ignore ne 'X'.
perform CheckName changing GROUPS-ITEM.
perform CheckName changing GROUPS-PARENT.
endif.
if GROUPS-ISGROUP eq 'X'. "HEADER!
perform AddDescription using GROUPS-ITEM GROUPS-DESCRIPTION.
if GROUPS-PARENT eq ''.
perform AddHeader.
concatenate p_folder '\' lSETHEADER-SETNAME '.txt'
into SETS-FILENAME.
replace '/' with '_' into SETS-FILENAME.
else. "Subgroup...
perform RemoveHeader. "This is a child group not a header.
perform AddNode using GROUPS-PARENT GROUPS-ITEM.
endif.
else. "Item...
perform AddLeaf using GROUPS-PARENT GROUPS-ITEM.
endif.
if cIsExported ne 'X' and GROUPS-ISGROUP ne 'X'.
"This loop is performed after the headers are ratified.
cIsExported = 'X'.
loop at it_SETHEADER into lSETHEADER.
concatenate p_folder '\' lSETHEADER-SETNAME '.txt'
into SETS-FILENAME.
replace '/' with '_' into SETS-FILENAME.
transfer_record. transfer_transaction.
endloop.
endif.
skip_transaction. "Skip every transaction process at end.
Knitting the source data into a hierarchical format and creating an export file is now a simple matter of aligning the data based on the supposed structure. A series of nested loops ensures that the structure can go as far as seven levels deep. More levels can be accommodated with minor adjustments to the source code in the End of Processing area.
if cIsExported ne 'X'. "Did not encouter an Item record...
uline. write: 'The source data has no Item records.' color 6,
'No transactions were created. Check source file.' color 6.
uline.
endif.
loop at it_SETHEADER into lSETHEADER.
clear wa_ExportFile.
wa_Exportfile-Dataline =
'R702 Import file creation tool written by Jim Kauffman'.
append wa_Exportfile to it_Exportfile.
loop at it_SETNODE into wa_SETNODE
where SETNAME eq lSETHEADER-SETNAME.
loop at it_SETNODE into wa_SETNODE1
where SETNAME eq wa_SETNODE-SUBSETNAME.
loop at it_SETNODE into wa_SETNODE2
where SETNAME eq wa_SETNODE1-SUBSETNAME.
loop at it_SETNODE into wa_SETNODE3
where SETNAME eq wa_SETNODE2-SUBSETNAME.
loop at it_SETNODE into wa_SETNODE4
where SETNAME eq wa_SETNODE3-SUBSETNAME.
loop at it_SETNODE into wa_SETNODE5
where SETNAME eq wa_SETNODE4-SUBSETNAME.
perform ShowNode using wa_SETNODE5-subSETNAME.
endloop.
perform ShowNode using wa_SETNODE4-subSETNAME.
endloop.
perform ShowNode using wa_SETNODE3-subSETNAME.
endloop.
perform ShowNode using wa_SETNODE2-subSETNAME.
endloop.
perform ShowNode using wa_SETNODE1-subSETNAME.
endloop.
perform ShowNode using wa_SETNODE-subSETNAME.
endloop.
perform ShowNode using lSETHEADER-SETNAME.
wa_Exportfile-Dataline = 'X'.
append wa_Exportfile to it_Exportfile.
The final step is to export the it_Exportfile internal table to a text file that matches the file name and path in the BDC entry. The ever-useful GUI_DOWNLOAD function comes into play.
concatenate p_Folder '\' lSETHEADER-SETNAME '.txt' into cFileName.
replace '/' with '_' into cFilename.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
FILENAME = cFileName
FILETYPE = 'ASC'
APPEND = ''
WRITE_FIELD_SEPARATOR = 'X'
HEADER = '00'
TRUNC_TRAILING_BLANKS = 'X'
TABLES
DATA_TAB = it_ExportFile
EXCEPTIONS
FILE_WRITE_ERROR = 1
OTHERS = 2.
A handful of forms populate the internal tables with the source data from the legacy system. These are included in the LSMW object at the bottom of the page.
form AddHeader.
data: lvIsAdded(1) type c.
lvIsAdded = ''.
loop at it_SETHEADER into lSETHEADER where SETNAME eq GROUPS-ITEM.
lvIsAdded = 'X'.
skip_transaction. "Don't want to import this file more than once.
endloop.
lSETHEADER-SETNAME = GROUPS-ITEM.
lSETHEADER-SETCLASS = l_SCLASS.
lSETHEADER-SUBCLASS = GROUPS-SUBCLASS.
lSETHEADER-RVALUE = GROUPS-DESCRIPTION.
lSETHEADER-SETTYPE = 'S'.
lSETHEADER-TABNAME = l_TNAME.
lSETHEADER-FIELDNAME = l_FNAME.
if lvIsAdded ne 'X'.
append lSETHEADER to it_SETHEADER.
endif.
endform.
form RemoveHeader.
"This function removes headers for subgroups.
delete it_SETHEADER where SETNAME eq GROUPS-ITEM.
endform.
form AddDescription using lvSETNAME lvDESCRIPT.
wa_SETHEADERT-SETNAME = lvSETNAME.
wa_SETHEADERT-DESCRIPT = lvDESCRIPT.
append wa_SETHEADERT to it_SETHEADERT.
endform.
form AddNode using lvSETNAME lvSUBSETNAME.
data: lvIsAdded(1) type c.
lvIsAdded = ''.
loop at it_SETNODE into wa_SETNODE WHERE SUBSETNAME eq lvSUBSETNAME.
lvIsAdded = 'X'.
endloop.
if lvIsAdded ne 'X'.
wa_SETNODE-SETNAME = lvSETNAME.
wa_SETNODE-SUBSETNAME = lvSUBSETNAME.
append wa_SETNODE to it_SETNODE.
endif.
endform.
Form AddLeaf using lvSETNAME lvVFrom lvVTo.
data: lvLINEID type i.
lvLINEID = 1.
loop at it_SETLEAF into wa_SETLEAF where SETNAME eq lvSETNAME.
add 1 to lvLINEID.
endloop.
wa_SETLEAF-SETNAME = lvSETNAME.
wa_SETLEAF-VALFROM = lvVFROM.
if lvVTO ne ''.
wa_SETLEAF-VALTO = lvVTO.
else.
wa_SETLEAF-VALTO = lvVFROM.
endif.
wa_SETLEAF-LINEID = lvLINEID.
append wa_SETLEAF to it_SETLEAF.
endform.
form ShowNode using lvSETNAME.
data: lvLineID type i, lvcLineID(10) type c, lv_SETNODE like SETNODE,
lv_SETLEAF like SETLEAF,
lvSETTYPE like SETHEADER-SETTYPE, lvDATALINE(498) type c.
lvSETTYPE = 'B'. "Basic set
loop at it_SETNODE into lv_SETNODE where SETNAME eq lvSETNAME.
lvSETTYPE = 'S'. "Single Dimensional (with sub-groups)
endloop.
* concatenate lSETHEADER-SETCLASS lSETHEADER-SUBCLASS lvSETNAME
* into lvDATALINE.
* shift lvDATALINE by 7 places right.
lvDATALINE = ''.
lvDATALINE+0(5) = 'HSTSO'.
lvDATALINE+7(4) = lSETHEADER-SETCLASS.
lvDATALINE+11(4) = lSETHEADER-SUBCLASS.
lvDATALINE+15(20) = lvSETNAME.
* write: / lvDATALINE.
wa_Exportfile-Dataline = lvDATALINE.
append wa_Exportfile to it_Exportfile.
lvDATALINE = ''.
lvDATALINE+0(6) = ' STSHD'. "Header
lvDATALINE+0(6) = ' STSHD'. "Header
lvDATALINE+7(4) = lSETHEADER-SETCLASS.
lvDATALINE+11(4) = lSETHEADER-SUBCLASS.
lvDATALINE+15(20) = lvSETNAME.
lvDataLine+41(1) = lvSETTYPE.
lvDATALINE+42(30) = lSETHEADER-TABNAME.
lvDATALINE+72(30) = lSETHEADER-FIELDNAME.
lvDATALINE+150(1) = 'X'. "Probably the "Unique Values" flag.
* write: / lvDataline.
wa_Exportfile-Dataline = lvDATALINE.
append wa_Exportfile to it_Exportfile.
lvLineID = 0.
* if lvSETNAME eq lSETHEADER-SETNAME. "Starts at 2. Who knows why?
* lvLineID = 1.
* endif.
loop at it_SETLEAF into lv_SETLEAF where SETNAME eq lvSETNAME.
add 1 to lvLineID.
lvcLineID = lvLineID.
shift lvcLineID right deleting trailing space.
overlay lvcLineID with '0000000000'.
lvDATALINE = ''.
lvDATALINE+0(6) = ' STSBS'. "Items
lvDATALINE+7(20) = lv_SETLEAF-VALFROM.
lvDATALINE+47(20) = lv_SETLEAF-VALTO.
lvDATALINE+99(10) = lvcLineID.
lvDATALINE+109(4) = '0000'. "Unknown variable.
* write: / lvDATALINE.
wa_Exportfile-Dataline = lvDATALINE.
append wa_Exportfile to it_Exportfile.
endloop.
loop at it_SETNODE into lv_SETNODE where SETNAME eq lvSETNAME.
add 1 to lvLineID.
lvcLineID = lvLineID.
shift lvcLineID right deleting trailing space.
overlay lvcLineID with '0000000000'.
lvDATALINE = ''.
lvDATALINE+0(6) = ' STSSG'. "Subgroup
lvDATALINE+7(4) = lSETHEADER-SETCLASS.
lvDATALINE+11(4) = lSETHEADER-SUBCLASS.
lvDATALINE+15(20) = lv_SETNODE-SUBSETNAME.
lvDATALINE+52(10) = lvcLineID.
* write: / lvDATALINE.
wa_Exportfile-Dataline = lvDATALINE.
append wa_Exportfile to it_Exportfile.
endloop.
lvDATALINE = ''.
lvDATALINE+0(6) = ' T800Y'.
lvDATALINE+7(1) = 'E'. "English
loop at it_SETHEADERT into wa_SETHEADERT where SETNAME eq lvSETNAME.
lvDATALINE+8(40) = wa_SETHEADERT-DESCRIPT. "lvDescription.
endloop.
wa_Exportfile-Dataline = lvDATALINE.
append wa_Exportfile to it_Exportfile.
lvDATALINE = 'E'. "English
wa_Exportfile-Dataline = lvDATALINE.
append wa_Exportfile to it_Exportfile.
write: / lvSETNAME.
endform.
form CheckName changing lvName.
data: lvLength type i.
lvLength = strlen( lvName ).
if lvLength gt 10.
Write: /
'This group name must be reduced to 10 characters or fewer:',
lvName color col_negative.
cIsExported = 'X'. "Don't export anything!
endif.
endform.
Download this project
The LSMW object in its entirety, along with the recording, is available below. No additional LSMW objects or source code is required to make this work.
LSMW_GroupLoader.txt
