Microsoft Dynamics GP (formerly Great Plains) is a bit of a bear to do integration with. At first, you might think that it should be straightforward. Basically, it is a windows application with a SQL Server backend whose tables (albeit cryptically named) are updated by stored procedures. The problem begins to get deeper when you find out that all of the stored procedures are passed XML documents as parameters.
Microsoft tries to remedy this by offering eConnect (.Net wrapper over COM components that turn objects into the XML files that the procedures need) and the GP Web Services (add a layer). The issue comes, however, when you need some sort of functionality that either doesn’t provide you. Unsurprisingly, you don’t get very far until you exceed the out of the box functionality provided by those products.
The most recent problem that I’ve encountered at work surrounds purchase orders. We create two different kinds of purchase orders. The first is the straight forward purchase order where it is for one or more items and we expect to be invoiced from the vendor for the full amount of the purchase order immediately.
The second kind of purchase order that we create is called a Blanket PO. We create this kind of PO when we contract with a vendor for a rental. We know that a vendor will be billing us X amount of dollars every time period for a certain duration. For instance, if we are renting a widget for $10.00 a month for 6 months, we would create a PO for $60.00 with 6 line items of $10.00 each (each line representing a month). However, we only want to release the first line of the purchase order, since that is all that we are expecting to be invoiced for immediately. In the future, we will release each line as the month that they represent comes up.
We used eConnect to try to automatically create these POs. The problem, however, is that eConnect automatically releases all lines of the blanket PO upon creation. Our second problem is that the update does not expose this property for us to go back and update the lines to unreleased. Since Microsoft encrypts the contents of the stored procedures, we could not inspect or alter what was happening.
After troubleshooting on our end and working with our Microsoft Partner, we eventually had to call Microsoft. The support engineer on the phone confirmed that the procedure in question sets the Release column of the POP10110 table to 1 no matter what the input.
We knew that we could just issue a SQL statement to update the column back to 0, but were unsure what else may need to be affected when that column is updated. GP is notorious for many things needing to be interconnected and we didn’t want to find our POs in some weird state several months down the line. The Microsoft engineers were unsure if anything was affected, so they ran some tests and found that this column operates in isolation and we were free to issue our update statement.
So, after we call eConnect to create the blanket PO, we issue this SQL statement. The POP10110 table holds PO line items (POP10100 holds the PO Header), and in our case, we want to set the Release column all of the lines greater than 1 back to 0 and the Released_Date to ‘1/1/1900’ (the default for “no date given” in GP).
DECLARE @PONumber varchar(10) -- This is set here as an example, but you can put it in a proc -- or however you want to get the value in. SET @PONumber = 'PO-1234567' UPDATE dbo.POP10110 SET Release = 0, Released_Date = '1/1/1900' WHERE PONumber = @PONumber AND LineNumber > 1
Simple answer to a complex problem.