3d small people - negative to positive

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):

verify SOP totals

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:

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.

5 Responses to “SQL view to verify unposted SOP transaction totals in Dynamics GP”

  1. Victoria, thank you for this post. This will help a lot of our clients, while Microsoft hopefully digs in to the real cause and solution. You are awesome!

    Like

Trackbacks/Pingbacks

  1. SQL view to verify unposted SOP transaction totals in Dynamics GP – 9/12, Victoria Yudin | - September 19, 2014

    […] Continue reading on Source Blog […]

    Like

  2. SQL view to verify unposted SOP transaction totals in Dynamics GP | Victoria Yudin - Microsoft Dynamics GP Community - September 15, 2014

    […] Victoria Yudin offers a SQL view to verify unposted SOP transaction totals in Dynamics GP  […]

    Like

  3. DynamicAccounting.net | SQL view to verify unposted SOP transaction totals in Dynamics GP | Victoria YudinSQL view to verify unposted SOP transaction totals in Dynamics GP | Victoria Yudin - DynamicAccounting.net - September 15, 2014

    […] Victoria Yudin offers a SQL view to verify unposted SOP transaction totals in Dynamics GP  […]

    Like

  4. SQL view to verify unposted SOP transaction totals in Dynamics GP : Interesting Findings & Knowledge Sharing - September 12, 2014

    […] Continue reading here: SQL view to verify unposted SOP transaction totals in Dynamics GP […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: