Question Status

Verified
JonathanCampbell asked a question on 1 Jul 2014 10:48 AM

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.

Reply
Butch Adams responded on 1 Jul 2014 12:16 PM

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.

...b

Butch Adams
www.conexussg.com

Follow me: @butch_adams

Reply
JonathanCampbell responded on 1 Jul 2014 12:57 PM

Hi, Butch,

     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.

Thanks

Jon

Reply
Verified Answer
JonathanCampbell responded on 2 Jul 2014 12:42 PM

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.

Reply
Verified Answer
JonathanCampbell responded on 2 Jul 2014 12:42 PM

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.

Reply