Solved

SQL statement for Great Plains

Posted on 2014-10-02
18
272 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
ID: 40358627
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
ID: 40358865
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
ID: 40359223
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
ID: 40359240
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
ID: 40359243
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
ID: 40360101
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
ID: 40360106
Pamela,

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

Thanks.
0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 40360142
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
ID: 40360382
It works... Victoria, you are the "bomb."  Thank you so much!!
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Closing Comment

by:pstre
ID: 40360383
Victoria is the bomb!!
0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 40360384
LOL...you are welcome! :-)
0
 

Author Comment

by:pstre
ID: 40419909
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
ID: 40419921
Sorry, what is the "Fixed Allocation accounts query"?
0
 

Author Comment

by:pstre
ID: 40420241
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
ID: 40420284
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
ID: 40420313
Thanks.
0
 

Author Comment

by:pstre
ID: 40420346
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
ID: 40420360
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 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

21 Experts available now in Live!

Get 1:1 Help Now