Monday, December 12, 2016

Rare eConnect taPMTransactionInsert error 311 and 312: Tax Schedule ID does not exist

By Steve Endow

Here are two obscure eConnect errors that you should never encounter.  Unlike my customer who did encounter them.


Error Number = 311 Stored Procedure= taPMTransactionInsert Error Description = Misc Tax Schedule ID (MSCSCHID) does not exist in the Sales/Purchse Tax Schedule Master Table – TX00102
MSCSCHID = Note: This parameter was not passed in, no value for the parameter will be returned.


Error Number = 312 Stored Procedure= taPMTransactionInsert Error Description = Freight Tax Schedule ID (FRTSCHID) does not exist in the Sales/Purchases Tax Schedule Master Table – TX00102
FRTSCHID = Note: This parameter was not passed in, no value for the parameter will be returned.


Notice that the error says that the tax schedule ID does not exist, but then says that no value was passed in for the tax schedule ID.

So, if you are sending in a blank tax schedule ID value to eConnect, how can it be invalid, and thus cause this error?

As with many eConnect errors like this, the error is not caused by what you send to eConnect.  It's caused by some value or configuration option buried deep in Dynamics GP, that is impossible to figure out based on the eConnect error alone.

Here is the validation script that triggers the 311 error:

IF ( @I_vMSCSCHID <> '' )
    BEGIN
        IF NOT EXISTS ( SELECT  1
                        FROM    TX00102 (NOLOCK)
                        WHERE   TAXSCHID = @I_vMSCSCHID )
            BEGIN
                SELECT  @O_iErrorState = 311;
                EXEC @iStatus = taUpdateString @O_iErrorState,
                    @oErrString, @oErrString OUTPUT,
                    @O_oErrorState OUTPUT;
            END;
    END;


This would seem to make sense--if a Misc Tax Schedule ID value was passed in, verify that it exists in the TX00102 tax table.

But...what if you aren't passing in a Misc Tax Schedule ID--which our error message above indicates?

Well, we then need to dig a little deeper to find out where a value is being set for @I_vMSCSCHID.  And we find this:

SELECT  @I_vPCHSCHID = CASE WHEN ( @I_vPCHSCHID = '' )
                            THEN PCHSCHID
                            ELSE @I_vPCHSCHID
                        END,
        @I_vMSCSCHID = CASE WHEN ( @I_vMSCSCHID = '' )
                            THEN MSCSCHID
                            ELSE @I_vMSCSCHID
                        END,
        @I_vFRTSCHID = CASE WHEN ( @I_vFRTSCHID = '' )
                            THEN FRTSCHID
                            ELSE @I_vFRTSCHID
                        END
FROM    PM40100 (NOLOCK)
WHERE   UNIQKEY = '1';


So what does this tell us?  If no tax schedules are passed into taPMTransactionInsert, eConnect tries to get default Tax Schedule IDs from the Payables Setup table, PM40100.  Once it gets those Tax Schedule IDs, it validates them.

So...how could that cause the error we're seeing?

Figured it out yet?

The only way the default Tax Schedule IDs in PM40100 could cause the error would be if those default Tax Schedule IDs are INVALID!

Wait a minute.  How could the default tax schedule IDs in the Payables Setup Options window be invalid, you ask?  The Payables Setup Options window validates those at the field level--the window won't let you enter an invalid value or save an invalid value.

So, that leaves either a direct SQL update to set an invalid value in PM40100, or perhaps more likely, someone ran a SQL delete to remove records from TX00102.  My guess is that someone figured they didn't need a bunch of pesky tax schedules, or wanted to change some tax schedule IDs, and they didn't realize that the PM40100 was also storing the tax schedule IDs.

I've asked the consultant to run this query to check the tax schedule IDs setup in PM40100.

SELECT  pm.PCHSCHID, 
(SELECT COUNT(*) FROM TX00102 WHERE TAXSCHID = pm.PCHSCHID) AS PurchIDExists, 
pm.MSCSCHID, 
(SELECT COUNT(*) FROM TX00102 WHERE TAXSCHID = pm.MSCSCHID) AS MiscIDExists, 
pm.FRTSCHID,
(SELECT COUNT(*) FROM TX00102 WHERE TAXSCHID = pm.FRTSCHID) AS FrtIDExists 
FROM PM40100 pm 



If the tax schedules have values, but the "IDExists" fields have a value of 0, then that means there are no matching records in TX00102, and that the values are invalid.



And that is the solution to your mystery eConnect error of the week!


You can also find him on Google+ and Twitter




No comments: