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
Okay, we are clear now.
To get your transaction corrected, I suggest doing the following steps;
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:
Mahmoud M. AlSaadi
Dynamics GP Essentials | mahmoudsaadi.blogspot.com
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
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.
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.
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?
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.
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
Let us know of the check links results when completed.
Check links has no impact
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?
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.
Other Microsoft Sites
I'm a Customer
I'm a Partner
Follow Microsoft Dynamics