Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

SQL statement for Great Plains

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
pstre
Asked:
pstre
  • 9
  • 8
1 Solution
 
Steve EndowMicrosoft MVP - Dynamics GPCommented:
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
 
Victoria YudinCommented:
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
 
pstreAuthor Commented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Victoria YudinCommented:
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
 
Victoria YudinCommented:
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
 
pstreAuthor Commented:
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
 
Victoria YudinCommented:
Pamela,

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

Thanks.
0
 
Victoria YudinCommented:
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
 
pstreAuthor Commented:
It works... Victoria, you are the "bomb."  Thank you so much!!
0
 
pstreAuthor Commented:
Victoria is the bomb!!
0
 
Victoria YudinCommented:
LOL...you are welcome! :-)
0
 
pstreAuthor Commented:
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
 
Victoria YudinCommented:
Sorry, what is the "Fixed Allocation accounts query"?
0
 
pstreAuthor Commented:
The script above that you gave me to exclude allocation accounts.  We only have fixed allocation accounts
0
 
Victoria YudinCommented:
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
 
pstreAuthor Commented:
Thanks.
0
 
pstreAuthor Commented:
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
 
Victoria YudinCommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now