|
Purpose Many GuiXT applications need data from SAP customizing tables, for example to:
Traditionally, this data is read at runtime, either via RFC using the function module This section describes an alternative that requires no RFC or OData calls to the SAP system at runtime. It works as follows:
The procedure is illustrated in the following diagram:
Limitations compared to runtime access via RFC/ODatad
Advantages
An Example
We demonstrate the procedure with a small example. The GuiXT script D;WAERS;CURRENCY
D;MEINS;UNIT_OF_MEASURE
D;BUKRS;COMPANY_CODE
D;VKORG;SALES_ORGANIZATION
D;VTWEG;DISTRIBUTION_CHANNEL
D;SPART;DIVISION
D;MTART;MATERIAL_TYPE
D;KOKRS;CONTROLLING_AREA
O;sap/opu/odata/sap/API_COUNTRY_SRV;COUNTRY;
A_CountryText;Country,CountryName;Language eq 'EN'
O;sap/opu/odata/sap/API_PLANT_SRV;PLANT;
A_Plant;Plant,PlantName,CompanyCode,CompanyCodeNameA detailed description of all entries follows further below. As a first orientation:
The second OData entry also shows how data from different SAP tables can be combined: for each plant, the table The generation takes only 2–3 seconds:
The size of the generated SQLite database file is 76 KB:
We can now inspect the generated SQLite database, for example with the free tool DB Browser for SQLite. The DB Browser shows the table names, column names and the table content, a good basis for using the tables in GuiXT scripts:
Next, we upload the file In the session script, we attach this file as database "sapconfig":
Session script "E.session.txt"
We now have the basis to work with the replicated configuration tables in each script. For example, we can easily create a dropdown list for "Currency":
Curreny dropdown list"
The currencies in the dropdown list are taken directly from the local SQLite table “CURRENCY”:
Another option is to restrict the currency list to the currencies actually in use during generation. You can do this with the following entry in the control table: D;WAERS;CURRENCY;;;WAERS in ('AUD','CAD','CNY','EUR','USD') AND SPRAS = 'E' ![]() You can also include additional SAP tables in the SQLite replication, for example your own Z tables. By specifying filter conditions, you can select subsets of the data and, going beyond pure replication, generate in advance exactly the records a particular application needs. As an example, we select all customers sharing the same name into the SQL table CUSTOMERDEDUP. All it takes is the following line in the control table: T;KNA1;CUSTOMERDEDUP;;NAME1,KUNNR,ORT01,LAND1;name1 IN ( SELECT name1 FROM kna1 GROUP BY name1 HAVING COUNT(*) > 1 ) The result:
Control File
|
| Type | Source | Data is read via |
|---|---|---|
D |
SAP domain (value table of the domain) | RFC |
O |
OData service | HTTPS |
T |
SAP table | RFC |
The meaning of fields 2 to 6 depends on the type:
| Field | D – Domain | O – OData service | T – Table |
|---|---|---|---|
| 2 | Domain name required | Service path required | SAP table name required |
| 3 | SQLite table name required | SQLite table name required | SQLite table name required |
| 4 | not used, leave empty | Entity name required | not used, leave empty |
| 5 | Column list optional | Property list optional | Column list required |
| 6 | Filter, ABAP SQL syntax optional | Filter, OData v4 syntax optional | Filter, ABAP SQL syntax optional |
SQLite table names are not case-sensitive. Please observe the following rules:
A-Z, a-z, digits 0-9 and the underscore _.- . ! @ # $.TABLE, SELECT, CREATE or INDEX cannot be used as a name on their own.Generates an SQLite table from the value table of an SAP domain.
| Field | Content | |
|---|---|---|
| 2 | Name of the SAP DDIC domain in uppercase, e.g. WAERS or MTART.
If no value table is assigned to the domain in the SAP system, nothing is generated.
Use * to generate all domains (see below). |
required |
| 3 | Name of the generated SQLite table, e.g. CURRENCY. |
required |
| 4 | Currently not used, should be empty. | |
| 5 | Comma-separated list of the columns to be extracted from the SAP value table. | optional |
| 6 | Filter condition for selecting the generated entries, in ABAP SELECT syntax, e.g.WAERS in ('AUD','CAD','CNY','EUR','USD') AND SPRAS = 'E'If you specify a filter, you need to include the language key yourself. |
optional |
If you specify * as the domain name, all SAP domains for which a value table
is defined in the SAP Data Dictionary are generated. The SQLite table names are formed
automatically from the domain name with the prefix DOM_, e.g. DOM_WAERS.
No further fields are required:
D;*
In our test on an SAP S/4HANA 2023 FPS3 fully-activated appliance, generating all domains took about 30 minutes. 6,456 value tables were generated, and the resulting SQLite database had a size of 405 MB (English texts).
Generating all domains is not recommended for productive use. It can be helpful, however, when selecting the relevant domains: you can browse the contents locally with DB Browser for SQLite to decide which domains you need, and possibly which subset of values (e.g. countries, currencies).
guixt/dbselect is installed.guixt/dbselect, as well as authorization to read the respective SAP tables.Generates an SQLite table from an entity set of an OData service.
| Field | Content | |
|---|---|---|
| 2 | Path of the OData service, e.g. sap/opu/odata/sap/API_PLANT_SRV. |
required |
| 3 | Name of the generated SQLite table, e.g. PLANT. |
required |
| 4 | Name of the OData entity, e.g. A_Plant. |
required |
| 5 | Comma-separated list of the properties to be read, e.g.Plant,PlantName,CompanyCode,CompanyCodeNameSpecify the names exactly as defined in the OData service, including upper/lower case. |
optional |
| 6 | Filter condition for the generated entries, e.g. Language eq 'EN'
if the service does not automatically return only entries in the logon language.
The filter syntax is described in the GuiXT OData interface documentation (OData v4 format). |
optional |
O;sap/opu/odata/sap/API_COUNTRY_SRV;COUNTRY;
A_CountryText;Country,CountryName;Language eq 'EN'
Generates an SQLite table from the contents of an SAP table.
| Field | Content | |
|---|---|---|
| 2 | Name of the SAP table in uppercase, e.g. T179T (product hierarchy texts). |
required |
| 3 | Name of the generated SQLite table, e.g. PRODHTEXT. |
required |
| 4 | Currently not used, should be empty. | |
| 5 | Comma-separated list of the columns to be extracted from the SAP table. | required |
| 6 | Filter condition for selecting the generated entries, in ABAP SELECT syntax.
If you specify a filter, you need to include the language key yourself. |
optional |
T;T179T;PRODHTEXT;;PRODH,VTEXT;SPRAS = 'E'
guixt/dbselect is installed.guixt/dbselect, as well as authorization to read the respective SAP tables.Copy the two scripts into your script folder, adjust the location of the control file and of the SQLite database, adjust the control file and start the generation via a pushbutton, e.g.
PushButton (toolbar) "Start SQLite generation" process="sqliteGeneration.txt"