I work on a product (an ETL tool) that reads account history data from SL (and other Dynamics GLs). The sample provided only spans 3 periods of 1 year of data. Unfortunately I don't have the time to learn to use SL at this time to add years/periods; I need to release this ETL tool imminently. To formulate a fiscal period end dates (into SQL Server datetime), I've been concatenating BudgetYear and FiscalPerEnd00 in table GlSetup, etc., but encountered a customer that has NULL in BudgetYear.
Not sure how to proceed. Is this customer's GlSetup corrupt, or is this a normal? If the latter, where would I find a year to use?
Obviously I'm an SL newbie; the code written by my predecessor created a kludge calendar from the actual history data, making it kind of difficult to create budget data beyond the current data years...any help would be appreciated.
Budget year is not a required field, so you cannot depend on it being there. (SL doesn't force you to budget!)
You could use PerNo to get your current period in yyyypp format or LastClosePerNo to get the last closed period.
Respond if you can help me narrow down what you need with more info.
Follow me: @butch_adams
Actually, what I'm looking for is a range of years and periods applicable to the G/L data. I assumed (perhaps incorrectly) that there would be a row for each fiscal year, since I noted that the sample, which as I mentioned only had 3 months of 1 year, specified the ending dates as Jan30, Feb28, etc. This is obviously not applicable for leap years...Perhaps I am asking too much; the AcctHist entries do have the periodID, so FY dates are somewhat immaterial, I could perform aggregation based on periodID rather than bracketing data by date. It would just be a bit more work for me.
My model for this mechanism is Dynamics NAV, which has a row for each period, and is completely date-driven.
I found it. The fiscal year table in SL is PJFISCAL. It has fy/period and period start and end dates, very similar to the one in Navision.