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.

Contents

• Keep rows on IC-table
• Package Description
• Execution

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.

1. We add a status-column with a default-value. (Making it a Key column will help on performance).
2. We configure Rule CRETABCONF to keep all loaded data in the IC- table.

Package Description

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 :

• DM_OPERATION
  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.
• DM_TABLE_OWNER
  Optional. Specify table owner if it is different from the user executing the job.
• DM_TABLE_NAME
  Mandatory. Table to be updated.
• DM_DB_LINK
  Optional. You might manipulate a table in a remote database.
• DM_DATE_FORMAT
  Optional. If the date-format on the attr-string is different from IFS standard (Client_SYS.date_format_).
• DM_KEY_COLUMNS
  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 RMB.

• 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.

1. The parameters of the procedure are listed in Method List Attributes, and we can see their specification in column Description.
2. 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.
   .
    
3. 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)

Execution

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.