500
Morning,
I am trying to move transactions (INV and PMT) from one account to another account in GP. I merged two accounts which should not have been merged, and need to take a few transactions from one account and put it back to its original account. Can this be done through interface of GP Dynamics?
Thanks in advance!
*This post is locked for comments
Yes, the PM30300 would be the Apply to History table.
gallery.technet.microsoft.com/.../c0c57332-8624-48c0-b4c3-5b31fe641c58
I often use this script to find all tables that contain a string. You could search for 'BRI061' and you will see all the tables and decide which records need to be updated.
update PM30200
SET VENDORID='BRI516'
Where VENDORID='BRI061'and VCHRNMBR='00032'
Developed this and Ran this off in the Test today. Ran it over History tables and checked the interface on GP dynamics (all in test environment). The transactions seem to have moved back!
All the transactions are HISTORY only, no opens, no work. So I'm assuming I only need to execute over PM30300 and PM30300
So all seems to look well, just have to run in test a few more times than will execute in live.
Thanks Richard!
where VCHRNMBR equals XXXXXX change (update?) VENDORID to BAS002
would this line of script be correct?
Thank you for all your help Richard, very much appreciated!
Regards,
Reece
Yes, but beware there could be up to four tables involved here:
Payables Work Table
Payables Open Table
Payables Transaction History
Payables Apply Table
and there are probably others.
So could the solution be now to just execute a query in SQL to change the VENDORID against VCHRNMBER?
Regards,
Reece
Richard,
I have gone back into the Test Company and via "Transaction by Creditor" I can see all the voucher Numbers for each transaction against an original account (before anything was merged) E.g. where in the "Live Company" I have merged BRI001 and BRI061 and all the transactions for both accounts are now in BRI061, I can go into test and see all the original voucher numbers for BRI001. I have checked LIVE and yes, these are all in BRI061 with the same details, just that they are under BRI061.
I believe that nothing needs to be changed in the GL tables as these currently reconcile, the GL allocation is coming from the Item, not the Vendor. Does this make it simpler to rectify?
Thanks Richard,
Regards,
Reece
OK, so when you say account you mean vendor ID not GL account? If so, we can forget about the ACTINDX. Now we get into the payables transaction open, history and apply tables. You can do that to the open and history tables but take care with the apply table. In the apply table you have a payment number, then the invoice to which it was applied with the vendor ID. After we make this correction we need to reconcile AP to correct the vendor balances and aging. Now this assumes that the expense account on these two vendors was the same. If not, then we get back to the ACTINDX again in addition to the vendor ID. This can all be done but again after making a backup. You would need to put the vendor back in and then we run the scripts. Do you know the voucher number for the cutoff?
Richard,
I have just had a conversation with my superior and believe I had been misleading, I apologize. I have merged two Creditors in GP Dynamics in purchasing rather than accounts in financial. E.g. I have merged BAS002 and BAS003 through the Vendor Combiner in professional tools. So now I am left with BAS003 with a mix of transactions from itself BAS003 and BAS002. This is what I am trying to reverse.
Could I run a script in SQL along the lines of "where VCHRNMBR equals XXXXXX change VENDORID to BAS002" to try and move transaction back to the original account.
Is there a ACTINDX for each transaction in a database? Or just one for a series under an account?
First, you need to know the ACTINDX of the GL account that remained and the ACTINDX of the account that was merged. Then you will need to update records in the GL20000 and GL30000 tables. How many journal entries are we talking about here. If it is only a few, I would start by adding the GL account number back in and then get the ACTINDX value for that account. You could then do J/E for the transactions that need to be switched back. Now if you can restore from backup to a TEST company we could find all the old transactions using the old ACTINDX value and then update your production database. Now whatever you wind up doing make sure you do this after hours and after making a backup. If you can tell me the names of your production database and test database I could come up with something. After you split them you will need to run GL reconcile on all your years, both open and historical. Now this is highly irregular and I make no guarantees but it should work. This also assume the merging tool did not delete the original records and all it did what change the value of the ACTINDX field in the GL20000 and GL30000 tables.
#1
André Arnaud de Cal...
291,711
Super User 2024 Season 2
#2
Martin Dráb
230,458
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
