I’m moving on from an unsuccessful attempt to use AFR for extraction of a trial balance. Now I’m trying to make use of the Erp.BO REST api… I need to get GL period balances. Here is my call to Erp.BO.GLPeriodBalSearchSvc.
Does anyone know what that odataexception means in the image above? Is there a problem in the GL period balance results?
Has anyone been able to successfully use this REST interface for period balances (Erp.BO.GLPeriodBalSearchSvc) ? Is there a better interface? I’d like to be able to get all balances for a period, not just a single one. The REST method isn’t very flexible, as near as I can tell. But I don’t have access to API docs… perhaps there are wildcards that are supported for the account code parameter?
Any pointers would be appreciated. My GL balances are locked up tight and I can’t get at them! The epicweb docs are locked up tight too.
It is a bit concerning that there isn’t a multi-balance REST call that filters on company, book, year and period. I don’t want to cause a performance impact on the database.
I use the rest api to pull our trial balance into both excel and PowerBI for all of our financial reporting. I just pulled it from a BAQ via rest though and it is working just fine.
@Mike Thanks, I’m having better luck with BAQ than I was with Erp.BO.GLPeriodBalSearchSvc.
BAQ seems fast enough when we are gathering a limited amount of data (single period, single company, etc).
I’m really confused by the structure of the data that I’m getting out of BAQ. Do you use GLPeriodBal? Does it have all balances, including any accounts without movement in a given period? I cannot figure out the structure of that table. Did you have any documentation to help you to interpret that internal database schema? I guess I was initially avoiding BAQ because of the chance to misinterpret that internal data. Presumably the other (Erp.BO) api would provide more user-friendly results.
We are new to Epicor. It is our first month using the product. I’m wondering if the missing accounts need to be filled in by doing some special operation (closing the periods, or performing a GL period refresh, or creating a special fiscal period with a number of zero, or some such thing) In this forum I have found some references that suggest using GLPeriodBal for general ledger, but they aren’t total explicit about how to form it into a trial balance. Any help would be appreciated.
I think there are a few things you need to address in the BAQ. I am going from memory so please confirm.
The income statement accounts are straight forward but I think you need to play around with the balance sheet accounts a bit to get the ending balance for the period since the balance amount on the table seems to be the delta for that period. But if you want the ending balance for for a balance sheet account for a month, it should be the sum of all rows for that year with a period less than or equal to the period you are looking for. There is a period “0” which has the opening balance for the year.
As you mentioned, it doesn’t have all G/L accounts for a given period BUT it should have all accounts that had any activity, even if it nets to zero. So, depending on how your are using it that may need to be addressed. I just linked the BAQ up to an excel file with REST and then I have a few tabs with various formats that reference the data pulled in. If the account didn’t have activity and isn’t in the data for that period it doesn’t hurt for what I am doing. If that is an issue for you, then you will need to address it in the BAQ which is doable depending on how handy you are with it. If you need a quick fix because you just went live, post a journal entry for 0 that hits every account… for every period you need it for.
As for documentation, nothing great that I found. Mostly just bits and pieces from posts on here plus trial and error. Maybe there is some out there but I couldn’t find it.
Last thing. A lot of people use XL Connect for stuff like this which would probably be easier if you are just going into excel. I wasn’t a fan of it but I know the majority of people really like it.
Thanks for the detailed explanation about getting these numbers. We don’t have the period “0”, for some reason. I think there is a configuration, or an operation that we had missed along the way (maybe “verify balances” operation?) I’m also noticing that all our opening balances are zero for the GLPeriodBal records. Its odd that the table is called “Bal” when it does NOT have all balances (only the ones with movement)!?
One last thing I noticed is that I only have records with a “BalanceType” of S and D. But some posts refer to “BalanceType” of B. We don’t have those and that might indicate that we have some misconfiguration within our G/L.
There are certainly some gaps I need to fill in my understanding of this G/L data. I certainly wish I could find some documentation. You did pretty well to build your Power Pivot workbooks on this data without docs!
If you just run the out of the box trial balance report does that tie out to what it should be? Also, how did you load your historical G/L activity when you went live? Did you run the “Transfer Opening Balances to Next Year” program… It is under General Ledger>General Operations. I am just guessing but that may create your period 0 record on this table. To be clear though, I am referring to period zero in the context of the GLPeriodBal table, not as a period in your actual fiscal period setup.
For the balance type, I only have S and D also. Not sure what would make you have a balance that is both but we are getting by just fine…
@Mike I’m a software developer and I’m new to the ERP. I don’t think we’ve used that “Transfer Opening Balances” program yet. I’ll be sure to ask soon. And we certainly don’t have a period “0” in the underlying table.
But most of the trial balance BAQ’s against the GLPeriodBal table seem to make an assumption that there should be a period “0”. So something probably isn’t right in our configuration.
I really wish these forums were also moderated by Epicor employees as well. Getting your G/L data out of the database shouldn’t involve guesswork or trial-and-error. If I ever find any formal documentation about this “GLPeriodBal” table, I will post it here.
Here is the link to a forum thread that talks about getting “B” -type records in the GLPeriodBal table. (Or at least that is what I gather, without actually trying it for myself):
