Excel Migration

A migration job with procedure type Excel Migration can be executed through MS Excel and an authorized end user could search / manipulate data in the specific logical units related to the given migration job. The IFS Data Migration Excel Add-In uses the Excel Migration procedure type and is an extension to the standard data migration functionality in IFS applications.

The Excel Migration procedure is somewhat similar to a source migration job. But this procedure has some special features. This includes concurrent execution support and data query support. Because of the concurrent execution support, you do not need to create duplicate jobs for several users. Rather you just need to assign the same job to different users.

This document describes how to set up and configure Excel Migration type jobs using IFS Data Migration. For more details on how to use the add-in, refer the help documentation in the Excel Addin.

Content

Creating a Excel Migration job

Creating an excel migration job is similar to creating a source migration job. First create a Migration Header, define method list, map source columns and configure the rules.

Job Header

In the Migration Job window, create a new job, set the Procedure Name to EXCEL_MIGRATION. If this job needs to be related to a specific group set the Group ID. Migration jobs which are related to groups are shown in their separate node in Migration Jobs Add-in pane in Microsoft Excel. You don't have to specify a 'View Name'.

Define Method List

Here define the logical unit you want to migrate the data into. See Migration Job/Method List.

You can add condition for executing a method by giving values for Column Name, Column Value.

In the above configuration, the methods will only execute when the CONTRACT value is equal to one. A similar filtering can done for querying data by specifying a WHERE condition for the FETCHCONDITION.

If you specify multiple LUs, and want the job to be queryable, you would need to specify a join condition specifying the relationship between the LUs in the FETCHCONDITION rule.

You can also use methods other than New__, Modify__ by specifying them in the Method Name field. These methods will be invoke only when the user presses the execute button. When the user validates New__ and Modify__ methods are executed in 'CHECK' mode and other methods are not executed.

Usage of BeforeLoop and AfterLoop actions are currently not recommended.

Usage of NoExecution action is allowed. This can be useful when you want to query from a view but do not want to modify data in them.

Use the 'Source Name' so that the source columns will be automatically mapped to column names in the source mapping tab. In most cases, this is the same as the view name.

Check 'On New Master' to avoid repeated execution for the same set of data if the master data is duplicated. For this to succeed the rows should be sorted by master's key values.

Check 'On First Row' to execute the method only for the first row of the data set.

Source Mapping

Here add/modify required columns for the migration job. Make sure Pos and Length are not set to 0. Enter a value for the Source Column if you want to display it as an input from the user in Excel. The Source Column names will be used when creating the IC table for migrating data.

Note the following restrictions for the Source column:

Important: If an input value need to be reused you can add the Source Column value of one record as the “Default Value” of another. By doing this, the user only have to give the value in one column in Excel.

You may have default values for fields (You can have default values either with a value for Source column or without)

Note the following when using default values,

You may also change the Description of the item (can also be translated, RMB Translation…) and add notes to the mapped source column (information to the Excel user)

Always try to keep the number of columns shown to the end-user (those which has a value for Source Column) to a minimum.

Configuring Job Rules

Only the following rules are currently supported by an EXCEL_MIGRATION job.

This rule can be used to retrieve data from a view which cannot be inferred by the method list, by mentioning the view name as 'QUERYVIEW'. If 'QUERYVIEW' rule is specified, then migration tool will use that view for the query purposes.

Note that the automatic column mapping will not be done for this and has to be done manually, in order to map source columns as defined in the ‘QUERYVIEW’

   

Granting Jobs to Endusers

Using the usual Data Migration Basic Data window the job can be granted to end-users. Make sure that the user has appropriate Package and Method grants in Solution Manager. If the user needs to query data through the job, make sure that they have required grants to the views as well.

End-users must be granted the FNDMIG_EXCEL_ADDIN permission set to use the add-In. Use Solution Manager to grant them. One job can be used by multiple users concurrently.

There is a associated license/sales part to use the IFS Data Migration Excel Add-In.

Translating fields in the Migration Job

The following fields in the Excel Migration job can be translated.

Migration Job header

Migration Job > Source mappings tab

To translate them RMB and select Translation… in the header or Source Mapping tab. The translation process is the same as how Basic Data Translation is handled. (See the following link for more details on the basic data translation process)

See the Add-In Deployment guide to see how to translate the Add-In itself.

Executing the Job and correcting errors

Before granting the job for end-user use, you should first test the job. To do this log in to the add-in with a user who has access to the Excel Migration job. (Should be done either on Test data or Non-Production environment)

Load the Migration job. You will see that the Excel Worksheet will be populated with the available columns and any default values specified. If the job is Query enabled try query and see if it works. Then enter some data and press validate, and see if you get any errors. The outcome of the process is shown in the Result Column or if it’s a severe error an error message will be shown. In any case you can debug the issue by enabling tracing. This can be done in the Excel Add-In by going to Settings and checking the Enable Trace option. Then go through the trace file to find the issue (Search for PLSQL Trace). Most of the time it should be basic data issue. Redo the steps until the job is ready to use.

See the Add-In User Manual on how to use the add-In.

Handling LUs with Custom Fields

It is possible to create Excel Migration jobs which can query from and insert/update data into LUs with Custom Fields.

Create the job header as usual and set the procedure name to EXCEL_MIGRATION. Then add two methods to in the Method List tab. One for the Base LU View (e.g. Supplier_Info), and another for the Custom Fields View (_CFV) . The Method Name for the Custom Fields should have the method _CFP.CF_NEW__. Note the Column Name and Column Value values given for the method. This is to ensure that method 20 is executed when DUMMY column has values. We will create this column in the Source Mapping tab later.

The Method List Attributes for the _CFV should only include the Custom Fields attributes(CF$_), INFO_, OBJID_, ATTR_CF_, ATTR_, ACTION_. Remove all the other attributes. The configuration should be as the following.

OBJID_@10 holds the OBJID value returned after calling New__, Modify__ of the LU in method 10.

Now go to the source mapping tab and enter values for the Source Column. If you have entered a View Name for the Migration Job Header when creating the job, most of the Source Columns would have been filled automatically. You might have the same column names coming from _CFV view as well. Remove them as they are not required but keep the Custom Field columns. Make sure that you have entered Source Column values for the Custom Fields. Create the column DUMMY mentioned before which acts as the condition for executing method 20. For the Default Value for the DUMMY column add the Custom Field's Source Column name. If you have more than one Custom Field, concatenate them together.

Finally, to query data from the custom fields and the standard LU, we need to join them together. To do this, go to the Rules tab and for the value for the FETCHCONDITION and enter <LU_VIEW>.OBJKEY = <LU_NAME>_CFV.OBJKEY (e.g. SUPPLIER_INFO.OBJKEY=SUPPLIER_INFO_CFV.OBJKEY).

When granting the job to the end-user make sure that all relevant view/package grants are given.

Migrate Data into Persistent Custom Reference Fields

This is very similar to the above configuration details (Handling LUs with Custom Fields). There’s only few modifications that should be done to migrate data into Persistent Custom Reference Fields.

Create the job header as usual and set the procedure name to EXCEL_MIGRATION. Then add two methods to the Method List tab. One for the Base LU View (e.g. Income_Type), and another for the Custom Fields View (_CFV).

The Method List Attributes for the _CFV should include the Custom Fields attributes(CF$_) & (CF$_DB). Make sure the CF$_DB field is there for the Persistent Reference Fields. The configuration should be as following:

Then for the Default Value of CF$_DB column, add an expression which will return the Rowkey from the given Client Value(Display Text). This is important because for Reference Fields, the RowKey will be saved in the database, and it will be fetched using the client value. Therefore make sure that the client value is unique.

Mainly there are two senarios where custom references can be used:

1. Persistent Reference created from a Custom Logical Unit.

2. Persistent Reference created from a Standard Logical Unit.

Above example shows migrating data to a Persistent Reference field which is created from a Custom Logical Unit.

Above example shows migrating data for two Persistent Reference fields where one is created from a Custom Logical Unit and the other one is from a Standard Logical Unit.

Handling LU's with CLOB columns

Handling columns with CLOB data type is not supported as is in Data Migration. This is because CLOB data is not sent in the Attribute String which is used to insert/update data. CLOB data is handled separately.

But there is a workaround which can be used to migrate data into CLOB columns up to 2000 characters.

Create the job header as usual and set the procedure name to EXCEL_MIGRATION. Then add two methods to in the Method List tab. One for the Base LU View (e.g. Business_Lead), and another to set the CLOB field (e.g Note) using a procedure E.g. Write_Note_Excel__. This method is similar to procedure exist for most LUs with CLOB fields which will be called Write_[Fieldname]__ (e.g. Write_Note__). The difference is that the method's arguments should be VARCHAR2 instead of ROWID. Then provide values for Column Name and Column Value for the method. This is to ensure that the Note field has a value.


The Method List Attributes for the LU attributes (i.e Execute Seq 10) include the attribute for the NOTE field. Because the Note field is handled by the Write_Note_Excel__ uncheck On New and On Modify for it. The configuration should be as the following.


The Method List Attributes for the Write_Note_Excel__ include the attributes OBJVERSION_, OBJID_ and NOTE_. The configuration should be as the following.


OBJVERSION@10 and OBJID_@10 holds the OBJVERSION and OBJID values returned after calling New__, Modify__ of the LU in method 10.
Now go to the source mapping tab and enter values for the Source Column. If you have entered a View Name for the Migration Job Header when creating the job, most of the Source Columns would have been filled automatically. You also have the same column names coming from Write_Note_Excel__ method list. Remove METHOD20.OBJVERSION_ and METHOD20.OBJID_ as they are filled using the Fixed Value in Method List Attributes. Change Data Type to VARCHAR2 for the NOTE field, because CLOB cannot be handled. Change the Length to 4000. Enter NOTE in source column for Note field and NOTE in the Default Values for Note_ attribute.


When granting the job to the end-user make sure that all relevant view/package grants are given.