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.
|1||This vendor is the prime vendor for this item in one of the sites|
|2||This vendor is not a prime vendor in any site for this item|
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