AnsweredAssumed Answered

Query customer profile on Dynamics GP using eConnect

Question asked by darren068106 on Feb 20, 2018
Latest reply on Mar 6, 2018 by cegbert328645

Hi All

 

I have noticed that the connector for MS Dynamics GP 2015 and eConnect works in a very peculiar way

 

I have the connector setup with the QUERY action with a successful connection to our UAT database. Both the information in the connection is fine and the user has the correct roles assigned in the database, etc. as per the guide on the Boomi portal

 

When I go to setup the import for the customer list it creates the SOAP profile, etc. as expected. I then setup the parameter for the CUSTNMBR using a static value as a test and it returns the following XML

<?xml version="1.0" encoding="windows-1252"?>
<root><eConnect><ACTION>0</ACTION><DOCTYPE>Customer</DOCTYPE><CUSTNMBR>999988</CUSTNMBR><DBNAME>UAT14</DBNAME><TABLENAME>RM00101</TABLENAME></eConnect></root>

It appears as if everything has worked as expected as the workflow has all turned green but the actual data for the customer record has not returned with the response

 

 

So when I add a SQL profiler on the database to monitor the T-SQL it runs the following query and I can confirm the XML returned is the same as above

declare @p40 int
set @p40=0
exec eConnectOut @I_vDOCTYPE='Customer',@I_vOUTPUTTYPE=0,@I_vINDEX1TO=default,@I_vINDEX2TO=default,@I_vINDEX3TO=default,@I_vINDEX4TO=default,@I_vINDEX5TO=default,@I_vINDEX6TO=default,@I_vINDEX7TO=default,@I_vINDEX8TO=default,@I_vINDEX9TO=default,@I_vINDEX10TO=default,@I_vINDEX11TO=default,@I_vINDEX12TO=default,@I_vINDEX13TO=default,@I_vINDEX14TO=default,@I_vINDEX15TO=default,@I_vINDEX1FROM=default,@I_vINDEX2FROM=default,@I_vINDEX3FROM=default,@I_vINDEX4FROM=default,@I_vINDEX5FROM=default,@I_vINDEX6FROM=default,@I_vINDEX7FROM=default,@I_vINDEX8FROM=default,@I_vINDEX9FROM=default,@I_vINDEX10FROM=default,@I_vINDEX11FROM=default,@I_vINDEX12FROM=default,@I_vINDEX13FROM=default,@I_vINDEX14FROM=default,@I_vINDEX15FROM=default,@I_vFORLOAD=default,@I_vFORLIST=1,@I_vACTION=default,@I_vROWCOUNT=default,@I_vREMOVE=default,@I_vDATE1=default,@I_vWhereClause='CUSTNMBR = ''999988''',@O_iErrorState=@p40 output
select @p40

I notice that when I change @l_vOUTPUTTYPE to 1 and execute the stored procedure it then returns the XML as expected because when this value is set to 1 it returns the master document as per the guide in the link below

https://msdn.microsoft.com/en-us/library/ff623993.aspx 

 

Output:

So I try an EXECUTE action instead to the GetEntity method with a specially crafted XML payload as below

<?xml version="1.0"?><eConnect xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><RQeConnectOutType><eConnectProcessInfo xsi:nil="true" /><taRequesterTrxDisabler_Items xsi:nil="true" /><eConnectOut><DOCTYPE>Customer</DOCTYPE><OUTPUTTYPE>1</OUTPUTTYPE><INDEX1TO>999988</INDEX1TO><INDEX1FROM>999988</INDEX1FROM><FORLIST>1</FORLIST></eConnectOut></RQeConnectOutType></eConnect>

It then returns the customer information similar to that of the eConnectOut procedure above but after modifying the @l_vOUTPUTTYPE to 1 as mentioned

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<GetEntityResponse xmlns="http://schemas.microsoft.com/dynamics/gp/2010/01">
<GetEntityResult>
<root>
<eConnect ACTION="0" Requester_DOCTYPE="Customer" DBNAME="UAT14" TABLENAME="RM00101" DATE1="1900-01-01T00:00:00" CUSTNMBR="999988">
<Customer>
<CUSTNMBR>999988</CUSTNMBR>
<ADDRESS1>...</ADDRESS1>
<ADDRESS2>...</ADDRESS2>
<ADDRESS3>...</ADDRESS3/>
<ADRSCODE>...</ADRSCODE>

...

 

So the question is how comes the standard QUERY functionality against the customer list does not return any data but passing a specially crafted XML document to the EXECUTE does?

 

Can someone explain if I am doing some thing wrong? The only parameter available is the CUSTNMBR on the QUERY

 

 

Any help will be much appreciated

 

Many thanks

Darren

Outcomes