Solved

SQL statement for Great Plains

Posted on 2014-10-02
18
301 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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
 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

820 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