This script is the result of a support call logged by a client where the incorrect value on sales invoices was being invoiced to customers. There was a small number of invoices being created which were showing the incorrect value; there was concern that the issue might be wider than thought, so I wrote this script to verify the sum of the Extended Cost of the lines on an invoice against the Subtotal.
This script is configured to check invoices, but could be used against other transaction types if the highlighted section is changed.
IF OBJECT_ID (N'uv_AZRCRV_CompareEXTDCOSTAgainstSubtotal', N'V') IS NOT NULL
DROP VIEW uv_AZRCRV_CompareEXTDCOSTAgainstSubtotal
GO
CREATE VIEW uv_AZRCRV_CompareEXTDCOSTAgainstSubtotal AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
['Sales Transaction Work'].SOPNUMBE
,FORMAT(['Sales Transaction Work'].DOCDATE, 'yyyy-MM-dd') AS DOCDATE
,['Sales Transaction Amounts Work'].XTNDPRCE
,['Sales Transaction Work'].SUBTOTAL
,'OPEN' AS TRXSTATUS
FROM
SOP10100 AS ['Sales Transaction Work']
INNER JOIN
(SELECT
SOPNUMBE
,SOPTYPE
,SUM(XTNDPRCE) AS XTNDPRCE
FROM
SOP10200
WHERE
SOPTYPE = 3 --invoice
GROUP BY
SOPNUMBE,SOPTYPE) AS ['Sales Transaction Amounts Work']
ON
['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE
AND
['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE
WHERE
['Sales Transaction Amounts Work'].XTNDPRCE <> ['Sales Transaction Work'].SUBTOTAL
UNION ALL
SELECT
['Sales Transaction History'].SOPNUMBE
,FORMAT(['Sales Transaction History'].DOCDATE, 'yyyy-MM-dd') AS DOCDATE
,['Sales Transaction Amounts History'].XTNDPRCE
,['Sales Transaction History'].SUBTOTAL
,'HIST' AS TRXSTATUS
FROM
SOP30200 AS ['Sales Transaction History']
INNER JOIN
(SELECT
SOPNUMBE
,SOPTYPE
,SUM(XTNDPRCE) AS XTNDPRCE
FROM
SOP30300
WHERE
SOPTYPE = 3 --invoice
GROUP BY
SOPNUMBE,SOPTYPE) AS ['Sales Transaction Amounts History']
ON
['Sales Transaction Amounts History'].SOPNUMBE = ['Sales Transaction History'].SOPNUMBE
AND
['Sales Transaction Amounts History'].SOPTYPE = ['Sales Transaction History'].SOPTYPE
WHERE
['Sales Transaction Amounts History'].XTNDPRCE <> ['Sales Transaction History'].SUBTOTAL
GO
GRANT SELECT ON uv_AZRCRV_CompareEXTDCOSTAgainstSubtotal TO DYNGRP
GO
The view can easily be plugged into SmartList Designer, SmartList Builder, a refreshable Excel Report, a SQL Server Reporting Services report or any other type of reporting tool.
Can ISC Software help?
ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you would like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.
3 thoughts on “SQL Script To Verify Sales Invoice Extended Cost Against Subtotal”