Home data migration dynamics gp gp setup microsoft dynamics

Streamline Dynamics GP: Copy Setup Tables Between Companies for Efficiency

Updated: • 0 • min read
Table of Contents

Streamline Dynamics GP

Setting up a new company in Microsoft Dynamics GP or Microsoft Business Solutions – Great Plains can be a time-consuming process, especially when extensive configurations are required. Fortunately, Dynamics GP offers a powerful solution to enhance efficiency: the ability to copy setup tables from an existing company to a new one. This method significantly streamlines the deployment of new company databases, ensuring consistency and reducing manual configuration efforts.

This article details the process of copying essential setup tables, providing a comprehensive guide to leveraging this feature for optimal operational efficiency. It covers which tables are copyable, crucial steps like database backups, and important post-copy procedures such as running the Check Links function.

Introduction to Copying Setup Tables

Copying setup tables is an invaluable practice for various scenarios, including the creation of new entities that share similar operational structures, establishing test environments for system changes, or standardizing configurations across multiple company databases. By replicating existing setup data, organizations can save considerable time and minimize the risk of human error associated with manual re-entry. This approach ensures that critical financial, purchasing, sales, inventory, and other module settings are consistent across your Dynamics GP instances.

The process involves identifying the relevant setup tables and executing a series of steps to transfer this data. It is imperative to perform these operations carefully, starting with robust database backups, to safeguard your existing data and ensure a smooth transition for the new company.

Essential Setup Tables for Replication

The following tables represent key setup information that can be efficiently copied to a new company database. It’s crucial to understand the implications of copying each set of tables, as they dictate the foundational behavior of various modules within Dynamics GP.

Finance Module Tables

These tables are critical for the financial backbone of your company, encompassing chart of accounts, cash management, and multicurrency settings.

  • GL00100 Chart of Accounts (Account Master): This table holds your general ledger account structure. Do not copy GL00101, as it contains account history and balances.
  • GL00102 Account Category Master
  • GL00103 Fixed Allocation Master
  • GL00104 Variable Allocation Master
  • GL00105 Account Index Master
  • CM00100 CM Checkbook Master
  • CM40100 Cash Management Setup
  • CM40101 Cash Management Transaction Type Setup
  • GL00200 Budget Master file
  • GL00201 Budget Summary Master file
  • GL40000 General Ledger Setup
  • GL40100 Quick Journal Setup
  • GL40101 Quick Journal Account Setup
  • GL40200 Segment Description Master
  • SY04100 Bank Master
  • MC40000 Multicurrency Setup
  • MC40100 Multicurrency Rate Type Setup
  • ASI*.* Advanced Lookup Files: These files support various advanced lookups throughout the system.

Purchase Module Tables

Copying these tables brings over vendor classes, vendor information, purchasing setups, and buyer data, essential for managing your procurement processes.

  • PM00100 PM Class Master File
  • PM00101 Vendor Class Accounts
  • PM00200 PM Vendor Master File
  • PM00203 Vendor Accounts
  • PM00300 PM Address Master
  • PM40100 PM Setup File
  • PM40101 PM Period Setup File
  • PM40102 Payables Document Types
  • PM40103 Payables Distribution Type SETP
  • POP00101 Buyer Master
  • POP40100 Purchasing Setup Table
  • POP40600 Purchasing Non-IV Item Currency Setup
  • ASI*.* Advanced Lookup Files

Sales Module Tables

These tables define your customer base, sales processes, and invoicing configurations, crucial for managing revenue and customer relationships.

  • IVC40100 Invoicing Setup
  • IVC40101 Invoicing Document Setup
  • RM00101 Customer Master
  • RM00102 Customer Master Address File
  • RM00105 National Accounts Master
  • RM00201 RM Class Master
  • RM00301 RM Salesperson Master files
  • RM00303 Sales Territory Master File
  • RM40101 RM Module Setup File
  • RM40201 RM Period Setup
  • RM40401 Document Type Setup File
  • SOP00100 Sales Process Holds Master
  • SOP00200 Sales Prospect Master
  • SOP40100 Sales Setup
  • SOP40200 Sales Type ID setup
  • SOP40201 Sales Default Process Holds Setup
  • SOP40300 Sales Document Setup
  • SOP40400 Sales User Defined Table Setup
  • SOP40500 Sales Master Number Setup
  • SOP40600 Sales Non-IV Item Currency Setup
  • ASI*.* Advanced Lookup Files

Inventory Module Tables

For companies managing physical goods, copying inventory setup tables is vital for item master data, quantity tracking, and pricing structures.

  • BM00101 Bill of Materials Header
  • BM00111 Bill of Materials Component
  • BM40100 Bill of Materials Setup
  • IV00101 Item Master
  • IV00102 Item Quantity Master
  • IV00103 Item Vendor Master
  • IV00104 Item Kit Master
  • IV00105 Item Currency Master
  • IV00106 Item Purchasing
  • IV00107 Item Price List Options
  • IV00108 Item Price List
  • IV00109 Item Serial Number Mask
  • IV40100 Inventory Control Setup
  • IV40201 Inventory U of M Schedule Setup
  • IV40202 Inventory U of M Schedule Detail Setup
  • IV40400 Item Class Setup
  • IV40401 Item Class Currency Setup
  • IV40500 Item Lot Category Setup
  • IV40600 Item Category Setup
  • IV40700 Site Setup
  • IV40800 Price Level Setup
  • IV40900 Price Group Master
  • IV41000 Stock Calendar
  • IV41001 Stock Calendar Exception Days
  • ASI*.* Advanced Lookup Files

Company-Wide Tables

These tables house general company settings, including account formats, posting accounts, shipping methods, and tax schedules, applicable across all modules.

  • SY00300 Account Format Setup
  • SY01100 Posting Account Master
  • SY02200 Posting Journal Destinations
  • SY02300 Posting Settings
  • SY03000 Shipping Methods Master
  • SY03100 Credit Card Master
  • SY03300 Payment Terms Master
  • SY40100 Fiscal Period Setup
  • SY40101 Fiscal Period Header
  • TX00101 Sales/Purchases Tax Schedule Header Master
  • TX00102 Sales/Purchases Tax Schedule Master
  • TX00201 Sales/Purchases Tax Master
  • STN*.* Named Printers Setup
  • ASI*.* Dynamics Explorer Files

U.S. Payroll Tables

When replicating payroll setup information, these tables are essential for unemployment, tax, pay code, and benefit configurations.
Important Note: If you copy the UPR40500 file, the posting accounts for payroll will become identical to those of the source company.

  • UPR40100 Payroll Unemployment Setup
  • UPR40101 Payroll Unemployment TSA
  • UPR40200 Payroll Setup
  • UPR40301 Payroll Position Setup
  • UPR40500 Payroll Accounts Setup
  • UPR40501 Payroll Tax Expense/Withholding Setup
  • UPR40600 Payroll Pay Code Setup
  • UPR40700 Payroll Workers Comp Setup
  • UPR40800 Payroll Benefit Setup
  • UPR40801 Payroll Benefit Based On Setup
  • UPR40900 Payroll Deduction Setup
  • UPR40901 Payroll Deduction Based On Setup
  • UPR40902 Payroll Deduction Sequence Setup
  • UPR41100 Payroll State Code Setup
  • UPR41200 Payroll Class Setup
  • UPR41201 Payroll Class Detail Setup
  • UPR41400 Payroll Local Tax Setup
  • UPR41401 Payroll Local Tax Table Setup
  • UPR41500 Payroll Shift Code Setup
  • UPR41700 Payroll Setup Supervisor
  • UPR41800 Payroll Maximum Deduction Setup (only in Microsoft Dynamics GP 10.0)
  • UPR41801 Payroll State/Fed Setup (only in Microsoft Dynamics GP 10.0)
  • UPR41900 Payroll Earnings Setup (only in Microsoft Dynamics GP 10.0)
  • UPR41901 Payroll Earnings Paycodes (only in Microsoft Dynamics GP 10.0)
  • UPR41902 Payroll Earnings Deductions (only in Microsoft Dynamics GP 10.0)

Payroll Extensions Tables

These tables relate to specific payroll functionalities such as deductions in arrears, payables integration, and overtime rate management.

  • ORM_UPR_SETP_OT_DTL
  • ORM_UPR_SETP_OT_HDR
  • UPR40600_OT
  • APR_DIA40100
  • APR_DIA40200
  • APR_UPR40500
  • APR_UPR40900
  • APR_PIP40100

Advanced Payroll Tables

For advanced payroll functionalities, these additional tables store specific configurations.

  • APR40600
  • APR41100
  • APR41101
  • APR41501
  • APR41601
  • APR_APR70901
  • APR_APR70900
  • APR_UPR40500
  • APR_APR40101
  • APR_APR40100

Canadian Payroll Tables

Specific to Canadian payroll, these tables manage employer, department, job, pay code, and WCB settings.

  • CPY10010 CDN Payroll Employer Master
  • CPY10020 CDN Payroll Department Master
  • CPY10030 CDN Payroll Employee Job Titles
  • CPY10050 CDN Payroll Employee Class
  • CPY10051 CDN Payroll Class Attached Pay codes File
  • CPY10060 CDN Payroll Pay code Master
  • CPY10061 CDN Payroll Pay code Attached Pay codes
  • CPY10062 CDN Payroll Income Attached Pay Codes
  • CPY10063 CDN Payroll Rate Table Codes
  • CPY10064 CDN Payroll Rate Tables
  • CPY10070 CDN Payroll WCB Master
  • CPY10075 CDN Payroll WCB Administration
  • CPY10080 CDN Payroll User Paid By
  • CPY10081 CDN Payroll User Drop Down Strings
  • CPY10082 CDN Payroll Reporting Codes
  • CPY10170 CDN Payroll Employee Unions
  • CPY10171 CDN Payroll UnionAttached Pay codes
  • CPY20200 CDN Payroll Job Master
  • CPY20201 CDN Payroll Phase Master
  • CPY20700 P_Security_Group_MSTR
  • CPY20705 P_Security_Group_Detail
  • CPY20710 P_Security_User_MSTR
  • Optional for copy (if information is identical):
    • CPY20100 CDN Payroll Control Master
    • CPY20110 CDN Payroll CSB Setup Information
    • CPY20111 CDN Payroll CSB Pay codes

Human Resources Tables

These tables are crucial for replicating HR-related setups, including benefits, FMLA, training, and salary matrix information.

  • BE020230 HR_Benefit_SETP
  • BE021030 BEN2_FMLA_Line
  • BE031000 BEN_FMLA_INFO
  • HR2Ben21 HR_Benefit_Tiers_SETP
  • HR2Ben11 HR_Benefit_Fund
  • HR2Ben12 HR_Benefit_MDVE_Table
  • HR2Ben13 HR_Benefit_Life_Premiums
  • HR2Ben14 HR_Venefit_MDVE_Types
  • HR2Div02 HR_Division2
  • HR2Tra01 HR_Train_Course
  • HR2Tra03 HR_Train_Class
  • HRCom022 HR_Company2_extra
  • HRDep022 HR_Department2_Extra
  • HRDiv022 HR_Division2_Extra
  • HRPBen05 HRP_BEN_FMLA_Set12Month
  • HRPro022 HR_Property
  • HRPppc01 HRP_Position_Pay_Code
  • HRsax012 HR_Salary_Matrix
  • HRsax022 HR_Salary_Matrix_Table
  • HRsax042 HR_Salary_Matrix_Col
  • HRsax032 HR Salary Matrix rows
  • HRtra042 HR_Train_Class_Skills
  • HRtrpc02 HR_Train_Position_Course_Class
  • HRtrps01 HR_Train_Position_Course
  • RV010221 HR_Review_LINE_V2
  • RV020221 HR_Review_Setup_LINE_V2
  • RV030221 HR_Review_Words_Setup_LINE
  • SK010230 HR_Skills_Line
  • TAAC0130 TA_SETP_Accrual_Type
  • TAPY0130 TA_Payroll_Link (Note: This table was removed in Microsoft Dynamics GP 9.0 and 10.0)
  • TAST0130 TA_Setup
  • TAST0230 TA_Attendance_reason
  • TAST0330 TA_Attendance_Types
  • TAST0532 TA_Pay_Period_accrual_LINE
  • TATM0130 TA_SETP_Types

Advanced Human Resources Tables

These tables pertain to extended HR functionalities, encompassing additional benefit, absence, and employee wellness setups.

  • APR_BLM41500
  • APR_BLM41501
  • APR_BLM41600
  • APR_BLM41601
  • APR_BLM41400
  • APR_BLM41401
  • APR_BLM41100
  • APR_BLM41101
  • APR_BLM41300
  • APR_BLM41301
  • APR_BLM41200
  • APR_BLM41201
  • APR_BLM42100
  • APR_BLM42101
  • APR_BLM42200
  • APR_BLM42201
  • APR_BLM43100
  • APR_BLM43200
  • APR_BLM43201
  • APR_BLM43300
  • APR_BLM43301
  • APR_APR40500
  • EHW40100
  • EHW40201
  • EHW40200
  • EHW40300
  • EHW40400
  • EHW40501
  • EHW40500
  • CLM40100
  • CLM40300
  • CLM40700
  • CLM40700
  • CLM40701
  • CLM40600
  • CLM40500
  • CLM40400
  • CLM40200

PTO Manager Tables

For managing paid time off, these tables contain configurations related to accrual types, policies, and employee leave.

  • PTO40100
  • PTO40101
  • PTO40200
  • PTO40201

Analytical Accounting Tables

Analytical Accounting (AA) tables are crucial for detailed financial analysis, storing setup for dimensions, codes, trees, and budgets.
Important Note: If you are copying AA setup tables from a source company (Company A) where AA is activated to a destination company (Company B) where AA is not yet activated, you must first install and activate AA in Company B. After AA is activated in Company B, proceed to copy these AA setup tables from Company A. Following the copy, it is essential to update the next available values stored in table AAG00102 within the Dynamics database for the AA setup tables. This step is critical to prevent “Cannot insert duplicate key in object” error messages during Analytical Accounting transactions.

  • AAG00400 aaTrxDimMstr (dimensions)
  • AAG00401 aaTrxDimCodeSetp (codes)
  • AAG00402 aaTrxDimCodeNumSetp
  • AAG00403 aaTrxDimCodeBoolSetp
  • AAG00404 aaTrxDimCodeDateSetp
  • AAG00405 aaTrxDimRelation
  • AAG00406 aaTrxDimCodeRelation
  • AAG00407 AA Trx Dim Adjustment Option
  • AAG00500 aaDateSetup
  • AAG00600 aaTreeMstr
  • AAG00601 aaTreeNodeMstr
  • AAG00602 aaTreeNodeLink
  • AAG00603 aaTreeNodeUserWork
  • AAG00605 aaTreeMstrDupe
  • AAG00700 aaOptionSetp
  • AAG00200 aaAccountMstr
  • AAG00201 aaAccountClassMstr
  • AAG00202 aaAccountClassDim
  • AAG00900 AA Budget Tree Master
  • AAG00901 AA Budget Tree Trx Dim Master
  • AAG00902 AA Budget Tree Trx Dim Code Master
  • AAG00903 AA Budget Master
  • AAG00904 AA Budget Tree Balance
  • AAG00905 AA Budget Tree Account Balance
  • AAG00906 AA Budget Tree View Work
  • AAG01000 AA UDF Setup option
  • AAG01001 AA UDF Trx Dim Setup
  • AAG01002 AA Trx Dim CodeUDF Maintenance

Version-Specific Tables (Great Plains 8.0 & Dynamics GP 9.0)

For users of Microsoft Business Solutions - Great Plains 8.0 and Microsoft Dynamics GP 9.0, there are additional setup tables to consider during the copying process.

Added in Microsoft Business Solutions - Great Plains 8.0

Sales Module:
* SOP00300 Sales Customer Item Substitute (Requires Inventory Setup to be copied)
* SOP10111 Sales Picking Instruction Master
* SOP40101 Sales Workflow Setup

Inventory Module:
* IV00113 Item Price List Details
* IV00114 Inactive Items
* IV00115 Multiple Manufacture Items Master

Added in Microsoft Dynamics GP 9.0

  • IV00117 Item Site Bin Priorities

Process Flow for Copying Setup Tables

The overall process involves careful planning, execution, and validation. Visualizing the workflow helps ensure all critical steps are followed.

mermaid graph TD A[Identify Source & Target Companies] --> B[Perform Full Database Backup]; B -- Backup Successful --> C[Execute SQL Scripts to Copy Setup Tables]; C --> D[Run Check Links Function in Target Company]; D --> E[Verify Data Integrity and Functionality]; E -- Validation Successful --> F[Go Live / Use Target Company]; B -- Backup Failed --> G[Troubleshoot Backup Issue]; E -- Validation Failed --> H[Review Copy Process & Troubleshoot Data Issues];

Step 1: Make a Database Backup

Before initiating any data transfer or modification, creating a full and complete database backup is paramount. This ensures that you have a recovery point in case of any unforeseen issues during the copying process. Perform a backup for both the source and destination company databases, as well as the DYNAMICS system database.

For SQL Server 2000

  1. Click Start, navigate to All Programs, then Microsoft SQL Server, and select Enterprise Manager.
  2. Expand Microsoft SQL Servers, then expand SQL Server Group.
  3. Expand the name of the server hosting SQL Server.
  4. Expand the Databases folder.
  5. Right-click the DYNAMICS database, point to All Tasks, and then click Backup Database.
  6. In the SQL Server Backup - DYNAMICS window, ensure Database - complete is selected as the backup type.
  7. Click Add, then click File Name. Browse to the desired location for storing the backup file.
  8. Enter a name for the backup in the Filename field (e.g., Dynamics_PreCopy.bak), and click OK.
  9. Click OK to close the Destination window.
  10. Click OK to begin the database backup. A success message will appear upon completion.
  11. Repeat steps 1 through 10 for each company database involved in the copying process (source and target).

For SQL Server 2005 or SQL Server 2008

  1. Open SQL Server Management Studio.
    • In the Connect to Server window, enter the Server name.
    • Select SQL Authentication in the Authentication box.
    • Type sa in the Login box.
    • Enter the password for the sa user, then click Connect.
  2. Expand the SQL Server instance in the Object Explorer.
  3. Expand the Databases folder.
  4. Right-click the DYNAMICS database, point to Tasks, and then click Back Up.
  5. In the Back Up Database - DYNAMICS window, confirm that Full is chosen as the Backup Type.
  6. Under “Backup component”, select “Database”.
  7. Under “Destination”, click Add to specify the backup file path and name. Browse to your preferred location.
  8. Enter a name for the backup in the Filename field (e.g., Dynamics_PreCopy.bak), and click OK.
  9. Click OK to exit the Destination window.
  10. Click OK to initiate the database backup. A confirmation message “The backup of database ‘DYNAMICS’ completed successfully.” will be displayed.
  11. Repeat steps 1 through 10 for each company database (source and target) that will be part of the setup table copy operation.

Step 2: Execute SQL Scripts to Copy Setup Tables

The actual copying of tables is performed using SQL scripts, typically INSERT INTO ... SELECT FROM statements. This process should be carried out by a database administrator or someone with advanced SQL knowledge. You will run these scripts from the source company database to the target company database for each table identified in the comprehensive lists above.

Example SQL for copying a table (replace [TargetCompany] and [SourceCompany] with actual database names, and [TableName] with the specific table):

INSERT INTO [TargetCompany].[dbo].[TableName]
SELECT *
FROM [SourceCompany].[dbo].[TableName]
WHERE 1=1; -- Or add specific WHERE clauses if only a subset of data is desired for setup

Caution: Ensure that no users are logged into either the source or target company database during the copy process to prevent data corruption or conflicts. This operation should ideally be performed during off-peak hours or scheduled maintenance windows.

After the setup tables have been successfully copied to the new company, it is crucial to run the Check Links function on all modules within the new company. Check Links identifies and corrects data integrity issues, ensuring that relationships between tables are properly maintained after the data transfer. This step is vital for the stability and correct functionality of your newly configured company.

Follow the steps below according to your version of Microsoft Dynamics GP:

  • Microsoft Dynamics GP 10.0 and Microsoft Dynamics GP 2010:
    1. On the Microsoft Dynamics GP menu, point to Maintenance.
    2. Click Check Links.
    3. Select all modules and run Check Links.
  • Microsoft Dynamics GP 9.0 and Microsoft Business Solutions - Great Plains 8.0:
    1. On the File menu, point to Maintenance.
    2. Click Check Links.
    3. Select all modules and run Check Links.

Running Check Links may take some time, depending on the size and complexity of your database. Monitor the process and address any reported issues.

Step 4: Verify Data Integrity and Functionality

Once Check Links has completed, conduct thorough testing within the new company. This involves:

  • Verifying master records: Check that accounts, vendors, customers, items, and employees are present and correctly configured.
  • Testing transactional processes: Create and post test transactions in each module (e.g., General Ledger journals, Purchase Orders, Sales Orders, Inventory adjustments, Payroll runs) to ensure all setup components function as expected.
  • Reviewing reports: Generate standard reports to confirm that data is accurately reflected.
  • Checking integrations: If your Dynamics GP instance integrates with other systems, test these integrations to ensure seamless data flow.

This verification phase is critical to confirm that the copied setup data translates into a fully operational and reliable company environment.

Best Practices and Considerations

  • Understand Data Dependencies: Not all tables are suitable for direct copying. Transactional data, historical records, and user-specific data are typically not copied in this manner. Focus strictly on setup tables.
  • User Access Control: Ensure appropriate security settings and user roles are configured in the new company after the setup copy.
  • System Configuration: Review system-wide settings that might not be part of the individual module setup tables but are crucial for the new company (e.g., email setup, reporting services integration).
  • Documentation: Maintain clear documentation of the tables copied, the date of the copy, and any post-copy adjustments made. This is invaluable for auditing and future reference.
  • Test Environment First: Whenever possible, perform the setup table copy in a non-production test environment before applying it to a live production database.

Professional Tools for Setup Management

While direct SQL scripting is a viable method, specialized tools are available to further streamline the management of setup data across multiple Dynamics GP companies. These tools often provide user-friendly interfaces to copy accounts, vendors, or customers from a master database to other databases, either individually or in bulk. Such solutions can greatly enhance efficiency and consistency for organizations managing numerous Dynamics GP companies. For more information on these types of tools, you may consult Microsoft Dynamics GP partners or specialized services.

Concluding Thoughts

Copying setup tables in Microsoft Dynamics GP is a powerful technique for creating new company databases with speed and precision. By following the outlined steps – from initial database backups and SQL table copying to running Check Links and comprehensive validation – businesses can ensure that their new Dynamics GP environments are established efficiently and accurately. This strategic approach not only saves time but also promotes data consistency and operational integrity across your enterprise resource planning landscape.

Do you have experience copying setup tables in Dynamics GP? What challenges or best practices have you discovered? Share your insights in the comments below!

Post a Comment