Data Migration Tool for V8 and V9 [BETA]
This topic explains how to export data from a legacy Retail Pro 8 (V8) or Retail Pro 9 database and import that data into a clean, empty Prism server using the Prism Data Migration Tool. The Data Migration Tool enables retailers currently on V8 or V9 to migrate data to Prism.
How it works
The export process extracts data from the V8 or V9 database tables and writes it to tab separated .csv files. The process creates .7z files from each set of .csv files for a specific legacy table. For example, export of the CUSTOMERS table may produce multiple .csv files that are then combined into a .7z compressed file. Finally, the process will create a single .7z file that combines all the individual compressed files. The tool generates log files that provide information about any .csv file with an error and the data that caused the error. If there are errors during the export (e.g., due to data that has not been properly cleaned), you will have to redo the export. (The Logs/Troubleshooting section of this document has information about common errors and how to fix them.)
Copy the single .7z file to the \PrismTools\Import\ folder of the clean, empty Prism 2.3 server machine for the import. If there are errors during the import, users must correct the errors using their own tools to edit a csv file (e.g., Notepad++ or LibreOffice Spreadsheet). After correcting the .csv file(s), users can resume the import from the point where the error occurred. A table tracks the history of imports and status of the import process.
Limitations
- The Data Migration Tool is for a single export and import of data. Do not use the tool to do a second export-import of overlapping (modified) data, as it will not update, merge, or overwrite existing data. The only data the tool will override/merge with is the basic data included with a clean install of Prism (e.g., Subsidiary 1, Subsidiary 1-Store 0 and Subsidiary 1-Store 1 records).
- The tool is not for replicating data between Prism and a legacy system. After importing data at the Prism root Point of Authority (POA), ordinary Prism-to-Prism replication sends data from the root POA to child POAs and stores.
- To access the import feature in Tech Toolkit, a regular Prism user must be granted the "Allow Data Import" permission in the Prism Group Management module.
- If migrating data from legacy V8/V9 to Prism 2.3, import data first, then activate licensing and verify correct active stores. If you activate licensing first and then import data, there may be a period of time before licensing ‘catches up' to data that was imported.
Challenges
- Legacy data will have to be "cleaned" before the export. This is unavoidable because of differences in the underlying technologies, databases, and data rules.
- Certain records need to be completed before the export. This includes resolving transfers in transfer verification, updating PI sheets, and completing (or discarding) held transactions.
- It is a challenge to complete the export/import in a timely manner (e.g., during the store closing times). As a result, different controllers may be on different versions of Prism at various points and this in turn may temporarily impact the replication of data across the enterprise.
- Importing data takes time, even after reducing the number of records for export. One strategy is to first export/import data like Inventory and Customers and then at a later time import documents. The "Sample Phased Data Migration Process Flow" section of this document has more information about this approach.Some data is not exported, including preferences, permissions, PI sheets and document designs.
- There may be minor differences in the totals and other amounts in the V8/V9 export database and the Prism import database. This is due to differences between the systems in things like rounding of global discounts.
Data Migration - Basic Steps
Export
1. Prepare the database for export by reducing the amount of data and "cleaning" data known to cause errors.
2. Complete "in-progress" records: Unfinished PI sheets, Unfinished/unverified transfers, Held documents, Pending purchase orders and proposed items.
3. Complete a full communications (polling) cycle with stores.
4. Upgrade the data source machine to the latest version of V8 (8.70.400.52 or greater) or v9 (9.40.11.401 or greater).
5. Launch Migrate_Exp.exe. Configure Preferences and start the export.
6. Before importing the data, check the export log to verify errors/warnings and fix the source data to prevent import errors.
Import
1. Install a clean, empty Prism 2.3 (or greater) database on the importing machine (i.e., the machine that will be root authority for the enterprise). Oracle users must also install the latest Oracle patch for Retail Pro/Prism (download at https://my.retailpro.com/Solutions/RPPrism/Downloads/)
2. Assign the required permission (Import & Export - Allow Data Import) to the Admin group.
3. Stop the PrismScheduling service.
4. Copy the .7z export file to the C:\ProgramData\RetailPro\ImportExport\Import\ folder on the Prism server.
5. Launch Prism via the Proxy shortcut, log in, and navigate to Tech Toolkit (separate login required). In Tech Toolkit, navigate to Data Utilities > Data Migration Tool. Select the data source type and select the .7z file. Click the ‘Run' button.
6. If the import process encounters an error, the import will stop. The History screen will display information about the file where the error occurred. Check the log file, fix the error in the .csv file, then resume the import.
7. Complete the post import tasks.
Overview of Export/Import Process to migrate from V8/V9 to Prism:
Requirements/Considerations
Export
- The machine doing the export must meet the minimum system requirements for Prism, plus additional memory. This means a minimum of 8GB of RAM. If the V8 legacy system does not meet the minimum system requirements, copy the data and history files to a machine that meets the requirements and export.
- The \LegacyExport\ folder that contains Migrate_Exp.exe must be downloaded separately.
- Special steps are required to migrate Centrals data from a hybrid V9/Prism 1.14.7 system to Prism 2.3 (See the "Centrals Data Migration - V9/1.14.7 to 2.3" section for details.)
- You can control how many .csv files are generated by the export; a higher LineLimit setting (max 10,000) generates fewer .csv files with more lines per file while a lower LineLimit setting (e.g., 100) generates more .csv files with fewer lines per file.
- V8 export requires defining key information for the new Prism machine into which data will be imported (Subsidiary, Country, Currency, and Price Level).
Import
- Install Prism 2.3 or later server on the machine that will do the import. Activate Licensing. [Allocate licensing before import?]. Grant the required security permission (Import & Export - Allow Data Import) to the user doing the import. This is done in the Prism Employees module
- If migrating data to Prism 2.3, import data first, then activate licensing and verify correct active stores. If you activate licensing first and then import data, there may be a period of time before licensing ‘catches up' to data that was imported.
- The importing machine must be on the latest Oracle patch for Retail Pro/Prism. Download the latest Oracle patch tested for use with Retail Pro/Prism at https://my.retailpro.com/Solutions/RPPrism/Downloads/.
- The .7z export file must be placed in the C:\ProgramData\RetailPro\ImportExport\Import\ folder.
Time Requirements
The time requirements will vary depending on the size and complexity of the data. During testing, the following benchmarks were achieved using the default line limit (5000):
Stores = 39, Inventory = 449,000, Customers = 534,000, History = 7 months
Export time = 13.5 mins total (9 min to export, 4.5 min to zip)
Import time on Oracle = 4.25 hours, Import time on MySQL = 6 hours
The "Sample Phased Data Migration Process Flow" section of this document has information about using a phased approach that mitigates time constraints. The phased approach includes lab testing and creating a timing plan so that data can be migrated in stages.
Export Data
This section includes information about:
- Migrate_Exp.exe, the program that exports data from V8/V9
- Preparing the legacy database for export
- Configuring Migrate_Exp.exe and launching the export
- Post-export tasks
Exported Data
| Documents | Inventory | Customers |
| Employees | Tax settings (except CHAR and Message settings) | Subsidiaries and Stores |
| Sublocations | Centrals balances | Disbursements |
Data not Exported
| Permissions | Preferences | Customer History |
| Promotions | PI sheets | Check In/Check Out |
| Doc Designs | Multi-subsidiary documents | Central credit transactions |
| Pending purchase orders | High Security receipts | Reversed/Reversing documents |
| Held documents | Archived sales orders | Cancelled vouchers |
| Transfer Slips, ASNs and Vouchers containing items no longer in the DB. |
About Migrate_Exp.exe
The Migrate_Exp.exe tool is available in a \LegacyExport\ folder that is a separate download. The \LegacyExport\ folder also includes the 7z.dll required by Migrate_Exp.exe to compress .csv files and the PreProc.exe program used for preprocessing of data.
The first time you launch Migrate_Exp.exe, the program creates the Migrate_Exp.ini configuration file and various subfolders within the \LegacyExport\ folder.
- The \Log\ subfolder will store the export log that is created when you run the export process.
- The\Zip\ subfolder will store the manifest file and the individual .7z files that are compressed into the single .7z file in the root \LegacyExport\ folder.
- The \Exp\ subfolder will store the individual .csv files. The export process appends the .csv files with an incrementing number starting with _1.csv. Depending on the amount of data exported, there may also be _2.csv and _3.csv files and so on. When referring to exported .csv files, this document will always refer to _1.csv. The number of records in each export file is controlled by the LineLimit setting (configurable in the Migrate_Exp.ini file) with a minimum of 3 records/lines to a maximum of 10,000 records/lines per csv file. A low value for the LineLimit setting (e.g., 100) will result in more .csv files. The default is 5,000 lines, which means 4,998 lines of data and two lines of information.
The first two lines of each .csv file have information for the import process. The first line indicates the field name and order for the data. The second line includes instructions about how to create/construct certain fields (e.g., fields that reference other fields in different tables/sources).
.7z Export File Naming Format
The export process uses the following file naming format for the compressed .7z file: V8-
Manifest (.mnf) File
The export process will write a FileList.mnf file that indicates the source of the data as well as the file count and line count for each table. The manifest file is in the \LegacyExport\Zip\ folder. This file should never be edited.
Large Datasets: 64-bit Compression vs. 32-bit Compression
DMT has added the ability to use 64-bit compression (Zip64 setting). The 64-bit zip will accommodate a larger export dataset. The 32-bit compression will take advantage of significantly less memory and for larger datasets it could abort, resulting in zero-byte CSV's and not all the data exported.
By default, the 32-bit compression is enabled to accommodate legacy users on a 32-bit OS. The default 32-bit compression should accommodate most v8 and v9 users.
Prepare DB for Export
To avoid errors during the import, do the following before exporting data:
- Reduce the amount of data to export
- Clean data to fix things that are known to cause errors during export
- Finish incomplete records
Reduce Amount of Data to Export
The export process will export data without filtering (except if using the Filter Resources feature); therefore, it is best reduce the number of records in the database by removing unneeded records. Tools like V8 Clean House and the V9 Tech Toolkit archive/purge feature can assist in removing unneeded records. Here are some guidelines:
V8 users should keep at most two years of document history - sales receipts, receiving vouchers, adjustment memos and transfer slips (this limiting of document history is not possible with V9). Use the Filter Dates feature to send the desired "From" date for each document type.
Purge all filled Sales Orders and Purchase Orders.
Clean Data
- V8 users should first run the Data Verification System (DVS) tool to clean all data.
- Make sure all stores have a Store Name defined.
- Review lists to make sure there are no duplicates or blank entries.
- Make sure Login Names are 30 characters or less. The export tool will truncate Login Names to 30 characters. The export log shows the truncated name. The employee must use the truncated name when logging in to Prism.
- Make sure employee passwords are 30 characters or less. If there are any employee passwords longer than 30 characters, the export will stop and you will have to reset any such passwords to 30 characters or less and then restart the export.
- Make sure Full Names (First Name + Last Name) are 60 characters or less. The export tool will truncate Full Names to 60 characters. The export log shows the truncated name.
- Verify that all gift certificate tenders include a CERTIFICATE_NO by checking the TENDER_GIFT_CERTIFICATE table and make sure the CERTIFICATE_NO column has an entry and add a CERTIFICATE_NO if empty.
Finish Incomplete records:
Physical Inventory sheets. The Migrate_Exp.exe tool will NOT export physical inventory (PI) records.
Resolve transfers in Transfer Verification, then turn off the transfers preference ‘Require Matching In slips to File Pairs,' if enabled. Make sure that Transfer Verification is empty for the duration of the migration. The export process stops if it detects unverified slips.
Approve pending purchase orders and accept into inventory any proposed items. The Migrate_Exp.exe tool will not export pending purchase orders.
Complete (or discard) held documents: The Migrate_Exp.exe tool will not export documents on hold.
Complete a full Polling Cycle
Complete a full polling cycle and verify the Main station (the station that will do the export) received all store data.
Upgrade to the Latest Version of V8/V9
Upgrade to the latest version of V8 (8.70.400.52) or V9 (9.40.11.401) AFTER completing the other "Prepare DB for Export" tasks.
Additional Tasks
- Document Designs: Make a list of document designs. After import, recreate document designs at the Root Point of Authority and replicate the designs to child POAs and stores.
- Note key preferences and permissions: The Migrate_Exp.exe tool will not export preferences and permissions. After the import at the Prism RA, configure preferences and permissions.
Configure Migrate_Exp.exe
Navigate to the \LegacyExport\ folder. Double-click Migrate_Exp.exe to launch.
Select the V8 System or V9 System radio button and click Preferences.
Configuring Migrate_Exp.exe includes the following main settings, some of which are optional, some required:
- (optional) Change the default file paths for the exported .csv and .7z files
- (optional) Set a "Base Export File Name"
- (required for V8) Set paths to V8 data, history, and security administrator files
- (optional for V8) Set defaults for Subsidiary No, Base Currency, Country, and Season
V8 Preferences
Refer to the following tables for information about the V8 preferences.
| Preference | Description |
|---|---|
| CsvFile Path | (required) The path to the exported .csv files. Click the ellipsis button to browse to the folder. |
| ZIP (7z) File Path | (required) The path to the zipped-up file of the individual .csv files. Click the ellipsis button to browse to the folder. |
| Base Export File Name | Used to build the name of the final 7z compressed file. The final 7z compressed file name consists of three parts separated by hyphens: 1)v8, 2) Base Export Name, 3) timestamp Example: Base Export File Name = STORE1_2, final 7z file = v8-STORE1_2-20230924_1220 |
| Path to V8 Data Files | (required) The path to the V8 data files. By default, this is the …\Retail\RPro folder but V8 can be configured to store data files in a different location. |
| Path to V8 History Files | (required) The path to the V8 history (document) files. By default, this is the …\Retail\RPro folder but V8 can be configured to store history files in a different location. |
| Path to V8 SecAdmin Files | (required) The path to the V8 security administrator files. By default, this is the …\Retail\SecAdmin folder V8 can be configured to store security files in a different location. |
| Subsidiary No | The subsidiary number for the V8 data import. |
| Subsidiary Name | The subsidiary name for the V8 data import. |
| Country Code | The ISO 3166 country code of the installation. |
| Currency Code | The ISO 4127 currency code of the base currency used by the installation. The base currency is the default currency used when calculating exchange rates with other currencies. |
| Season | The current season for the calendar used by this installation. |
| Filter Store | Click the Filter Store button to select those stores for which documents, customers and inventory should be exported. (All employees and vendors will be exported regardless of the Filter Store settings. Selecting a store will enable store filtering; TO, PO and SO are filtered by the store they were created at; Transfer Slips are filtered by the from store; Vouchers, ASNs, Sales and returns are filtered by the store they are for; Qty memos are filtered by the store they are for; Cost and Price memos affect all stores so if any store is filtered, they will be exported. |
| Filter Date | Click the Filter Dates tab to define "Import From" dates for: Customer, Inventory, Sales Orders (SO), Purchase Orders (PO), Transfer Orders (TO), Invoices, Adjustment Memos, Transfer Slips and Vouchers. For each record type, select the desired "From" date. Record of that type created after the "From" date will not be included in the export. When using the Filter Date feature, any referenced documents (e.g., purchase orders) must also be within the date range. For example, if a voucher references a PO, both the PO and voucher must fall within the filtered date range. |
Sample V8 Preferences for Migrate_Exp.exe:
About V8 Filter Store
Click the Filter Store button to select those stores for which documents, customers and inventory should be exported. (All employees and vendors will be exported regardless of the V8 Filter Store settings)
About V8 Filter Date
Click the Filter Dates tab to set an "From" date for various record types. The Filter Dates feature has two main purposes:
- For the document types, this features enables users to export a limited amount of document history (e.g., six months of documents)
- For the Customer and Inventory types, after the initial export, export new inventory/customers added after the main export.
Do not filter dates for initial export of Inventory/Customers
The date filter on Inventory/Customers should not be used on initial export. Later, do a date filter export for items/customers added after the main export until today to finish. Do not use date filtering for the main export of inventory.
V9 Preferences
| Preference | Description |
|---|---|
| CSV File Path | The path to the exported .csv files. Click the ellipsis button to browse to the folder. |
| ZIP (7z) File Path | The path to the zipped-up file of the individual .csv files. Click the ellipsis button to browse to the desired folder. |
| Base Export File Name | The Base Export File Name is used to build the name of the final 7z compressed file. Example: Base Export File Name = SBS123, final 7z file = v9-SBS123-20221201_1220 |
| V9 Oracle Db Name | The name of the V9 Oracle database. Default = RProOds |
| Filter Subsidiary | Click the Filter Subsidiary button to select those subsidiaries for which documents, customers and inventory should be exported. (All employees and vendors will be exported regardless of the Filter Subsidiary settings). Enter the database name in preferences before clicking Filter Subsidiary because Migrate_Exp.exe pulls the list of subsidiaries from the entered database. Some tables include SBS_NO but will not be filtered. The SUBSIDIARY and EMPLOYEE tables are examples. |
| Filter Store | Click the Filter Store button to select those stores for which documents and inventory should be exported. (All employees and vendors will be exported regardless of the Filter Store settings. Subsidiary filtering must be enabled to select stores to be filtered. Selecting a store will enable store filtering. Documents for the selected Stores will be exported. Documents for stores that are not selected will not be exported. The stores themselves are not filtered out by the filter; they will still be present. |
| Filter Date |
Click the Filter Date button to define "Import From" dates for: Customer, Inventory, Sales Orders (SO), Purchase Orders (PO), Transfer Orders (TO), Invoices, Adjustment Memos, Transfer Slips and Vouchers. For each record type, select the desired "From" date. Record of that type created after the "From" date will not be included in the export. |
Revert to Original Settings
Click the Revert button to revert preferences to what they were when the user first entered preferences.
About V9 Filter Subsidiary
Click the Filter Subsidiary button to display a list of subsidiaries. When filtering by subsidiary, only the selected subsidiaries will have documents and inventory exported.
Subsidiary filtering allows the filtering of documents and inventory based on subsidiary.
Selecting Filter by Subsidiary will enable subsidiary filtering. Any subsidiaries not selected will be exported with no inventory or documents.
The subsidiary itself will still be exported.
- Enter the database name in preferences before clicking Filter Subsidiary because Migrate_Exp.exe pulls the list of subsidiaries from the entered database.
- All Employees and Vendors will have their data exported.
Some tables include SBS_NO but will not be filtered. The SUBSIDIARY and EMPLOYEE tables are examples.
About V9 Filter Store
Click Filter Store to display a modal for selecting specific stores for the export. Select a Subsidiary from the dropdown and then select individual stores for the selected Sub. If a Subsidiary Filter is also defined, only those filtered subsidiaries will be available in the dropdown. If not filtering by Subsidiary, users can select any Subsidiary in the dropdown and select individual stores for that Subsidiary. For example, you can filter for Sub 1 Store 1, Sub 2 Stores 2 and 3, and Sub 3 Stores 1, 2, 3 and 5.
In both V8 and V9 data migration, the location the document was created at is the store considered when filtering it. If an SO, TO, or PO is created at store 1, for store 3… it will be exported as a store1 document.
TO, PO and SO are filtered by the store they were created
Transfer Slips are filtered by the from store.
Vouchers, ASNS, Sales and returns are filtered by the store they are for
QTY memos are filtered by the store they are for. COST and PRICE memos affect all stores so if any store is filtered they will be exported
Any stores not selected will be exported with no inventory or documents.
About V9 Filter Dates
- Click Filter Dates to set an "Export Date" for various record types. The Filter Dates feature has two main purposes:
- Export a limited amount of document history (e.g., the last six months of documents)
- After the initial export, export new inventory/customers added after the main export.
- Do not filter dates for initial export of Inventory/Customers. Later, do a date filter export for items/customers added after the main export until today to finish. Do not use date filtering for the main export of inventory.
- Date Filter must keep in mind any referenced documents. For example, a PO referenced on a voucher must fall within the date range of the filter.
Export Data
After confirming that preferences are configured correctly, click the Export button.
The tool reads all the data specified in the export paths defined in preferences and exports the data into a set of tab-separated value (.csv) files. The export process zips files twice: first by type, then into one large .7z file.
Before importing data, check the export log to verify errors/warnings and determine if the source data must be fixed to prevent import errors.
Sample exported .csv files:
Export Notes
- The export will .7z compress all the exported files grouped by table.
- By default, the export processes all records from all tables. The Filter Subsidiary feature and the FilterRes setting in Migrate_Exp.ini can modify this behavior.
- The default number of records in a single export file is 5000 (Default). Users can change this (e.g., set the number to a higher value like 10000 by adjusting the LineLimit setting in the Migrate_Exp.ini file. If you decide to change the number of lines per file, keep in mind that more lines per file might make it more difficult to troubleshoot. Fewer lines per file will result in many more files.
- The total number of lines listed includes two lines of header information. For example, if the total number of lines is 5000 (default), it means there are 4998 lines of records plus two lines of header information.
- The Export Completion message alerts the user to the number of errors and warnings within the completed export.
- The tool exports UDF fields, comments and other "lists" entered on documents with those documents and populates most Prism preferences (See "Miscellaneous - About Lists."). Certain preference lists (e.g., PO Instructions, Receipt Comments, Voucher Comments) are not exported/imported. Default UDF's needed for Prism import pertaining to Inventory, Vendors, Customers & Employees will be created during data export.
- The export truncates check numbers longer than 10 digits.
- The export trims leading and trailing spaces. For example, an employee name of: "Employ 1 " is exported as "Employ 1". The exporting system may have non-duplicate records because of these leading/trailing spaces. For example, "Employ 1 " with a trailing space and "Employ 1" without a trailing space. Trimming spaces may create duplicates. In most cases, the export detects the existence of this duplicate condition and writes an entry in the export log. Review the export log after the export. If such records exist, rename the duplicate record, or delete it.
Export Process will Create Records if necessary
There may be instances in which the export recreates data previously deleted in the source system to ensure data integrity during the import. For example, if a document references a department that no longer exists) the tool will create the needed record (in this case, a DCS code) for data import. The export creates the following record types, if necessary: address_type, company, currency, dcs, email_type, phone_type, subsidiary, tax_area, tax_code, till, title, vendor, inventory
Characters that are replaced during Export (MySQL and Oracle):
- "\" is replaced with _in customer names
- " is replaced with _ in description fields
- + is replaced with _ in comments table
Export of Multi-Subsidiary Documents in Legacy Database
This section has information about issues involving multi-subsidiary documents in legacy database (e.g., multi-subsidiary purchase orders or transfer orders). If multi-subsidiary documents/records exist, be sure to include all subsidiaries involved when filtering by subsidiary. For example, if the enterprise has 10 subsidiaries but only four are involved in multi-subsidiary documents, then be sure to select (at least) those four in the Filter Subsidiary modal. It is up to the user to make an informed decision when choosing which subsidiaries to filter. If the user fails to bring in a subsidiary that has required data, the user must reimport.
Example 1:
The database has Sub 001, 002, 003, 004, 005
The user creates multi-subsidiary documents (of any kind) between Sub 001, 003, and 005
The user must import Sub 001, 003, and 005. The user can filter out sub 002 and 004.
Example 2:
The database has Sub 001, 002, 003, 004, 005
The user does not create any multi-subsidiary documents.
The user can filter any of the subsidiaries.
About Differences in .csv File Format before and after Import Processing
The two examples below are the same Tender_Gift_Certificate.CSV file, before and after import processing.
The first image shows the raw exported format before import processing. Line 1 shows the column headers for the data. Line 2 has specific directions for the import processor on how to interpret the data. Line 3 begins the data.
Example export format:
The second image shows the CSV file after import processing has occurred. Line 1 shows the column headers updated to correspond to the Prism table schema. Line 2 has no specific function but maintains row numbering. Line 3 begins the data that is populated with both the export information and the appropriate Prism information required for that record.
Same CSV file after import pre-processing:
Lines 1 and 2 may contain additional information which may be useful in diagnosing from where exported data was derived. If these fields are present, they are typically at the end of lines 1 and 2 but may be in any column. Line 2 will designate the column as "hidden," which means the import ignores the column during the import process.
This information can be useful when trying to determine the source of exported data which causes errors during import.
Install Clean, Empty Prism Database
Prism is available in both Oracle and MySQL versions. Oracle users should download a Prism version that includes the option to install the Oracle database and install the DB first. MySQL users should download and install MySQL 8.0.x first.
- If using Oracle, install the Oracle database and Apache included with the Prism installer before installing the Prism server. Install the latest Oracle Patch for Retail Pro/Prism. The most recent Oracle patch tested for use with Retail Pro and Prism is available for download at https://my.retailpro.com/Solutions/RPPrism/Downloads/. If you are using Oracle as the database and the machine doing the import is not on the latest Oracle patch for Retail Pro/Prism, the import will appear to be successful; however, upon trying to use Prism after the import, the system will be unable to do tasks like creating transactions.
- If using MySQL, install MySQL 8.x and Apache before installing the Prism server and then, when installing the Prism server, select the MySQL option.
Seed Data in New Prism Install
A new clean Prism install includes certain "seed" data like SBS1, SBS1-Store0 and SBS1-Store1.
- V9 uses Subsidiaries so the subsidiary information for V9 is imported as is, overwriting the seed data.
- V8 does not use Subsidiaries. For V8, what happens to the seed data depends on the Subsidiary Number defined in the V8 Preferences area of the Migrate_Exp.exe tool. By default, V8 Preferences have Subsidiary Number set to 1. If V8 Preferences are configured for SBS 1, then the legacy data is imported into SBS1, overwriting the seed data. If V8 Preferences are configured for any subsidiary number other than 1, then the SBS 1 seed data is left as is and the legacy data is imported into the subsidiary specified in V8 Preferences.
The import_export table tracks if SBS1, and SBS1-STORE0 / STORE1 have been updated by an import and if updated will not update them again if another import is run.
SBS1 Seed data that is overwritten during import
The following data is updated for SBS1 with the imported data. If the import file does not contain a record to update existing seed data or does contain such record but some identifying columns (such as "Name") are blank in the import file, the seed data row or the seed columns are left untouched.
- Tax_Codes - 0,1,2
- price_level - Updates the two metadata price level records in price_level table for subs 1
- invn_udf
- customer_udf
- vendor_udf
- employee_udf
- doc_fee_type
- employee = SYSADMIN record only
- Currency records - a currency record is updated with the import record values if the importing legacy currency is active. All columns are updated except the Curr Code.
- Currency denominations
- Tran_fee_types are created for V8 subsidiaries other than sub 1 during import.
Import Data
Stop PrismScheduling Service and set to Manual startup
Before starting the import of the .7z file, launch the Windows Services program and scroll down to the Retail Pro PrismScheduling Service listing.
Click the Stop button and set the Startup type dropdown to Manual. Click OK.
Import Data
1. Copy the .7z file to the …\ProgramData\RetailPro\ImportExport\Import directory on the Prism server (users cannot browse to the file).
2. Log in to Prism Tech Toolkit.
3. In a new Prism installation, there will be a default RPS node listed on the left and it will be selected; otherwise, select the desired node (server) on the left.
4. Select the Import Source and the Import file (.7z file). If the "Delete Zip after import" checkbox is selected, the .7z file in the import folder will be deleted after import completes. Click the Run icon. The data migration tool unpacks the .7z file into the C:\ProgramData\Retailpro\ImportExport\working\
The import will pre-process all files in proper sequence. During pre-processing, the special instructions in each file are executed, temporary indexes and lookup tables are built to enhance performance. The preprocessed data is saved to (i.e., replaces) the original csv file in the working folder. The unprocessed csv files will exist only in the original export zip file.
When pre-processing for a csv file is completed, the data in that file is imported to the appropriate table before the next csv file is processed and loaded to the table. During import, any record in a .csv file that is imported correctly is deleted from the csv file. When a complete .csv file is imported without errors, that .csv file is deleted.
Data Utilities > Data Migration Tool user interface:
Files deleted after successful import
If all the records in the .csv file are successfully imported, the .csv file is automatically deleted. If the "Delete Zip after import" checkbox is selected, the .7z file will also be deleted after the import process completes.
For many errors (not all), if a record (i.e., individual line in the .csv file) has the error, the import process will stop and the .csv file will be retained starting from point of the failed record.
Import Notes
- The administrator doing the import should be at the computer where the data is being imported or connected remotely to that machine (using Remote Desktop). In other words, do not simply connect to the machine via a http(s) connection.
- Depending on the amount of data, it may be necessary to resize data tables before the import.
- Before importing data, stop the PrismScheduling Service.
- In Prism TTK, only one import process can be running at a time.
- The import process will use the same processing order used for initialization, skipping missing files. See the "Import Order" section of this document.
- A table tracks the history of imports and status of the import process. See the "Import History" section of this document.
- If in the legacy system an SO was created at one store then forwarded to another by PROCESS AT, the fulfillment of that SO in Prism may require these steps: 1) Lookup & select the order in Prism. 2) Select ORDER DETAILS. 3) If the FULFILL ORDER button is disabled you will need to select the Fulfillment Store dropdown and select the appropriate store. 4) Select OK (note: you MUST select OK in order for Prism to change the new fulfillment store). 5) Fulfill order can now be completed.
Refresh Button
If you add the .7z file to the ProgramData\RetailPro\Server\Import\ folder after entering Data Migration Tool, you must use the Refresh button to refresh the display so that the file shows up in the Import File drop-down.
Do not use System while Import Process is Running
When the Import process starts, it runs in the background. It is not necessary to keep TTK or the DMT tool open since the process now runs independently. However, do not use Prism or other TTK tools while the Import process is running.
Monitoring the Import Status
Users can monitor the status by occasionally returning to the DataMigration Tool and checking the import "History" record which is updated every 5 seconds when the DMT tool form is open.
As stated above, when not monitoring the status, TechToolkit may be closed and the process will continue unaffected.
Failed Imports
If a row in the current .csv file being processed cannot be loaded, the import stops. All rows that could be loaded in the file will have been loaded already and only those that could not be loaded will remain in the .csv file. This simplifies finding and resolving the error condition.
You can tell when the import stopped due to a data loading issue by checking the Import History grid:
- History record Status will be "Failed".
- The Status message in the grid will be: 'IMPORT STOPPED on data load error in
!' - You must resolve the error(s) before clicking the "Restart/Reprocess" button to resume the import.
Sample Import History grid:
To investigate the problem and correct the error
Navigate to the \LegacyExport\Working\
Next, navigate to the \LegacyExport\Logs\ subfolder and open the log file with the same name as the .csv file. The log file will contain the error message.
Note that "duplicate entry" or "unique constraint violation" messages are not considered errors by the Import process and the process will not stop when these are reported. You can ignore such messages if you happen to see them in one or more log files.
Open the csv file with your preferred editing tool, for example NotePad++ with csvQuery plugin. Only the rows failing to load will be in the file, so it should be easy to find the offending record, as it may be the only one or two in the file.
Perhaps there is a missing value that must be filled in. Deleting the row is ONLY an option if the deletion will not cause subsequent cascading data load errors (cascading errors will occur if the deleted record is referenced in other files). Deletion must be done with great care and knowledge of the database system.
After fixing the .csv file, click the "Restart/Reprocess" button in the History grid to reload that file and continue with the remaining files.
Example
The History grid Status field (not shown) is set to "Failed" and the Details field (shown) has a short description of the error and a reference to the specific .csv file that has the error. If there is more than one import History record, the most recent import will always be at the top of the list.
Navigate to the …\ProgramData\RetailPro\ImportExport\Working\ folder. Inside is the folder with an auto-generated SID for a name.
Open the folder.
Inside is a \Logs\ folder as well as the .csv files for the import. Open the \Logs\ folder.
Locate the log file for the .csv file that caused the error. In our example, we want the scale_pattern_qty_1.log file. (Typically there will only be one or a few log files.)
Note the type of error and the tables/columns that produced the error. This error indicates the import process is unable to link the SCALE_PATTERN_QTY .csv file values with the appropriate SCALE_PATTERN.csv files.
Navigate to …\ProgramData\RetailPro\ImportExport\Working\
Open the .csv file linked to the log file. Edit the specific columns that produced the error as needed. Save the changes.
If you see csv files in the directory prepended with an underscore, it means all rows loaded except for one or more identified as a "duplicate entry" or "unique constraint violation." As mentioned above, these are not considered errors that should stop an import. However, these duplicate rows that were rejected are preserved in the csv file if needed for troubleshooting subsequent errors that might relate to one of the rows being rejected. Otherwise, these "_".csv files can be ignored.
Return to the TTK Data Migration tool and click the Restart/Reprocess button on the import record. This will be the top import record if there was a prior separate import done. The import process resumes from the point of the previous failure. When resuming an import, the server does not unpack the .7z file again but instead looks to the working folder to continue the import from the point of the previous failure.
Post Import Tasks
After successful import of legacy data, complete the following tasks:
1. Join the Enterprise
2. Restart Retail Pro PrismScheduling service.
3. Review Global and Node Preferences.
4. Assign a Home Store to Employees. Configure group permissions and assign employees to groups (V8 Import: Review Employee Names).
5. Review Tax Area CHAR and Message settings for each subsidiary.
6. Recreate Document Designs
7. Start Customer Initialize History Task
The following sections have details about each of these tasks.
Join the Enterprise
All servers except the "root authority" server are joined to the enterprise via a Tech Toolkit process. When ready to join a subordinate server to the enterprise, log in to Tech Toolkit at the POA. On the left side will be a list of servers already joined, if any. Select "Add Subordinate server to
Restart the PrismScheduling service
Restart the PrismScheduling service that you stopped before the import.
Review Global and Node Preferences
Some preferences are exported/imported. This includes Currencies, Price Levels, UDF fields and Tax Codes. Most preferences are not exported/imported. Go into Global and Node Preferences and edit Prism's default settings as needed. The "About Lists" section of this document has information about preferences exported/imported.
Assign a Home Store to employees and configure group permissions
Employees and employee group names are exported/imported; however, employee group assignments and permissions are not. After the export, assign the appropriate permissions to each employee group and assign employees to groups. Each employee in Prism must be assigned a Home Store in the employee record.
Review Tax Area CHAR and Message
For each subsidiary, go into Admin Console > Node Preferences > Taxes > Tax Areas and define CHAR and Message columns for each tax area in the tax rules section.
Recreate Document Designs
Document designs must be recreated at the RA and distributed throughout the enterprise after the import.
Start Customer History Initialize task in the Scheduler.
After all documents (i.e., invoices, orders, vouchers, etc.) have been imported to a server, it is necessary to generate Prism customer history records from the legacy documents. This is done by the "Customer History Initialize" task in the TechToolkit Scheduler when the task is set to active.
Generating customer history can be done before or after joining the enterprise - but must be done at the server where the history documents were imported, whether that is the POA or the subordinate. After customer history records have been created, they can be replicated to other servers in the enterprise (without having to replicate the entire collection of document and tender tables).
Choosing the Document Transaction Date to Begin Generating CH records
By default, customer history records will be created from customer POS transactions, orders, etc. that have a created or modified date of January 1, 2016 or more recent, if the earliest date found is more recent.
This "Start Date" can be changed to earlier or later, if desired, prior to starting the CH task. Editing the Start Date,
Log into TechToolkit, go to the Scheduler and scroll to the Customer History Initialize task.
Change the Start Date to the desired document transaction date for the process to begin with and save the record.
This must be done before the task is set to Active and started. (Once the task starts, changing the Start Date has no effect.)
Note that the default Start Date appearing in the Scheduler will be some date in January 2016. Any Start Date in January 2016 "tells" the CH process to begin with January 1, 2016. So it is not necessary to change the date if January 1, 2016 is acceptable as the beginning transaction date.
- Starting the Task: Place a check mark in the Active column of the task and save the change. This task will start within 60 seconds.
- Monitoring Progress: Customer history generation begins with the earliest date and "crawls" forward in time processing 5-to-10-day batches of transactions at a time. After processing a batch, it returns to the Scheduler and updates the task's history record which can viewed to see the progress. The task will be set to inactive when completed. It runs only once.
- Pausing the CH Initialize task during working hours: If there are tens of millions of history records to process, the task may take more than a day or two days to complete. The task's workload is performed by the e PrismBackOffice service. If this impacts performance of this service during working hours, the task can be paused by two methods: Set a task End Time of, for example, 8:00 A.M. and save the record. The task will stop running at or about 8:00 A.M. and automatically resume around midnight, or manually set the tasks to not active in the Scheduler during working hours then turn it back on during the off-hours so it resumes where it left off.
- Import History List: The Import History grid is positioned at the bottom of the Data Migration Tool window. Initially it will be empty. When the user clicks the "Run" button to start an import, the import process first performs several quick validations and will return a toast message if appropriate. When all validations pass, a new import record is created and will display in the History grid. From the History screen, users can a) Get details for troubleshooting (if an import fails), b) Resume a failed import (after making corrections to .csv files), c) Check the status of the import(s). The status is updated every 5 seconds. d) Clear the files, logs and folders associated with a previously completed.
Clear Files
The import process results in the creation of various folders and log files in addition to the unzipping of many compressed files. Clicking the Clear Files button will delete these unzipped .csv files from the \working folder. In addition, the Clear Files feature will delete log files and folders.
Sample Phased Data Migration Process Flow
The following sample phased migration approach involves first exporting/importing non-document data like Inventory and Customers. This provides stores with the data needed to ring up sales. Later, export/import documents. This phased approach is accomplished by manually editing the FilterRes setting of the Migrate_Exp.ini file to export specific data types.
Before "going live," test the export/import and record the time required for the process. You can then use the information when planning the go live cutover.
Step 1. Prepare database for export
The "Prepare DB for Export" section of this document has information about how to reduce the amount of data to export and clean data known to cause errors.
Upgrade to the latest version of V8 (8.70.400.52) or V9 (9.40.11.401) AFTER completing the other "Prepare DB for Export" tasks.
Step 2. Export/Import Non-Document Records
1. On the legacy system, edit the FilterRes setting of the Migrate_Exp.ini file so that documents are not exported. To do this, set Core, Lty, Cust and Invn to "1" and set all the Docs to "0."
2. Export data. Fix data with errors or warnings. Repeat until all errors or warnings are cleared (successful export).
3. Install Prism 2.3 and activate licensing.
4. Import non-document data. Assign a Home Store to each employee in the Prism employee record. Verify data using Prism Customer Lookup, Item Lookup, etc., and tools like PLSQL Developer or MySQL Workbench.
Step 3. Export/Import Documents
1. On the legacy system, edit the FilterRes setting of the Migrate_Exp.ini file so that documents are exported. To do this, set Core, Lty, Cust and Invn to "0" and set all the Docs to "1."
2. Export data. Fix data with errors or warnings. Repeat until all errors or warnings are cleared (successful export).
3. Import the document data and verify using Transaction Lookup, Voucher Lookup, Slip Lookup, Purchase Order Lookup, etc. to do a spot check of individual documents.
Notes about Migrating Specific Data Types
Lists
List information defined in preferences (user-defined fields, comments, instructions, fee types, price levels, shipping methods, etc.) and entered on documents will be exported with those documents and most lists are populated in Prism preferences. If two or more exported list entries are identical except for uppercase/lowercase, the export tool recognizes each list entry separately. Any list entries created by the export will be inactive.
Lists that are populated in Prism Preferences:
- Currencies and denominations
- Tax codes
- Tax areas (except the CHAR and Message columns)
- Adjustment reasons
- Discount reasons
- User-defined 1-4 (Customer, Employee, Inventory, Vendor)
- Price levels
Before export, review lists in the legacy database to make sure there are no duplicates or blank entries. Duplicate or blank entries may be flagged during import. The following table shows the lists and where each list type is defined in V8, V9 and Prism. Lists that are imported into Prism (and don't need to be recreated) are marked with an asterisk (*). All other lists must be recreated in Prism after the import in order to use them on documents.
| List Type | V8 Path | V9 Path | Prism Path |
|---|---|---|---|
| Adjustment Reasons* | System Preferences > Adjustments | System Preferences > Local Preferences > Merchandise > Adjustments | Admin Console > Node Preferences > System > Reasons and Notes |
| Customer UDF* | System Preferences > Point of Sale > Customers >User-Defined System Preferences > Point of Sale > Customers > Aux Fields |
System Preferences > Local Preferences > Customers > UDF/Aux | Admin Console > Node Preferences > Customers > UDF Fields |
| Customer Class | System Preferences > Point of Sale > Customers > Price/Discounts | System Preferences > Local Preferences > Customers > General | N/A |
| Disbursement Reasons | N/A | System Preferences > Local Preferences > Point of Sale > Receipts > Disbursement Options | Admin Console > Node Preferences > System > Reasons and Notes |
| Discount Reasons* | System Preferences > Point of Sale > Price/Discounts | System Preferences > Local Preferences > Point of Sale > General > Price/Discounts | Admin Console > Node Preferences > System > Reasons and Notes |
| Drawers | System Preferences > Employees | System Preferences > Local Preferences > Point of Sale > General > Options | Admin Console > Node Preferences > Reporting > X/Z-Out |
| Employee UDF* | N/A | System Preferences > Local Preferences > Employees > UDF/Aux | Admin Console > Node Preferences > Employee > UDF Fields |
| Inventory UDF* | System Preferences > Merchandise > User-Defined System Preferences > Merchandise > Auxiliary |
System Preferences > Local Preferences > Merchandise > User-defined/Auxiliary | Admin Console > Node Preferences > Merchandise > Inventory > UDF Fields |
| Item Notes (predefined) | N/A | System Preferences > Local Preferences > Documents > General | N/A (10 item notes can be entered on each transaction, but no predefined entries) |
| PO/Voucher Fee Types | System Preferences > Purchasing | System Preferences > Local Preferences > Purchasing > General | Admin Console > Node Preferences > Purchasing > Purchase Orders |
| PO Instructions | System Preferences > Purchasing > Comments | System Preferences > Local Preferences > Purchasing > Comments | Admin Console > Node Preferences > Purchasing > Purchase Orders |
| POS Fee Types | System Preferences > Point of Sale > Fees/Shipment | System Preferences > Local Preferences > Point of Sale > General >Fees/Shipment | Admin Console > Node Preferences > Transactions > Fees/Shipment |
| POS Flags | System Preferences > Point of Sale > POS Flags/Note Field | System Preferences > Local Preferences > Point of Sale > General >POS Flags/Note Fields | Admin Console > Node Preferences > Transactions > POS Flag/Note |
| Price Levels* | System Preferences > Merchandise > Pricing > Advanced | System Preferences > Local Preferences > Merchandise > Pricing > General | Admin Console > Node Preferences > Merchandise > Pricing > Price Levels |
| Price Rounding Instructions | System Preferences > Merchandise > Pricing | System Preferences > Local Preferences > Merchandise > Pricing > Advanced | Admin Console > Node Preferences > Transactions > Tenders > Requirements |
| Receipt Comments | System Preferences > Point of Sale > Receipts > Comments/Terms | System Preferences > Local Preferences > Point of Sale > Receipts > Comments | N/A The Comments available in Prism Transaction Details are those replicated from RIL. |
| Reason Codes | N/A |
System Preferences > Local Preferences > Documents > Reason Codes |
Admin Console > Node Preferences > System > Reasons |
| Shipping Methods | System Preferences > Point of Sale > Sales Orders > Options | System Preferences > Local Preferences > Point of Sale > Sales Orders > Options | Admin Console > Node Preferences > Transactions > Fees/Shipment |
| SO Comments and Instructions | System Preferences > Point of Sale > Sales Orders > Comments | System Preferences > Local Preferences > Point of Sale > Sales Orders > Comments | Admin Console > Node Preferences > System > Reasons and Notes |
| Tax Areas* | System Preferences > Tax Areas | System Preferences > Local Preferences > Taxes > Tax Areas | Admin Console > Node Preferences > Taxes > Tax Areas. |
| Tax Codes* | System Preferences > Tax Areas | System Preferences > Local Preferences > Taxes > Tax Codes | Admin Console > Node Preferences > Taxes > Tax Codes |
| Titles | System Preferences > Point of Sale > Customers | System Preferences > Local Preferences > System > Titles | Admin Console > Node Preferences > Data Types |
| Transfer Fee Types | N/A | System Preferences > Local Preferences > Transfers > General | Admin Console > Node Preferences > Transfers > General |
| Transfer Slip Comments | System Preferences > Transfers | System Preferences > Local Preferences > Transfers > Comments/Reason | Admin Console > Node Preferences > Transfers > Comments |
| Vendor UDF* | System Preferences > Merchandise > Vendors > User-Defined | System Preferences > Local Preferences > Merchandise > Vendors > User-Defined | Admin Console > Node Preferences > Merchandise > Vendor > UDF Fields |
The following table has information about the export/import of individual data types:
| Data Type | V8 | V9 |
|---|---|---|
| Adjustment Memos | Memos that contain items that are not in their subsidiary are not exported. The Export Log notifies the user that the memo has been eliminated. | |
| Centrals | Central Credit: The Export/Import tool does not export central credit transactions, only central credit balances. When exporting/importing data, the central credit amount is imported for the customer who is currently assigned that Central Credit ID (i.e., the customer who used it last). Central Gift Cards: The Export/Import tools does export central gift card transactions (but not held transactions). Central Gift Certificates: Central gift certificates are not exported and are not supported in Prism. |
|
| Currencies | For V8, in Migrate_Exp.exe preferences, define a Default Currency Code and Currency Name so that the subsidiary into which the V8 data is being imported will have the correct base currency. | For V9, Currencies and denominations are among the lists that are exported/imported (no need to recreate the lists). After successful import users can view currencies and denominations in Prism Admin Console > Global Preferences > Currencies. Foreign currencies are handled differently in Prism and legacy systems. For example, V9 invoices do not use FC (foreign currency) tenders; instead, V9 puts foreign currencies in as a cash tender but with some extra information. The Migrate_Exp.exe tool will convert these to foreign currency records in Prism. All Custom currency names are migrated except for the currency that has the USD Currency Abbreviation, which will default back to U.S. Dollars during the Import. |
| Customers | The following customer-related tables are exported from V8: customer customer_address customer_email customer_extend customer_phone Customer Names: Full Name in Retail Pro 8, First Name, Last Name in Prism V8 customer records have a Full Name field; this field does not exist in Prism. Prism has First Name and Last Name fields that do not exist in V8. When importing V8 customers, the Full Name will be copied to the Last Name field and truncated if it doesn't fit. |
The v9 max_disc_perc is mapped to the Prism suggested_disc_perc. The v9 disc_perc_upper_limit is mapped to the Prism max_disc_perc. In V9, all customer records get a customer id, even if it is for employee data. In Prism, the customer record only gets a customer id if it is an actual customer. To explain further, Prism employees are their own type of record but are also a subset of customer records. The CUST_TYPE flag on the customer record indicates if the customer is also an employee. When importing data, the relative order is: First the import process imports customers who are employees (CUST_TYPE=1, in the 1st_customer_1.csv file). After that (relative order, not sequential), the import process imports employees. Last, the import process imports customer records with CUST_TYPE=0. |
| Customer History | Not Exported | Not Exported |
| Dates | The V8 invoice's Created Date populates both the INVC_POST_DATE and the CREATED_DATE fields in the Prism document table. The V8 invoice's Post Date populates the POST_DATE field in the Prism document table and the V8 Last Edit Date populates the MODIFIED_DATE field in the document table. | |
| Disbursements | In legacy systems, disbursements are included among the various "high security receipts." The following is the list of High Security receipt types; however, only the "disbursement" types 4, 12 and 13 (Cash Drop, Paid Out, Paid In) are exported. Here is the full list: 1 = exit pos, 2 = open drawer, 3 = reboot, 4 = cash drop, 5 = manager override, 6 = decrypt card, 7 = audit, 8 = no value, 9 = no value, 10 = open register, 11 = close register, 12 = paid out, 13 = paid in, 14 = misc drawer open, 15 = no value, 16 = no value. |
|
| Employeees | For v8 employees, only a Full Name field exists. The V8 Full Name is imported into the Prism employee Last Name. If the V8 Full Name exceeds the max length of the Prism Last Name field, the data will be truncated. The following employee-related tables are exported from V8: employee employee_extend employee_subsidiary employee_store In Prism, employees can be regular users, or sysadmin users (sysadmin users have the System Admin checkbox in employee record selected). By default, employees are regular users. V8 has a single sysadmin user, but the V8 Security Administrator program (SecAdmin.exe) allows users to be regular users or power users. During the import, the tool will: Look for a user id of 0 (this identifies the single V8 sysadmin user) Look at the User Class value: admin (0), power user (1), or user (2). If the user id is 0, or if the user class is 0 or 1, the employee will be flagged as sysadmin when migrated into Prism. |
The following employee-related tables are exported from V9: employee employee_subsidiary employee_store user_group_user Notes Assign a Home Store in the Prism employee record after data import. Login names longer than 30 characters will be truncated. The export log will show the truncated name and the employee must use the 30-character truncated name when logging in to Prism. If a password is longer than 30 characters, the export will halt and the export log will notify the user of ALL employee passwords that exceed the limit and need to be changed. Reset any such passwords to 30 characters or fewer and then restart the export. Full Names (First Name + Last Name) longer than 60 characters will be truncated during export. |
| Gift Certificates | Gift Certificate numbers are truncated to 20 characters upon import into Prism, if needed. The last 20 characters are retained. Before exporting data, verify that all gift certificate tenders include a certificate number by checking the tender_gift_certificate table. Verify the certificate_no column has an entry and add a certificate_no if empty. The import process will add a certificate_no of ‘00000000' if a blank or null entry is encountered. After the import, you can edit those ‘00000000' numbers if desired. |
|
| Images | N/A | Export item, style, and customer images from V9 using ImageExporter.exe. The ImageExporter.exe tool is available in the \Program Files (x86)\RetailPro\ImageExporter\ folder of a Prism server install. |
| Inventory | When recreating items, the following default values are used: MAX_DISC_PER1 and MAX_DISC_PERC2 default to 100. The SERIAL_TYPE, REGIONAL, LOT_TYPE, NON_INVENTORY and NON_COMMIT fields all default to zero. |
|
| Loyalty | In V8, customer loyalty feature is an "off-the-shelf" tool, separate from core V8; therefore, loyalty information is not exportable from V8. | The following loyalty-related tables are exported from V9: lty_level lty_level_program lty_cust_central Due to differences between V9 and Prism Loyalty, false duplicate errors may occur during the export/import process. If the Export Log shows warnings that duplicate programs or levels exist, it is likely that previously deleted programs or levels have the same name as active programs or levels. To fix this, edit the appropriate CSV(s) by changing the "name" field of inactive records that have the same name as an active Loyalty Program or Loyalty Level. |
| Min/Max | The values from the min_qty, max_qty, new_min_qty, new_max_qty fields in the invn_sbs_qty table are included in the export. Their values will populate the invn_minmax, and invn_minmax_qty tables in Prism after migration is complete. | |
| Notes | The NOTES_GENERAL field in the DOCUMENT csv is truncated to 250 characters by the export tool. There will be warnings in the Export Log, but the export will continue. The NOTES_GENERAL field corresponds to the Notes text box on documents like receipts and sales orders. After the export, review the Export Log. If there are warnings indicating note text has been truncated, review the relevant records in the legacy system and decide whether the truncated note text is enough. | |
| Packages and Kits | Packages and Kits are exported. Kit component item information is exported to kit_component_1.csv | |
| Physical Inventory | Physical Inventory sheets are not exported. When preparing the legacy database for export, update any physical inventory sheets that are in progress. Physical Inventory Import Maps are exported. |
|
| Price Levels | A requirement for V8 export is to first define the default price level in Migrate_Exp.exe V8 preferences area (or in the Migrate_Exp.ini file). This price level will be assigned to the Default Subsidiary entered in V8 preferences and can be viewed/edited in the Prism Subsidiary record. | In V9, price level is applied at the item level on documents. In Prism, price level is applied at the transaction level; however, individual items on a Prism transaction can have a different price level from the transaction price level. Because price level information is not exported from V9, the price level on migrated transactions will be blank in Prism. |
| Purchase Orders |
Multi-subsidiary purchase orders are not exported |
|
| Regional Inventory | Data Migration Tool will only export Corp Region data. | |
| Sales Orders | Held sales orders are not exported. Do not try to use imported registries until the registry feature is available in Prism. Reversed and Reversing receipts are not exported. If a sales order deposit was taken and that deposit was subsequently reversed, the Reversed and Reversing sales receipts which recorded the cancelation will also not be exported and thus not show up in Deposit History for the order in Prism. Because these documents cancelled each other out this has no impact other than a loss of the historical record of the transaction and its reversal. If there is no Bill To Customer on the document, export will copy the Ship To info to the Bill To fields (so Prism can display customer name on the Document Search). If no Bill To or Ship To customer exists on the document, the import process will create a "dummy" customer (all such SOs will reference THE SAME dummy customer). To find imported orders using Prism Transaction Lookup, enter the full 14-digit SO number in the Document Number field of the lookup. The SO# in v8 was 10 characters with the four-digit store/station prepended to that number. SO Note fields are truncated to 250 characters. There will be warnings in the Export Log, but the export will continue. When an order is imported into Prism, if the Fulfilling Store is different from the originating store, the document cannot be fulfilled. |
|
| Serial Numbers | The Export tool logs an error if there are any wildcard characters (*) in serial numbers. | |
| Sublocations | Sublocations are not available in V8. | If exporting from a V9 that is using sublocations, enable Sublocations in Prism BEFORE importing data. If enabled, the import will create sublocations and appropriately distribute inventory quantities. If not enabled before the import, the import will fail. |
| Subsidiaries | A new, clean Prism install includes certain "seed" data like SBS1, SBS1-Store0 and SBS1-Store1. V8 does not use subsidiaries. Users must enter a Default Subsidiary Number in V8 Preferences of Migrate_Exp.exe: By default, V8 Preferences have Subsidiary No set to 1, which means the legacy data will be imported into Prism SBS1, overwriting the seed data. If V8 Preferences are configured for any other subsidiary number, then the SBS1 seed data is left as is and the legacy data will be imported into the specified sub. |
|
| SYSADMIN | The employee record of the single SYSADMIN user (username "sysadmin") is updated by all the values in the import record, including password. The Prism SYSADMIN employee name and username / login name will remain as "SYSADMIN", even if the legacy name used was "Admin". This update will occur for each import of employee records that contains a SYSADMIN record with SYSADMIN login name, Active status and password in the csv file being imported. If there is a Sysadmin employee name record with a login name that is blank or anything other than SYSADMIN and that has an Inactive status in the csv file being imported, the record will be added to the employee table. | |
| Tax | Tax codes and tax areas (along with their associated tax rules) are among the "lists" that are exported/imported (no need to recreate the lists). After successful import, users can view tax codes and tax areas in Prism Admin Console > Node Preferences > Taxes. However, there are two pieces of information in Tax Rules that are NOT IMPORTED: CHAR and Message. After the import, users must go into Prism Admin Console > Nodes > Taxes > Tax Areas for each subsidiary and define the CHAR & Message column for each tax area in the Tax Rules. The tax percent that is displayed at the document level is derived from the tax percentages of the document items. If all the items have the same tax percent, it will be displayed at the document level. If they are different, it will not display a tax percent at the document level. The item tax percentages are migrated correctly. |
|
| Transfer Slips | Reversed and reversing slips are not exported. | Reversed and Reversing transfer slips are not exported Multi-subsidiary transfer orders are not exported TAX_AREA_SID and TAX_AREA1 are not available for Slip items in V9; these values will therefore be blank on V9 slips imported into Prism. Slips that contain items that are not in their subsidiary are not exported. The Export Log notifies the user that the slip has been eliminated |
| Transfer Verification | All transfers must be verified before an export can be completed. Unverified transfers will cause an error during import | All transfers must be verified before an export can be completed. Unverified transfers will cause an error during import |
| User-defined fields | UDF Date1 and UDF Date 2 are migrated as into UDF1_date and UDF2_date; UDF 3 and UDF 4 are migrated into UDF1_string and UDF2_string (these are not in the PRISM UI); UDFS 5-UDF 20 are migrated into UDF3-18 | |
| Vendor Invoices | All vendor invoices and vendor invoice numbers are exported from V8. In V8, vendor invoice numbers can contain both letters and numbers. For example, some retailers prepend the number with a location identifier like ABQ for Albuquerque. Vendor invoice numbers are case-sensitive, so ABQ1234 and abq1234 are considered different numbers. In addition, "duplicates" are allowed so multiple vendor invoices can have number ABQ1234, with each invoice linked to a different voucher. | |
| Vouchers | Held vouchers are not exported. | Held Vouchers are not exported Vouchers that contain items that are not in their subsidiary are not exported. The Export Log notifies the user that the voucher has been removed. There are three store fields in the V9 voucher table. The same three store fields are in the Prism voucher table. The following three store fields are exported if they have information in V9: STORE_NO, ORIG_STORE_NO, SLIP_STORE_NO |
Logs/Troubleshooting
The following logs are generated by the Data Migration Tool:
- Export Log
- Import Logs
- TechToolkit logs
Export Log
For the data export, a single log file is generated and placed in the PrismTools\LegacyExport\Log folder. The Migrate_Exp.exe tool will perform a basic check of the data and the log may include warnings if any potential problems are found.
Sample Export Log:
Import Logs
When a user runs the import process, the tool creates a "\Logs" folder and as data is imported, the process writes entries to the log file(s) regarding success, warnings, and errors-failure to insert a row.
If all rows in a csv file load successfully, the usual case, the log file and the processed csv file are deleted. Only log files with errors causing data not to load are preserved.
As stated above, the import process stops when any data row fails to load. The user then must look at the log file for that import file and determine the problem. The user must then fix the data error in the .csv file, if possible. A string of arrow characters "<<<…" indicates errors captured in the log file. Therefore, to search the log file for errors, search for a string like "<<<".
To access the logs, navigate to the C:\ProgramData\Retailpro\ImportExport\Working\
Tech Toolkit Logs
Some errors and warnings may only show up in the TechToolkitService logs. Review the TechToolkitService log to make sure there are no errors. These errors and warnings are more likely to pertain to data in a record that was missing but that did not prevent the record from loading.
To access the Logs area of TTK, the default username/password is loguser/loguser. Then select the PrismTechToolkit log file(s) for the dates the import was run.
Export Warnings/Errors
| Problem | Descripton/Example | Solution |
|---|---|---|
| Duplicate key | >>>> Warning: Type= tRpExpListReason, Resource= pref_reason, Message= Duplicate Key=2 : 5 : 10 | This is telling the user that in the "pref_reason.csv" there are duplicates for "Key=2 : 5: 10". These keys correspond to line 1 of the export file - lkup_sbs_sid : reason_type : reason_id. Therefore, this warning is for Subsidiary 2, Reason Type 5 & Reason ID 10. If you look for this combination of reason codes (2, 5 & 10), it would be apparent that it was exported more than once into the CSV. These warnings should have no impact on data migration. |
| Duplicate key for employee | Starting: employee >>>> Warning: Type= tRpExpListEmplNam, Resource= employee, Message= Duplicate Key=1 : sp |
This warning during export may indicate errors when employees are imported. In this case a v8 Employee Nickname had a trailing space which made the name unique. However, leading and trailing spaces are trimmed to ensure data integrity in Prism. In this case after trimming the space there was one or more duplicate Nicknames, thus the warning. Correct the CSV file by renaming or deleting the duplicate(s). |
| Missing Department Code | Starting: document Processing: SA202210.Dat Adding Missing Dept Code: G T R |
This message is telling the user that an inventory item was added to a receipt, then subsequently the items DCS code as changed or deleted. To maintain data base integrity, the DCS code that no longer exists will be added to the department CSV file so it can be imported. (The deleted/changed DCS will appear as a new department in Prism) |
| Invalid DCS Code or Name | >>>> Warning: Invalid Name for: S N (DName= Serial , CName= Stamps , SName= ) | For DCS there may be a warning that either D, C or S (code or name) is blank. In the example, the Subclass Name (SName) was blank. The user can ignore these warnings; they are simply there to alert the user of this condition. |
| Export warning ‘missing subsidiary record' | >>>> Warning: Type= tRpExpListSubs, Resource= subsidiary, Message= Missing Key=3 Adding Missing Subsidiary: 3 Root cause: At some point, a Subsidiary existed in RP9 which was subsequently deleted by executing the ‘RP9 TTK > Delete Subsidiary' function. |
This warning can be ignored, but if ignored, a dummy subsidiary 3 will be imported to ensure all employees have proper references. This dummy subsidiary cannot in Prism be deleted or reused because it does not have related child records (e.g., default price level). To prevent this, correct the employee assignments in RP9 TTK > SQL Shell by executing queries. Where HOME_SBS_NO or HOME_SBS_NO equals the deleted subsidiary set them to match the SBS_NO. Optionally delete the dummy subsidiary(ies) and change the employee assignments in their respective CSV files. |
| Processing of export is halted | "Unverifed Slip, Processing is Halting. Please Fix this as processing cannot continue!" | All unverified slips must be resolved before an export can be completed. |
| Employee password of full name exceeds maximum allowed number of characters | Either one of these errors will halt the export and the employee record must be corrected before an export can be completed: >>>>>>>> Error: Employee "Employee1" has a Password that is longer than 30 characters, Processing is Halting. Please fix this, as processing cannot continue! >>>>>>>> Error: Employee "Employee2" has a Full name is longer than 60 characters, Processing is Halting. Please fix this, as processing cannot continue! |
Edit the designated employee record to shorten the Employee name or the Employee password, then re-export Note: The Export will stop processing when the first error is encountered, only one error will be identified per export attempt. |
| Employee login name exceeds maximum allowed number of characters | >>>> Warning: Employee "LongName" has a User name that is longer than 30 characters >>>> Warning: Changed From: "VeryLongLoginName12345678901234" >>>> Warning: Changed To: "VeryLongLoginName1234567890123" |
Because the User/Login name is not editable, the tool will truncate the Login Name to 30 characters to facilitate the import operation. From this point, the employee must use the 30-character truncated name when logging in to Prism. |
| Duplicate Loyalty Programs or Levels exist | Due to differences between RP9 & Prism Loyalty, there is the possibility that false duplicate errors may occur during the export/import process. | If the Export log indicates warnings that duplicate Loyalty Programs or Loyalty Levels exist it is very likely that previously deleted programs or levels have the same name as active ones. Although the records in RP9 should not have allowed deletion unless they were unassigned to programs, customers, or inventory, it is best to alter the exported CSV files to ensure all records import (whether active or not) to ensure integrity of the data. If records are inactive AND have the same name as an active Loyalty Program or Loyalty Level, alter the inactive records "name" field to a unique name. |
| When exporting data, users receive a warning that a document referenced by another document cannot be found. | File: PO0000.DAT >>>> Warning: Type= tRpExpListDocSoNo, Resource= document, Message= Missing Key=1 : 00000000000032 |
1. Note the document type being processed: PO0000.DAT. This tells us that the problem is with a purchase order (PO). 2. Note the "tRpExpList" ends with DocSoNo. This tells us that the problem is a PO that references a sales order (SO). 3. The "Missing Key=1 : 00000000000032" means that the export process was looking for a document for subsidiary 1. 4. The "Missing Key=1 : 00000000000032" means SO#32 has been deleted. 5. Correct the CSV file. |
| V9 version requires upgrade | "Error "SOI"."INCLUDE_ON_PO": invalid identifier" | This error occurs when the version of V9 hasn't been upgraded, or when the executables are updated but the DB scripts do not complete for some reason. The fix is to re-run the MP. |
| Duplicate loyalty programs or levels exist. | Due to differences between V9 and Prism Loyalty, there is the possibility that false duplicate errors may occur during the export/import process. | If the Export Log indicates warnings that duplicate Loyalty Programs or Loyalty Levels exist it is very likely that previously deleted programs or levels have the same name as active ones. Although the records in V9 should not have allowed deletion unless they were unassigned to programs, customers, or inventory, it is best to alter the exported CSV files to ensure all records import (whether active or not) to ensure integrity of the data. If records are inactive AND have the same name as an active Loyalty Program or Loyalty Level, alter the inactive records "name" field to a unique name. |
| Available memory, message count info during customer export | Processing Resource: customer 20230410-132446.131 AvailVirtual: 4058456064 20230410-132447.695 Records To Process: 1723492 20230410-132945.944 Type= tRpExpListCustomer, Resource= customer, Message= Count: 524288 20230410-133236.900 Type= tRpExpListCustomer, Resource= customer, Message= Count: 1048576 20230410-133532.797 customer, Records Added: 1724182 20230410-133532.797 AvailVirtual: 3833798656 |
No action is needed when this information is displayed. |
| Serial # exists for item no longer in Inventory | >>>> Warning: Serial# with base inventory item missing. Not exporting: Item_Sid= 6729521167981347056, Serial#= 4185 | This warning means that during export a Serial# existed for an item that is no longer in inventory. This type of message can also appear in the export log for Kit Items, Lot #, and Multi-Vendor items that are missing their parent record due to some type of corruption. There is no action required, the information alerts to information that has been orphaned. It will not be exported because it cannot be imported without the parent inventory item |
| Dummy item created because a deleted item was referenced on PO | File: PO0000.DAT >>>> Warning: Type= tRpExpListDocSoNo, Resource= document, Message= Missing Key= 1 : 00000000000032 >>>> Warning: Type= tRpExpListInvnItemNo, Resource= invn_sbs_item, Message= Missing Key= 1 : 63 >>>> Warning: Adding Dummy Item |
(v8 only) V8 Inventory items are allowed to be deleted. If the retailer deleted an item that was referenced on a PO, we create a dummy item in order to maintain the integrity of the document
|
Import Warnings/Errors
| Problem | Example | Solution |
|---|---|---|
| Unique constraint or "Duplicate entry" | Unique constraint errors occur when duplicate records are found when the records must be unique. "Duplicate entry" is the log message generated by imports to MySQL for the same condition. |
When there is a unique constraint error during import, a log entry notes the .csv file and other information to help users locate the error. These are considered valid rejections, not true error conditions, and usually can be ignored. The import does not stop for unique constraint or duplicate entry "errors". |
| Integrity constraint | Integrity constraint errors occur when a foreign key value has no matching primary key value. | Make sure the matching primary key value is present in the table being referenced. |
| Foreign key constraint | Foreign key constraint errors occur when the values being put into a table are not available in the referencing (parent) table. Example: Cannot add or update a child row: a foreign key constraint fails (`rpsods`.`slip_item`, CONSTRAINT `item_SlipItem` FOREIGN KEY (`item_sid`) REFERENCES `invn_sbs_item` (`sid`)) |
To solve this type of problem, make sure the parent table being referenced includes the needed values. |
| Purchase order or transfer order that includes an item deleted from Inventory | If a PO or TO includes an item no longer in Inventory, the import will stop and an error like the following will be displayed: Record 1: Rejected - Error on table PO_ITEM, column ITEM_SID. ORA-01400: cannot insert NULL into ("RPS"."PO_ITEM"."ITEM_SID") |
Check the error message, find the .csv file and delete the row, then resume the import. |
| UDF Values flagged as duplicates | Duplicate entry '639844078000195034-ROMA' for key 'customer_udf_option.unq2customer_udf_option' Example of roma vs ROMA for customer_udf in UDF3 C:\ProgramData\RetailPro\ImportExport\Exp\ customer_udf_option_1.csv (1 hits) Line 98: "1+3+96" "1" "1+3" "96" "roma" "1" "0" "3" "115" C:\ProgramData\RetailPro\ImportExport\Exp\ customer_udf_option_2.csv (1 hit) Line 41: "1+3+137" "1" "1+3" "137" "ROMA" "1" "0" "3" "115" |
UDF values may be flagged as duplicates during import because they match another entry and differ only in case (lower vs. upper). This non-critical warning is informational. No action is required. |
| Missing resource key | A missing resource key warning occurs when data (like a DCS code for an inventory item) is referenced and for some reason the source data does not exist. | In this case the missing data is created with what exists to ensure all referential data is present for import. No action is needed. |
| null Gift Certificate tenders require a certificate number | Certificate_No is null. | The import will stop on the tender_gift_certificate,csv file if there are null values in the certificate_no column. Before exporting data, verify that all gift certificate tenders include a certificate number by checking the tender_gift_certificate table. Verify the certificate_no column has an entry and add a certificate_no if empty. |
| Unique constraint in vendor invoices | Duplicate entry '639844078000108004-638449468946433010-P5034513' for key 'vendor_invoice.unq2vendor_invoice' | Unique constraints in the vendor_invoice table should not stop an import. If there is a unique constraint error during import, check the key value that caused the unique constraint and check the table to see if that value was already imported. It could be due to values that are not unique keys in legacy but are unique keys in Prism, which is fine. |
| Error between PO Item Sku & Qty Sku | File: PO0000.DAT >>>>>>>> Error: Error in PO Item Sku and Qty Sku: Rec#= 763 |
This PO was corrupted at some time in the past. During export it is required to link the PO information to the PO Qty information (stored in different v8 files). The PO has a differing number of items than the records (linked by PO SID) in PO QTY. To determine which PO is affected use the v8 Repair2000 tool to investigate the designated PO record #. |
| Corrupted Invoice, missing items (or another history document) | Processing: SA202101.Dat >>>>>>>> Error: Corrupted Invoice Item, Missing Item SID: Invoice Rec# = 17572, Invoice No= 199332 |
This type of message indicates that for some unknown reason an item on a sales receipt (or some other history document) is missing. It is very likely that data was corrupted, then when the file was repaired the corrupted data was excised. |
| Error in Sales Orders, Item Count/Ext Count | Starting: document File: SO0000.dat Buffering the Sales Order File >>>>>>>> Error: Error in SO#: 00000000000014, Item Count= 3, Ext Count= 1 |
1. These errors occur when there is corruption in the SO file. The problem has to do with correlating the basic item information to the extended information. 2. This means that very likely the reference to the record containing Desc3, Desc4, Scanned UPC, etc. has been corrupted. Normally SO's with this error will import w/no problems, however there will be data loss because the extended data does not exist. 3. Only base item information is imported. |
IMPORT_EXPORT, IMPORT_TABLES_SEQ Tables
There are two tables in the RPSODS database related to the Export/Import process:
- IMPORT_EXPORT has general information about the import or export
- IMPORT_TABLES_SEQ has information about the sequence in which resources are processed in.
IMPORT_EXPORT
| Element/Name | Type | Nullable? | Comments |
|---|---|---|---|
| DIRECTION | NUMBER(1) | No | 0=import, 1=export |
| DATA_SOURCE | NUMBER(1) | No | 0=V8, 1=RIL, 2=Prism, 3=External |
| DATA_SOURCE_NAME | NVARCHAR2(20) | Yes | Hostname, machine name or other identifier of the data source |
| PRISM_VERSION | NVARCHAR2(15) | Yes | Prism version at time of data import or export. |
| DESCRIPT | NVARCHAR2(50) | Yes | Description populated by api for UI display. |
| START_DATETIME | TIMESTAMP(0) WITH TIMEZONE | Yes | Date and time when the import or export started. |
| END_DATETIME | TIMESTAMP(0) WITH TIMEZONE | Yes | Date and time when the import or export stopped. |
| STATUS | NUMBER(1) | Yes | In progress, Completed, Canceled, Failed, Paused. |
| STATUS_DESC | NVARCHAR2(100) | Yes | In progress message updated by api which the UI can query. |
| TABLE_COUNT | NUMBER(5) | Yes | Total tables imported or exported. |
| CSV_FILECOUNT | NUMBER(12) | Yes | Total import or export .csv files to process or generated. |
| LAST_TABLE_PROCESSED | NVARCHAR2(35) | Yes | Last table being processed for the data imported or exported. |
| LAST_CSV_PROCESSED | NVARCHAR2(35) | Yes | Last .csv file being processed. Indicates where to resume if processing stops. |
| COMPRESSED_FILENAME | NVARCHAR2(50) | Yes | Name of compressed file used for the import or created by the export |
| REIMPORT_SID | NUMBER(19) | Yes | The SID of the previous import_export record if this record was created for a reimport. |
| DELETE_ZIP_FILE | NUMBER(1) | Yes | Store user directive to delete import .zip file after successful import. |
| PAUSED | NUMBER(1) | Yes | Webclient sets this to True when the user pauses an import or export. |
| SEED_DATA_UPDATED | NUMBER(1) | Yes | Indicates if SBS 1 and SBS 1 / STORE 0 were updated by this import. |
| MANIFEST_FILE | CLOB | Yes | Holds the import or export manifest file contents. |
IMPORT_TABLES_SEQ
| Element/Name | Type | Nullable? | Comments |
|---|---|---|---|
| TABLE_NAME | NVARCHAR2(40) | No | Prism table receiving the import data. |
| SEQ | NUMBER(5) | No | Import table processing order. |
| EXCLUDE_IMPORT | NUMBER(1) | Yes | 0=Table is not excluded from import, 1=Table is excluded from import. |
| ALT_CSV_FILENAME | NVARCHAR2(40) | Yes | Used when the import .csv requires a different name than the table name default. |
| PARENT_TABLE_NAME | NVARCHAR2(40) | Yes | Parent table name if this is a child table. |
| LOOKUP_COLUMNS | NVARCHAR2(25) | Yes | The column names for looking up a parent sid, which will also form the first part of lookup for unique row lookup. |
| RESOURCE_NAME | NVARCHAR2(40) | Yes | The Prism model or PODO name. |
| RESOURCE_TYPE | NUMBER(1) | Yes | Prism resource type: 2=Core, 0=Non-Core |
| REIMPORT_FLAG | NUMBER(1) | Yes | Can be used in UI to specify which tables to reimport. Clear it after the reimport. |
| IMPORT_SOURCE | NUMBER(1) | Yes | 0=V8, 1=RIL, 2=Prism, 3=External Use if special processing is needed for a table based on data source. |
Data Import API
http://
{
"data" : [
{
"ImportFile" : "RPS-WKS_DataExport_2022-10-07-18-36.zip",
"ImportSource: 2, {Used for API enum TImportExpSource = (ditV8, ditRIL, ditPrism, ditExternal)}
"DeleteZipAfterImport" : false,
"ReimportSid" : (Sid of import_export record. This is needed to "resume" an import.)
}
]
}
API Import notes:
Only the ImportFile name and ImportSource properties are required in the payload to start the import process. The other properties default to False.
The Import source number informs the server what data type is being imported and is used by the API enum:
- TImportExpSource = 0 // V8 datasource; 1 // V9 datasource
- "ImportFile" - The compressed file and manifest file naming conventions are TBD. Keep in mind users may be selecting from more than one compressed file to import so the file name should identify where the data came from.
"GET requests of the compressed files available for import are made using a "DirectoryFiles" virtual resource. The API returns a list of all files in the import folder. Only .zip or .7z files will be accepted for data import. The Data Import process will raise an exception if the file type is not a .zip or .7z and will reject the import attempt.
GET URL: http://
Import API Keys
| Property | Description |
|---|---|
| Import File | Required Name of the compressed file containing the manifest file and import data. |
| Import Source | Used for API enumeration. |
| DeleteZipAfterImport | Flag to delete the import zip files after successful import of all records in the zip. Default=False |
| ReimportSid | The SID of the previous import_export record. REQUIRED when resuming an import. |
Export Order - V8
The following table shows the order in which data is exported from V8. Note that some tables have sub-resources that are exported at the same time as the parent table:
| Sequence | V8 Table Name | Sub-resource |
|---|---|---|
| 1. | currency | |
| 2. | subsidiary | |
| 3. | pref_reason | |
| 4. | price_level | |
| 5. | tax_code | |
| 6. | tax_area | |
| 7. | tax_code_rule | |
| 8. | tax | |
| 9. | store | |
| 10. | sublocation | |
| 11. | allocation_pattern | |
| 12. | allocation_pattern_qty | |
| 13. | dcs | |
| 14. | vendor_udf | |
| 15. | vendor_udf_option | |
| 16. | vendor | |
| 17. | vendor_contact | |
| 18. | vendor_contact_address | |
| 19. | vendor_extend | |
| 20. | vendor_term | |
| 21. | scale | |
| 22. | scale_attribute | |
| 23. | scale_size | |
| 24. | scale_pattern | |
| 25. | scale_pattern_qty | |
| 26. | customer_udf | |
| 27. | customer_udf_option | |
| 28. | customer | |
| 29. | customer_address | |
| 30. | customer_email | |
| 31. | customer_extend | |
| 32. | customer_phone | |
| 33. | employee | |
| 34. | employee_extend | |
| 35. | employee_subsidiary | |
| 36. | employee_store | |
| 37. | purch_fee_type | |
| 38. | invn_udf | |
| 39. | invn_udf_option | |
| 40. | invn_sbs_item | |
| 41. | invn_style | |
| 42. | invn_sbs_extend | |
| 43. | invn_sbs_item_qty | |
| 44. | invn_sbs_price | |
| 45. | invn_sbs_item_lot | |
| 46. | invn_sbs_item_lot_qty | |
| 47. | invn_sbs_item_sn | |
| 48. | invn_sbs_item_sn_qty | |
| 49. | invn_sbs_vendor | |
| 50. | doc_pos_flag | |
| 51. | doc_pos_flag_option | |
| 52. | document | |
| 53. | document_item | |
| 54. | po | |
| 55. | po_term | |
| 56. | po_item | |
| 57. | po_qty | |
| 58. | tord | |
| 59. | tord_item | |
| 60. | tord_item_qty | |
| 61. | document | |
| 62. | document_coupon | |
| 63. | document_deposit | |
| 64. | document_disc | |
| 65. | document_extend | |
| 66. | document_item | |
| 67. | document_item_disc | |
| 68. | tender | |
| 69. | tender_check | |
| 70. | tender_foreign_currency | |
| 71. | tender_credit_card | |
| 72. | tender_debit_card | |
| 73. | tender_payments | |
| 74. | tender_store_credit | |
| 75. | voucher | |
| 76. | vou_item | |
| 77. | vou_approval | |
| 78. | vou_comment | |
| 79. | vou_fee | |
| 80. | vou_package | |
| 81. | vou_term | |
| 82. | slip | |
| 83. | slip_item | |
| 84. | slip_comment | |
| 85. | slip_fee | |
| 86. | adjustment | |
| 87. | adj_item | |
| 88. | adj_qty | |
| 89. | adj_comment | |
| 90. | adj_gift_card | |
| 91. | adj_lot_qty | |
| 92. | adj_serial | |
| 93. | adj_sublocation |
Import Order
The following table shows the order in which data types (resources) are imported into Prism during initialization. The import process follows the same order as initialization. Keep in mind that certain resources included in initialization may not be included during data import.
| Sequence # | Resource | RPSODS Table |
|---|---|---|
| 10 | controller | controller |
| 20 | tenant | tenant |
| 30 | calendar | calendar |
| 40 | country | country |
| 50 | language | rps_language |
| 60 | customschema | custom_schema |
| 70 | transformdesign | transformdesign |
| 80 | season | season |
| 90 | currency | currency |
| 100 | subsidiary | subsidiary |
| 110 | pricelevel | price_level |
| 120 | taxcode | tax_code |
| 130 | taxarea | tax_area |
| 135 | taxcoderule | tax_code_rule |
| 140 | storetype | store_type |
| 150 | store | store |
| 160 | sublocationsegpref | sublocation_seg_pref |
| 170 | sublocation | sublocation |
| 180 | usergroup | user_group |
| 190 | replicationstatus | replication_status |
| 200 | subscription | subscription |
| 210 | pricerounding | price_rounding |
| 220 | addresstype | address_type |
| 230 | customerclass | customer_class |
| 240 | job | job |
| 250 | exchangerate | exchange_rate |
| 252 | commission | commission |
| 260 | vendor | vendor |
| 270 | mediatype | media_type |
| 280 | company | company |
| 290 | allocationpattern | allocation_pattern |
| 300 | dcs | dcs |
| 310 | scale | scale |
| 320 | scalepattern | scale_pattern |
| 330 | title | title |
| 340 | phonetype | phone_type |
| 350 | emailtype | email_type |
| 360 | contacttype | contact_type |
| 370 | kitcomponent | kit_component |
| 380 | documentfeetype | document_fee_type |
| 390 | tranfeetype | tran_fee_type |
| 400 | purchfeetype | purch_fee_type |
| 410 | reason | pref_reason |
| 420 | comment | comments |
| 430 | till | till |
| 440 | inventorystyle | invn_style |
| 450 | shippingmethod | shipping_method |
| 460 | userpasswordhistory | user_password_history |
| 470 | customerudf | customer_udf |
| 480 | vendorudf | vendor_udf |
| 490 | invnudf | invn_udf |
| 500 | docposflag | doc_pos_flag |
| 510 | region | region |
| 520 | ltylevel | lty_level |
| 530 | ltylevelprogram | lty_level_program |
| 540 | customer | customer |
| 550 | employee | employee |
| 560 | customer | customer |
| 570 | inventory | invn_sbs_item |
| 580 | invnlot | invn_sbs_item_lot |
| 590 | invnserial | invn_sbs_item_sn |
| 600 | invnsbssublocationqty | invn_sbs_sublocation_qty |
| 610 | vendorinvoice | vendor_invoice |
| 620 | purchaseorder | po |
| 630 | receiving | voucher |
| 640 | transferorder | transfer_order |
| 650 | transferslip | transfer_slip |
| 660 | plugindata | plugin_data |
| 670 | chargeterm | charge_term |
| 680 | adjustment | adjustment |
| 690 | markdown | markdown |
| 700 | timeclock | time_clock |
| 710 | drawerevent | drawer_event |
| 720 | couponset | coupon_set |
| 730 | couponsetcoupon | coupon_set_coupon |
| 740 | pcppromotion | pcp_promotion |
| 750 | document | document |
| 755 | documentitem | document_item |
| 760 | touchmenu | touch_menu |
| 770 | zoutcontrol | zout_control |
| 780 | biometrics | biometrics |
| 790 | printarea | print_area |
| 800 | griddata | griddata |
| 810 | customerdocumenthistory | customer_document_history |
| 820 | fiscaldocument | fiscal_document |
| 830 | fiscalsequence | fiscal_sequence |
| 840 | employeeudf | employee_udf |
| 850 | transrule | trans_rule |
| 860 | fiscaldocumentitem | fiscal_document_item |
| 870 | sublocationsegment | sublocation_segment |
| 880 | regionsubsidiary | region_subsidiary |
| 890 | reportgroupitem | report_group_item |
| 900 | employeeudfoption | employee_udf_option |
| 910 | reportgroup | report_group |
| 920 | printareavalue | print_area_value |
| 930 | allocationpatternqty | allocation_pattern_qty |
| 935 | tender | tender |
| 940 | touchbutton | touch_button |
| 950 | gridcolumn | grid_column |
| 960 | gridsearchby | grid_search_by |
| 970 | markdownadj | markdown_adj |
| 980 | markdownitem | markdown_item |
| 990 | scalepatternqty | scale_pattern_qty |
| 1000 | tordqty | tord_qty |
| 1005 | torditem | tord_item |
| 1010 | tenderstorecredit | tender_store_credit |
| 1020 | tenderpayments | tender_payments |
| 1030 | tendertravelercheck | tender_traveler_check |
| 1040 | tendergiftcertificate | tender_gift_certificate |
| 1950 | tendergiftcard | tender_gift_card |
| 1060 | tenderforeigncurrency | tender_foreign_currency |
| 1070 | tenderforeigncheck | tender_foreign_check |
| 1080 | tenderdebitcard | tender_debit_card |
| 1090 | tendercreditcard | tender_credit_card |
| 1100 | tendercheck | tender_check |
| 1110 | tendercharger | tender_charge |
| 1120 | tendercentralcredit | tender_central_credit |
| 1130 | tendercentralgiftcard | tender_central_gift_card |
| 1140 | docdiscount | document_discount |
| 1150 | docdeposit | document_deposit |
| 1160 | docitemdiscount | document_item_discount |
| 1170 | docextend | document_extend |
| 1190 | doccoupon | document_coupon |
| 1210 | drawereventcurrency | drawer_event_currency |
| 1215 | invn_sbs_item_lty | |
| 1229 | invnserialqty | invn_sbs_item_sn_qty |
| 1230 | invnlotqty | invn_sbs_item_lot_qty |
| 1240 | zoutcontrolaudit | zout_control_audit |
| 1260 | tax | tax |
| 1270 | docposflagoption | doc_pos_flag_option |
| 1280 | invnudfoption | invn_udf_option |
| 1290 | usergroupuser | user_group_user |
| 1300 | employeeextend | employee_extend |
| 1310 | emplphone | empl_phone |
| 1320 | emplmail | empl_mail |
| 1330 | employeesubsidiary | employee_subsidiary |
| 1340 | employeestore | employee_store |
| 1350 | empladdress | empl_address |
| 1360 | currencydenomination | currency_denomination |
| 1370 | subresource | subresource |
| 1380 | repstatusdetail | rep_status_detail |
| 1390 | repstatusresource | rep_status_resource |
| 1400 | scaleattribute | scale_attribute |
| 1410 | scalesize | scale_size |
| 1412 | scale_pattern_qty | |
| 1420 | vendorcontact | vendor_contact |
| 1430 | vendorterm | vendor_term |
| 1440 | vendor_contact_address | |
| 1445 | vendorextend | vendor_extend |
| 1450 | vendorudfoption | vendor_udf_option |
| 1460 | customerudfoption | customer_udf_option |
| 1470 | custextend | customer_extend |
| 1480 | custaddress | customer_address |
| 1490 | custemail | customer_email |
| 1500 | custphone | customer_phone |
| 1555 | adjitem | adj_item |
| 1560 | adjqty | adj_qty |
| 1565 | adjserial | adj_serial |
| 1570 | adjcomment | adj_comment |
| 1572 | adjsublocation | adj_sublocation |
| 1573 | adjgiftcard | adj_gift_card |
| 1574 | adjlotqty | adj_lot_qty |
| 1580 | slipfee | slip_fee |
| 1590 | slipcomment | slip_comment |
| 1600 | slipitem | slip_comment |
| 1610 | pcpvalidationelement | pcp_validation_element |
| 1620 | pcprewardfilterelement | pcp_reward_filter_element |
| 1630 | pcppromotiondistrict | pcp_promotion_district |
| 1640 | pcppromotionpricelevel | pcp_promotion_price_level |
| 1650 | pcppromotionstore | pcp_promotion_store |
| 1660 | pcprewardgddisctier | pcp_reward_gd_disc_tier |
| 1670 | pcprewarditemdisctier | pcp_rewarditemdisctier |
| 1680 | pcpvalidatecoupon | pcp_validate_coupon |
| 1690 | pcprewardotheritemrule | pcp_reward_other_item_rule |
| 1700 | pcpvalidationitemrule | pcp_validation_item_rule |
| 1710 | pcppromotionbusinessunit | pcp_promotion_business_unit |
| 1720 | invnmedia | invn_media |
| 1730 | invnvendor | invn_sbs_vendor |
| 1740 | invnquantity | invn_sbs_item_qty |
| 1750 | invnprice | invn_sbs_price |
| 1760 | invnkit | invn_sbs_kit |
| 1770 | invnextend | invn_sbs_extend |
| 1780 | recvpackage | vou_package |
| 1790 | recvitem | vou_item |
| 1800 | recvfee | vou_fee |
| 1810 | recvcomment | vou_comment |
| 1820 | recvapproval | vou_approval |
| 1830 | recvterm | vou_term |
| 1840 | poqty | po_qty |
| 1850 | poterm | po_term |
| 1860 | pofee | po_fee |
| 1870 | poapproval | po_approval |
| 1880 | poquantity | po_qty |
| 1890 | centralcredit | central_credit |
| 1900 | centralgiftcard | central_gift_card |
| 1910 | pimap | pi_map |
| 1920 | lty_cust_central | lty_cust_central |
Migrate_Exp.ini Settings
| Setting | Description |
|---|---|
| Export | |
| PathCsv | The path to the folder where the .csv files will be exported. Default = C:\Users\sysadmin\Downloads\LegacyExport\Exp\ |
| PathZip | The path to the .zip files containing the .csv files of exported data. By default, this will be a \Zip folder in the same folder where the tool is located. Default = C:\Users\sysadmin\Downloads\LegacyExport\Zip\ |
| FileName | The Base Export File Name defined in V8 Preferences. Example: Base Export File Name = STORE1, final 7z file = V8-STORE1-20230115_1220 |
| LineLimit | Default = 5000. Every file includes two lines of instructions at the top of the file. This means for the default LineLimit of 5000, there are 4998 lines of records and two lines of instructions. DeleteCsv 0=do not delete the .csv files after zipping up into the master .zip file. 1=delete the .csv files are zipping up into the master .zip file. Sometimes, it is useful for troubleshooting to keep the original csv files. Default = 0 |
| DeleteCsv | 0=do not delete the .csv files after zipping up into the master .zip file. 1=delete the .csv files after zipping up into the master .zip file. Sometimes, it is useful for troubleshooting to keep the original csv files. Default = 0 |
| NoZip | NoZip means the tool will not create the compressed files. The import only works for compressed files so if NoZip is selected, the compressed files must be created manually. |
| Zip64 | 7Zip recently released a 64-bit version that is only available via the CLI. To use the standalone CLI only version of 7-zip, change the Zip64 setting from the default 0 to 1. 0=disabled, 1=enabled. |
| LogMem | Enables or disables logging of memory. 0=disabled, 1=enabled. Default=1. |
| LogLstCnt | This setting is used for debugging. Do not change unless instructed to by technical support. Default = 1. |
| FilterRes | The FilterRes section in the Migrate_Exp.ini file enables users to filter groups of resources and/or documents from the export. WARNING!! This setting is not for general user use. TECHNICIANS ONLY!!! 1 = the group is exported; 0 = the group is not exported. Core Core=1 Lty=1 Cust=1 Invn=1 Documents DocSOrd=1 DocInvc=1 DocPOrd=1 DocVchr=1 DocTOrd=1 DocSlip=1 DocMemo=1 |
| V8 | |
| Data | Path to the exported legacy Retail Pro data files. Default=C:\Retail\Rpro |
| History | Path to the exported legacy Retail Pro history files. Default=C:\Retail\Rpro |
| SecAdmin | Path to the exported legacy Retail Pro Security Administrator files. Default=C:\Retail\SecAdmin |
| WsNo | Workstation that initiated the data export. |
| SbsNo | The subsidiary into which the exported data will be placed. |
| SbsName | The name of the subsidiary into which the exported data will be placed. |
| Country Code | Country code (three characters). |
| Currency Name | Default=US Dollar |
| Season | Default Season for the subsidiary. |
| BufferSord | Default = 1. DO NOT CHANGE. |
FilterRes
Refer to the following tables for a list of the resources in each of the filter groupings for RIL and V8
V8 INI filter groupings
Core (V8)
| currency | subsidiary | tax_code_rule | price_level |
| tax_code | tax_area | tax_code_rule | tax |
| store | sublocation | allocation_pattern | allocation_pattern_qty |
| dcs | vendor_udf | vendor_udf_option | vendor |
| vendor_contact | vendor_contact_address | vendor_extend | vendor_term |
| scale | scale_attribute | scale_size | scale_pattern |
| scale_pattern_qty | customer_udf | custom_udf_option | employee |
| employee_extend | employee_subsidiary | employee_store | purch_fee_type |
| invn_udf | invn_udf_option | doc_pos_flag | doc_pos_flag_option |
Cust (V8)
| customer | customer_address | customer_email | customer_extend |
| customer_phone |
Invn (V8)
| sbs_item | invn_style | invn_sbs_extend | invn_sbs_item_qty |
| invn_sbs_price | invn_sbs_item_lot | invn_sbs_item_lot_qty | invn_sbs_item_sn |
| invn_sbs_item_sn_qty | invn_sbs_vendor |
DocInvc (V8)
| document | document_coupon | document_deposit | document_disc |
| document_extend | document_item | document_item_disc | tender |
| tender_check | tender_foreign_currency | tender_credit_card | tender_debit_card |
| tender_payments | tender_store_credit |
DocMemo (V8)
| adjustment | adj_item | adj_qty | adj_comment |
| adj_gift_card | adj_lot_qty | adj_serial | adj_sublocations |
DocPOrd (V8)
| po | po_term | po_item | po_qty |
DocSlip (V8)
| slip | slip_item | slip_comment | slip_fee |
DocSOrd (V8)
| document | document_item |
DocTOrd (V8)
| tord | tord_item | tord_item_qty |
DocVchr (V8)
| voucher | vou_item | vou_approval | vou_comment |
| vou_fee | vou_package | vou_term |
Data Import Permission
To run the import, the user needs to be granted Allow Data Import permission in the Prism Employee Group Management module.
Navigate to Store Operations > Employees > Group Management. The Usergroup Lookup screen is displayed.
Click the Search button.
A new Prism install has a single ADMIN group. Click the checkmark column for the ADMIN group and then click the Details button.
The permissions screen is displayed. By default, all permissions are set to "Deny."
Click Edit.
Type "import" in the text search to filter the permission list.
You may have to scroll down to see the Export/Import permissions.
Set the Import & Export - Allow Data Import permission to "Allow."
Save the changes.
