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
Name | Type | Constraints | Description
apply_customer_payment_transaction_KEY | int (4) | NOT NULL | Primary Key for this table.
user_session_guid | uniqueidentifier (16) | NOT NULL | From the User_Session table, it allows our table contents to be related to a specific user session.
gl_transaction_KEY | int (4) | NOT NULL | This is a foreign key to the GL_Transaction table. Every AR transaction and AR payment will have a gl transaction key.
gl_transaction_type_KEY | int (4) | NOT NULL | This 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_number | int (4) |  | This allows AR transactions and AR payments to be associated together, for the creation of an AR Payment application.
customer_name | nvarchar (100) | NOT NULL | The customer Name associated with the customer_KEY in the same table row. Needed for filtering.
customer_id | nvarchar (22) | NOT NULL | The 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_sortable | nvarchar (96) | NOT NULL | The 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_KEY | int (4) | NOT NULL | This is a foreign key to the customer table. Every AR payment and AR transaction will pertain to a particular customer.
transaction_date | smalldatetime (4) | NOT NULL | the transation date of the AR payment or AR transaction.
due_date | smalldatetime (4) |  | The due date of an invoice. This will be null if the table record is not an AR transaction - invoice.
discount_expiration_date | smalldatetime (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_number | nvarchar (32) | NOT NULL | The reference number of AR payment or AR transaction.whichever one a particular row in the Apply_Customer_Payment table pertains to.
reference_number_sortable | nvarchar (160) | NOT NULL | The reference number of AR payment or AR transaction.whichever one a particular row in the Apply_Customer_Payment table pertains to.
open_balance_amount | decimal (9, 2) | NOT NULL | The open balance amount of the AR payment or AR transaction.
discount_honored_amount | decimal (9, 2) |  | The discount honored amount of an invoice. This will be null if the table record is not an AR transaction - invoice.
applied_amount | decimal (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_amount | decimal (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_KEY | int (4) |  | This is a foreign key to the GL_Account table. Used for invoices only. Null for other transactions.

Primary key
Name | Columns | Description
PK_Apply_Customer_Payment_Transaction | apply_customer_payment_transaction_KEY |

Foreign keys
Name | Columns | Referenced table | Description
FK_Apply_Customer_Payment_Transaction__Customer__customer_KEY | customer_KEY | Customer (customer_KEY) |
FK_Apply_Customer_Payment_Transaction__GL_Account__discount__gl_account_KEY | discount__gl_account_KEY | GL_Account (gl_account_KEY) |
FK_Apply_Customer_Payment_Transaction__GL_Transaction__gl_transaction_KEY | gl_transaction_KEY | GL_Transaction (gl_transaction_KEY) |