Please do not use this post to make sweeping changes to your tables without a full understanding of the inner workings of GP and the relationship of these tables and fields.
*****Please consider consulting Microsoft before making any changes*****
The client created a check with 9,450 Payables Transactions (Invoices and Credit Memos). They then proceeded to delete the check, but ended the GP Task before it could finish which caused a tremendous amount of data corruption in the GP User Interface and the SQL Tables.
The ability to rebuild the check in any form was not there. Nothing we did in the UI or the “basics” of the table manipulation was working (i.e. Checklinks, etc).
The Diagnosis and Determination:
I first needed to determine where the transactions might be and how to reset them into the correct tables.
I found some in the PM30200 (PM Paid Transaction History File) and some in the PM20000 (PM Transaction Open).
Just moving them back into the PM20000 was not working to be able to rebuild the check. I determined that there were numerous other triggers to be able to reset a transaction back to the original “never having run through the check build process” state.
I also ran a search on a “healthy” transaction to find which tables it should be in. (A transaction NOT included in any previous check batch or paid at all).
PM10201 (PM Payment Apply To Work File):
This table contains the “Check Number” and the amount to be paid on the check number. If there are any transactions in this table that are associated with the check you are trying to rebuild, you’ll need to delete them from this table.
There is no vendor ID in this table, so you’ll need to run any SELECT and DELETE statements on the PMNTNMBR (If known).
The PMNTNMBR in conjunction with the DOCNUMBR is the best way to find the correct data to delete.
This table also contains the data as to the Discounts taken and the Net Check Amount.
PM10200 (PM Apply To Work OPEN File):
This table contains the “Build” and “Apply” Information.
You’ll find the date of the check build under “Date1” and other apply information is available throughout.
To find the correct transactions on this you’ll need to query by VENDORID and APTODCNM (Apply to Document Number) and APFRDCNM (Apply From Document Number) to find all of the transactions.
This table also has the associated Voucher Number (Payment Number, in this case) which are an even more secure method of finding the transactions in question.
Query: SELECT * FROM PM10200 WHERE VENDORID = ‘<YourVendorID>’ and VCHRNMBR = ‘<ThePaymentNumberInQuestion>’.
Use the above statement modified to delete from this table if you find any transactions that match the build you are trying to reset.
PM20100 (PM Apply To OPEN OPEN Temporary File):
This table contains apply information for the Credit memos, etc.
If you want your build to apply the credit memos, you’ll need to remove them from this table so that they auto-apply to the transactions from the PM20000 table during the new check build.
This is the PM Open Transactions Table.
However, just making sure your transactions are moved back into this table from the PM30200 (if they are stuck there), or any other table you have found them in, will not be enough.
There are triggers within this table that will need to be reset.
**** I’ll state again that EXTREME caution will need to be taken when making these changes and adjustments to these instructions will need to be made for any given situation.****
BACKUP, BACKUP, BACKUP!!!
DOCAMNT vs CURTRXAM:
The CURTRXAM is the column that contains what GP considers the “Open” amount of these transactions. This column is updated during a check build to the DOCAMNT less any amount you have applied. So, to make the math easy: If you fully apply a $100.00 document during your check build process, then your DOCAMNT will be $100.00, but your CURTRXAM will be $0.00.
If the CURTRXAM is $0.00 for any of the documents in question, it will NOT be able to be built into a new batch, so will need to be corrected in the PM20000 table.
If you are SURE that the invoice has not been paid before in any part or portion, then the easiest query is:
UPDATE PM20000 SET CURTRXAM = DOCAMNT WHERE VENDORID = ‘<yourvendorid>’ AND DOCNUMBR = ‘<vendorsdocnumber>’.
If you prefer to use Voucher Numbers or DEX_ROW_ID, then please feel free to add any further AND statements to ensure you are updating the correct transactions.
If an invoice has been partially paid or applied to, you will need to determine what the remaining amount should be in CURTRXAM and update accordingly.
The DISTKNAM (Discount Taken Amount) will contain data if the discount taken has already been calculated during a check build process. After you review these for accuracy, reset this to what it needs to be – In this case I reset this to zero so that it would calculate again.
The DINVPDOF (Date Invoice Paid Off) will contain a date if the transaction is associated with a payment already.
The document will NOT Be included in a new check build if this column has a date in it. In my case, I reset the date to 1900-01-00 for the 9,450 documents.
Short Cuts For Massive Changes
There are a couple of short cuts I apply when working with massive amounts of non-contiguous data.
I run a select statement on the documents to narrow down to what I can with the documents in question then right click on the upper left corner of my results and select “Copy with Headers”
I paste that into Excel making any field format changes necessary to read the data. I then use Excel to further filter to the correct transactions. At which point I make the SYNTAX to use that is needed to query the tables.
Add a couple of columns to the spreadsheet (right after the searchable field you want) run the query on and in the first column for each row add the text:
OR APFRDCNM = ‘
(or, whichever field you are searching)
In the second column, add the formula:
=AB2&AA2&"'" Where AB2=the column your text from above is and where AA2 is the column containing the data you want to search for in SQL.
Gather all of your transactions like this, changing the column name each time you need to search under a different column name for the criteria you are looking for.
You’ll also use this formula to apply any Updates to tables for the correct documents/criteria.
While this post is by NO MEANS complete as to how to fix whatever has gone wrong with your data, it may help serve in understanding the data in the PM tables for check building.