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.
 
image
 
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!

GP Database Maintenance Utility

Problem today with attempting to run the GP Database Maintenance utility, however it was only listing DYNAMICS and no other companies installed on the database server.

image

I took a look at what the window was up to with the database, to see if permissions were an issue. The following SQL is being executed around this window, but the user has Sysadmin rights, so I’m baffled as to why this wont work.

There are posts on Google about making certain other users are not logged into the databases, however even waiting until out of hours, with no users, gave the same result.

 

select [name] from [master]..[sysdatabases]

if ( ( OBJECTPROPERTY ( OBJECT_ID('DB_Upgrade'), 'IsTable' ) = 1)) select 1 else select 0
if ( (select [db_status] from [DB_Upgrade] where [db_name]='DYNAMICS' and PRODID=0) = 0)
select RTRIM(LTRIM(STR(db_verMajor))) + '.' + RTRIM(LTRIM(STR(db_verMinor))) +
'.' + RTRIM(LTRIM(STR(db_verBuild))) from DB_Upgrade
where db_name = 'DYNAMICS' and PRODID = 0 else select RTRIM(LTRIM(STR(db_verOldMajor))) + '.' + RTRIM(LTRIM(STR(db_verOldMinor))) + '.' +
RTRIM(LTRIM(STR(db_verOldBuild))) from DB_Upgrade where db_name = 'DYNAMICS' and PRODID = 0

if (IS_SRVROLEMEMBER ('sysadmin')) = 1 BEGIN select 1 END ELSE BEGIN select 0 END

But it works on the server

First port of call on these kind of issues is to go try it on a machine that is not used for software development as the GP install does tend to get hacked around a lot in developing solutions. Launching the utility on the server worked correctly, so a local install issue. I performed a diff between the two application folders and files. After twenty minutes I had it narrowed down to the DYNUTILS.SET file. When this file was copied from the server, the utility started working.

Performing a file diff on the SET file revealed the problem.

image

The manufacturing module reference had been taken out of the local SET file, but that alone should not cause this issue. Look, the left hand diff summary visualisation shows only the DIC count and the DIC declaration to be changed between the two files. What about the file path pointers later on in the SET file? I would expect to see three groups of changes to remove a module. Three lines for the Manufacturing module .DIC references to the files should have been taken out of the SET file. This turns out to be the source of the problem. When the reference to the module was removed at the top of the file, the reference to the DIC files had not been removed, leaving a corrupt SET file.

image

We can see from the first image that manufacturing module ID is 346, so removing the following lines and then saving the SET file made everything good again.

:C:Program Files (x86)/Microsoft Dynamics/GP2013/du346.dic
:C:Program Files (x86)/Microsoft Dynamics/GP2013/Data/DU346F.DIC
:C:Program Files (x86)/Microsoft Dynamics/GP2013/Data/DU346R.DIC
 
 
Result: Now we can see all the companies (if I hadn’t blurred them out)…
 
image
As it happens I’ve sorted out the issue I needed the Database Maintenance Utility for in the first place, but others may find this helpful.
 
Please leave a comment if you found this helpful, as comments motivate me to write more!
 

m~hance form NetSuite division to plug cloud SaaS gap

m~hance, one of the largest Dynamics GP partners in the UK, also providers of hosted Dynamics GP in the cloud, have announced a move that acknowledges the market direction of ERP delivery. They have committed to Cloud ERP with the creation of a new division named “HighCloud Solutions”. In a letter to existing customers m-hance state, that this will be an “accredited NetSuite Provider supporting customers who are seeking a cloud-based Enterprise Resource Planning (ERP) platform”. In the same letter m~hance talk about the market challenges of migrating large complex installations to cloud based ERP using the current Dynamics products.

So from this do we conclude that for some customer prospects that are seeking Software-as-a-Service (SaaS) that Dynamics is currently uncompetitive, when stacked up against competing ERP suites that were born in the cloud? Although with the new web clients for NAV and GP, the story is improving for cloud hosted Dynamics ERP, the native cloud apps still have the advantage of being developed for the cloud platform and the are easier to provision as a result. 

m-hance reassure the Dynamics customers  that the company “remains dedicated to Microsoft”, and that “we’ll continue to invest in best-of breed, future-proof Microsoft Solutions to ensure we can go on meeting your needs now and in the future.”

This is an interesting move for the UK ERP market and would seem to indicate that m~hance feel they have been loosing too many new ERP deals and existing customers to competitors in the cloud. They now join other Dynamics partners such as Nolan, using Netsuite to plug the SaaS product gap. This move will also fuel the questions over where the future lies for the long term future of Dynamics GP within the UK, however many companies need a very good reason to consider a major ERP project so for the moment Dynamics GP should remain strong, certainly I know many new sites are still adopting GP in the UK and there is a large existing user base.

Back in Jan 2015 Forbes published this Five Catalysts Accelerating Cloud ERP Growth In 2015  that indicates new hybrid business models that are two tier (cloud and on premise). It also states "net new license revenues for traditional ERP systems have been declining since 2013". It also goes on to talk about the new world of business, " Scale and speed need to be measured from the customers’ perspective, not just from internal – and often highly myopic – metrics. Monolithic legacy, on premise ERP systems have often been designed to match a predictable drumbeat of production". The article is worth a read. 

TSQL determine inventory MultiBinning is enabled in Dynamics GP

Inventory Control Setup Table IV40100, field “ENABLEMULTIBIN” shows if the Dynamics GP company has multiple binning turned on or off (enabled or not).

Use the following SQL to check the multiple bin status of a GP company:

SELECT CASE 
WHEN ENABLEMULTIBIN = 1
THEN 'ENABLED'
ELSE 'DISABLED'
END AS [MultiBinning Status]
FROM IV40100
 
The GP inventory setup window contains the checkbox that controls this setting.