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, MVPSELECT 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 VENDNAMEFROM 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, MVPSELECT 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 VENDNAMEFROM 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 IfEnd Sub

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

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 ONGO

SET QUOTED_IDENTIFIER ONGO

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

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 DYNGRPGO

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 ASIF 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 TRANENDGO

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/

Welcome Frank Hamelly, MVP to the blogosphere!

Well, I believe my friend and fellow MVP, Frank Hamelly, has succumbed to the daily pressures I inflict on him and have turned out to the blogosphere as many of us have done throughout this year alone. Not only has he setup tent over at GP2TheMax, but he has written his first article on the all too confusing subject of Received not Invoice. Please join me in welcoming Frank, but more importantly, please stop by his new blog, read his articles, and enter a comment (or two, or three, or…) about his work.

One interesting fact about Frank: he is an aviation fanatic with an already astonishing 300 hours of flight. His already familiar picture was taken at the Empire State Aerosciencies Museum in Schenectady, NY.

Until next post!

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

New Article on MSDynamicsWorld – "When Cash Is King: 8 Strategies for Using Microsoft Dynamics GP to Wring Extra Money from Regular Transactions"

Hi folks! After a long writing hiatus from MSDynamicsWorld.com, my new article has finally made it to the headlines. In this edition, I discuss 8 comprehensive strategies to improve your purchasing to disbursement business process and match the Microsoft Dynamics GP application components required to effectively support the proposed strategies. As always, please let me know your thoughts and/or let me know what you would like to see posted on this site.

Until next post!

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

To Check or Not to Check: Understanding Landed Cost’s Invoice Match and Revalue Inventory Options in Microsoft Dynamics GP

Dynamics GP presents two selection options in the Landed Cost Maintenance screen: Invoice Match and Revalue Inventory. These two options have profound accounting effects in how inventory and purchases are tracked in GP. The following is provided as is and was posted by Marge Swanson, Senior Software Development Engineer at Microsoft in response to a user’s question on the Dynamics GP community board, but I felt it was important to rescue as it contains valuable information not found in the manuals or elsewhere:

You should determine whether or not to mark the Invoice Match checkbox based on how you want your distributions to be tracked for the Landed Cost. If you do not mark Invoice Match, the distributions will be reversed from the accrued purchases account used on the Shipment – which defaults from the Landed Cost card. If you mark Invoice Match, the distributions will be created with the Purchase Price Variance account on the Landed Cost Maintenance window.

This helps you track the variances to a separate account if you want to. If you also mark Revalue IV when you mark Invoice Match, the distributions will assigned to the inventory account associated with the item the landed cost is applied to. The cost basis for the item will also be updated for any cost variance on the Landed Cost.

Example: Invoice matching and distributions for landed costs

Marking the Invoice Match option for a landed cost record will affect account distributions. For example, suppose that a shipment is recorded for 10 items at $1 each. The landed cost uses the Flat Amount cost calculation method, and the flat amount is $0.50.

The distributions for the shipment would look like this:

Inventory                                  $10.50Accrued Purchases - Landed Cost            ($0.50)Accrued Purchases – Inventory             ($10.00)

Suppose that when the invoice is received, the cost of the goods is unchanged, but the landed cost has increased to $0.75. If Invoice Match is not marked, the account distributions would be as follows.

Accrued Purchases - Landed Cost             $0.75Accrued Purchases – Inventory              $10.00Accounts Payable                          ($10.75)

If Invoice Match is marked and the Revalue Inventory option is marked for the cost variance, the account distributions would be as follows:

Accrued Purchases - Landed Cost             $0.50Accrued Purchases - Inventory              $10.00Inventory                                   $0.25Accounts Payable                          ($10.75)

If Invoice Match is marked and Revalue Inventory option is not marked for the cost variance, the account distributions would be as follows:

Accrued Purchases - Landed Cost             $0.50Accrued Purchases - Inventory              $10.00Purchase Price Variance                     $0.25Accounts Payable                          ($10.75)

Until next post!

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

Automating Buyer ID field entry with logged in User ID in the PO Entry window in Microsoft Dynamics GP

Here is another very common request: how can the Buyer ID field entry be automated with the user ID currently logged into Microsoft Dynamics GP? This is certainly an important control feature in environments where security is key to auditors and systems administrators alike.

A simple VBA script can take care of this issue, as follows:

1) Add the Purchase Order Entry screen and the Buyer ID field to Visual Basic
2) Open the Visual Basic Editor
3) Locate Microsoft_Dynamics_GP > Microsoft Dynamics GP Objects
4) Double-click on the PurchaseOrderEntry (Window) object
5) Add the following code in the editor

' Created by Mariano Gomez, MVP' Code is provided "AS IS". No warranties express or implied'Private Sub BuyerID_BeforeGotFocus(CancelLogic As Boolean)  Dim usrinfObj As UserInfo  Dim usrID As String

  Set usrinfObj = VbaGlobal.UserInfoGet()  usrID = usrinfObj.UserID

  BuyerID = usrIDEnd Sub

I know what you are thinking… this script could use some enhancements:

1) You can add additional code to check whether the buyer ID already exist in the database prior to assigning the usrinfObj.UserID content to the usrID variable

2) Of course, all the ramifications of the non-existance of the buyer ID that goes along with programming best practices and application security.

Until next post!

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

New Article on MSDynamicsWorld: In-Transit Transfer Transactions

Dear readers,

Check my new article at MSDynamicsWorld.com: “Here’s a Creative Exercise you Probably Won’t Miss: Allocating Inventory in Transit Using Microsoft Dynamics GP”. The article recollects the old tricks used to manage in-transit inventory and explores the new In-Transit Transfer Transactions feature in Microsoft Dynamics GP 10.0.

Once again, thanks to David Gumpert, Managing Partner and Editorial Director at MSDynamicsWorld.com for allowing me the chance to exercise my second passion — writing.

Until next post!

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