Dynamics GP–Auto SOP POP commitments

I’d wrongly assumed it to be possible to commit a single sales line qty to multiple purchase order lines until it is fully committed. Frustratingly it does not work this way.

  • Any single sales line may only be linked to one purchase order line
  • Multiple sales lines can be linked to a single purchase order line
  • Units of measure must be the same on both sides, no buying boxes and selling each!
  • The check box must be set to allow commitments in the purchase order
  • The site IDs must match on both sides

This came about when I was developing a SQL script to automatically create the links between sales and purchase orders. As we accumulate sales orders we aggregate them into purchase orders against our suppliers adding in replenishment stock and stock required for manufacturing at purchase order time.

Once all the purchase orders have been generated ideally I want to link up the sales orders using identified priorities.

I also discovered that when the units of measure differ between the purchase and sales orders, a link is not permitted. This is all pants (technical term).

WilloWare Inc. have done some work in this area. Looks like they have their own table with a few forms over it that also hook into GP at critical points.

A new SOP-POP Multi-Link module gives you the ability to link a PO Line to a partial quantity of a sales line; link multiple PO Lines to the same sales line so that as different purchase orders are placed, or as a single PO is partially received, quantities can be allocated to one or more sales lines.  Additional features include: Batch ID’s on sales orders are automatically changed when they are fulfilled by the receipt of a linked PO Line.  The new module will integrate into Binary Stream so that split documents maintain existing SOP-POP Links. http://www.willoware.com/custom/files/DS0068.pdf

As we use our own custom bespoke SOP item allocation service, it is possible that I can cook something up to do this properly with all the flexibility we need. First I’ll wait to see what pricing Willoware come back with.

[updated 28th May 2015]

We decided we needed the flexibility that owning the code would give us, so have embarked on our own solution for this to provide a more flexible version of SOP-POP linking.

WCF Service on Server 2012

The page you are requesting cannot be served because of the extension 
configuration. If the page is a script, add a handler. If the file should be 
downloaded, add a MIME map.

My first experiences with Server 2012 had me puzzled for a few mins when trying to get some WCF service end points transferred from another older 2003 server.

It was solved by going into server manager, drilling down in the add roles and features to the server concerned, selecting Features. under the .NET Framework 4.5 features there was WCF Services, after checking the box and under that also selecting HTTP Activation to on, everything sprang to life.

There are also 3.5 versions of these too if the application is running on the older framework.



Progress messages from SQL Server to VB.NET/C# application

When executing long running queries on SQL server, the messages from PRINT statements do not return to the caller sometimes until the whole process is finished, or intermittently at best.

If running a long running process from the GUI or debugging in SQL server management studio, it can be desirable to let the user know where things are at and prevent them force quitting your application prematurely.

By using the following statement messages can be sent back immediately, note the NOWAIT, this forces the message back right away:

RAISERROR (N'working', 10,1) WITH NOWAIT

In the above example, 10 is the severity level of the error we are raising  and 1 the state. This is not severe enough to stop the statement running.

I use this all the time now for getting a feeling of security from seeing something happening on long running scripts.


With C#/VB.NET using ADO.NET to connect to the procedure, we have a “infoMessage” event that is raised whenever a message comes back from SQL. By handling this event we can proceed to update the user GUI with that message, or another message as required.

cn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
txtMessages.Text += "\n" + e.Message;



Regarding hashing passwords .NET & Rfc2898DeriveBytes

I live outside the world of oAuth and need to hash passwords for authentication

For goodness sake don’t be tempted to write your own hash! Research the latest advise as it changes as machines and cracking advances. Swathes of passwords have been stolen from compromised sites in the past, and have been cracked and sold or given away. Don’t let your site be the source of misery!

Use well known hashing algorithm

Use the .NET class Rfc2898DeriveBytes that implements PBKDF2 for password hashing. This uses iterations to make it computationally expensive for any brute force attacks.

Use salt

To prevent attackers reverse engineering your users’ passwords using rainbow tables, and other colourful techniques, use the above class with a random salt per user hash. You need to store the salt in your data store, its ok to store it concatenated with password hash.

Use good random salt with high amount of entropy

Use a decent random generator such as that provided by the RNGCryptoServiceProvider  in the System.Security.Cryptography namespace to generate your salt, there are degrees of randomness, remember random is pseudo random in the random class in .NET.

Load the CPU with iterations

Choose a good number of iterations when generating the hash. To future proof your implementation, also store the number of iterations used to generate the hash against each user, with the hash and salt, in the data store. This makes it possible to “turn up” the number of iterations as machines get faster in the future and not “spoil” the existing hashes (obviously the hashes would be upgraded after login in this scenario).

Risks of Denial of Service (Dos)

Denial of Service, be aware that for this protection you are on purpose introducing a computationally expensive routine to the authentication methods of the site. This exposes a possible denial of service attack by bombarding the login method with authentication requests, some sort self healing technique to limit such an attack is required. Perhaps if high traffic is detected, temporarily add a capture to the login page to limit the DoS impact.