Commonly Used Tables:
GL00100 – Account Master
GL00102 – Account Category Master
GL00105 – Account Index Master [contains concatenated account number]
GL00200 – Budget Master Header
GL00201 – Budget Master Detail
GL10000 – Transaction Work/Unposted (header)
GL10001 – Transaction Work/Unposted (line detail)
GL10110 – Open Year Summary Master
GL10111 – Historical Year Summary Master
GL20000 – Open Year Posted Transactions
GL30000 – Historical Year Transactions
GL40200 – Segment Description Master
DTA10100 – MDA Transaction Groups
DTA10200 – MDA Transaction Codes
MC00200 – Multicurrency Account Master
Summary Views:
GL01201 – Budget Summary Master View [contains concatenated account number]
GL11110 – Open Year Summary Master
GL11111 – Historical Year Summary Master
[Note: These views show debits, credits and net changes per period per account, they do not show the period balances. The reason to use these and not the corresponding GL10110 and GL10111 tables is that these views also include the Account Description.]
ACCTTYPE (Account Type):
1 – Posting Account
2 – Unit Account
3 – Posting Allocation Account
4 – Unit Allocation Account
PSTNGTYP (Posting Type):
0 – Balance Sheet
1 – Profit and Loss
Active:
0 – Inactive
1 – Active
TPCLBLNC (Typical Balance):
0 – Debit
1 – Credit
FXDORVAR (Fixed or Variable):
1 – Fixed Allocation
2 – Variable Allocation
ACCATNUM (Account Category):
1 – Cash
2 – Short Term Investments
3 – Accounts Receivable
4 – Notes Receivable
5 – Inventory
6 – Work In Process
7 – Prepaid Expenses
8 – Long-Term Investments
9 – Property, Plant and Equipment
10 – Accumulated Depreciation
11 – Intangible Assets
12 – Other Assets
13 – Accounts Payable
14 – Notes Payable
15 – Current Maturities Of Long-Term Debt
16 – Taxes Payable
17 – Interest Payable
18 – Dividends Payable
19 – Leases Payable (Current)
20 – Sinking Fund Payable (Current)
21 – Other Current Liabilities
22 – Long-Term Debt
23 – Common Stock
24 – Preferred Stock
25 – Additional Paid-In Capital – Common
26 – Additional Paid-In Capital – Preferred
27 – Retained Earnings
28 – Treasury Stock
29 – Common Dividends
30 – Preferred Dividends
31 – Sales
32 – Sales Returns And Discounts
33 – Cost Of Goods Sold
34 – Selling Expense
35 – Administrative Expense
36 – Salaries Expense
37 – Other Employee Expenses
38 – Interest Expense
39 – Tax Expense
40 – Depreciation Expense
41 – Income Tax Expense
42 – Other Expenses
43 – Other Income
44 – Charges Not Using Working Capital
45 – Revenues Not Producing Working Capital
46 – Gain/Loss On Asset Disposal
47 – Amortization Of Intangible Assets
48 – Non-Financial Accounts
[Note: Many installations that we have seen have changed the account category names to better fit their chart of accounts. Any categories created in addition to these defaults are marked with a * in GP, even thought they have a valid category number in the GL00102 table.]
DECPLACS (Decimal Places):
[Used for Unit Accounts]
1 – 0
2 – 1
3 – 2
TRXTYPE (Transaction Type) in GL10000:
0 – Standard
1 – Reversing
Series (in GL tables):
1 – All
2 – Financial
3 – Sales
4 – Purchasing
5 – Inventory
6 – Payroll – USA
7 – Project
10 – 3rd Party
DTA_Series (in DTA tables):
2 – Financial
3 – Sales
4 – Purchasing
10 – Invoicing
11 – Sales Order Processing
12 – Purchase Order Processing
BACHFREQ (Batch Frequency):
1 – Single Use
2 – Weekly
3 – Biweekly
4 – Semimonthly
5 – Monthly
6 – Bimonthly
7 – Quarterly
8 – Miscellaneous
PSTGSTUS (Posting Status) in DTA10100:
1 – Work
2 – Open
3 – History
4 – Originating (not in the GL yet)
ACCTENTR (Allow Account Entry) in GL00100:
0 – No (do not allow account entry)
1 – Yes (allow account entry)
General Ledger reporting notes:
- Use ACTINDX to link account numbers from transactions.
- GL00100 will have the account name and GL00105 will have the concatenated account number.
- Unposted transactions are stored in 2 tables (GL10000 and GL10001), whereas posted transactions will be in one table (either GL20000 for open year or GL30000 for closed year).
- General Ledger SQL views
—
Last Updated: March 8, 2019
Victoria Yudin 
Hi Victoria,
Thank you for all your information you have here, it is very helpful! I was wondering if you could help me with knowing the field links for two tables.
The tables are Account Transaction and Transaction Work Table. I am trying to figure out the correct linkage. I have tried source document to source document and trx source to trx source but they do not seem correct. This was a smartlist we had before that was accidently deleted and I am trying to re-create it.
The purpose of the report is to see all account transactions posted/unposted in one spot so we are able to filter and use in smartview.
Best,
Alyssa
LikeLike
Hi Alyssa,
Please try this: https://victoriayudin.com/2011/04/27/sql-view-for-all-gl-transactions-in-dynamics-gp/
-Victoria
LikeLike
Victoria, I’m trying to use GL11110 to view my current balance sheet but I’m having trouble determining how to filter for entries that are posted only. I don’t see a way to do this. Any thoughts?
Thank you
LikeLike
Hi Joe,
This view only includes posted entries. Nothing unposted will show up here.
-Victoria
LikeLike
Do the summary master views (i.e. GL11110) factor in XCHGRATE or CURNCYID?
LikeLike
Hi Ruben,
These views show the data in functional currency only.
-Victoria
LikeLike
Great to join in. Victoria I am a fan and have used your site for meaningful GP solutions. I use GP 2015 R2
LikeLiked by 1 person
Hello Victoria,
Is it possible to see what unit accounts are tied to a GL account? Basically need to be able to query out what ACCTTYPE (Account Type) 1 – Posting Accounts are related to what ACCTTYPE (Account Type) 2 – Unit Account.
LikeLike
I’m trying to figure out where and how a PO or Receipt or Invoice gets assigned the Account Number that shows up in GL… ACTNUMBR_1 for example I have looked at items, lines, headers and vendors. I’m trying t reflect what Account open PO’s would be as if they had been pulled from dbo_GL11110 for example.
LikeLike
Here is my understanding of how it works:
On a PO there are only accounts stored for inventory:
– If the item is an inventory item then the account number is taken from the Item accounts…if that is blank, then from company posting accounts under Inventory.
– For non-inventory items the account is taken from the Vendor accounts…if that is blank, then from company posting accounts under Purchasing.
Also important to note that a Purchase Order transaction will never get “posted”, so you will not see any GL entries that came “from a PO”.
On a Receipt: it depends on if it’s coming from a PO or not. If yes, then it’s from the PO, if not, see above. That’s for the line items. All the other accounts are taken from the vendor and if they are not there, then the company posting accounts.
Hope that helps,
-Victoria
LikeLike
I went to Cards > Financial > Accounts and brought up an income account. It is categorized as Sales, P&L, with a normal credit balance. I click on the Summary and the totals reflect a significant credit balance, but the net change and period balance show as negative. I would expect an Income account with a credit balance to show a positive net change and balance. Is it possible the client had it categorized incorrectly at one time? For example, maybe they had it categorized as Expense with a normal credit balance.
LikeLiked by 1 person
Hi Damieyn,
Net change and period balances for all accounts are always shown as positive for debits and negative for credits. This is correct and working as it should be. If the display changed depending on the type of account or typical balance set up, the user would have to check this every time and think about what the balance they are seeing means, which could result in a lot of uncertainty and confusion.
-Victoria
LikeLike
Thank you! That is very helpful.
LikeLike
Thank you! That is very helpful information.
LikeLike
Victoria,
I’m pulling fields from GL20000 and wondering if there’s a unique id field available that I call pull also. I’m refreshing my database and need to have a unique id field so that I don’t replicate records but rather simply refresh those that were previously pulled. Appreciate any insights you might have.
Tavo
LikeLike
Hi Tavo,
Technically the DEX_ROW_ID will always be unique. However, if you are comparing data from different times, there is a possibility that the DEX_ROW_ID’s will be changed, so you cannot always use that for comparison. Normally the uniqueness of each record will be determined by the following 3 fields: JRNENTRY, SEQNUMBR, RCTRXSEQ. The only exception to this will be the BBF (balance brought forward) entry.
So….are you looking to copy data that is already there from one database to another? If so you can check for the combination of JRNENTRY and RCTRXSEQ. I think that should work in your scenario.
-Victoria
LikeLike
Victoria, I have rows that do not have combined unique values of JRNENTRY, SEQNUMBR, RCTRXSEQ in GL20000. Should this be possible?
LikeLike
I found the issue. We have multi currency, and when I add the Currency ID to the group by, it becomes unique.
LikeLike
Brent, what is the SOURCDOC of the lines where you are seeing this?
-Victoria
LikeLike
Victoria, the majority of them were BBF (Balance Brought Forward, and the reason for the duplication, I found out was currency. There were multiple Currency Balances.
LikeLike
Brent,
For BBF transactions, I would expect those values to all be the same – this is normal.
-Victoria
LikeLiked by 1 person
Hi Victoria,
I’m trying to find out how the system controls which accounts are used when posting Sales Orders. I see the sales, inv, cogs accounts on line item, however, I don’t see how the system is determining what liability, sales tax, and freight accounts to use.
Would you be able to point me where in the database this is defined?
Thank You
LikeLike
Hi Kelly,
I have a blog post on this that may help: https://victoriayudin.com/2008/12/08/sales-transaction-entry-gl-distributions-in-dynamics-gp/
Let me know if you have other questions that this does not answer.
-Victoria
LikeLike
We have discovered that a GL account was somehow set up incorrectly, and we get an error message alerting us that This is not a Posting Account. I can see in the GL00100 table that this account is set up as an ACCTTYPE 0 with PSTNGTYP 0. How can we correct the Account Type and Posting Type? Thank you for any assistance you can offer.
LikeLike
Hi Rachel,
Sound like something went wrong when you were setting up the account. I believe ACCTTYPE should never be zero – you can change that to a 1 in SQL and see if it resolves the problem. PSTNGTYP of 0 is fine – that means Balance Sheet. If it should be P&L you can change it once you fix the ACCTTYPE and can access the account in the GP application.
-Victoria
LikeLike
Thank you!
LikeLike
Great post! However, I’m still not sure where the logic is defined to populate some of the accounts. Like I know the item has some of the accounts defined there, but the receivable account is not among them.
You can see that account by looking at the distribution on the sales order, but how does GP know to use that specific receivable account? It’s not like the user has to enter the receivable account every time they create a sales order.
LikeLike
Kelly,
The receivables account for sales invoices and returns is always taken from the customer. If the customer does not have one specified, it’s taken from the company posting accounts.
-Victoria
LikeLike
Would you happen to know the database table that has those company posting accounts?
LikeLike
Kelly,
Those are in the SY01100 table. I have added that to my Company/System tables page: https://victoriayudin.com/gp-tables/companysystem-tables/.
-Victoria
LikeLike
I got it all tied out. Thank You!
LikeLike
Hi Greg,
You should normally be able to use the created date (CREATDDT) in GL00100 for this.
-Victoria
LikeLike
Hi Victoria,
Is there a way to join the following three tables (using union all) to the sales lines in SOP30300 – Historical Transactions (line detail)?
GL10001 – Transaction Work/Unposted (line detail)
GL20000 – Open Year Posted Transactions
GL30000 – Historical Year Transactions
I am trying to make a report that classify GL entries based on item numbers.
Is this possible? Please advise.
Thank you!
LikeLike
Hi Acacia,
There is no reliable way to do this because Dynamics GP does not post to the General Ledger based on line items on a sales transactions. For example, if you have 10 line items on a sales transaction with 3 different sales accounts, you will only see 3 sales account distributions in the GL, not 10.
-Victoria
LikeLike
You can create a view that will allow you to pull the data and compare/analyze where items posted to the GL:
SELECT * FROM
–Sales Line
FROM SOP30300 a
–Sales Header
JOIN SOP30200 c on a.SOPNUMBE = c.SOPNUMBE AND a.SOPTYPE = c.SOPTYPE
–Inventory Account Number
LEFT OUTER JOIN (SELECT a.ActIndx,InventoryAcct = a.ActNumSt, b.ActDescr, a.ActNumbr_1, a.ActNumbr_2
FROM gl00105 a
JOIN gl00100 b ON a.ActIndx = b.ActIndx) f on a.InvIndx = f.ActIndx
–Cost Account Number
LEFT OUTER JOIN (SELECT a.ActIndx, CostAcct = a.ActNumSt, b.ActDescr, a.ActNumbr_1, a.ActNumbr_2
FROM gl00105 a
JOIN gl00100 b ON a.ActIndx = b.ActIndx) g on a.CSLSIndx = g.ActIndx
–Sales Account Number
LEFT OUTER JOIN (select a.ActIndx, SalesAcct = a.ActNumSt, b.ActDescr, a.ActNumbr_1, a.ActNumbr_2
FROM gl00105 a
JOIN gl00100 b ON a.ActIndx = b.ActIndx) h ON a.SLSIndx = h.ActIndx
WHERE a.SOPTYPE IN (3,4)
AND c.VoidStts <> 1
Keep in mind both 3 (Invoice) and 4 (Return) document types have positive values in the tables, so you will need to adjust for Debit and Credit values based on what entry you are comparing.
Hope this helps.
LikeLike
Catherine,
This simply shows you the account numbers defaulted on each item in SOP. Unfortunately, there is absolutely no way to tell whether those accounts were actually used on the SOP document when it was posted. There is also no way to definitively link the amounts of the GL entry lines to the items numbers. 😦
-Victoria
LikeLike
Hi Vitoria,
Is there a way to join the SOP30200 or SOP30300 with GL20000 and GL30000?
I need to get the journal entry for each transaction (SOP30200), but can’t find a way to join these tables.
Thank you
LikeLike
Hi Di,
If you are posting in detail and if you just want the journal entry and not the actual detail line in the GL, then you can link on the following:
GL.ORDOCNUM = SOP.SOPNUMBE and GL.ORTRXTYP = SOP.SOPTYPE and GL.ORGNTSRC = ‘SLSTE’
GL in this case can be either GL20000 or GL30000 and SOP can be either SOP30200 or SOP30300
-Victoria
LikeLike
Thank you.
LikeLike
Question, doesn’t SmartList already do this for you, using Series = Sales and pulling in the Originating columns, especially Originating Document Number?
LikeLike
Yes, SmartList will do this for you in the Account Transactions SmartList. However, if you wanted to see it from the sales side, or need it for a custom report, then linking it in SQL might be required.
Also, be careful about using just the originating number – those are not required to be unique. For example in SOP you can have a return and an invoice be the same exact SOP Number. Sometimes people do this on purpose to show that the transactions are related. Sometimes it just happens because of the numbering schemes that are set up.
-Victoria
LikeLike
Thank you Victoria
LikeLike
I’ve run a select * from gl10110, for some reason the account number columns are completely blank. Is there some setting in GP that’s keeping the data from being populated?
LikeLike
Hi Rob,
The ACTNUMBR_1, ACTNUMBR_2, etc. columns have been empty in this table as far back and I can remember. What you want to do is link to the GL00100 or GL00105 table on the ACTINDX in GL10110. That will get you the account number or name or whatever other info you need.
-Victoria
LikeLike
am new to accounting
what data table contains every single transaction regardless of type?
Not sure if there is such a table. I assume all transactions go into one table but are categorized and appear in various “views” depending on type.
thanks
Alex Glaros
LikeLike
Hi Alex,
You are right – there is no one table that holds everything. If there was, you would see terrible performance in the GP application. Every module has multiple tables for transactions. If you’re just getting into this and are looking for more information, there are a lot of blogs like this one out there to help.
-Victoria
LikeLike
We are just running a financial smartlist and the column “originating trx type” has data that is showing reversing for our invoices. We were trying to understand how that could be.
LikeLike
Aha. Looks like that column in SmartList is bringing in some bogus data. It IS mapping a 1 in that database field to ‘Reversing’, like you said, but incorrectly. I would not use this column in SmartList, as it’s not going to help you with anything.
-Victoria
LikeLiked by 1 person
Victoria- We are trying to figure out which GL table to use if we want to find the date of the last General Journal Entry made in one of our companies. Do you have any suggestions on which table to use to accurately pull this information?
Thanks!
Kaley
LikeLike
Hi Kaley,
Are you looking for the actual date someone last entered a transaction or the date of the last transaction or something else? Also, are you talking about entries entered directly into the GL or anything that may have been posted in a subledger that went to the GL? There are a lot of different possibilities, so if you can be very specific about what you’re looking for, I can try to help.
-Victoria
LikeLike
Hi Victoria,
We are looking for the actual date someone last entered a transaction directly into the GL.
Hope this helps clarify.
Thanks!
Kaley
LikeLike
Hi Kaley,
Thanks for the clarification. Unfortunately, GP does not track this. You can sometimes use the DEX_ROW_TS in the GL10001 table and the GL20000 table, but that will store the last time a change was made to the transaction, not when it was actually entered.
-Victoria
LikeLike
The transaction originated from the payables module. I was am trying to determine what the what the value of “1” means in the field “ORTRXTYP” in the GL20000 table. I assumed it meant reversing because when I run a smartlist and choose the column “originating TRX Type” it says reversing. What does “reversing” mean in the context of an invoice? I also see invoices that have nothing and clearing in the originating trx type field.
LikeLike
Tamara,
Again, you’re starting with an incorrect premise. A value of 1 in ORTRXTYP in the GL20000 table does not mean ‘reversing’. The values in the ORTRXTYP field in the GL20000 table are not an absolute list. They are relative depending on where the original transaction was entered. If the original transaction was entered in the PM (Payables Management) module, as you are saying, a 1 in ORTRXTYP will mean payables invoice and a 6 will mean payables payment. Those should cover 95% of your PM transactions typically. For other modules, the values in ORTRXTYP will be different. For example, for the SOP module you should not see anything except 3 and 4 in the ORTRXTYP. For RM transactions, you will mostly see 9, but may see others, depending on how you are using GP.
Bigger picture – what is it that you are trying to do that leads you to ask about this field in the first place?
-Victoria
LikeLike
The transaction came from the payables subledger. Do you know what reversing might mean in this context?
LikeLike
There is no such thing in Payables. A payables transaction with the ORTRXTYP of 1 means an invoice. You can see all the other values under the DOCTYPE on this page: https://victoriayudin.com/gp-tables/pm-tables/.
-Victoria
LikeLike
I looking at the GL20000 table and there is a field called “ORTRXTYP” which is the originating transaction type. The majority of our invoices have a “1” as the type. I believe the “1” represents a reversing entry but I’m confused as to how this is possible. We aren’t selecting the invoices to be reversing and they don’t reverse the following month. Can you tell me what this field really represents?
LikeLike
The ORTRXTYP is the transaction type of the transaction in the originating subledger. So the values will have different meanings depending on where the transaction came from, which you can typically see in the ORGNTSRC and SOURCEDOC columns.
LikeLike
Hi Victoria
We are using GP2015 R2
I am trying to meet Mozambique statutory requirements by entering the government code for each of the GL accounts.
I am trying to do this using the ALIAS field in the Financial> Cards> Account window
This field will not let me use the same alias for two different accounts.
Any suggestions?
Thanks
Rhys
LikeLike
Hi Rhys,
I am not familiar with Mozambique requirements, however, the Alias must be unique, so it does not sound like a good place to store this data. If all you need is to store additional data for each account, perhaps you can use one of the User Defined fields on the same window? There are 4 of them and I rarely see all of them used, so hopefully you have one available.
-Victoria
LikeLike
Thanks Victoria, we have worked around it with a bit of creative list making 🙂
LikeLike
Hi Victoria
We do a daily close each day and post to our GL. Finally , after 15 years, the daily post was forgotten nor was this noticed the following day so when the post was done the following evening, two days of transactions were rolled into one. Is there any way to rectify this?
You can tell I’m hopeless as I can’t even figure out how to use this blog properly.
Thanks
Peter
LikeLike
Hi Peter,
Unfortunately, your “daily close” is something specific to your company, so it’s pretty difficult to help with your question generically and without a lot more detail. What does your daily close entail? What exactly was rolled into one and how?
-Victoria
LikeLike
Victoria, can you think of any reason why there would be a different posted user in the GL than what shows in the PM module when our Posting Setup in PM is set to Post Through General Ledger Files?
LikeLike
Ben,
The main reason is probably that some transactions do not auto-post through through the GL, even though that’s your setting. For example voids. So there will be some batches that always end up having to be posted manually in the GL.
-Victoria
LikeLike
Hi Beth,
I believe it’s in GL00100 and called Clear_Balance.
-Victoria
LikeLiked by 1 person
Hi Victoria, Is there a way to modify the general ledger entry “description” field through SQL? The amounts and dates are fine, it is just the notation that I want to change.
Thank you
Marcia
LikeLike
Marcia,
Do you mean the field called “Reference” on the General Ledger Transaction Entry window? If so, you can update the REFRENCE field in GL20000 (and GL30000) in SQL to change that. If you mean something else, can you please elaborate?
-Victoria
LikeLike
Hi Victoria,
Is there a table that holds a value for the new check box “Clear Balance During Year-End Close’ on the Unit Account Maintenance screen? We have over 2300 unit accounts and would like to take advantage of this new GP feature.
Thanks,
Beth
LikeLike
Thanks Victoria! I will try that.
LikeLike
I have a query that returns what I’m looking for – beginning balance, debit, credit, net change, ending balance by account for period/year. My issue is that if an account doesn’t have activity for a period, it’s not in the GL10110 (or GL10111) table, so it doesn’t get a row in my resulting query. I need a row for each period for a report I’m writing. So let’s say at the end of Period 1, Account 12345 has a balance of $10,000. It has no debits or credits for Period 2. But I need a row in my query for Period 2 with Beg Bal $10,000, Debit $0, Credit $0, Net Change $0, End Bal $10,000.
Any ideas how to get a row for each period/year even if account has no debit/credit for the period? Thanks in advance! I use your website daily.
Here’s my query.
SELECT TOP (100) PERCENT ACTINDX, GPACCOUNTNO, DESCRIPTION, DIV, OFC, SLS, ACT, MDE, FISCALYEAR, FISCALPERIOD, FISCALPERIODNAME, BEGBAL, DEBIT, CREDIT, NETCHANGE, ENDBAL
FROM (SELECT B.ACTINDX, RTRIM(B.ACTNUMST) AS GPACCOUNTNO, RTRIM(C.ACTDESCR) AS DESCRIPTION, RTRIM(B.ACTNUMBR_1) AS DIV, RTRIM(B.ACTNUMBR_2) AS OFC, RTRIM(B.ACTNUMBR_3) AS SLS, RTRIM(B.ACTNUMBR_4) AS ACT, RTRIM(B.ACTNUMBR_5) AS MDE, A.YEAR1 AS FISCALYEAR,
A.PERIODID AS FISCALPERIOD, E.PERNAME AS FISCALPERIODNAME,
(SELECT ISNULL(SUM(PERDBLNC), 0) AS Expr1
FROM dbo.GL10110 AS D
WHERE (ACTINDX = A.ACTINDX) AND (YEAR1 = A.YEAR1) AND (PERIODID <= A.PERIODID)) – ISNULL(A.PERDBLNC, 0) AS BEGBAL, A.DEBITAMT AS DEBIT, A.CRDTAMNT AS CREDIT, ISNULL(A.PERDBLNC, 0) AS NETCHANGE,
(SELECT ISNULL(SUM(PERDBLNC), 0) AS Expr1
FROM dbo.GL10110 AS D
WHERE (ACTINDX = A.ACTINDX) AND (YEAR1 = A.YEAR1) AND (PERIODID <= A.PERIODID)) AS ENDBAL
FROM dbo.GL10110 AS A INNER JOIN
dbo.GL00105 AS B ON B.ACTINDX = A.ACTINDX INNER JOIN
dbo.GL00100 AS C ON C.ACTINDX = A.ACTINDX INNER JOIN
dbo.SY40100 AS E ON E.YEAR1 = A.YEAR1 AND E.PERIODID = A.PERIODID AND E.SERIES = 0
UNION ALL
SELECT B.ACTINDX, RTRIM(B.ACTNUMST) AS GPACCOUNTNO, RTRIM(C.ACTDESCR) AS DESCRIPTION, RTRIM(B.ACTNUMBR_1) AS DIV, RTRIM(B.ACTNUMBR_2) AS OFC, RTRIM(B.ACTNUMBR_3) AS SLS, RTRIM(B.ACTNUMBR_4) AS ACT, RTRIM(B.ACTNUMBR_5) AS MDE, A.YEAR1 AS FISCALYEAR,
A.PERIODID AS FISCALPERIOD, E.PERNAME AS FISCALPERIODNAME,
(SELECT ISNULL(SUM(PERDBLNC), 0) AS Expr1
FROM dbo.GL10110 AS D
WHERE (ACTINDX = A.ACTINDX) AND (YEAR1 = A.YEAR1) AND (PERIODID <= A.PERIODID)) – ISNULL(A.PERDBLNC, 0) AS BEGBAL, A.DEBITAMT AS DEBIT, A.CRDTAMNT AS CREDIT, ISNULL(A.PERDBLNC, 0) AS NETCHANGE,
(SELECT ISNULL(SUM(PERDBLNC), 0) AS Expr1
FROM dbo.GL10111 AS D
WHERE (ACTINDX = A.ACTINDX) AND (YEAR1 = A.YEAR1) AND (PERIODID <= A.PERIODID)) AS ENDBAL
FROM dbo.GL10111 AS A INNER JOIN
dbo.GL00105 AS B ON B.ACTINDX = A.ACTINDX INNER JOIN
dbo.GL00100 AS C ON C.ACTINDX = A.ACTINDX INNER JOIN
dbo.SY40100 AS E ON E.YEAR1 = A.YEAR1 AND E.PERIODID = A.PERIODID AND E.SERIES = 0) AS Z
ORDER BY GPACCOUNTNO, FISCALYEAR, FISCALPERIOD
LikeLike
Hi Sherry,
To get a row for every account and period, you have to start with a query that results in that. Kind of like a ‘master list’. Maybe something that joins the account master table and the fiscal periods table like this:
selecta.ACTINDX,
f.YEAR1,
f.PERIODID
from GL00100 a
cross join
(select distinct PERIODID, YEAR1
from SY40100) f
Then you can do a left outer join to add your existing query to this. Hope that helps.
-Victoria
LikeLike
I need a way of uniquely identifying a GL batch that can be used by the end user to trace back to that specific batch in the UI. In systems I’m familiar with, each GL batch has a unique batch number. From what I’m seeing in GP, batch numbers seem to be reused for recurring batches – i.e. not unique. Is it a combination of fields that uniquely identify a batch?
I’m sure this has an obvious answer, but I’m stumped.
LikeLike
May,
You are correct. There is nothing about a Batch ID that must be unique. Even without recurring batches, a user can re-use the same batch ID over and over. Why do you need this? Perhaps if you explain what you’re ultimately looking to do someone can suggest a different way of accomplishing it.
-Victoria
LikeLike
I have developed an external database to calculate deferred revenue. Each month, this database is the source of the JE to recognise the revenue (i.e. move it from Deferred Revenue to Revenue). Once the JE has been created the status of the recognised records are updated so they can no longer be changed. I need a way of connecting the details to the ultimate JE. In any other financial system, that would be the GL Batch Number. What are my options in GP?
LikeLike
Thanks May,
That helps me understand what you’re doing. You certainly cannot use the batch for this in GP.
The following 4 fields combined will give you a unique record in GL20000:
OPENYEAR, JRNENTRY, SEQNUMBR, RCTRXSEQ.
These 4 will give you a unique record in GL30000: HSTYEAR, JRNENTRY, SEQNUMBR, RCTRXSEQ.
However, you will also need to exclude any records with SOURCDOC in (‘BBF’,’P/L’). Those are the year end close transactions and they should be excluded as they are not real transactions.
Hope that helps with what you’re looking to do.
-Victoria
LikeLike
Thanks, Victoria. I really appreciate your help. Are any of those fields visible to the end user in the UI?
LikeLike
Hi May,
Only the journal entry number and the year. Typically that should be enough. If there are a lot of recurring entries, you can maybe add the TRXDATE. Unless they have recurring transactions that are posted multiple times with the same date (possible, but extremely unlikely) that should do it.
-Victoria
LikeLike
That will work since this is a ‘once a month’ JE. Thank you! You are a lifesaver!
LikeLike
Hi Victoria,
I am trying to build a SmartList that will pull info from the GL00100 table as well as the segment description from the GL40200 table. Is there a way to link these tables in SmartList Builder?
Thanks,
Colleen
LikeLike
Colleen,
Once you’re doing anything that’s not plain vanilla in SmartList Builder, I typically recommend creating your logic in a SQL view, then simply pointing a new SmartList to that view. To link GL00100 to GL40200 you would do something like this:
select a.*, s.DSCRIPTN
from GL00100 a
left outer join GL40200 s
on a.ACTNUMBR_1 = s.SGMNTID and s.SGMTNUMB = 1
That example is for the first account segment, you can change the 1 to whatever segment you’re looking for on the last line.
Hope that helps.
-Victoria
LikeLike
Not clear how I can post a new question so I’ll try to ask here:
Is it possible to insert unposted transactions (J/E batches) directly into GL10000 and GL10001?
LikeLike
Hi Mike,
While it is technically possible to insert new transactions directly into tables, it is strongly not recommended. Also, you will likely need more tables than just GL10000 and GL10001 if you also need to insert new batches.
The recommended approach is to use an import tool, such as Integration Manager or eConnect, as they will maintain the integrity of the business logic built into Dynamics GP.
-Victoria
LikeLike
Hi Victoria,
I am attempting to link the AccountTransactions view to a customer/vendor. I see I can use the Originating Master ID to link to either customer master or vendor master table, however we have customers that are also vendors and they have the same ID set up in both tables. I am looking for the best way to link an account transaction to either the vendor or customer. I’m thinking I can use the Source Document possibly to choose whether it was a vendor or customer, but I’m not familiar enough with the Source Document codes to figure it out. Just curious on your thoughts. Any help would be greatly appreciated!.
Kyle
LikeLike
Kyle,
I think you should be able to use the Series column for this – if it’s Purchasing then you have a vendor, if it’s Sales then you have a customer.
-Victoria
LikeLike
HI Victoria,
I am using GP 2015 and the budget tables you mention do not exist in my database. Do you have an updated table list for GP 2015?
LikeLike
Hi Justin,
I just checked my GP 2015 and both GL00200 and GL00201 are there. Not sure why you’re not seeing them…where/how are you looking?
For what it’s worth – it would be extremely unlikely for Microsoft to remove a table once it’s there. Even if they stop using it…for legacy support and reporting, it will be kept.
-Victoria
LikeLike
Hi Victoria,
I’m in Query Designer in SSRS Report Builder. I am in the tables section above views, stored procedure and Table-Valued Functions. I see many of the tables you list above but the GL00200 and GL00201 do not show. Where else would these tables be listed?
LikeLike
Hi Justin,
Sorry, I don’t use SSRS Report Builder, so I cannot say why they would not show up there. It may be that your user does not have access to them, or they have to be added in there explicitly to be available? If you look at the list of tables directly in SQL Server Management Studio, you will see them there.
-Victoria
LikeLike
Hi Victoria –
My company is using field security to limit access to the Delete button on the GL transaction entry window in GP2013 (SP2). Only a few users are exempt from it. Some of the users that are not exempt need to use Integration Manager to import GL transactions into the system. When they try to import they receive the following error,
Opening source query…
Establishing source record count…
Beginning integration…
DOC 1 ERROR: Macro execution error, Dynamics.DEXTERITY_IM_MACRO_INVISIBLE_ITEM (error DEXTERITY_IM_MACRO_INVISIBLE_ITEM): ClickHit field ‘Delete Button’
Integration Failed
Integration Results
1 documents were read from the source query.
1 documents were attempted:
0 integrated without warnings.
0 integrated with warnings.
1 failed to integrate.
We have tried changing the type of field security from Hide Field to Disable Field to Password with no luck. My understanding is that it will not work since we have “modified” the window form and integration manager is running a macro. They need to run these integrations on a pretty normal basis. Do you know of a workaround? Is there any way we can keep them from having access to delete GL transactions but allow them to import them? Any help would be terrific. Thanks!!!
LikeLike
Hi CRuiz,
One idea might be to upgrade to GP 2013 R2 (or to GP 2015) – where you can paste GL entries fro Excel directly into Dynamics GP.
Another idea would be to go back to the unmodified window and disallow deleting GL entries for all users. Instead of deleting users can void. If they really need to delete, they would need to go to someone who has access to the General Ledger setup window and can change the setting for them temporarily.
-Victoria
LikeLike
Dear Victoria,
This is a great website. Thank YOU verymuch
I have an issue, serious for me. As per the finance guy The inventory cost on hand from module and inventory cost from gl doesnt match.
Can u Please tell me how we get inventory cost on hand from GL ?
(Because i think the finance team is making some mistake by taking the wrong report. because the cost difference is 35 million.)
PLEASE HELP…
LikeLike
Algam,
Unfortunately, there is no one answer to your question. There could be a combination of many factors contributing to the Inventory subledger being off from the General Ledger. I would recommend either getting together with your GP Partner to look through your Dynamics GP transactions and see if they can help you find the issues or purchasing a tool like The Closer to help you find all transactions that do not match between the GL and Inventory.
-Victoria
LikeLike
I’m using the “SQL view for easier general ledger transaction searches” you created, and it has been incredible helpful in reviewing some data as I don’t actually have GP, merely GP data loaded onto a SQL Server.
It seems like I may be missing some extended transaction data. The descriptive fields REFRENCE and DSCRIPTN are short, and I wonder if there are additional notes fields. Does the originating document (ORDOCNUM) contain more? I see two series, IAJ and DAJ, appearing most often in the transactions I’m interested in. Can you point me towards tables that have more detail for these transactions?
Thanks,
Abe
LikeLike
Hi Abe,
REFRENCE and DSCRIPTN are both a max of 30 characters and there is nothing more on the transaction itself.
IAJ and DAJ should both be Bank Reconciliation transactions – please take a look at my page listing commonly used Bank Rec tables for more detail.
-Victoria
LikeLike
Hi Victoria,
We would like to reconcile the subledgers of the GL by month and account back to the GL. The approach we are taking is GL20000 = PM20000 + PM30200 + RM20101 + RM30101 + the General Jouranal entreis from GL20000. We are close on most of the accounts, but there are some which are off and we are not quite sure where to look. Is this something you have tried before, and if so do you have a query we can look at?
Thank you
Tracey
LikeLike
Hi Tracey,
I don’t think that approach will work. How about the Reconcile to GL routine, have you tried that?
-Victoria
LikeLike
Victoria,
I ran into a situation where we closed the year ex: 2013, but then after the year was closed, created a year 2012 and entered transactions. Found this when trying to close 2014 because it is trying to close 2012, but can not because 2013 is closed. Any suggestions as to how to fix this. Not opposed to deleting the data in 2012 then entering it as adjustments in 2013.
LikeLike
Cecilee,
I would recommend re-opening the 2013 year. Depending on the version of GP you are on, you can either do this yourself or you may need Microsoft to do this for you. Your GP Partner should be able to help you with this.
-Victoria
LikeLike
Hi Victoria,
I’m new to GP and have minimum SQL experience, trying to get my head around the reporting but it is confusing and not very user friendly as other financial solutions. I’m trying to obtain all my expenses associated to an specific cost center. What tables should I be looking at? or do you have query that can assist for this inquiry?
Thanks!
K
LikeLike
Hi Kevin,
Unfortunately, there is no one answer to your question as ‘cost center’ is not a set concept in GP, it’s something you determine/set up. Also, ‘all expenses’ is something that could be calculated quite differently, depending how they are entered. If this is a typical question you have come up, I would recommend getting together with your GP Partner for a little training on the options for pulling up reports like this based on your specific GP setup and usage.
Sorry not to have a straightforward answer. One of the things that’s both good and bad about Dynamics GP is that you can set it up to track some pretty complicated scenarios. It’s good, in that you can do it. It’s bad in that it’s sometimes not so easy to get what seems like simple stuff when you have a complicated setup.
-Victoria
LikeLike
Kevin,
It depends on the set-up. We have four segments, with each segment meaning different pieces, from entity wide to just the individual accounts. Our third segment operates as a Department, which enables us to filter transactions by “cost center”.
SRicks
LikeLike
Hi Victoria,
Why does the sample company (Fabrikam) configure the “typical balance” of “accumulated depreciation” accounts to be debit accounts? Accumulated depreciation is a contra-asset account. Assets are debit accounts. Contra-assets are credit accounts. Is GP using a non-standard definition of “debit account” and “credit account” here?
Thanks,
Chris
LikeLike
Chris,
Over the years I have found many glitches and issues in the sample company, so I try to be very careful about relying on existing data in there to be “correct”. This is probably one of the smaller glitches, since the typical balance does not really drive any functionality except what defaults for accounts when entering GL transactions. The nice thing about this particular issue, is that you can easily change this yourself in the account setup. 🙂
-Victoria
LikeLike
Your site is a great help Victoria.
I apologize if this question has been answered before but I didn’t find it while I searched through the site.
I am trying to add the Distribution Reference to the GL Batch edit list but cannot find it in the work table. Is this kept in another table?
LikeLike
Hi Robert,
The distribution reference is in the GL10001 table for unposted transactions. The field is called DSCRIPTN.
-Victoria
LikeLike
Is there a table that shows the user ID of the person who created a G/L account? The GL00100 table showed me the date and time, but it did not show a user ID.
LikeLike
Tom,
GP does not track this data out of the box.
-Victoria
LikeLike
Thanks, Victoria! I really appreciate all the useful information you provide here.
LikeLike
Hi Victoria,
We have a report that shows all GL Accounts and Balances. What we would like to do is once we click on the account number , be able to see all the transactions that make up that Balance.
Is there a way to look at all the detailed transactions that make up a GL balance?
LikeLike
Hi Larry,
Yes, you can use code that does something similar to my SQL view for all posted GL transactions in Dynamics GP.
-Victoria
LikeLike
I know it’s a one time job, but you may want to check out SmartConnect going forward if you will have more data imports. It is a lot faster than Integration Manager.
LikeLike
I need to import 300 thousand ( 3 LAC ) records in COA in GP 2013. Is it correct that I import it through SQL Server in a table instead of using Integration Manager, because IM is taking a long time to import the huge data. Please identify the table in which I can Import this.
LikeLike
Muhammed,
I do not recommend importing directly into SQL unless you (a) know the table structure very well and (b) have done a lot of testing with your import to make sure that there will be no issues. Even though IM might take a long time to do this import, it is best to use an existing tool.
Since this is a one time import, once you’ve done this it is over. If this is not a one time import, I would encourage you to reconsider – 300,000 account numbers is already A LOT. Anything more should be tested and considered carefully. While technically it will work, I would worry about performance and usability with this many accounts.
-Victoria
LikeLike
Thanks for reply.
it is running with 60 records in a minutes. It means it will take 13 hours for my first 50000 record integration.
Please help, I have very good exp. in SQL Server, I can do this. Is there any other table I need to update after GL00100?
Regards,
Arif
LikeLike
Arif,
I have not done this, so, as I said, you would definitely want to test this, but I believe you also need to populate GL00105. Once done, you will need to make sure to run Check Links on the Financial Series so that any other related records that are needed can be created.
-Victoria
LikeLike
Thanks for great suggestions.
Arif
LikeLike
You may also try to divide your import into sections. I’ve done some huge imports in the past (10 years of history), which took days by the time I got through all of the modules. I found that breaking the data into multiple files sped up the process in Integration Manager.
LikeLike
Thanks, That is exactly what I needed.
LikeLike
Hello Victoria,
I am trying to discover the Source of ‘POC’ SOURDOC in my GL transactions. Is there a place where all the SOURDOC values are enumerated.
LikeLike
Hi Warren,
I believe you can see this list by going to Microsoft Dynamics GP | Tools | Setup | Posting | Source Document.
-Victoria
LikeLike
Hello Victoria,
Thanks for a wealth of GP information on your site. I am trying to query information from journal entries that are NOT posted. Based on information I have researched, it seems the GL10110 should be the table with the detail, but there are no current month transactions. For example, we have posted sales invoices and I am trying to utilize the unposted GL transactions created by the sales invoice posting process in a report for preliminary monthly revenue numbers. Thanks in advance for your insight.
Thanks – Brian
LikeLike
Hi Brian,
GL10110 is a summary table that only includes posted transactions. Unposted GL transactions will be in tables GL10000 (header) and GL10001 (detail). You can use my All GL Transactions view and filter for ‘Work’ transactions to see these.
-Victoria
LikeLike
Hello Victoria,
Great site! In the GL2000 table what is the field name for the user creating a journal entry and the field name for the user approving the journal entry?
Thanks,
-Joe
LikeLike
Joe,
I believe batch approvals are going to be in the SY00500 table. Once a batch is posted, the approval information goes away and is not stored anywhere. If you need that, you will have to come up with some way of storing that information. Maybe something like a SQL trigger and a custom table that you can query for the data.
In the GL20000 there is a User Who Posted and Last User. Theoretically, the user who created the journal entry could be different than both of those. So again, if you need that, you may have to come up with a way of capturing and storing that data.
-Victoria
LikeLike
Victoria,
We are trying to take the GL account posting to produce a P/L enriched with item information from the SOP tables.
First we are extracting the GL transactions using this SQL (Data set 1):
SELECT t.ACCOUNTNUM
,t.ACTNUMBR_1
,t.ACTNUMBR_2
,t.ACTNUMBR_3
,t.AmountCur
,t.BUSINESSUNIT
,t.CURNCYID
,t.Department
,t.ORTRXSRC
,t.TRXDATE
FROM
(
SELECT CAST(LTRIM(RTRIM(l.ACTNUMBR_1)) AS NVARCHAR(7)) AS ACTNUMBR_1
,CAST(LTRIM(RTRIM(l.ACTNUMBR_2)) AS NVARCHAR(7)) AS ACTNUMBR_2
,CAST(LTRIM(RTRIM(l.ACTNUMBR_3)) AS NVARCHAR(7)) AS ACTNUMBR_3
,g.trxdate
,CAST(g.ORTRXSRC AS NVARCHAR(25)) AS ORTRXSRC
,SUM(g.DEBITAMT – g.CRDTAMNT) AS AmountCur
,CAST(g.CURNCYID AS NVARCHAR(5)) AS CURNCYID
,CAST(LTRIM(RTRIM(l.ACTNUMBR_1)) AS NVARCHAR(10)) AS BUSINESSUNIT
,LTRIM(RTRIM(l.ACTNUMBR_3)) AS Department
,CAST(LTRIM(RTRIM(l.ACTNUMBR_2)) AS NVARCHAR(10)) AS ACCOUNTNUM
FROM GL20000 g
INNER JOIN GL00100 l
ON g.ACTINDX = l.ACTINDX
WHERE g.TRXDATE >= ‘2011-01-01 00:00:00.000’
GROUP BY l.ACTNUMBR_1
,l.ACTNUMBR_2
,l.ACTNUMBR_3
,g.trxdate
,g.ORTRXSRC
,g.CURNCYID
)t
Secondly then we are extracting the SOP tables using this Query (Data set 2):
select CAST(SOP_LINES.ITEMNMBR AS NVARCHAR(50)) AS ITEMNMBR
,CAST(LTRIM(RTRIM(COA.ACTNUMBR_1)) AS NVARCHAR(7)) AS ACTNUMBR_1
,CAST(LTRIM(RTRIM(COA.ACTNUMBR_2)) AS NVARCHAR(7)) AS ACTNUMBR_2
,CAST(LTRIM(RTRIM(COA.ACTNUMBR_3)) AS NVARCHAR(7)) AS ACTNUMBR_3
,SOP_LINES.QUANTITY
,CAST(SOP_HDR.CUSTNMBR AS NVARCHAR(25)) AS CUSTNMBR
,CAST(SOP_HDR.TRXSORCE AS NVARCHAR(25)) AS TRXSORCE
,SUM(SOP_LINES.UNITCOST * SOP_LINES.QUANTITY) AS CostAmoutPosted
,CASE
WHEN SOP_HDR.SOPTYPE = ‘4’ THEN (CAST(SUM(SOP_LINES.UNITPRCE * SOP_LINES.QUANTITY) AS NUMERIC(38,9)) *-1)
ELSE CAST(SUM(SOP_LINES.UNITPRCE * SOP_LINES.QUANTITY) AS NUMERIC(38,9)) END AS LineAmount –Returns has to have the sign changed
,CAST(SOP_HDR.CURNCYID AS NVARCHAR(5)) AS CURNCYID
,SOP_HDR.CCODE
,SOP_HDR.GLPOSTDT
FROM sop30300 SOP_LINES
INNER JOIN SOP30200 SOP_HDR
ON SOP_LINES.SOPNUMBE = SOP_HDR.SOPNUMBE
AND SOP_LINES.SOPTYPE = SOP_HDR.SOPTYPE
INNER JOIN GL00100 COA
ON SOP_LINES.SLSINDX = COA.ACTINDX
WHERE 1=1
AND SOP_LINES.SOPTYPE IN (3,4) — Only Sales Orders and returns
AND SOP_hdr.PSTGSTUS = ‘2’ –Only posted items
AND sop_hdr.VOIDSTTS = ‘0’ –Exclude voided transactions
GROUP BY SOP_LINES.ITEMNMBR
,coa.ACTNUMBR_1
,COA.ACTNUMBR_2
,COA.ACTNUMBR_3
,SOP_HDR.SOPTYPE
,SOP_LINES.QUANTITY
,SOP_HDR.CUSTNMBR
,SOP_HDR.TRXSORCE
,SOP_HDR.CURNCYID
,SOP_HDR.CCODE
,SOP_HDR.GLPOSTDT
Thirdly we are outer joining the data set 1 = data set 2 by this key:
TRXDATE = GLPOSTDT
ACTNUMBR_1 = ACTNUMBR_1
ACTNUMBR_2 = ACTNUMBR_2
ACTNUMBR_3 = ACTNUMBR_3
ORTRXSRC = TRXSORCE
In case we are able to join the two data sets we take the “Lineamount” from data set 2 and use that number to calculate the P/L – however it doesn’t add up to if we produce a trial balance based on the GL accounts postings only.
We were hoping to drill from a GL account to the actual invoice line.
Is it at all possible to link the GL postings to the SOP tables and in that case what are we doing wrong.
I hope you can put some light on our little challenge.
Kind Regards
Carsten Klausman
Sr. Businesss Intelligence Specialist
Pandora Jewellry
LikeLike
Hi Carsten,
There are many reasons why this logic may not work, the top few I can think of:
So, in summary, depending on exactly how you are entering transactions into GP it may not be possible to link the GL directly to the individual line items like you’re attempting. What I would probably do instead is take several passes through the data, first get the GL detail, then ‘expand’ each GL detail that came from SOP into what line items and accounts/amounts that contains. You will need some error checking here to make sure that the totals match what’s in the GL and if they do not, at least show a message or come up with some way to show that on your report.
I hope that helps.
-Victoria
LikeLike
HI Victoria,
Which field in GL20000 table is the date the user entered the data into the system? TRXDATE, DEX_ROW_TS or ORPSTDDT]
LikeLike
Hi Kimberly,
I am not sure that you can always definitively get the date something was entered. ORPSTDDT stores the date the originating transaction was posted – not entered. DEX_ROW_TS stored the date and time the latest change was made to the transaction. TRXDATE is the General Ledger Posting Date.
-Victoria
LikeLike
Hi Victoria,
Sorry to reply to such an old post but I’ve been looking at using the DEX_ROW_TS field to find out when a JE was posted, thinking that once a record reached the GL20000 table, it won’t be subsequently changed. Thus, DEX_ROW_TS should reflect the SQL date/time the record was inserted. I’m finding, however, this not necessarily the case and the time stored can be later than the time the record was posted.
Do you know any mechanisms by which GP would update a posted GL20000 record? I’ve scripted all the triggers in the company database and don’t see any affecting the GL20000 table (except for the GL20000U trigger on the GL20000 table.
Thanks for any insight you can provide.
-Tim
LikeLike
Victoria,
I am trying to run
select * from GL00100
where ACTINDX not in
(select distinct ACTINDX from GL10001
union
select distinct ACTINDX from GL20000
union
select distinct ACTINDX from GL30000)
to find the accounts not used on any GL transactions, but it returns
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘GL00100’
Please help! Thank you!
LikeLike
LK,
Sounds like you may be running your query against the wrong database – this will only work when you run it against the GP company database. 🙂
-Victoria
LikeLike
Thank you Victoria. Can you please clarify what you mean by “GP company database”? I am using SSMS 2008 to run the query in the DYNAMICS database on the server where Dynamics GP was installed.
Thank you!
LikeLike
LK,
Each company you have set up in Dynamics GP is a separate SQL Server database. The DYNAMICS database (also called the ‘system database’) holds overall/shared settings, including users, security and multicurrency. All the other data, including accounts and transactions, will be in the individual database for each company. You can see a list of your companies in the SY01500 table in the DYNAMICS database – the INTERID column in that table will hold the SQL Server database name for each company.
-Victoria
LikeLike
Thank you so much Victoria! I got it figured out. You are the best!
LikeLike
hello mam
i want to join table [GL30000] ,[GL20000],[GL00100],can u please tell the relationship between these table
i am trying to filter through trxdate from GL30000 table
LikeLike
Rakesh,
Since the GL20000 and GL30000 tables contain similar data, you would typically want to UNION, not JOIN them. Either one of them can be joined to GL00100 by the ACTINDX column. You can take a look at an example of both of these in my code for All Posted GL Transactions.
-Victoria
LikeLike
Thanks for the reply Victoria.
Also If I want to link PM table (open/posted transactions) to GL, what are the fields I should join on? I eventually want to pull out the related account number to the transactions.
Thanks again.
LikeLike
Abhishek,
You should be able to link from GL20000 to the AP tables on GL20000.ORCTRNUM = voucher number (VCHRNMBR) and GL20000.ORTRXTYP = AP doc type (DOCTYPE).
-Victoria
LikeLike
Hi Victoria,
Could you please tell me which column in GL table refers to Cost Center.
LikeLike
Hi Abhishek,
‘Cost Center’ is not a standard concept in Dynamics GP, so there is no way to answer this generically. It would depend on your specific setup of Dynamics GP.
-Victoria
LikeLike
I’ve found this info very helpful Victoria, so thanks for making it available.
I’m extracting data from GP and found that I got different results if I inner joined the transactions to GL00100 vs GL00105 on the ACTINDX field. I expected the two account master tables to be equivalent in terms of account index values, but apparently they weren’t. Is it common that these two tables get out of sync with each other? Is one of the account master tables more “reliable” than the other?
Thx
Darren
LikeLike
Hi Darren,
That does not sound right. These 2 tables should have the same number of accounts, with the same account indexes. Can you run the following queries against your data and see what they come back with?
-Victoria
LikeLike
Hi Victoria, I have a question about joining GL and PM tables. I’ve seen your recommendation that suggests joining DocType to ORTrxTyp and VChrNmbr to ORCtrNum, but in my instance, that join doesn’t work. What I’m looking for is VendorID and POrdNmbr for a GL transaction. I’ve found the PO # in PM30200 and the GL Trx# in GL20000. How do I equate them? Thanks for your help! This is a great site!
LikeLike
Hi Bill,
Are you saying you want to show the PO number for the GL transactions coming from payables transactions? If so, there are two modules that these could be coming from – PM and POP – and you would probably want to code for these separately (unless you’re only using one or the other). But if really depends on what you’re looking to report on. It may help if you give me a little bit more detail on what you’re looking to do… 🙂
-Victoria
LikeLike
Yes. Only for payables. Sorry.
I didn’t consider the POP tables….I will see what I find there.
This is for a report that shows detailed account transactions by fiscal period. Essentially, I need to give the vendor and po # where possible.
Bill
LikeLike
Bill,
If you’re only looking at PM tables, you can do something like the following:
-Victoria
LikeLike
Thanks for the query. It does work. But, any idea why most of the results of POrdNmbr are either null or blank? Less than 1 percent have a valid PO#.
LikeLike
Hi Bill,
The query does not limit itself to only payables transactions, I just gave you something to get you started. Also, nothing except an invoice will have a PO number in payables, and then only a subset of them is typical to see PO numbers for in most companies. If you’re finding a lot of PM invoices do not have PO numbers and you are expecting them to, then you will want to talk to your accounting department about how these are being entered. Hope that helps.
-Victoria
LikeLike
Yes. It does. I will speak with them.
Thanks again for the help.
LikeLike
How do we relate our GL2000 table with the chart of accounts and their categories?
We have 13,757 posted transactions in GL2000 and when we try to relate that table to the chart of accounts GL00100 and GL40200 so that we can group by our categories , we end up with 25,000+ records.
Victoria, how can we create a SQL query that would Show us all the GL2000 accounts but also give us the flexibility to group them by our categories in the GL40200 table?
LikeLike
Karry,
Account Categories are in GL00102. Segment names are in GL40200. Below is a script to show all the entries in GL20000 and their category names:
-Victoria
LikeLike
Hi Victoria,
Im a newbie to this and find your tables very helpful. Im trying to find current balance and Beginning balance for GL Accounts (GLPGL). I couldnt find any columns which directly lists the, Can you please tell me where to find or how to calculate them.
Thanks,
Chev
LikeLike
Chev,
You can’t find this because GP does not store current or beginning balances in tables. You would need to calculate them.
-Victoria
LikeLike
Thankou Victoria..
LikeLike
Could you please tell me if there is any straight forward way to calculate those balances??
LikeLike
Chev,
This gets more complicated if you have multiple open years, but you can try something like this:
-Victoria
LikeLike
Thankyou..It was very helpful
LikeLike
How about GL10110 (open year) and GL10111 (closed years)?
These tables have balances by year and period.
LikeLike
Deanne,
Are you referring to the PERDBLNC column? If so, those are actually net changes per period, not beginning or ending balances.
-Victoria
LikeLike
Victoria,
I’ve looked throught these threads and cannot find the answer I’m looking for. I’m hoping you can help. I’ve been asked to write a report that shows all GL Entries posted after 5:00 PM on the close date for the closing period.
I looked at GL20000.Dex_Row_TS thinking that it would have only been updated when a record was entered, but found that it is apparently updated at other times. I cannot use the GL20000.TrxDate because it is the “posted” date and not the date entered (much less time).
Do you know of a reliable method in the Great Plains SQL database for pulling GL records by entered date/time?
LikeLike
Cat,
I am not aware of anything in GP that will track the correct time – the Dex_Row_TS is as close as you will get, but it is not always accurate, as you have seen. You can get the date the transaction was posted in ORPSTDDT – that may be the best you can do. If this is important for the future, you may want to add a trigger to track this in a separate table.
-Victoria
LikeLike
Thank you so much for your speedy response.
I will see if they will approve the trigger and custom table, then write the report using that.
I appreciate you,
Cat
LikeLike
once posted is the JE reversing date tracked aywhere else I am working on a user request to identify entries as reversing with the reversing date
using series=2 and ortrxtyp=1 i can id as reversing but don’t see the rev date in the gl20000 or gl30000
LikeLike
Mark,
From looking through our data, it looks like the reversing transaction will have the same journal entry number, but the RCTRXSEQ will be 1 (whereas for the original entry it will be 0). Also the reversing transactions will have a TRXSORCE starting with ‘GLREV’.
Hope that helps.
-Victoria
LikeLike
Hi Victoria,
I apologize if the answer is in here already. When I ran year end close and reconcile for a couple databases (we have 47 databases with our instance of GP 10.0), the periodid 0 in their GL10110 and GL10111 tables are missing. All the rest of the databases are fine, it is just for a couple that were all setup at the same time (do I have a trigger accidentally set to clear this – if so, where would it be?). I have deleted both tables and rebuilt them but the starting numbers are missing. There are no missing entries in GL20000 or GL30000, all the BBF entries are there. These BBF are what populates periodid 0. Is there a way to fix this other than creating them manually?
LikeLike
Mark,
I have not seen this happen before. Does this mean that you have no beginning balances for the balance sheets accounts in these companies? Are you able to restore and re-run the year end close? If not, you may want to talk to GP support for help with this.
-Victoria
LikeLike
Hello,
Our finance department has many reoccurring General Journal entries. However, it does not appear that great plains creates entries in the GL 10000 table like it does if you just create a standard General Journal entry. How does Great Plains handle the General Journal Entries that are reoccurring? I do see a handful of entries just sitting in the GL 10000 table as if they are a unposted batch. Do Reoccurring transactions always remain as an open Batch and can only be posted?
Why I ask is that it states that the GL 10000 account is the work file for all General Journal transactions. However, we never see any transactions getting created in this work file if they arte set as reoccurring transactions.
What is the workflow through the tables for General Journal entries?
LikeLike
Hi Larry,
Unlike posted GL entries, unposted GL entries are stored in 2 tables: GL10000 has the header information and GL10001 has the detail/line items. Recurring entries work very much like other GL entries and any saved recurring GL entry will be in both of these tables. We have about 5 of these in our database, and I can see them in there alongside the non-recurring entries. In the GL10000 table the RCRNGTRX field will be 1 for any transactions that are recurring.
Another way to look for these would be to check the SY00500 table – this will contain all unposted batches. Since unposted recurring transactions must be in a batch, you should see them in this table with a BACHFREQ of something other than 1 (single use) and a SERIES of 2 (financial).
If you’re not seeing anything like that, I would suspect that what your users are calling ‘reocurring’ may not actually be using the recurring entry functionality in GP.
-Victoria
LikeLike
Hi Victoria,
I have been going to your site a fair bit recently as I am starting to do more reporting work with GP. I am still learning the tables and their relationships so hoping you can help.
I want to develop a query where I can start with g/l postings for a specific account number and then join their a/p voucher details (if they exist) and then the notes from the SY03900 table.
I am having difficulty finding the primary key(s) to use on GL20000 and PM32000. I tried voucher number, but got some odd results. I also tried the original source, but that is a batch reference.
What can I use to grab the GL20000 records for an account and pull the related A/P details?
thx,
Doug
LikeLike
Hi Doug,
You should be able to link from GL20000 to the AP tables on GL20000.ORCTRNUM = voucher number and GL20000.ORTRXTYP = AP doc type. Hope that helps.
-Victoria
LikeLike
Hi Victoria, I need your help. Is there a SQL view/script that will give me all GL accounts with no historical or current activity. I’d like to take the results and delete those unused accounts; is there also a script to do a mass delete within SQL that you would recommend?
LikeLike
Hi Ana,
You can use the following code to find the accounts not used on any GL transactions:
However, this will not identify any accounts used on setup windows for items, customers, vendors, etc. Or any accounts on unposted transactions in the subledgers. To do a mass delete from SQL, you would really want to make sure those accounts are not used ANYWHERE else. Otherwise users will get errors when the system tries to use those accounts and they are not longer valid.
LikeLike
Hi Victoria,
I just stumbled across your blog and have found more info here about GP than all other places combined, I was thinking there wasn’t much out there!
Anyways, I am a bit new go GP and am groping around. I am trying to link items purchased in SOP30300 to the GL distributions (in GL2000?). I see items and their components in IV00104, and the GL ACTINDX column in IV00101, but that looks like the setup rules for an item. I am more interested in getting from the sale to the GLs the money ended up in.
Thanks in advance for any help! I hope I am being clear, please let me know if clarification is needed to make sense of this!
LikeLike
Hi Warren,
The account distributions that go to the General Ledger when an SOP transaction is posted are not stored on a line item basis and SOP30300 is the line item detail…so it may not be possible to always definitively get this at the line item level. If you’re ok with looking at the GL distributions at the SOP transaction level, they are stored in the SOP10102 table. In case you need the distribution types, are listed on my SOP tables page. Hope that helps. If you have more specific questions, let me know.
-Victoria
LikeLike
Hi Victoria
What a great site, thank you so much for providing this. I am new to GP and this has really helped to guide me through the database :).
I have a quick question. Is it possible to link the GL transactions to the POP tables to return the Item and PO number(s) which correspond to the GL Transaction? I have tried various combinations, but as the invoices created are one Receipt to many PO’s I am struggling to get a correct match.
Many thanks.
LikeLike
Hi Jubline,
Thank you for your kind words.
It may be possible to link the GL transactions on the POP tables and the item(s)/PO number(s), however, you might not be able to do it by amount. And you will have to somehow manage multiple results…so depending on exactly what you want to show this may be possible, but certainly not easy. Part of the issue is what you mention – one receipt could represent multiple PO’s. Another part would be that items are not necessarily linked to GL distributions on the POP transactions because the GL distributions are stored in summary, not by line item or corresponding PO number.
If you can narrow your scope to only include certain types of GL (or POP) transactions that may be easier. What specifically you are trying to accomplish with your reporting?
-Victoria
LikeLike
Hi Victoria
Many thanks for such a speedy response – it’s so kind of you.
I am trying to link 2 types of GL transactions to their corresponding PO number and Item numbers so that we can follow PPV (Purchase price Variance) through the system.
The issue seems to be that whilst most of the goods received may have a one-to-one relationship with the PRGN number, at the point that the GL Invoice is raised and a Purchase Invoice put onto the system, the GL Line PRGN has many PO’s and items.
The only natural key between GL and POP seems to be the PRGN number. I am currently linking the GLTransactionView to GL00105 on ACTINDX and then to POP30310:
GLTransaction_View.ORDOCNUM = POP30310.POPRCTNM
then to POP 10500:
GLTransaction_View.ORDOCNUM = POP10500.POPRCTNM
This is quite possibly incorrect… I got my idea from picking the POP_PPV view apart and looking at the tables used there and then trying to link them into the GL view.
Any light that you could shed on this would be really gratefully received. Thanks so much again for all your help 🙂
Justine
ps – I think I’m trying to do something similar to Mark (below):
Victoria, Regarding this comment made earlier and your response: Typically (and definitely in this case) GP posts in aggregate – if you have 5 lines on a receipt that all go to the same account, you will not see 5 separate GL distributions for the same account in the same transaction, you will see one line combining them. So there is nothing to link a GL entry or part of it to a particular line number on a receipt.
Do you know if there is a way t post the detail of the receipt line items to the GL without them posting in aggregate. If not, what would you suggest to get the detail of these line items along with purchasing to balance to G/L.
LikeLike
Jubline,
First thing that jumps out at me is that since both POP30310 and POP10500 hold multiple transaction types, at the very least you would want to add a link on the transaction type.
I am not familiar with ‘POP_PPV view’ and ‘the PRGN number’, what are those?
One suggestion that is more of a setup thing, but that might make tracking PPV easier is to set up GP so that it posts the PPV to a separate account (or group of accounts). That way you only have to check the accounts where PPV goes, not all transactions/accounts.
But realistically, even if this type of reporting can be done (and I am still not convinced that it can), I am not sure I can ‘generically’ help with this, it may require someone actually looking at your data to help you with it.
-Victoria
LikeLike
Hi Victoria
Apologies for not sending you all the correct information. PRGN numbers are the receipt numbers:
POPRCTNM on the POP tables and then ORDOCNUM (and possibly ORCTRNUM on the GL side).
The PPV view is possibly something written in-house? It just returns all of the PPV transactions.
I shall try adding a link on the Transaction Type between POP30310 and POP10500 and see if that helps me.
Thanks so much for all of the suggestions. I will do as you suggest and see if we can get someone to take a look at the data here and make some suggestions.
Thanks again, I really appreciate your assistance. 🙂
Jubline
LikeLike
Have you looked at joining the INVINDX field in POP10100 to the ACTINDX in GL00100? This is how I can get GL transactions down to the PO info.
LikeLike
Hi Victoria,
Let me know why we get this error : Input variable contains a duplicate journal entry number in General Leder in GL10000 GL20000 .
This is my one of the client facing this error. Can you tell me how can i solve this…
LikeLike
Kavitha,
That does not sound like a typical GP error to me, at least not one that I have seen before. If you have a customization or 3rd party product involved, I would ask them about this.
-Victoria
LikeLike
victoria,
It does not involved any 3rd Party product.But for customization …first i am not clear with this. can you explain about customization.
I have used same DLL file to import excel file into my GP .
Let me know if you need any farther info…. for giving me help
LikeLike
Kavitha,
As far as I know importing into GP using a “DLL file” is not something that is supported or out-of-the-box. So I would think that is a custom process. The error message sounds like you are trying to import a journal entry number that already exists. If that is not the case, I would probably recommend starting with the developer of this DLL for getting help.
-Victoria
LikeLike
Yes your correct victoria, I have used same dll file in two diff systems…i have imported successfully.
Myself only develop this DLL file. I have used this SP taGetNextJournalEntry to finding next journal number…
i am getting correct next journal entry number. But i am bit confusing why in my client system not increment correctly..
LikeLike
Kavitha,
I would recommend talking to Microsoft Support for help on this.
-Victoria
LikeLike
Hi Victoria,
I hope all is well. We have an issue. Our client was in the process of closing the year for his company. He ran through the year end closing procedures. After closing the year I noticed that there were a bunch of transactions that were not posted. I opened the fiscal period and the user posted the remaining batches. After doing so I noticed in our report that the opening balance for an income account was not zero. The closing balance was carried forward to the opening balance of the current year. Where can I confirm the opening balance for an account in Great Plains? Is there a way to fix this? Thanks.
LikeLike
Hi Kevin,
Sounds like that account may have been set up incorrectly. Here is a link to a KB article on how to fix this: https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;en-us;864913
-Victoria
LikeLike
Hi Victoria,
Not sure if this question is related to this place. Im a DBA and just trying to help my colleagues. I hope the below can explain well what we are looking for:
The situation is that we show entries to the Sales Tax liability account that show what our system thinks we should be paying to the State. But, when we prepare the tax return we discover differences; always. This is either due to us not catching something on the tax return or our GP system is coding some kind of sale/sales incorrectly as a taxable sale.
Question:
Is there any kind of report that can be run that shows which accounts in our system are set up as taxable?
May be if it is possible we go through the transactions of the accounts that are charged with taxes?
Thanks,
Sameer
LikeLike
Hi Sameer,
I do not believe there is just one report you can run that would help you determine what is causing the discrepancies you’re seeing. I would recommend working with your GP partner to go through your GP setup as well as the reports/windows you are looking at currently that show the discrepancies to determine what is causing them. While it could be something simple, it could also be a lot of other things and it is really impossible to say without looking at your system.
-Victoria
LikeLike
Thank you for quick reply,
Lets assume if you have system in front, what things you’ll check.
LikeLike
Sameer,
I am not sure what ‘Lets assume if you have system in front’ means, but again, there is no way to answer this without seeing the reports you are looking at and going over your actual data. For all I know you have General Ledger entries updating the sales tax liability account directly. There is no system setting to check for that – this would require understanding where the data for those entries is coming from and why it is being entered directly into the General Ledger.
-Victoria
LikeLike
Running GP2010 SP1 with Binary Stream Multi Entity Management.
LikeLike
Jeff,
I don’t think this was something not working in SP 1 of GP 2010, but you’d have to look through the list of fixes for SP 2 to determine for sure. It’s also possible that the MEM product is replacing that window (as it does with many windows) and their version of this window does not have the same functionality as the GP version. If MEM is replacing this window, you could possibly test this by temporarily disabling the modified window or the MEM product…or talking to Binary Stream support to check with them.
-Victoria
LikeLike
Is there an action that keeps the sort order? The right side of the lookup screen keeps returning to ‘Type’ as in posting or unit etc. rather than staying Main Segment.
LikeLike
Jeff,
Not that I am aware of. Mine is just remembering the last option I chose automatically. What build of Dynamics GP are you on?
-Victoria
LikeLike
Victoria,
Is there a way to change the Account Lookups sort to run by a specifc segment rather than the out of the box sort which is ‘Type’ AFter you change to a segment, lets say main segment for example how do you lock that choice in so that it comes up with the sort every time you open the accounts lookup screen?
LikeLike
Jeff,
GP should automatically remember these. I just tested this in my GP 2010 SP 2 and that is the case. This is going to be per user (possibly per company, as well).
-Victoria
LikeLike
Hi Victoria,
First of all thank you for blogging extensively to keep all of the rest of us sane… 🙂
My question involves GL transactions. Here is the scenario: we took a client live in Dynamics 2010 September of this year. Shortly prior to golive we imported all of their GL transactions for 2012 and posted them. Within a few days we realized that the data was corrupt and included a bunch of batches that were commitments and budgets. (The budgets and commits had been posted to the GL as actual transactions)
The obvious solution is to back those batches out of the GL – there are only a few thousand of them – but I was wondering what would happen if we simply over-wrote GL20000 with the correct data for pre-September, and then just imported the transactions from Sept forward onward. If all of the current JE numbers were the same, would we maintain data integrity? We wouldn’t want to lose drill-down and audit trails.
This is probably a stupid question – but you never know!
Kind Regards,
Sean
LikeLike
Hi Sean,
I would strongly recommend NOT doing this directly in the tables. There are a lot of related tables and you’re going to spend a lot of time testing, checking and fixing all of them. My recommendation would be import correcting entries.
-Victoria
LikeLike
Hi Victoria,
Thanks, that is what I figured – but it is always best to get a second opinion…
Kind Regards,
Sean
LikeLike
I am looking for a table that actually has the general ledger account balances – not the period change – for each period. I am working with GL10110 and GL10111. Is there any other table that just gives balances or am I stuck having to calculate this info?
LikeLike
Deanne,
GP does not store the account balances anywhere. If you need them in SQL, the only way to get them is to calculate them.
-Victoria
LikeLike
Thanks Victoria
I will have to buy the book. I think it will defintely be worth it.
Regards
Nish
LikeLike
Hi Victoria,
I need some urgent help from you. We are in the process of moving a compnay over from pastel Account to Dynamics GP 2010. I need to import the COA together with three years of historical data. Please could you advise on the best process to follow in this regard.
Regards
Nish
LikeLike
Nish,
I would typically use Integration Manager for this. I outline the process in my book.
-Victoria
LikeLike
Hi Victoria,
Good Day! Could you please help me in fixing this error we are about to post a batch coming from Sales Transaction window in Financial Batches window but this error appear ” you can’t post tranasction in General Ledger while multicurrency for the Financial Series being valued”
Thanks,
Myrna
LikeLike
Hi Myrna,
Sounds like there is a revaluation process started, you cannot post batches until it is completed. If no one has the revaluation window open, it’s possible there is a stuck process in SQL. To clear it, make sure all your users are out of GP, then run the following in SQL:
-Victoria
LikeLike
Hi Victoria,
Thanks for your recommendation.
Best Regards,
Myrns
LikeLike
Victoria
Thank you so much – really appreciate your help. You’ve saved me hours of frustration. I’m sure I speak for everyone when I say we’re all so glad to have found you.
LikeLike
Hi Victoria
I’ve just discovered your blog & am really impressed & grateful. I’m wondering if you can help, I am in IT & have been asked to work with our Accoiunting Dept so am trying to get my head around GP. I am trying to pull together the following information:
Transaction Date
Business Unit
Department
Natural Account
Amount
Financial Year
Financial Period
Can you tell me how I can link the GL00100, GL00105, GL2000 & GL30000 in order to pull this information together? I can link the GL00100+GL00105 tables but don’t know how to link them to the GL20000+GL30000 tables.
KR
Amanda
LikeLike
Hi Amanda,
Some of these things are specific to your business (like Business Unit, Department, etc.), so it’s difficult to answer specifically. In general, you can link GL20000 or GL30000 to the GL00100 and GL00105 tables by the ACTINDX (account index). Each General Ledger account has an index that is used in most tables that you can use to simplify linking. There are some tables that have multiple account indexes with different columns names, but all the tables you listed will have just one – ACTINDX. Hope that helps.
-Victoria
LikeLike
GL Variable Account Maitenance SQL Script for SSRS edit list
Please provide input and thoughts:
In a SOX environment getting accountants access to Maintenance screens to view variable account maintenance is not straight forward so creating a SSRS edit list for variable account maintenace seems a possible options. Those of you with more under the hood SQL experience with GP2010 any thoughts on elements missing or an extra garnishment needed? Please reply:
/****** Variable Account Maintenance: Group Insurance ******/
/****** I was focusing on one variable account you might have others or want all. ******/
USE [TEMPDB]
if object_id(‘#GLVAMSTR’) is not null drop table #GLVAMSTR
if object_id(‘#GLUAMSTR’) is not null drop table #GLUAMSTR
USE [NREG]
SELECT DISTINCT
RTRIM(GLS.ACTNUMST) AS VAR_ACT,
GLV.ACTDESCR AS VAR_ACTDISC,
RTRIM(GS.ACTNUMST) AS DIST_ACCT,
GL.ACTDESCR AS DIST_ACDISC,
DIST_ACTIVE = CASE WHEN GL.ACTIVE = 1 THEN ‘Active’ WHEN GL.ACTIVE = 0 THEN ‘Inactive’ ELSE ” END,
VA.BDNINDX
INTO #GLVAMSTR
FROM GL00104 AS VA
INNER JOIN GL00100 AS GL ON VA.DSTINDX = GL.ACTINDX
INNER JOIN GL00105 AS GS ON GL.ACTINDX = GS.ACTINDX
LEFT OUTER JOIN GL00100 AS GLV ON VA.ACTINDX = GLV.ACTINDX AND GLV.ACTINDX= 17918 — I was focusing on one variable account you might have others or want all.
LEFT OUTER JOIN GL00105 AS GLS ON VA.ACTINDX = GLS.ACTINDX AND GLS.ACTINDX= 17918 — I was focusing on one variable account you might have others or want all.
WHERE GLS.ACTNUMST IS NOT NULL
SELECT
UA.ACTINDX AS UAGLINDX,
RTRIM(GS.ACTNUMST) AS UNIT_ACCT,
GL.ACTDESCR AS UNT_ACTDISC,
[YEAR] = CASE WHEN UA.YEAR1 IS NULL THEN 0000 ELSE UA.YEAR1 END,
PERIOD = CASE WHEN UA.PERIODID IS NULL THEN 0000 ELSE UA.PERIODID END,
AMOUNT = CASE WHEN UA.PERDBLNC IS NULL THEN 0.00 ELSE UA.PERDBLNC END,
UNT_ACTIVE = CASE WHEN GL.ACTIVE = 1 THEN ‘Active’ WHEN GL.ACTIVE = 0 THEN ‘Inactive’ ELSE ” END
INTO #GLUAMSTR
FROM GL10110 AS UA
INNER JOIN GL00100 AS GL ON UA.ACTINDX = GL.ACTINDX
INNER JOIN GL00105 AS GS ON GL.ACTINDX = GS.ACTINDX
WHERE UA.ACTINDX IN (Select BDNINDX FROM #GLVAMSTR)
SELECT
a.VAR_ACT, VAR_ACTDISC, DIST_ACCT, DIST_ACDISC, DIST_ACTIVE, UNIT_ACCT, UNT_ACTDISC, UNT_ACTIVE, [YEAR], PERIOD, AMOUNT
FROM #GLVAMSTR a
inner join #GLUAMSTR b ON a.BDNINDX = b.UAGLINDX
ORDER BY a.VAR_ACT, YEAR, DIST_ACCT, UNIT_ACCT, PERIOD
LikeLike
Hi Ron,
I think I have some code that is a bit more simplified for this…give me a little time to dig it up (and clean it up) and I will put up a new blog post with it.
-Victoria
LikeLike
Victoria –
Your blog is one of the first sites I come to when trying to figure out an issue with Dynamics GP. I have always found it EXTREMELY useful in my Dynamics GP travels.
I do have a question about the GL10001 table while researching an issue with MDA. How is the DTA_GL_Status column populated? I currently have a problem where MDA records are “disappearing” from the DTA10100 and DTA10200 tables. In the GL10001 table I noticed its been populated with a status of 0 or 32, but I don’t know what these mean. Do you happen to know?
Many Thanks,
Sean
LikeLike
Hi Sean,
Thank you very much for the kind words!
I cannot find any reference for the MDA (DTA) tables in the GP SDK, or anywhere else. I submitted a question to Microsoft asking if anything like that is available. In the meantime, I did find this about the field you are asking about: http://support.microsoft.com/kb/857570. Also found another comment on a partner forum saying that the DTA_GL_Status will be 32 if a transaction is coming from a subledger. In our data, I see most of the transactions with 0 coming from Intercompany transactions, Quick Journal transactions or transactions like the year end close – basically transactions that do not work with MDA.
In any case though, my gut feeling is that transactions “disappearing” from the DTA10100 and DTA10200 tables would not be caused by what is in that field. Although, just to make sure I understand, when you say “disappearing” – are they there at one point, then not there anymore later? Or do they never make it in there to begin with?
-Victoria
LikeLike
Thank you for the info! I also found the kb you noted. It didn’t really help answer what I’m trying to track down.
Here’s the steps of what’s happening.
1. Information from ADP is delivered
2. Accountant imports the data into the Financial >> General Journal window via Tools >> Integrate >> Import from ADP which he then links to a spreadsheet to import the data.
3. Because we’re using MDA (albeit probably not correctly) he executes a Macro (generated from VBA code in a spreadsheet) to input the MDA information.
Once he’s done this I can validate the transactions in the GL10000, GL100001, DTA10100 and DTA10200. When he deletes the batch, it deletes the transaction correctly from the GL10000 and GL10001 tables and the DTA10100/200 tables but it deletes extra records from the MDA tables. So before he executed this transaction, I validated 1061 records in the DTA10200 table, after he did the import, I had 1078 which looked correct. When he deleted the batch, I expected to have 1061, but instead had 941, so it deleted more than just the MDA records associated with the transaction.
Same thing happens when he posts. He can post a transaction with MDA items and it will post correctly, when he posts another transaction with MDA items, it will delete the previous MDA information. It’s the strangest thing I’ve ever seen!
I tried using the Support Debugging Tool to fire off an email in the event of a record deletion from those tables, but never get it to fire correctly. I ran a SQL trace this morning and can see it calling several sp’s relating to the process (gldeletebatch, dtavalidatestats, etc.) but trying to figure out “why” the extra records are being deleted is becoming quite daunting.
Any thoughts where I might start with this? The good thing is this is easily reproduced in our test environment which is helpful so it’s not a random occurrence.
LikeLike
Hi Sean,
Thanks for the additional detail. That definitely sounds like there is something going wrong and again, I would not expect the value in the DTA_GL_Status to be the answer. You have 2 ‘processes’ that are not quite out-of-the-box, I would start testing by first eliminating them, then adding them back one at a time.
For example, start by entering a sample transaction manually (doesn’t have to be the entire transaction you normally import, just a small portion), add the MDA information manually – do you still have the same results when deleting or posting? If not, then do an Import from ADP and then enter the MDA information manually – do you have the same results when deleting or posting?
If that still does not help track down the problem, I would recommend working either with your GP Partner if they know the GP tables well, or Microsoft GP Support. Exiting records being deleted is a pretty serious issue, and I would want to track down what is causing it as soon as possible.
-Victoria
LikeLike
Victoria, do you know where the Source Document APL comes from in GL20000? I know it has something to do with applying payments but I don’t know what steps or through which window these entries are created.
Thanks
LikeLike
Hi Peter,
APL stands for Apply Receivables Documents. While not recommended, it is possible to change the source codes, but you can check them for your company by going to Microsoft Dynamics GP | Tools | Setup | Posting | Source Documents.
-Victoria
LikeLike
PS. Typically APL postings are generated when you apply a receivables payment, return or credit to an invoice after posting using the Apply Sales Documents window and there is a GL change that needs to be recorded. For example, a discount or writeoff is taken or there is an exchange rate difference.
-Victoria
LikeLike
VIctoria,
Thanks for the website. It is so useful! I am using GP with Crystal Reports and do not see a GL11110 or GL11111 on my table list. Do you know why this is or how to fix it? Thanks in advance for your help.
Deeann
LikeLike
Hi Deeann,
Very glad to hear my blog is helpful! GL11110 and GL11111 are views, not tables. So if you are in Crystal and you’re only looking at tables, you will not see them listed. Quick way to finding these in the list when you are looking at the data connections in Crystal:
Hope that helps
-Victoria
LikeLike
Thanks Victoria! That worked perfectly!
LikeLike
Is there a GP 2010 table where we can see the Batch Number for financial transactions that have already been posted? I am doing a data query from GP10000 for unposted transactions, would like something similar for posted transactions. Any help would be appreicated!
LikeLike
Jeanette,
The batch number for posted GL transactions will be in the GL20000 (open) and GL30000 (historical) tables in field ORGNTSRC.
-Victoria
LikeLike
Thanks Victoria! They had told me to use the PERIOD BALANCE field but when it came up negative they said the balance was wrong. But as you said I think it is just the way they entered the information.
LikeLike
Victoria,
Please excuse my GP ignorance. Someone asked me to write a report giving the balance on a line of credit.
Wouldnt the credit amount field be equal to the total credit avaliable. And wouldnt the debit amount field be the amount used of the credit line?
LikeLike
Hi Jerome,
No problem at all – I was answering your question from an accounting (and GP) perspective. Accounting (and GP) does not know what you are actually putting in a particular General Ledger account. It could be cash, it could be sales, it could be an expense, or…a line of credit. No matter what, the balance is always Debits – Credits. That does not change.
What does change is how the accounts are used. For example, Cash accounts increase on the Debit side and decrease on the Credit side. So a Debit balance for a cash account means a positive cash balance. However, Sales accounts increase on the Credit side and decrease on the Debit side. So a negative balance for a sales account actually means positive sales. None of this is GP specific – this is strictly accounting.
Unfortunately, since I do not know exactly how your line of credit entries are booked, it’s really impossible to answer your question any more specifically. I can tell you what I think, or how I might enter these, but that would just be wasting your time – you need to get this knowledge from your accounting folks. So…if you are not too familiar with accounting and specifically how this account is being used in your GP, whoever has asked you to create this report may need to give you a little more help/guidance than simply asking for a balance of an account.
Hope that helps explain things just a little better.
-Victoria
LikeLike
In the GL11110 my period balance is -6000 but my Credit Amnt is 20000 and my Debit amnt is 14000. Is this correct? shoudnt my Period Balance = Credit amt – Debit amt ?
LikeLike
Hi Jerome,
Credits are negative and debits are positive. And if you’re subtracting the 2, it’s always Debit – Credit. So your period balance is 14,000 – 20,000 = -6,000.
-Victoria
LikeLike
Hai victoria, here is an issue: I ran the Check Links yesterday. Now, users(inlcluding powerusers) cannot see / access / view / created any GL accounts. However, after logging in as the sa, they are definitely all there. I run checklinks of system and finance…they show no error…do you know where the problem is…?
LikeLike
Fazil,
I have not seen Check Links do anything like this, and while nothing is impossible, I would consider that pretty unlikely. Is it possible that security settings and/or user permissions got changed?
-Victoria
LikeLike
Noo, its not changed. This is a newly created company.we also have another two companies which is working fine. The same users who can access GL in this two companies cannot access in the new company.
LikeLike
If you create a brand new user and make them a POWERUSER, can they see the accounts?
-Victoria
LikeLike
Victoria, Regarding this comment made earlier and your response: Typically (and definitely in this case) GP posts in aggregate – if you have 5 lines on a receipt that all go to the same account, you will not see 5 separate GL distributions for the same account in the same transaction, you will see one line combining them. So there is nothing to link a GL entry or part of it to a particular line number on a receipt.
Do you know if there is a way t post the detail of the receipt line items to the GL without them posting in aggregate. If not, what would you suggest to get the detail of these line items along with purchasing to balance to G/L.
LikeLike
Hi Mark,
I am not aware of any setting to change the posting behavior for POP receipts. The only options would be to post receipts of one item at a time. This will not break anything, you can still enter invoices in aggregate, but it would certainly take a lot longer to process receipts if you have a lot of items on receipts.
Looking at if from the reporting side, you could possibility use the POP accounts and distributions from POP30310 and POP30390, but you would need to do some testing to make sure this works for you data.
However, if you’re looking to do this because you’re having trouble reconciling the Inventory module or Payables module to GL, then I am not sure this is the way to go because POP only has a subset of what goes into Inventory and Payables. So you would really want to compare to Inventory and Payables tables, but even then doing this manually using tables is very complicated.
If this is a recurring issue, you might want to consider The Closer.
-Victoria
LikeLike
No, new users also cannt see the accounts… here is a similar issue i found from internet… http://msgroups.net/microsoft.public.greatplains/cannot-view-enter-any-gl-account/83605#replyForm…. but no solution in this.
LikeLike
You’re probably better off having someone take a look at this. I would recommend talking to your GP Partner or GP Support to get this straightened out.
-Victoria
LikeLike
I am pulling a transaction detail from the GL10000 and 100001 tabls but want to also see (in seperate fields) the segment names from the GL40200 table. How do I link them together and pull into my excel reports. I dont know what fileds to lik together between the the Trx tables/GL00100/gl00105/gl40200 tables to make that work. Any thoughts?
LikeLike
Patrick,
You can link from GL10001 to GL00100 on ACTINDX. Once you’ve done that, you an link from GL00100 to GL40200 based on the segment number and value, for example:
For the first segment:
For the second segment:
-Victoria
LikeLike
Hi Victoria,
I created a SmartList using Segment Description Master to get listing of all Segments. Is there a way to get the User ID (who created) and the Create Date
Thanks,
Khan
LikeLike
Hi Khan,
GP does not store information about who created a segment description or when, so unless you were doing your own tracking on this, I don’t believe this is possible.
-Victoria
LikeLike
As always – Thanks for the Info
– Khan
LikeLike
Victoria,
Thanks for previously answering my questions, very helpful. I have identified several DEBITS in the RM20101 & RM20201 tables that I am unable to identify its corresponding SOP Invoice record. The APTODCNM = ‘DBT00XXXXX’ and the ‘APFRDCNM = ‘CRT00XXXXX’. I am not understanding what exactly this debits are related to? Are they debiting credits previously attached? (I have confirmed in this particular system prefix DBT & CRT are infact debits & credits and confirmed using RMDTYPAL field). Any insight into this matter would greatly be appreciated.
– Dave
LikeLike
Dave,
There is no requirement that a debit or a credit memo has to ‘relate’ to an invoice. So, this is not a GP question so much as it is a question for your accounting department. Give them the specific debit and credit numbers and ask them. If they say…’oh, this goes with SOP invoice XXXXX’, ask them how they know this – is there something they have entered into GP that tells them this? If so, where is it?
-Victoria
LikeLike
Hi Victoria,
First of all, Love your Blog! Very informative! Am learning a lot here and able to adapt quickly to GP because of it.
I have a question re:GP2010, I noticed that there are the GL00100F1 to F4 tables. I wonder what these are for. I am familiar with the GL00100 tables but when I try to research what these “filters” tables are for, I only find their definition as “filters” and nothing else describing what they are for.
This is out of curiosity.
Thanks! and More Power!
LikeLike
Hi Peter,
Thanks for your kind words!
It’s a good question, as I have not seen this documented anywhere. I am not sure what those tables are for. I suspect they were added for some performance gains and are used by certain processes in GP as needed. I have not checked them often, but have never seen them have any data in any of the GP installs I have worked on. For more information, consider posting your question on the GP Community Forum to see if anyone can answer this.
-Victoria
LikeLike
Hi Victoria,
Thanks for the quick reply. Will try to research some more on this also.
Peter
LikeLike
Hello, just to let you know that these tables are used for the Account Security asignment. Each table represents one of the four levels available for the Organizational Chart used for Account Security in GP. The will hold each user’s account access. Best regards.
LikeLike
Thanks Raul!
-Victoria
LikeLike
Thanks Raul for the update.
– Peter
PS: Am looking for my log-in information since I changed laptops.
LikeLike
Hi Victoria… I’m one month new to Dynamics and stepped into an existing problem.
We are running Dynamics GP 10 with SP 3 + Hotfixes.
Historically the “Unapplied Amount Total” in the Inquiry -> Sales -> Receivables Summary screen has always matched the “Period Balance Total” in the Inquiry -> Financial -> Summary screen for our AR GL. About 14 months ago the Period Balance Total amount was over by $19.74. This difference has increased three more times over the past 14 months by similar amounts. I have been tasked with locating the transactions that caused these differences and/or explaining why the two amounts no longer balance.
I have been researching table definitions and looking at scripts but feel I am just spinning my wheels. The only employee with Dynamics experience left abruptly about two years ago and since then employees have been doing things by rote, with little to no routine maintenance being performed.
Any assistance on where to start would be greatly appreciated!
Thank you.
David
LikeLike
David,
Have you tried using the Reconcile to GL feature for this? (GP | Tools | Routines | Financial | Reconcile to GL) Sometimes this works really well, other times not so much. But I would start with that to see if it helps track down the issues.
-Victoria
LikeLike
Hi Victoria… I have tried running the Reconcile to GL feature, but it crashes with a SQL Server error of:
Violation of PRIMARY KEY constraint ‘PK##1752157′. Cannot insert duplicate key in object’dbo.##1752157’.
I have researched the error but have yet to find a workable solution. There was apparently a KB952380 article with a query to fix the error but I am unable to locate it.
Thank you.
David
LikeLike
Hi David,
I cannot access that KB article either, but other KB articles with similar errors do not seem very helpful, either. Let’s tackle this from a different angle. First I have a question about the data you are comparing. I am not sure that I would use the Receivables Summary Inquiry window to compare to your General Ledger balance because if users are in the system entering and posting transactions as you are doing this, you are going to be chasing a moving target.
What I typically do as a start to trying to track stuff like this down is establish the real balances I am trying to match up historically, so I can see where they started to have an issue and to narrow down the data I need to search through. I do this by printing the Receivables Historical Aged Trial Balance (HATB) report using the GL Posting Date option and compare that to the GL balances. I would also recommend checking period ends, not current dates. So for example, if your periods are calendar months, print the HATB as of 10/31/11 and compare that to the GL period balance as of 10/31/11. Are those off? If so, go back until you find where they started to be off. Make a list of both balances in Excel and calculate the differences. This will help establish where you need to look more closely.
Another consideration I want to mention – unless something small and easy to find is causing differences, tracking down a $20 difference may not be worth a lot of time and effort. If your differences are consistently that small and if they go back and forth ($20 one month, -$20 another month, etc.) this may be just timing differences in some voids and not really an issue worth spending time on. I don’t know the size of your typical AR balance, but for most companies $20 is a pretty insignificant percentage of the total. I just wanted to mention this to add a little perspective. That said, I always make sure that our books reconcile to the penny, but that’s because the resource I am using for tracking this stuff down is me and this is what I do for a living and know it very well, and frankly, it’s not very common that our subledgers don’t reconcile to the penny anyway. 🙂
-Victoria
LikeLike
Hi Victoria… Thank you for the detailed information. From what I understand, they have a process where they validate the totals on the Receivables Summary Inquiry window and the General Ledger at the end of the day, when no one is else is in the system. Apparently the totals balanced to the penny until one day last year.
Since they balance daily, they know that the initial imbalance ocurred between 9/12/201 and 9/14/2010. I found one record in RM10101 with a debit amount that equals the balance difference, but do not know how to validate if this is the transaction in question.
Thank you.
David
LikeLike
Hi David,
Are the accounting folks not able to look at the transaction associated with this distribution record you found and see what the issue is? It’s pretty difficult to guess without looking at it. The typical issues are dates or account numbers, but it could be something else also.
-Victoria
LikeLike
If I can add a suggestion – take a look at the transactions (if not too many) created between the dates you noted and see if any of the distributions have an incorrect account going against your transaction type of RECV. It has been my experience that an out-of-balance is caused by users over-writing the RECV account with an account *other than* the control account that should be pre-populated on that transaction line. Good Luck!
LikeLike
Hi Kristie… It looks like we have over 13,000 transactions for those three days. If you can tell me know which tables/fields I should be looking at I could probably create a SQL query to narrow things down.
Thank you!
David
LikeLike
Again – merely a suggestion, no guarantees.
Here’s what I use to find the transactions entered incorrectly for Receivables:
SELECT * FROM RM30301
WHERE DSTINDX ***
and DISTTYPE = 3
*** = Receivables Control Account found by querying the GL00100 table
DISTTYPE 3 is for the RECV transaction type I mentioned earler
Victoria – if this is not the correct way for me to use your forum, please do not hestitate to let me know – I love this site though because so many times it has helped me out of some ruts 🙂
LikeLike
Hi Kristie,
It’s all good – I really appreciate you chiming in!
-Victoria
LikeLike
I think the reply form may have stripped out some of the code from Kristie’s reply, it does that to me all the time. Here is what the query should look like:
You could also search for the reverse condition:
-Victoria
LikeLike
Thanks Kristie! The query did locate two transactions, but one was a sale and one was a return so they should cancel each other out.
I am again going to recommend they post an entry to adjust for these small differences. Many individuals have spent too much time trying to track this down.
Thanks again!!
David
LikeLike
Thanks Victoria… Unfortunately the accounting folks are do not fully understand the processes and are relying on me to correct issues that have existed for months/years. Since I’m an IT person and don’t have an accounting background, I need explanations on why things are done and how they work, which they cannot supply. I think I have convinced them that the time and effort to track down the discrepancies is not worth it.
I now have two additional issues to research and don’t know where to start.
Thanks again!
David
LikeLike
David,
Consider talking to your GP Partner to see if they can help you bridge that gap between accounting and IT. Maybe you can have them do some training/troubleshooting for you that will help you learn GP better if you are going to be expected to support the accounting department in this way.
-Victoria
LikeLike
Hi Victoria… It just happened again. This time we can trace it to something that was posted on 12/27/2011. The Receivables Summary Inquiry was $10 lower than the GL Summary Inquiry for the AR cash account. Both summary windows were viewed with no one else in the system.
Is there any way to identify the detailed transactions behind each of these summary windows to locate the offending record(s)?
Thank you.
David
LikeLike
Hi David,
I would use the queries from the previous posts here. I just fixed them to show the code properly. However, based on our prior conversation, you may need to talk to your GP partner or GP support to help you look at the results and determine what is actually causing this. I would suspect there is a customer that has an account number not set up properly, but beyond that, without looking at it I cannot tell you more.
-Victoria
LikeLike
David, have your accounting folks looked at the default AR accounts assigned to any of your customers. That would automatically overwrite the “normal” AR account and could cause the issue that you’re seeing here. It would self correct over time if the customer record is changed.
LikeLike
Hi, I’m also an IT guy currently helping out the Accounts team with the same error. From what I have worked out so far this seems to be a temp table which is built to hold enquiry data from multiple tables. Work, Open, and History for RM Transactions in my case. I have narrowed my error to a transaction that exists in both RM20101 (RM Open File) and RM30101 (RM History File) with the same DOCNUMBR. It would seem the process left the RM20101 record when it should have removed it during posting. I could just remove the record but instinct tells me not to touch it. I have run reconcile and check links but no errors found. Has anyone solved this issue yet?
LikeLike
Hi Chris,
I am always very conscious of data integrity and doing anything directly inside the GP tables unless I am 100% certain. Because of that, I feel it would be irresponsible to simply say that you should delete what looks like a duplicate record in RM20101 without someone who understands the GP tables well looking at it. From what you are describing, most likely that is the solution, but it also may be that the record in RM30101 is not completely correct and/or other tables may need to be checked to make sure everything matches up, for example RM00401, RM20201, RM30201, RM10101 and RM30301. It may be best to ask for someone to look at this with you – maybe your GP partner or GP Support.
-Victoria
LikeLike
Hi Victoria,
I am using GP 10.0, trying to get some data from AP module to GL transactions.
Kindly please advise how I can link AP details to GL transactions.
Any column can be linked or there is other step for it.
Thanks, Murad
LikeLike
Murad,
You should be able to link from the GL tables to the payables tables using the ‘originating’ columns. ORMSTRNM will link to the vendor ID, ORDOCNUM will link the document number, ORTRXTYP will link to the document type, and ORCTRNUM will link to the voucher number.
-Victoria
LikeLike
Victoria,
Do you know which table hold Fiscal periods?
Thanks,
Hishma
LikeLike
Hishma,
There are 2 tables: SY40100 and SY40101.
-Victoria
LikeLike
Thanks Victoria!
LikeLike
Hello. Just found your site. Love it! I am a bit new to GP so any help is appreciated.
We are running GP10.
In the canned Metric “Gross Profit for the Past 12 Months”, the field for December 2010 is blank. All others are populating automatically. The literature I’ve found points to GL20000, and it appears to have data for that month…any ideas?
Thanks,
Rose
LikeLike
Hi Rose,
I am not 100% sure, but I seem to remember there being a bug that was causing this behavior. Unfortunately, I am not finding anything about this on the internet. 😦 I personally am not crazy about this particular metric because it is dependent on account categories all being correct and many companies do not pay attention to that when creating new GL accounts. Here is some more information on that: http://www.vaidy-dyngp.com/2010/08/gross-profit-metrics-daouds-article.html.
I would recommend posting your question on the GP Community Forum to see if others have seen this and can help you find the cause.
-Victoria
LikeLike
Hi Victoria.
Thanks for the fast reply. I used the information at vaidy-dyngp.com and found the data is there, per the smart lists referenced to check the values in the metric. Very cool exercise but still have the 0 data in our Dec 2010 bucket on the metrics graph, even though it’s there when I do the smart lists. Let me know if you hear anything else about a bug.
Thanks,
Rose
LikeLike
Hi Victoria,
Do you know if the GL Account Rollup has a corresponding SQL table where all Account Rollups that were set up in GP are stored? Is there a way to set up the Account Rollup Inquiry to pull GL Balances from multiple databases? Or even maybe by using the Smartlist Builder? Thanks!
Lynor
LikeLike
Hi Lynor,
Account Rollups have a bunch of related tables:
SE000200 Account Rollups Account Segment Index
SE000300 Account Rollups Options Header
SE000301 Account Rollups Options
SE000401 Account Rollups Account Period Detail
SE00400 Account Rollups Account Detail
SE465546 Account Rollups Calculated Column
SE810000 Account Rollups Account List
SE90001 Account Rollups Account List Accelerator
SE988977 Account Rollups Options Columns
There is no way to pull multiple databases into one Account Rollup, to do that I would usually use FRx. Yes, you can accomplish this with SmartList Builder, but it may be somewhat of a challenge to code this, as GP does not actually store balances, only net changes, so if you needed to see period balances, you would have to code that.
-Victoria
LikeLike
Victoria,
Do you know where the Sales Line Item Distributions are held? I am looking for the individual line item distributions and I cannot seem to find it in Resource Descriptions.
Ron
LikeLike
Hi Ron,
They should be in SOP10102. Please let me know if that is not what you’re looking for.
-Victoria
LikeLike
Bab,
Depending on your posting settings and a number of other factors, transactions that have been posted in payables may now be sitting in the GL waiting to be posted. They would be in the GL10000 and GL10001 tables at this stage. Only once the GL transactions are posted do they move to GL20000. Budgets are in GL00201, but you can also use view GL01201 to report on them. Hope that helps.
-Victoria
LikeLike
Hi Victoria
When we enter a sales order and apply a payment from a credit card. Is there an entry mane in the GL at that time?
Thanks
Vic
LikeLike
Hi Vic,
Yes, there should be a GL entry resulting from that.
-Victoria
LikeLike
Victoria
I searched the GL10000 and GL10001 tables for the amount or any sogn of it and found nothing. Any other places I should look? Also it is still an SO it has not been transfered to invoice yet , does that matter??
Thanks
Vic
LikeLike
Hi Vic
it wil only be in GL10000 GL10001 when you post the invoice in SOP and you just will see that journal in GL10000 GL10001 after you post SOP when your posting options is unchecked “post through GL” in setup / posting /
hope that helps
LikeLike
FAC
Thanks yes it does it!!!!
Vic
LikeLike
Hi Victoria,
I recently had an upgrade to GP 2010. Prior to the upgrade to 2010, I was able to change the description of an invoice within the GL. The path was:
Inquiry -> Financial -> Detail or History Detail -> And then click on the line I wanted to change
Next, I’d go to:
Extras -> Additional -> Maintain GL Ref ->
Then I’d change the description/detail of that invoice
I don’t see the “Extras” option any longer and I haven’t been able to find a way to do this. Is it still possible to perform this change? I appreciate your help.
Thanks,
Pooja Shah
LikeLike
Hi Pooja,
Sounds like you had a customization prior to the upgrade, as what you are describing is not possible out-of-the-box in GP. Customizations need to be upgraded separately when you upgrade GP, so you need to talk to whoever created that customization for you originally and see if they can upgrade it for you.
-Victoria
LikeLike
Hi Victoria
We are looking for a view that shows ALL gl transactions payables, receivables, bank transactions and journal entries.
Do you have anything like this???
Thanks
Vic
LikeLike
Vic,
How about this one: https://victoriayudin.com/2009/08/11/sql-view-for-all-posted-gl-transactions-in-dynamics-gp/.
-Victoria
LikeLike
Victoria
Right on a s usual. Is there an unposted version as well??
thanks
Vic
LikeLike
Hi Vic,
I didn’t include the unposted GL transactions, as I am not sure if all the same fields are available for them, plus it’s two tables instead of one. I will look into adding those at some point in the future. If you’re doing this yourself, you will need tables GL10000 and GL10001.
-Victoria
LikeLike
Victoria
Thanks!!! If i come up with something I will shoot it over to you…
Vic
LikeLike
Hi Victoria,
How do I know which transactions in GL20000 are included in the period of GL10110? Looks like I can’t use TRAXDATE and ORPSTDDT to determine if they fall in the month period in GL10110.
Many thanks,
gaganiaro
LikeLike
Gaganiaro,
Usually you can simply use the TRXDATE, however, there are exceptions. The few I can think of off the top of my head are stuff like beginning balances brought forward, adjusting periods that overlap and non-calendar month fiscal periods.
You should be able to use the OPENYEAR and TRXDATE fields in GL20000 together with the fiscal period setup in the company to determine the corresponding period in GL10110.
I would absolutely NOT use ORPSTDDT – that is the actual date something was posted and has nothing to do with GL dates whatsoever.
-Victoria
LikeLike
Hi Victoria
Please i got a problem with some journals posted in GL20000.
I got a sales invoice (date february 2011)
that was posted on feb 1st 2011.
but that transaction generated 8 journal entries in gl2000
JRNENTRY 8 is the correct distribution of the sales invoice and TRXDATE = feb 1st 2011
but JRNENTRY 1 to 7 are in 30th april 2010, 31th may 2010, 30th june 2010, 31th july 2010, 31th august 2010, 31th december 2010 and 31th january 2011.
And all these journals distributions are very small ammounts using inventory and cost of goods accounts
JRNENTRY=8 has ORGNTSRC = SLSTE00003908 AND SERIES=3 AND ORTRXTYP=3
AND JRNENTRY 1 to 7 has ORGNTSRC = GLTRX088013 AND SERIES=5 AND ORTRXTYP=0
I dont know why these journals were automatically created. The problem is that they have changed the data of 2010 (they didnt close 2010 periods)
Could you help me please?
is there a way to setup posting that not generate this journals?
Thanks a lot
LikeLike
FAC,
It sounds to me like journal entries 1 through 7 were actually generated by the POP invoices for adjustments to inventory cost. Are they hitting Inventory and Cost of Goods accounts? Usually the REFRENCE for these transactions will be ‘SALES’ and then the POP receipt number. So SALES11069 will be created from POP receipt # 11069.
I think that the dates will be the original dates of the inventory receipts, but I would have to test to confirm that. I don’t believe there is a way to stop this posting from happening. This is why it is important to look at the posting reports that come out when transactions are posted. And of course, to close periods that should not be posted to, as you mention. This would have gotten caught in Batch Recovery, so the dates could be changed as needed.
-Victoria
LikeLike
Thanks Victoria
those items quantities are integrated by Inventory Transactions (adjustments) by a third party manufacturing software.
The Sales invoice number is 002-002949
and the REFRENCE for journal entries 1 to 7 are IVT002-002949, IVA002-002949, VENTA002-002949 all of them with the sales invoice number at the end.
So i cannot find which inventory adjustments they are referring to.
The one thing i cannot understand is why they are hitting Inventory and Cost of Goods accounts? I mean why a sales invoice has to change a cost of good account?
Is there way to view a posting report again?
Thanks a lot
I really appreciate your help.
LikeLike
FAC,
IVT is an inventory transfer, IVA is an inventory adjustment, but I have not seen VENTA before…
What is the inventory valuation method? That might make a difference in finding these…if it is FIFO and this invoice was just posted, you should be able to see what the last cost layers sold were.
Most, but not all posting reports can be reprinted. For Sales, you can go to Reports | Sales | Posting Journals…but I believe what you’re looking for is the Cost Variance Journal, which is not something I see as available for reprinting.
I would recommend posting this on the GP forums to see if you get some more folks responding who have seen these…I have only seen them coming from the POP module, not from sales.
-Victoria
LikeLike
Thank you very much!
LikeLike
Hi Victoria ,
I have a journal entry in GL20000. It has,
SOURCDOC : GJ .
REFRENCE : SALESC123555
BCHSOURC : GL_Normal
SERIES : 5.
BACHNUMB :GLTRX441006
I tried tracing this journal in Inquiry , but it shows a message “Transaction History does not exist for this transaction”. In GL10000, some journals are available for ORTRXSRC : GLTRX441006 .(same as GL20000’s BACHNUMB) and these journal’s SOURCDOC,BCHSOURC ,SERIES are same as mentioned above . I dont know for which scenario and why these journals are created . Please help.
Thanks in advance.
LikeLike
Hi Divya,
This sounds like an inventory cost adjustment that got posted directly to the GL from a POP invoice. I would expect ‘C123555’ to be your POP Receipt Number for the invoice. The typical scenario is something like this:
The difference in the inventory cost for what was already sold will be posted directly to the GL in a transaction like you are describing.
Hope that helps,
-Victoria
LikeLike
Hi Victoria,
We are trying to perform the year closing for one of our entities and we receive a message that says the retained earnings account is inactive. I have checked table GL00100 in SQL and all of the retained earnings accounts are coded with a 1. I also looked at the account itself under the mastercard records and they are coded as active. Have you seen this message before?
LikeLike
Yolanda,
I have not seen this before. Are you closing to just one Retained Earnings account? Or do you have ‘Close to Divisional Account Segments’ checked?
Have you recently made any changes to your accounts? Have you tried running Check Links or Reconcile on the Financial series?
-Victoria
LikeLike
Hi victoria,
I am running GP10 SP3 + Hotfix (standard modules + Analytical Accounting)
As a test i have performed a year end in Fabrikam.
after year end i verified the BBAL details between
Summary inquiry Screen and Detail inquiry…
the date do not match why is it so… but for earlier years it matches Exactly.
Example:
Cash Operating Account : 000-1100-00
BBAL
Debit : 1529714.84
Credit : 921077.53
on clicking details of the same BBal Period
Detail inquiry screen displays followings
Cash Operating Account : 000-1100-00
Year: 2015
Date: 31-12-2014 to 31-12-2014
JV No : 3460
Debit : 608637.31
Credit : 0.00
Can you let me know why this is not matching?????
Same is the case with my live company tooo….
Thanks
Akram
LikeLike
Akram,
This may just be an issue with hoe GP displays/stores the data. Just to make sure I understand, can you please let me know the following:
1. Go to Inquiry | Financial | History Summary and bring up the account and the last closed year (2014 in your example), what is the Period Balance (last column) on the LAST line?
2. Go to Inquiry | Financial | History Summary and bring up the same account for the following year (2015 in your example), what is the Period Balance (last column) on the FIRST line?
-Victoria
LikeLike
Hi Victoria,
Thanks for instant reply
1. Go to Inquiry | Financial | History Summary and bring up the account and the last closed year (2014 in your example), what is the Period Balance (last column) on the LAST line?
2. Go to Inquiry | Financial | Current Year Summary Screen Summary and bring up the same account for the following year (2015 in your example), what is the Period Balance (last column) on the FIRST line?
The period Balance are same on both screen with last line matching the first line…
608637.31 USD
In live testing scenario…
The trail balance before and after matches completely, also the retain earning accounts balance is correct as per acct dept.
its only that this two screen do not match each other.
Is there any specific reason for this….
-Akram
LikeLike
Hi Akram,
This may be because you posted additional transactions to the closed year after the year was closed. Or you may be looking at the representation of how GP stores the year-end closing entry. Without seeing your data or at least some screen shots, it is difficult to tell exactly what the reason is for what you are seeing. However, if the ending period balance for the closed year matches the beginning period balance for the new year, there should be no issue.
-Victoria
LikeLike
can i attach the screen shot here in this forums…
as its fabrikam… i would like you to see this….
LikeLike
Akram,
You would have to post the pictures somewhere else and then put a link to them here. I would also caution you about using Fabrikam for intensive testing – it has data that is not quite clean and I have seen issues in the past with using Fabrikam to test year-end closing specifically. If you are only seeing an issue in Fabrikam, then it may be related to how the data was imported in there. It is usually better to test with a copy of your own data.
-Victoria
LikeLike
Case 1. ( Not matching case)
Case 2: Summary Matching only with Historical Details
Here you go…..
same is the case with my live company too….
i need to give my Finance dept a reason why this is happening and they still can continue as it will not hamper their work.
it happens only for BBF not for any other periods 1 to 12….
-Akram.
LikeLike
Hello Victoria,
Here are the cases both screen shot…
Hope you can help me out in this…
http://www.megaupload.com/?d=2EF0W9FI
LikeLike
Akram,
I am seeing this in some cases in our live data as well. Again, this is not an issue. It is simply how GP is displaying this. The only thing that matters on the beginning balance line is the period balance. If this is a real issue for the accounting department, I would recommend talking to GP Support to see if they can explain how this is getting populated.
-Victoria
LikeLike
thanks for lot for bearing with me….
Appreciate your responses….
LikeLike
Hi Victoria
Hope I am in the correct forum.
Accounting posted and invoice last month as 12/01/2011 instead of 12/1/2010. My cfo would like to now change the posting dat to 01/01/2011. Can that be done?
Thanks
Vic
LikeLike
Hi Victoria
My Controller needs to see all open balances with a term of CC (Credit Card for us)
Will any of your views show me that?
Thanks
Vic
LikeLike
Hi Vic,
From the description, it sounds like you would want to start with the Receivables Unapplied Transactions view. You would need to add a filter on the terms.
-Victoria
LikeLike
As usual the PERFECT solution and a very quick response!!!!!
Thanks again !!!!
You Rock!!
LikeLike
Hi Victoria,
I want to see if you can please give me your input. I need to post few transactions in June ,July Aug and Sept however, we closed the period and all the statements has been done. Can I still post trx to June? If so what are the best way to open the priod and post the transactions and close them again?
Anything else needs to be done after posting the transaction? like Reconcile to GL ?
Thank you,
LikeLike
maybe i can help with this
did you close the period of 2010 in Config / Company / Fiscal Periods by checking the month u wanted to close?
If so, if you want to open that period u just uncheck that month and post ur trx and close it again
no reconcile needed
the only problem would be if u already printed legal reports from those closed periods, data will change and u will have legal problems (well in my country we would)
i hope thats what u wanted
LikeLike
FAC,
Yes. That answers my question…
Thank you very much….
-Jim
LikeLike
Hi ,
I have encountered the MDA issue while posting.
I have verified GL10001 and DTA10300 tables. I found in there is one line in GL1001 table Orgseqnum field did not match with DTA10300 Seqnumbr filed.
How the GL10001 Orgseqnum get updated..? any logic or setup table behind that..?
I request you to give some more information on MDA analysis and GL10001 Orgseqnum details..
LikeLike
Vigneshwaran,
Since this sounds like a possible issue with your data, I would recommend contacting GP Support to make sure you get the most complete information and support specific to your situation.
-Victoria
LikeLike
Hi Victoria,
Sorry I forgot to mention the impacted tables.
This are the impacted tables:
SY00500
LikeLike
This site is really helpful. I would like to ask some concerns on GP 7.5:
The 1st problem is this, one of are users is about to post a IVADJ-B batch however, the batch was automatically marked to posting prior to triggering the actual POST button. Can this be possible?
Next, we ran the audit trail to check and found out that there was a IVADJ-A batch ran that was completed and finished successfully which immedeatly followed the posting of IVADJ-B. We do not know how this happened considering that the end-user did not trigger the POST button. We also checked that the IVADJ-B was has “marked” (MKDTOPOST) status ‘1’ and batch status (BCHSTTUS) ‘3’.
Could you help us and explain what happened to this? Thank you
LikeLike
Hello,
As you probably know, GP 7.5 had not been supported for a long time (more detail here), and I believe that there have been improvements in batch posting error handling in newer version of GP. It’s also been a pretty long time since I have worked on GP 7.5, however, most of the concepts should be the same.
BCHSTTUS = 3 means that the batch is still receiving transactions. When the batch is receiving transactions, the system will not allow anyone else to post it, so that may be why the marked to post status is 1. I have seen this happen when there is a problem, interruption or error during posting and the system will need manual intervention to be able to complete posting. Take a look at the Batch Recovery and Master Posting windows to see if you can still see the original batches and if there are any messages or statuses associated with them there.
-Victoria
LikeLike
Hello Victoria,
Thanks for your help. We were able to checked your resolution and fully understand how this scenario behave.
Cheers,
Freedom
LikeLike
I am trying to find out if anyone has ever experienced losing their detail journal entry lines from within a batch. The batchID, number of transactions, as well as the dollar amount of the entire batch remains in tact, but the details within that batch have disappeared. When I check the GL10000 table, all is as it should be, but when querying for the details within the batch in the GL10001 table, the distribution lines are not there. I ran Financial Checklinks in hopes it would resync everything, but no luck. Our biggest concern is with the intercompany journal entries which originated from other companies, the details are lost in some of those batches as well as others. My apologies if this is not the right way to get this issue posted, but I could not find where to go to create a new post. Thank you in advance for any help.
LikeLike
Kristie,
Sounds like something went really wrong somewhere…I have not encountered this before. The only thing I can think of is trying to restore from a backup if you have the ability to go back to when the data may have been there.
-Victoria
LikeLike
Thanks Victoria – from what we could tell there was some sort of hiccup with this particular database and at the same time the hourly transaction log was not getting generated, the details within the journal entries were mysteriously disappearing. We were able to recover back to the last hourly transaction log on to a test database and replicate the details over to our production database, but we still lost about two hours with of journals between the last good hourly log and the next one. So, my lesson learned, for your larger databases, monitor hourly logs, and if possible, create an alert if there’s a gap larger than 1.25 hours between logs. Our outsourcing partner thinks this may have all happened because of lack of space on the hardware where backups are stored until they’re moved to tape. Just sharing what I can in hopes others can avoid the fiasco we went through to recover what we could.
LikeLike
Kristie,
Thank you very much for the follow up and sharing what happened and what you have found.
-Victoria
LikeLike
Hi,
I am wondering if there an actual field in Dynamics that holds a cumulative GL account balance for each account balance as opposed to adding period balances together?
Thanks
LikeLike
Hi Bob,
I am not aware of any tables that hold period balances for GL accounts, only net changes. You would need to perform your own calculations to get period balances.
-Victoria
LikeLike
Ok. Thanks for the help. Have a nice day.
LikeLike
Hi Victoria
I was wondering the post date and time of a transaction, in ORPSTDDT only stores the date but no time
then i thought the DEX_ROW_TS field in GL20000 was the post and date time, but that field showed me todays date about an hour ago, im just curious what data stores in DEX_ROW_TS and why that field has today’s date, original post date was months ago.
Thanks a lot and pardon my english
you are the best
LikeLike
Hi FAC,
To my knowledge, the ORPSTDDT is the ‘Originating Posting Date’, not ‘original’. Slight difference, but it is important, as in GP ‘originating’ means where something came from. So in this case, the Originating Posting Date is the date the transaction was originally posted in the subledger it came from. For example, if you are looking at a GL transaction that was created by a Payables Invoice, this date will show when the Payables Invoice was posted. All ‘old’ date fields in GP will only have a date, no time, even though in SQL they are a datetime field. DEX_ROW_TS is a new field introduced in GP 10.0 and in this case will hold the date and time the record was created in the GL20000 table, so when it was actually posted in the General Ledger. Depending on your posting settings and many other factors, this date could be different from the Originating Posting Date. Also, the time in the DEX_ROW_TS field is show in UTC (Coordinated Universal Time), not the time on your SQL Server.
Hope that helps.
-Victoria
LikeLike
Hi Victoria
Is there any thread in this blog that describes about Analytical Accounting tables, structure etc.., as available for RM/PM/GL/CM etc..,
Rgds..Sreekumar
LikeLike
Hi Sreekumar,
I do not work with Analytical Accounting, so I don’t have any resources for that to share.
-Victoria
LikeLike
A user has contacted me after running the Distribution History Detail – Payables Managment report that an account came up “not found.” I have determined that in PM30200 the field for CHEKBKID is blank though that does not seem to be consistant filled or blank. Any suggestion on where to start tracing this back to the missing account?
Thanks,
Allen McEuin
LikeLike
Allen,
Typically an account not found would make me think that there is something wrong with the account index on the transaction. I would find the transaction this came up for in the PM30600 and look at all the account indexes for it, then look those account indexes up in GL00100 – are they all there?
-Victoria
LikeLike
Victoria,
I am attempting to link the GL20000 table to inventory items from receipts or po’s and am having difficulty because this table does not seem to hold the original po or receipt line number. Any suggestions?
LikeLike
Bill,
Typically (and definitely in this case) GP posts in aggregate – if you have 5 lines on a receipt that all go to the same account, you will not see 5 separate GL distributions for the same account in the same transaction, you will see one line combining them. So there is nothing to link a GL entry or part of it to a particular line number on a receipt. PO’s don’t get posted to the GL, so you’d only be able to link to a receipt and then from there back to a PO.
-Victoria
LikeLike
Dear Victoria,
First, thanks for this space.
I am working with analitical accounting, and I need to join the GL with AAG tables, but i have been troubles with it because i was doing it using sqncline and origsecnum that works in the most of the cases but it does not for a few ones.
So, how can i have something like
select *
from GL00100
where ACTINDX not in
(select ACTINDX
from GL20000
where TRXDATE between
DATEADD(yy,-1,GETDATE()) and GETDATE()
union
select ACTINDX
from GL30000
where TRXDATE between
DATEADD(yy,-1,GETDATE()) and GETDATE())
-Victoria
but with aag tables.
Hope you can help me, thank you.
LikeLike
Hi Marcia,
I am sorry, I do not work with Analytical Accounting, so I am not a good resource for this. I would recommend posting your question on one of the GP newsgroups to see if anyone there can help you.
-Victoria
LikeLike
Hi Victoria
The table explanations for all of the modules are great. Very useful ones.
I am trying if I can delete or mark inactive all the GL accounts which has not been used for last one year. It would so helpful for me if you can suggest any query for the process.
thanks
Jubith
LikeLike
Jubith,
I would not recommend deleting accounts from the database, as that can break all sorts of things in GP. Even changing them to inactive might not be the best idea, so definitely test this thoroughly and make backups. However, if you’re simply looking to come up with a list of these accounts, here is a query that would help:
-Victoria
LikeLike
Thanks for the reply and query. I would also not opt for deleting the records from GP, rather I would tick out the option ‘Allow Account Entry’.
If I filter the sub query with DISTINCT ACTINDX, it runs faster.
thanks.
*Jubith
LikeLike
Hi Victoria,
Thank you for all of your advice and help for the Dynamics GP Community, truly appreciate it!
I was wondering if you could assist me or shed some light on an issue we have. We just upgraded to GP 10.0 to GP 9.0. The upgrade went fine, and during the test upgrade we posted some test invoices to see if everything was going alright.
However, being new to GP myself, I did a mistake that I only removed these posted invoices from table RM20101… without realizing that the test invoices put numbers into the GL and Invoice Registries.
Is there a way to completely delete these records from our GP tables? I see that I could delete the GL from GL20000 table; however is this going to affect anything else, as in the GL account balances in GL10100 or etc?
Thanks in advanced for your help! Greatly appreciate it.
-Elizabeth
LikeLike
Hi Elisabeth,
Unfortunately you did something that I would not recommend doing. 😦 Taking posted transactions out of a table is a subledger is going to cause all sorts of havoc. Depending on the details of those transactions, there could be dozens of tables that got updated. Some of them are summary tables which add to totals every time a transaction gets posted, so there is no easy way to cleanly remove selected transactions from them. If it’s possible, I would recommend putting back the rows you have deleted or restoring from a backup and then cleaning these up from the GP user interface by either voiding them or entering returns/credit memos.
-Victoria
LikeLike
Victoria, thanks for the quick answer.
I agree with you and I think we’re going to put back the rows I deleted and let’s see if we could just void these invoices. I’ll let you know how it goes.
Thanks for your advice!
LikeLike
Hi Victoria
thanks for all your so fast replies you are very helpfull and the best.
Well i am going to bother you with another problem (sorry)
Someone screw up the post configuration and today all the posting from every module were not generating journal entries (cause the “post directly to GL” was not checked)
well after i killed that guy 😉 i am trying to fix this huge mistake,
restore the database to the yesterday’s backup is not an option cause there are a lot of transactions and users will not do all the transactions again,
so i was thinking to make a query of all PM, RM …. posted transactions that are not posted in GL20000 (well all transactions of today)
something like this (ie. PM20000 table)
SELECT VCHRNMBR from PM20000 WHERE VCHRNMBR NOT IN ( SELECT ORCTRNUM FROM GL20000 )
then i get the distributions from PM10100 of that documents and then make a journal entry for every VCHRNMBR and upload it to GP (through Integration Manager) and then post them.
is this correct? or there is another way to do that?
thanks a lot Victoria
LikeLike
FAC,
Yes, your approach sounds like it will work. You may want to enlist the help of GP Support or your partner to ensure that all transactions are captured. There may be quite a lot of tables and information to wade through.
Also, since it may be very difficult to ‘link’ the GL transactions to the subledgers after the fact, another option may be to gather the totals of the distributions and create a General Ledger transaction with just the totals.
Finally, take access to the Posting Setup window away from all users.
-Victoria
LikeLike
Victoria,
I am trying to find the details from GL. I have Jrnl No.
and Audit code (Starts wiht GLTrx….) How do I get the detail info? Can you please see if you can help me out?
-Jim
LikeLike
Hi Jim,
What kind of details are you trying to get?
-Victoria
LikeLike
Hello Victoria,
I need your help in above listed Commonly Used Tables GL Tabels for GL Reports.
WIll you please let me know about Tables series of DTA. what data has been stored in DTA tables? Whats stands for MDA or please let me know what is MDA Transactions?
LikeLike
Any suggestions on how to get started on a Crystal report that shows me actual vs. budget, current month and year to date. I use FRx for this for financials, but there is a need for external locations to track their spending and I thought Crystal on Citrix would be the only way to go for my cash strapped company. This way managers could log in, run the Crystal report and see exactly where they are with their spending… and not bother me for the info! 🙂
Thanks and happy holidays all!
Gordon
LikeLike
Gordon,
If you’re only looking to report on P&L accounts, this should be fairly straightforward. I would start with the following views/tables:
-Victoria
LikeLike
Victoria,
That worked out really well, thanks. I am on my way.
Basically, I have the report grouped by GL account and in the details are the actuals and budget amounts for each period. However, for some reason, if the actual for a given period is 0.00, but there is a budget amount, it will not display the record. This is problematic because it is not calculating the correct total for each account. I need it to display period balances that are 0.00.
Any thoughts on why this is happening or more importantly, how to correct it?
Thanks and regards,
Gordon
LikeLike
Gordon,
You probably need to have the GL account master (GL00100) as your first table, so that you capture all the accounts, then link in GL11110 and GL01201.
-Victoria
LikeLike
OK, that table is in the mix. I allowed it to Auto-Link the tables since my linking is weak. In looking over the links it feels compelled to link all 4 tables (I added GL00105 for the ACTNUMST field) by each one of the ACTNUMBR_1, …2,… 3, and so on to ACTNUMBR_10.
Is that overkill? Should I try to do the linking myself?
Thanks,
Gordon
LikeLike
That is absolutely overkill. The only linking you need is on the ACTINDX which will be present in every one of those tables and then possibly on the period ID’s, depending on how you are coding that in your report. Use left outer joins on the table to be safe.
-Victoria
LikeLike
OK, that didn’t pick up the missing amounts.
I am actually trying to get this done right with one company (as I know you have advocated in a previous email from a few months back), but not in a view but just with the database itself.
So, now I am going to try to create the view (one company only) to see if that will get me to where I want to be.
In setting it up, I don’t see that I even need GL00100 as I can get the ACTDESCR from GL11110, so I removed that table. When I run the query, the results are not what I want, so my linking must be wrong.
Using GL11110 and GL01201, how can I get the results to give me the period balance and budget amount just once for each account for each period?
Thanks.
Gordon
LikeLike
Gordon,
There are many different ways to do this. Here is one option:
select n.actnumst [Account],a.actdescr [Name],
gl.periodid Period,
coalesce(gl.perdblnc,0) Actual,
coalesce(b.budgetamt,0) Budget
from gl00105 n
inner join gl00100 a
on n.actindx = a.actindx
left outer join gl11110 gl
on gl.actindx = n.actindx
and gl.year1 = 2009
left outer join gl01201 b
on b.actindx = n.actindx
and b.budgetid = '2009' --change to yours
and b.periodid = gl.periodid
-Victoria
LikeLike
Gordon, actually, this might be better:
SELECT n.ACTNUMST Account,d.ACTDESCR Name,
a.PERIODID Period,
SUM(CASE a.AmtType
WHEN 'Actual'
THEN Amt ELSE 0
END) Actual_Amt,
SUM(CASE a.AmtType
WHEN 'Budget'
THEN Amt ELSE 0
END) Budget_Amt
FROM
(SELECT ACTINDX, PERIODID,
BUDGETAMT Amt, AmtType = 'Budget'
FROM GL01201
WHERE BUDGETID = '2009' -- change
UNION ALL
SELECT ACTINDX, PERIODID,
PERDBLNC Amt, AmtType = 'Actual'
FROM GL11110
WHERE YEAR1 = 2009 -- change
) a
LEFT OUTER JOIN GL00100 d
ON a.ACTINDX = d.ACTINDX
LEFT OUTER JOIN GL00105 n
ON a.ACTINDX = n.ACTINDX
GROUP BY n.ACTNUMST,
d.ACTDESCR, a.PERIODID
-Victoria
LikeLike
Thanks Victoria, I will give this a try and let you know how I make out.
Happy New Year!
Gordon
LikeLike
Victoria,
Preliminary results are in and so far things are looking good, really good.
I’ll keep you posted.
Thanks so much.
Regards,
Gordon
LikeLike
Victoria,
I’ve successfully created this report for one company and have now made it so that it reports data from multiple companies.
However, with one company, it was easy to just use the Select Expert to give it a range of GL accounts (the Income Statement accounts), but with multiple companies, that range in the Select Expert won’t work for all.
Would I have to enter the account number range in the SQL view or could I somehow set up the Select Expert at the report level to give me only the Income Statement accounts?
Thanks and regards,
Gordon
LikeLike
Gordon,
My 2 cents: if you’re always only going to want the Income Statement accounts (or P&L accounts as GP calls them), then do this in SQL – if you have a lot of data this will speed up processing. You could limit your selection on GL00100.PSTNGTYP = 1. On the other hand, if you think you might need to see all the accounts in a future version of this report, then bring in the GL00100.PSTNGTYP column so that you can use that in the record selection formula in Crystal.
-Victoria
LikeLike
Victoria,
That did it! Thanks. I think I have everything I need on this report.
Thanks so much for your assistance.
Regards,
Gordon
LikeLike
Hello Victoria,
Tons of Congrats for MVP.. I need your help in above listed Commonly Used Tables GL Tabels for GL Reports.
WIll you please let me know about Tables series of DTA. what data has been stored in DTA tables? Whats stands for MDA or please let me know what is MDA Transactions?
LikeLike
Sanjay,
MDA stands for Multidimensional Analysis, a GP module that allows tracking of additional information for GL accounts. Similar to Analytical Accounting, but much simpler. If your company is not using MDA there is no reason for you to ever need the DTA tables for your reporting.
-Victoria
LikeLike
How can i find out what tables the smartlist under financial->account transactions uses
LikeLike
Larry,
There is a manual called IG.pdf on disk 2 of the GP 9.0 installation that lists the tables in the out of the box SmartLists. For Account Transactions, here is the list:
GL10001 – GL_TRX_LINE_WORK
GL20000 – GL_YTD_TRX_OPEN
GL30000 – GL_Account_TRX_HIST
MC40200 – MC_Currency_SETP
GL00100 – GL_Account_MSTR
GL10000 – GL_TRX_HDR_WORK
-Victoria
LikeLike
Larry,
There’s a View called ‘AccountTransactions’.
You can check it.
Erik
LikeLike
Hi Victoria,
I have a client that is looking to find the table name used after an inventory roll and before Reval so he can create a Crystal Report.. Can you tell me the name of the table he can use?
Thanks,
Mellisa
LikeLike
Hi Mellisa,
Most likely there is more than one table involved. I am not quite sure what you mean by ‘inventory roll’, but in general I think your client may need to give you some more detailed specifications for the report they are looking to create for you to help them find the appropriate tables. Sorry not to gave a quick answer.
-Victoria
LikeLike
If you are referring to having manufacturing, then the table you probably want is ICIV0323.
LikeLike
Thanks for your summary of tables. It is very helpful. I was wondering if you can touch on adding custom tables to GP. In GP 9, there are only 4 user defined fields. Because GL accounts have multiple dimensions, it would be great if one could add an additional table for user defined fields.
It would be even better if you could update the tables in the account maintenance window.
Thanks for your thoughts,
Joe
LikeLike
Joe,
Can you elaborate a little on what you’re looking to track in these additional dimensions? There are some tools available that might help, like Extender, MDA and AA, but whether any of them can be useful in your situation would depend on the specific need.
-Victoria
LikeLike
I would like to have a query for recievables management to get the following information –
Invoice number
GL Post Date
Counterparty Name
Amount Due
Cash Due Date
Date Paid
Amount Paid
Description Field
Days Past Due
Can you provide me the query?
LikeLike
Atif,
I do not have a query like this available and it would take some work to put together. If you are interested in having this created for you as a billable project, please let me know.
-Victoria
LikeLike
Victoria,
Would you be able to tell me what tables will I be looking for to build the query?
LikeLike
Atif,
I would start with RM20101, RM30101, RM20201 and RM30201. My RM Tables page has more detail on these.
-Victoria
LikeLike
Hi Victoria,
The table explanations for all of the modules are great…I reference it all the time.
I was wondering if you could assist me or shed some light on an issue we have. Somehow, a PO was created and several of the items were marked as “Non-Inventory” even thought they are Inventory Items. The PO was received and posted. Now, our General Ledger has a total for this PO that does not match the Sub Ledger because the sub-ledger only included the Inventory Items from the PO. Any thoughts or suggestions on how to correct/proceed?
Thanks so much.
Brent
LikeLike
Hi Brent,
If the GL is correct and these items are still in your physical inventory, then I would adjust the inventory subledger by entering an inventory increase adjustment transaction and not posting it to the GL. Hope that helps.
-Victoria
LikeLike
Victoria
Thank you for the advice. I had a notion that it would use the General ledger. Having never used MGP before I find myself in the proverbial deep end. I have sent an email to the users to determine how they use MGP for their asset management.
I am from the other side of the world in Cape Town South Africa. I am a business analyst doing data analyst work on an EDW project. The client is in Central Africa.
Just to make my job a little more difficult, the DB I have is the structure only, no data…
Johannes
LikeLike
Victoria
I certainly gained some insight from your website. I have a very difficult task at hand. I have to do Source to Stage and Source to Model mapping from the MGP database. What makes it difficult is the fact that I do not have an MGP system to work with. I have to map straight from the tables and column headers.
I virtually have to find out what tables are utilized for a specific report and then work out what field refers to a specific report measure.
Therefor your refrences to the table names and where they fit in, is helping me. I am however stuck with assets. The database that I have, does not have the FA***** tables. What other tables can be used for assets?
Regards
Johannes
LikeLike
Johannes,
If the database you have does not have FA tables, the Fixed Assets module is not being used, so assets are being tracked some other way. You would need to talk to the users to find out how assets are being tracked and where. The two most common methods I have seen when not using the Fixed Assets module are: (1) setting up a General Ledger account for each asset or (2) tracking asset details outside of GP, typically in Excel, and only entering summary transactions into GP’s General Ledger.
-Victoria
LikeLike
Victoria,
Thanks again for this helpful information. I’m currently using SBF and I do not have a GL11110 or GL11111 tables. (We will be updating later this year!) Do you know of a way to link account descriptions to the GL tables? For example, I have tried many ways to link GL00100 to GL20000 or GL30000 with no success.
I am attempting to pull the expenses broken out by category, instead of just CGS. Do you know of a way to do this? Thanks!
– Will
LikeLike
Will,
You can link GL20000 or GL30000 to GL00100 on ACTINDX. You may also want to link in GL00105 (also on ACTINDX) for the full account number.
-Victoria
LikeLike
Victoria,
I just discovered your site today while writing some custom report queries for Dynamics GP. This information is very helpful.
I have a background in database design, SQL, Java, Linux, sys-admin and Windows-Linux interop but I don’t know much about the internals of Dynamics. With recent developments here at work, I’m now forced to learn about Dynamics. So far, it’s been interesting and you have shortened the learning curve somewhat with the information you provide on your blog.
Cheers,
Joe
LikeLike
thankx
LikeLike