Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / PM Key Master Table (P...
Microsoft Dynamics GP (Archived)

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

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

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

  • Bill Campbell Profile Picture
    Bill Campbell 22,647 on at
    RE: PM Key Master Table (PM00400) Rebuild in GP 2013 R2? Still an option?

    I am working on this and will let you know if I can fix it.

    Richard, I agree, the DBA will get a lashing for this one as soon as the Director finds out about this - right now the cost is going to several thousand dollars - just trying to help - will only go so far.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: PM Key Master Table (PM00400) Rebuild in GP 2013 R2? Still an option?

    May be wrong recall

    Regsrds,

    amtricks

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: PM Key Master Table (PM00400) Rebuild in GP 2013 R2? Still an option?

    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.

  • Bill Campbell Profile Picture
    Bill Campbell 22,647 on at
    RE: PM Key Master Table (PM00400) Rebuild in GP 2013 R2? Still an option?

    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?

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: PM Key Master Table (PM00400) Rebuild in GP 2013 R2? Still an option?

    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.

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: PM Key Master Table (PM00400) Rebuild in GP 2013 R2? Still an option?

    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!

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: PM Key Master Table (PM00400) Rebuild in GP 2013 R2? Still an option?

    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.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: PM Key Master Table (PM00400) Rebuild in GP 2013 R2? Still an option?

    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?

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: PM Key Master Table (PM00400) Rebuild in GP 2013 R2? Still an option?

    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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: PM Key Master Table (PM00400) Rebuild in GP 2013 R2? Still an option?

    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.  

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans