Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP forum
Unanswered

Sales Tax Order Detail Rounding Issue when using taSopHdrIvcInsert with CALCULATETAXES Set to 1

Posted on by Microsoft Employee

Good Day All,

I have a client that is using the taSopHdrIvcInsert eConnect call to modify orders in an external system (yikes...I know). That said, they are running into a scenario where the taxes calculated on the line level are coming out with a rounding error. Now, I am aware that the tax engine in GP actually calculates on a per line item level. When you initially create the Order, there is only one entry in the SOP10105 table. It has a LNITMSEQ of 0 and contains the correct tax value. To put some feet on this, please see below:

Trade.png

Upon initial entry, the only entry in the SOP10105 table is 

SOPNUMBE LNITMSEQ STAXAMT FRTTXAMT TXDTOTTX
EDI0001661539 0 9.30000 0.00000 0.00000

This is VERY odd to me as the freight is not broken out correctly since this should be $0.07, but that is likely my lack of knowledge here.

Alright, here's where the fun comes in (I promise this problem isn't as boring as it sounds ^_^. When I run the following eConnect and tell the SOP to recalculate taxes using CREATETAXES=1 and even attempting USINGHEADERLEVELTAXES=1 then it does so, however, it calculates the rounding up by 1 penny. I won't break this down, but I understand why it is doing that since it adds up the tax per item as opposed to calculating on the amount. Oddly, when running the following code (specifically look at the last chunk where I tell it to update taxes, it actually then gives 4 entries in the SOP10105 table (as opposed to JUST one entry with tax on the entire transaction).  This is then what is in that table after I run this:

SOPNUMBE LNITMSEQ STAXAMNT FRTTXAMT TXDTOTTX
EDI0001661539         0 9.24000 0.07000 9.31000
EDI0001661539         16384 0.59000 0.00000 0.59000
EDI0001661539         32768 8.65000 0.00000 8.65000
EDI0001661539         2147483646 0.00000 0.07000 0.07000

Here is my code for running this eConnect proc in SQL directly.

-------------------------------------------------------------------------------------


DECLARE @_iErrorState int
DECLARE @_ErrString varchar(255)


exec [dbo].[taSopLineIvcInsert]

@I_vSOPTYPE ="2",
@I_vSOPNUMBE ="EDI0001661539",
@I_vCUSTNMBR ="SNOWJ01",
@I_vDOCDATE ="4/12/2027",
@I_vLOCNCODE ="NJ_305VET",
@I_vITEMNMBR ="SJBLZD",
@I_vUNITPRCE ="8.95",
@I_vXTNDPRCE ="8.95",
@I_vQUANTITY ="1",
@I_vMRKDNAMT ="0",
@I_vCOMMENT_4 ="EDI0001661312",
@I_vITEMDESC ="TESTING",
--@I_vTAXAMNT ="0.59", --commented out because it doesn't matter since we are using CREATETAXES=1
@I_vLNITMSEQ ="16384",
@I_vDOCID ="EDI",
@I_vSALSTERR ="EMPLOYEES",
@I_vSLPRSNID ="E_SJ",
@I_vTAXSCHID ="NJ",
@I_vShipToName ="SnowJoe.com - (DS PPD)",
@I_vADDRESS1 ="305 VETERANS BOULEVARD",
@I_vCITY ="CARLSTADT",
@I_vSTATE ="NJ",
@I_vZIPCODE ="07072",
@I_vCOUNTRY ="United States of America",
@I_vPHONE1 ="8667669563",
@I_vReqShipDate ="12/11/2027",
--@I_vSHIPMTHD ="PPD_GROUND_RES",
@I_vQTYFULFI ="0",
@I_vUpdateIfExists ="1",
@I_vCURNCYID ="USD",
@I_vUOFM ="EACH",
@O_iErrorState = @_iErrorState OUTPUT,
@oErrString = @_ErrString OUTPUT
SELECT @_iErrorState as errstat2 , @_ErrString as errstring2 -- 0 errstat means all ok


exec [dbo].[taSopLineIvcInsert]

@I_vSOPTYPE ="2",
@I_vSOPNUMBE ="EDI0001661539",
@I_vCUSTNMBR ="SNOWJ01",
@I_vDOCDATE ="4/12/2027",
@I_vLOCNCODE ="NJ_305VET",
@I_vITEMNMBR ="SPX3000",
@I_vUNITPRCE ="131.49",
@I_vXTNDPRCE ="131.49",
@I_vQUANTITY ="1",
@I_vMRKDNAMT ="0",
@I_vCOMMENT_4 ="EDI0001661312",
@I_vITEMDESC ="TESTING",
--@I_vTAXAMNT ="8.65", --commented out because it doesn't matter since we are using CREATETAXES=1
@I_vLNITMSEQ ="32768", --@I_vLNITMSEQ ="32768",
@I_vDOCID ="EDI",
@I_vSALSTERR ="EMPLOYEES",
@I_vSLPRSNID ="E_SJ",
@I_vTAXSCHID ="NJ",
@I_vShipToName ="SnowJoe.com - (DS PPD)",
@I_vADDRESS1 ="305 VETERANS BOULEVARD",
@I_vCITY ="CARLSTADT",
@I_vSTATE ="NJ",
@I_vZIPCODE ="07072",
@I_vCOUNTRY ="United States of America",
@I_vPHONE1 ="8667669563",
@I_vReqShipDate ="12/11/2027",
--@I_vSHIPMTHD ="PPD_GROUND_RES",
@I_vQTYFULFI ="0",
@I_vUpdateIfExists ="1",
@I_vCURNCYID ="USD",
@I_vUOFM ="EACH",
@O_iErrorState = @_iErrorState OUTPUT,
@oErrString = @_ErrString OUTPUT
SELECT @_iErrorState as errstat2 , @_ErrString as errstring2 -- 0 errstat means all ok

exec [dbo].[taSopHdrIvcInsert]

@I_vSOPTYPE ="2",
@I_vDOCID ="EDI",
@I_vSOPNUMBE ="EDI0001661539",
@I_vTAXSCHID ="NJ",
@I_vFRTSCHID ="NJ",
@I_vMSCSCHID ="NJ",
--@I_vSHIPMTHD ="PPD_GROUND_RES",
@I_vTAXAMNT ="9.30",
@I_vUSINGHEADERLEVELTAXES ="1",
@I_vCREATETAXES ="1",
@I_vLOCNCODE ="NJ_305VET",
@I_vDOCDATE ="12/19/2018",
@I_vFREIGHT ="1.00",
@I_vTRDISAMT ="1.00",
@I_vCUSTNMBR ="SNOWJ01",
@I_vCUSTNAME ="SnowJoe.com - DS PPD",
@I_vCSTPONBR ="123456",
@I_vShipToName ="SnowJoe.com - (DS PPD)",
@I_vADDRESS1 ="305 VETERANS BOULEVARD",
@I_vCITY ="CARLSTADT",
@I_vSTATE ="NJ",
@I_vZIPCODE ="07072",
@I_vCOUNTRY ="United States of America",
@I_vPHNUMBR1 ="8667669563",
@I_vSUBTOTAL ="140.44",
@I_vDOCAMNT ="149.74",
@I_vSALSTERR ="EMPLOYEES",
@I_vSLPRSNID ="E_SJ",
@I_vUSER2ENT ="Consul1",
@I_vBACHNUMB ="EDIBATCH",
@I_vPRSTADCD ="MAIN",
@I_vPYMTRMID ="COD",
@I_vCURNCYID ="USD",
@I_vReqShipDate ="12/19/2018",
@I_vUpdateExisting ="1",
@I_vPRCLEVEL ="CASH01",
@I_vNOTETEXT ="Test Matt edit order",

@O_iErrorState = @_iErrorState OUTPUT,
@oErrString = @_ErrString OUTPUT
SELECT @_iErrorState as errstat2 , @_ErrString as errstring2 -- 0 errstat means all ok

-------------------------------------------------------------------------------------

I am specifically not using taSopLineIvcTaxInsert because I can't...I realize this may be needed for the USEHEADERLEVELTAXES where I need to set the amount to 0 and a few other things. The issue is that the client wants to UPDATE the taxes, not create them so this call is out. I get an eConnect error 795 "Tax detail already exists" when I attempt this portion of the code (removed from code above). Here is that code in case it's helpful:


----This is for the sales tax
exec [dbo].[taSopLineIvcTaxInsert]

@I_vSOPTYPE ="2", --This must match the document type
@I_vSOPNUMBE ="EDI0001661535", --This is self explanatory
@I_vCUSTNMBR ="SNOWJ01", --This must match the Customer on the order
@I_vLNITMSEQ ="0",
@I_vSALESAMT ="140.44", --This is to be the total amount of recalculated taxes
@I_vTAXDTLID ="NJ SALES", --This is the name of the Tax
@I_vSTAXAMNT ="9.30", --This is to be the total amount of recalculated taxes
--@I_vTXDTOTTX ="9.30",
@O_iErrorState = @_iErrorState OUTPUT,
@oErrString = @_ErrString OUTPUT
SELECT @_iErrorState as errstat2 , @_ErrString as errstring2 -- 0 errstat means all ok

I am likely going to explain that this is not a scenario we even WANT to be doing in GP. It's not best practice and I simply will not write a stored procedure to update the SOP tables to manually tweak the taxes. This gets nasty, and I won't recommend it. This company sells hundreds of items on a single order at times to the implications of incorrectly executing this is "cowboy coding" prohibitive. :) 

Thanks for listening. If you have any insight...this is a tough cookie to crack and I welcome it. I will VENMO you a beer if we somehow figure this out. 

Cheers,

Matt

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,532 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,501 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Product updates

Dynamics 365 release plans