Dynamics Community Forum Thread Details

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:

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