Sage ERP MAS 500
Software Development Kit (SDK)
Data Migrator Guide
trademarks of Sage Software, Inc. or its affiliated entities. All other trademarks are the property of their respective owners.
Contents
Chapter 1: Overview... 1
Introduction to Data Migrator ...1
Using Data Migrator...2
Setting Up Migration Links ...2
Migrating Data...2
Chapter 2: Data Migrator Architecture ... 3
High Level Data Flow ...4
Supported Data Migrator Configurations...5
Data Migrator Processes & Communication ...6
Processes and Communication Details...8
Migration Links ...8
Role of Assisted Company Setup in Migration... 11
Migrate <Step Name> ... 12
Staging Table Architecture Details ... 16
Migration Calling Architectural Overview... 17
Migration Interface: dmMigrateIntfc... 17
Migration Caller Interface: IOwner ... 18
Migration Interface Enumerated Types ...18
Extraction Architecture Details ... 21
Heterogeneous Data Sources Issue ... 21
Getting Extraction Data in Sets ... 22
Extraction COM Interface: IExtract... 22
Extraction Stored Procedure Interface ... 24
Insert Architecture Details ... 25
Background Information and Overview ...25
Insert Stored Procedures Interface ... 27
Migration Parameter Behavior and Metadata ... 28
Chapter 3: Data Migrator Customization ...33
Calling the APIs ... 34
Determine the API Name ... 34
Determine the Staging Tables ... 34
Passing Data to the API... 35
Maximizing API Performance ... 36
Return Values ... 37
Populating Migration Parameters ... 37
Getting API Error Logs... 38
Example of Calling the APIs ... 38
Creating a New Source System Migration ... 39
Create the Custom Extract Procedure ... 39
Defining a Migration Link For Your Source System ... 46
Add Metadata for Your Extractions ... 46
Adding Functionality to Existing Extractions and Insertions ... 47
Sample Add-on Extraction Metadata ... 47
Adding a Staging Table to a Migration Step ... 48
Chapter 4: API Reference ...49
Balances ... 49
General Ledger Account Balances ... 49
Inventory Balances ... 51
Inventory Balances – Actual Cost Method ... 51
Cash ... 52
Banks ... 52
Bank Accounts ... 52
Tender Types... 53
Customers ... 53
Customer Classes ... 53
Customer Custom Fields ... 53
Customer Items ... 54
Customer Payment Terms ... 54
Customer Price Groups ... 55
Customers ... 55
Sales Territories... 57
General ... 57
Commission Classes ... 57
Processing Cycles ... 58
Product Categories ... 58
Reason Codes ... 58
General Ledger ... 59
Allocations ... 59
Journals ... 59
General Ledger Accounts ... 60
Account Segments... 60
Budgets... 60
General Ledger Custom Fields ... 61
Natural Accounts ... 61
Sales Tax Accounts ... 61
Global ...62
Sales Tax Classes...62
Sales Tax Codes ...62
Sales Tax Schedules...63
History...64
Closed AP Invoices ...64
Closed AR Invoices ...65
Closed Purchase Orders ...66
Closed Sales Orders ...67
General Ledger Transactions ...67
Inventory History ...67
Inventory ...68
Inventory...68
Inventory Item Class...69
Inventory Items ...70
Warehouse Bin ...72
Warehouses ...72
Items...73
Freight Classes ...73
Item Custom Fields ...73
Noninventory Item Class ...73
Noninventory Items ...74
Product Price Groups ...74
Purchase Product Line ...75
Sales Product Line ...75
Manufacturing ...75
Employees...75
Operations ...76
Routings ...76
Tools...77
Work Centers ...77
Open Orders ...77
Purchase Orders ...77
Sales Orders ...78
Open Transactions...78
Bank Account Transactions...78
Open AP Invoices...79
Open AR Invoices ...79
Pricing ...81
Customer (Contract) Pricing ...81
Matrix Pricing by Product Price Group ...82
Matrix Pricing by Customer Group ...83
Price Sheets ...83
Product Group Pricing ...83
Purchasing...84
Buyers ...84
Purchase Order Custom Fields ...84
Purchase Order Departments ...85
Sales...85
Sales Transaction Custom Fields...85
Sales Organizations ... 86
Sales Commissions ... 86
Sales Teams ... 87
Sales Persons... 88
Vendors ... 88
Vendor Classes ... 88
Vendor Custom Fields ... 88
Payment Terms ... 89
Vendor Items ... 89
Vendors ... 90
Overview
Use this guide to learn what drives migration, how to build your own migration for any legacy system you want to upgrade, and how to use Data Migrator APIs to load data on an ongoing basis. This guide provides detailed information on the technical aspects of Data Migrator, including:
• Schema and metadata that drive migration
• APIs used in Data Migrator
• Calling the Data Migrator APIs
• Modifying and creating extractions and insertions
Introduction to Data Migrator
Data Migrator, a task in Assisted Company Setup, offers a straightforward means of upgrading from Sage ERP MAS 90 and 200 to Sage ERP MAS 500. This migration feature replaces the need to write custom routines to transfer customer, vendor, and inventory information and to transfer the data to flat files for use with the import programs. Migrations are available for most of the data entry tasks, including all of the critical and voluminous items.
Data Migrator automates many aspects of upgrading a back-office system to Sage ERP MAS 500. With the features available in Assisted Company Setup, Data Migrator provides the following features:
• A guided list of steps to set up a new company in Sage ERP MAS 500, including access to the appropriate migration, import, and data entry forms
• Expert schema mapping from Sage ERP MAS 90 and 200 to Sage ERP MAS 500
• Staging tables to hold data between the source and Sage ERP MAS 500
1
Using Data Migrator
Setting Up Migration Links
If your migration involves automatic extraction of data from a source system, you must first define a migration link that refers to the source system. For example, a migration link may indicate that the source data is Sage ERP MAS 90 Level 3.70, company ABC, and is found on the centralized server called Accounting in the MAS90 shared folder. By giving this migration link a name, the user can refer to the particular Sage ERP MAS 90 information without having to know where it is or how to extract it.
You do not need to set up a migration link if you are going to use Data Migrator only as an insertion tool, entering data into the staging tables yourself, and then running the insertion process.
For more information, refer to the Assisted Company Setup Help.
Migrating Data
Data Migrator consists of three separate activities:
1 Extracting the data from the source system (this is optional and used only for Sage ERP MAS 90 and 200)
2 Modifying, deleting, or adding records to those extracted 3 Inserting the records into Sage ERP MAS 500
The first and third steps are controlled with the Processing Options tab on the Migrate form.
You can input data manually by going to the Staging Tables tab and using Excel to populate the staging tables, or you can use Data Transformation Services (DTS) in SQL. Processing options allow the choice of how to handle certain records.
There are several perspectives from which to discuss the Data Migrator architecture including the following:
• A high level view of the data flow
• The supported topologies
• The various processes and communication between them
• A functional view of the various processes in Data Migrator
Data Migrator 2
Architecture
High Level Data Flow
The figure below illustrates how Data Migrator supports the concept of moving data from a source system to the staging table holding area in the Sage ERP MAS 500 database. Data in the staging tables can be examined and modified using tools such as Microsoft® Excel®. The data can then be moved into the Sage ERP MAS 500 permanent tables and accessed through the front-end forms.
Figure 1 - Data Migrator High Level Data Flow
Sage ERP MAS 500 Client PC Client PC
Current Accounting
S
Sage ERP MAS 500 Sage ERP
MAS 500 S
System Database
Permanent Tables Staging
Tables
Supported Data Migrator Configurations
The figure below illustrates the five supported configurations. The first two configurations indicate that a source database, including a Sage ERP MAS 200 SQL database, can reside on any server in the network that has access to the Sage ERP MAS 500 client and server. (Sage ERP MAS 200 data is in a database pair: one database, MAS_xxx, where xxx is the company ID, and MAS_SYSTEM, which contains information common to all Sage ERP MAS 200 companies.) The third configuration, shown in the figure below, depicts the support for Sage ERP MAS 90 and 200. The fourth configuration indicates that any source system accessible using a connection string can be supported with customization. In particular, the common code can be used with an ODBC or OLE DB provider that can communicate using ActiveX Data Objects (ADO). Even if the common access code cannot support getting the source system data, you can still exploit the migration architecture, including the Assisted Customer Setup (ACS) forms, and use your access mechanism to put the data into the Sage ERP MAS 500 staging tables using the Sage ERP MAS 500 connection. The fifth configuration indicates that you can put Sage ERP MAS 90 or 200 data on the same server as SQL and the Sage ERP MAS 500 database. This configuration avoids network traffic and increases system performance.
The SQL commands required to move data between SQL Servers is different than those required when the databases reside on the same server. Also, some connection properties and behaviors are different for the ProvideX ODBC driver, other ODBC drivers, and OLE DB.
Sage has built a common piece of code to handle many of these differences. The third configuration in the figure shown above indicates that Sage ERP MAS 90 and 200 files can be located on any server in the network, including the Sage ERP MAS 500 database server.
Because Sage ERP MAS 90 and 200 databases are files, passing of data between them and SQL server does not depend on where they are in the network, as long as both servers can be accessed by the client PC. For information on the methodology used to obtain data in all four cases, see Processes and Communication Details.
Data Migrator Processes & Communication
There are three main recurring processes in Data Migrator: the extraction, the insertion, and the Assisted Company Setup (ACS) form interaction (which contains two parts). The main ACS form and migration form are the first two pieces in the process: taking requests from the user, and calling the appropriate extractions and insertions, based on the current ACS step.
The main processes and their primary responsibilities include:
• Assisted Company Setup – Helps the user set up a Sage ERP MAS 500 company.
• Lets the user pick a migration source system.
• Determines if a migration is available for a particular ACS step.
• Launches the migration processing form.
• Migrate <Step Name> – This is the primary migration interaction form. <Step Name> is the name of the current ACS step (for example, Sales Tax Accounts). Based on user choices, this migration interaction form runs the extraction and insertion and allows staging table data manipulation. A migration progress dialog box displays when either extraction or insertion is running.
• Extraction – Places all data related to a particular step into the Sage ERP MAS 500 staging tables associated with that step.
• Insertion – Applies the Sage ERP MAS 500 business rules to data in a step's staging tables and inserts the records into Sage ERP MAS 500. Depending on the report setting, information may be stored about the successes, warnings, and failures of each attempted record.
The communication between the migration process and the extraction and insertion processes is done through two well-defined interfaces. All extractions use the same interface and all insertions use the same interface. The migration process uses metadata to determine what extractions and insertions to call for a particular step. The details of this process are provided later in this document.
The diagram below depicts a data flow of the migration process.
Figure 3 - Data Migrator Data Flow Diagram A s s is te d C o m p a n y
S e tu p
M ig ra te S te p
E x c e l
In s e rtio n
E x tra c tio n
U s e r-in te rfa ce in te ra c tio n
M ig ra te S ys te m , lin k a n d s te p in fo rm a tio n
D a ta M ig ra tio n S y s te m T a b le s
R e p o rt L o g
S a g e E R P M A S 5 0 0 D a ta b a s e S ta g in g
T a b le s S o u rc e S y s te m
D a ta b a s e o r F ile s
S te p re la te d m ig ra tio n d a ta
S te p re la te d e xtra c t, in s e rt, a n d p a ra m e te r d a ta
S ta g in g ta b le d a ta
S te p e n tity re la te d d a ta (a ll re c o rd s th a t ca n b e m ig ra te d)
E xtra c t, in s e rt, a n d s ta g in g ta b le m a n ip u la tio n re q u e s ts
(u s in g u s e r-in te rfa c e in te ra c tio n )
S ta g in g ta b le d a ta m e e tin g s te p ’s S a g e E R P M A S 5 0 0
b u s in e s s ru le s D a ta to M ig ra te S te p
fo rm , m o d ific a tio n s re tu rn e d to s ta g in g
ta b le
S o u rc e s ys te m d a ta m a p p e d to s ta g in g
ta b le la y o u t
S o u rce lin k, S a g e E R P M A S 5 0 0 c o n n e c tio n , s u c ce s s ,
fa ilu re
S a g e E R P M A S 5 0 0 c o n n e c tio n , c o n tin u e fla g,
n u m b e r o f re co rd s p ro c e s s e d , s u c c e s s , fa ilu re
Processes and Communication Details
This section describes the primary migration processes, the metadata that drives them, and the communication between the processes.
Migration Links
Migration links are specific to a particular source system and version, for example, Sage ERP MAS 90 Level 3.61. They are restricted to a single company, so if you want to migrate two Sage ERP MAS 90 companies, you need to either define two migration links, or migrate the first company's data, modify the migration link to point to the second Sage ERP MAS 90 company, and then migrate the second company's data.
Migration links are one of four types:
• Linked server – A linked server is a SQL Server object that allows the execution of commands against OLE DB data sources. These can be used to communicate with Sage ERP MAS 200 SQL databases that reside on another server.
• Same server database – Another database located on the same server as the Sage ERP MAS 500 application databases.
• ProvideX ODBC driver – The ODBC driver for Sage ERP MAS 90 and 200 files.
• Other connection string supported providers – These include ODBC and OLE DB providers.
If a migration link's source system is one of the natively supported products, the migration link type is determined automatically. Selecting either Sage ERP MAS 90 or 200 results in a ProvideX ODBC type. Selecting Sage ERP MAS 200 results in either a linked server or a database link, depending on whether the source database is on the same server as Sage ERP MAS 500. Custom migration source links support a connection string provider. If the
connection string provider is supported by ADO, the common code can be used to obtain the source data; otherwise, another means needs to be employed to put the data into the staging tables. This does not, however, preclude utilizing the communication between the migration process and your extract logic.
Migration link data are stored in tsmMigrateLink, which is described more fully in the Schema Browser. Migration links require a Migration Source system (for example, an entry in tsmMigrateSource).
Linked Servers
Linked servers exist in SQL server at the server level – they are not database specific objects;
however, Sage ERP MAS 500's migration links are database-specific objects (stored in tsmMigrateLink). Because linked servers are not database specific, migration links are not company specific. After defining a migration link in one company, the migration links are available in all Sage ERP MAS 500 companies in that database.
In environments with multiple Sage ERP MAS 500 databases on the same server, if you created one database per company, linked servers may exist on the machine that are not known to a particular database (company). To get the migration link definition into additional databases requires redefining it in each database. When you save the migration link in another database on the same server, you will be prompted with a message stating that the migration link already exists (because it is at the server level, not the database level). You must overwrite the migration link to save the definition in the current database. An alternative is to name the migration links differently from one another.
Using Migration Links in Assisted Company Setup
For an illustration of how a migration link is used, see Figure 4 – How Migration Links are Used. In ACS, selecting a product version for which migration links exist, results in the lower drop-down list having the available migration links appear. In this example, selecting Sage ERP MAS 90 ABC points to the network server, Tahoe, where the Sage ERP MAS 200 SQL
“MAS_ABC” company resides.
After a migration link is selected, step-sensitive help becomes available and the Migrate button becomes available or unavailable based on the availability of a migration for the current step.
If your source system is not one of the supported source systems, you will find that ACS can be used for moving your data into Sage ERP MAS 500. By selecting source system Other and version Any, you can use all of the migration steps; however, there will not be an available extract. You can, however, access the staging tables and use Excel to obtain your source data manually. The data you put into the staging tables using Excel will be available to the insert step.
Figure 4 – How Migration Links are Used
For a description of how to define migration links for custom extractions, see Chapter 3 - Data Migrator Customization.
Role of Assisted Company Setup in Migration
Assisted Company Setup (ACS) supports migrations from predefined source systems. These can be out-of-the-box migrations, or custom migrations. The schema related to ACS migration metadata is shown in the figure below. Selecting a migration source on the Migration System tab in the Assisted Company Setup form is allowed if that product is defined as a migration source in tsmMigrateSource. The bottom of the figure shown below illustrates the
relationship between the tab and the database table. Selecting a valid migration system relays to ACS to check for the migration system for any step. Selecting a Source Name migration link relays to ACS where the data is located. This step, source system, and migration link
information is used by the Migrate <Step Name> process.
A phase, such as Inventory, can have multiple steps, and each could have a migration from several source systems (for example, Sage ERP MAS 90 and 200).
ts m S e tu p P h a s e
ts m S e tu p S te p
ts m M ig ra te S te p
ts m M ig ra te S o u rc e S e tu p S te p K e y M ig ra te S o u rc e S y s te m M ig ra te S o u rc e V e rs io n N o
S e tu p S te p K e y S e tu p P h a s e K e y
M ig ra te S o u rc e S y s te m
Migrate <Step Name>
Extraction and Insertion
The Migrate <Step Name> process is called by ACS to run a particular step's extractions and insertions. The form's options allow for running either the extract or insert process or both. It is here that the reporting option is also specified. The following information is communicated to this process:
• Migration link
• Target or current company in Sage ERP MAS 500
• Current step
Each step can support multiple, serialized extraction and insertion steps. For example, the extraction might consist of two steps and the insertion might consist of one step. The name, type, and sequence number of each step is stored in the tsmMigrateStepExtract and tsmMigrateStepInsert tables. The type of an extract or insert refers to the software component's type (for example, stored procedure or DLL). This, determines how it is called by ACS.
For an illustration of the schema metadata that determines the extraction and insert
processes, see Figure 6 - Migrate <Step Name> Metadata. By calling DLL routines in a late bound fashion, they can be named in metadata and unknown until they are called. This is the technique Sage ERP MAS 500 uses to call extraction routines, which are written as Visual Basic COM objects exposing a standardized interface expected by this process. For more details, see Migration Calling Architectural Overview, Extraction Architecture Details, and Insert Architecture Details. In addition to extraction and insertion metadata, there is also metadata for migration parameters.
Migration Parameters
Migration parameters help fix extracted data that fails to meet the Sage ERP MAS 500 business rules. For example, the source system may not require the existence of records in foreign key relationships, such as ZIP Codes that do not exist in the ZIP Code table. If the field is not required in Sage ERP MAS 500, you can be prompted to fail the records that have bad references or blank out the bad references. Another commonly needed parameter is for missing general ledger accounts. If Sage ERP MAS 500 requires an account that is not a supported concept in the source system, then a parameter allows you to choose to set the missing general ledger account to the suspense account. Alternatively, the parameter could allow selection of an existing Sage ERP MAS 500 general ledger account. For more information about parameter definitions and usage, see Migration Parameter Behavior and Metadata.
Figure 6 - Migrate <Step Name> Metadata
After insertion is run, if any reporting was requested, the Migration report appears. The report can be used to refer to the staging tables as an aid to correct failed records.
SetupStepKey tsmSetupStep
tsmMigrateStep
SetupStepKey MigrateSourceSystem MigrateSourceVersionNo
SetupStepKey ProcessNo
SetupStepKey ProcessNo
SetupStepKey ParamNo MigrateSourceSystem Migrate SourceVersionNo
tsmMigrateStepExtract
SetupStepKey ParamNo
tsmMigrateStepInsert
tsmMigrateStepParam
tsmMigrateStepParamUse
Staging Table Data
The extraction routines place data into staging tables. Before the insert process is run, all of the extracted data is available for review. Running insertion removes successful records from the staging tables, leaving only those records that failed the insert step's business rules. Use the Staging Tables tab in the Migrate <Step Name> form to access the step's data. The figure below shows a sample of the Staging Tables tab. When you select a table from the list, its data is brought to your machine and the total number of rows in the table displays.
You can add or delete rows, clear all of the staging tables' data, edit value directly in the grid or edit the data in Excel. Returning modified data from Excel immediately puts the changes into the staging table in the Sage ERP MAS 500 database.
Figure 7 - Staging Table Data NOTE
For large data sets, bringing the data to your PC and moving it between Excel and the Staging Table grid can take a significant amount of time. Excel allows only 65536 rows in a spreadsheet.
StageTableName tsmSetupStepStage
SetupStepKey tsmSetupStep
Migration Progress Form
The Migration Progress form (illustrated in the figure below) provides feedback while extracts and inserts occur. The Migration Progress form also indicates the number of records to process, the number processed, and the number of failed records. On extraction, if there are any data issues, a report is provided to describe the issue. For example, Sage ERP MAS 90 commissions have the concept of a base commission, while Sage ERP MAS 500 does not;
therefore, Sage ERP MAS 90 records with nonzero base commissions are reported, informing you that their base commissions will not be brought into Sage ERP MAS 500. During insertion, which is an asynchronous process, the Done button is replaced with the Stop Processing button, allowing you to stop insertion. Extraction is not asynchronous; therefore, “Not Responding” may appear in the title bar of the Migration Progress form during extraction.
Figure 8 - Migration Progress Form
Migration Error Log
When the Insert process is running for a migration step, it logs successes and failures as specified in the reporting option and warning flag. If, for example, you request a report of all failed records, the insert step records information about failed records. The report appears after the insertion is finished and contains the following information:
• Step Name – This is located in the report header.
• Entity ID – Depending on the migration step, this indicates the natural key of the entity being reported on. Several different entities may be involved in a migration; therefore, an understanding of them is required to be able to determine the entity ID. For example, during the Sales Tax Code migration, the entity ID results may be similar to the following:
The first line's entity ID, CA, refers to the sales tax code staging table's natural key (STaxCodeID), while the second line's entity, CA | OC, refers to the sales tax class staging table's compound natural key (STaxCodeID, STaxClassID). The pipe character ( | ), is used to separate the different columns in the entity ID of compound key entities.
Entity ID CA CA | OC NOTE
Information is not recorded about all records, but only for records that were requested.
This helps increase the insertion performance by avoiding writing data that will not be used.
• Status – Indication of failure, success or warning for the reported row.
• Column – Name of the staging table column related to the entry. This column and the Value column are only populated if a failure or warning occurred, making this column useful for reviewing a report for failed records.
• Value – Data in the staging table column referred to in the previous column. For example, if this is a duplicate record, then the entity ID displays here, because it is the value that caused the duplications. Empty columns that require data appear blank here.
• Comment – The reason for the failure or warning. Successful records will not populate this column.
Staging Table Architecture Details
The use of staging tables in migration provides a location where source system data can be placed before it is validated against the rigorous business rules of the Sage ERP MAS 500 application. Sage ERP MAS 500 demands a highly reliable, consistent set of data that adheres to strict referential integrity, data type, and value range rules. It would be extremely challenging to move data out of an existing system and simultaneously require that it conform to the business rules of Sage ERP MAS 500. Additionally, the Sage ERP MAS 500 databases are highly normalized and provide maximum flexibility. This normalization has a tradeoff – it is difficult for a nontechnical person to understand. Because of these challenges, Sage provides staging tables as the target for source system data extraction.
Staging tables resemble their Sage ERP MAS 500 counterparts, but are different in a few significant ways:
• Surrogate key values do not exist in the staging tables. These are created during insertion.
• Fields that are numerically enumerated values in the Sage ERP MAS 500 permanent tables have been changed to varchar fields (the SQL Server variable character length field type) in the staging tables. For example, if the field in Sage ERP MAS 500 supports values (0, 1, and 2), which represent (Active, Inactive, Deleted), then the staging table equivalent would be a varchar(8) – long enough to support the longest string value equivalent of the enumerated value 1, Inactive. By doing this, the entry of any of the six acceptable values is allowed – (0, 1, 2, Active, Inactive, and Deleted). It is expected that extractions will populate these fields with the string value equivalents (for example, Active). The values are converted to the corresponding numeric value on insertion.
• Where it improves understandability, otherwise normalized data is flattened into the parent table. For example, the Sage ERP MAS 500 customer table references a customer address table, which references our common address table. This three-level normalization allows all addresses to be kept in a single table for referral from different entities, adding addition entity-only related attributes to the joining table (in this case, the customer address table). For example, customer addresses contain a reference to a sales person, which is not applicable to vendor address; they more likely relate to buyers. If, in this case, a source system only supports one customer address, there is no need to populate two staging tables. Even in this case, full functionality has not been removed – a customer address table is provided for additional addresses related to a customer.
Staging tables do not enforce referential integrity, uniqueness, or any other business rule related consistency. Only the natural SQL Server data type restrictions are enforced.
NOTE
The third level was removed from the normalization; the customer address and common address related fields were pulled out to keep the abstraction transparent to any populating of the staging tables.
This avoids confusion and a need to fully understand our customer address schema.
All Sage ERP MAS 500 staging tables contain three columns used by the migration
processes. Any new staging tables you define must contain these columns to be processed in our migration environment:
• ProcessStatus – Indicates the insertion status of the staging table record.
• 0 – Not processed
• 2 – Failed the insertion process. If a parent record's validation fails, often it will have ProcessStatus 2, while its child records have ProcessStatus 0. Child records are not considered because the parent record is known to be bad.
• RowKey – A unique number identifying this row. This is defined as an identity column so that its value cannot be changed.
Example:
A staging table has three columns. There are two records in the staging table and the permanent table does not allow duplicates:
Technically, the first row is good and the second is a duplicate. When the first row is processed, if deletion is attempted based on its ID, both rows are deleted and duplication is not noticed; therefore, a column that is guaranteed to be unique is needed.
• SessionKey – Migration supports multiple users running the same migration step at the same time. To enable migration to differentiate between two users' data in the staging tables, the concept of the SessionKey is available. Table tsmMigrateSession contains the information for a particular session.
Migration Calling Architectural Overview
This section describes the migration interface that is provided through the Assisted Company Setup (ACS) migration processes. Details of the extraction and insertion functionality and related interfaces are provided in the following sections.
Migration Interface: dmMigrateIntfc
dmMigratIntfc.DLL contains the migration interfaces needed for extraction. Its inclusion in DLL type extractions and insertions is required. The interfaces it contains are:
• IExtract – The extraction COM interface. For more information about this interface, see Extraction Architecture Details.
• IInsert – The insertion COM interface. For more information about this interface, see Insert Architecture Details.
• IOwnerMigrate – COM Interface supported by extraction callers, for example ACS, to enable communication of progress.
ID Description Effective Date
LowPrice Low priced items 01/01/2002 LowPrice Low priced items 01/01/2002 NOTE
Successful rows are deleted from the staging table as they are
processed.
NOTE
It is not required that a row exist in tsmMigrateSession to utilize the Sage staging tables, but the SessionKey in the staging table must match that passed into the insertion routine to have the correct data considered for insertion.
Migration Caller Interface: IOwner
A migration caller supports the following interface, allowing called extractions or insertions to respond back through the available IOwner methods.
Public Sub OnError
(ByVal EventSource As String, ByVal ErrorCode As Long, ByVal Source As String, ByVal Description As String, ByVal HelpFile As String, ByVal HelpContext As Long, ByVal IDofInterfaceWithError As String, pbCancel As Boolean) Public Sub OnFinish
(ByVal EventSource As String) Public Sub OnProgress
(ByVal EventSource As String, ByVal ProgressDescription As String,
ByVal PercentComplete As Long, ByVal ProgressCountLow As Long, ByVal ProgressCountHigh As Long)
Public Sub OnQueryCancel
(ByVal EventSource As String, pbCancel As Boolean) Public Sub OnStart
(ByVal EventSource As String)
Migration Interface Enumerated Types ExtractCallReturnValsEnum
Extract DLL or stored procedure return values.
Constant Value Description
UnknownFailure 0 Error trapped but not enumerated.
Success 1 Procedure was successful. This does not mean that all data is correct, only that the process was successful in doing its job of attempting to extract data. Extracting zero records can still result in success.
GeneralFailure 2 Nonspecific, fatal error.
BadParameters 3 Some parameter is in error, such as required but not supplied.
VersionError 4 Unsupported product version.
ProductError 5 Unsupported product.
TempTableError 6 An issue occurred with a temporary table. For example, it could not be created, or was expected to exist and does not.
ConnectionError 7 Unable to connect to the Sage ERP MAS 500 database or the source database given the connection and/or connection information.
SourceTableNotFound 8 A source system table, expected to exist, does not.
SuccessWithInfo 9 Extraction was successful, but data issues were discovered and recorded in the migration error log.
NOTE Although this interface is required, at this time, only OnProgress is supported (although Sage migrations do not utilize it).
OnProgress causes information to be written into the detailed progress grid on the Migration Progress form.
ExtractSourceType
Indicates what type of source system connection is expected, allowing access to
heterogeneous data sources. Certain extraction parameters are interpreted according to this value (for more information, see Start Function). UseDatabase, UseLinkServer, and
UseODBC_ProvideX are all supported by the Sage ERP MAS 500 extractions.
InsertCallReturnValsEnum
Insertion DLL or stored procedure return values.
InsertDuplicateActionEnum
Determines what to do when a duplicate is encountered.
Constant Value Description
UseNeither 0 No connection information supplied.
UseLinkServer 1 The connection is expected to be through a linked server, defined on the Sage ERP MAS 500 server (can point to any product supported by SQL Server linked servers and supporting SQL syntax).
UseDatabase 2 The connection is expected to be to a SQL Server database on the same server as the Sage ERP MAS 500 application database.
UseODBC_ProvideX 3 Sage ERP MAS 90 ProvideX ODBC driver connect type.
UseConnectString 4 A free form string used to connect to any provider. This is stored encrypted, so passwords can be safely included.
Constant Value Description
UnknownFailure 0 Error trapped but not enumerated.
GeneralFailure 2 Nonspecific, fatal error.
Success 1 Procedure was successful. This does not mean that all data is correct, only that the process was successful in doing its job of attempting to insert data.
BadParameters 3 Some parameter is in error, such as required but not supplied.
Constant Value Description
DoNotInsert 0 Fail records that are duplicates.
ReplaceExisting 1 Replace duplicate record with this record.
InsertInvalidGLAcctActionEnum
Determines how the occurrence of invalid general ledger accounts should be handled during insertion.
InsertInvalidReferenceActionEnum
Indicates how invalid references in staging table data should be handled upon insertion.
InsertReportOptionEnum
Determines what records to log during the insertion process for a migration step.
InsertWarningActionEnum
Determines what records to log during the insertion process for a migration step.
Constant Value Description
DoNotInsert 0 If the general ledger account field violates a business rule, do not insert this record. This includes bad general ledger accounts or fields with missing general ledger accounts when required.
UseSuspenseAcct 1 Use the suspense account in place of the bad value.
Constant Value Description
DoNotInsert 0 Fail records that have invalid foreign key references.
UseBlankReference 1 Replace invalid foreign key references with a blank
reference. This is only done if the foreign key reference is not required.
Constant Value Description
All 0 Log both records that succeeded and records that failed insertion.
None 1 No migration logging on insertion.
Good 2 Only log successful records.
Bad 3 Only log failed records.
Constant Value Description
ExcludeWarnings 0 Exclude warnings from the migration log.
IncludeWarnings 1 Include warnings on the migration log. This is only applicable if InsertReportOptionEnum is not equal to None.
Extraction Architecture Details
Extraction routines move source system data to the Sage ERP MAS 500 staging tables. There are two primary technical challenges that shaped the architecture developed for migration extracts. First, because the source data can be in different databases, including file-based structures, extraction requires data retrieval from various data sources. Second, without being able to bookmark the current spot in the extraction data, the technique of getting sets of data (of a predefined size) in a looping construct cannot be used. That is, you cannot get the first 100 records, and then get the next 100 until you are done. The implications of each of these issues are discussed below.
The discussion of extraction architecture below is also heavily influenced by the supported configurations (for information on supported configurations, see Figure 2 - Supported Configurations).
Heterogeneous Data Sources Issue
At first glance, having source system data in different data structures seems like a simple problem to solve because OLEDB and ODBC drivers exist for many data sources. These drivers are not available to stored procedures; however, Data Migrator must support both stored procedure and DLL extracts.
To support communication to a remote server through stored procedures, infrastructure was created to use linked servers; however, SQL Server's 6.5 compatibility mode does not support linked server query syntax. To resolve this issue, the following is performed in the Sage extract processes when using linked servers:
• Move to the MASTER database, which supports the OPENQUERY SQL syntax for linked servers because it is in 8.0 compatibility mode.
• Create a temporary table copy of the source system tables using the link source SQL statement. By putting the data into a temporary table, the location is always known and a database name does not need to be specified to reference it.
• Return to the Sage ERP MAS 500 application database.
These are important considerations when building your own extracts. Regardless of the link type (Linked Server, database located on the Sage ERP MAS 500 server, ProvideX ODBC, or Connection String), Sage’s extractions always place the source data into temporary tables and refer to these when populating the staging tables.
Getting Extraction Data in Sets
Long processes should provide progress feedback to the user. This includes extracting what may be many thousands of records from a source system. Because there is no way of tagging individual source system records, you cannot stop extracting at some record, mark the current position in the data, inform the user of the progress, and then continue picking up where you left off. (Certainly, there are ways to continue, including knowing the source entity's unique key and using some looping structure.)
To help address this issue, Sage decided that by providing a COM-based extraction interface, we can inform the user when a lengthy process is about to start and also inform the user of milestones within the extraction. Alternatively, we can create an asynchronous process, but some data connectivity providers are not asynchronous. As a result, “Not Responding” may appear in the title bar of the active migration window. To make extraction as quick as possible, we decided that all extraction processes will pull all rows at once, in a set-oriented manner.
Sage’s extractions must support the extraction COM interface expected by ACS. ACS also supports an extract interface for stored procedures; choose either mechanism for your custom extractions. (Technically, the interface is expected by the Migrate <Step Name> process, which is a DLL called by ACS to perform the extractions and insertions.)
There are two interface types that must be supported in an extraction environment: the extraction process must support the extraction interface, allowing the caller to start the extraction routine; and the caller must support the migration communication interface allowing the extraction to communicate status back to the caller.
Extraction COM Interface: IExtract
To support ACS's migration extraction call, an extraction DLL must support the
dmMigrationIntfc.IExtract interface discussed below, while an extraction stored procedure must support the interface discussed in the Register section.
Sage ERP MAS 500 extract DLLs written in Visual Basic expose the interface using Visual Basic's Implements statement. Unfortunately, Implements does not support events; therefore, to mimic the ability to receive events, the caller (ACS) passes a handle (to itself) to the extract class as an IOwnerMigrate interface. This interface supplied by ACS allows the extraction object to communicate status back to ACS.
Sage ERP MAS 500 has a COM object that exposes the extraction interface expected by ACS.
The extraction interface has three public routines, listed below.
Public Function Start
(oConn As ADODB.Connection, ByRef bCancel As Boolean,
ByVal lSessionKey As Long, ByVal sCompanyID As String, ByVal eSourceType As ExtractSourceType, ByVal sAppSourceName As String, ByVal sSysSourceName As String, ByVal sAppDBName As String, ByVal iMigrateSourceSystem As Integer, ByVal sVersionNo As String)
As ExtractCallReturnValsEnum Public Sub Register(ByVal Callback As IOwnerMigrate)
Public Sub UnRegister()
Start Function
The Start function is called to perform the extraction. For information about enumerated values, see Migration Interface Enumerated Types.
Parameter Description
oConn A connection to the Sage ERP MAS 500 application database with the current user's connection information. Note: This connection has the following properties:
SET ANSI_NULLS ON SET ANSI_WARNINGS ON
bCancel Returned to the caller if this process was cancelled. Currently not used.
lSessionKey The unique key for this user's session, used to identify their data in the staging tables.
sCompanyID Company ID of the destination company in Sage ERP MAS 500.
eSourceType Determines the type of connection to make to the source system. See Migration Interface Enumerated Types.
sAppSource If eSourceType = UseDatabase or UseLinkServer, then this is the application database for the source system.
If eSourceType = UseConnectString or UseODBC_ProvideX, then this is the decrypted connection string.
sSysSourceName If eSourceType = UseDatabase or UseLinkServer, then this is the system database for the source system (which may not be applicable). Unused otherwise.
sAppDBName Name of the Sage ERP MAS 500 application database.
iMigrateSourceSystem Valid entries from tsmMigrateSource.MigrateSourceSystem: 0 Other
1 Platinum for Windows 2 Sage ERP MAS 200 SQL 3 Sage ERP MAS 200 client/server 4 Sage ERP MAS 90
Note: Only values 2, 3, and 4 are supported by our extractions.
sVersionNo Valid entries from tsmMigrateSource.MigrateVersionNo.
Sage’s extractions support the following levels: 3.6, 3.61, and 3.7 in conjunction with the supported migration source systems supported (shown above for tsmMigrateSource.MigrateSourceSystem) that is:
Sage ERP MAS 200 client/server Level 3.6 Sage ERP MAS 200 client/server Level 3.61 Sage ERP MAS 200 client/server Level 3.7 Sage ERP MAS 200 client/server Level 3.71 Sage ERP MAS 200 SQL Level 3.6 Sage ERP MAS 200 SQL Level 3.61 Sage ERP MAS 200 SQL Level 3.7 Sage ERP MAS 200 SQL Level 3.71 Sage ERP MAS 90 Level 3.6 Sage ERP MAS 90 Level 3.61
Register
Register is used by the caller to pass a reference to itself to the extraction object, allowing it to call the IOwner methods mentioned in Migration Caller Interface: IOwner.
UnRegister
Releases references to caller.
Extraction Stored Procedure Interface
The following section describes the interface for extraction stored procedures. For information about enumerated values, see Migration Interface Enumerated Types.
CREATE PROCEDURE <ProductName><EntityDescription>Ext (@_iSessionKey INTEGER, @_iCompanyID CHAR(03),
@Product SMALLINT, @VersionNo CHAR(20),
@_oRetVal INTEGER OUTPUT)
For template examples of using the extraction interface, see Chapter 3 - Data Migrator Customization.
Parameter Description
@_iSessionKey The unique key for this user's session, used to identify their data in the staging tables.
@_iCompanyID Company ID of the destination company in Sage ERP MAS 500.
@_iProduct Indicates the product to be extracted from. The expected values are listed in Migration Interface Enumerated Types.
@_iVersionNo The version number of the migration source system. This is a string because it can have letters in it, for example 5.0e.
@_oRetVal Integer indicating the extraction return state. Expected values are listed in the enumeration section under Extraction Return Values.
Insert Architecture Details
This section discusses the interfaces and coding style of the migration insertion routines. It also discusses why this architecture was chosen. If you are only interested in the insertion interface, see Insert Stored Procedures Interface without missing prerequisites.
Background Information and Overview
Insertion routines exist to move data from staging or temporary tables into Sage ERP MAS 500 after validating the records against the insert step's business rules. There are two primary technical issues to recognize when considering the architecture developed for migration inserts. First, because it is likely that large sets of data will be processed, do not bring the data to the client machine. Second, because the process may be lengthy and you may want to cancel it (if, for example, the failed record count becomes too large), the architecture needs to support processing data in chunks.
Large Data Set Issue
As mentioned in the extraction architecture section, long processes should provide progress feedback to the user. Additionally, because the insertion procedures will be taking large sets of data and moving them into Sage ERP MAS 500, the data should not travel more than is necessary. Because the extraction process described above has already put the data into staging tables located in the Sage ERP MAS 500 application database, Sage strongly suggests the use of stored procedures to validate and insert the data into Sage ERP
MAS 500, which will avoid bringing the data to the client machine. Data Migrator uses stored procedures for insertion.
Insertion Processing Time
Inserting data is more involved than extracting data because each record must be validated before performing the actual insertion. Validations can be quite extensive. Due to the relatively long, per-record processing time for an insertion (when compared to the extraction process' per-record processing time), it is preferable to provide useful feedback to the user. Because you may want to cancel the insertion, an asynchronous behavior is necessary. Data Migrator launches the insertion stored procedures in an asynchronous manner using ADO. This technique allows the Migration Progress form to receive your Cancel button click action and interrupt the insertion. To keep you informed of the insertion progress, the insertion stored procedure interface includes a continue flag, which lets the stored procedure return to the caller, but tell it (for example the Migration Progress form) that there are more rows to insert.
The interface also includes a cancel flag, which lets the caller tell the stored procedure that the insertion should be cancelled.
To provide feedback, a call to an insertion stored procedure will, in general, return some type of status information approximately every 10 seconds. When you examine the example template, you will notice that it compares the stored procedure start time with the current time and works on a subset of rows from the staging tables – hopefully a set that it can process within the 10 second limit. The insertion should stop if 10 seconds has transpired, no more rows are available, or a critical error happened.
The previous paragraph stated that feedback is provided approximately every 10 seconds “in general.” When would this not be the case? – As mentioned above, the insertion stored procedures support moving data from temporary tables or staging tables. Two things must be true for an insert API to return to the caller before processing all rows: first, the source data must be in staging tables; second, the temporary tables needed by the insert process must be created before the insert process is called. The first requirement is Sage’s. If data is placed in staging tables, the data is processed in 10 second chunks. The second requirement is a limitation of SQL Server cursors – regardless of where the data reside (staging tables or temporary tables) Sage places the data into temporary tables and creates cursors based on the temporary tables. Creating and dropping temporary tables used by a stored procedure results in the stored procedure being recompiled, which is time consuming. Additionally, cursors cannot remain allocated for temporary tables that are dropped. Cursors also cannot be repeatedly allocated and de-allocated reliably; therefore, if the insert stored procedure's temporary tables are not created by the caller, all records will be processed before returning.
All of the ACS calls to the insertion stored procedures use staging tables. Alternatively, you can put data into local temporary table copies of the staging tables. Chapter 4 - API Reference lists the tables used by each API call; the Schema Browser documents each staging table.
Stored procedures are often recompiled when temporary tables that they refer to are created.
Since ACS calls the insert API stored procedures repeatedly (every few seconds, if they return with their continue flag set to true), performance is increased if the temporary tables are created outside the stored procedures once at the beginning of the calling loop. The list of temporary tables to create for an insert API are kept in metadata in
tsmMigrateStepInsertTable. It is recommended that you use this same technique of creating the temporary tables used by an API when calling the insertion APIs. If you are calling them in a loop, this will significantly increase their performance.
Supplying Useful Feedback
Supplying useful insertion feedback is a primary goal of the insert APIs. To achieve this, the migration error log contains detailed information about why a record did not meet the business rules. Each error log entry points to a specific record and gives an explicit reason for failing the data value. Isolation of unexpected, catastrophic errors is also a key goal, but presents a technical challenge.
Isolation of Unexpected Errors
The most efficient way to process large sets of data is by using SQL Server's set-oriented processing. When doing this, however, the risk of failing a set of records, and not knowing exactly which record caused the failure, is increased. To avoid this issue, Sage ERP MAS 500 insertion APIs insert records one at a time. Set-oriented processing is done in certain cases where the per-record processing time is large and the number of records is also large (given that we expected that there may be a large number of records). Currently, these include the following insertions: items, inventory, posted vouchers, and posted invoices. These insertions process one record at a time if the number of records is less than 250. Although this cursor- based processing takes somewhat longer, it helps ensure a successful insertion without excessive requirements (such as memory or log size) on the SQL Server. Because of the tradeoff between performance and single-record processing, Sage conducted performance benchmark tests of all of the migrations to verify their per-record processing times.
Research determined the manner that the insertion APIs implement cursors; they are defined FOR UPDATE and are allocated against a clustered index on the staging tables' RowKey field.
This maximizes the performance achieved when using cursor-based processing. Most insertion APIs create an index on the primary staging table if it is not already defined, although you may want to define a clustered index on your table if you create your own temporary table for the insertion API.
Allocating and deallocating cursors is an expensive operation. To avoid this, cursors are only allocated the first time an insert API is called and deallocated when the API is finished. The cursors are allocated against the local temporary table copy of the staging table (which we create automatically if staging tables are used). If, however, the temporary table is not created outside the API, then the cursor cannot be kept open when the API returns to provide in- progress feedback. To avoid the issue of cursor allocation in this scenario, all records are processed before returning to the caller.
Other Common Insertion Behaviors
In addition to processing data and returning every two seconds, the insertion stored procedures must have some other common behaviors:
• Based on the reporting option chosen by the user, errors, warnings, and/or successes must be logged. An issue here is that if the reporting option indicates to log only failed records and while processing a particular record, three warnings are noted, the warnings should only be logged if the record insertion actually fails (because those are the only records the user wants to see information about).
• Records in the staging tables are deleted, if successful. Records that do not meet the business rules are marked as failed.
• The insertion routines must support having data passed to them in temporary tables, not just from the staging tables. The temporary tables are still expected to be local temporary tables, with the same name and in the same format as the staging tables. If the caller does not use staging tables, then the two second time limit is ignored; processing continues until all records are considered.
• Insertion routines must be able to retry record insertion under certain conditions. For example, if an insert fails because an invalid foreign key reference exists and the reference is not required (by Sage ERP MAS 500) and you then decide to use the Replace Invalid References with Blanks option, then the invalid reference should be replaced with a blank and the insert retried.
Insert Stored Procedures Interface
The stored procedure interface provides status and connection information to the insert APIs, while data is supplied in staging tables. The insertion APIs have the interface shown below.
The API name is a guideline for how they are named, specific names can be found in the section listing all of the available APIs. For information about enumerated values, see Migration Interface Enumerated Types.
PROCEDURE <moduleID>api<EntityName>Ins (@_oContinue SMALLINT OUTPUT,
@_iCancel SMALLINT,
@_iSessionKey INTEGER,
@_iCompanyID CHAR(3),
@_iRptOption INTEGER = 1, -- Default = Print None
@_iPrintWarnings SMALLINT = 1, -- Default = Print Warnings
@_iUseStageTable SMALLINT = 1, -- Default = Use Staging Tables
@_oRecsProcessed INTEGER OUTPUT,
@_oFailedRecs INTEGER OUTPUT,
@_oTotalRecs INTEGER OUTPUT,
@_oRetVal INTEGER OUTPUT)
Migration Parameter Behavior and Metadata
Parameter Description
@_oContinue Returned by the insert routine to indicate that there are more records to be processed.
@_iCancel Passed in to tell the routine to do any cancel clean up, for example putting a “Cancel” notice on the report and stop processing.
@_iSessionKey Unique ID of the session, used to obtain only this user's session data from the shared staging tables.
@_iCompanyID Current Sage ERP MAS 500 company ID.
@_iRptOption The type of report the user wants. The expected value is one of the InsertReportOptionEnum values. For more information, see InsertReportOptionEnum.
@_iPrintWarnings Indicates that warnings should be included on the report. This is only applicable if the report option is not NONE. The expected value is one of the InsertWarningActionEnum values. For more information, see InsertReportOptionEnum.
@_iUseStageTable If true (1), data is obtained from the staging tables; otherwise, it is assumed to already be populated in local temporary table equivalents of the staging tables – this has the same name and structure as the staging tables.
@_oRecsProcessed The number of records that have been considered for insertion.
Each subsequent call to this routine adds to the value passed in; it is not reset to zero. It is set to zero on the initial call.
@_oFailedRecs The number of records that have failed validation. Each
subsequent call to this routine should add to the value passed in; it is not reset to zero. It is set to zero on the initial call.
@_oTotalRecs The number of records to be processed. If zero, then a count in the staging table should be made (if using staging tables); otherwise, leave the passed in value. When using staging tables, the first call should pass zero in this value, which tells the stored procedure that it has not been called yet, so it should do the initialization
procedures. This is set to zero on the initial call or unexpected results may occur.
@_oRetVal Insertion stored procedure return status. The expected value is one of the InsertCallReturnValsEnum values. For more information, see InsertCallReturnValsEnum.
Migration Parameters describes how migration parameters help enhance the migration behavior to dynamically change data values to meet the business rules. Using parameters can make otherwise noncompliant data meet the Sage ERP MAS 500 business rules. Migration parameter metadata are contained in two tables. The use of parameters consists of obtaining the parameters, selecting a response to the parameter, and applying the parameters. If you use the parameters through the Assisted Company Setup (ACS) interface, their
implementation is automatic. Using parameters on your own is an unsupported, advanced technique. Although they can be used to provide enhanced insertion functionality, creating and using your own parameters should be done carefully.
In ACS, parameters are displayed on the Migrate <Step Name> form (for more information, see Migration Parameters). In ACS, a user selects one of the parameter values, and the parameter is then either applied to the data immediately after extraction, or it is applied during the insert API.
The table that contains parameter definitions for the migration steps is
tsmMigrateStepParam(refer to the Schema Browser for a complete description of the parameter tables). The table that determines if a parameter applies to a given migration source system is tsmMigrateStepParamUse, and the table that contains the selections made by a user for particular parameters is tsmMigrateStepParamValue.
tsmMigrateStepParam's DfltInStgTable column determines if the parameter value should be put into the staging table before the insertion. For example, if the user selects a general ledger account for the FreightInAccount in the Shipping Method step, the account value chosen appears in the staging table before the insert process is run. To be populated on extract, the following must be true about a parameter and its destination column:
• The parameter must apply to only one staging table column.
• The final target column in Sage ERP MAS 500 must be required.
• The parameter must prompt the user for a value that can be applied directly to the column;
the user must have selected the value that will go into the column, not a yes/no question indicating that they want a value that is obtained elsewhere. For example, many steps prompt if you want to use the suspense account for missing general ledger accounts that are required. Answering “Use suspense,” however, does not indicate what the suspense account is; therefore, this will not be populated until insert. (It is possible to enhance ACS to do this.)
When parameters are populated into the staging table on extract, they are also rechecked during insertion; therefore, if you add a new row to the staging table and do not specify the values for the extract parameters column, or you delete values from existing rows, the insert repopulates the missing values.
The insert APIs use any available values for parameters that exist in
tsmMigrateStepParamValue. You can populate data into this table before you run an insert API to have parameters applied during your application of the API. Parameters values should be populated as described in Parameter Control Types.
NOTE
The information in this section is provided to help you understand parameters and their metadata; it is not meant to discuss how to add or modify
parameters.
Parameter Control Types
Parameters are displayed to the user on a grid in a certain type of control. By displaying the control to the user and in some cases, providing the user a list of acceptable values (described in the next section), the user's selection can be obtained and made available to the migration routines. The possible parameter control types are as follows:
• Text Box – A field allowing free form entry
• GL Account – The SOTAGLAccount control
• Date – The SOTACalendar control
• Combo Box (both static and dynamic) – Static list uses tsmListValidation entries, dynamic list uses values specified in the parameter definition metadata
• Navigator – The SOTALookup control
• Currency – The SOTACurrency control
• Check Box – Uses the grid's check box control
Every parameter must be one of the types listed above. Based on the type, other columns in tsmMigrateStepParam must adhere to certain rules. In particular, the CtrlAttributes column must conform to a strict layout, depending on the control type. By complying to these rules, the parameters are understood and displayed by ACS.
Parameter Values
There are four parameter types: Invalid Account Option, Invalid Reference Option, Duplicate Option, and Default Value. The Invalid Account Option determines what to do if a general ledger account is not valid, meaning that the account specified does not exist in the general ledger Account list or no account was specified and one is required. The valid values are listed in InsertInvalidGLAcctActionEnum. Similarly, the Invalid Reference Option and Duplicate Options have a set list of values (see InsertInvalidReferenceActionEnum and
InsertDuplicateActionEnum). In general, these three options allow you to choose to fail a record when the situation occurs or to correct it using a standard behavior. Use the suspense account or a blank reference, or update the existing record. All three of these parameters are displayed as check boxes. Default value parameters have specific metadata definition rules, depending on the default parameter type. The metadata definition rules are defined in the next section.
Parameter Metadata Definition Rules
This section provides the metadata rules for default type parameter records. Conforming to these rules enables ACS to properly display and pre-fill any selection choices in the parameter grid. You can skip this section if you do not plan on creating your own parameters.
Control Type CtrlDflt CtrlAttributes
Text Box Any string Not used
GL Account Blank or a valid general ledger account without delimiters
Not used
Date Blank or a valid date Not used
Combo Box – Dynamic Blank or one of the values from the value list (not one of the strings).
The control attributes determines what appears in the combo box. The format is:
“Text=<string1>{:<string2>:…<st ringn>};
Value=<value1>{:<value2>:…<valu en>}”
For example:
"Text=Next:Previous:Today's;
Value=0:1:2"
Combo Box - Static List index of the value you want to display as the default (from
tsmListValidation)
tsmListValidation entry for the table and column of interest. The format is:
“TableName=<tsmListValidation.T ableName value>;
ColumnName=<tsmListValidation.C olumnName value>”
Navigator Blank Navigator information from tsmNavDefCol in the following format:
“NavID=<NavigatorID>;
TableName=<tsmNavDefCol.TableNa me>;
IDColumnName=<tsmNavDefCol.Colu mnName>;
KeyColumnName=<TableName's key column>;
CompanyColumn=<TableName's companyID column, if exists>;
WhichDB=<”app” or “sys” = db' where TableName is located>”
Currency Blank or currency amount Not used
Check Box 0 or 1 Not used
