To update transparent database tables, ‘create entries’ option in transaction SE11, or creating and using a table maintenance generator for tables, or creating custom programs could be used. However, using ‘create entries’ or maintenance generator, are ineffective techniques when the number of entries is large; and though creation of custom programs results in faster validating and updating, a single program generally caters to one or some tables, therefore are inflexible.
Our business client supplied agile requirements, thus requiring us to constantly create or update custom tables, which led to a substantial time being invested in maintaining such tables or creating/modifying custom programs made for updating large number of entries in tables. Hence, there was a necessity to provide a solution to this agile framework where tables of varying structures would be easily updated.
A generic program allowing validation and maintenance of table of any structure would have 4 essential components:
- Creation of dynamic internal table at run time utilizing the structure of user defined table name.
- Generating a template based on the table structure.
- Fetching the domains for every filed in table, determining if it contains a value table or fixed values maintained, and validating all records from fetched data.
- Calculating the field-address (row and column number) of the data elements that fail validation and reporting the same.
1. Dynamic Internal Structures
To achieve a dynamic structure that assumes a definite shape at run-time, use of generic field-symbols of type ‘standard tables’ for internal tables and type ‘any’ for work areas.
CREATE DATA lr_table TYPE STANDARD TABLE of (p_tname).
ASSIGN lr_table TO <fs_table>.
2. Generating Template
Step 1: Create an internal table (say table B) with each field being of type string, and having the same number of fields as that of the user specified table, using the same dynamic table creation process as described above. Let the field names be numeric values (1, 2, 3…).
Step 2: Loop through the table containing field level information of table A. Within the loop assign the corresponding fields to a field symbol. Populate this field symbol with the name of the field of table A. Hence, once the loop is complete, all the fields of a single work area contain the names of the fields of table A.
LOOP AT dd_fields INTO ls_ddfields.
ASSIGN COMPONENT sy-tabix OF STRUCTURE <fs_area2> TO <fs_value>.
<fs_value> = ls_ddfields–fieldname.
APPEND <fs_area2> TO <fs_table2>.
Step 3: Append this work area to table B and download it on the path specified by user.
3. Validating Data
Step 1: Fetch the domain level information for the domains of all fields of the table to be updated. This could be achieved by using standard SAP functionalities. For instance, function module DD_DOMA_GET or standard tables like DD01L and DD07L could directly be used.
Step 2: Loop through every field of the table, and fetch its domain level information into work area. Check if a value table exists for the domain. If a value table exists, create dynamic internal table for the value table.
Step 3: Read through the structure of the value table to fetch for the first field whose domain is the same domain whose value table is referred. This field would contain the check values. Fetch data from value table into an internal table. Loop through all the records uploaded by the user, and validate the field value against the value table values.
Step 4: If no value table exists for the domain, fetch the fixed values checks maintained for the domain through table DD07L. If no values are fetched, it implies that the domain does not have a restriction on the data it takes. If entries are fetched, loop through the records uploaded by the user, and validate the field against the fetched values.
4. Reporting Validation Fail or Updating Table
Step 1: While updating entries from an excel sheet, a user could be prompted with the cell numbers of the data that failed validation. Since the rows in excel are numeric while columns are alphabetical, reporting the cell column and row in the same format eases in identifying the fail.
The row count can be achieved by calculating the iteration while looping through uploaded data. For generating the column name, following can be used:
DATA: lv_string TYPE string,
lv_col TYPE c,
m TYPE i.
lv_string = sy–abcde.
IF n < 26. “n is the iteration number while loooping on the records uploaded
lv_col = lv_string+n(1).
lv_alpha = lv_col.
m = n DIV 26.
m = m – 1.
lv_col = lv_string+m(1).
lv_alpha = lv_col.
m = n MOD 26.
lv_col = lv_string+m(1).
CONCATENATE lv_alpha lv_col INTO lv_alpha.