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.

ITMVNDTY
Value

Meaning
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

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

Custom links example in Dynamics GP

A post on the Dynamics GP community forum reminded me of something I set up way back, custom links for linking to an item on a website from GP. Dynamics Community Forum for GP

Custom Links

Custom links are supported for a limited number of prompts within GP;

  • Chequebook
  • Credit Card
  • Debtor
  • Employee
  • Exchange Rate
  • Item
  • Salesperson
  • Tracking Number (SOP user defined fields)
  • Creditor

lets choose item as an example, here we see the custom link by clicking on Item Number in item enquiry or other screens that contain the hyperlink style Item Number prompt.

image

A drop down button list is displayed with a default entry of Item Maintenance and the custom link we have previously created that opens a web page for the item.

Creating a custom link

The Custom Link Setup window is found by navigating to:

Dynamics GP>>Tools>>Setup>>Company>>Custom Links

image

To create a new link click the new button.

image

For this example we want the custom link to open up the web page URL that has been saved into the Internet Information window for the item, under the “Home Page” field. So select Item for the prompt we want to create our link inside and then type “View web page” into the custom link label field. This is the text that will be displayed in the drop down button list. Finally select the source for the navigation, select home page and then save.

Before this will work you must edit the item to put the URL into the internet information window. In Item Maintenance  put the URL to open in the Home Page field and save. The internet information window is opened from the globe icon at the end of item description field.

image

Clicking on the button in item enquiry will now open our anteater page in the web browser.

image

Finally put some SQL in a SQL job to build the links every night…

INSERT INTO SY01200 (Master_type, Master_ID, INET2, INETINFO, EmailToAddress, EmailccAddress, EmailBccAddress)
SELECT 'ITM',
ITEMNMBR,
'http://www.mysite.com/products/' + rtrim(ITEMNMBR),
'',
'',
'',
''
FROM IV00101
WHERE ITEMNMBR NOT IN (SELECT Master_ID
FROM SY01200
WHERE Master_Type = 'ITM');
UPDATE SY01200
SET INET2 = 'http://www.mysite.com/products/' + rtrim(ITEMNMBR)
FROM IV00101
WHERE IV00101.ITEMNMBR = SY01200.Master_ID
AND SY01200.Master_Type = 'ITM' AND
INET2 != 'http://www.mysite.com/products/' + rtrim(ITEMNMBR) ;

This is very old SQL, these days I’d use a SQL MERGE statement to do this, but you get the idea.

Here is a good example of the technique used on tracking numbers to carrier websites for parcel tracking;

Dynamics GP Custom Links - Shipment Tracking

Dynamics GP Fix “Amount Remaining” incorrect on receivables transaction payment apply

For the following Debtor, the GP client server connection was interrupted whilst payments were being applied. This left the apply in an inconstant state. Subsequently the payment was unapplied, leaving the “amount remaining” for the payment double what it should be. This is because removing the apply has “credited” the amount remaining with the value of the document however it was never “debited” in the first instance, due to the interruption to the client server connection. This results in it now being twice the value it should be.

image

There are currently no apply records in Open Transactions Apply table RM20201.

SELECT * FROM RM20201 where CUSTNMBR='xxx'
(0 row(s) affected)
 
Looking at the transaction table RM20101, the two transaction values look good, many forum posts suggest that the CURTRXAM would be wrong in this table, for this situation, but everything is fine here:
SELECT * FROM RM20101 where CUSTNMBR='xxx'
image
So what is wrong? Note that this is a multicurrency transaction, checking in Multicurrency Receivables Transactions table MC020102 we find the following records:
SELECT * FROM MC020102 where CUSTNMBR='xxx'

image

Notice our offending 109.24 is here. Now updating this value to 54.62 from 109.24 brings us back to where we should be!

image

Now reapplying the transactions to each other works, with nothing remaining, as indeed there should be.

image

NOTE: Always back up your SQL data before attempting any data changes. If unsure consult your GP partner.

Custom eConnect XML Node–Procedure or function expects parameter, which was not supplied

Custom XML nodes error with parameters

It is possible to run your own custom stored procedures from eConnect, by passing in parameters via a custom XML nodes. I have explained this technique in a previous post, there is more information on MSDN too:

When developing a new Custom XML node it may be that the following error is encountered

Procedure or function expects parameter,  which was not supplied

This may be due to misconfiguration of the stored procedure parameters or XML node names.
Remember:

  • Prefix the stored procedure parameter names with I_v (Input Variable), so a parameter named “cardnumber” would become @I_Vcardnumber in the stored procedure
  • Pass in the eConnect XML document node like this (no prefix):
    <cardnumber>4929000000006</cardnumber>

Dynamics GP Integration Service

The Dynamics GP Integration Service, a windows service that provides an econnect end point service, if this is how the documents are being pushed through to GP, if using this then read on.

eConnect for Microsoft Dynamics GP 2013 Integration Service

eConnect integration Service for Dynamics GP

Cause

I have seen this behaviour a few times now. The error stated above occurs, using SQL profiler to view the SQL conversation between eConnect and the stored procedure, it looks like the parameters are being passed with the value of “default” rather than the expected values from XML.

Putting a break point on the application and inspecting the source eConnect XML document, it seems the XML looks ok, with all the data in the relevant nodes of the document. 

-So the nodes are not being extracted by eConnect and passed as the SQL parameters – why?

Solution

If this is being experienced, I expect that the stored procedure has been edited and/or the fields in the document have been edited, perhaps adding an extra field, changing a parameter name? What I have found is that the Integration Service seems to cache the profile of the SQL stored procedure parameters. I can guess this would be a good idea for performance reasons, but it means if a change is made to custom XML nodes or procedures there of, then the service must be restarted.

Restarting the service resets the cache and suddenly with no further changes the custom nodes behave correctly again! This cache behaviour can cause some serious head scratching until it is revealed what is happening.

not your issue?

There is another solution from another cause for this error, the post on Steve Endow’s blog explains:
Mysterious eConnect Error: The stored procedure does not exist. Watch your schemas!