Validating data after econnect integration in Dynamics GP

After ingesting a customer purchase order into GP to create a sales order, it is a biz requirement to check that the pricing provided by the customer matches that currently set up in GP pricing. This could be a tedious task for the sales user, drilling into each line of the order, checking the price against the current price in GP.

In this simple example, the customer submits in one currency and has a one column (qty break) pricelist in the default GP pricing modules (no enhanced pricing used).

SQL Solution

eConnect provides a SQL event procedure procedure [dbo].[taSopHdrIvcInsertPost] that is fired after a document is pushed through eConnect.

Placing the following SQL into that procedure, inserts a line into the order notes for every line of the order that has a price different to that currently defined in the customer price list. This allows the user to look in one place for this information.

This same idea could be applied to other validation purposes, checking against master data of any kind. It is always wise to do sanity checking of data for when it arrives from untrusted sources (such as customers ).

It would be better if this were done with in the code building the eConnect document, however sometimes a company may not have control of that code or they may need to put a temp fix in while the feature request is processed by a software development company.

UPDATE SY03900
SET SY03900.TXTFIELD = cast(TXTFIELD AS NVARCHAR(max)) 
                            + convert(VARCHAR(max), 
                            ISNULL((
                            SELECT convert(VARCHAR(max), CHAR(10) 
                                + 'Price variation:' 
                                + convert(VARCHAR(40), orderline.LNITMSEQ / 16384) 
                                + CHAR(9) 
                                + convert(CHAR(12), orderline.UNITPRCE) 
                                + CHAR(9) 
                                + convert(CHAR(12), customerPrice.UOMPRICE) 
                                + CHAR(9) 
                                + rtrim(orderline.ITEMNMBR) 
                                + CHAR(9) 
                                + rtrim(orderline.ITEMDESC) 
                                + CHAR(9)
                                ) AS [text()]
                FROM SOP10200 orderline
                LEFT JOIN IV00108 customerPrice ON orderline.ITEMNMBR = customerPrice.ITEMNMBR
                    AND customerPrice.PRCLEVEL = '{customerPriceLevel}'
                    AND customerPrice.CURNCYID = 'GBP'
                JOIN SOP10100 oh ON oh.SOPTYPE = orderline.SOPTYPE
                    AND oh.SOPNUMBE = orderline.SOPNUMBE
                WHERE customerPrice.UOMPRICE != orderline.UNITPRCE
                    AND orderline.SOPNUMBE = @I_vSOPNUMBE 
                    AND orderline.SOPTYPE = @I_vSOPTYPE
                FOR XML PATH('')
                ), ''))
WHERE NOTEINDX = (
        SELECT NOTEINDX
        FROM SOP10100
        WHERE SOPNUMBE = @I_vSOPNUMBE
            AND SOPTYPE = @I_vSOPTYPE
        )

The script assumes that there is already an order note present, in our case this is true, just change to an INSERT or MERGE statement, if that is more appropriate to your import. It also assumes currency GBP. Add some formatting to the prices to clear up the decimal places too, if you like, however the above script should give the foundations of something that can be adapted to your needs.