eConnect Customer Requester Document–Missing ShipToName field
A new field for addresses, “Ship to Address Name” was added to GP2013 (much to my delight!). When making an eConnect requester document request through eConnect, this field was missing in the XML returned by eConnect.
Using the stored procedure that generates the output, proves this field is missing from output of the database and hence it is not anything in any layers output that of the data layer of eConnect causing this issue.
DECLARE @return_value int
EXEC @return_value = [dbo].[taRequesterCUSTOMER2]
@I_vDOCTYPE = N'CUSTOMER',
@I_vOUTPUTTYPE = 2,
@I_vINDEX1TO = N'AARONFIT0001',
@I_vINDEX1FROM = N'AARONFIT0001',
@I_vFORLIST = 1
SELECT 'Return Value' = @return_value
Below is the example returned XML for The World Online customer AARONFIT0001, there are no ShipToName fields in the XML returned (GP2013R2).
<eConnect ACTION="0" Requester_DOCTYPE="CUSTOMER" DBNAME="TWO" TABLENAME="RM00101" DATE1="1900-01-01T00:00:00" CUSTNMBR="AARONFIT0001">
<Customer>
<CUSTNMBR>AARONFIT0001</CUSTNMBR>
<ADDRESS1>One Microsoft Way</ADDRESS1>
<ADDRESS2></ADDRESS2>
<ADDRESS3></ADDRESS3>
<ADRSCODE>PRIMARY</ADRSCODE>
<CITY>Redmond</CITY>
<CNTCPRSN>Bob Fitz</CNTCPRSN>
<COUNTRY>USA</COUNTRY>
<CPRCSTNM></CPRCSTNM>
<CURNCYID>Z-US$</CURNCYID>
<CUSTCLAS>USA-ILMO-T1</CUSTCLAS>
<CUSTDISC>0</CUSTDISC>
<CUSTNAME>Aaron Fitz Electrical</CUSTNAME>
<PHONE1>42555501010000</PHONE1>
<PHONE2>00000000000000</PHONE2>
<PHONE3></PHONE3>
<FAX>31255501010000</FAX>
<PYMTRMID>Net 30</PYMTRMID>
<SALSTERR>TERRITORY 1</SALSTERR>
<SHIPMTHD>LOCAL DELIVERY</SHIPMTHD>
<SLPRSNID>PAUL W.</SLPRSNID>
<STATE>WA</STATE>
<TAXSCHID>USASTCITY-6*</TAXSCHID>
<TXRGNNUM></TXRGNNUM>
<UPSZONE></UPSZONE>
<ZIP>98052-6399</ZIP>
<STMTNAME>Aaron Fitz Electrical</STMTNAME>
<SHRTNAME>Aaron Fitz Elec</SHRTNAME>
<PRBTADCD>PRIMARY</PRBTADCD>
<PRSTADCD>WAREHOUSE</PRSTADCD>
<STADDRCD>PRIMARY</STADDRCD>
<CHEKBKID>UPTOWN TRUST</CHEKBKID>
<CRLMTTYP>2</CRLMTTYP>
<CRLMTAMT>35000.00000</CRLMTAMT>
<CRLMTPER>0</CRLMTPER>
<CRLMTPAM>0.00000</CRLMTPAM>
<RATETPID></RATETPID>
<PRCLEVEL></PRCLEVEL>
<MINPYTYP>0</MINPYTYP>
<MINPYDLR>0.00000</MINPYDLR>
<MINPYPCT>0</MINPYPCT>
<FNCHATYP>1</FNCHATYP>
<FNCHPCNT>150</FNCHPCNT>
<FINCHDLR>0.00000</FINCHDLR>
<MXWOFTYP>2</MXWOFTYP>
<MXWROFAM>25.00000</MXWROFAM>
<COMMENT1></COMMENT1>
<COMMENT2></COMMENT2>
<USERDEF1>Retail</USERDEF1>
<USERDEF2></USERDEF2>
<TAXEXMT1></TAXEXMT1>
<TAXEXMT2></TAXEXMT2>
<BALNCTYP>0</BALNCTYP>
<STMTCYCL>5</STMTCYCL>
<BANKNAME></BANKNAME>
<BNKBRNCH></BNKBRNCH>
<FRSTINDT>1900-01-01T00:00:00</FRSTINDT>
<INACTIVE>0</INACTIVE>
<HOLD>0</HOLD>
<CRCARDID></CRCARDID>
<CRCRDNUM></CRCRDNUM>
<CCRDXPDT>1900-01-01T00:00:00</CCRDXPDT>
<KPDSTHST>1</KPDSTHST>
<KPCALHST>1</KPCALHST>
<KPERHIST>1</KPERHIST>
<KPTRXHST>1</KPTRXHST>
<CREATDDT>1970-01-01T00:00:00</CREATDDT>
<MODIFDT>2004-01-30T00:00:00</MODIFDT>
<Revalue_Customer>1</Revalue_Customer>
<Post_Results_To>0</Post_Results_To>
<FINCHID></FINCHID>
<GOVCRPID></GOVCRPID>
<GOVINDID></GOVINDID>
<DISGRPER>0</DISGRPER>
<DUEGRPER>0</DUEGRPER>
<DOCFMTID></DOCFMTID>
<Send_Email_Statements>0</Send_Email_Statements>
<GPSFOINTEGRATIONID></GPSFOINTEGRATIONID>
<INTEGRATIONSOURCE>0</INTEGRATIONSOURCE>
<INTEGRATIONID></INTEGRATIONID>
<Address>
<CUSTNMBR>AARONFIT0001</CUSTNMBR>
<ADRSCODE>PRIMARY</ADRSCODE>
<SLPRSNID></SLPRSNID>
<UPSZONE></UPSZONE>
<SHIPMTHD>LOCAL DELIVERY</SHIPMTHD>
<TAXSCHID>USASTCITY-6*</TAXSCHID>
<CNTCPRSN>Bob Fitz</CNTCPRSN>
<ADDRESS1>One Microsoft Way</ADDRESS1>
<ADDRESS2></ADDRESS2>
<ADDRESS3></ADDRESS3>
<COUNTRY>USA</COUNTRY>
<CITY>Redmond</CITY>
<STATE>WA</STATE>
<ZIP>98052-6399</ZIP>
<PHONE1>42555501010000</PHONE1>
<PHONE2>00000000000000</PHONE2>
<PHONE3></PHONE3>
<FAX>31255501010000</FAX>
<GPSFOINTEGRATIONID></GPSFOINTEGRATIONID>
<INTEGRATIONSOURCE>0</INTEGRATIONSOURCE>
<INTEGRATIONID></INTEGRATIONID>
<Internet_Address />
</Address>
<Address>
<CUSTNMBR>AARONFIT0001</CUSTNMBR>
<ADRSCODE>WAREHOUSE</ADRSCODE>
<SLPRSNID></SLPRSNID>
<UPSZONE></UPSZONE>
<SHIPMTHD>LOCAL DELIVERY</SHIPMTHD>
<TAXSCHID>USASTCITY-6*</TAXSCHID>
<CNTCPRSN>Bob Fitz</CNTCPRSN>
<ADDRESS1>11403 45 St. South</ADDRESS1>
<ADDRESS2></ADDRESS2>
<ADDRESS3></ADDRESS3>
<COUNTRY>USA</COUNTRY>
<CITY>Chicago</CITY>
<STATE>IL</STATE>
<ZIP>60603-0776</ZIP>
<PHONE1>31255501020000</PHONE1>
<PHONE2>31255501020000</PHONE2>
<PHONE3></PHONE3>
<FAX>31255501020000</FAX>
<GPSFOINTEGRATIONID></GPSFOINTEGRATIONID>
<INTEGRATIONSOURCE>0</INTEGRATIONSOURCE>
<INTEGRATIONID></INTEGRATIONID>
<Internet_Address />
</Address>
</Customer>
</eConnect>
Thanks to Steve Endow for running against GP 2015 R2 (14.00.0817) to find same thing, no ship to name.
<eConnect ACTION="0" Requester_DOCTYPE="CUSTOMER" DBNAME="TWO" TABLENAME="RM00101" DATE1="1900-01-01T00:00:00" CUSTNMBR="AARONFIT0001">
<Customer>
<CUSTNMBR>AARONFIT0001</CUSTNMBR>
<ADDRESS1>One Microsoft Way</ADDRESS1>
<ADDRESS2></ADDRESS2>
<ADDRESS3></ADDRESS3>
<ADRSCODE>PRIMARY</ADRSCODE>
<CITY>Redmond</CITY>
<CNTCPRSN>Bob Fitz</CNTCPRSN>
<COUNTRY>USA</COUNTRY>
<CPRCSTNM></CPRCSTNM>
<CURNCYID>Z-US$</CURNCYID>
<CUSTCLAS>USA-ILMO-T1</CUSTCLAS>
<CUSTDISC>0</CUSTDISC>
<CUSTNAME>Aaron Fitz Electrical</CUSTNAME>
<PHONE1>42555501010000</PHONE1>
<PHONE2>00000000000000</PHONE2>
<PHONE3></PHONE3>
<FAX>31255501010000</FAX>
<PYMTRMID>Net 30</PYMTRMID>
<SALSTERR>TERRITORY 1</SALSTERR>
<SHIPMTHD>LOCAL DELIVERY</SHIPMTHD>
<SLPRSNID>PAUL W.</SLPRSNID>
<STATE>WA</STATE>
<TAXSCHID>USASTCITY-6*</TAXSCHID>
<TXRGNNUM></TXRGNNUM>
<UPSZONE></UPSZONE>
<ZIP>98052-6399</ZIP>
<STMTNAME>Aaron Fitz Electrical</STMTNAME>
<SHRTNAME>Aaron Fitz Elec</SHRTNAME>
<PRBTADCD>PRIMARY</PRBTADCD>
<PRSTADCD>WAREHOUSE</PRSTADCD>
<STADDRCD>PRIMARY</STADDRCD>
<CHEKBKID>UPTOWN TRUST</CHEKBKID>
<CRLMTTYP>2</CRLMTTYP>
<CRLMTAMT>35000.00000</CRLMTAMT>
<CRLMTPER>0</CRLMTPER>
<CRLMTPAM>0.00000</CRLMTPAM>
<RATETPID></RATETPID>
<PRCLEVEL></PRCLEVEL>
<MINPYTYP>0</MINPYTYP>
<MINPYDLR>0.00000</MINPYDLR>
<MINPYPCT>0</MINPYPCT>
<FNCHATYP>1</FNCHATYP>
<FNCHPCNT>150</FNCHPCNT>
<FINCHDLR>0.00000</FINCHDLR>
<MXWOFTYP>2</MXWOFTYP>
<MXWROFAM>25.00000</MXWROFAM>
<COMMENT1></COMMENT1>
<COMMENT2></COMMENT2>
<USERDEF1>Retail</USERDEF1>
<USERDEF2></USERDEF2>
<TAXEXMT1></TAXEXMT1>
<TAXEXMT2></TAXEXMT2>
<BALNCTYP>0</BALNCTYP>
<STMTCYCL>5</STMTCYCL>
<BANKNAME></BANKNAME>
<BNKBRNCH></BNKBRNCH>
<FRSTINDT>1900-01-01T00:00:00</FRSTINDT>
<INACTIVE>0</INACTIVE>
<HOLD>0</HOLD>
<CRCARDID></CRCARDID>
<CRCRDNUM></CRCRDNUM>
<CCRDXPDT>1900-01-01T00:00:00</CCRDXPDT>
<KPDSTHST>1</KPDSTHST>
<KPCALHST>1</KPCALHST>
<KPERHIST>1</KPERHIST>
<KPTRXHST>1</KPTRXHST>
<CREATDDT>1980-01-01T00:00:00</CREATDDT>
<MODIFDT>2014-01-30T00:00:00</MODIFDT>
<Revalue_Customer>1</Revalue_Customer>
<Post_Results_To>0</Post_Results_To>
<FINCHID></FINCHID>
<GOVCRPID></GOVCRPID>
<GOVINDID></GOVINDID>
<DISGRPER>0</DISGRPER>
<DUEGRPER>0</DUEGRPER>
<DOCFMTID></DOCFMTID>
<Send_Email_Statements>0</Send_Email_Statements>
<GPSFOINTEGRATIONID></GPSFOINTEGRATIONID>
<INTEGRATIONSOURCE>0</INTEGRATIONSOURCE>
<INTEGRATIONID></INTEGRATIONID>
<Address>
<CUSTNMBR>AARONFIT0001</CUSTNMBR>
<ADRSCODE>PRIMARY</ADRSCODE>
<SLPRSNID></SLPRSNID>
<UPSZONE></UPSZONE>
<SHIPMTHD>LOCAL DELIVERY</SHIPMTHD>
<TAXSCHID>USASTCITY-6*</TAXSCHID>
<CNTCPRSN>Bob Fitz</CNTCPRSN>
<ADDRESS1>One Microsoft Way</ADDRESS1>
<ADDRESS2></ADDRESS2>
<ADDRESS3></ADDRESS3>
<COUNTRY>USA</COUNTRY>
<CITY>Redmond</CITY>
<STATE>WA</STATE>
<ZIP>98052-6399</ZIP>
<PHONE1>42555501010000</PHONE1>
<PHONE2>00000000000000</PHONE2>
<PHONE3></PHONE3>
<FAX>31255501010000</FAX>
<GPSFOINTEGRATIONID></GPSFOINTEGRATIONID>
<INTEGRATIONSOURCE>0</INTEGRATIONSOURCE>
<INTEGRATIONID></INTEGRATIONID>
<Internet_Address />
</Address>
<Address>
<CUSTNMBR>AARONFIT0001</CUSTNMBR>
<ADRSCODE>WAREHOUSE</ADRSCODE>
<SLPRSNID></SLPRSNID>
<UPSZONE></UPSZONE>
<SHIPMTHD>LOCAL DELIVERY</SHIPMTHD>
<TAXSCHID>USASTCITY-6*</TAXSCHID>
<CNTCPRSN>Bob Fitz</CNTCPRSN>
<ADDRESS1>11403 45 St. South</ADDRESS1>
<ADDRESS2></ADDRESS2>
<ADDRESS3></ADDRESS3>
<COUNTRY>USA</COUNTRY>
<CITY>Chicago</CITY>
<STATE>IL</STATE>
<ZIP>60603-0776</ZIP>
<PHONE1>31255501020000</PHONE1>
<PHONE2>31255501020000</PHONE2>
<PHONE3></PHONE3>
<FAX>31255501020000</FAX>
<GPSFOINTEGRATIONID></GPSFOINTEGRATIONID>
<INTEGRATIONSOURCE>0</INTEGRATIONSOURCE>
<INTEGRATIONID></INTEGRATIONID>
<Internet_Address />
</Address>
</Customer>
</eConnect>
Plan B
Out the box, the requester does not include the “new” ship to name field in the address table. I switched to “Plan B”, I would create my own custom requester, I already access some none native tables for some other custom modifications where I access them from eConnect via custom requesters. This experience means that I know its a simple config in the eConnect setup.
Custom eConnect requester
To create a custom requester, edit the setup table, eConnect_Out_Setup in the company database. On opening the table, I realised something that has never registered in my head before -the default “out the box” requesters are defined in that table already! Great, that potentially means I can add this missing field to the existing requester through configuration.
To add a field to the requester output insert the column name, “ShipToName” into the next empty data column in the setup table for the DOCTYPE in question. Remember to increment the data count column, otherwise it will not generate the new column in the output XML. In my table the next empty cell was DATA20, so inserted “ShipToName” in there, here is the SQL.
-- Create new field in requester output
-- Increase the output field count by one
UPDATE [dbo].[eConnect_Out_Setup]
SET
DATACNT=DATACNT+1,
DATA20='ShipToName'
WHERE
DOCTYPE='CUSTOMER'
AND TABLENAME='RM00102'
AND DATA20=''
AND DATA19!=''
The document Updating the Transaction Requester for retrieve operations describes how to generate the stored procedures used by eConnect, following that guidance run the following SQL.
-- Drop The original output stored proc
drop PROCEDURE taRequesterCUSTOMER2
-- Rebuild the stored proc
exec eConnectOutCreate 'CUSTOMER', 2
Now running the test SQL procedure used earlier, I find the XML output now includes the ShipToName field, issue fixed!
So my learning today was that the default, out the box, encrypted stored procedures are not shipped with GP and are actually built from definitions in the eConnect setup table. These can be updated to your requirements. This is also perhaps the reason the new field is not included, customers upgrading would not want the existing eConnect definitions changing in the setup table, something the installer would have to do in order to make the new fields available. I guess all is forgiven!
Create your own
In production, it is better to define a new requester document type, than update the default ones so as to avoid conflicts with other, perhaps badly authored integrating applications. They might not expect that extra field in the XML output from the default requester document types and break.
To do this simply duplicate the “CUSTOMER” rows (DocType=CUSTOMER) in the setup table, and rename the Document type field to another name in the copy, this will be your new document type to use in place of CUSTOMER in the above dealings.
Please feel free to comment on this post, it motivates me to write more!