Gracefully dealing with eConnect errors

If you need to integrate with Microsoft Dynamics GP one of the options you may choose is to use the eConnect product. eConnect is an API that allows you to submit XML documents to Dynamics GP to perform CRUD operations on most of the document types in GP. Using .NET for integrations, if any issues/problems arise from submissions to eConnect,  via eConnect_EntryPoint or eConnect_Requester methods, then these errors are surfaced as eConnect exceptions. The .Message property of this class contains the error text.

The econnectException class returns the message from the eConnect stored procedure that originated the problem. A table of these messages is held in SQL server, DYNAMICS database, table taErrorCode. This table gives an idea of the error conditions you may not have thought possible and lets you be a bit more proactive at handling errors.

ta_ErrorCode To see the contents of this table see this file:

Item does not allow back orders

Lets choose an example problem that you may experience. When submitting a SOP sales order document, normally you want any items that are out of stock to be back ordered. This is easy, set the QtyShrtOpt = 4 in the XML to back order the balance. However if you have a situation where you have a telesales team hammering in orders as well as a website taking orders, even with SQL replication you can occasionally get a scenario where a web order comes in for an item that has been set to disallow web orders and it no longer has enough stock to satisfy the web order. This may be due to latency in updating stock on the website for example. eConnect lets us know with the following exception: “Microsoft.GreatPlains.eConnect.eConnectException: Error Number = 4776”

Gracefully dealing with it

There a few ways I can think of to deal with this, the chosen one is to change the QtyShrtOpt = 6 for the line in question inorder to cancel the qty that can not be allocated from stock. It is wise to then set an order note to let the sales staff who will process the order know about the issue so it can be resolved with the customer, perhaps alternative item offered.

.NET code

A regular expression is used to parse the eConnect exception text. This allows easy detection of what error has occurred and extracts the item number for the order line raising the exception.

  1. Dim ItemNumberList As New List(Of String)
  2. 'Get the item numbers that exhibit this error
  3. For Each CurrentMatch As RegularExpressions.Match In _
  4.     RegularExpressions.Regex.Matches(ErrorText, _
  5.         "(<taSopLineIvcInsert>.*<ITEMNMBR>(.*)</ITEMNMBR>.*</taSopLineIvcInsert>) --->.*Error Number = 4776", _
  6.         RegularExpressions.RegexOptions.Singleline)
  7.     If CurrentMatch.Groups.Count > 1 Then
  8.         'capture 2 has the itemnmbr
  9.         ItemNumberList.Add(CurrentMatch.Groups(2).Value.Trim)
  10.     End If
  11. Next CurrentMatch

Having a list of item numbers with this issue allows us to change the XML of the document being submitted to alter the quantity shortage option flag to cancel the balance (option 6). Promoting LINQ for XML work, load the XML document into a XDocument (LINQ XML Document) class.

  1. Dim salesdoc As XDocument = XDocument.Parse(xmlSalesOrder.OuterXml)

Now use a LINQ query to get all the elements that need the backorder option changing and change it to 6.

  1. For Each CurrentItem In ItemNumberList
  2.     Dim CurrenItemVar As String = CurrentItem
  3.     For Each CurrentElement As XElement In From salesline In salesdoc.Elements.Descendants("taSopLineIvcInsert") _
  4.                     Where salesline.Element("ITEMNMBR").Value.StartsWith(CurrenItemVar) _
  5.                     Select salesline
  6.         CurrentElement.SetElementValue("QtyShrtOpt", "6")
  7.     Next
  8.     salesdoc.Elements.Descendants("taSopHdrIvcInsert")(0) _
  9.       .SetElementValue("NOTETEXT", _
  10.         salesdoc.Elements.Descendants("taSopHdrIvcInsert")(0).Element("NOTETEXT").Value & vbCr & String.Format( _
  11.         "Item: {0} could not be fully ordered due to no back order allowed flag set on item and lack of stock.", CurrenItemVar))
  12. Next
For each item fixed, the XML of the document we are submitting has order notes appended to take account of the fact there is an issue with this item. The sales are already always reading the order notes for customer comments coming through from the website so should catch these notes.

Now the altered XML document is resubmitted to Dynamics GP via eConnnect. If it fails this time there is an issue that we have not programmed for so it needs administrative intervention.

To load the xDocument back into the XMLDocument class for submission to eConnect,

  1. xmlSalesOrder.Load(salesdoc.CreateReader())

Note on security

Beware exposing your ERP system to your website – if the website gets compromised, then so is your business. eConnect allows most of your business data to be altered and queried - this is something to be very careful of. With the implementation I created, the XML document is punched through the firewalls to a custom web service on the GP segment of the network. This web service only lets through the specific eConnect documents we want to allow through and only those that meet specific criteria to limit the attack potential.

Summary

By adding to this example, common errors your eConnect integration encounters could be eliminated so IT staff are prevented from spending time supporting disruptive day to day integration issues. Thus these problem cases are handed back to the process owner, be that; buyers, warehouse, or sales staff.

“Kill bit” shot our Dynamics GP!

After installing security advisory 960715 , Microsoft Dynamics GP clients went down. This happens on log in to GP, raising exceptions due to missing objects.

It turns out that there are still a few VBA forms used by the GP instance that have not yet been migrated to .NET. These forms used the msdatgrd.ocx control and some others to that it turns out have been identified as having security flaws. There is a mechanism in place that allows a “kill bit” to be issued against an Active X control to kill it off. This is one of the things the security update does, killing your application that uses these controls.

VBA Form for order tracing

The was not entirely clear a few months ago when the issue was first encountered, but since then research explained the corrective actions required. Basically installing the newer version of Visual Studio 6 installs the newer safe controls into the windows system32 directory. Deleting the cache copies of these controls from C:\Documents and Settings\[username]\Application Data\Microsoft\Forms\ (*.exd) and regsvr32ing the controls sorts it all out.

You must have developer rights to the control i.e. Visual Studio. Luckily since I worked all this out the Visual Studio Office Developer (VSOD) Support Team have put together a comprehensive summary of all the information and guidance required to tackle this issue on these pages here. http://blogs.msdn.com/vsod/archive/2009/06/05/visual-basic-6-controls-stop-working-after-security-advisory-960715.aspx

Meanwhile these VBA forms are to be made redundant by moving them to .NET, exposed through COM Callable Wrapper (CCW).

Selling inventory items from SOP in another GP company

WarehousePicToday we started thinking about ways that we could sell items from one company's inventory using another company's SOP module in Dynamics GP.

Problem outlined

We have two mail order companies selling, for example pet care items and a DIY store selling tools. We have a desire to offer some of the overlapping items to both companies putting sections in the websites and printed catalogues of items from the other company. All the items are held in the same physical real warehouse but are held in the respective company’s inventory module in GP. Both companies are on the same instance of SQL server.

Example: Some DIY customers might like to buy a rabbit hutch as it is sort of on the edge of that stores proposition. The pet store might want to offer small tool kits to help with fitting cat flaps etc.

Ideas

We have thought of creating a inventory enquiry screen in SOP document entry of GP that looks at a union of the two inventories to bring back stock levels and details. Thus the sales staff can see the stock no matter which company it is in.

To raise a sales order for a customer users  could enter a non-inventoried item into SOP as a drop ship order against the other company as the supplier.

We could then use eConnect to create a purchase order and sales order in the other company for the items that were ordered. This integration would have to also handle if items are cancelled or quantities changed on the order -hmmm.

Fulfilment

We have a custom solution in our warehouse that I wrote that handles fulfilment. This could produce a consolidated pick list across the two companies for orders that have been created this way. Users could fulfil both the drop ship sales order and the normal sales order when the consolidated pick list is fulfilled. Thus to the customer the fact that two different companies are fulfilling the order would be transparent.

What do you think?

Have you done something similar before, or got other ideas/products that would help, if so leave a comment…

Automatic Monthly Sales Report – Reporting services

A quick to solve problem came up on last Friday. One of our customers needs as part of their contract with us, a list of all the sales order lines we have created for them for the previous month.This report is needed on the first of each month.

To solve this I chose my tool, reporting services and set to work.

As always everyone uses Dynamics GP differently, for this company the back order document type is not used. Thus all items remain on the same order throughout the lifecycle of the order. This makes reporting a relative doddle.
Customers are grouped using the Territory ID field by us so we can report on groups of customers. That results in the following SQL to drive the report, this was pasted into the query of a new report, and the body of the report created to show the results. We wanted to show voided orders, you can exclude them with a VOIDSTTS=0 in the where for each half.

WITH SOPWorkHist 
AS
(SELECT    SOP10100.SOPTYPE,  SOP10100.SOPNUMBE, SOP10100.DOCDATE,
    SOP10100.CUSTNMBR, SOP10100.CSTPONBR, SOP10100.CUSTNAME, SOP10200.ITEMNMBR, 
    SOP10200.ITEMDESC, SOP10200.OXTNDPRC, SOP10200.QUANTITY, 
    SOP10200.QTYCANCE, SOP10200.QTYTBAOR, IV00101.USCATVLS_6, 
    IV00101.USCATVLS_1
FROM         IV00101 WITH (NOLOCK) RIGHT OUTER JOIN
                      SOP10100 WITH (NOLOCK) INNER JOIN
                      SOP10200 WITH (NOLOCK) ON SOP10100.SOPNUMBE = SOP10200.SOPNUMBE 
                      AND SOP10100.SOPTYPE = SOP10200.SOPTYPE ON 
                      IV00101.ITEMNMBR = SOP10200.ITEMNMBR
WHERE     (SOP10100.DOCDATE > @StartDate) AND (SOP10100.DOCDATE < @EndDate) 
AND (SOP10100.SOPTYPE = 2)
AND SOP10100.CUSTNMBR IN(
SELECT CUSTNMBR FROM RM00101 WHERE  (RM00101.SALSTERR = @CustomerIdent))
UNION 
SELECT     SOP30200.SOPTYPE, SOP30200.SOPNUMBE, SOP30200.DOCDATE, 
           SOP30200.CUSTNMBR, SOP30200.CSTPONBR, SOP30200.CUSTNAME, SOP30300.ITEMNMBR, 
           SOP30300.ITEMDESC, SOP30300.OXTNDPRC, SOP30300.QUANTITY, SOP30300.QTYCANCE, 
           SOP30300.QTYTBAOR, 
           IV00101.USCATVLS_6, IV00101.USCATVLS_1
FROM         IV00101 WITH (NOLOCK) RIGHT OUTER JOIN
                      SOP30200 WITH (NOLOCK) INNER JOIN
                      SOP30300 WITH (NOLOCK) ON SOP30200.SOPTYPE = SOP30300.SOPTYPE 
                      AND SOP30200.SOPNUMBE = SOP30300.SOPNUMBE ON 
                      IV00101.ITEMNMBR = SOP30300.ITEMNMBR
WHERE     (SOP30200.DOCDATE > @StartDate) AND (SOP30200.DOCDATE < @ENDDate) 
AND (SOP30200.SOPTYPE = 2) 
AND SOP30200.CUSTNMBR IN(
SELECT CUSTNMBR FROM RM00101 WHERE  (RM00101.SALSTERR = @CustomerIdent)))
-- Now select what we require from above
select * from SOPWorkHist order by 3,2

This was fine. The next challenge was to automatically email this every month. To do this I simply set up a default parameter that calculated the dates for the previous month. If you live in the UK don’t fall into the trap of copy and paste. Many of the examples to get the first and last day of the month for reporting services expressions on the web are assuming US format dates.

For us I ensured the language setting property of the report was set to UK English.

For the parameters of the report, StartDate and EndDate had formulas entered as non-queried default values (you might like to check midnight boundary conditions here, not a worry for my requirement as orders only get entered during office hours):

StartDate:
= DateSerial(datevalue(Now().AddMonths(-1)).Year, datevalue(Now().AddMonths(-1)).Month ,1 )

and

EndDate:

=dateserial(datevalue(Now()).Year, datevalue(Now()).Month,1).AddMilliseconds(-1)

 

A default code was also set up for the CustomerIdent Parameter for this particular customer group.

This defaults the values using the reporting services expresssion to the first and last day of the previous month.

Finally the report had a schedule created for it that emails it every month on the first of that month to the recipients. The default values populate with the previous month’s first of the month and last day of the month, Job done.