Sunday, June 28, 2015

How to determine the first fiscal year in a Dynamics GP Company in SQL

For Dynamics GP users who are familiar with the Fiscal Period setup window, which exists at the company level, it should come as no surprise, rapidly identifying the First year an entity was established can be a time-consuming endeavor. 

To do this manually, one would need to go to Tools > Setup > Company > Fiscal Periods, and select the Year from the drop down list, in order to see the oldest year in each company in Dynamics GP. For organizations with numerous entities, especially those who have performed such an exercise, they know manually performing this task is daunting.

Users have to keep track of where they are in the process, make a list, validate the list, etc. Alternatively, the following query has proven useful in identifying the first year in Fiscal Period setup, in each of the organization's entities.

select 'Fabrikam' Company_Name, MIN(YEAR1) First_Year from TWO..sy40101
UNION 
select 'Company 1' Company_Name, MIN(YEAR1) First_Year from CO1..sy40101
UNION
select 'Company 2' Company_Name, MIN(YEAR1) First_Year from CO2..sy40101

It is important to note, the Company Name must be manually updated in the query above, and the Entity/Database ID must also be edited.

No comments:

Post a Comment