There are several reasons why you may need to change the date on an AP invoice in Dynamics GP but the most common reason is that it was simply entered incorrectly. This tends to occur quite frequently and unfortunately GP does not allow an invoice date to be modified once it has been created. Since an incorrect invoice date can affect reporting the best option is to update the date from the back-end using a SQL script.
Depending on the information provided to you by the user requesting the update, you may need to get creative with locating the correct invoice. The best case scenario would have the user providing you the actual document number of the invoice. However, there are cases where they may not have full visibility to the document number and may simply provide you the Vendor ID and incorrect invoice date. When I do not have the document number I use the following script to help me locate the record or records that need to be updated. Open purchase items are stored in the
PM20000 - PM Transaction Open File Purchasing table.
SELECT VendorID, DOCDATE, DUEDATE, Tax_Date, *
WHERE vendorID = ''
AND docnumbr = ''
--AND docdate > '/1/2018'
--AND YEAR(docdate) = '2017'
--AND month(docdate) = '11'
Notice I have several lines of the script commented out. It may not be necessary to use all the options available. In a best case scenario you will have the vendor ID as well as the document number provided for you. If this is the case just update the values and run the query and it should return the applicable record or records.
However, there are cases where the user is only able to provide the vendor ID and the incorrect invoice date. If this is the case you can simply remove the comments from one or more of the
AND statements to help narrow down the search. In my experience the year tends to be the culprit. If the year of the incorrect date is something like 2077 then it should be pretty easy to locate the record as it is unlikely there are multiple incorrect records for a single vendor with that document date year.
Once you have located the necessary information to identify the invoice that needs to be corrected, use the following script to update the necessary date fields. You will need to update the
Tax_Date columns of the
PM20000 table to correctly fix the date.
DECLARE @date DATETIME,
SET @vendor = '' --insert vendorID
SET @date = '' --insert correct date
SET @docnumber = '' --insert document number
SET DOCDATE = @date,
DUEDATE = @date,
Tax_Date = @date
WHERE VENDORID = @vendor
AND DOCNUMBR = @docnumber
The script requires you to provide the vendor ID, correct invoice date and the document number of the invoice. Once specified the values will be passed to the update script and updated in the database. After making the change it is best to have the user confirm the changes are being reflected on the front-end.