AR Transaction Files

What follows is an explanation of the fields in the artransactions and armatchinglines tables, which carry all the customer activity and are used to display transaction history, customer activity, and the AR aging report.

'artransactions' Table Structure

'id' - this is a consecutive, autoincrement field that is assigned automatically as records are added.

'loriginalbatchnumber' - this refers to the original posting batch which created the transaction

'loriginalentrynumber' - this refers to the entry number in the original posting batch which created the transaction.

'idoctype' indicates the type of AR document:

AR transaction doc types are:

0: "Invoice";

1: "Credit";

2: "Receipt";

3: "Prepayment";

4: "Reversal";

5: "Invoice Adjustment";

6: "Misc Receipt";

7: "Cash Adjustment";

8: "Credit Adjustment";

9: "Retainage";

10: "Apply-To";

'spayeepayor' - this is the customer number.

'docnumber' - document number: invoice number, or the cash entry doc number, etc.  The combination of spayeepayor and sdocnumber has to be unique.

'sterms' - the terms on the original document (as for invoices, etc. - otherwise, leave blank)

'datdocdate' - document date, e.g. invoice date, cash entry date, etc.

'datduedate' - date the document (typically invoice) is due - for any other document.

'doriginalamt' - the original amount of the document (i.e., before any money was applied against it.  This would be the original amount of the invoice, or the original amount of the cash entry, for example.  The amount is positive or negative, depending on whether it INCREASES or DECREASES the customer's liability: so for example INVOICE amounts are positive, CASH ENTRY amounts are negative.

'dcurrentamt' - the amount remaining: if the invoice is partially paid, this would be the amount remaining (which will appear on the aging, for example).  If it's a cash entry, it's the amount still remaining unapplied, etc.

'sdocdescription' - a description that a user might enter when making this entry.  (e.g., the customer name on invoices, 'cash entry' on cash entries, 'prepay' on prepays, etc.  Not critical to the processing.)

'sordernumber' - the original order number for which the invoice (typically) was generated..

'scontrolacct' - this would be the AR control acct from the GL.

'iretainage' - this is a 'true/false' - if the document is retainage, this should be a '1', if not, a '0'

'sponumber' - if the customer used a PO number when making placing the order, this would be recorded on the sales order, and then passed on to the AR invoice transaction, recorded in this field.

'armatchinglines' Table Structure:

armatchingline records are a list of the 'applying' amounts, or the amounts which 'reduce' a document.  So if, for example, you have a 1000.00 invoice, and there's a 500.00 cash entry entered against it, there will be an armatchingline record 'from' the cash entry, applying 500.00 'against' the invoice, AND an armatchingline record 'from' the invoice, 'applying' 500.00 against the cash entry.  When you subtract the amounts of all the 'applying' records (from armatchinglines) from the original amount of the 'applied-to' transaction, what remains is the 'current' amount.

The sign (positive or negative) of the armatchinglines is POSITIVE when its parent is a cash entry, and it's applying TO an invoice, for example.  It's NEGATIVE when it's parent is the invoice, and applying to (reducing the amount of) the cash entry.  So the math works like this: to determine the current amount of an invoice, say, you would SUBTRACT all the armatchingline amounts that apply to it.  Same for cash, you would SUBTRACT all the armatchingline amounts which apply to it.  (Those armatchingline amounts would be negative numbers.)  Basically, a transaction which INCREASES the customer's liability, like an invoice, is a POSITIVE number, and one which DECREASES the customer's liability is a NEGATIVE number.

In the armatchingline records, the transaction (e.g. cash entry) which generated the matching line is called the 'parent' transaction.  So when a cash entry is posted, it creates two (or more, if it applied to multiple invoices) armatchingline records, but ALL the armtachingline records that it generates have an 'lparenttransactionid' value that points to the transaction ID of that cash entry. The transaction which it reduces, or to which it is applied, is called the 'apply to' transaction.

Fields:

'id' - this is a consecutive, autoincrement field that will be assigned automatically as the records are added.

'spayeepayor' - this is the customer number.

'sdocnumber' - this is the document number of the 'parent' document.

'sapplytodoc' - this is the document number of the transaction TO WHICH this armatchingline applies.

'ldocappliedtoid' - this is the ID of the transaction TO WHICH this armatchingline applies.

'damount' - amount applied - see note above.

'sdescription' - description of the matching line - most often not used, not critical to processing.

'dattransactiondate - transaction date of the application, which should be the document date of the PARENT transaction.

'lparenttransactionid' - the id of the PARENT transaction, that is, the transaction which generated this line - for example, the cash transaction which created both of the matching lines.

'iretainage' - refers to the retainage flag of the transaction TO WHICH this line applies.  If the transaction TO WHICH it applies is flagged as retainage, this is a '1', if not, it's a '0'.

Example: