Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / SQL Update Batch ID fo...
Microsoft Dynamics GP (Archived)

SQL Update Batch ID for Sales Work Documents

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi

I wanted to regularly delete quotes in the Sales Transactions Work and Sales Transactions Amounts Work table that are over 2 months old.

One way I considered is to run an update script to change the batch number and then delete that batch.

update SOP10100 set BACHNUMB = 'OLDQUOTES' where DOCDATE < DATEADD(month, -2, GETDATE())

 

What would happen if I run the update script without the batch already existing? Would it corrupt any data?

Also if you can think of any easier way to automate that would be great.

Thanks

Tania

*This post is locked for comments

  • Suggested answer
    Tim Foster Profile Picture
    Tim Foster 8,515 on at
    RE: SQL Update Batch ID for Sales Work Documents

    Mariano Gomez has a good solution:

    dynamicsgpblogster.blogspot.ca/.../moving-sop-transactions-from-one-batch.html

    Tim

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SQL Update Batch ID for Sales Work Documents

    I have figured this update out. I'm still fairly amateur so this may not be the best way to do it, but it works in the test database.

    First step checks whether the batch already exists -

    use GPSTJ if exists (select BACHNUMB from SY00500 where BACHNUMB = 'DELETEQUOTES')

    Second step queries any quotes over 2 months old, counts the number of transactions and sums the total, groups by batch number. Then takes these values off the current batch numbers they sit in (other transactions may still remain in them)

    update T1
set T1.BCHTOTAL = (T1.BCHTOTAL - T2.BCHTOTAL), T1.NUMOFTRX = (T1.NUMOFTRX - T2.COUNT)
from SY00500 T1
inner join (select BACHNUMB, count(BACHNUMB) as [Count], sum(ACCTAMNT) as [BCHTOTAL] from SOP10100 where SOPTYPE = 1 and DOCDATE < DATEADD(month, -2, GETDATE())group by BACHNUMB) 
T2 on T2.BACHNUMB = T1.BACHNUMB


    Next step adds the count of transactions and sum of all transactions to the new batch DELETEQUOTES

    update SY00500
set NUMOFTRX = (select count(ACCTAMNT) from SOP10100 where SOPTYPE = 1 and DOCDATE < DATEADD(month, -2, GETDATE())), BCHTOTAL = (select sum(ACCTAMNT) as [BCHTOTAL] from SOP10100 where SOPTYPE = 1 and DOCDATE < DATEADD(month, -2, GETDATE()))
where BACHNUMB = 'DELETEQUOTES'


    Then updates the batch number on the transactions table. Then the batch can successfully be deleted.

    update SOP10100 set BACHNUMB = 'DELETEQUOTES' where SOPTYPE = 1 and DOCDATE < DATEADD(month, -2, GETDATE())

    Thanks

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SQL Update Batch ID for Sales Work Documents

    Thanks Leslie, this sounds like a great solution, however we are not interested in keeping the quotes.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SQL Update Batch ID for Sales Work Documents

    Hi Mahmoud, thanks for the response.

    What exactly needs to be updated on the batch master table? I thought by changing the batch number at the transaction level it would move them into this batch?

    Thanks

  • L Vail Profile Picture
    L Vail 65,271 on at
    RE: SQL Update Batch ID for Sales Work Documents

    Good point Mahmoud.

    We run check links regularly on the SOP Work logical file and that does correct those numbers. Perhaps it's just my own laziness of not wanting to calculate the number of documents and change the SY00500 table. It doesn't seem to bother anything being wrong. Meaning that this particular company does not rely on that number for anything. In fact, I don't think any my clients have used that number for anything. Still, to move them properly, the SY00500 table should be corrected as you said.

    Kind regards,

    Leslie

  • Suggested answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: SQL Update Batch ID for Sales Work Documents

    You are supposed to apply the same logic on the batch master table to either update the original batch number as well. Otherwise, your process is incomplete

  • Suggested answer
    L Vail Profile Picture
    L Vail 65,271 on at
    RE: SQL Update Batch ID for Sales Work Documents

    I have a client who wants tokeep their quotes, but want the quotes to move to the history table once they have expired. I came up with a pretty straightforward solution to just monitor the dates and after a spec fix dat is met, all expired quotes are written two th history tables. I've got the  entire script at my blog if you are interested. We have it as a stored procedure that runs according to a schedule. We do not have to anything to get it to work. We have been using it a couple of years now and so far it is delivering just what we want.

    Kind regard,

    Leslie

    Q

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SQL Update Batch ID for Sales Work Documents

    I actually tried this in the test company, but only where I had already 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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SQL Update Batch ID for Sales Work Documents

    I missed the

    and SOPTYPE = 1 in that update

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans