PM Key Master Table (PM00400) Rebuild in GP 2013 R2? Still an option?

(0) Share

Report

Posted on by Community Member

I know in version 10, at least I think I recall correctly, that you could rebuild the PM master key table by truncating the PM00400 table and running check links. Can still be done today? I tried doing just that in a test database and when I run check links (for transaction logical and historical logical) I get a duplicate key air for an invoice and credit memo with the same voucher number. As I understand it, as in past versions, you can have two documents that have the same voucher number as long as they do not have the same document type. This is the case in this situation, but when I run check links it treats them as duplicates and stops the process. I removed one of the documents by using transaction removal and check links completed.

Does check links no longer rebuild the PM00400 table if you truncate the it like it did in the past, or is my recollection incorrect?

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (1)

Richard Wheeler 75,848 Moderator on at

Like (0)

Report

The primary key for the PM00400 is CNTRLNUM and CNTRLTYP. As long as this multi-segment key is unique check links will run just fine. CNTRLNUM = VCHRNMBR. Were you running SQL Profiler or DEXSQL.LOG to determine why check links was stopping?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Richard, I used neither. I received an error that it was trying to add a specific voucher number that was a duplicate. I queried the tables and found two records with the same voucher number but one with a doctype of 1 and the other 5. I removed the 5 voucher and Check Links got past it to stop on another that was the same scenario. My plan was to run DEXSQL.LOG and Profiler but thought I would reach out first to see if anyone else ran into this.

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

You might want to check the index on your PM00400 table. It sounds like someone may have dropped the CNTRLTYP segment. Truncating the PM00400 table and then running check links is the way to rebuild it. This will drop and recreate the index. Please do a backup of your company database first.

ALTER TABLE [dbo].[PM00400] DROP CONSTRAINT [PKPM00400]

GO

ALTER TABLE [dbo].[PM00400] ADD CONSTRAINT [PKPM00400] PRIMARY KEY NONCLUSTERED

(

[CNTRLTYP] ASC,

[CNTRLNUM] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yeah. I thought the same thing and checked that. It is there. That said, the CNTRLTYP tags the state the transaction is in (0,work, open, history) doesn't it? Since both the Voucher and the Credit Memo are coming from the same history table it would assign it the same number and make it NOT unique wouldn't it? I wouldn't take into consideration the DOCTYPE. That's the part that doesn't make sense to me. Maybe I have my facts wrong in regards to what the CNTRLTYP is?

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

Here is what these two fields mean.

Control Number:          Voucher Number – WORK from the PM_Transaction WORK record.

Control Type:               Field values:

                                    0 = Voucher types

                                    1 = Payment types

                                    2 = Printed alignment forms.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

Ah! Ok. So there's the problem. There is a Credit Memo and an Invoice with same Voucher Number. Even though the DOCTYPE is different that isn't allowed. It might be allowed to have a Payment with the same Voucher Number in the table as an Invoice but not a Credit Memo. I confirmed that in the User Interface. Didn't allow it. The Invoice transaction has an extremely low DEX_ROW_ID and the Credit Memo is a few years after it. The first is in the year they implemented. I am guessing that someone imported directly to the tables. I also queried the PM00400 on the Voucher Number and the second iteration, the CM, is the only one in there. Makes me more confident the first was imported directly to bring in history or some similar reason.

The fact that payments are assigned Control Type of 1 and Credit Memos are given 0 proves that and violates the PK. Would you agree?

Thanks for you help on clarifying!

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

That is exactly correct. If the data was imported directly to the AP history tables they may have bypassed the PM00400 table so this problem was just waiting for someone to come along and discover it by running check links.

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

Maybe wrong place to post this question but I will start here, as the PM00400 comes into play.

I have a situation where the client entered payments and credit memos and then manually applied them to the invoices for a vendor. At the end of the day they determined that they had miss applied one - and it was causing the system to show an out of balance situation. Also we could not post the remaining documents as there was only wrong documents to post to.

So the 'young' DBA said, hey I know SQL and I know that I can remove the transaction from the PM30300 table. That will fix the problem.

Wrong - now we have the payment / apply missing but the transaction still show as they did before in the Historical and Regular Trial Balance.

So ( here is where PM00400 comes ) I thought to fix we might drop the PM Keys and have the system rebuild the keys and see the missing transaction noted or fixed or something - but nothing happened.

I saw 62 pages of notes about the Keys getting rebuilt, but not the one I needed. The HATB and Regular Trial Balance are still the same.

Any suggestions?

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

I think this 'young' DBA needs some old fashioned discipline. The PM30300 table is the History Apply To table. I can only assume this genius did not make a backup before deleting the PM30300 table? Rebuilding the PM00400 will only bring back the transactions not recreate all the apply information. I would restore a backup to a temp database and bring back as many records as you can to the PM30300 table. I still truncate the PM00400 just as always and run check links to bring the transactions back.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

May be wrong recall

Regsrds,

amtricks

Was this reply helpful? Yes No