eConnect Error 9370- You are passing a Currency ID in the header and you did not pass the same one to the line

Problem updating sales order with blank lines in Dynamics GP using eConnect

Existing blank empty lines on Dynamics GP sales orders cause a problem when updating orders using eConnect. Bank rows are ones where there is no item in the item number field. If no item number is entered, the line in the sales line table SOP10200 is not initialised, ppecifically of interest for this problem, is that the CURRNIDX field has a value of zero for empty lines.

The eConnect exception when trying to update and order with empty rows is, 9370:

eConnect error 9370 You are passing a Currency ID in the header and you did not pass the same one to the line

Finding the Root cause

Looking at the eConnect taSopHdrIvcInsert stored procedure and searching for 9370 locates the code check that is raises this exception.

if (@I_vCURNCYID <> '')
begin
    if exists
    (
        select 1
        from SOP10200 (nolock)
        where SOPNUMBE = @I_vSOPNUMBE
              and SOPTYPE = @I_vSOPTYPE
              and CURRNIDX <> @CURRNIDX
              and CMPNTSEQ = 0
    )
    begin
        select @O_iErrorState = 9370
        exec @iStatus = taUpdateString @O_iErrorState,
                                       @oErrString,
                                       @oErrString output,
                                       @O_oErrorState output
    end
end

Specifically the issue arises from the following fragment of SQL, that checks that the value of the field CURRNIDX in the sales header also matches all CURRNIDX field values of the child lines for the order.

select 1
from SOP10200 (nolock)
where SOPNUMBE = @I_vSOPNUMBE
and SOPTYPE = @I_vSOPTYPE
and CURRNIDX <> @CURRNIDX
and CMPNTSEQ = 0

Developing a "hack" work around

So the problem with the above check is that empty rows on a sales order have not had the CURRNIDX initialised and hence break this "rule". It is likely blank sales order lines were something the developers of eConnect were never aware of as a valid state, an oversight.
To work around this it is possible to use the [taSopHdrIvcInsertPre] and [taSopHdrIvcInsertPost] stored procedures, that run before and after the main [taSopHdrIvcInsert] procedure to set the CURRNIDX of these problematic rows to what they are expected to be. I am a great fan of leaving the data as it was found in these kind of work arounds. Other developers of add-ins or GP itself could be relying on the CURRNIDX having a zero value, to drive the logic of their application.

Procedure [taSopHdrIvcInsertPre]

For the sales order, when there is no item number and no item description (i.e. an empty row), store the original value of the CURRNIDX (almost certainly zero), in the item description field. The item description field is used as it is going to empty and the size and nature of the field allows the data to be kept there, without creating any extra "holding tables" in the database, it keeps the "hack" self contained. To make double sure there is no confusion, the CURRNIDX value is prefixed with "CURRNIDX:", this allows it to be identified for certain later.
At the same time as the CURRNIDX field is preserved, it is also updated to be the same value as the order header, allowing the above check to succeed.
Thus, the following code could be added to the body of the [taSopHdrIvcInsertPre] stored procedure before "return (@O_iErrorState)".

-- Bug fix to deal with fact that hdr routine does not line empty lines as CURRINDX does not match header
--		this proc saves the original index into item desc and it gets restored in the post stored proc
UPDATE SOP10200 
SET ITEMDESC= CONCAT('CURRNIDX:',SOP10200.CURRNIDX),
    CURRNIDX= SOP10100.CURRNIDX
FROM 
SOP10100 
JOIN 
SOP10200 
ON SOP10100.SOPTYPE=SOP10200.SOPTYPE AND SOP10100.SOPNUMBE=SOP10200.SOPNUMBE
WHERE 
SOP10100.CURRNIDX != SOP10200.CURRNIDX
AND SOP10100.SOPTYPE=@I_vSOPTYPE 
AND SOP10100.SOPNUMBE=@I_vSOPNUMBE
AND ITEMDESC =''
AND ITEMNMBR =''

Procedure [taSopHdrIvcInsertPost]

Then after eConnect has done its thing, any sales lines with the tag "CURRNIDX:" and having empty ITEMNMBR field, has the original CURRNIDX stripped out of the ITEMDESC field and that value is restored into the CURRNIDX field, leaving the record as if nothing happened.

-- Bug fix to deal with fact that hdr routine does not line empty lines as CURRINDX does not match header
--		"pre" routine saves the original index into item desc and it gets restored here
UPDATE SOP10200 
SET CURRNIDX= SUBSTRING(ITEMDESC,10,LEN(ITEMDESC)),
ITEMDESC=''
FROM 
SOP10200 
WHERE 
SOP10200.SOPTYPE=@I_vSOPTYPE 
AND SOP10200.SOPNUMBE=@I_vSOPNUMBE
AND ITEMDESC LIKE 'CURRNIDX:%'
AND ITEMNMBR = ''

This is put before the end of the procedure, before the "return (@O_iErrorState)".

Preserving the original value is not strictly required, and I suspect that just setting the CURRNIDX to that of the header, and leaving it so would also work. However I have been caught out in the past when changing data in Dynamics GP, where othere developers have made assumptions about the record state to hang logic from.
This "hack" will allow sales orders with blank lines to be updated by eConnect and is another example of the good design choices made when eConnect was developed, providing good extensibility point with the pre and post procedures.