Screwed Allocations in Dynamics GP

SOP Entry

This is purely and observational post of a phenomenon we see in GP from time to time.

The telesales team perform data entry in the SOP screens at a lightening pace, tabbing through windows, hammering information into the screens very quickly while the customer verbalises the order to them. Over the years we regularly see inconsistencies between IV and SOP modules. Here is one that I captured that I’ve seen a few times.

The order default site is “1” and the user has no reason to sell from any location than “1”. There is no stock in location “5” nor any reason to look at that location. The order below is curious.

2017-03-29_09-44-36

I would say some how the user has entered the item order quantity, accidentally, into the SITE ID field, every time the I see this the quantity ordered is the same as the accidentally entered site id. Thus my assumption.

That alone is not odd, but look further…

2017-03-29_09-47-08

The stock has fully allocated to the order line.

2017-03-29_09-47-44

However looking above that item has no stock (or ever has had) in location 5. Indeed the location allocation shows zero allocated.

2017-03-29_09-49-47

Now above we look at location “1”, where the stock should have come from, and we see 5 allocated. If we drill into that allocation we find no documents allocated, however switching to location 5 shows our SOP document.

So it seems to me that the line originally was taking from location “1” and allocated, then somehow the user has managed to change the site ID without GP attempting to reallocate the stock. If it did reallocate it would have to back order the items as there is none to allocate in 5.

Then the order line has been saved with 5 as the site ID, leaving corrupt figures between SOP-IV modules.
I can only think there is a way or speed at which the events on the form don’t fire correctly or in the right sequence that allows this to happen.

Dynamics GP Stored procedure [smFormatStringsForExecs]

You’ve seen it often enough in GP stored procedures, but what is it doing?

EXEC @iStatus = smFormatStringsForExecs 
@I_vInputString = @I_charEndCustomer,
@O_cOutputString = @cEndCustomer OUTPUT,
@O_iErrorState = @O_iErrorState OUTPUT

The above code snippet is the common code pattern it appears in. I had always assumed, until today that this procedure was cleaning the input into the procedures for anti-SQL injection attack purposes, alas it seems not.

The procedure actually turns the passed string into a quoted string for use as parameters when building up SQL by concatenation within other GP stored procedures.

Example:

IF we pass the string 6252''5 002     (with the trailing spaces) into the procedure, this is what we find:

DECLARE    @return_value int,
@O_cOutputString char(255),
@O_iErrorState int

EXEC @return_value = [dbo].[smFormatStringsForExecs]
@I_vInputString = N'6252''5 002 ',
@O_cOutputString = @O_cOutputString OUTPUT,
@O_iErrorState = @O_iErrorState OUTPUT

SELECT @O_cOutputString as N'@O_cOutputString',
@O_iErrorState as N'@O_iErrorState'

SELECT 'Return Value' = @return_value

2017-03-10_09-43-34

So the procedure has taken any trailing space out and wrapped the passed string in quotes and doubled up any quotes that were in the string to delimit them. Thus the output of the procedure can be used in string concatenation to build a SQL script dynamically.

So you don’t want to use the output of this parameter directly or it won’t work:

Example
i.e. don’t do this with the output from the format strings procedure…

DELETE FROM RM00101 WHERE CUSTNMBR=@O_cOutputString

-as it will not match any customer numbers (unless your customer numbers are in quotes!).

Dynamics GP eConnect and handling exceptions under Web API

Beware when using eConnect with ASP.NET Web API.

slipping 

… as exceptions raised by eConnect will not be of type eConnect.Exception

thus using our normal typical catch block, such as:

catch(eConnectException ex)

will not work, because all eConnectExceptions will actually cause the following exception to be raised/returned:

System.NullReferenceException: Object reference not set to an instance of an object.

  at Microsoft.Dynamics.GP.eConnect.EventLogHelper.AddExceptionHeader(String action, Object[] inputParameters, StringBuilder errorString)

This is because there is a flaw in the eConnect, eConnect.EventLogHelper.CreateEventLogEntry method. It attempts to extract the Thread.CurrentPrincipal.Identity.Name as part of the output, but when eConnect is used in the context of ASP.NET Web API, this is null

As it is null this causes a new exception to be caused, the original exception superseded by the new System.NullReferenceException caused in the exception handler, that masks the original issue.

Explicitly setting the current threads identity name just before calling eConnect solves this problem and so allows the eConnect.Exception to be caught and processed as would normally be expected.

I discovered this whilst diagnosing a problem on the forums for someone, see: Econnect Exception Handling doesn't work in WEB API 2 application