Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP (Archived)

Void Orders in GP

(0) ShareShare
ReportReport
Posted on by 80

I ran the following query to determine open orders that should be voided.  After reviewing and approving them, we need to know how we can void these orders using SQL.  We have over 30K SOPNUMBE order #'s over the years in this results set.

SELECT distinct ca.custname, ca.CUSTNMBR, datediff(day,getdate(),oh.ReqShipDate) Age,

oh.ReqShipDate,oh.SOPNUMBE,

ca.HOLD AcctHold,

oh.SUBTOTAL

from  RM00101 ca join SOP10100 oh on ca.CUSTNMBR = oh.CUSTNMBR

where ca.INACTIVE =0 AND oh.SOPTYPE=2 and oh.voidstts = 0

and oh.reqshipdate<(getdate()-90)

and not exists

(--order is not yet in the history table

select distinct b.* from sop10200 b where b.sopnumbe = oh.sopnumbe and b.soptype = 2 and b.qtyfulfi > 0

)

Thank you in advance.

Data Analyst

GP Novice

*This post is locked for comments

  • MrsWatkins2u Profile Picture
    MrsWatkins2u 80 on at
    RE: Void Orders in GP

    Thank you so much!  I originally ran this scrip to assess orders with unfilled line items for active customers on hold and waiting to be filled; not expecting to find this breadth of issues... I will rerun my script against the history to be sure nothing unexpected is there.

  • Verified answer
    Tristan Clores Profile Picture
    Tristan Clores 2,812 on at
    RE: Void Orders in GP

    Hi,

    Voiding a SOP Order is not that simple.  

    If you do this within GP, the system will mark the transaction with a SLSVTXXXX value in the TRXSORCE field.  

    I think it will be best to just cancel the remaining quantities of the line items involved of each SOP Order.  You can use the script below as a guide:

    update SOP10200 set QTYREMAI = 0, QTYTOINV = 0, QTYFULFI = 0, ATYALLOC = 0, QTYSLCTD = 0, QTYCANCE = X where SOPNUMBE =  'Enter Order Here'

    **where X is the quantity

    Once done, you can run Reconcile - Remove Sales Document (Remove Completed Documents) to move the transactions from WORK to History.

  • Verified answer
    KirkLivermont Profile Picture
    KirkLivermont 5,985 on at
    RE: Void Orders in GP

    I don't recall is the void status is stored in more than one of these tables. You might want to run a select statement against most of the SOP1***** tables using the SOP Number of a couple of the invoices you want to void.  

    I reread your original statement and am not seeing a reference to the SOP30200 tables that hold posted sales transactions. An excellent reference for GP SOP tables is here victoriayudin.com/.../sop-tables.  I think you may want to look at the SOP10100 and the SOP30100 instead of the RM00101 table.

  • MrsWatkins2u Profile Picture
    MrsWatkins2u 80 on at
    RE: Void Orders in GP

    Thank you.  If it weren't for the neglect in keeping the table clean and current, I would not even consider this route.  What about the lines on the orders?  Do I need to update the SOP10200 table as well, ie, quantities. I get concerned since GP uses 10 tables to create an invoice so I want to be sure it does not require updating more than the one table to aptly void an order and its order lines.

  • Suggested answer
    KirkLivermont Profile Picture
    KirkLivermont 5,985 on at
    RE: Void Orders in GP

    It would be better to void these transactions using GP but because of the number of transactions I can understand why you want to do this through SQL. I believe you should be able to update the void status (VOIDSTTS) for these transactions to 1.

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

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,371 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans