Solved

SQL statement for Great Plains

Posted on 2014-10-02
18
243 Views
Last Modified: 2014-11-03
I need to delete about 4000 erroneous account strings that were set up several years ago. What Great Plains (GP) tables are involved?

I have attached an example of what needs to be deleted if that would help in determining what tables need to be involved
Q--Users-PMS-EE-deletes.xlsx
0
Comment
Question by:pstre
  • 9
  • 8
18 Comments
 
LVL 18

Expert Comment

by:Steve Endow
Comment Utility
Hi,

Here is a pretty good article on deleting GL accounts from Dynamics GP.  Definitely make sure that you check all of the conditions they list before deleting an account (e.g. that it does not have a balance, etc.).

http://support2.microsoft.com/kb/933642

If you have any questions, please let me know.

Thanks,

Steve Endow
Microsoft MVP - Dynamics GP
Dynamics GP Certified IT Professional
0
 
LVL 18

Expert Comment

by:Victoria Yudin
Comment Utility
I wrote this little script to check if the accounts have been used in any GL transactions that may help.
select n.ACTINDX [Account Index], rtrim(n.ACTNUMST) Account, 
rtrim(a.ACTDESCR)[Account Name] 
from GL00105 n
inner join GL00100 a on a.ACTINDX = n.ACTINDX

where n.ACTINDX 
not in 
(select distinct ACTINDX from GL10001
 union 
 select distinct ACTINDX from GL20000 
 union 
 select distinct ACTINDX from GL30000) 

Open in new window


If the accounts were set up and never used and you can confirm it, then you can delete the accounts from the GL00100 table and the GL00105 table in SQL. Then run check links on the financial series in GP.
0
 

Author Comment

by:pstre
Comment Utility
Thanks, Victoria.. This helped in determining that we have a lot of accounts not being used

I have an excel file with the accounts that need to be deleted. How would I use this file in SQL to delete the records?  What would the script look like?
0
 
LVL 18

Expert Comment

by:Victoria Yudin
Comment Utility
Pamela,

Before you do anything, please make sure you have a backup and that all users are out of GP! :-)

What I typically do is write a little formula in Excel to get all the values in a comma delimited list. You might be better off using the account index, so let's say you had an excel file where the index was in column A. In a different column, write the following formula:
=A1&","

Open in new window


and fill that down for the entire column. For example:
excel.png
Now you can use the following script in SQL and copy your list with the commas from Excel inside the parentheses. Make sure to delete the comma after the last value:
delete from GL00100 where ACTINDX in (
--copy your list from Excel with the commas here and delete the comma on the last line
)

Open in new window


With the example I gave of the Excel file, my script would look like this:
delete from GL00100 where ACTINDX in (
1,
2,
5,
11,
13
)

Open in new window


You can then change the GL00100 to GL00105 and re-run the script. Don't forget about Check Links in GP.

Hope that helps.
0
 
LVL 18

Accepted Solution

by:
Victoria Yudin earned 500 total points
Comment Utility
Pamela, one other note. If you have allocation accounts set up, you might want to change the original script I gave you to the following:

select n.ACTINDX [Account Index], rtrim(n.ACTNUMST) Account, 
rtrim(a.ACTDESCR)[Account Name] 
from GL00105 n
inner join GL00100 a on a.ACTINDX = n.ACTINDX

where n.ACTINDX 
not in 
(select distinct ACTINDX from GL10001 where ACCTTYPE = 1
 union 
 select distinct ACTINDX from GL20000 where ACCTTYPE = 1
 union 
 select distinct ACTINDX from GL30000 where ACCTTYPE = 1) 

Open in new window


This will exclude anything except posting accounts.
0
 

Author Comment

by:pstre
Comment Utility
Victoria, I ran the scripts (just deleted 2 accounts to see if it worked) and the accounts are still showing in Financial\Accounts.  I ran Checklinks but which ones do I need to select.  I can't select all under Financial because it will change our GL setup.
0
 
LVL 18

Expert Comment

by:Victoria Yudin
Comment Utility
Pamela,

Can you please post the scripts you ran so I can take a look?

Thanks.
0
 
LVL 18

Expert Comment

by:Victoria Yudin
Comment Utility
Also, what will change your GL setup? Do you have something custom going on? Normally you should be able to run check links on everything under the Financial series (really, under any series) with no problem.
0
 

Author Comment

by:pstre
Comment Utility
It works... Victoria, you are the "bomb."  Thank you so much!!
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Closing Comment

by:pstre
Comment Utility
Victoria is the bomb!!
0
 
LVL 18

Expert Comment

by:Victoria Yudin
Comment Utility
LOL...you are welcome! :-)
0
 

Author Comment

by:pstre
Comment Utility
Victoria, when I ran the Fixed Allocation accounts query, I get the exact same # of lines as I did before I included the posting type =1
0
 
LVL 18

Expert Comment

by:Victoria Yudin
Comment Utility
Sorry, what is the "Fixed Allocation accounts query"?
0
 

Author Comment

by:pstre
Comment Utility
The script above that you gave me to exclude allocation accounts.  We only have fixed allocation accounts
0
 
LVL 18

Expert Comment

by:Victoria Yudin
Comment Utility
Sorry, do you mean the script is picking up your allocation accounts as having no activity? If so, here is a slightly different version of the script:

select n.ACTINDX [Account Index], rtrim(n.ACTNUMST) Account, 
rtrim(a.ACTDESCR)[Account Name] 
from GL00105 n
inner join GL00100 a on a.ACTINDX = n.ACTINDX and a.ACCTTYPE = 1 

where n.ACTINDX 
not in 
(select distinct ACTINDX from GL10001
 union 
 select distinct ACTINDX from GL20000
 union 
 select distinct ACTINDX from GL30000) 

Open in new window

0
 

Author Comment

by:pstre
Comment Utility
Thanks.
0
 

Author Comment

by:pstre
Comment Utility
Hopefully, last question!!  Just found out from the GL team that we need to exclude the accounts that are tied to the budget ids.  Is there a way to exclude these accounts?
0
 
LVL 18

Expert Comment

by:Victoria Yudin
Comment Utility
Yep, add a the following line at the very end:

 and n.ACTINDX not in (select ACTINDX from GL00201)

Open in new window

0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now