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
pstreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 YudinOwner / Dynamics GP ConsultantCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Victoria YudinOwner / Dynamics GP ConsultantCommented:
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 YudinOwner / Dynamics GP ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 YudinOwner / Dynamics GP ConsultantCommented:
Pamela,

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

Thanks.
0
Victoria YudinOwner / Dynamics GP ConsultantCommented:
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 YudinOwner / Dynamics GP ConsultantCommented:
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 YudinOwner / Dynamics GP ConsultantCommented:
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 YudinOwner / Dynamics GP ConsultantCommented:
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 YudinOwner / Dynamics GP ConsultantCommented:
Yep, add a the following line at the very end:

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

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.