Dynamics GP – Changing the Voucher Number of a Posted Payables Transaction

Introduction

Recently I had a situation where I had to change the voucher number of a posted payables document. There is no standard way to do this via the application however it was causing issues with a third party integration so we had to investigate how this could be done. To do this task I teamed up with a developer and we came up with the following solution.

Please note there may be additional tables/columns you would have to update. Hopefully this article will not only outline the solution we used, but also how you can track down any additional tables you may need to update that we didn’t.

Find the tables with a VCHRNMBR column

The first thing we did was to find all the Dynamics GP tables that contained the VCHNMBR column. To do this we used the script below:

SELECT col.NAME AS 'Column',
       tab.NAME AS 'Table'
FROM   sys.columns col
       INNER JOIN sys.tables tab
         ON col.object_id = tab.object_id
WHERE  col.NAME LIKE '%VCHRNMBR%' 

This gave us a full list of tables that contained the VCHRNMBR column.

Next we decided to find which of those columns contained the actual voucher number we needed to change.

Find the tables that contained our voucher number

The previous query gave us quite an extensive list of tables that needed to be updated, and we weren’t sure we would need to update all of those tables and columns. Therefore we ran a query to find all the tables that contained a column with our actual voucher number in. For this I found this excellent query which I’ve used many times before when troubleshooting data issues. (This query has been taken from this forum post over on stack overflow)


USE DATABASE_NAME
DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT ColumnName, ColumnValue FROM @Results

To run this on your system just changed the DATABASE_NAME to your Dynamics GP company database and also enter the string you wish to search in place of the SEARCH_TEXT.

We now had a list of tables and columns that contained the voucher number we needed to change.

The final update script

Pulling the information we had gleaned from the queries above, and using knowledge of their system and of the Dynamics GP table structure (for example the column name we needed to change in the apply table is APTVCHNM rather than VCHRNMBR) we used the script below to update the voucher number:

USE TWO
GO
/****** Object:  StoredProcedure [dbo].[ChangePMVoucherNumber]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ChangePMVoucherNumber]
	@OldVoucher	   AS VARCHAR(17),
	@NewVoucher        AS VARCHAR(17)

AS
BEGIN

	
	-- Example of execution:  exec ChangePMVoucherNumber '00000000000000398', '10000000000000398'

	

	-- Environment
	SET NOCOUNT ON
	SET DATEFORMAT DMY
	

        -- Declare variables
	DECLARE @DocType SMALLINT 


	-- Checks
	IF @OldVoucher IS NULL BEGIN 
		PRINT 'Old voucher must be specified'
		RETURN
	END
	IF @NewVoucher IS NULL BEGIN 
		PRINT 'New voucher must be specified'
		RETURN
	END
	IF @OldVoucher = @NewVoucher BEGIN 
		PRINT 'Old must differ from new voucher'
		RETURN
	END

	----Get document type from keys of original voucher
	--SELECT	@DocType = DOCTYPE
	--FROM	TWO.dbo.PM00400
	--WHERE	CNTRLNUM = @OldVoucher
	--AND	DOCTYPE = 1

	--IF @DocType IS NULL BEGIN 
	--	PRINT 'Voucher is not an invoice / Not Found'
	--	RETURN
	--END 



	-- Process
	BEGIN TRY

		BEGIN TRANSACTION A_Wrapper	

			Print 'Starting change of ' + @OldVoucher

			-- Query (PM Key Master File)
			UPDATE TWO.dbo.PM00400
			SET		CNTRLNUM = @NewVoucher
			WHERE	CNTRLNUM = @OldVoucher


			-- Query (PM Transaction OPEN File)
			UPDATE TWO.dbo.PM20000
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (Multicurrency Payables Transactions)
			UPDATE TWO.dbo.MC020103
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (Multicurrency PM Revaluation Activity)
			UPDATE TWO.dbo.MC020105
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (Purchasing Receipt History)
			UPDATE TWO.dbo.POP30300
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (PM Tax Invoices)
			UPDATE TWO.dbo.PM30800
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (PM Tax History File)
			UPDATE TWO.dbo.PM30700
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (PM Distribution WORK OPEN)
			UPDATE TWO.dbo.PM10100
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (PM Tax Work File)
			UPDATE TWO.dbo.PM10500
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (PM Paid Transaction History File)
			UPDATE TWO.dbo.PM30200
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (PM Distribution History File)
			UPDATE TWO.dbo.PM30600
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (PM History Removal Temporary File)
			UPDATE TWO.dbo.PM50100
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (PM Apply To History File)
			UPDATE  TWO.dbo.PM30300
			SET		APTVCHNM = @NewVoucher
			WHERE	APTVCHNM = @OldVoucher

			-- Query (PM Apply To WORK OPEN File)
			UPDATE TWO.dbo.PM10200
			SET		APTVCHNM = @NewVoucher
			WHERE	APTVCHNM = @OldVoucher

			-- Query (PM Payment Apply To Work File)
			UPDATE TWO.dbo.PM10201
			SET		APTVCHNM = @NewVoucher
			WHERE	APTVCHNM = @OldVoucher

			-- Query (PM Reprint Posting Journal Apply To File)
			UPDATE TWO.dbo.PM80500
			SET		APTVCHNM = @NewVoucher
			WHERE	APTVCHNM = @OldVoucher

			Print 'Ending change of ' + @OldVoucher

		COMMIT TRANSACTION A_Wrapper
			
	END TRY

	BEGIN CATCH
							
		ROLLBACK TRANSACTION A_Wrapper
		
		PRINT 'Issue changing voucher number ' + @OldVoucher + ' ' + ERROR_MESSAGE()
		
	END CATCH





END

To change a voucher number using the script you first create the procedure by executing the script and then execute the stored procedure as per below. (this would change voucher number 00000000000000398 to 10000000000000398)

exec ChangePMVoucherNumber '00000000000000398', '10000000000000398'

Conclusion

This script worked well but it was vitally important we did the research on other affected tables using the two scripts at the top of the article.

I also recommend doing this in a practice company prior to execution and also running reports such as a the Historical Aged Trial Balance before and after the voucher number has been changed.

Finally always have a goof full backup of your system that can be quickly restored if need be.

Thanks for reading!

Dynamics GP – Smartlist doesn’t load when selected off the menu

Introduction

Sometimes when you select “Smartlist” off the menu in Dynamics GP nothing appears to happen? Smartlist doesn’t load but you don’t get any errors. When you look at the Windows toolbar, smartlist appears to be open but you can’t select it. Its all very strange.

We get this scenario quite often on the support desk and although solutions are documented online I wanted one I could quickly refer to so decided to write this blog :).

I also wanted to show a solution I use to resolve this rather than using a SQL script which is also sometimes recommended.

Fixing using keyboard shortcuts

Most of the time when this happens smartlist has actually loaded however its opened off the viewable screen. To fix this, and bring it back on screen, you can use a series of keyboard shortcuts.

The exact steps I follow are:

  1. Click on smartlist on the menu bar (even though this won’t load smartlist you now have focus on it)
  2. Press ALT+SPACE
  3. Press M
  4. Press the right cursor key
  5. Wriggle the mouse to drag the smartlist window back on screen.

For clarity by pressing ALT+SPACE you are using the keyboard shortcut to load the menu on the smartlist window i.e.

Pressing M selects “Move” off the menu.

Pressing the right cursor moves the smartlist window slightly

Moving the mouse controls the window and you can bring it back on screen.

Conclusion

When this happens it can prove to be a pain so hopefully these simple steps will help anyone else struggling with this issue.

Thanks for reading!

Dynamics GP Vs Dynamics 365 Business Central – Place Purchase Transactions and Vendors on Hold

Introduction

This is another blog in a series I’ve been writing comparing functionality in Dynamics GP to Dynamics 365 Business Central.

In this post I’ll compare how purchase holds work in both Dynamics GP and Dynamics 365 Business Central. Both products offer solutions for applying holds at a document and Vendor level, however there are differences which we can explore.

Dynamics GP

In Dynamics GP you apply holds to individual transactions via the “Transactions > Purchasing > Holds” window.

By placing the document on hold you won’t be able to pay this via a manual payment or the automated “Select Cheques” routine (the Dynamics 365 Business Central equivalent being “Suggested Payment” batch job).

Therefore you must remove the Hold via this window in order to pay the document.

You can also produce a list of documents on Hold via a Smartlist prior to creating a payment run to assist with the process.

If you wish to prevent any documents from being paid on a particular Vendor you can tick the “Hold” flag on the Vendor Maintenance window as per below:

Placing the Vendor on Hold will prevent any payments being issued however you will still be able to enter and post transactions other than payments.

Dynamics 365 Business Central

To place a document on hold you go to “Vendor Ledger Entries” and click “Edit List”.

You then enter any 3 characters in the “On Hold” field to indicate the document is on hold. For example below I’ve entered the characters “GW” in the On Hold column.

Although you can use any 3 characters we tend to suggest using users initials as this gives the added benefit of knowing who has placed the document on hold. You can also filter the “Vendor Ledger Entries” either on initials or whether the field is blank or not to identify which documents are on hold (i.e. add a filter for On Hold <>’)

When you add the 3 characters to the On Hold field this excludes the document from being picked up by the “Suggest Payments” batch job in the Payment Journal however you can still pay and apply the document manually. This is different to Dynamics GP which requires the Hold flag to be removed before it can paid.

You can prevent any payment to the Vendor via the “Blocked” field on the Vendor Page.

You can prevent all payments from being made by selecting “Payment” or prevent any transaction from being posted on the Vendor by choosing “All”. This differs to Dynamics GP as even if a Vendor is placed on Hold you are only prevented from posting payments.

Conclusion

Both Dynamics products offer robust solutions for applying document and vendor holds. The main difference seems to be that Dynamics GP gives the user the ability to ensure a single document won’t be paid, even via a manual payment, and Dynamics 365 Business Central gives flexibility to prevent all document types being posted against a blocked Vendor.

Thanks for reading!