Question Status

DDC asked a question on 25 Aug 2008 11:09 AM

I have an integration for open Purchase orders.  When I run the integration I get an error that the Vendor Item does not exist.  Is there any way for the integration to add the vendor item to the vendor item file at the time of the integration.  I am working with GP version 10 and IM version 10.

Eric1 responded on 25 Aug 2008 3:35 PM

THere is a seperate Integration under Items that will allow importing the Vendor Item. You would need to run that one, then your PO Integration

DDC responded on 26 Aug 2008 5:43 AM

Thank you.  I did run the item integration.  What is happening is these items were not in the spreadsheets at the of the item integration.  What I would like to do is to add at the time of the PO integration.  The item is in the item file, but this is a new purchase from a vendor.  Is there a way of doing that.


Thank you again for your help.

Ron Draganowski responded on 3 Sep 2008 11:28 AM

I don't usually give away free code as I need to feed my family, etc., but I'm having a good afternoon.

What you need to do change the source for the item mapping to use a script.  In the script, you have to do something rather fancy.  You need to connect to the GP company database in SQL, and create the relationship via eConnect.  This can be done at the database level without web services or .NET applications.  This code makes it happen:

DECLARE @intStatus int, @outErrStatus int, @outErrMsg varchar(255)
EXEC @intStatus = taCreateItemVendors
= 'ENTER Item Number here',
@I_vVENDORID = 'ENTER Vendor ID here',
@I_vVNDITNUM = 'ENTER Vendor Item Number here',
@O_iErrorState = @outErrStatus OUTPUT,
@oErrString = @outErrMsg OUTPUT

if @intStatus <> 0 or @outErrStatus <> 0
if exists(SELECT 1 FROM DYNAMICS..taErrorCode (NOLOCK) WHERE ErrorCode = @outErrStatus)
select @outErrMsg = rtrim(ErrorDesc) FROM DYNAMICS..taErrorCode (NOLOCK) WHERE ErrorCode = @outErrStatus
select @outErrMsg = 'Unknown eConnect error encountered - taCreateItemVendors'

SELECT @outErrStatus as ErrorStatus, @outErrMsg as ErrMessage

Note that the last line of my code returns two variables just indicating whether the creation was successful or not.  That might help you decide what to do next.  You could even log the eConnect error to the Integration Mgr error log.

You then need to set the mapping value with a line something like

CurrentField.Value = SourceField("Items.Item Number").Value

Check the syntax against the IM User Guide, as that's from memory and not cross checked.

FYI on best practice.  It would not be preferred for you to open a connection to the database for every line of the integration.  It's best to open the connection in the "Before Integration" event and set it to a global variable.  I think you'll find examples of how to do this in CustomerSource Knowledgebase.  Then you can use the global variable in the item mapping script.

Ron Draganowski
Senior Solution Developer
Olsen Thielen Technologies, Inc.
St Paul, Minnesota


Find me on LinkedIn: