Company/System Tables
Commonly Used Tables – Company databases:
SY00500 – Batch Master
SY01200 – Internet Addresses
SY01401 – User Defaults
SY02200 – Posting Journal Destinations
SY02300 – Posting Settings
SY03000 – Shipping Methods Master
SY03100 – Credit Card Master
SY03300 – Payment Terms Master
SY03900 – Record Notes Master
SY40100 – Period Setup
SY40101 – Period Header
TX00101 – Tax Schedule Header Master (header)
TX00102 – Tax Schedule Master (detail)
TX00201 – Tax Detail Master
TX30000 – Tax History
Commonly Used Tables – System (DYNAMICS) database:
ACTIVITY – User Activity
SY003001 – Account Definition Header
SY00302 – Account Definition Detail
SY00800 – Batch Activity
SY00801 – Resource Activity
SY01400 – Users Master
SY01402 – System User Defaults
SY01403 – User Tasks (Reminders)
SY01404 – Customer Reminders Setup
SY01500 – Company Master
Security Tables in GP 10.0 – System (DYNAMICS) database:
SY09000 – Task master
SY09100 – Role master
SY09200 – Alternate or modified form and report ID master
SY10500 – Role assignment master
SY10550 – DEFAULTUSER task ID assignment master
SY10600 – Tasks assignments master
SY10700 – Operations assignments master
SY10750 – DEFAULTUSER task assignment
SY10800 – Alternate or modified form and report ID assignment master
Official list of BCHSTTUS (Batch Status) in SY00500:
0 – Available
1 – Marked to Post
2 – Available / Delete
3 – Marked / Receive
4 – Marked
5 – Marked / Print
6 – Marked / Update
7 – Posting Interrupted
8 – Journal Printing Interrupted
9 – Table Updates Interrupted
10 – Recurring Batch Error – Edit Required
11 – Single Use Error – Edit Required
15 – Computer Check Posting Error
110 – Checks Printing
130 – Remittance Processing
Francisco’s list of BCHSTTUS (Batch Status) in SY00500:
[This is a much more comprehensive list posted by Francisco Hillyer in a GP Newsgroup - provided courtesy of e-mail by Robert Cavill - thanks Robert and Francisco!]
0 – Available
1 - Batch Posting
2 - Batch Being Deleted
3 - Batch Receiving Transactions
4 - Batch Done Posting
5 - Being Printed
6 - Being Updated
7 - Interrupted While Posting
8 - Interrupted While Printing
9 - Interrupted While Updating
10 - Recurring Batch Errors / Transactions Did Not Post
11 - Single Batch Error / Transactions Did Not Post
15 - Error While Posting Computer Checks
20 - Interrupted While Processing Computer Checks
25 - Interrupted While Printing Computer Checks Align
30 - Interrupted While Printing Computer Checks
35 - Interrupted While Printing Computer Checks Align Before Reprint Computer Checks
40 - Interrupted While Voiding Checks
45 - Interrupted While Reprinting Checks
50 - Interrupted While Processing Remittance Report
55 - Interrupted While Processing Remittance Report Align
60 - Interrupted While Printing Remittance Report
100 – Processing Computer Checks
105 – Check Align Being Printed Before Print Checks
110 – Printing Computer Checks
115 – Check Align Being Printed Before Reprint Checks
120 – Voiding Computer Checks
125 – Reprint Computer Checks
130 – Remittance Report
135 – Printing Remittance Align Form
140 – Printing Remittance Form
Series:
1 – All
2 – Financial
3 – Sales
4 – Purchasing
5 – Inventory
6 – Payroll
7 – Project
10 – 3rd Party
BACHFREQ (Batch Frequency):
1 – Single Use
2 – Weekly
3 – Biweekly
4 – Semimonthly
5 – Monthly
6 – Bimonthly
7 – Quarterly
8 – Miscellaneous
–
Last Updated: 10.15.2009
Hello victoria,
i am developing an application for Dynamics GP and i created some additional tables under one of the companies. The problem that i am having is that im able to perform a select to any of the Dynamics GP tables but when i try to perform a select to one of my tables i get ” INVALID OBJECT NAME”
i changed all the secutirty settings on my tables and its the same issue. im using the GPConnNET reference on connect to GP. i really hope that you can help me. thank you
Hector,
I am not the best resource for this, as I do not do this kind of coding myself. I would recommend posting your question on the GP Customer forum to see if some others more experienced with this can help you.
-Victoria
thank you for your response, just FYI i fixed the problem by changing the ODBC thank you again
Hi Victoria,
I know I can get company name from SY01500 table in the Dynamics DB….I want to link this to the bank account details for the company, sort Code, Acc number etc….which tables are these found in?
Many Thanks in advance
Nev Browitt
Hi Nev,
The bank details will be in the SY04100 table (Bank Maintenance) in the GP company databases. If you’re looking for checkbook information, that is the CM00100 table (Checkbook Maintenance).
-Victoria
Hi all
GP9
I need to create a user account report from GP referencing all areas related to basic user setup for audit purposes.
Where is the information shown under ‘Advanced SQL Server options’ stored?
I don’t see any references in SY01400 other than possibly SECACCS but that does not appear to change when marking/unmarking the check boxes.
Help always appreciated
Regards
Stu
found it:
sys.sql_logins in master
Stu,
Thanks for the update – glad you found it!
-Victoria
Hi Victoria,
We are seeing that when we make a change to the GL account assignments at the item class level, it gives us the option to push these changes to all items in the class. However, we are seeing that it is not pushing changes to the Unrealized Purchase Price Variance down to the items in that class.
Can you tell me what table I can query to view Inventory Item to GL Account assignments?
Thanks,
Jeff
Hi Victoria,
Sorry for the noise, I found the associations in IV00101.
Thanks,
Jeff
Jeff,
Great, thanks for the update.
-Victoria
Hello Victoria,
I am working on GP 7.0 I am trying to set new User Class for entry level only. Accordingly I have made changes thru Security Level but still its shows all pallets although i have keep unmarked. Please help me on this. Also please let me if there tables wherein we can make changes for User Class or access.
Thanks
Sanjay Parab.
Sanjay,
Are you using Advanced Security? If so, can you try rolling the user class settings down to the user(s) to see if that will help? If not, I am not sure how much I can help, as the ‘old’ security is something I have not used in many years. One thing you can try after making all the changes to the user class is to create a new user and put them in that class – does the new user have the appropriate security?
-Victoria
Can you provide definitions for the SY02000 fields – DICTID, RESTYPE, RESID, ALIAS, ALTDICID?
Example If you look at the SY00500 table (BCHSTTUS field – 0 is for Available, 8 is for Interrupted while Printing etc etc
Many thanks
Colin,
Maybe I have not had enough coffee yet, but I am not seeing the SY02000 table anywhere. Perhaps I do not have the module that installs that table on my test machine. However, by the name of the fields it sounds like you’re asking about a security table. If so, I am not aware of a listing available for the value of those fields, sorry.
-Victoria
This seems to be in older versions. We’re trying to figure them out too.
Hi Victoria,
Congratulations on your MVP status for another year.
This error that we get is driving us mad.
When we try to post a timesheet batch (timesheet batch entry window) – we get the following error “This Batch must be pre-processed first”
Have printed edit list – no errors
Batch is available.
Any thoughts would be appreciated.
Cheers
Paul
Hi Paul,
I have not come across this error before, nor can I find anything even remotely close to it in the GP KnowledgeBase. If you’re not able to get help on any of the GP newsgroups, I think you may need to talk to GP Support to resolve this.
-Victoria
Hi Victoria,
In Transaction Dimension Code, is there a way to modify the accounting class link without affecting the unposted transactions?
Thanks.
Ernesto
Ernesto,
Is this for Analytical Accounting? If so, I would recommend posting your question on one of the Dynamics GP newsgroups (links are on the right side of this page) with some more details of what you are trying to accomplish.
-Victoria
Dear Victoria,
I was upgrading my GP from SP1 to SP4 and I have a total of 7 companies.
After the upgrade.. 2 companies were successful and the others, when you login to GP utilities, have a small icon with a lock.
When you try to select one of them it gives you “The company is being updated by another client”
Is there a way to check the status of the companies from SQL because there seems that something went wrong during the upgrade and they became locked? or do you have any solution in mind ?
thank you in advance,
Barrak
I found the solution for the locked companies problem.
There is a table in DYNAMICS database with the name dulck and this has a list of locked dbs for update.
Thank you anyway Victoria,
Regards,
Barrak
Barrak,
Glad you were able to find a solution. Thanks for the update.
-Victoria
Hi all
Is there a best practice for recovery of Smartlist Favourites should a user delete one other than user recreation of said favourite?
If not, how would I best recover a deleted favourite if user recreation is not an option?
Thanks as always for any assistance.
Stu
Stu,
The only other option would be to restore from a backup. That might be a lot more work than having the user recreate their SmartLists. You could also restore the backup to somewhere else and see if you can find the needed data and copy it directly into your tables. Unfortunately, I do not have all the tables needed for this and you may interfere with new favorites created in the meantime, so you may need to do some additional research and/or talk to Dynamics GP support.
-Victoria
Dear Victoria,
I know this question might sound lame but I have a company for testing that is not showing in GP, although it has been in GP before now its not showing.
I checked SY01500 and its not listed.
My question is how can we put it back to show in GP and is it a manual procedure?
Barrak,
If the company was showing up before but is no longer there, especially if it is not in the DYNAMICS..SY01500 table someone would have had to remove/delete it. Without knowing the details of what exactly was done, the safest suggestion I can think to get your test company back is to create a new company (I would use a different database ID, but you can use the same company name if you want). Then once the new company is created, if you want to get the data in your old test company into the new one, just restore from the old to the new test database in SQL.
-Victoria
well thank you so much for the reply victoria.
I deleted the testdb from sql and then recreated it in GP and then restored it just like you said.
thank you again
Hi Victoria,
I am stuck with a stuck check batch in recovery , i checked the sy00500 table the batchstatus is set to 15, there is no entry in the dynamics..Sy00800 for that batch , we are using ver 6 ,
the checks printed successfully
vendor invoice shows open
vendor pmt shows work
gl did not update
checkbook did not update , however in checkbook maintenance it shows the correct next available check #
Any idea or input in this regard will be highly appreciated.
Thanks,
Vinay
Vinay,
Check posting problems are typically very difficult to pinpoint remotely like this, however, I would recommend changing the batch status to 0 (and also error state to 0 if it is not) in the SY00500 table, then trying to open the batch in GP to see if you can tell what the problem is.
-Victoria
Thank you victoria for your suggestion, I updated the batch status to 0 , the error state was already set to 0, had the user print the batch to check for any errors, there were no errors found, the user reprinted the checks with the same check numbers (over writing the ones that were used), and re processed it , the batch got processed fine without any issues.
Thanks again for you help
Vinay
Hi Victoria,
Could you please help me with this concern? I’d like to disable the pop-up for ‘credit limit override password’ on Sales Order document within SOP window. Do you know where I can go to disable it?
Many Thanks & Warm Regards,
Nikki
Nikki,
I do not know of a way to completely disable that pop-up without a customization. The only work around I can think of is setting the credit limit to unlimited for every customer – that would ensure that you are never prompted to override the credit limit.
-Victoria
I see, thanks so much for your reply. Once again, I really appreciate all your help!!
Best Regards,
Nikki
Hello Victoria,
In which table I would get Period Start Date and Period End Date?
Sanjay,
Period start and end dates are in the SY40100 table. You can filter by SERIES = 0 to get rid of all the extraneous data.
-Victoria
Thanks Victoria Its really helpful for my report.
Regards,
Sanjay.
Can you pls let me know what is Posting date, Posted date and Document date in Payable Module?
Sanjay,
This is an excellent and very popular question – please take a look at my latest blog post for the answer.
-Victoria
Victoria,
Sometime ago we lost our Fabrikam, Inc. and would like to reinstall it. Any idea as to how we would do this?
Thanks!
Bill,
It would really depend on what you mean by ‘lost’, as there might need to be some cleanup done, but I would start with going into GP Utilities and choosing the option to reinstall the sample company.
-Victoria
The Fabrikam company was completely removed through the transistion to a new server as they figured it was not needed. I ran GP Utilities and installed a sample company and it created a “TWO” company but I could not access it from GP.
Bill,
Are you able to log in as ‘sa’ and access Fabrikam? If so, you need to grant access to the other users by going to Tools > Setup > System > User Access.
-Victoria
It will not list the Fabrikam company in the company list.
Bill,
In that case, I would delete the TWO database in SQL and run the automated solution to delete references the TWO company out of all the tables. You can find that by going to this link for automated solutions. It is called Delete Company in Dynamics GP. Once this is done, re-create the sample company again.
-Victoria
I was able to delete the TWO company and re-created the sample company. I can now access the Fabrikam, Inc. company. Thank you for your help on all of this!
Victoria,
First of all thank you for putting all these useful info on here, It has helped in the past in number of ways in locating the data,
Now to the question, what would be the best way to remove a batch,
Vinay,
Can you please provide some more information? What exactly are you trying to remove and from where?
-Victoria
hai
Yudin
it is execellant composition of all tables at one place
i am searching from the last 4 months
Iam aFinance Controller of EPSILON at hyd,India,& we r implementing Nav 09
thank u for u guidance
get back to u if rerequired
Victoria,
We have a transaction that is stuck in the marked/print (5)status. Generally our stuck transactions have not yet been posted (mkdtopst 1 and bchsttus 0) and thus we are able to simply reset the transaction and the user can then repost. In this case the transaction shows both the sy00500 table and the dynamics sy00800 table, however the user is not currently logged into GP. I’m tempted to simply reset the transaction to batch status 0 and marked to post 0 but I’m concerned about the fact that it’s already posted. Any suggestions? Thanks!
Dave,
The batch being in SY00800 simply means that there is a stuck process, not that it’s already posted. First thing I would try is logging in as that user to see if GP will recognize there is a process running and finish it. If that does not work, then you would need to delete the process out of the SY00800 table and reset the status in SY00500. To verify that the batch is not already posted (while that is unusual, I have seen it happen), I would typically check the work and posted tables for your transaction type. Hope that helps.
-Victoria
Victoria,
Thanks again.
Thanks so much for the response. Having the user log back into that company and then run batch recovery worked! Who knew it would’ve been so simple
Dave,
Great, thanks for the follow up. This is one of the things that I think has gotten much better in the last few versions of GP.
-Victoria
Hi,Victoria,
Thanks for your prompt response. I try to add the INTERID = ‘INSG3′ before “Select” but system saying that “Incorrect syntax near “=”. Would you please take a look the following statement and give me some input?
Thanks a bunch!
SELECT GL00105.ACTNUMST, GL00100.ACTDESCR, GL20000.JRNENTRY, GL20000.TRXDATE, GL20000.DEBITAMT, GL20000.CRDTAMNT, GL20000.DSCRIPTN, GL20000.SOURCDOC, GL20000.ORMSTRNM, GL20000.REFRENCE, SY01500.CMPNYNAM
FROM INSG3.dbo.GL00100 GL00100, INSG3.dbo.GL00105 GL00105, INSG3.dbo.GL20000 GL20000, DYNAMICS.dbo.SY01500 SY01500
WHERE GL00105.ACTINDX = GL20000.ACTINDX AND GL00105.ACTINDX = GL00100.ACTINDX AND ((GL00100.ACTNUMBR_1 Like ’1100%’) AND (GL20000.TRXDATE<{ts '2009-05-01 00:00:00'}))
Lily,
At the end of your query, add the following:
AND SY01500.INTERID = 'INSG3'-Victoria
Hi, Victoria,
I am trying to show “Company name” on my query with GL 00100, GL00105, GL 20000 and SY01500 selected. However, it does not show up correctly. What kind of relationship should I set up beteween SY01500 and other tables?
Thank you very much!
Lily,
Since the SY01500 table is in the DYNAMICS database and the other tables are in your company database(s) you would need to do a link on something like:
INTERID = 'COMP'replacing COMP with your company database ID.
-Victoria
Pingback: Releasing Stuck Batches and Transactions without exiting all Users - Developing for Dynamics GP