System/Setup SQL Code

Dynamics GP System and Setup SQL Code

Change fiscal period names query to change fiscal period names
Fiscal Years and Periods queries to find fiscal years and periods
Security Details in Dynamics GP
Security Roles and Tasks in Dynamics GP
Security and SmartList Details in Dynamics GP
SOP Email Setup in GP 2010 and later shows the status and format of the SOP email setup for each customers as well as the email addresses for the default bill to address
User Activity in GP shows all the users logged in and a count of the batches, resources and tables each user has locked or open

15 Responses to “System/Setup SQL Code”

Dan Reply November 28, 2018 at 2:33 pm

Victoria,
We are running Dynamics GP 2013 Version.R2. Our IT group is quite busy at the moment and we are not in a position to upgrade to the latest version. That said, our IT manager wants to find out if there are any issues with moving to SQL-2017 on the back end.

Thanks,
Dan

LikeLike
- Victoria Yudin Reply November 28, 2018 at 3:19 pm
  
  Hi Dan,
  
  SQL 2017 is not supported with GP 2013 and there could absolutely be a serious issue, particularly when you finally do go to upgrade to a newer version of GP. I have seen a number of companies go through some significant pain after upgrading to a SQL version that’s not supported for their GP version and I would strongly recommend against this.
  
  -Victoria
  
  LikeLike
Dan Reply June 27, 2017 at 11:46 am

Hello Victoria,
I found a set up that is causing us issues in running EOne SmartConnects AP mapping.

What does checking/unchecking the box marked “Enter Corresponding Company ID” really do. It is on the Dynamics GP Intercompany Set Up screen.

The documentation is extremely helpful:

“Mark the Enter Corresponding Company ID option if you enter Corresponding Company IDs when you enter intercompany transactions.”

Thanks,

LikeLike
- Victoria Yudin Reply June 27, 2017 at 11:54 am
  
  Hi Dan,
  
  LOL on the documentation! 🙂 Unfortunately, I cannot tell you what that checkbox does, I’ve never had the occasion to find out. However, I can tell you that I always leave that unchecked in all my installations and have never seen that cause a problem for any of our customers. We are also using Intercompany extensively internally and we have that unchecked.
  
  Is the problem you are running into caused by having this checked or unchecked?
  
  -Victoria
  
  LikeLike
  - Dan Reply June 27, 2017 at 12:25 pm
    
    You got that correct, our problem is that may years ago our partner suggested we check that box.
    
    The question is what sort of mischief will arise if we go ahead and turn it off?
    
    Thanks,
    Dan
    
    LikeLike
    - Victoria Yudin Reply June 27, 2017 at 12:44 pm
      
      Hi Dan,
      
      Unfortunately, no way to tell without a bunch of testing, which I would always recommend for this kind of change. But, like I said, none of our customers have the checked and everything has been fine.
      
      -Victoria
      
      LikeLike
Tom Nichols Reply September 18, 2012 at 5:50 pm

Victoria-

I wasn’t sure where to post this, but we are debating using the organizational structure to segregate entities in a single database. Primarily we want to eliminate a huge amount of intercompany entries, but I am wondering if there is any actual reporting you can leverage using the organizational setup? It seems the org struct centers more around access, control, and visibility rather than any real reporting capabilities?

LikeLike
- Victoria Yudin Reply September 20, 2012 at 11:54 am
  
  Tom,
  
  I am not aware of any exiting reporting that leverages the organizational structure.
  
  -Victoria
  
  LikeLike
Farman Reply September 1, 2012 at 2:10 pm

HI Victoria,

my company is opening up a branch in the UK, is it a good idea to have uk setup as a site in GP or have a separate company all together and how would multicurrency become easy for Finance as well as for IT

LikeLike
- Victoria Yudin Reply September 5, 2012 at 3:38 am
  Farman,
  
  There is no short or easy answer to this, as it will depend on a great many variables and your specific situation. There are some cases where it is clearly needed to set up a separate company, here is a quote from my book on implementing GP:
  
  There are situations when separate companies are clearly recommended in Dynamics GP:
  
  If accounting records must be kept in different functional currencies to conform to local requirements
  
  When using Dynamics GP Payroll and there are different Federal ID Numbers
  
  If there are entities that have different fiscal periods
  
  Some types of businesses require separate accounting books to be kept to comply with various federal and state regulations
  
  There is additional discussion in the book on this subject, but often it is best to get together with your GP Partner to discuss your specific needs so they can recommend the best solution for you.
  
  -Victoria
  
  LikeLike
Nishcalen Chinappa Reply December 21, 2011 at 6:09 am

Victoria, sorry to bug again. this script does not return all active users. What i need is a script to return all enabled users in GP

LikeLike
Nishcalen Chinappa Reply December 21, 2011 at 6:07 am

Thank you so much for all your help

LikeLike
Nishcalen Chinappa Reply November 24, 2011 at 9:27 am

Hi Victoria,

I want to ask a silly question. I need to run a script in SQL that will return all active users in Dynamics GP 2010. Please assist me,

Regards
Nish

LikeLike
- Victoria Yudin Reply November 26, 2011 at 7:43 am
  
  Nish,
  
  My SQL View for User Activity in Dynamics GP can help with this.
  
  -Victoria
  
  LikeLike
- Victoria Yudin Reply December 21, 2011 at 6:22 am
  
  Nish,
  
  Typically ‘active users’ means all users that are currently logged into GP. Is your definition something different? If so, can you please explain what your definition of ‘active users’ is?
  
  -Victoria
  
  LikeLike