Table: dbo.AR_Transaction
This table stores Invoices, Credit Memos, and Finance Charges for the Accounts Receivable service. Invoices and Finance Charges are transactions customers have to pay while Credit Memos are can be used to reduce open balance.
Columns
NameTypeConstraintsDescription
gl_transaction_KEYint (4)NOT NULLPrimary key for this table. AR Transactions are required to have a GL Transaction row.
customer_KEYint (4)NOT NULLThe customer the transaction is for.
ar_transaction_type_KEYint (4)NOT NULLAR Transaction Type including Invoice, Credit Memo and Finance Charge.
transaction_status_KEYint (4)NOT NULLTransaction status. Valid statuses for AR Transactions include Live, Voided, and Pending
transaction_datesmalldatetime (4)NOT NULLThe date the transaction took place on.
reference_numbernvarchar (32)NOT NULLThe reference number for the transaction.
payment_discount_amount_originaldecimal (9, 2)NOT NULLA discount amount for making early payments. This field is for an original amount.
payment_discount_amount_overridedecimal (9, 2)An override field for payment discount amount.
payment_term_KEYint (4)An optional payment term for the transaction.
due_datesmalldatetime (4)The due date for the transaction if applicable.
purchase_order_numbernvarchar (32)NOT NULLThe purchase order number for the transaction.
gl_account_KEYint (4)NOT NULLThe AR Transaction GL Account.
payment_discount_expiration_datesmalldatetime (4)The discount expiration date for the payment discount.
payment_discount__gl_account_KEYint (4)The gl account for the payment discount amount.
shipping__contact_address_type_KEYint (4)The type of contact address to use for shipping.
shipping_datesmalldatetime (4)The date the items for the invoice shipped.
shipping_salutationnvarchar (80)NOT NULLThe salutation to use for the shipping name.
shipping_namenvarchar (100)NOT NULLThe name to use in the shipping address.
printedbit (1)NOT NULLTrue if this AR_Transaction has ever been printed.
generated_by__transaction_template_KEYint (4)Specifies that this AR transaction was generated by a specific template. This link is important for template scheduling; we need to keep track of what has already been generated by a template. A null value indicates that it was not generated by a template.
reference_number_sortablevarchar (80)NOT NULLA system generated sortable reference number.
purchase_order_number_sortablevarchar (80)NOT NULLA system generated sortable purchase order number.
messagenvarchar (2000)NOT NULLA transaction message.
payment_discount_amountdecimal (9, 2)NOT NULLThis payment discount amount field shows the override value if not null otherwise it shows the original amount.
created_datedatetime (8, 3)NOT NULLThe date on which the AR transaction was created. To maintain consistency with the Payable_Transaction and Bank_Transaction_Event versions of created date, we want to use DATETIME instead of DATE and the local time value needs to be assigned rather than using UTC time.
Primary key
NameColumnsDescription
PK_AR_Transaction$gl_transaction_KEYgl_transaction_KEY
Foreign keys
NameColumnsReferenced tableDescription
FK_AR_Transaction__AR_Transaction_Template__generated_by__transaction_template_KEYgenerated_by__transaction_template_KEYAR_Transaction_Template (transaction_template_KEY)
FK_AR_Transaction__AR_Transaction_Type__ar_transaction_type_KEYar_transaction_type_KEYAR_Transaction_Type (ar_transaction_type_KEY)
FK_AR_Transaction__Contact_Address_Type__shipping__contact_address_type_KEYshipping__contact_address_type_KEYContact_Address_Type (contact_address_type_KEY)
FK_AR_Transaction__Customer__customer_KEYcustomer_KEYCustomer (customer_KEY)
FK_AR_Transaction__GL_Account__gl_account_KEYgl_account_KEYGL_Account (gl_account_KEY)
FK_AR_Transaction__GL_Account__payment_discount__gl_account_KEYpayment_discount__gl_account_KEYGL_Account (gl_account_KEY)
FK_AR_Transaction__GL_Transaction__gl_transaction_KEYgl_transaction_KEYGL_Transaction (gl_transaction_KEY)
FK_AR_Transaction__Payment_Term__payment_term_KEYpayment_term_KEYPayment_Term (payment_term_KEY)
FK_AR_Transaction__Transaction_Status__transaction_status_KEYtransaction_status_KEYTransaction_Status (transaction_status_KEY)
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_Customer_Historical_AR_Transaction__AR_Transaction__gl_transaction_KEYgl_transaction_KEYCustomer_Historical_AR_Transaction
FK_AR_Transaction_Item__AR_Transaction__gl_transaction_KEYgl_transaction_KEYAR_Transaction_Item
FK_AR_Transaction_Item__AR_Transaction__assessed_invoice__gl_transaction_KEYassessed_invoice__gl_transaction_KEYAR_Transaction_Item
FK_Print_AR_Transaction_Selection__AR_Transaction__gl_transaction_KEYgl_transaction_KEYPrint_AR_Transaction_Selection
FK_AR_Payment_Application__AR_Transaction__receivable__gl_transaction_KEYreceivable__gl_transaction_KEYAR_Payment_Application

Schema diagram