Accounting Schema Relationships
This documentation describes the relationships of the accounting tables listed below.
| Table | Role | Primary Key |
|---|---|---|
| ARCUSTMN | AR customer master. | AR_NUMBER |
| ARCUSTHD | AR customer financial header. | AR_NUMBER |
| ARCONTCT | AR customer contacts. | AR_NUMBER, LIST_NUMBER |
| DCEMLCUS | AR customer email list. | AR_NUMBER, LIST_NUMBER, UNIQUE |
| ARSUBDET | Recurring billing line items. | AR_NUMBER, ACCOUNT_NUMBER, BILLING_CODE_LINE |
| ARSUBEXP | Expiration overrides assigned to billing lines. | AR_NUMBER, ACCOUNT_NUMBER, BILLING_CODE_LINE |
| ARRECNTS | Free-text notes attached to a recurring billing line. | AR_NUMBER, ACCOUNT_NUMBER, BILLING_CODE_LINE |
Together, these tables provide a complete record of your accounting customers’ information. The tables are linked through each customer’s assigned AR_NUMBER and the shared primary-key fields described below.
The tables fall into two groups: those that describe the customer profile (ARCUSTMN, ARCUSTHD, ARCONTCT, DCEMLCUS) and those that describe a customer’s recurring billing (ARSUBDET, ARSUBEXP, ARRECNTS).
Customer profile tables
The ARCUSTMN table holds one master record per customer and serves as the parent for the customer’s profile. Each related table joins back to it on AR_NUMBER.
| Relationship | Join on | Cardinality |
|---|---|---|
ARCUSTMN → ARCUSTHD | AR_NUMBER | One-to-one. Each master record has exactly one financial header record. |
ARCUSTMN → ARCONTCT | AR_NUMBER | One-to-many. A customer can have any number of contacts, each distinguished by LIST_NUMBER. |
ARCONTCT → DCEMLCUS | AR_NUMBER, LIST_NUMBER | One-to-many. A contact can have any number of email addresses assigned. |
The ARCUSTMN record stores identifying information (name, address, branch, billing settings). The ARCUSTHD record stores the customer’s current financial state (open balance, aging buckets, billing status). The ARCONTCT table stores the people associated with the customer, and DCEMLCUS extends each contact with one or more email addresses.
Please Note The
UNIQUEfield onDCEMLCUSis part of the primary key for legacy reasons, but should remain blank.
Recurring billing tables
A single recurring billing line is uniquely identified by three fields together: AR_NUMBER (the customer being billed), ACCOUNT_NUMBER (the subscriber account the line is applied to), and BILLING_CODE_LINE (the line number within that account). The expiration override and notes tables extend a recurring line using the same three-field key.
| Relationship | Join on | Cardinality |
|---|---|---|
ARSUBDET → ARSUBEXP | AR_NUMBER, ACCOUNT_NUMBER, BILLING_CODE_LINE | One-to-one. A recurring line has at most one expiration override row. |
ARSUBDET → ARRECNTS | AR_NUMBER, ACCOUNT_NUMBER, BILLING_CODE_LINE | One-to-one. A recurring line has at most one notes row. |
A single customer can have recurring lines across multiple subscriber accounts, and a single subscriber account can have multiple recurring lines distinguished by BILLING_CODE_LINE.
Connecting the two groups
Both groups are anchored to the customer through AR_NUMBER. To assemble a complete picture of a customer’s billing relationship, start from ARCUSTMN and ARCUSTHD for identity and financial state, branch into ARCONTCT and DCEMLCUS for who to contact, and fan out into ARSUBDET — with its ARSUBEXP and ARRECNTS extensions — for what they are being billed.