Tuesday, January 28, 2014

Populating column with "Expr: 'RowIdToRowIdNum ([Id])'" expression in the pre-default value property of field

Sometimes the requirement is to populate columns having the expression 'Expr: 'RowIdToRowIdNum ([Id])' in the pre-default property of the field. There are few columns in siebel where the field is pre-defaulted to this expression. 
For example, SR_NUM column of S_SRV_REQ table (Service Request table), OFFER_NUM column of S_DMND_CRTN_PRG table, MTRX_RULE_NUM column of S_STDPROD_PMTRX table etc.

Let us first understand the purpose of the Expression "RowIdToRowIdNum ([Id])":

1. Pre-default value always gets evaluated whenever a new record in created from UI. While importing records through EIM, the pre-default value is ignored.

2. As the expression itself indicates, it evaluates the value of the column by converting the value of ROW_ID column into some numeric value. This conversion algorithm/formula (ROW_ID value to numeric with a hyphen '-' in-between) is internal to siebel.

3. There are very few fields in siebel which have this expression in the pre-default property and most of them are not significant. Hence the requirement of populating these columns is also very few.

4. Moreover these columns are required to populate only when they are marked as required column in siebel tools, otherwise it can be ignored.

If the requirement is to populate the column say for example S_STDPROD_PMTRX.MTRX_RULE_NUM which is a required user key column, it can be updated with either the ROW_ID value or any other unique value like sequence etc.

Updating MTRX_RULE_NUM with ROW_ID value:

1. Run EIM to import the S_STDPROD_PMTRX rows without some unique MTRX_RULE_NUM values first, for example populate it with any database sequence value.

2. Once the import is successful, select the imported rows in step 1, and re-populate them into the interface table (with different batch number) with ROW_ID values (from siebel) in the MTRX_RULE_NUM column.

3. Run EIM import again to update S_STDPROD_PMTRX.MTRX_RULE_NUM with the values populated in step 2. Please include the following parameters in the .ifb file:

       TYPE = IMPORT
       BATCH = 2
       TABLE = EIM_ADJ_GROUP
       ONLY BASE TABLES = S_ADJ_GROUP, S_STDPROD_PMTRX
       INSERT ROWS = S_ADJ_GROUP, FALSE
       UPDATE ROWS = S_ADJ_GROUP, FALSE
       INSERT ROWS = S_STDPROD_PMTRX, FALSE
       UPDATE ROWS = S_STDPROD_PMTRX, TRUE

This way we can update the column with siebel ROW_ID values i.e by first importing then exporting and then again importing with update only.

Remember, These special columns cannot be populated with exact pre-default expression values through EIM.

For more details logon to Oracle Support portal.