Archive for the ‘Multicurrency’ Category

Table Structure Definitions – Multicurrency Account Master

Hi all

Today, I am starting a series of articles where I am planning to explain the table structure of some of the tables in Dynamics GP.

To begin with, I am going to explain the Multicurrency Summary Master (as revaluations is one of the concepts which I like most in a multicurrency environment). The physical name of this table in SQL Server is MC00200. This table has 5 columns as shown below.

Column  Name SQL Column Name Data type Length Comments
Account Index ACTINDX INT   This is the account index for the GL account defined in GP.
Currency ID CURNCYID CHAR 15 This field stores the currency ID for which the accounts have access.
Revalue REVALUE TINYINT   Stores information if the account is revalued or not.
Revalue How REVLUHOW SMALLINT   Stores the revaluation option (whether Net Change or Period Balance).
Post Results To Post_Results_To SMALLINT   Stores how the revaluations are posted (whether to the same account or offset account)

The window from which this table gets updated is the Account Currencies window which opens from Cards >> Financials >> Account Currencies. This window has both header and detailed line level information (in the scrolling window), but both information is stored in a single table which leads to the complication in the way the table information needs to be fetched.

image

Note that this table contains two sets of information and the data has to be read from this table depending on the type of information which needs to be fetched.

Fetching Account Currency Access Information

If we need to fetch the details of the various currencies which each account has access to, we need to use the following query.

SELECT  *
FROM    MC00200
WHERE   CURNCYID <> ''
ORDER BY ACTINDX,
        CURNCYID

In this scenario, the last three columns in the table above (REVALUE, REVLUHOW, Post_Results_To) do not have any relevance and for all the records returned by the above query, these three columns will always have a zero value. Further, we only need to pull out all the records which has a value for the CURNCYID column. There will be NO record for the functional currency of the database, since every GL account which is created in a company has access to the functional currency by default and cannot be changed by the user. This is why you ONLY see the various foreign currencies in the Account Currencies window shown above.

So when the user updates any values in the scrolling window, the system ONLY updates the CURNCYID column for the specific account and the other three columns are never updated and always remain at 0.

Note: If there is a non-zero value is present for any of these columns, it would be a data consistency issue and these columns have to be updated to 0 for the records returned by the above query. Data inconsistency in these records would result in discrepancies during the revaluation process.

Checking the Revaluation Status of an Account

If we need to check the revaluation status of an account and the revaluation options for the account, we need to use the following query.

SELECT  *
FROM    MC00200
WHERE   CURNCYID = ''
ORDER BY ACTINDX

In this scenario, the second column in the above table (CURNCYID) does not have any relevance. In order to check the revaluation status of an account, we ONLY need to pull the records in this table with a blank CURNCYID value.

So when the user updates the revaluation options in the header section of the window above, the system ONLY updates the last three columns in the table above (REVALUE, REVLUHOW, Post_Results_To) for records which have a CURNCYID as blank. It DOES NOT update any records which have a value for the CURNCYID column.

Hope this information is helpful to all…

Until next post…

May 3, 2011 · veeyeskay · No Comments
Tags: , , , , ,  · Posted in: Dynamics, General Ledger, Great Plains, Multicurrency Total Views: 3,073