Custom XML nodes with eConnect for Dynamics GP to call user stored procedures
Problem
Suppose for a moment you wish to transfer credit card authorisation ticket information or other supporting transaction information from a website into your Dynamics GP database.
Solution
You can piggyback on your eConnect integration rather than introducing your own integration. The minds behind eConnect thankfully provided some very easy to use extensibility points in the product.
It is really simple. By adding your own XML node to the XML document that is submitted to eConnect it is possible to move your data to the destination Dynamics database. A stored procedure is called there (stored proc name must match the XML node name) and in that stored procedure you can do anything at all -especially if you are into writing CLR code for SQL server.
Example
In the following example we feed an order created by a website, as an XML document (xmlOrder) into a function. Contained within the xml of teh order are the authorisation details for online credit card authorisations. These details need to end up in a custom table in the Dynamics GP company database. The function returns a LINQ XElement that can then be inserted into the eConnect XML that is subsequently submitted to Dynamics GP via the eConnect API. Lets have a look at the VB.NET.
Public Function CreatePaymentTransactionXML(ByVal xmlOrder As XmlDocument) As XElement
Dim oCustomNode As XElement = _
<eConnect_InsertCardPayment>
<SOPNUMBE><%= CurrentSOPNumber %></SOPNUMBE>
<SOPTYPE><%= 2 %></SOPTYPE>
<VPSTxID><%= xmlOrder.SelectSingleNode("/order/Credit-Card-Tx-Code").InnerText %></VPSTxID>
<TxAuthNo><%= xmlOrder.SelectSingleNode("/order/Credit-Card-Auth-No").InnerText %></TxAuthNo>
<SecurityKey>9999</SecurityKey>
<VendorTxCode><%= xmlOrder.SelectSingleNode("/order/Credit-Card-Order-No").InnerText %></VendorTxCode>
<Amount><%= 0 %></Amount>
<Currency><%= xmlOrder.SelectSingleNode("/order/Currency-Id").InnerText %></Currency>
<VendorName><%= "mycompanyname" %></VendorName>
</eConnect_InsertCardPayment>
Return oCustomNode
End Function
The example uses the VB.NET literal LINQ xml syntax, it is great for working with these kinds of small XML fragment constructs. The value for each node is pulled out of the sales order XML using XPath statements. Note the example is not yet complete, for example the amount field is tied to a static value of zero but it is functional enough to show the process.
Take notice of the name of the element,“”, this name is also the name of the stored procedure called in the company database. So now a stored procedure needs creating on the company database that will be the end point for this transaction. In this example that stored procedure will insert the data held in the XML to a row in a custom database table.
The custom XML fragment we have just created is inserted into the XML document submitted to eConnect. Add the custom XML node nested inside eConnect transaction type.
The developer can take control of when the procedures are called. The choices are before or after the eConnect procedures have executed. Use the node to do this. The node should always immediately follow the transaction type node. It looks like this:
TRUE
One of these process info nodes can be added per transaction in the XML document submitted, executing this in VB code looks like this;
Dim oeConnectProcessInfo As New eConnectProcessInfo
oeConnectProcessInfo.eConnectProcsRunFirst = "TRUE"
oeConnectType.SOPTransactionType(0).eConnectProcessInfo = oeConnectProcessInfo
Dynamics GP Company Custom Stored Procedure
Each XML element within our custom XML fragment is passed into the stored procedure as a parameter with the same name as shown below (proceeded with @I_v for input variable). You should therefore make certain your elements are named uniquely. Within the stored procedure, as a developer you can achieve what you want. In this case the payment card fulfilment information is merely inserted into a table for later processing or reporting.
CREATE PROCEDURE [dbo].[eConnect_InsertCardPayment]
( @I_vSOPNUMBE varchar(31),
@I_vSOPTYPE smallint,
@I_vVPSTxID nvarchar(50),
@I_vTxAuthNo nvarchar(50),
@I_vSecurityKey nvarchar(50),
@I_vVendorTxCode nvarchar(40),
@I_vAmount money =0,
@I_vCurrency char(3),
@I_vVendorName varchar(20),
@O_iErrorState int output, /* Return value: 0 = No Errors, Any Errors > 0 */
@oErrString varchar(255) output /* Return Error Code List */
)
AS
declare
@O_oErrorState int,
@iError int,
@iStatus smallint,
@iAddCodeErrState int
/*********************** Initialize locals *****************************************************/
select @O_iErrorState = 0,
@oErrString = '',
@iStatus = 0,
@iAddCodeErrState = 0
INSERT INTO [my_PaymentCardTransaction]
([TxType]
,[Status]
,[StatusDetail]
,[VPSTxID]
,[TxAuthNo]
,[SecurityKey]
,[VendorTxCode]
,[Amount]
,[Currency]
,[SOPTYPE]
,[SOPNUMBE]
,[VendorName]
,[ModifiedDate]
,[CreatedDate]
)
VALUES
('DEFERRED',
'OK',
'',
@I_vVPSTxID,
@I_vTxAuthNo,
@I_vSecurityKey,
@I_vVendorTxCode,
@I_vAmount,
@I_vCurrency,
@I_vSOPTYPE,
@I_vSOPNUMBE,
@I_vVendorName,
getdate(),
getdate()
)
return (@O_iErrorState)
But wait there is more
For all the eConnect operations there is a rudimentary “event model” made available via stored procedures. For example inserting a SOP Sales Document will cause the taSOPHdrIvcInsertPre and taSOPHdrIvcInsertPost procedures in the company database to be called before the record is inserted into SOP10100 and after respectively.
You are allowed to open up these procedures and code your own functionality in there. Perhaps you have some third party modifications that don’t natively support eConnect. You could set some database values in these third party tables using the taSOPHdrIvcInsertPost procedure, after the sales order has been inserted. As this is happening after it has been inserted you have all the defaulted values from the sales order document type/classes available to you in the procedure together with the data inserted from the supplied eConnect XML document.
Perhaps you need to validate the sales order information, say for example checking the customer has not supplied a duplicate PO reference to you. This could be done in the taSOPHdrIvcInsertPre, from where you could raise an error should validation fail.
Another useful technique is to combine the custom XML node technique discussed earlier with this event model. Use the custom node to insert the data into a cache table then pull it out and use it in earnest in the post event of the document transaction once all the defaults have been set. One benefit may be to keep the bulk of the sql in one place for clarity and ease of maintenance.
Another idea is to send an email to the sales team from inside the post stored procedure of the transaction, each time a sales order is created by eConnect. Sales staff now get a neat notification that there is a web sales order to process. If you are into writing CLR stuff in SQL you could go almost anywhere with this. Perhaps an alternative to the notification email would be to create a Microsoft Sharepoint task for someone to deal with the order and spin off a workflow to keep the process on track.
Go ahead use your imagination in our implantation, like I have in ours.
Further references
Recommend reading are the MSDN pages on this subject to get the full understanding and further examples of options for eConnectProcessInfo. Full schemas for eConnect are also provided there.
MSDN: eConnectProcessInfo http://msdn.microsoft.com/en-us/library/bb648359.aspx
MSDN Custom XML Nodes: http://msdn.microsoft.com/en-us/library/bb625125.aspx