Dynamics Community Forum Thread Details

AP Historical Aged Trial Balance Date Issue

I was recently struggling with why certain payments were not appearing on the AP HATB for certain dates. What I discovered was this.

Invoice Date: 09/15/14

Payment Date: 01/06/15

Payment is applied to this invoice on 12/31/14.

When this payment was applied the Posting Date and the Apply Posting Date were set to 12/31/14.

If I print the AP HATB for any date between 01/01/15 and 01/05/15 this invoice will appear as unpaid. It is not until I set the date to 01/06/15 or later that it is not on the report.

Now if I reversed the dates so that the document date of the payment is before the Posting Date and the Apply Posting Date, the invoice comes off the report as expected. So my question here is that it appears that the Posting Date and Apply Posting Dates are not used at all on the AP HTB report. Are those fields used on any report or screen. Is there any way to view those dates in GP? I can see then in the PM30300 table using SQL but no where in GP can I find a screen or report that shows those dates.

I have a blog article on what all of those dates mean in several of the payables tables. Your discovery about the apply date is correct. The system relies on the apply date for the historical aged trial balance. Less obvious is that the apply did is also the date that determines when an amount will be reported on a 1099. You may have cut the check to the vendor, but until it's applied to a 1099 transaction, it isn't considered a 1099 payment. The link to my article is here: dynamicsconfessions.blogspot.com/.../whats-in-date-pm-transactions.html

But, because links bother me when I'm looking for answers (because they get outdated) I'm going to copy the entire article in this reply. I hope this is helpful.

Recently, I worked with a group that needed to make changes to the dates of some of their posted payables transactions. Of course, you can’t do that through the user interface, so it was SQL to the rescue. While the initial job sounded easy, they were astonished to see just how many different date fields were in the tables. This post will review the various date fields in the following three tables:

Physical Name	Display Name	Date Fields
PM20000	PM Transaction OPEN File	10
PM30200	PM Paid Transaction History File	11
PM30300	PM Apply To History File	6

For each table, I’ll give the physical name for each relevant field and a short explanation of what I know about that date field. Some of the fields are obvious. Some, well, not so much.
I need a better explanation for the Purchase Date. Please add a comment to this post if you have more information on how this field is populated.

PM20000

PM Transaction Open File - 10 dates

VCHRNMBR	Voucher Number
VENDORID	Vendor ID
DOCTYPE	Document type of transaction. (Invoice, Return, Payment, etc.)
DOCDATE	Document date on the invoice or payment
DISCDATE	Date by which the invoice must be paid to earn the terms discount
DUEDATE	Date the invoice is due. After this date it is delinquent
POSTEDDT	System date when user pushed the [Post] button when the doc was originally posted.
MODIFDT	User date when transaction was last modified
DINVPDOF	The apply date from the final document applied to the invoice. This is the date the invoice or pmt became fully applied. Voided is fully applied, written-off can be fully applied. A document in the Open table should not have a value in this field, because no documents in the open table should be ‘paid off’.
PSTGDATE	Posting date for the invoice or payment; set by user on batch window or doc date expansion window
Tax_Date	Date that should be used to calculate and/or report sales tax, defaults to Document date; set by user on window
PRCHDATE	Physical date of transfer of goods/services
DEX_ROW_TS	Dex Row Timestamp – system date & time when last modified

PM30200

PM Paid Transaction History File - 11 dates

VCHRNMBR	Voucher Number
VENDORID	Vendor ID
DOCTYPE	Document type of transaction. (Invoice, Return, Payment, etc.)
DOCDATE	Document date on the invoice or payment
DISCDATE	Date by which the invoice must be paid to earn the terms discount
DUEDATE	Date the invoice is due. After this date it is delinquent
POSTEDDT	System date when user pushed the [Post] button when the doc was originally posted.
MODIFDT	user date when trx last modified an ‘apply’ is a modification, a void is not.
DINVPDOF	Apply date of the document that caused the invoice or pmt to be fully applied. Voided is fully applied, written-off can be fully applied. The DATE1 field for the final apply record in the PM30300 table becomes the DINVPDOF.
PSTGDATE	Posting date for the invoice or payment; set by user on batch window or doc date expansion window
VOIDPDATE	The Posting Date (GL) for the Void -The sub ledger void date becomes the DINVPDOF
Tax_Date	Date that should be used to calculate and/or report sales tax, defaults to Document date; set by user on window
PRCHDATE	Physical date of transfer of goods/services
DEX_ROW_TS	Dex Row Timestamp – system date & time when last modified, includes voids.

PM30300

PM Apply to History File - 6 dates

VENDORID	Vendor ID
VCHRNMBR	Voucher number of the payment document. The ‘apply from’ voucher number.
APFRDCNM	Document number of the payment ( check number). The ‘apply from’ Document number
DOCTYPE	Document type of the payment. The ‘apply from’ document type.
APTVCHNM	Voucher number of the invoice being paid. The ‘apply to’ voucher number.
APTODCNM	Document number of the invoice being paid. The ‘apply to’ invoice number
APTODCTY	The type of document the pmt is being applied to; invoice, debit memo, etc. The ‘apply to’ document type.
DOCDATE	Document date of the payment being applied; check date. The ‘apply from’ document date.
DATE1	The date the sub ledger uses for the ‘apply date’. This is set by the user on the apply window and used by the system in the HITB and to determine when the 1099 amount is reportable.
GLPOSTDT	Posting date to the general ledger. Set by user on the apply window
APTODCDT	Document date of the invoice being paid. The ‘apply to’ document date
ApplyToGLPostDate	Original GL posting date of the invoice being paid. The ‘apply to’ GL posting date.
ApplyFromGLPostDate	Original GL posting date of the payment document. The ‘apply from’ GL posting date.