System databases versus company databases

The first task of identifying where our data is located is making sure we are using the correct database! Microsoft Dynamics GP 2013 utilizes the Microsoft SQL Server platform as its database engine. When Dynamics GP 2013 is installed on our environment and a new company is created, the installation process creates several databases on a server that has been designated during the installation. These databases will store all the information entered through the Dynamics GP 2013 application, and we can use SQL Server Management Studio to access the underlying tables that store this data.

Microsoft Dynamics GP has two types of databases, a system database and company database(s). For first-time report developers and seasoned writers, knowing which of these databases stores a particular piece of information we need is crucial for an accurate report.

System databases

Prior to GP 2013, the system database was named the DYNAMICS database, and this default name could not be changed. Now, with the new installation of GP 2013, the system database can be named as something different than DYNAMICS. This functionality was introduced as a way to allow multiple sets of GP installations to reside on the same SQL server instance. This is especially important for companies providing GP hosting services for multiple companies on a single SQL server instance.

When Microsoft Dynamics GP is first installed and some initial settings are provided, a system database will be created. This database is the system database that can contain up to ten characters. It includes things such as records for each company that you create, the organization's registration information, and the maximum account framework.

Tip

While many of us can expect to work in environments where only one system database exists, and where that system database uses the standard 'DYNAMICS' name, we should still be careful not to hard-code references to this database. While we may have been able to take this shortcut in reports for earlier versions of GP, this will surely cause issues when we least expect. Whenever querying company data, use the DBNAME field from the SY00100 table in the company database to ensure the right system database name is being used.

From a reporting standpoint, there is certain information located in the system database that we may need to report on at one time or another. We have provided a quick reference for this information in the following list:

  • Multicurrency System Setups: This includes the setup of the currencies, the exchange rates, and the currency symbols for those organizations that process transactions in any number of foreign currencies.
  • Intercompany Setup: This is where the intercompany relationships are stored and the specific dues to/due from accounts are mapped.
  • Organizations Structures: This will include the organizational levels and entities that have been created for those organizations that use this feature.
  • User Master: This table stores information about the users in the ERP system, including their user ID and username.
  • User Tasks: This table stores the tasks that users set up in Dynamics GP. These are the tasks that are displayed on the users' home screens in GP.
  • Company Master: This stores company setup information, such as whether security is enabled, the company ID is in the form of the company database ID in SQL Management Studio, primary address information, tax schedule defaults, and any number of options for the company.
  • Security Setups: This includes all of the security tasks, security roles, and user security assignments.
  • User-Company Access: This includes the companies that each user has access to.

Company databases

Each company that we create in Dynamics GP has its own company database. As information such as transactions, accounts, and customer or vendor data is entered through GP, this information is recorded in individual fields. These fields comprise the smallest unit of data stored. All of this data makes up a record, and a record is grouped with similar records and stored in a table.

For obvious reasons, this data is segmented by a company database so that each company can maintain unique records. In addition to this transactional data, numerous additional company setups exist in the company database. As with the System database, we may need to report on some of these company system setups.

The following is a quick reference to the more common company setup tables:

  • Account Formats: This stores the chart of accounts format for the company.
  • Posting Definitions: This stores how the individual modules post to the General Ledger.
  • Company Locations: This lists additional addresses for each company.
  • Source Document Master and Audit Trail Codes: Every transaction is assigned both a source document and an audit trail code. This table can be used to report on the full description of these codes.
  • Shipping Methods Master: This stores the setup details of the shipping methods for the company.
  • Payment Terms Master: This stores the setup details of the payment terms created for the company.
  • Record Notes Master: This stores all of the record level notes for the particular company.
  • Comment Master: This stores predefined comments to be used across multiple series in Dynamics GP.
  • Electronic Funds Transfer: This stores the EFT setup information for the company for both Payables and Receivables modules. This includes customer and vendor banking information.
  • Period Setup: This stores the fiscal period setup for the company.
  • Sales/Purchases Tax Tables: These tables store the tax detail and tax schedule records as well as the tax summary amounts.
上一章目录下一章