Tips & Tricks - Update Tables Directly, parse ATTR-strings
The LU IntfaceTableMigrations is designed to manipulate tables directly. By standard, it is only tables from Sales&Marketing or IC-tables (created by FNDMIG) that can be accessed. This can be changed by customizing the package, but it is recommended not to allow access on tables that belong to a Business Logic LU.
Keep rows on IC-table
In this case, we want to keep all loaded data in the IC-table, and we want to keep track of whether the data has been processed or not.
- We add a status-column with a default-value. (Making it a Key column will help on performance).
- We configure Rule CRETABCONF to keep all loaded data in the IC- table.
The procedure we use for table manipulation, is Intface_Table_Migrations_API.Process_Table( attr_). The attr-string to this procedure may contain some predefined items as listed here:
Optional. This item can hold value INSERT or UPDATE. If no value is added, the procedure first try to make a Insert, and if this fails due to DUP_VAL_ON_INDEX, a update will be performed.
Optional. Specify table owner if it is different from the user executing the job.
Mandatory. Table to be updated.
Optional. You might manipulate a table in a remote database.
Optional. If the date-format on the attr-string is different from IFS standard (Client_SYS.date_format_).
Mandatory for operation UPDATE. Enter name of key-column(s). If concatenated key, separate with semicolon. The keys are used to build the WHERE-clause of the dynamic UPDATE-statement
In addition to these items, you add the key-column(s) + the columns you want to insert/update (see below).
Configuration of Job
Add a last method to the Method List. All you have to do, is specify View Name INTFACE_TABLE_MIGRATIONS, and save; Data Migration will find correct Method Name and add the predefined items to Method List Attributes.
After the method is inserted, we update the attributes from Method List attributes.
- Add necessary values to the pre-defined items.
- Insert new items, i.e. key column (in this case we use ROWID) + the column we want to UPDATE.
Now, the rows we manually added to the Method List Attributes above, was also added to the Source Mapping folder, and we may connect them to values from our IC-table, or specify default-values.
Parsing Attr-strings to Procedures
Now let's look closer to what happens when we specify a procedure with an Attr-string as IN-parameter.
- The parameters of the procedure are listed in Method List Attributes, and we can see their specification in column Description.
- If we add items to the Method List Attributes, and map values to them, they will be packed as an Attr-string, and automatically parsed to the parameter named ATTR_ In this way we are free to compose any attr-string and pass it on to the procedure.
- Sometimes the parameter is named something else than ATTR_ (w_attr_, in_attr_, f.ex.) or the procedure may have more than one attr-strings as input.. In this case we add the text ATTR_ to Fixed Value to indicate where we want to parse our attribute string. (Of course not needed in this example)
Now, as we execute the job, each row on the IC-table will be updated with ROW_STATUS = Ready. Before starting the job, we add a WHERE-clause to the header, to process only new rows in the IC-table.