PM HATB report is not aligned with AP/GL Reconciliation

This question is answered

Hi,

I have a scenario where the AP/GL reconciliation matched perfectly when I run the routine as of Oct 31, 2013

However when I try to check the PM HATB report it shows a difference with $900

I found one payment transaction with matching amount that has been created in the following month (Nov 2013) and applied against one of the outstanding invoices listed in Oct 2013

Not sure if this relevant

***One important thing to say, the report correct itself when I query for Nov 2013, and it matches the balance in GL for the same period  

Any help please

Verified Answer
  • Okay, we are clear now.

    To get your transaction corrected, I suggest doing the following steps;

    • Post a corrective Journal Entry in General Ledger
    • If the invoice is fully applied, then you could fill in the distribution details in PM30600. Otherwise, fill in the distribution in PM10100
    • If applied, check that all the details are not corrupted on the transaction file (History - PM30200). Otherwise, check the open transaction table (Open - PM20000)
    • Run reconciliation

    If you are not quite aware of how the distribution should be recorded, then you could quite this step as the PM report doesn't read the document amounts from the distribution tables, rather from the transaction table.

    Another easy suggestion, get your document unapplied then voided. And re-enter the transaction all over again to ensure that all transaction tables are to be filled correctly.

    Further issues to be understood regarding the PM tables as associated with GL:

    • When an invoice is entered and saved in PM Transaction window, the PM10000| PM Transaction WORK File  will be filled out.
      • Distribution will be created in PM10100 | Open and Work Distribution
    • When the invoice is posted, the data will be moved from the table above into PM20000 | PM Transaction OPEN File.
      • Distribution will be kept in the same distribution table PM10100
      • A journal entry will be created in GL20000.  (Supposing the GL Setup is Post Though GL)
    • When the invoice is "fully" applied, the data from the table above will be moved into PM30200 | PM Distribution History File
      • Distribution will be moved into PM30600 | PM History Distribution.
      • The journal entry created in GL20000 has nothing to do with the apply. Once the end year closing is performed, journal entries in GL20000 will be moved into GL30000


    Best Regards,

    Mahmoud M. AlSaadi

    Dynamics GP Essentials |   mahmoudsaadi.blogspot.com

All Replies
  • I am not quite convinced, but it could be different GL posting versus Document date. You could make sure by checking the following script which shows detailed reconciliation per AP transaction,

    SQL Script for AP-GL Reconciliation

    Best Regards,

    Mahmoud M. AlSaadi

    Dynamics GP Essentials |   mahmoudsaadi.blogspot.com

  • Thanks Mahmoud!

    I managed to find out the transaction causing the issue. There is one invoice transaction posted in PM which has no distribution at all!!

    Is that event possible? my understanding that GP validates distributions before posting.

    How do I correct that?

  • Let me clarify one issue about payable distribution. Let me take the Payment as an example to illustrate how the system works.

    On the module level, Transactions and distribution are recorded in two different tables. On other hand, the General Ledger apparently keeps records on the journal entries associated with the transaction.

    • PM20000  | PM Transaction Open Work 
    • PM10100  | PM Distribution Open Work
    • GL20000  | Journal Entries

    When you open the inquiry, it will retrieved the transaction details as recorded within the "Transaction Table", when you click on the distribution button, it will retrieve the data stored within the "Distribution Table". Journal Entries can be reached at the financial inquiry window.

    Now, the question is ... Does GP allow a transaction to be posted without associated distribution ? The answer is simply  "No". But, the below clarifications are the possible scenarios for your case;

    1- Vendor Card Maintenance > Options > Maintain History: The first scenario in which "Distribution" is not kept is the setup at the vendor card. as shown below;

    2- Tools > Routines > Purchasing > Remove Distribution History: Supposing the the distribution has been checked to be kept from the setup. This window is used to delete the distribution for certain document number or range of documents.

    3- Delete on the Database level: through which a "delete" statement has been executed on the distribution table directly to remove the distribution.

    4- Posting Corruption: It could still be possible that the distribution has been missed because of a posting corruption, cause of network error or other interrupting factors.

    Best Regards,

    Mahmoud M. AlSaadi

    Dynamics GP Essentials |   mahmoudsaadi.blogspot.com

  • Once again appreciated your detailed explanation.

    All what you stated above make sense, however I could not think about any possibility other than posting interruption by network issue.

    Is there any existing routine like check links that may take care of such correction.

    if Not, Is it safe to enter this missing record from the backend into the related tables in AP & GL?

    Thank you,

  • Regardless of the different parameters of your case, the check links is always required to run in such cases, as it may create missing records in certain tables. But I'm not quite optimistic about it.

    Is the General Ledger Journal missing as well !

    Please let me know which of the tables I mentioned above having missing records, screen shots are highly appreciated.

    Best Regards,

    Mahmoud M. AlSaadi

    Dynamics GP Essentials |   mahmoudsaadi.blogspot.com

  • Yes, The distribution is missing in both PM10100 & GL20000.

    But should not the check to be on historical tables PM30600 & GL30000 since that invoice transaction has an applied payment against, both posted and moved from Open/Work to historical?

    However there is no reference for the GL distribution in GL30000 nor in PM30600

    Thank you,

  • Let us know of the check links results when completed.

    Best Regards,

    Mahmoud M. AlSaadi

    Dynamics GP Essentials |   mahmoudsaadi.blogspot.com

  • Check links has no impact

  • Okay, we are clear now.

    To get your transaction corrected, I suggest doing the following steps;

    • Post a corrective Journal Entry in General Ledger
    • If the invoice is fully applied, then you could fill in the distribution details in PM30600. Otherwise, fill in the distribution in PM10100
    • If applied, check that all the details are not corrupted on the transaction file (History - PM30200). Otherwise, check the open transaction table (Open - PM20000)
    • Run reconciliation

    If you are not quite aware of how the distribution should be recorded, then you could quite this step as the PM report doesn't read the document amounts from the distribution tables, rather from the transaction table.

    Another easy suggestion, get your document unapplied then voided. And re-enter the transaction all over again to ensure that all transaction tables are to be filled correctly.

    Further issues to be understood regarding the PM tables as associated with GL:

    • When an invoice is entered and saved in PM Transaction window, the PM10000| PM Transaction WORK File  will be filled out.
      • Distribution will be created in PM10100 | Open and Work Distribution
    • When the invoice is posted, the data will be moved from the table above into PM20000 | PM Transaction OPEN File.
      • Distribution will be kept in the same distribution table PM10100
      • A journal entry will be created in GL20000.  (Supposing the GL Setup is Post Though GL)
    • When the invoice is "fully" applied, the data from the table above will be moved into PM30200 | PM Distribution History File
      • Distribution will be moved into PM30600 | PM History Distribution.
      • The journal entry created in GL20000 has nothing to do with the apply. Once the end year closing is performed, journal entries in GL20000 will be moved into GL30000


    Best Regards,

    Mahmoud M. AlSaadi

    Dynamics GP Essentials |   mahmoudsaadi.blogspot.com

  • This is perfect!

    The easiest solution for me is to populate PM30600 for the missing distribution and to post a corrective GL entry in GL

    BTW, the field TRXSORCE in PM30600  is a system generated, it always match ORTRXSRC in GL20000/GL30000

    does it matter whatever value to populate in?

    Thank you,

  • The originating transaction source (ORTRXSRC) is very important. When you open the journal entry inquiry for instance, and click on the "Source Document" to drill down for document details, another inquiry window will pop displaying the originating document details from the module by using this field.

    Therefore, it is stored in both General Ledger and Originating Modules.

    Best Regards,

    Mahmoud M. AlSaadi

    Dynamics GP Essentials |   mahmoudsaadi.blogspot.com