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.
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
3 thoughts on “SQL Script To Verify Sales Invoice Extended Cost Against Subtotal”