Table: dbo.AR_Payment
Table which holds all the necessary information for each payment in Accounts Receivable.
Columns
NameTypeConstraintsDescription
gl_transaction_KEYint (4)NOT NULLEach Accounts Receivable payment is based on a GL transaction.
ar_payment_type_KEYint (4)NOT NULLSpecifies what type of payment this row is.
customer_KEYint (4)NOT NULLThe customer that is associated with this payment.
customer_namenvarchar (100)NOT NULLThe name of the customer associated with this payment. This field serves as a historical snapshot containing the customer name from when the transaction was created. If the name of the customer is changed in the customer table after the transaction is created, it is important to retain the original name the transaction was created under.
reference_numbernvarchar (32)NOT NULLThe number to be associated with this payment.
transaction_datesmalldatetime (4)NOT NULLThe date to be associated with this payment.
amountdecimal (9, 2)NOT NULLThe numerical cash value of this payment
undeposited_payment__gl_account_KEYint (4)NOT NULLThe GL account to be associated with undeposited payments.
unearned_revenue__gl_account_KEYint (4)NOT NULLThe GL account to be associated with unearned revenue.
transaction_status_KEYint (4)NOT NULLSpecifies the state the payment is currently in.
ar_deposit__bank_transaction_KEYint (4)When an AR Payment is tied to a bank transaction of type AR deposit, this key shall be the key of that AR deposit. For AR Payments that are not yet tied to an AR deposit, this key shall be null.
reference_number_sortablevarchar (80)NOT NULLAn easily sortable version of the reference number. This is automatically generated when the reference number is added.
created_datedatetime (8, 3)NOT NULLThe date on which the AR transaction was created. Similar to Bank_Transaction_Event.event_date, we want the actual time in form of DATETIME instead of DATE and the local time value needs to be assigned rather than using UTC time. For the purposes of historical data, we get midnight (T00:00 Local time) as an artifact of the data conversion process, but new data should include the time-of-day.
Primary key
NameColumnsDescription
PK_AR_Payment$gl_transaction_KEYgl_transaction_KEY
Foreign keys
NameColumnsReferenced tableDescription
FK_AR_Payment__AR_Payment_Type__ar_payment_type_KEYar_payment_type_KEYAR_Payment_Type (ar_payment_type_KEY)
FK_AR_Payment__Bank_Transaction__ar_deposit__bank_transaction_KEYar_deposit__bank_transaction_KEYBank_Transaction (bank_transaction_KEY)
FK_AR_Payment__Customer__customer_KEYcustomer_KEYCustomer (customer_KEY)
FK_AR_Payment__GL_Account__undeposited_payment__gl_account_KEYundeposited_payment__gl_account_KEYGL_Account (gl_account_KEY)
FK_AR_Payment__GL_Account__unearned_revenue__gl_account_KEYunearned_revenue__gl_account_KEYGL_Account (gl_account_KEY)
FK_AR_Payment__GL_Transaction__gl_transaction_KEYgl_transaction_KEYGL_Transaction (gl_transaction_KEY)
FK_AR_Payment__Transaction_Status__transaction_status_KEYtransaction_status_KEYTransaction_Status (transaction_status_KEY)

Schema diagram