Post date: Aug 03, 2016 8:8:36 PM
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:
So both of these two armatchinglines were created when someone posted a cash entry of 6,473.55 dollars against that invoice, using 'document number' 1040731 for that cash entry.
The first line you see listed here is an armatchingline and it 'comes from' that cash entry, and 'applies to' the invoice, which was invoice number (document number) 748701, which has a transaction id of 341749. The 'parent transaction' was that cash transaction, which had an id of 344317.
The second line listed is another armatchingline and it 'comes from' (or is 'related to') the invoice, and it 'applies to' (reduces the value of) the cash entry, which was document number 1040731, and that cash entry has a transaction id of 344317. BUT: the 'parent transaction' for this is ALSO the cash entry, which is transaction ID 344317. That is because that 'lparenttransactionid' is supposed to point to the TRANSACTION THAT ACTUALLY CREATED THE ENTRY, which in this case is the CASH entry.
Regarding retainage:
Say you have an invoice for 1000.00, and nothing is paid on it yet.
Then you get a check for 900.00, and the customer says 'we are holding 100.00 - 10% - retainage'. (Usually you know ahead of time if they will hold retainage and how much anyway.)
So you:
1) Enter a cash entry for 900.00, and this leaves the customer owing 100.00.
2) NEXT - you go create a RETAINAGE entry, which is another transaction, for 100.00, and you 'point' it to the original 1000.00 invoice, and post it. It works kind of like cash: it reduces the invoice balance to zero, but essentially 'moves' the 100.00 balance 'into retainage'. So now you have a 'retainage' transaction (document type '9') for 100.00. When they finally pay that you enter a cash entry against it, and it all clears out.
So a retainage entry works pretty much the same as a cash entry in that regard, except that the amount stored in the tables is POSITIVE, like an invoice amount, since the customer still owes the money, and the 'liability' is still positive.