Saturday, February 1, 2014

What’s in a date? PM Transactions

cat calendar
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.
Until next post!
Leslie

3 comments:

Anonymous said...

Just wondering if you ever got additional information on the PRCHDATE field.

I have checked my databases and none have anything other than '1900-01-01' in them. So I believe I am going to commandeer this field for another project. I am sending data back to another system and I am going to use this field to track the date when the transaction was sent.

Grady

Dynamics Confessor said...

Hi Grady,
I do not think it calls or is called by anything. Looks like it is a good candidate for a takeover!
Kind regards,
Leslie

cmatumbura said...

Thank you Leslie for such a great insight into these PM dates