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.

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.

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.

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;

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

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.

Mergers and acquisitions present new challenges in all parts of the business and in my most recent challenge it has presented the opportunity to solve the problem of allowing one GP company with inventory to resell a recently acquired GP company's stock. Both companies now members of the same holding group. As it was suspected that there would be a rapid high volume uptake of the new products, the system had to run itself.

For sound strategic, legal and financial reasons the GP companies have to be maintained as separate entities, they may be hosted on different servers in different physical locations.

How it was done

The main requirements are;

• Item enquiry and SOP entry staff should have sight of the stock held in the other company.
• The selling price in the reseller company is totally independent of the originating company selling prices.
• The cost price in the reseller company should be the FIFO cost price from the first company.
• Stock must be reserved (allocated) as soon as possible to the reseller company to guarantee stock promises to customers.

Resulting from a couple of meetings with the stakeholders and a white boarding session, we finally picked the the best fit solution from the various proposed ideas and thus crafting of the system began.

The core of the solution is to utilise stock adjustment out of the source company and a matching stock adjustments into the reseller company. Ensuring the FIFO cost from the out adjustment of the source company is used as the cost to adjust into the selling company with. This stock requisition system had to be automatic to deal with the potential volumes of transactions each day. Easy was to get eConnect to create the inventory transactions, but more challenging was the issue of how to get those inventory transactions posted automatically.

Automatic posting of Dynamic GP transactions

Automatic posting of transactions is an integration issue often faced. There is no API exposed to allow developers to post transactions in GP. This lack of a posting API is an unfortunate gap in the developer tools experience.

Recently discovered was the Post Master product by Envisage Software. this software runs as a .NET plug-in to Dynamics GP, allowing automated posting of batches. It can be controlled through a control table, providing the long sought after hook into the posting process. Post Master will be covered in another blog post, it is a well written robust solution for automatic posting of transaction batches in Microsoft Dynamics GP.

Controlling the requisition

A set of transaction tables have been created for controlling the process of requisitioning stock. These tables allow stock to be requested and track progress as the process creates the out adjustment, in adjustment and allocates up the requisitioned stock to the SOP order. It is obvious the the stock requisition system must be very robust, coping with servers restarting, connection problems and other unforeseen events. It has to be able to roll forward or backwards any transactions that get interrupted half way through otherwise stock will “leak” causing no end of horrors! The control tables give full insight into where the stock has come from and which SOP order the stock ended up allocated to.

The control tables are monitored and written to by a windows service that runs on a server. The server process constantly looks at the control tables for new stock requests. The service uses eConnect to create the stock transactions in each company and also controls posting of the stock in the two companies via the Post Master application. Finally the windows service allocates up the stock to the SOP document requesting stock, once it has been successfully requisitioned from the source company.

When the user saves the SOP document in the reseller company, the lines on the order are scanned to see if any are held by the other company. If found and stock is available in the other company, a stock requisition request is made to the windows service by adding rows to the stock requisition control tables.

An independently developed stock allocation routine is ran using custom processes every hour to allocate SOP documents. This allocation process also checks over to the source company for available stock and requests stock from the requisition system if available. This deals with requisitioning items that have a back ordered quantity as they are received by the source company.

Duplication of items

The items to be resold have to exist in the reseller company, integration manager helped duplicate the items over to the selling company. Going forward  critical fields like item description, bin locations are synced in SQL to the source company items so that any changes are correctly propagated over. Price list are created for the reseller company items independently so that they can be priced appropriately for that companies market. 

Fulfilment

Pick lists are printed with the locations and bins that were synchronised from the other company. As the items have been requisitioned into this reseller, they are fully fledged products in that company by this point. Thus fulfilment and despatch is no different for these items to any other in the company’s inventory.

Deallocated stock

Deallocated stock due to cancelled lines or changed quantities show up as available stock in the reseller. The reseller should never hold free stock. There should never be available stock in the reseller as any stock is always associated with an order and will be allocated, thus free stock needs to go back to the source company. Pushing stock back to the source company it cam from, is simply a reverse process to that to used to requisition the stock in the first place. This free stock sweep is done regularly to ensure stock goes back to the source company as soon as possible in order to allow it to be sold there if required.

Sock level visibility

Visibility of the stock is provided to the users through extra fields added to the SOP and Inventory enquiry windows. These windows display the stock available from the other company.

Returned items from customers

Returned items are dealt with manually at the moment. The return has to be processed through both companies to keep everything right and proper.

The first couple of months how it has gone…

The solution has been working well for a couple of months now, fully hands off, stock is merrily moving between companies with no admin or user intervention. Very satisfying solution indeed!

I’ve cut out some of the detail of this implementation as it is very specific to how GP is ran in our environment, but feel free to leave me a comment if you would like to know more about any aspects of this solution.

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.

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>

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

If you need to integrate with Microsoft Dynamics GP one of the options you may choose is to use the eConnect product. eConnect is an API that allows you to submit XML documents to Dynamics GP to perform CRUD operations on most of the document types in GP. Using .NET for integrations, if any issues/problems arise from submissions to eConnect,  via eConnect_EntryPoint or eConnect_Requester methods, then these errors are surfaced as eConnect exceptions. The .Message property of this class contains the error text.

The econnectException class returns the message from the eConnect stored procedure that originated the problem. A table of these messages is held in SQL server, DYNAMICS database, table taErrorCode. This table gives an idea of the error conditions you may not have thought possible and lets you be a bit more proactive at handling errors.

To see the contents of this table see this file:

Item does not allow back orders

Lets choose an example problem that you may experience. When submitting a SOP sales order document, normally you want any items that are out of stock to be back ordered. This is easy, set the QtyShrtOpt = 4 in the XML to back order the balance. However if you have a situation where you have a telesales team hammering in orders as well as a website taking orders, even with SQL replication you can occasionally get a scenario where a web order comes in for an item that has been set to disallow web orders and it no longer has enough stock to satisfy the web order. This may be due to latency in updating stock on the website for example. eConnect lets us know with the following exception: “Microsoft.GreatPlains.eConnect.eConnectException: Error Number = 4776”

Gracefully dealing with it

There a few ways I can think of to deal with this, the chosen one is to change the QtyShrtOpt = 6 for the line in question inorder to cancel the qty that can not be allocated from stock. It is wise to then set an order note to let the sales staff who will process the order know about the issue so it can be resolved with the customer, perhaps alternative item offered.

.NET code

A regular expression is used to parse the eConnect exception text. This allows easy detection of what error has occurred and extracts the item number for the order line raising the exception.

Having a list of item numbers with this issue allows us to change the XML of the document being submitted to alter the quantity shortage option flag to cancel the balance (option 6). Promoting LINQ for XML work, load the XML document into a XDocument (LINQ XML Document) class.

Now use a LINQ query to get all the elements that need the backorder option changing and change it to 6.

Now the altered XML document is resubmitted to Dynamics GP via eConnnect. If it fails this time there is an issue that we have not programmed for so it needs administrative intervention.

To load the xDocument back into the XMLDocument class for submission to eConnect,

Note on security

Beware exposing your ERP system to your website – if the website gets compromised, then so is your business. eConnect allows most of your business data to be altered and queried - this is something to be very careful of. With the implementation I created, the XML document is punched through the firewalls to a custom web service on the GP segment of the network. This web service only lets through the specific eConnect documents we want to allow through and only those that meet specific criteria to limit the attack potential.

Summary

By adding to this example, common errors your eConnect integration encounters could be eliminated so IT staff are prevented from spending time supporting disruptive day to day integration issues. Thus these problem cases are handed back to the process owner, be that; buyers, warehouse, or sales staff.