Skip to main content

Notifications

Announcements

New Dynamics 365 Community Call! (ERP Edition)
News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP forum / Void Orders in GP
Microsoft Dynamics GP forum
Answered

Void Orders in GP

(0) ShareShare
ReportReport
Posted on by MrsWatkins2u 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

  • 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.

Helpful resources

News and Announcements

New Dynamics 365 Community Call! (ERP Edition)

Quick Links

Dynamics 365 Community Update

Welcome to the inaugural Community Platform Update. As part of our commitment to…

Dynamics 365 Community Newsletter - August 2024

Catch up on the latest D365 Community news

Community Spotlight of the Month

Kudos to Mohana Yadav!

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,107 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 227,954 Super User 2024 Season 2

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans