eConnect fails to update field ITMVNDTY (IV00103) from field PRIMVNDR (IV00102)

Why does eConnect not update the IV00103 ITMVNDTY field when changing the primary vendor, PRIMVNDR of IV00102?

Table IV00102 item-site

This table relates items to sites, including what stock is at each site and which primary default vendor should be used for that site. The default vendor is held in field PRIMVNDR, Primary Vendor.

Table IV00103 item-vendor

This table relates items to vendors. It has a field ITMVNDTY this is the “item vendor type”. The field would normally only contain 1 or 2 as a value. There are also some reports of corrupt records with a value of 0, but this is not normal.

Normal operation

When using the GP client, as soon as a vendor is selected in the primary vendor field of the item site window, then the value of ITMVNDTY is set to 1 for that site-vendor combination.

Should a different vendor be set as primary vendor for another site, then that vendor-item combination will also be set with a value of 1. Hence it is normal to have multiple records for single item to have prime vendor type set to a value of 1 in IV00103 if the item is set up in multiple sites.

eConnect taItemSite does not update the primary vendor type

The taItemSite node in Dynamics GP allows the Prime Vendor to be set for an item site. On decryption of the eConnect stored procedure taItemSite, there is no reference to table IV00103. Hence updating the primary vendor using eConnect will corrupt or not set the field ITMVNDTY in IV00103 as one would expect it to.

This problem could be addressed by adding the following SQL into the post eConnect procedure taItemSitePost. This post procedure is called after the taItemSite has executed. The SQL is written to correct the ITMVNDTY in respect of any changes to the IV00102 item site table.

SQL snippet if an example, please test and verify in your own environment before using against a production database.

;  
  
WITH VendorSummary  
AS (  
    SELECT ITEMNMBR  
        ,PRIMVNDR  
    FROM IV00102  
    WHERE PRIMVNDR != ''  
        AND RCRDTYPE = 2  
    GROUP BY ITEMNMBR  
        ,PRIMVNDR  
    )  
UPDATE IV00103  
SET ITMVNDTY = CASE   
        WHEN VendorSummary.PRIMVNDR IS NULL  
            THEN 2  
        ELSE 1  
        END  
FROM IV00103  
LEFT JOIN VendorSummary ON IV00103.ITEMNMBR = VendorSummary.ITEMNMBR  
    AND IV00103.VENDORID = VendorSummary.PRIMVNDR  
WHERE IV00103.ITEMNMBR = @I_vITEMNMBR