Reference: Microsoft Dynamics GP Data Source Tables
Setting up a Microsoft Dynamics GP Data Source describes the basics of configuring a data source using the preconfigured adapter for Microsoft Dynamics GP. This topic provides additional technical information for data designers who are setting up the Microsoft Dynamics GP preconfigured adapter. Integration supports Microsoft Dynamics GP version 2010, 2013, and 2015, but not the older Microsoft Dynamics GP 10.0.
The key differentiator for using Microsoft Dynamics GP as a data source is that it supports multiple companies. When setting up a Microsoft Dynamics GP data source, you can enter the companies you want to import in the Company Databases field in the data source settings tab and separate them with semicolons. Leaving this field blank imports all of the companies.
Supported Microsoft Dynamics GP Tables
The Microsoft Dynamics GP preconfigured adapter brings over the metadata for the tables below.
The tables marked with Yes are enabled by default. For enabled tables, integration
brings over the most common fields by default. The user can then edit the field
selection as desired.
System tables are indicated with an asterix:
Physical Name Friendly Name | Enabled by default | Notes |
|---|---|---|
GL00100 Account Master | Yes | |
GL00102 Account Category Master | Yes | |
GL10110 Account Current Summary Master | ||
GL10111 Account Summary History | ||
GL20000 Year-to-Date Transaction Open | Yes | |
GL30000 Account Transaction History | Yes | |
SY40100 Period Setup | Yes | |
SY40101 Period Header | Yes | |
SY01500 Company Master * | Yes | |
$ADP_ACCOUNTSUMMARY - Account Summary | Yes | This is a table created by the Agent. It is not a native DB table from GP. |
GL00105 Account Index Master | ||
GL00200 Budget Master Header | ||
GL00201 Budget Master Detail | ||
GL40200 Segment Description Master | ||
DTA10100 MDA Transaction Groups | ||
DTA10200 MDA Transaction Codes | ||
GL01201 Budget Summary Master View | ||
GL11110 Open Year Summary Master | ||
GL11111 Historical Year Summary Master | ||
SY02200 Posting Journal Destinations | ||
SY02300 Posting Settings | ||
SY03300 Payment Terms Master | ||
SY04200 Comment Master | ||
TX00101 Tax Schedule Header Master (header) | ||
TX00102 Tax Schedule Master (detail) | ||
TX00201 Tax Detail Master | ||
TX30000 Tax History | ||
SY003001 Account Definition Header * | ||
SY00302 Account Definition Detail * | ||
SY00801 Resource Activity * | ||
SY01400 Users Master * | ||
SY01402 System User Defaults * | ||
SY00800 Batch Activity * | ||
SY60100 User Access * | ||
SOP10100 Unposted/Work Transactions (header) | ||
SOP10200 Unposted/Work Transactions (line detail) | ||
SOP10101 Commissions Work and History | ||
SOP10102 GL Distributions Work and History | ||
SOP10103 Payment Work and History | ||
SOP10104 Process Holds Work and History | ||
SOP10105 Sales Taxes Work and History | ||
SOP10106 User Defined Work and History | ||
SOP10107 Tracking Numbers Work and History | ||
SOP10201 Serial/Lot Work and History | ||
SOP10202 Line Comment Work and History | ||
SOP30200 Historical Transactions (header) | ||
SOP30300 Historical Transactions (line detail) | ||
SOP60100 SOP-POP Link | ||
SOP60300 Customer Item Numbers | ||
GL10000 Transaction Work/Unposted (header) | ||
GL10001 Transaction Work/Unposted (line detail) |
Additional Considerations
These are some additional considerations for accessing Microsoft Dynamics GP data:
Simple column filters with comparison and logic expressions may be used when querying Microsoft Dynamics GP.
- You can not use operators (such as +, -, /, *, $, ||), scalar functions, nor case statements.
- The configuration can be further customized via advanced configurations available to Professional Services.
- For information on the interoperability of MS SQL Server's Integrated Security with integration, see Interoperability of MS SQL Server's Integrated Security.