Table: dbo.Manage_AP_Payment_Processable_Transaction
reasons for the Manage_AP_Payment_Processable_Transaction table It has been recommended that the best way to provide a data source for the Manage AP Payments rapid tab grid is with a special database table which 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 Manage Payments view is displayed. This table holds a combination of payment and payable transactions for a given client. The payment and payable transactions will each have a gross open balance amount that has not yet been reduced to zero. These transactions will be paired together so that they can be applied, that is, the payable gross amount will be reduced and the payment gross amount will be reduced in tandem.
Columns
NameTypeConstraintsDescription
manage_ap_payment_processable_transaction_KEYint (4)NOT NULLPrimary Key for this table.
user_session_guiduniqueidentifier (16)NOT NULLThis is a foreign key to the User_Session table, it allows our table contents to be related to a specific user session.
vendor_KEYint (4)NOT NULLThis is a foreign key to the vendor table. Every payment or payable will pertain to a particular vendor.
vendor_idnvarchar (22)NOT NULLThe vendor ID associated with the vendor_KEY in the same table row. Needed for filtering and as the data source of a grid column.
vendor_namenvarchar (100)NOT NULLThe vendor Name associated with the vendor_KEY in the same table row. Neede for filtering.
vendor_type_KEYint (4)NOT NULLThis is a foreign key to the Vendor_TYPE table.
application_selection_group_numberint (4)This allows us to associate payments and payables together so that they can be applied. This will be null if the applied option has not been chosen for this table row (grid row).
payment_selection_group_numberint (4)This allows payables and pending payments to be associated together, so that the pending payment can be turned into a new payment, and then the payment and payable can be applied. This will be null if the pay option has not been chosen for this table row (grid row).
payable_transaction_KEYint (4)Foreign key to the Payable_Transaction table. This will be non null for table rows that are for payable transactions.
payment__gl_transaction_KEYint (4)Foreign key to the GL_Transaction table for an existing payment. This will be non null for table rows that are for existing payments.
pending_ap_payment_KEYint (4)Foreign key to the Pending_AP_Payment table. This will be non null for table rows that are payables that have been selected to pay and thus are tied to a pending payment.
discount_expiration_datesmalldatetime (4)The date of expiration of a payable discount, if any. This will be null if the table row is for a payment instead of a payable.
due_datesmalldatetime (4)The due date of a payable transaction. This will be null if the table record is for a payment instead of a payable.
reference_numbernvarchar (32)NOT NULLThe reference number of an existing payment or an existing payable, whichever one a particular row in the Manage_AP_Payments_Appliable_Transaction table pertains to.
reference_number_pending_paymentnvarchar (32)The reference number of a pending payment, if any. Null if this is not a payable row tied to a pending payment.
gross_open_balancedecimal (9, 2)NOT NULLThe gross open balance of the payment or payable to which a row pertains.
pending_payment_applied_amountdecimal (9, 2)This is the sum of the manage ap payment applications that pertain to this payment or payable.
bank_transaction_type_KEYint (4)Foreign key to the Bank_Transaction_Type table. Used if a row in this table is a payment row, Null for payables.
payable_transaction_type_KEYint (4)Foreign key to the Payable_Transaction_Type table. Null for table rows that are not payable liabilities or credit memos.
pending__ap_payment_type_KEYint (4)Foreign key to the ap_payment_type table. Null for table rows not tied to a pending ap payment.
new_payment_amountdecimal (9, 2)If a table row is for a payable, and that payable is associated with a pending payment, then this will be the amount of the pending payment.
checkbook_KEYint (4)Foreign key to the checkbook table. This will be used to display the bank account for pending payments, and the bank account for unprinted AP checks. Null for payables not tied to a pending payment.
payment_datedatetime (8, 3)If this table row is for a payment, then this will be the payment date. Null if the table row is not for a payment.
payable_datedatetime (8, 3)If this table row is for a payable, then this will be the payable date. Null if the table row is not for a payable.
transaction_datedatetime (8, 3)NOT NULLGeneric transation date. This will be the payment date for payments and the payable date for payables. We need this column for filtering on the transaction date, that is, a filter that wants to bring in both payments and payables, not just one or the other.
journal_KEYint (4)NOT NULLA foreign key to the Journal table.
gl_period_KEYint (4)NOT NULLA foreign key to the gl period table, to give us the gl period to adjust.
gross_amountdecimal (9, 2)NOT NULLThe gross amount of the payable or payment.
paid_to_date_amountdecimal (9, 2)Used for payables. This will be the sum of all previous payments plus previous discounts taken. In other words it does not include pending application amounts. Null if the table row is not for a payable.
discount_taken_amountdecimal (9, 2)Used for payables. This will be the pending discount taken amount for any payable selected to pay or to apply. Null if the table row is not for a payable.
current_payment_amountdecimal (9, 2)Used for payables. This will be the pending payment amount for any payables selected to pay or to apply. Null if the table row is not for a payable.
discount_remaining_amountdecimal (9, 2)Used for payables. This will be the discount available for the payable, and is used to default the discount taken amount. Null if the table row is not for a payable.
is_handwritten_checkbit (1)This tells us the handwritten status of new and existing payments. Null if the table row is a payable that is not connected to a new payment.
is_transaction_date_overridebit (1)NOT NULLThis value lets us know that the user has chosen to set the transaction date of a pending payment themselves through the User Interface. There are two ways to set the transaction date in Manage AP Payments when creating a pending AP payment. The first is to programmatically set the date to a value. The second is for the user to set the transaction date themselves via the user interface. We need to know if the user has overwritten the system transaction date so that we do not continue to try to reset it. For example, in Manage AP Payments, there is an option to use the oldest due date of a group of payable transactions as the payment date. If the user has set the payment date themselves, and then adds additional payables to the group, we need to know that we do not want the system to update the payment date. Once it is set by the user, it should not be set by the system. This field will be false for records in this table that are not for pending ap payments, and when the user has not overridden the system calculated date.
vendor__contact_address_type_KEYint (4)For records in this table that are for payable transactions, this will be the contact address type of the payable as entered in enter payables. This is not a required field for payables, it can be null. If the record in this table is for a pending ap payment, this will be the contact address type that will be assigned to and received from the pending ap payment table record for use when creating the payment.
created_datedatetime (8, 3)The date the payable transaction was created.
created__staff_KEYint (4)The staff that created the payable transaction.
gl_transaction_type_KEYint (4)The type of the AP transaction.
amount_selected_to_paydecimal (9, 2)The total amount a vendor has selected to pay.
is_posting_period_overriddenbit (1)NOT NULLIf this is true, the use_transaction_date_to_default_posting_period flag in Client_GL_Setup is ignored when deciding if the posting period needs to be updated to match the transaction date.
Primary key
NameColumnsDescription
PK_Manage_AP_Payment_Processable_Transactionmanage_ap_payment_processable_transaction_KEY
Foreign keys
NameColumnsReferenced tableDescription
FK_Manage_AP_Payment_Processable_Transaction__AP_Payment_Type__pending__ap_payment_type_KEYpending__ap_payment_type_KEYAP_Payment_Type (ap_payment_type_KEY)
FK_Manage_AP_Payment_Processable_Transaction__Bank_Transaction_Type__bank_transaction_type_KEYbank_transaction_type_KEYBank_Transaction_Type (bank_transaction_type_KEY)
FK_Manage_AP_Payment_Processable_Transaction__Contact_Address_Type__vendor__contact_address_type_KEYvendor__contact_address_type_KEYContact_Address_Type (contact_address_type_KEY)
FK_Manage_AP_Payment_Processable_Transaction__Payable_Transaction_Type__payable_transaction_type_KEYpayable_transaction_type_KEYPayable_Transaction_Type (payable_transaction_type_KEY)
FK_Manage_AP_Payment_Processable_Transaction__Staff__created__staff_KEYcreated__staff_KEYStaff (staff_KEY)
FK_Manage_AP_Payment_Processable_Transaction__User_Session__user_session_guiduser_session_guidUser_Session (user_session_guid)
FK_Manage_AP_Payment_Processable_Transaction__Vendor__vendor_KEYvendor_KEYVendor (vendor_KEY)
FK_Manage_AP_Payment_Processable_Transaction__Vendor_Type__vendor_type_KEYvendor_type_KEYVendor_Type (vendor_type_KEY)

Schema diagram