x Want to participate? Join the group to interact
  • State Verified Answer
  • Replies 2 replies
  • Subscribers 132 subscribers
  • Views 1849 views
  • Users 0 members are here
Browse Forums
Announcements
270
topics
260
replies
Databases and Operating Systems
760
topics
2k
replies
Financials Suite
1k
topics
3k
replies
General Discussion
2k
topics
10k
replies
Operational Suite
852
topics
2k
replies
Reporting and Analytics
1k
topics
3k
replies
Reports, Macros, and Customizations
877
topics
3k
replies
Sage CRM for Sage 300
183
topics
414
replies
A-Welty

Use SQL to identify the active fiscal period

SOLVED
Posted By

Looking for someone who knows their SQL tables here. 

How can I use SQL to identify the active fiscal period? I see the CSFSC table but what is the best way to determine which fiscal period start date to use? Looking at BGNDATE1, BGNDATE2, BGNDATE3, etc - is there a way to use the database to tell me either:

1. Which period is unlocked in all modules?

or

2. Today's relationship to a BGNDATE1-ENDDATE1 combination?

Curious how others have gone about this. Thanks for any ideas.

  • 0

    You have two ways.  If you're going to need to convert dates to FYFP often and need fast access then I'd make a table. You only need the period start date along with the FY/FP.  Store it in an optional table.

    If you need it to calculate once at the top of a stored procedure, for example, then create a SQL function to do that work.  It won't matter if it is somewhat of a brute force set of queries because it is a very small number of records to examine. Or load the results of the single record into variables and write the logic within the SQL function.

  • +1
    verified answer

    You'll find more info in  CSFSCST, which has a column for each period, with 0 if locked and 1 if open.  You could do a GROUP BY in SQL, containing the year and the sum of the values in each period.  The period whose sum is equal to the number of modules in your data is the one where everything is open.

*Community Hub is the new name for Sage City