eConnect “Customer is Inactive, you must Activate the customer to enter transactions”

Started looking at the eConnect integration with one of our websites today. We get the following error when the customer has been marked as inactive. Inactive in our world is something the finance department quantity, however we obviously want to still accept sales and ask questions later.

The problem is when the customer places the order we get the eConnect error which makes business sense,

WebDocumentTransport.FileProcessingException: Error processing order: 39646 ---> System.Exception: Microsoft.GreatPlains.eConnect.eConnectException: Sql procedure error codes returned: Error Number = 1790 Stored Procedure taSopHdrIvcInsert Error Description = Customer is Inactive, you must Activate the customer to enter transactions Node Identifier Parameters: taSopHdrIvcInsert SOPNUMBE = 3899646 SOPTYPE = 2 Related Error Code Parameters for Node : taSopHdrIvcInsert CUSTNMBR = TEST

 

First I checked to see if there was a field in the XML that overrides this behaviour, there was not so next I thought I could use the econnect pre and post stored procedures to get around this. The procedures, taSopHdrIvcInsertPre and taSopHdrIvcInsertPost, supposedly run before and after the document is processed.

As I was seeking a simple rapid solution, I bunged some TSQL in to stash away the customer status if it was inactive, set customer as active and then restore the status afterwards in the post procedure.

Annoyingly this does not work. The taSopHdrIvcInsertPre never executes if the customer in inactive. I guess the error occurs before econnect hands off to taSopHdrIvcInsertPre. I admit this seems like a acceptable behaviour, just not the one I was seeking.

So looks like I have run the SQL procedures I wrote, before and after the sales document is created myself. Alternatively I have to go back to plan A that was to do the change through eConnect itself that might be the more correct way to handle this anyway.

Dynamics GP – Copy address button using VBA & Modifier

Introduction - why add a copy button?

Dynamics GP Sales Order Processing (SOP) allows orders to be placed against debtors (customers). For each customer you can have any number of addresses stored, this address book can then be used by sales staff when an order is placed to avoid rekeying the information.

We needed to start storing an address per contact as the contact’s name is captured within the address block. Some delivery sites for national organisations are huge, many thousands of people are fed from one central goods in delivery point. This means that each contact email address is essentially the same bar for the contact name.

Requirement

To speed up order entry and address capture and accuracy, a requirement was identified to allow SOP users to copy a master address for these larger site. This would then merely require sales to add a contact name to the account master template address they copy, rather than manually copying and pasting the address each time.

Add a copy button & combo box to address windows

Using Microsoft Dynamics Modifier, select the address entry screens for GP. You might choose

DebtorAddressMaintenance & SalesShipToAddressEntry, for example. If you use 3rd party dictionaries, watch for alternatives to these in those dictionaries.

Drop a new button onto each form, rename the button btnCopy for this example.
Drop a drop down combobox to the form, name it ddlAddress

Modified GP Dynamics Address Form

When the form is loaded with data, the combobox will be filled with the list of address ID’s available for this customer. By default the default address ID will select itself, and the selected address ID will be used as the source for the copy address function. The copy address functionality will be invoked by the pressing of the btnCopy.

Save the modified form, if you do not currently have permissions set up for the modified form, please ensure your sers are pointed by security at the new form.

VBA behind the form

Now it must be remembered to add the window and fields to VBA that were created. Those are the button and the combobox, also needed is to add the customer number field and each of the address fields that are to be copied to VBA.

For this article I will assume we are working with the DebtorAddressMaintenance window. For this window when a new debtor number is entered, then the combobox that was added must be populated with the available AddressID’s for that customer. The combobox is also required to default to the customer address ID selected in the main customer maintenance card as the default ship address.

SQL

We must query the Dynamics GP database for all the address IDs for this customer stored in the “address book”.

ALTER PROCEDURE [yourschema].[SOP_GetCustomerAddressCodes]
   @CUSTNMBR  varchar(15)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT     RM00102.ADRSCODE, ISNULL(RM00101.PRSTADCD,'') 
        as SHIPPINGADRSCODE
    FROM RM00102
    LEFT JOIN 
         RM00101 
    ON RM00102.CUSTNMBR=RM00101.CUSTNMBR
    WHERE  
      (RM00102.CUSTNMBR = @CUSTNMBR) 
  
END
 

And to get a specific address

 

ALTER PROCEDURE [yourschema].[SOP_GetCustomerAddress]
   @CUSTNMBR  varchar(15),
   @ADRSCODE  varchar(15)    
AS
BEGIN
    SET NOCOUNT ON;
    SELECT CUSTNMBR, ADRSCODE, SLPRSNID, UPSZONE, SHIPMTHD,
           TAXSCHID, CNTCPRSN, ADDRESS1, ADDRESS2, ADDRESS3, COUNTRY, CITY, STATE, 
           ZIP, PHONE1, PHONE2, PHONE3, FAX, MODIFDT, CREATDDT, GPSFOINTEGRATIONID,
           INTEGRATIONSOURCE, INTEGRATIONID, CCode, DECLID, 
           LOCNCODE, SALSTERR, USERDEF1, USERDEF2, DEX_ROW_ID
    FROM   RM00102
    WHERE  (CUSTNMBR = @CUSTNMBR) 
       AND (ADRSCODE = @ADRSCODE)
 
END

Address Class

A class called clsSOPAddress is made in the class modules section of your VBA project as follows;

Option Explicit
Private m_CustomerNumber As String
Private m_AddressID As String
Private m_Contact As String
Private m_Address1 As String
Private m_Address2 As String
Private m_Address3 As String
Private m_City As String
Private m_County As String
Private m_PostCode As String
Private m_CountryCode As String
Private m_Country As String
Private m_Phone1 As String
Private m_Phone2 As String
Private m_Phone3 As String
Private m_Fax As String
Private m_CarrierZone As String
Private m_ShippingMethod As String
Private m_TaxScheduleID As String
Private m_SiteID As String
Private m_SalesPersonID As String
Private m_TerritoryID As String
Private m_UserDefined1 As String
Private m_UserDefined2 As String



Public Property Let CustomerNumber(CustNumber As String)
m_CustomerNumber = CustNumber
End Property
Public Property Get CustomerNumber() As String
CustomerNumber = m_CustomerNumber
End Property

Public Property Let AddressID(Adrid As String)
m_AddressID = Adrid
End Property
Public Property Get AddressID() As String
AddressID = m_AddressID
End Property

Public Property Get Contact() As String
Contact = m_Contact
End Property
Public Property Get Address1() As String
Address1 = m_Address1
End Property
Public Property Get Address2() As String
Address2 = m_Address2
End Property
Public Property Get Address3() As String
Address3 = m_Address3
End Property
Public Property Get City() As String
City = m_City
End Property
Public Property Get County() As String
County = m_County
End Property
Public Property Get Postcode() As String
Postcode = m_PostCode
End Property
Public Property Get CountryCode() As String
CountryCode = m_CountryCode
End Property
Public Property Get Country() As String
Country = m_Country
End Property
Public Property Get Phone1() As String
Phone1 = m_Phone1
End Property
Public Property Get Phone2() As String
Phone2 = m_Phone2
End Property
Public Property Get Phone3() As String
Phone3 = m_Phone3
End Property
Public Property Get Fax() As String
Fax = m_Fax
End Property
Public Property Get CarrierZone() As String
CarrierZone = m_CarrierZone
End Property
Public Property Get ShippingMethod() As String
ShippingMethod = m_ShippingMethod
End Property
Public Property Get TaxScheduleID() As String
TaxScheduleID = m_TaxScheduleID
End Property
Public Property Get SiteID() As String
SiteID = m_SiteID
End Property
Public Property Get SalespersonID() As String
SalespersonID = m_SalesPersonID
End Property
Public Property Get TerritoryID() As String
TerritoryID = m_TerritoryID
End Property
Public Property Get UserDefined1() As String
UserDefined1 = m_UserDefined1
End Property
Public Property Get UserDefined2() As String
UserDefined2 = m_UserDefined2
End Property

Public Sub LoadAddress()
On Error GoTo ErrorHandler
Dim SQLRec As New ADODB.Recordset 'holds recordset SQL

Dim Cmd1 As ADODB.Command
Set Cmd1 = New ADODB.Command

Dim SQLCon As ADODB.Connection
Set SQLCon = New ADODB.Connection

If SQLCon.State <> adStateOpen Then SQLCon.Open Microsoft_Dynamics_GP.GetConnectionStr
Cmd1.ActiveConnection = SQLCon
Cmd1.CommandText = "SOP_GetCustomerAddress"
Cmd1.CommandType = adCmdStoredProc
Dim prm1 As New ADODB.Parameter
Dim prm2 As New ADODB.Parameter

Set prm1 = Cmd1.CreateParameter("@CUSTNMBR", adVarChar, adParamInput, 15, Trim(Me.CustomerNumber))
Set prm2 = Cmd1.CreateParameter("@SOPNUMBE", adVarChar, adParamInput, 15, Trim(Me.AddressID))
With Cmd1
.Parameters.Append prm1
.Parameters.Append prm2
End With

SQLRec.Open Cmd1.Execute
If Not SQLRec.BOF And Not SQLRec.EOF Then
m_Contact = Trim(SQLRec.Fields("CNTCPRSN").Value)
m_Address1 = Trim(SQLRec.Fields("ADDRESS1").Value)
m_Address2 = Trim(SQLRec.Fields("ADDRESS2").Value)
m_Address3 = Trim(SQLRec.Fields("ADDRESS3").Value)
m_City = Trim(SQLRec.Fields("CITY").Value)
m_County = Trim(SQLRec.Fields("STATE").Value)
m_PostCode = Trim(SQLRec.Fields("ZIP").Value)
m_Phone1 = Trim(SQLRec.Fields("PHONE1").Value)
m_Phone2 = Trim(SQLRec.Fields("PHONE2").Value)
m_Phone3 = Trim(SQLRec.Fields("PHONE3").Value)
m_Fax = Trim(SQLRec.Fields("FAX").Value)
m_CountryCode = Trim(SQLRec.Fields("CCODE").Value)
m_Country = Trim(SQLRec.Fields("COUNTRY").Value)
m_CarrierZone = Trim(SQLRec.Fields("UPSZONE").Value)
m_ShippingMethod = Trim(SQLRec.Fields("SHIPMTHD").Value)
m_TaxScheduleID = Trim(SQLRec.Fields("TAXSCHID").Value)
m_SiteID = Trim(SQLRec.Fields("LOCNCODE").Value)
m_SalesPersonID = Trim(SQLRec.Fields("SLPRSNID").Value)
m_TerritoryID = Trim(SQLRec.Fields("SALSTERR").Value)
m_UserDefined1 = Trim(SQLRec.Fields("USERDEF1").Value)
m_UserDefined2 = Trim(SQLRec.Fields("USERDEF2").Value)


Else
'Address not found...
End If


'Close database connection and clean up
If CBool(SQLRec.State And adStateOpen) = True Then SQLRec.Close
Set SQLRec = Nothing

If CBool(SQLCon.State And adStateOpen) = True Then SQLCon.Close
Set SQLCon = Nothing

Set prm1 = Nothing
Set prm2 = Nothing
Set Cmd1 = Nothing

'Break before error hander
Exit Sub
ErrorHandler:
MsgBox "VBA Error trying to get address" + vbCrLf _
& vbCrLf & Err.Description, vbCritical + vbOKOnly, "Error in get address"
End Sub

Public Function GetAvailableAddressCodes() As String()
On Error GoTo ErrorHandler
Dim Results() As String
ReDim Results(0 To 0)
Dim SQLRec As New ADODB.Recordset 'holds recordset SQL

Dim Cmd1 As ADODB.Command
Set Cmd1 = New ADODB.Command

Dim SQLCon As ADODB.Connection
Set SQLCon = New ADODB.Connection

If SQLCon.State <> adStateOpen Then SQLCon.Open Microsoft_Dynamics_GP.GetConnectionStr
Cmd1.ActiveConnection = SQLCon
Cmd1.CommandText = "SOP_GetCustomerAddressCodes"
Cmd1.CommandType = adCmdStoredProc
Dim prm1 As New ADODB.Parameter

Set prm1 = Cmd1.CreateParameter("@CUSTNMBR", adVarChar, adParamInput, 15, Trim(Me.CustomerNumber))
With Cmd1
.Parameters.Append prm1
End With

SQLRec.Open Cmd1.Execute

Dim RecordCount As Integer
RecordCount = 0

Do While Not SQLRec.EOF

ReDim Preserve Results(RecordCount)
If SQLRec.Fields("ADRSCODE").Value = SQLRec.Fields("SHIPPINGADRSCODE").Value Then
Results(RecordCount) = "%" & SQLRec.Fields("ADRSCODE").Value
Else
Results(RecordCount) = SQLRec.Fields("ADRSCODE").Value
End If

RecordCount = RecordCount + 1
SQLRec.MoveNext
Loop

'Close database connection and clean up
If CBool(SQLRec.State And adStateOpen) = True Then SQLRec.Close
Set SQLRec = Nothing

If CBool(SQLCon.State And adStateOpen) = True Then SQLCon.Close
Set SQLCon = Nothing

Set prm1 = Nothing
Set Cmd1 = Nothing
GetAvailableAddressCodes = Results
'Break before error hander
Exit Function
ErrorHandler:
MsgBox "VBA Error trying to get address" + vbCrLf _
& vbCrLf & Err.Description, vbCritical + vbOKOnly, "Error in get address"
End Function

This class will handle fetching of the addresses. The default address is marked by a % that allows the combo box to by default select the address that contains that symbol and use it as default selection.

Now introduce a new module, utils, in the VBA project, into it add the following function for getting the list of addresses;

Public Function GetAddresses(DebtorID As String) As String()
On Error GoTo ErrorHandler

Dim osopAddress As New clsSOPAddress
osopAddress.CustomerNumber = DebtorID
GetAddresses = osopAddress.GetAvailableAddressCodes
Exit Function
ErrorHandler:
MsgBox "VBA Error trying to get available address ids." + vbCrLf _
+ " " + vbCrLf _
& vbCrLf & Err.Description, vbCritical + vbOKOnly, _
"Error in GetAddress"
End Function

We can now call this function from our address form in order to fill our combobox, We do this by using a unsupported (but fully working) method of calling dex code from VBA. In this case we are running this form from a 3rd party credit control form, hence the referenced dictionary.

Private Sub DebtorID_Changed()
On Error GoTo ErrorHandler
Dim CompilerApp As Object
Dim CompilerMessage As String
Dim CompilerError As Integer
Dim CompilerCommand As String

Set CompilerApp = CreateObject("Dynamics.Application")
CompilerCommand = ""
CompilerCommand = CompilerCommand & _
"clear '(L) ddlAddresses' of window RM_Customer_Address of form RM_Customer_Address;"_
& vbCrLf

ReDim oAddressList(0 To 0) 'initialise to zero for ubound checking
If Not DebtorID.Empty Then
oAddressList = utils.GetAddresses(DebtorID.Value)
Dim currentIndex As Integer
Dim defaultIndex As Integer
defaultIndex = -1
For currentIndex = LBound(oAddressList) To UBound(oAddressList)
If Left(oAddressList(currentIndex), 1) = "%" Then
CompilerCommand = CompilerCommand & "add item "" " & _
Mid(oAddressList(currentIndex), 2) & """, " & CStr(currentIndex) & vbCrLf
defaultIndex = currentIndex
Else
CompilerCommand = CompilerCommand & "add item "" " & oAddressList(currentIndex) _
& """, " & CStr(currentIndex) & vbCrLf
End If
CompilerCommand = CompilerCommand &_
" to '(L) ddlAddresses' of window RM_Customer_Address of form RM_Customer_Address;" _
& vbCrLf
Next currentIndex
End If
If defaultIndex = -1 Then defaultIndex = 0
CompilerCommand = CompilerCommand & _
"'(L) ddlAddresses' of window RM_Customer_Address of form RM_Customer_Address = "_
& Str(defaultIndex + 1) & ";" & vbCrLf

' Execute SanScript
CompilerApp.CurrentProductID = 2422 ' Nolans Credit Control
CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified"
CompilerError = CompilerApp.ExecuteSanscript(CompilerCommand, CompilerMessage)
If CompilerError <> 0 Then
MsgBox CompilerMessage
End If
'exit sub before the error handling kicks in
Exit Sub
ErrorHandler:
MsgBox "An Error occured on debtorid change." + vbCrLf + "" & vbCrLf & Err.Description,_
vbCritical + vbOKOnly, "Error in DebtorID.Changed"
End Sub

Finally the copy itself

When the uses presses btncopy the address associated with the addresscode shown by the combobox is got and it is used to populate the form fields.

Private oAddressList() As String ' holds addresslist for lookup

Private Sub Copy_BeforeUserChanged(KeepFocus As Boolean, _
CancelLogic As Boolean)
On Error GoTo ErrorHandler
If Not DebtorID.Empty Then
Dim oAddress As clsSOPAddress
If Left(oAddressList(ddlAddresses.Value - 1), 1) = "%" Then
Set oAddress = utils.GetCustomerAddress(Me.DebtorID, _
Mid(oAddressList(ddlAddresses.Value - 1), 2))
Else
Set oAddress = utils.GetCustomerAddress(Me.DebtorID, _
oAddressList(ddlAddresses.Value - 1))
End If

Contact.Value = oAddress.Contact
Address.Value = oAddress.Address1
Address1.Value = oAddress.Address2
Address2.Value = oAddress.Address3
City.Value = oAddress.City
County.Value = oAddress.County
Postcode.Value = oAddress.Postcode
Country.Value = oAddress.Country
CountryCode.Value = oAddress.CountryCode
Phone1.Value = oAddress.Phone1
Phone2.Value = oAddress.Phone2
Phone3.Value = oAddress.Phone3
Fax.Value = oAddress.Fax
CarrierZone.Value = oAddress.CarrierZone
ShippingMethod.Value = oAddress.ShippingMethod
TaxScheduleID.Value = oAddress.TaxScheduleID
SiteID.Value = oAddress.SiteID
SalespersonID.Value = oAddress.SalespersonID
TerritoryID.Value = oAddress.TerritoryID
UserDefined1.Value = oAddress.UserDefined1
End If
'exit sub before the error handling kicks in
Exit Sub

Crosstab Microsoft Dynamics GP price tables

Rows to columns for price breaks

No doubt your sales team want to go on the road with a human friendly version of your prices for the customers to read. It is possible to do this with a SQL table function to extract the prices from GP with price breaks. The following example assumes you know how many price breaks you have in your price lists and will result in output something like the following screen shot. These results may be squirted into excel with more columns as described by your business requirements.
Results of join with IV00101 showing description pulled in

Two key SQL server functions that many people I find are not familiar with but are vital for this kind of data manipulation are; “ROW_NUMBER()” and “PARTITION BY” , one way to learn is to dive in with an example.

GP Price Table

Natively the prices are held in the table IV00108 of your company database.

ITEMNMBR CURNCYID PRCLEVEL UOFM TOQTY FROMQTY UOMPRICE
WIRE100 Z-US$ RETAIL Foot 100 0.01 0.35
WIRE100 Z-US$ RETAIL Foot 999999999999.99 100.01 0.29
WIRE100 Z-US$ RETAIL Spool 999999999999.99 0.01 190
WIRE100 Z-US$ RETAIL Yard 999999999999.99 0.01 0.65
WIRE100 Z-US$ EXTPRCLVL Foot 999999999999.99 0.01 0
WIRE100 Z-US$ EXTPRCLVL Yard 999999999999.99 0.01 0
WIRE100 Z-US$ EXTPRCLVL Spool 999999999999.99 0.01 0

There is a row per “price point”. Each row contains, the item sku, Currency of the price list, price list name, unit of measure, quantity break range and price.

This is unreadable to humans once you get, say 15,000 products, five currencies and ten or so price levels. From experience, one company this solution is used with has 1,623,586 rows in the price table IV00108.

Table Partitioning

Firstly the rows are grouped together by the common factor each output row should be sharing. Each row in this example should have the same Item, Currency, Price Level and unit of measure. A row number is added for each successive row within this grouping;

SELECT 
ITEMNMBR,CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY, UOFM, TOQTY, ROW_NUMBER()
OVER(PARTITION BY
ITEMNMBR,PRCLEVEL, CURNCYID, UOFM
ORDER BY toqty ASC) AS 'RowNumber'
FROM iv00108 (NOLOCK) WHERE itemnmbr='WIRE100'

The above TSQL partitions the returned rows from IV00108 by ITEMNMBR,PRCLEVEL, CURNCYID, UOFM, for each row in the group a row number is generated by ROW_NUMBER() see the following output example. For this example, there are two quanity break columns for the prices of the “foot” unit of measure.
These are breaks of; 0.01+ and 100+, resulting in row numbers one and two for this unit of measure.

ITEMNMBR CURNCYID PRCLEVEL UOMPRICE FROMQTY UOFM TOQTY RowNumber
WIRE100 Z-US$ EXTPRCLVL 0.00000 0.01000

Foot

999999999999.99 1
WIRE100 Z-US$ EXTPRCLVL 0.00000 0.01000

Spool

999999999999.99 1
WIRE100 Z-US$ EXTPRCLVL 0.00000 0.01000

Yard

999999999999.99 1
WIRE100 Z-US$ RETAIL 0.35000 0.01000

Foot

100 1
WIRE100 Z-US$ RETAIL 0.29000 100.01000

Foot

999999999999.99 2
WIRE100 Z-US$ RETAIL 190.00000 0.01000

Spool

999999999999.99 1
WIRE100 Z-US$ RETAIL 0.65000 0.01000

Yard

999999999999.99 1

 

Now that we have the RowNumber, this can act as the anchor field to crosstab the data with. It makes sense to wrap this query in a common table expression (CTE) to clean it up. The output from the below should be identical to that above.

WITH PriceTable 
(ITEMNMBR, CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY,UOFM, TOQTY,[RowNumber]) AS
(SELECT ITEMNMBR,CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY, UOFM, toqty, ROW_NUMBER()
OVER(PARTITION BY
ITEMNMBR,PRCLEVEL, CURNCYID,UOFM
ORDER BY TOQTY ASC) AS 'RowNumber'
FROM iv00108 (NOLOCK) where itemnmbr='WIRE100'
)
SELECT * FROM PriceTable

Crosstabbing the Common Table Expression

Now building on the select statement from the CTE, it is crosstabbed by using CASE statements as shown below. All that has changed between these two scripts is the select out of the CTE. The select is also add “+” to the price from column results as well as some NULL handling to make the presentation cleaner for Excel should it end up there. This is optional, it might be more appropriate for other uses to keep the results as numeric values and do that kind of processing in the reporting tool.

WITH PriceTable
(ITEMNMBR, CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY,UOFM, TOQTY,[RowNumber]) AS
(SELECT ITEMNMBR,CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY, UOFM, toqty, ROW_NUMBER()
OVER(PARTITION BY
ITEMNMBR,PRCLEVEL, CURNCYID,UOFM
ORDER BY TOQTY ASC) AS 'RowNumber'
FROM iv00108 (NOLOCK) where itemnmbr='WIRE100'
)

select itemnmbr,
isnull(max(case when PriceTable.[RowNumber] = 1 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break1
, max(case when PriceTable.[RowNumber] = 1 then
uomprice end) as Price1
, isnull(max(case when PriceTable.[RowNumber] = 2 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break2
, max(case when PriceTable.[RowNumber] = 2 then
uomprice end) as Price2
,isnull( max(case when PriceTable.[RowNumber] = 3 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break3
, max(case when PriceTable.[RowNumber] = 3 then
uomprice end) as Price3
,isnull( max(case when PriceTable.[RowNumber] = 4 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break4
, max(case when PriceTable.[RowNumber] = 4 then
uomprice end) as Price4
, isnull(max(case when PriceTable.[RowNumber] = 5 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break5
, max(case when PriceTable.[RowNumber] = 5 then
uomprice end) as Price5
, isnull(max(case when PriceTable.[RowNumber] = 6 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break6
, max(case when PriceTable.[RowNumber] = 6 then
uomprice end) as Price6
from PriceTable
group by itemnmbr, curncyid, prclevel, UOFM;

 
 
The above TSQL generates the following table, where the rows have been transformed into columns by TSQL, just as required.
itemnmbr Break1   Price1 Break2 Price2 Break3 Price3 Break4 Price4 Break5 Price5 Break6 Price6
WIRE100 0+ 0.00000   NULL   NULL   NULL   NULL   NULL
WIRE100 0+ 0.00000   NULL   NULL   NULL   NULL   NULL
WIRE100 0+ 0.00000   NULL   NULL   NULL   NULL   NULL
WIRE100 0+ 0.35000 100+ 0.29000   NULL   NULL   NULL   NULL
WIRE100 0+ 190.00000   NULL   NULL   NULL   NULL   NULL
WIRE100 0+ 0.65000   NULL   NULL   NULL   NULL   NULL

Table valued function


Great there we have it, price table partitioned and crosstabbed. Lets not stop there as this is much more useful as a table valued function. This is achieved by wrapping the above SQL as shown below. Here we have decided that the calling script should provide the currency, pricelist, item pattern and unit of measure to export. Your application may differ and not require the expensive type conversions.

CREATE function [dbo].[Extract_PricesCrosstabTable] (
@CURNCYID varchar(15),
@PRCLEVEL varchar(11),
@ITEMPATTERN nvarchar(31) = '%',
@UOFM varchar(9) = '%'
)
RETURNS @retTable TABLE
(
[ITEMNMBR] [varchar](31) primary key NOT NULL,
[BREAK1] [varchar](255) NOT NULL,
[PRICE1] [numeric](19, 5) NULL,
[BREAK2] [varchar](255) NOT NULL,
[PRICE2] [numeric](19, 5) NULL,
[BREAK3] [varchar](255) NOT NULL,
[PRICE3] [numeric](19, 5) NULL,
[BREAK4] [varchar](255) NOT NULL,
[PRICE4] [numeric](19, 5) NULL,
[BREAK5] [varchar](255) NOT NULL,
[PRICE5] [numeric](19, 5) NULL,
[BREAK6] [varchar](255) NOT NULL,
[PRICE6] [numeric](19, 5) NULL
)
AS
BEGIN

WITH PriceTable
(ITEMNMBR, CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY,UOFM, TOQTY,[RowNumber]) AS
(SELECT ITEMNMBR,CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY, UOFM, toqty, ROW_NUMBER()
OVER(PARTITION BY
ITEMNMBR,PRCLEVEL, CURNCYID,UOFM
ORDER BY TOQTY ASC) AS 'RowNumber'
FROM iv00108 (NOLOCK) where itemnmbr like @ITEMPATTERN and PRCLEVEL= @PRCLEVEL
AND CURNCYID=@CURNCYID AND UOFM LIKE @UOFM
)
INSERT @retTable
select itemnmbr,
isnull(max(case when PriceTable.[RowNumber] = 1 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break1
, max(case when PriceTable.[RowNumber] = 1 then
uomprice end) as Price1
, isnull(max(case when PriceTable.[RowNumber] = 2 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break2
, max(case when PriceTable.[RowNumber] = 2 then
uomprice end) as Price2
,isnull( max(case when PriceTable.[RowNumber] = 3 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break3
, max(case when PriceTable.[RowNumber] = 3 then
uomprice end) as Price3
,isnull( max(case when PriceTable.[RowNumber] = 4 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break4
, max(case when PriceTable.[RowNumber] = 4 then
uomprice end) as Price4
, isnull(max(case when PriceTable.[RowNumber] = 5 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break5
, max(case when PriceTable.[RowNumber] = 5 then
uomprice end) as Price5
, isnull(max(case when PriceTable.[RowNumber] = 6 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break6
, max(case when PriceTable.[RowNumber] = 6 then
uomprice end) as Price6
from PriceTable
group by itemnmbr, curncyid, prclevel, UOFM;

RETURN

END;
 

Putting it to work

Now it is a table valued function, this allows a crosstabbed price table to be used as if it were a table. For example to add in the item description from the item master table IV00101, the following is used;

 
SELECT 
IV00101.ITEMDESC,
PricesCrossTab.*
From Extract_PricesCrosstabTable('Z-US$','RETAIL','WIRE%','Foot') PricesCrossTab
JOIN IV00101
ON PricesCrossTab.ITEMNMBR= IV00101.ITEMNMBR

Results of join with IV00101 showing description pulled in

The unit of measure has been used as a parameter here for selection, however by changing the schema of the table valued function returned table type to include unit of measure as part of the primary key, all units of measure can be returned. This is the foundations of some scripts that can be amended to produce the results that you require for your particular circumstances.

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.

  1. Public Function CreatePaymentTransactionXML(ByVal xmlOrder As XmlDocument) As XElement
  2.         Dim oCustomNode As XElement = _
  3.         <eConnect_InsertCardPayment>
  4.             <SOPNUMBE><%= CurrentSOPNumber %></SOPNUMBE>
  5.             <SOPTYPE><%= 2 %></SOPTYPE>
  6.             <VPSTxID><%= xmlOrder.SelectSingleNode("/order/Credit-Card-Tx-Code").InnerText %></VPSTxID>
  7.             <TxAuthNo><%= xmlOrder.SelectSingleNode("/order/Credit-Card-Auth-No").InnerText %></TxAuthNo>
  8.             <SecurityKey>9999</SecurityKey>
  9.             <VendorTxCode><%= xmlOrder.SelectSingleNode("/order/Credit-Card-Order-No").InnerText %></VendorTxCode>
  10.             <Amount><%= 0 %></Amount>
  11.             <Currency><%= xmlOrder.SelectSingleNode("/order/Currency-Id").InnerText %></Currency>
  12.             <VendorName><%= "mycompanyname" %></VendorName>
  13.         </eConnect_InsertCardPayment>
  14.         Return oCustomNode
  15.     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,“<eConnect_InsertCardPayment>”, 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 <eConnectProcessInfo> node to do this. The <eConnectProcessInfo> node should always immediately follow the transaction type node. It looks like this:
<eConnectProcessInfo>
   <eConnectProcsRunFirst>TRUE</eConnectProcsRunFirst>
</eConnectProcessInfo>

One of these process info nodes can be added per transaction in the XML document submitted, executing this in VB code looks like this;
  1. Dim oeConnectProcessInfo As New eConnectProcessInfo
  2.         oeConnectProcessInfo.eConnectProcsRunFirst = "TRUE"
  3.         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.
image

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