Share
Report*This post is locked for comments
You’re offline. This is a read only version of the page.
55
*This post is locked for comments
Thanks Leslie. I have actually figured out a script and successfully tested it that takes the document count and totals off of the old batches, and then updates the new batch.
-- Update old batch totals
update T1
set T1.BCHTOTAL = (T1.BCHTOTAL - T2.BCHTOTAL), T1.NUMOFTRX = (T1.NUMOFTRX - T2.COUNT)
from GPSTJ..SY00500 T1
inner join (select BACHNUMB, count(BACHNUMB) as [Count], sum(ACCTAMNT) as [BCHTOTAL] from GPSTJ..SOP10100 where SOPTYPE = 1 and DOCDATE < DATEADD(month, -2, GETDATE())group by BACHNUMB)
T2 on T2.BACHNUMB = T1.BACHNUMB
-- Update new batch totals
update GPSTJ..SY00500
set NUMOFTRX = (select count(ACCTAMNT) from GPSTJ..SOP10100 where SOPTYPE = 1 and DOCDATE < DATEADD(month, -2, GETDATE())),
BCHTOTAL = (select sum(ACCTAMNT) from GPSTJ..SOP10100 where SOPTYPE = 1 and DOCDATE < DATEADD(month, -2, GETDATE()))
where BACHNUMB = 'DELETEQUOTES'
-- Change Batch ID on Transactions
update GPSTJ..SOP10100 set BACHNUMB = 'DELETEQUOTES' where SOPTYPE = 1 and DOCDATE < DATEADD(month, -2, GETDATE())
I'm not sure how to post code in here so sorry about the formatting.
Thanks
Hi,
The problem is that the total transactions field in the sy00500 table is not being updated. If you are OK with modifying it in SQL you could just change it to something other than zero. It doesn't need to be correct, it just cannot be zero. I think if you run check links on the Sales Work transactions (not the exact name, do not have GP on this iPad) it will update the batches with the correct totals.
Kind regards,
Leslie
Try the script found here:
dynamicsgpblogster.blogspot.ca/.../moving-sop-transactions-from-one-batch.html
Tim
Hi Leslie, We don't want to keep the quotes. I have done what Richard Wheeler suggested above, but whilst it updates the batch field it doesn't make them available in the batch to delete. I'm still unsure how to resolve this.
Thanks
Hi, I ran the following update script in the test company -
update SOP10100 set BACHNUMB = 'OLDQUOTES' where DOCDATE < DATEADD(month, -2, GETDATE()) and SOPTYPE = 1
I created the batch first. When you open a Quote in GP Transaction Entry window, the batch field has updated to OLDQUOTES, but if you open Sales Batches and OLDQUOTES, it says No Transactions, and nothing deletes?
Thanks
You could run a SQL script on the SOP10100 table deleting SOPTYPE 1. You'd want to backup the database before doing this and after deleting the records, run Checklinks on the SOP Work table to delete records from associated tables.
In that case, I would write a little SQL script to look for any documents in the SOP10100 table where SOPTYPE=1 and change the BACHNUMB field to QUOTES. This will move all quotes into a batch called QUOTES. Then go back into GP and delete the batch and they will all go away.
Thanks Richard...our orders are transferred to invoices and posted. So I guess the problem is that the quotes remain unposted, which unfortunately never puts them history for mass deletion. We recently updated and I had hoped that there maybe a fix for this issue with the quotes. It looks like I will have to transfer the current quotes into a new batch and than delete all the old remaining.
Can you put them all into the same sale batch and then delete the batch?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (