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 365 Business Central – An Overview of the fields in the Bank Reconciliation page

When performing a bank reconciliation in Business Central I tend to only ever reference a few of the fields on the reconciliation page however I’m often asked what others are used for and how they are calculated.

As a consequence I’ve created the cheat sheet below which highlights what most of the fields are and therefore how they can be used.

You can click the image to enlarge it 🙂

I hope others find this useful.

Thanks for reading!

Dynamics 365 Business Central – Why didn’t the Reverse Transaction function in the General Ledger Entries page reverse all my General Journal?

Introduction

A client reported that when reversing a large journal via the “Reverse” option in the “General Ledger Entries” page only part of their original General Journal was reversed?

This stumped me and forced me to dig deeper into how the journal was posted. On investigation it seems the journal lines on the journal had been grouped, and each group has been given a different “Transaction No.” in the G/L Entry table. Then, when the reversal option had been selected, only those lines with the same “Transaction No” had reversed.

Its the first time I’ve come across this behaviour in one distinct journal, so in this post I’ll demonstrate how this happened and the correct way this should have been reversed. (i.e. I’d expect this for different document numbers but not the same document number)

The Journal

The clients journal contained many lines however I can recreate the scenario with a much simpler journal.

Below is a journal with five lines. All the lines have the same Posting Date and Document number and the journal balances to nil. This is essentially one journal which we want to post as one unit.

I’ll now post the journal and go and view this in the General Ledger Entries page. I filter on the document number and I can see the full journal. This is great and just what I expect 🙂

The Reversal

I now realise I’ve made a mistake and want to reverse the whole journal. Therefore, while still in the General Ledger Entries page, I select the top line and choose “Process > Reverse Transaction” from the menu however it only gives me the option to reverse a portion of the journal? (the first three lines of the original journal)

So what has happened here? Why is the system not offering to Reverse all the entries on this Document Number?

The Transaction No. column

As shown on the screen shot above it seems the “Reverse Transaction” option pulls back the G/L Entries based on a “Transaction No.”?

I’ve never seen this column before, and when I try and add this via personalisation to the “General Ledger Entries” page, its not an available column?

I’ll therefore dig deeper and look at this document number directly in the the G/L Entry table, and there I can see the journal has been grouped and each group has been given a separate “Transaction No.”

So now the question is why has a journal with the same Posting Date and Document Number been broken up and given two separate “Transaction No.”?

The answer lies in how I posted the journal. If I look back at my original journal I’d inadvertently balanced the journal in two sections.

It seems even though the journal lines have the same posting date, and more importantly the same document number, the system gave these a different “Transaction No” based on how its balanced part way through.

Finally, it seems the “Reverse Transaction” function pulls back the entries based on this “Transaction No.” which causes a complication if I wanted to reverse the whole Document Number.

Quickly Reverse all Entries

So how do I reverse all the lines?

In my example its quick and easy as the journal is small. I could stay in the “General Ledger Entries” page and select one of the first three lines, and click “Reverse Transaction” and then select the fourth line and use the same “Reverse Transaction” option. However what happens if the journal contained many lines that had been grouped into lots of different “Transaction No.”?

In this scenario you can reverse via the G/L Registers page.

As the G/L Register contains all the G/L entries in my journal I just click click “Reverse > Reverse Register” as per below

This gives me all the G/L Entries in my journal that I can then reverse.


Conclusion

It was great investigating and finding out more about how Business Central works in the background. In some ways you could argue this behaviour is advantageous, as you have the opportunity to only reverse part of one huge journal.

My biggest takeaway from this is when reversing an entry via the “Reverse Transaction” in the “General Ledger Entries” page ensure all the expected lines are being reversed.

Also, if you do want to reverse all the journal lines consider using the “Reverse Register” function on the “G/L Register” page rather than “Reverse Transaction”.

Thanks for reading!