SQL view to verify unposted SOP transaction totals in Dynamics GP

Over the years we have seen a number of situations where a sales transaction total will be calculated incorrectly. It does not happen often and we’ve never been able to reproduce this on demand, so it’s very difficult to track down the cause for this. I have also seen a number of newsgroup posts talking about this, a new one pops up probably once a year, but since it’s so random and hard to pin down, there are no answers.

I will say that the times I have seen this are often on transactions with a lot of items or when many changes have been made. It’s also more frequent on very busy networks or on what I typically consider ‘servers that could use some help’, ones where we often see posting interruptions or issues.

While there may be no ready fix for this, if this is something that you see happening periodically, one option may be to check your SOP transactions for this issue prior to invoicing customers and posting the transactions. Once posted, this is much more difficult to address and, of course, if you’ve already sent an invoice to a customer it looks bad to come back with a ‘corrected’ one. The view below will show you any transactions where the sum of the extended prices of an unposted SOP transaction does equal the subtotal on the header of the transaction.

Here is a sample of the results (please click the image to see a bigger version):

The transactions with the few cent differences may be fine – that could be multicurrency or rounding issues. However, line 6 with the 49.90 difference is definitely an issue and should be fixed. Usually you can fix this by running the Reconcile – Remote Sales Documents utility for the transaction in question.

Additional resources:

• Sales Order Processing (SOP) commonly used tables
• Sales Order Processing (SOP) SQL views
• Other GP Reporting links

Here is the code to create the view:

create view view_verify_SOP_totals
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- Created September 12, 2014 by Victoria Yudin
-- Flexible Solutions, Inc.
-- For other code, please visit http://victoriayudin.com
-- Compares extended price from all line items to subtotal
--   on transaction header for all unposted SOP transactions
--   only returns transactions with an issue
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
h.SOPTYPE SOP_Type,
h.SOPNUMBE SOP_Number,
h.DOCDATE Doc_Date,
h.SUBTOTAL Doc_Subtotal,
sum(d.XTNDPRCE) Sum_of_Extended_Prices,
h.SUBTOTAL - sum(d.XTNDPRCE) [Difference]

from SOP10100 h
inner join SOP10200 d
     on h.SOPTYPE = d.SOPTYPE and h.SOPNUMBE = d.SOPNUMBE

group by h.SOPTYPE, h.SOPNUMBE, h.DOCDATE, h.SUBTOTAL
having h.SUBTOTAL <> sum(d.XTNDPRCE)

go
grant select on view_verify_SOP_totals to DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.