Rounding SOP MultiCurrency invoices to two decimal places

Recently a client asked us to round their sop documents to two decimals. Dynamics is set up in 5 decimal places because they sell widgets (small items). Also, the client uses Multicurrency so we have to deal with that.

This article includes the script that does the job in both the SOP document and the distributions

Related Articles

... and you 'll find more on the SQL (Dynamics) Menu

I have a caveat before we begin - if the user goes into one of these documents and changes something that causes the document to retotal... all bets are off. This logic depends on the user not editing the SOP document

IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE  name = N'dd_SOPRound'
       AND    type = 'P')
    DROP PROCEDURE dd_SOPRound
GO
  
CREATE PROCEDURE dd_SOPRound
  
as
set nocount on
set transaction isolation level read uncommitted
 
--===========================================================================================
-- originating currency
--===========================================================================================
select  h.SOPNUMBE,
        h.SOPTYPE,
        ORDOCAMT - ROUND(ORDOCAMT,2,1) as Diff,
        d.Dex_Row_ID
    INTO #oTMP
    from SOP10100 h
        join (select sopnumbe, soptype, MAX(DEX_ROW_ID) as Dex_Row_ID from SOP10102 where DISTTYPE = 1 group by sopnumbe, soptype) d on d.sopnumbe = h.sopnumbe and d.soptype = d.soptype
    where ORDOCAMT <> ROUND(ORDOCAMT,2,1)
        and h.soptype = 3
        and h.VOIDSTTS = 0
    order by 1
 
update SOP10100 set ORDOCAMT = ORACTAMT - t.Diff, ORACTAMT = ORACTAMT - t.Diff, ORSUBTOT = ORSUBTOT - t.Diff
    from SOP10100 h
        join #oTMP t on t.SOPNUMBE = h.SOPNUMBE and t.SOPTYPE = h.SOPTYPE
 
update SOP10102 set ORDBTAMT = ORDBTAMT - t.Diff
    from SOP10102 h
        join #oTMP t on t.SOPNUMBE = h.SOPNUMBE and t.SOPTYPE = h.SOPTYPE
    where DISTTYPE = 2
 
update SOP10102 set  ORCRDAMT =  ORCRDAMT - t.Diff
    from SOP10102 h
        join #oTMP t on t.SOPNUMBE = h.SOPNUMBE and t.SOPTYPE = h.SOPTYPE
    where h.DEX_ROW_ID = t.Dex_Row_ID
                 
--===========================================================================================
-- functional currency
--===========================================================================================
select  h.SOPNUMBE,
        h.SOPTYPE,
        h.DOCAMNT - ROUND(DOCAMNT,2,1) as Diff,
        d.Dex_Row_ID
    INTO #TMP
    from SOP10100 h
        join (select sopnumbe, soptype, MAX(DEX_ROW_ID) as Dex_Row_ID from SOP10102 where DISTTYPE = 1 group by sopnumbe, soptype) d on d.sopnumbe = h.sopnumbe and d.soptype = d.soptype
    where DOCAMNT <> ROUND(DOCAMNT,2,1)
        and h.soptype = 3
        and h.VOIDSTTS = 0
    order by 1
 
update SOP10100 set DOCAMNT = DOCAMNT - t.Diff, ACCTAMNT = ACCTAMNT - t.Diff, SUBTOTAL = SUBTOTAL - t.Diff
    from SOP10100 h
        join #TMP t on t.SOPNUMBE = h.SOPNUMBE and t.SOPTYPE = h.SOPTYPE
 
update SOP10102 set DEBITAMT = DEBITAMT - t.Diff
    from SOP10102 h
        join #TMP t on t.SOPNUMBE = h.SOPNUMBE and t.SOPTYPE = h.SOPTYPE
    where DISTTYPE = 2
 
update SOP10102 set CRDTAMNT = CRDTAMNT - t.Diff
    from SOP10102 h
        join #TMP t on t.SOPNUMBE = h.SOPNUMBE and t.SOPTYPE = h.SOPTYPE
    where h.DEX_ROW_ID = t.Dex_Row_ID
 
go
  
grant exec on dd_SOPRound to public

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences