Cannot insert the value NULL into column ‘CONTACT’ error when clicking on Items List in Navigation Pane

Moving on from my previous article on a similar subject – see Cannot insert the value NULL into column ‘BASEUOFM’ error when clicking on Items List in Navigation Pane, I recently came across this error, Cannot insert the value NULL into column ‘CONTACT’ when clicking on the All Purchasing Transactions list under the Purchasing Navigation Pane option, after performing an upgrade from Microsoft Dynamics GP 9.0 to Microsoft Dynamics GP 2010 R2.

All Purchasing Transactions list error – Purchasing Navigation List

The name of the global temp table – in this case, tempdb.dbo.##2093338- varies in almost all cases, but the end result of the error is the same. The issue has been identified running Microsoft Dynamics GP 2010 RTM, SP1 or SP2.

Upon further review, the issue is due to bad data in the Vendor ID (VENDORID) column in the Purchasing Receipt History table (POP30300). In summary, if you have a purchasing receipt with a blank vendor ID or a vendor ID that does not exist in the Vendor Master table (PM00200), it will cause the Items list to fail with the error above.

The following query should help in identifying the offending record(s):

' Created by Mariano Gomez, MVP

'  This code is licensed under the Creative Commons 

'  Attribution-NonCommercial-ShareAlike 2.5 Generic license.

SELECT * FROM POP30300 WHERE VENDORID NOT IN (SELECT VENDORID FROM PM00200);

Once you have identified the record(s) causing the failure, you can use the Vendor Maintenance window to add the missing vendor or further study the issue to remove the offending receipts if necessary:

Vendor Maintenance window

Patrick Roth, Escalation Engineer with Microsoft and blogger at Developing for Dynamics GP, provides a full explanation of his troubleshooting method for this error at the Partner Online Technical Community forum:

GP2010 Purchasing List Error – Partner Online Technical Community forum

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Configuring Email for Sales and Purchasing Documents in Microsoft Dynamics GP 2010

Recently, I have been receiving a number of questions on the new Sales documents email functionality in Microsoft Dynamics GP 2010 and how to get it configured and working adequately. If you know me by now, I love to translate those questions into useful articles for the Microsoft Dynamics GP community at large.

Fortunately enough, the configuration process is not that difficult. Microsoft Dynamics GP 2010 allows this feature to be configured in a top-down approach which in turn ensures that all related areas of the application are email-aware.

As a first step, there is a setup needed to be done at the company level – MSDGP > Setup > Company > E-mail Settings, or if you prefer, click on Administration on the Navigation Bar, then locate the E-mail Settings link under the Setup web-part:

The first settings you will be able to configure is whether you want the actual document embedded as part of the email body or as an attachment to the email itself. My preference is the latter as it allows for a cleaner look when the email is received by the recipient. Microsoft Dynamics GP supports 4 different attachment formats, DOCX, HTML, PDF and XPS.

Since Microsoft Word and Adobe Acrobat are widely available and have been around for a while, I strongly suggest selecting at least these 2 options. XPS has only been around as a document standard since 2009 (released in 2006) and has native support on Windows Vista and Windows 7, hence recipients running Windows XP may experience some difficulty opening attachments in this format, especially if they are unaware of the XPS viewer’s download location.

NOTE: The IE-hosted XPS viewer and the XPS Document Writer are also available to Windows XP users with the download and installation of Microsoft .NET Framework 3.0.

Once you have defined the initial settings, you may then proceed to enable the email documents that can be submitted from your Microsoft Dynamics GP application. You will be able to define settings for both the Sales and the Purchasing series, as shown below:

Sales E-mail Setup

Purchasing E-mail Setup

The Sales E-mail Setup and Purchasing E-mail Setup windows can also be accessed under the respective series setup menus.

Of special importance is to setup the different labels for each document that will be enabled for submission. Also, if you are expecting the recipient to reply to your emails, select the mail boxes to which replies will be delivered using the Select Names button. My personal preference is to setup general mailboxes on your Microsoft Exchange Server or any other email hosting application. For example, your sales orders can have a general mailbox such as orders@fabrikam.com, or accounts_receivable@fabrikam.com if you are expecting replies to things like AR credit memos or debit memos from the recipients Accounts Payable team. You may do the same for purchasing. This overall approach ensures that emails are delivered to one location or an individual in charge of monitoring these replies.

Finally, you may further tailor specific settings for customer and vendor records, which will allow you to define at a granular level wheter the recipient will receive multiple attachments per email and the specific format for each document that will be attached to the email. You may also choose to enable or disable specific documents that may be submitted to the recipient.

Customer E-mail Options

Vendor E-mail Options

A feature I find particularly useful is, if you have customers or vendors that have size limits to their mailboxes, you can tailor Microsoft Dynamics GP to not allow file sizes above those limits.

I hope this review of the email capabilties of sales and purchasing documents has been useful. Please drop your comments and let me know whether you are using these features today in your business and if it has aliviated any of the past pains you’ve experienced by not having the functionality or in turn has created new problems.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Encumbrance vs PO Commitments

Two modules often confused are the Encumbrance and PO Commitments modules. They do have some similarities, but also have clear differences.

The basic differences are:

1. Encumbrance tracks history in Great Plains while PO Commitments does not.
2. Encumbrance doesn’t integrate with Requisition Management only PO Commitments. You can still use Encumbrance instead of Commitments, but you can’t track against a budget within Business Portal Requisition Management until you create a PO in Great Plains.
3. Encumbrance requires all budgets to be entered for all open fiscal years but PO Commitment’s budgets can be entered for any years you want to.

Their similarities:

1. They are initiated in the Purchase Order Entry window.
2. Budgets are for Purchase Orders only.
3. Can do multiple purchases simultaneously (mass).
4. Inquiries on current view.
5. No MC functionality.
6. Maintenance available.

Hope this helps shed some light.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Moving Items out of Inventory and into Fixed Assets

I love challenging customers (and partners) to use their Microsoft Dynamics GP system in very innovative ways. I like hearing things like ‘this cannot be done‘ or ‘there isn’t a way to do that‘. If there is a way, trust me, I will find it! One of these ways came in the form a simple question: how would you take an item that is already in inventory and move it to Fixed Assets?

Right off the start gate, I can think of 3 different ways to do this, but the solution I like the most because of its elegance and simplicity is as follows:

1. Setup an inventory site called FA and one called VIRTUAL or I/O (typically you will already have one of these setup if you have been using Inventory Control for a while).

2. Use the In-Transit Trasfer Transactions to transfer from your warehouse to the FA site using VIRTUAL as via site. Click on the Ship button to initate the ‘transfer’ to a Fixed Assets. In turn, this should create and in and out to your Inventory account — so far good!

3. Open the Receiving Transaction Entry window in GP and perform an In-transit Inventory receipt type.

4. Select the In-Transit Transfer number and select the item to be transferred to a Fixed Asset.

5. Open the Receiving Item Detail Entry window then mark the Capital Item option.

6. When you click the Capital Item checkbox, click on the expansion button (blue arrow) to open the FA PO Additional Information window to add the asset information.

7. Post the receipt.

Since the Purchase Order Processing module uses a Fixed Assets clearing account to capitalize an item, you should be fine (accounting wise) once the asset is created in FA and you run your FA GL posting routine. There you go! No need to create additional vendors or customers or perform extra gimmicks.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

From the Newsgroups: Multicurrency POP Receipts

Welcome to another weekly edition of the new From the Newsgroups blog entry. This week’s topic revolves around Multicurrency POP Receipts and a feature available in GP. As usual, no names will be given out, just the question and the answer.

Q: Functional currency is CAN [Ed: Canadian Dollars]. Item has a receipt that was for qty of 6000 each at .62 each USD currency. Vendor is setup as a US vendor. In the fifo layers it shows a receipt for 5999 at .62 each then another line for a qty of 1 at $12.57. Why is the system showing two lines for this receipt and breaking out the exchange all in one line. The exchange should spread over the qty of 6000 not qty 1.

We know there is an issue with split receipts when the item has more than 2 decimal points or there is landed cost involved. That is not the case here. This appears to be because of the exchange rate.

The response is as follows:

A: Good Day.

Thank you for using Forums.

With your exchange rate, it would appear that you have 6000 qty at an extended cost of $3731.95. Is this correct?

If so, the way GP handles a situation where the qty is not evenly divided into the extended cost is to produce a split receipt. The ‘logic’ behind it takes as many qty at the ‘unit cost’ as possible, and then puts the left over amount on the last single qty. This is the way this has worked as long as we’ve been using split receipts.

This is not so much a matter of putting the ‘exchange amount’ on one qty, but rather that we can’t spread the extended cost over the 6000 qty evenly. $3731.95 divided by 6000 is .6219916666666666666 repeating. We have to use some mechanism to have 6000 qty in our system at total cost of $3731.95. The method we use is a split reciept of 5999 @ .62 and 1 @ 12.57. This is standard GP functionality and has been for many versions.

If you have some suggestions on doing this in a different manner, please feel free to enter a product suggestion so that it can be reviewed for possible future releases.

Thanks!

Some of the features in GP are simply hidden to the naked eye unless you are dealing with a number of situations day in and day out. Multicurrency is one such situation.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Why my PO Number seems to skip randomly?

The opening goes something like this:

I upgraded from version X.XX [typically 7.x, 8.x] to Microsoft Dynamics GP 10.0 and now we are having a problem where the next PO number will sometimes jump back in sequence, often many numbers. What’s happening to our PO Numbers?

In my previous article, Why does my next document number change randomly? I gave some insight into why Microsoft Dynamics GP 10.0 seems to randomly skip document numbers, sometimes backwards. Here is more information on PO Numbers and another method of preventing this behavior.

In Microsoft Dynamics GP 10.0 Purchase Order Processing module, it is possible that a user may have a purchase order number that is completely different from the next number found in the Purchase Order Processing Setup window. Although the next number for purchase orders is set up in the Purchase Order Processing Setup window, Microsoft Dynamics GP will look for unused PO numbers that precede that number. The system will be able to re-use a purchase order number in the following circumstances:

1. If you are not keeping purchase order history and void a purchase order.
2. If you are not keeping purchase order history and delete a purchase order.
3. If you are keeping purchase order history and remove historical purchase orders.

If PO Numbers lower than the one specified are still available, Microsoft Dynamics GP will use this PO Number instead of the one you have set up.

To resolve this, you can use the Minimum PO/Receipt Number utility from the Professional Services Tools Library (PSTL). This tool will allow you to prevent Purchase Order Processing from defaulting the PO Number or Receipt Number to a number that is previous to the currently set up next PO.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

SQL – Retrieving the most recent receipt info for an item

It’s been quite a while since I have posted a SQL script, and it’s funny, because this is what I had in mind when I started out my blog. Shaun Childers posted a question on the Microsoft Dynamics GP public newsgroup, as follows:

“We are trying to create a script that will pull together our most recent
purchasing information only. The results should give all inventory items
with a qty on hand > 0, the current cost (we are average perpetual), the most
recent receipt number, the unit cost for that receipted item, the receipt date,
and the vendor name. I have tried to put this together, but have been
unsuccessful.”

At first, this query may not seem to complex, but when you start to analyze the information being requested, it becomes apparent that using a standard set based query is not going to be as simple. Luckily enough, we can take advatage of the latest T-SQL enhancements to use ranks and partitions on sets to deliver the requested query, as follows:

-- Created by: Mariano Gomez, MVP
SELECT A.ITEMNMBR
 , B.ITEMDESC
 , B.CURRCOST
 , ISNULL(R.receiptdate, '01/01/1900') AS LastReceiptDate
 , ISNULL(R.UNITCOST, 0.00) AS UNITCOST
 , ISNULL(R.VENDNAME, '') AS VENDNAME
FROM IV00102 A
 LEFT OUTER JOIN IV00101 B ON (A.ITEMNMBR = B.ITEMNMBR)
 LEFT OUTER JOIN (
  SELECT ITEMNMBR, UNITCOST, receiptdate, VENDNAME FROM (
   SELECT C.ITEMNMBR, C.UNITCOST, D.receiptdate, D.VENDNAME, RANK() OVER
    (PARTITION BY C.ITEMNMBR ORDER BY D.receiptdate DESC) AS RECEIPT_RANK
   FROM POP30310 C
    LEFT OUTER JOIN POP30300 D ON (C.POPRCTNM = D.POPRCTNM)
  ) Receipts WHERE RECEIPT_RANK = 1
 ) R ON (A.ITEMNMBR = R.ITEMNMBR)
WHERE (A.RCRDTYPE = 1) AND (A.QTYONHND > 0)

In Microsoft Dynamics GP v10, the POP Receipt history tables also hold In-Transit Transfer transactions. If you are looking to retrieve strictly vendor receipts, you may change the query as follows:

-- Created by: Mariano Gomez, MVP
SELECT A.ITEMNMBR
  , B.ITEMDESC
  , B.CURRCOST
  , ISNULL(R.receiptdate, '01/01/1900') AS LastReceiptDate
  , ISNULL(R.UNITCOST, 0.00) AS UNITCOST
  , ISNULL(R.VENDNAME, '') AS VENDNAME
FROM IV00102 A
 LEFT OUTER JOIN IV00101 B ON (A.ITEMNMBR = B.ITEMNMBR)
 LEFT OUTER JOIN (
  SELECT ITEMNMBR, UNITCOST, receiptdate, VENDNAME FROM (
   SELECT C.ITEMNMBR, C.UNITCOST, D.receiptdate, D.VENDNAME, RANK() OVER
     (PARTITION BY C.ITEMNMBR ORDER BY D.receiptdate DESC) AS RECEIPT_RANK
    FROM POP30310 C
     LEFT OUTER JOIN POP30300 D ON (C.POPRCTNM = D.POPRCTNM)
    WHERE D.POPTYPE  8
   ) Receipts WHERE RECEIPT_RANK = 1
  ) R ON (A.ITEMNMBR = R.ITEMNMBR)
WHERE (A.RCRDTYPE = 1) AND (A.QTYONHND > 0)

Hope you find this query useful.

Until next post,

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Mark Polino on Use Tax and Microsoft Dynamics GP

Use tax is one of those things that many companies using Microsoft Dynamics GP immediately resort to third-party solutions without really considering the systems out-of-the-box capabilities. The reasons may be various, among them, a lack of awareness of the GP’s capabilities to begin with.

I had the opportunity to actively participate in the review of a document on Use Tax written by fellow Dynamics GP MVP, Mark Polino. In his document, Mark extensively covers the methods for configuring, tracking, and accounting for use tax in Microsoft Dynamics GP.

Please make sure you download and read Mark’s document as he has dedicated a significant amount of time working through the techniques and methods with GP out of the box functionality.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

How to roll down SOP’s Requested Ship Date changes to line items and purchase orders – Part II

In the previous installment, you saw how to make changes to the Requested Ship Date roll down to the line items and linked purchase order documents via a SQL Server trigger.

This article will now show a VBA method combined with a SQL Server stored procedure call. The advantage of using VBA is to allow more interaction with the end-user and control the flow a bit more.

The following steps will get us there:

1) Add the Sales Transaction Entry window to VBA. Open the Sales Transaction Entry window and press CTRL + F11 on your keyboard.

2) Add the Document Type drop-down list, Document Number, and Customer ID fields to VBA. Press Shift + F11 on your keyboard to activate the visual cue. Once active, click on both fields. Press Shift + F11 to deactivate the visual cue.

3) Add the Sales Date Entry window to VBA. Click on the expansion button next to the Date field to open the Sales Date Entry window and press CTRL + F11 on your keyboard.

4) Add the Requested Ship Date field to VBA. Press Shift + F11 on your keyboard to activate the visual cue. Once active, click on the Requested Ship Date field. Press Shift + F11 to deactivate the visual cue.

5) Open the Visual Basic Editor. Press ALT + F11 on your keyboard. Locate the Microsoft Dynamics GP project in the Project Explorer window to the left of the screen. Expand to locate the SalesDateEntry (Window) object. Double-click to open the code editor.

6) Copy and paste the following code in the Editor:

'----------------------------------------------------------------------
' Created by Mariano Gomez
' Maximum Global Business, LLC - http://www.maximumglobalbusiness.com
' (C) 2008
' This code is provided "AS IS" with no warranties expressed or implied
'----------------------------------------------------------------------
Option Explicit

Private Sub RequestedShipDate_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
    Dim result As Integer
    Dim SOPNumber As String
    Dim SOPType As Integer

    Dim objConn As New ADODB.Connection
    Dim objCmd As New ADODB.Command
    Dim objRS As New ADODB.Recordset

    If Not (SalesTransactionEntry.DocumentNo.Empty) And Not (SalesTransactionEntry.CustomerID.Empty) Then
        ' Check to see if there are any line items entered for this transaction, otherwise

        Set objConn = UserInfoGet.CreateADOConnection()
       
        With objConn
           .CursorLocation = adUseClient
           .DefaultDatabase = UserInfoGet.IntercompanyID
        End With
       
        With objCmd
            .ActiveConnection = objConn
            .CommandType = adCmdText
            .CommandText = "SELECT * FROM SOP10100 WHERE SOPNUMBE = '" & SOPNumber & "' AND SOPTYPE = " & CStr(SOPType)
            Set objRS = .Execute
        End With
   
        If objRS.RecordCount > 0 Then
            ' We have some records to process in the SOP_Line_WORK
       
            result = MsgBox("Do you want to roll down changes to line items and linked POs?", vbYesNo, "Roll down Requested Ship Date")
            If result = vbYes Then
                SOPNumber = SalesTransactionEntry.DocumentNo
           
                ' Since the SOP Types in the DDL are not equivalent to the actual SOP Types stored in SOP10100
                ' we need to switch them around a bit
           
                Select Case SalesTransactionEntry.TypeTypeID
                    Case 1
                        SOPType = 1  'Quote
                    Case 2
                        SOPType = 2  'Order
                    Case 4
                        SOPType = 3  'Invoice
                    Case 5
                        SOPType = 4  'Return
                    Case 6
                        SOPType = 5  'Back Order
                    Case 3
                        SOPType = 6  'Fulfillment Order
                End Select
                           
                With objCmd
                    .CommandType = adCmdText
                    .CommandText = "EXEC usp_SOPRollDownReqShipDate '" & SOPNumber & "', " & CStr(SOPType) & ", '" & CStr(RequestedShipDate) & "'"
                    .Execute , , adExecuteNoRecords
                End With
           
                Set objCmd = Nothing
           
                ' close and destroy Recordset object
                objRS.Close
                Set objRS = Nothing
           
                ' close and destroy connection object
                objConn.Close
                Set objConn = Nothing
             End If

        End If
    End If
End Sub

7) Go to the Debug menu and select Compile Microsoft Dynamics GP.

8) The above code calls the dbo.usp_SOPRollDownReqShipDate stored procedure which must be created for each company database. Open SQL Server Management Studio and create a New Query. Copy and paste the following code to your query window:

/*
'----------------------------------------------------------------------
' Created by Mariano Gomez
' Maximum Global Business, LLC - http://www.maximumglobalbusiness.com
' (C) 2008
' This code is provided "AS IS" with no warranties expressed or implied
'----------------------------------------------------------------------
*/
USE [YourCompanyDB]
GO

/****** Object:  StoredProcedure [dbo].[usp_SOPRollDownReqShipDate]    Script Date: 12/22/2008 19:34:32 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SOPRollDownReqShipDate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SOPRollDownReqShipDate]
GO

/****** Object:  StoredProcedure [dbo].[usp_SOPRollDownReqShipDate]    Script Date: 12/22/2008 19:34:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_SOPRollDownReqShipDate]
 @SOPNumber char(31),
 @SOPType smallint,
 @ReqShipDate datetime
AS

IF ISNULL(@SOPNumber, '') = ''
 RETURN

BEGIN TRAN

  -- roll down to all items
  UPDATE SOP10200 WITH (ROWLOCK) SET ReqShipDate = @ReqShipDate
  WHERE SOPNUMBE = @SOPNumber and SOPTYPE = @SOPType

  -- updated the Required Date column for linked POs
  UPDATE A WITH (ROWLOCK) SET A.REQDATE = @ReqShipDate
  FROM POP10100 A INNER JOIN SOP60100 B ON A.PONUMBER = B.PONUMBER
  WHERE (B.SOPNUMBE = @SOPNumber) AND (B.SOPTYPE = @SOPType)

  -- updated the Required Date column for the line items on linked POs
  UPDATE A WITH (ROWLOCK) SET A.REQDATE = @ReqShipDate
  FROM POP10110 A INNER JOIN SOP60100 B ON (A.PONUMBER = B.PONUMBER) AND (A.ORD = B.ORD)
  WHERE (B.SOPNUMBE = @SOPNumber) AND (B.SOPTYPE = @SOPType)

 IF @@ERROR  0
  COMMIT TRAN
 ELSE
  ROLLBACK TRAN

GO

GRANT EXECUTE ON [dbo].[usp_SOPRollDownReqShipDate] TO DYNGRP
GO

9) Execute the query against each company database to be able to run the VBA customization across more than one company.

Testing the Customization

To test the customization, select an existing sales order. Change the Requested Ship Date. Close the sales order saving any changes. Reopen the order and all line items should now reflect the correct requested ship date.

You can choose to select a sales order that has a linked purchase order to test changes to the Required Date on the purchase order and purchase order line items.

Downloads

You can download the package files and stored procedure for this project below

Click here to download the usp_SOPRollDownReqShipDate stored procedure SQL file.
Click here to download the v10_SOP_Transaction_Entry package file.
Click here to download the v10_SOP_Transaction_Entry_VBA_References package file.

Until next post!

MG.-
Mariano Gomez, MIS, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

How to roll down changes to SOP’s Requested Ship Date to line items and purchase orders

Business Situation

A sales order entry clerk has been instructed to change all requested ship dates on a 100-line item sales order. This sales order is also linked to a purchase order for the 100 line items. However, the clerk has discovered that Dynamics GP will not allow these changes to take effect by changing only the Requested Ship Date on the transaction header and must go into each line item to accomodate the for the new request. In addition, the clerk must communicate to the company’s buyer that all line items on the PO must be changed to meet the new Required Date.

Solution

It would be much easier sometimes if certain Dynamics GP functionality was available out-of-the-box. However, that would leave a lot of us without a job :-).

Throughout the application, we get so many messages about rolling down changes across multiple records, but SOP seems to be one of those modules where rolling down changes to line items don’t seem to be a choice. Take for example Requested Ship Date field. Over and over, I have seem so many newsgroup posts asking for this feature. There are typically two approaches: a) you can create a SQL Server trigger to address the changes when the Requested Ship Date is updated from the SOP document header, or b) you can create a VBA customization that will ask to rolldown the changes, and if the user acknowledges, then run a stored procedure to make these changes happen.

I like both approaches, however approach (b) provides ample flexibility and allows for an interactive user experience. In this article, I will address option (a) and will follow up — in a second article — with option (b).

So let’s take a look at our SQL Server trigger first.

CREATE TRIGGER dbo.sopRollDownReqShipDate ON SOP10100 AFTER UPDATE AS
IF UPDATE(ReqShipDate)
BEGIN
 BEGIN TRAN

  -- roll down to all items
  UPDATE A WITH (ROWLOCK) SET A.ReqShipDate = I.ReqShipDate
  FROM SOP10200 A INNER JOIN INSERTED I ON (A.SOPNUMBE = I.SOPNUMBE) AND (A.SOPTYPE = I.SOPTYPE) AND (I.SOPTYPE IN (2, 3)) -- only orders and invoices

  -- updated the Required Date column for linked POs
  UPDATE A WITH (ROWLOCK) SET A.REQDATE = I.ReqShipDate
  FROM POP10100 A INNER JOIN SOP60100 B ON (A.PONUMBER = B.PONUMBER) 
   INNER JOIN INSERTED I ON (B.SOPNUMBE = I.SOPNUMBE) AND (B.SOPTYPE = I.SOPTYPE) AND (I.SOPTYPE IN (2, 3)) -- only orders and invoices

  -- updated the Required Date column for the line items on linked POs
  UPDATE A WITH (ROWLOCK) SET A.REQDATE = I.ReqShipDate
  FROM POP10110 A INNER JOIN SOP60100 B ON (A.PONUMBER = B.PONUMBER) AND (A.ORD = B.ORD)
   INNER JOIN INSERTED I ON (B.SOPNUMBE = I.SOPNUMBE) AND (B.SOPTYPE = I.SOPTYPE) AND (I.SOPTYPE IN (2, 3)) -- only orders and invoices

 IF @@ERROR  0
  COMMIT TRAN
 ELSE
  ROLLBACK TRAN
END
GO

Let study our trigger for a bit. By using a BEGIN TRAN and checking for errors before committing out transaction, with SQL Server’s @@ERROR global variable, we are ensuring that our trigger will work as a single block of code. This is, we are only committing all changes if the updates were all successful.

By performing an UPDATE A WITH (ROWLOCK), we are allowing our trigger exclusive access to the rows involved in the update. This will prevent other changes from taking place on these rows while our trigger attempts to change the records.

Finally, our DML trigger makes use of the INSERTED special table to account for all header records being modified at once. You may say, well sales orders and invoices are updated one at a time in GP. This is true, but if you have an integration that insert records in bulk as part of a transaction, say for example orders coming from a CRM system through a BizTalk orchestration, you may need to address all these orders as one transaction block instead of individually. Also, working with INSERTED prevents the use of SQL Server cursors and allow the use of a set-based approach to our implementation.

Please stay tuned for the follow up post!

Until next post!

MG.-
Mariano Gomez, MVP, MCP, PMP, MIS
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/