Dynamics NAV \ Business Central – “The Posting Date is not within your range of allowed posting dates” when posting a Purchase Invoice

Introduction

You receive the message “The Posting Date is not within your range of allowed posting dates” when trying to post a Purchase Invoice in Business Central.

According to the user setup the “Posting Date” of the document I’m posting is within the allowed posting range so why won’t the system allow me to post it?

Background – Value Entries

To provide a little more detail I’m trying to post a Purchase Invoice that I’ve matched to a Posted Purchase Receipt and I’ve increased the Unit Cost on the Purchase Invoice as the price has changed since the goods were received. I’ve also sold the items on a Sales Invoice before I’ve tried posting the Purchase Invoice.

Therefore, if we look at the value entries of this item prior to attempting to post the Purchase Invoice they are as follows

We have a value entry for the Posted Purchase Receipt showing a date of 12/05/2017 and a “Cost Amount (Expected)” of £10.00 (this the amount I used when posting the Purchase Receipt)

We also have a value entry for the Sales Invoice showing a Posting date of 25/05/2017 and a “Cost Amount (Expected)” of £10.00.

Details of the Purchase Invoice

The Purchase Invoice I’m posting is dated 01/06/2017 and I’ve amended the Unit Cost from the original £10.00 that pulled through from the Posted Purchase Receipt to £12.00


Now when I try and post this transaction, I receive the message

Therefore, just to confirm the Posting Date of the Purchase Invoice is within my allowed posting dates below is a screen shot of the User Setup window showing my Allow Posting Dates

The dates are also within the General Ledger allowed posting dates as shown below

Therefore, at first glance its not apparent why the system isn’t allowing me to post this document? The Purchase Invoice posting date is 01/06/17 and this is within my range of allowed posting dates?

The Issue – Automatic Cost Adjustment and Adjust Cost Item Entries

When posting the Purchase Invoice, the system has detected that the cost has changed from the Posted Purchase Receipt, and as this has been sold on a Sales Invoice, the cost of goods sold need adjusting.

The system therefore tries to post an adjustment using the Posting Date of the entry its adjusting (in this case the Sale Entry on the 25/05/17), which is in May, and as this falls outside of my posting range I receive the error “The Posting Date is not within your range of allowed Posting Dates”.

** Please note there is a complex method for selecting the Posting Date if the Posting Date of the entry being adjusted also fell outside of the “Allowed Posting Dates” of the General Ledger and also if “Inventory Periods” are being used. More information on this can be found in this Microsoft document https://docs.microsoft.com/en-us/previous-versions/dynamicsnav-2016/dn948192(v=nav.90)

** Its also worth noting I’m getting this message when posting the Purchase Invoice because the option “Automatic Cost Adjustment” is set to “Always” in Inventory Setup. This means the system checks for cost adjustments when you post the transaction. If this wasn’t set to “Always”, then depending on its setting its possible the document would post however when the “Adjust Cost Item Entries” batch job was subsequently run the error would occur.

See below for my Inventory Setup

Solution

There are two possible solutions to my issue here. The first is to change my “Allowed Posting Dates” in the “User Setup” to 25/05/2017 through to 30/06/2017. This will then include the posting date of the entry that will be adjusted.

Alternatively, I could change the “Allowed Posting Dates” in the General Ledger Setup to 01/06/2017 through to 30/06/2017. Then, as per the article I linked to, the system would use the date of 01/06/2017 for the adjustment entries, (i.e. the first open date in the General Ledger Setup) which does fall in my allowed periods to post to.

Therefore I’ll change my “Allowed Posting Dates” in the User Setup as per below

And now when I post the Purchase Invoice this is succecssful

If I now view the Value Entries you can see the adjustment entry created with a Posting Date of 25/05/17.

Conclusion

Although this is a simple example it shows why you may encounter this error when it seems the postings date configuration on the User Setup should allow a document to post.

Thanks for reading

Dynamics GP – Clear Activity in Bank Management – Delete from CBEU1020

I recently had an issue where a user was stuck in a batch in the Bank Management module. You can usually run the “Clear Activity” option to resolve the issue however on this occasion it didn’t work. I found I had to manually delete a row in an SQL table to clear the lock and allow the user access to the batch.

The exact issue they received is as follows:

With this error message the first thing to try is to clear activity and although this didn’t work on this occasion I’ll detail the steps below.

Select “Tools > Routines > Financial > Bank Management > Clear Activity”

Select “Clear Activity”

Ensure there are no users in Dynamics GP and click “Yes” to the message below. (rather than asking everyone to log out I usually just ensure there’s nobody doing any Bank Management activities)

After doing this you will be prompted with the message below confirming activity is cleared

However I found I still couldn’t access the batch. On investigation there is still some activity for the batch in the CBEU1020 table which needed clearing. I therefore ran the SQL query below in the company database to delete the row. (replace TWO18 with the name of your company database)

I could then access the batch.

Thanks for reading

Dynamics NAV \ Business Central – Expected Costing with Purchasing Transactions

Background

Within Dynamics NAV \ Business Central you can switch Expected Cost Posting to G/L both ON and OFF via the option below in Inventory Setup.

In this post I’ve been playing with this feature to see how things work and how the various postings differ to Dynamics GP. I also take a look at how the Value Entries in Inventory play a pivotal role in this. I end by taking a closer look at the SQL tables involved and how things fit together.

Expected Costs in Dynamics GP

When you receive goods via a Shipment transaction in Dynamics GP a Purchase Accrual is automatically created to a General Ledger accrual account to record the expected cost in the General Ledger. This account is generally taken from the Creditor Card as per below:

Dynamics GP – Creditor Account Maintenance

The opposite debit entry is taken from the Inventory Item card as per below:

Dynamics GP – Item Account Maintenance

Let’s add a Purchase Order Shipment transaction in Dynamics GP and see this in action:

Dynamics GP – Receiving Transaction Entry

As you can see from the screen shot above, I’m receiving one Inventory item, and this has created an accrual distribution crediting the 000-2111-00 accrued purchases account I specified on the creditor card. The balancing debit side is to the Inventory code that we specified on the Inventory Item card.

Now let’s see what happens when we invoice the Shipment:

Dynamics GP – Purchase Invoice Entry

Just as expected the accrual is reversed via a Debit entry to the 000-2111-00 accrued purchases account and the accounts payable is credited. Therefore, the balance in the accrual account is now nil.

There’s no way to disable this behaviour in Dynamics GP. When you post a “Shipment” for some Inventory Items General Ledger entries are always created. (however you can prevent the entries posting through to the General Ledger via the Posting Setup)

Expected Costs in Dynamics NAV \ Business Central

Before we look at Expected Costing in Dynamics NAV \ Business Central we first have to take a step back and look at the various inventory entries that are created when you post inventory transactions.

When you post an inventory transaction in Dynamics NAV \ Business Central the system creates a minimum of two inventory entries: an Item Ledger Entry and a Value Entry. The Item Ledger Entry records the change in quantity and the Value Entry records the change in inventory values. For the purposes of this post we just need to know that when posting Purchase receipts Value Entries are created for Expected Costs, and when you post Purchase Invoices, Value Entries are created for Actual Costs, and Expected Costs are reversed.

Expected Cost Posting to G/L – Switched ON

Unlike Dynamics GP you can switch ON and OFF accrual postings in Dynamics NAV \ Business Central via the Expected Cost Posting to G/L option in Inventory Setup. When you switch Expected Cost Posting to G/L ON interim accounts are used to post the accrual and inventory entries for Purchase receipt transactions.

The equivalent Dynamics GP accrued purchases account is called Invt. Accrual Acc. (Interim) and is specified in the General Posting Setup window and is selected based on the Posting Groups used on the Item and Creditor. (see my previous post for more details on the posting groups). I’ve highlighted this below

Dynamics NAV \ Business Central – General Posting Setup

The Inventory code for the debit side of the transaction is taken from the Inventory Posting Group and again is based on the combination of posting groups used. I’ve highlighted this below

Dynamics NAV \ Business Central – Inventory Posting Setup

The key difference here is Dynamics GP doesn’t use an interim Inventory account whereas Dynamics NAV \ Business Central does.

In my Cronus demo data, the option Expected Cost Posting to G/L is currently switched ON so let’s see how this works when I create a Purchase Order for an Inventory item and receive it.

Here’s my Purchase Order and after clicking Post I’m going to choose receive so I receive the items into the Inventory:

When I view the item I can see this has created the following Value Entry (Number 454) which shows the Cost Amount (Expected) and Expected Cost Posted to G/L both populated.

If I highlight the Value Entry and click Navigate > General Ledger I can see the G/L Entries associated with the Value Entry

As expected, we can see the 5510 Accrual account is being credited and the debit entry is posting to the 2111 “Interim” Inventory code.

Now let’s invoice the purchase order and take a look at the G/L entries. First I click Post and select Invoice on the Purchase Order. (Incidentally if I were to select Receive and Invoice the system recognises I’ve already received the items. It doesn’t receive them again)

This has created the a new Value Entry (Number 455) . There’s a few things to note here. Firstly the Cost Amount (Expected) and the Expected Cost to G/L have been reversed. Secondly the Cost Amount (Actual) and Cost Posted to G/L have been populated.

Again if I highlight the Value Entry and choose Navigate > General Ledger we can the G/L Entries associated with this Value Entry.

As you can see the original entries created via the Purchase Receipt have been reversed by posting a debit to the 5510 Inventory accrual account and a credit to the 2111 Interim Inventory account. The system has then posted new entries to the 2210 Resale items inventory account (debit) and the direct cost applied account (credit). (for more info on the direct cost applied account see my previous post).

So that’s it. Although there are a few extra distributions to Dynamics GP everything makes sense. Its also apparent that the Value Entries have a direct relation to the G/L entries that are created.

Expected Costing to G/L – Switched OFF

Now let’s see what happens when we post a Purchase Receipt with the Expected Cost Posting to G/L switched OFF. First I switch the option OFF and then create and post the Purchase Receipt

This has created the following Value Entries (Number 456)

The key thing to note here is that although the Cost Amount (Expected) is populated the Expected Cost Posted to G/L isn’t. This means no G/L Entries have been created. To prove this click Navigate > General Ledger to view any G/L Entries

Let’s now invoice the Purchase Order and see what happens:

This has created the following Value Entry

This Value Entry records the Cost Amount (Expected) being reversed and the Cost Amount (Actual) and Cost Posted to G/L are also populated. Therefore we get the following G/L Entries

As expected no expected cost postings have been created or reversed for this transaction.

Bonus – G/L Item Ledger Relation and Post Value to G/L SQL tables

An unexpected bonus of writing this post was the chance to geek out on some of the Dynamics NAV tables. Unlike Dynamics GP, I don’t have much of a grasp of the SQL tables in Dynamics NAV however while going through the various scenario’s I was curious about how a couple of things worked which encouraged me to dig a little deeper.

The first thing was how I was able to drill down on the G/L Entries from the Value Entries screen? This meant there must be a direct or indirect relationship between the tables.

After some digging I found the link was via the G_L – Item Ledger Relation table. Therefore writing the SQL query below enabled me to join the G/L Entries and Value Entries table to see all the details for Value Entry Number 455

The next thing I was curious about was what would happen if the Expected Cost Posting to G/L was switched from OFF to ON when there were lots of Purchase Receipts that hadn’t yet been invoiced?

I found the answer to this question lay in the two prompts you receive when you switch Expected Cost Posting to G/L from OFF to ON (or ON to OFF). Below are the two messages you get when toggling the setting

Dynamics NAV \ Business Central – Inventory Setup

As per the first message it seems when you switch the option Expected Cost Posting to G/L ON the system determines if the Actual Costs for the Purchase Receipt have been posted and if not a record is written for that Value Entry to the Post Value Entry to G_L SQL table. This has a link back to the Value Entry so the system knows to create the Expected Cost interim postings for this Value Entry.

To show this in action, I switched the Expected Cost Posting to G/L option OFF and queried the SQL table:

SQL Query on Post Value Entry to GL Table

As per the image above the SQL table is currently blank.

I then created a Purchase Order and received it as per below:

Dynamics NAV \ Business Central – Purchase Order

After posting this I queried the Post Value Entry to G/L SQL table again to see if any new rows had been added and the table was still blank

SQL Query on Post Value Entry to GL Table

I then checked the Value Entry for my receipt and as per the screen shot below the Cost Amount (Expected) is populated but the Expected Cost Posted to G/L is blank. As this is only the Purchase Receipt the Cost Posted to G/L is also zero.

Dynamics NAV \ Business Central – Value Entries

I then went back to Inventory Setup and switched Expected Cost Posting to G/L back ON and clicked YES to the prompt and now when I check the Post Value Entry to G/L table its populated as per below

SQL Query on Post Value Entry to GL Table

After toggling the Expected Cost Posting to G/L option to ON the system has determined that this Value Entry has no G/L Entries for the Expected Costs and has inserted a record into the Post Value Entry to G_L table with a direct link back to the Value Entry that was created when I posted my receipt.

Now if I run the Post Inventory Cost to G/L batch job as instructed in the second message G/L entries are created for the purchase receipt, the SQL table is cleared, and the Value Entry is updated. See below:

The report output of the “Post Inventory Cost to G/L” shows entries have been created:

Dynamics NAV \ Business Central – Post Inventory Cost to G/L

Below are the expected cost General Ledger entries created to the interim accounts. (in my previous example these were created immediately because I had Post Expected Costs to G/L switched ON)

Dynamics NAV \ Business Central – General Ledger Entries

And finally the Expected Cost Posted to G/L field on the Value Entry has been updated to show the General Ledger entries have been created and posted.

Dynamics NAV \ Business Central – Value Entries

If I now check the Post Value Entry to G_L table in SQL I can see its been cleared.

Incidentally if I were to switch OFF Expected Cost Posting to G/L before running the Post Inventory Cost to G/L batch job the SQL row is removed from the Post Value Entry to G_L table.

Conclusion

In conclusion I find the way Dynamics GP deals with expected costs to be a much more conventional and simple approach however there’s no doubting that Dynamics NAV \ Business Central gives more flexibility.

Although I don’t know much about the inner workings of Dynamics NAV \ Business Central it also seems to me that G/L Entries are created based on the Value Entries.

In another post I hope to look at how Expected Costs work with Sales Shipments and Sales Invoices.

Thanks for reading.

Dynamics NAV \ Business Central – Postings Group

Quick Introduction

All ERP systems aim to make data entry simple, fast and accurate. One way to achieve this is to default as much data as possible when the user is entering transactions, including the General Ledger distributions.  In this post I aim to show how Dynamics NAV \ Business Central defaults the General Ledger codes when entering a Sales Invoice using inventory Items. Also, as I come from a Dynamics GP background, I’ll start off by providing a quick overview of how Dynamics GP achieves this, to offer a comparison between the two Dynamics systems.

The Dynamics GP way

In Dynamics GP you enter default General Ledger codes on entities like customers, vendors, items, fixed assets and then the “catch all” which is the Posting Accounts Setup window.

Once this has been configured the General Ledger codes default automatically onto the transaction. For example, when creating a Sales Invoice for an inventory item usually the control account would default from the customer card and the revenue code would default from the inventory item (you can change this but usually it would be setup this way).
At this point the user can potentially edit and change the General Ledger codes on the transaction prior to posting thus overriding the system defaults. Being able to edit the General Ledger codes inside the transaction gives the user more flexibility however it can also introduce mistakes or errors. An example would be someone changing the control account, which would likely cause a reconciliation issue at month end.

Dynamics GP – Default SOP Distributions can be edited as per above

The Dynamics NAV \ Business Central way

From a Dynamics GP perspective things change quite dramatically when you look at how Dynamics NAV \ Business Central defaults the General Ledger codes. Instead of assigning specific General Ledger codes on customers, vendors, items, fixed assets, you assign Posting Groups to each of these entities. It’s the posting groups that have the General Ledger codes assigned and based on the combination of the posting groups used, general ledger postings are automatically performed when the transaction is posted. This means unlike Dynamics GP you can’t edit or change the default General Ledger codes prior to posting which gives less flexibility but there’s also less chance of mistakes being made.

So how do Dynamics NAV \ Business Central posting groups work?

There are two main types of posting groups – Specific and General.

Specific posting groups are used to default the control accounts. For example, I’ve assigned the specific Customer Posting Group “DOMESTIC” to the customer below:

Business Central – Customer Card

If we open the Customer Posting Group window and look at the setup, we can see when I post a transaction for this customer the General Ledger code 40400 will be used for the receivables control account.

Business Central – Customer Posting Groups

Moving on to the General Posting Groups things become a little more complex.

General posting groups can be split into two further groups – Business and Product. You assign “Business” posting groups to customers and vendors and “Product” postings groups to Items.

The combination of the General Business Posting group from the customer and the General Product Posting Group from the item determines the General Ledger codes that will be used. This is something that is best explained via an image, so I’ve included a screen shot below of the General Posting Setup window from my demo version of Business Central.

Business Central – General Posting Setup

As you can see the posting groups form a matrix and the combination of “Business” (labelled Gen. Bus. Posting Group in the window) and “Product” (labelled Gen. Prod. Posting Group in the window) determines the General Ledger codes used when you post a transaction.

Therefore, based on the General Posting Setup above, if a customer has been assigned a Gen. Bus Posting Group of DOMESTIC as per below:

Business Central – Customer Card

And the item they are buying has been assigned a Gen. Prod Posting Group of RETAIL as per below:

Business Central – Item Card

Then based on the General Posting Setup matrix defined in the General Posting Setup window the General Ledger sales account that will be used when the transaction is posted is 10200.

Defaulting the VAT Codes – VAT Posting Groups

A similar concept is used when Business Central is determining the VAT percentage and VAT General Ledger codes to be used. The matrix is defined in the VAT Posting Setup window as per below:

Business Central – VAT Posting Setup

So, in this example if the VAT Bus. Posting Group on the customer was “DOMESTIC” and the VAT Prod. Posting Group used on the Item is “STANDARD” the VAT percentage used would be 20 and the General Ledger code used would be 56100.

So there you have it. A very quick overview of how Dynamics NAV \ Business Central uses Posting Groups to create the General Ledger distributions when posting a Sales Invoice with inventory items.

Thanks for reading.

Dynamics 365 Business Central – How to use the Reconciliation Account feature to view the balances of your GL Codes prior to posting

Introduction

When posting journals there may be times when it would be helpful to see what affect the journal would have on balances prior to posting the entries.

In this post we’ll walk through how this can be achieved by using the “Reconciliation Account” feature on the GL account page.

Configure the GL Codes

The first thing you need to do is select the option “Reconciliation Account” on the GL code. You can do this by browsing to the “Chart of Accounts” and selecting a GL code and switching the option “Reconciliation Account” on.

In this example I’m switching the option on for 40500 Prepaid Rent and 30100 Rent Expense.

Create the Journal

Now I create a journal which is going to hit these two GL codes however before I jump in and click “Post” I want a sneak peak at what the balances would be if I were to post the journal.

Run the Test Report

In order to see what the balance of the GL codes would be I need to run the “Test Report” using the following selections:

Then, along with any posting errors, there’s an extra section showing the “Balance after Posting” for each GL code as per below:

In the scenario above this feature could have been especially useful, as I may have wanted the balance in my Pre Paid Rent account to be £0.00 after I’d posted this journal. If this was the case, I can now simply close the Test Report, change the amounts to £510.00 and then post. This would have then prevented an extra journal entry or a potential reversal entry.

Conclusion

This is a handy feature that is going to be espeically useful when posting accrual entries or correcting entries at a reporting period end. This is when accountants maybe checking balances and finalising accounts.

Thanks for reading!

Dynamics 365 Business Central – How to Change the Description on the Vendor Ledger Entry when Posting a Purchase Invoice

When posting a Purchase Invoice the description on the Vendor Ledger Entry defaults to the original unposted Invoice number.

For example when I post the Invoice below:

The Vendor Ledger Entries show the unposted Invoice number in the “Description” column:

If you would like to have a specific description for the Purchase Invoice in the Vendor Ledger Entries page you can personalise the Purchase Invoice page and add the “Posting Description” field.

This is shown below:

Now that we have access to the “Posting Description” on the Purchase Invoice page you can edit this and have a custom description when posting the Invoice.

For example I’ve changed the description below to “Septembers Rent Invoice”

And this is reflected on the Vendor Ledger Entry

I hope you find this useful.

Thanks for reading!

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!