Archive for the ‘Fiscal Periods’ Category

Table Structure Definitions – Fiscal Period Setup

Hi all

This is the second article that I have decided to post in the table structure definition series. This time, I am going to explain the table structure of the Fiscal Period Setup (SY40100) table in Dynamics GP.

The structure of the table is as follows:

Column Name SQL Column Name Data Type Length Comments
Origin Closed CLOSED TINYINT 1  
Series SERIES SMALLINT 2 Defines the Series in Dynamics GP
Origin Description ODESCTN CHAR 51 Displays the individual transaction description
First Origin FORIGIN TINYINT 1  
Period ID PERIODID SMALLINT 2 Fiscal Period ID
Period Date PERIODDT DATETIME 16 Start Date of the Fiscal Period
Period Name PERNAME CHAR 21 Name of the Fiscal Period
Period Series[1] PSERIES_1 TINYINT 1 Financial Series Status
Period Series[2] PSERIES_2 TINYINT 1 Sales Series Status
Period Series[3] PSERIES_3 TINYINT 1 Purchasing Series Status
Period Series[4] PSERIES_4 TINYINT 1 Inventory Series Status
Period Series[5] PSERIES_5 TINYINT 1 Payroll Series Status
Period Series[6] PSERIES_6 TINYINT 1 Project Series Status
Year YEAR1 SMALLINT 2 Fiscal Year
Period Ending Date PERDENDT DATETIME 16 Ending Date of the Fiscal Period

The fiscal period screen looks as shown below.


If you want to query the table to find the status of all the series for all the fiscal periods for 2017, you will have to write the query as follows:

        PERIODID ,
        PERNAME ,
        PSERIES_2 AS SALES ,
FROM    SY40100
WHERE   YEAR1 = 2017
        AND SERIES = 0

The user interface (UI) for the fiscal period master is based on values in the PSERIES fields for all records with the SERIES as 0. So what the user sees on the fiscal period setup window are records which have the series values as 0. For these records, the columns like CLOSED, ODESCTN will always be zero or blank. The FORIGIN field will always be 1 for all the records returned by the query above.

However, there are other records in the table which have a non zero value for the SERIES field, where the SERIES field represents the following modules

1 – Financial Transactions

2 – Sales/Receivables Transactions

3 – Purchasing/Payables Transactions

4 – Payroll Transactions

5 – Project Transactions

So for every type of transaction in each of these series, there will be a record, which will contain either 0 or 1 in the CLOSED field which indicates if the fiscal period is opened for the specific transaction type in the selected series. A 0 value for the CLOSED field indicates that the fiscal period is open for the specific transaction type and a value of 1 indicates that the fiscal period is closed for the specific transaction type.

So if you want to check if the fiscal period is opened for all the transactions in the Financial Series, you can use the following query.

        ODESCTN ,
        YEAR1 ,
        PERIODID ,
FROM    SY40100
WHERE   YEAR1 = 2017
        AND SERIES = 2

The PSERIES columns will be 0 for all these records which have a non zero value for the Series.

So if the user wants to close all the transactions in a particular series, he can check the box against the specific series and the specific fiscal period on the Fiscal Period Maintenance window shown above. But if we want to lock the period for ONLY the bank transactions in the financial series, we can click on the Mass Close button on the Fiscal Period Maintenance window to achieve the same, as shown below.


Hope this article helps the community on the usage of the Fiscal Period Setup.

Until next post…

May 17, 2011 · veeyeskay · 3 Comments
Tags: , ,  · Posted in: Dynamics, Fiscal Periods, Great Plains, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 4,101