eConnect Customer Requester Document–Missing ShipToName field
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
<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
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.