SQL Calculate Customer Balance & On Order amount for Dynamics GP

The values in the Customer summary table RM00103 keep drifting from what they should be. This causes issues with our pick list printing service that was using them in a complex credit checking routine. Sometimes the on order amount could be minus thousands of pounds, leading to goods being despatched when they shouldn’t.

For the way we use GP, the following script is adequate to compare the values in the tables with the transactions in GP. We don’t use receivable invoices but this could easily be added in using table IVC10100.

So on freshly reconciled company the following script returns no rows, when the values go out of line a SQL job emails the row to us, in order to try and ascertain what just happened somewhere in GP.

I post the SQL here merely as a starting point for others looking at customer balances or outstanding transactions against customers in GP with SQL as I didn’t see much from web searches on that subject. I strongly urge you to test the query first on your system.

;WITH CTE_OrderSummary AS(
SELECT CUSTNMBR, SUM(REMSUBTO) ONORDAMNTCALC FROM SOP10100
WHERE (SOPTYPE=2 OR SOPTYPE=3)
AND VOIDSTTS=0
GROUP BY CUSTNMBR
),
CTE_RM_Summary AS(
SELECT 
CUSTNMBR,SUM(CASE WHEN RMDTYPAL IN (0,1,3,4,5,6) THEN  
       CURTRXAM ELSE -1*CURTRXAM END)AS CALCBAL 
FROM RM20101
WHERE 
VOIDSTTS=0
GROUP BY CUSTNMBR)

SELECT RM00103.CUSTNMBR, 
CUSTBLNC, 
ISNULL(CALCBAL,0) AS CALCBAL,
ONORDAMT, 
ISNULL(ONORDAMNTCALC,0) AS ONORDAMNTCALC,
'Summary information differs from calc value for balance or on order amount'

FROM RM00103
LEFT JOIN CTE_OrderSummary ON RM00103.CUSTNMBR=CTE_OrderSummary.CUSTNMBR
LEFT JOIN CTE_RM_Summary ON RM00103.CUSTNMBR=CTE_RM_Summary.CUSTNMBR
where
(
ISNULL(ONORDAMNTCALC,0)!=ONORDAMT
OR
ISNULL( CALCBAL,0)!=CUSTBLNC
)