Table: dbo.Apply_Customer_Payment_Transaction
This table exists to provide a data source for the apply customer payments view grid. This table will exist only for the purpose of feeding the datasource to the grid, and to facilitate filtering in our system. Such a table is sometimes called a “temporary” table. It is permanently in the database, but holds data only temporarily, while the Apply Customer Payments view is displayed. This table holds a combination of ar payments (cash, check, credit card and write-offs) and ar transactions (invoices, credit memos, and finance charges) for a given client.
Columns
NameTypeConstraintsDescription
apply_customer_payment_transaction_KEYint (4)NOT NULLPrimary Key for this table.
user_session_guiduniqueidentifier (16)NOT NULLFrom the User_Session table, it allows our table contents to be related to a specific user session.
gl_transaction_KEYint (4)NOT NULLThis is a foreign key to the GL_Transaction table. Every AR transaction and AR payment will have a gl transaction key.
gl_transaction_type_KEYint (4)NOT NULLThis is a foreign key to the GL_Transaction_Type table. Every ar transaction and ar payment will have a gl transaction type.
customer_payment_application_numberint (4)This allows AR transactions and AR payments to be associated together, for the creation of an AR Payment application.
customer_namenvarchar (100)NOT NULLThe customer Name associated with the customer_KEY in the same table row. Needed for filtering.
customer_idnvarchar (22)NOT NULLThe customer ID associated with the customer_KEY in the same table row. Needed for filtering and as the data source of a grid column.
customer_id_sortablenvarchar (96)NOT NULLThe customer ID associated with the customer_KEY in the same table row. Needed as the data source of a grid column, for sorting purposes.
customer_KEYint (4)NOT NULLThis is a foreign key to the customer table. Every AR payment and AR transaction will pertain to a particular customer.
transaction_datesmalldatetime (4)NOT NULLthe transation date of the AR payment or AR transaction.
due_datesmalldatetime (4)The due date of an invoice. This will be null if the table record is not an AR transaction - invoice.
discount_expiration_datesmalldatetime (4)The date of expiration of an invoice discount, if any. This will be null if the table row is not an AR transaction - invoice.
reference_numbernvarchar (32)NOT NULLThe reference number of AR payment or AR transaction.whichever one a particular row in the Apply_Customer_Payment table pertains to.
reference_number_sortablenvarchar (160)NOT NULLThe reference number of AR payment or AR transaction.whichever one a particular row in the Apply_Customer_Payment table pertains to.
open_balance_amountdecimal (9, 2)NOT NULLThe open balance amount of the AR payment or AR transaction.
discount_honored_amountdecimal (9, 2)The discount honored amount of an invoice. This will be null if the table record is not an AR transaction - invoice.
applied_amountdecimal (9, 2)The amount of the AR payment or AR transaction that is being applied in an application that the user creates. Null if no application is yet being created for the transaction.
unapplied_balance_amountdecimal (9, 2)The amount of the AR payment or AR transaction that is not being applied in an application that the user creates. Null if no application is yet being created for the transaction.
discount__gl_account_KEYint (4)This is a foreign key to the GL_Account table. Used for invoices only. Null for other transactions.
Primary key
NameColumnsDescription
PK_Apply_Customer_Payment_Transactionapply_customer_payment_transaction_KEY
Foreign keys
NameColumnsReferenced tableDescription
FK_Apply_Customer_Payment_Transaction__Customer__customer_KEYcustomer_KEYCustomer (customer_KEY)
FK_Apply_Customer_Payment_Transaction__GL_Account__discount__gl_account_KEYdiscount__gl_account_KEYGL_Account (gl_account_KEY)
FK_Apply_Customer_Payment_Transaction__GL_Transaction__gl_transaction_KEYgl_transaction_KEYGL_Transaction (gl_transaction_KEY)

Schema diagram