Table: Billing_Decision_Collection | |||
This table would be more properly named [Billing_Decision_Application] and "collected" in column names could be replaced with "applied". This table stores rows that indicate how much of a [Billing_Decision] has been closed and what closed it. The main purpose of this is to track payments all the way down to [Sheet_Entry] so we can compute how much was produced, billed and collected for each sheet entry row. Not all rows in this table are really collections, as collections are only the amounts of billing decision rows that are closed by payments. Other sources can close billings, including credit memos, billing adjustments and negative WIP. ([ledger_entry_KEY], [sheet_entry_KEY]) identifies the [Billing_Decision] row this application is closing. ([from__ledger_entry_KEY], [collection_source__ledger_entry_KEY]) identifies the [Ledger_Entry_Application] row by ([from__ledger_entry_KEY], [to__ledger_entry_KEY]) that caused these applications for "Standard" type applications (see below). This can't directly be a relation to [Ledger_Entry_Application] because only standard type applications would have a record in LEA. This may change in the future to always be able to reference a LEA row (the LEA row would be for a zero amount except for standard type records). ([from__ledger_entry_KEY]) identifies the invoice that had applications to Progress Billings that tricked down to applications against progress relief on the invoice [ledger_entry_KEY] represents. There are three types of Billing_Decision_Collection records: Standard (user-created): ... from__ledger_entry_KEY == ledger_entry_KEY and from__ledger_entry_KEY <> collection_source__ledger_entry_KEY Zero Invoice Closing Adjustment: ... from__ledger_entry_KEY == ledger_entry_KEY and from__ledger_entry_KEY == collection_source__ledger_entry_KEY Progress Relief applications: ... from__ledger_entry_KEY <> ledger_entry_KEY Standard type applications don't have to track the progress relief to a different invoice so [from__ledger_entry_KEY] == [ledger_entry_KEY]. | |||
Columns | |||
Name | Type | Constraints | Description |
update__staff_KEY | int | NOT NULL | |
update_date_utc | datetime | NOT NULL | |
create_date_utc | datetime | NOT NULL | |
collection_source__ledger_entry_KEY | int | NOT NULL | Identifies the source of the application - usually this is the [Ledger_Entry] row of a payment, but it can also be the same as [ledger_entry_KEY] in adjustment scenarios. Should have been named to__ledger_entry_KEY. |
ledger_entry_KEY | int | NOT NULL | The combination of ledger_entry_KEY and sheet_entry_KEY identifies the billing decision this collection is for. |
from__ledger_entry_KEY | int | NOT NULL | Indicates which [Invoice] row was the source of the application in scenarios such as progress billing. Standard entries that users can create have [from__ledger_entry_KEY] set to the same value as [ledger_entry_KEY]. |
sheet_entry_KEY | int | NOT NULL | The combination of ledger_entry_KEY and sheet_entry_KEY identifies the billing decision this collection is for. |
bill_amount_collected | decimal (9, 2) | NOT NULL | The amount collected for the bill_amount of the Billing_Decision. |
surcharge_collected | decimal (9, 2) | NOT NULL | The amount collected for the surcharge of the Billing_Decision. |
discount_collected | decimal (9, 2) | NOT NULL | The amount collected for the discount of the Billing_Decision. |
sales_tax_collected | decimal (9, 2) | NOT NULL | The amount collected for the sales tax of the Billing_Decision. |
service_tax_collected | decimal (9, 2) | NOT NULL | The amount collected for the service tax of the Billing_Decision. |
collected | decimal (9, 2) | NOT NULL | The total amount collected for the Billing_Decision. |
Primary key | |||
Name | Columns | Description | |
PK_Billing_Decision_Collection$collection_source__ledger_entry_KEY$ledger_entry_KEY$from__ledger_entry_KEY$sheet_entry_KEY | collection_source__ledger_entry_KEY, ledger_entry_KEY, from__ledger_entry_KEY, sheet_entry_KEY | CLUSTERED | |
Indexes | |||
Name | Columns | Description | |
IX_Billing_Decision_Collection$from__ledger_entry_KEY | from__ledger_entry_KEY | Index supports foreign key FK_Billing_Decision_Collection__Invoice__from__ledger_entry_KEY | |
IX_Billing_Decision_Collection$ledger_entry_KEY$collection_source__ledger_entry_KEY$from__ledger_entry_KEY$sheet_entry_KEY@@@ | ledger_entry_KEY, collection_source__ledger_entry_KEY, from__ledger_entry_KEY, sheet_entry_KEY, bill_amount_collected, collected, discount_collected, sales_tax_collected, service_tax_collected, surcharge_collected | ||
IX_Billing_Decision_Collection$sheet_entry_KEY$ledger_entry_KEY$collection_source__ledger_entry_KEY$from__ledger_entry_KEY@@@ | sheet_entry_KEY, ledger_entry_KEY, collection_source__ledger_entry_KEY, from__ledger_entry_KEY, bill_amount_collected, collected, discount_collected, sales_tax_collected, service_tax_collected, surcharge_collected |
Foreign keys | |||
Name | Columns | Referenced table | Description |
FK_Billing_Decision_Collection__Billing_Decision__sheet_entry_KEY__ledger_entry_KEY | sheet_entry_KEY, ledger_entry_KEY | Billing_Decision | on delete cascade; |
FK_Billing_Decision_Collection__Invoice__from__ledger_entry_KEY | from__ledger_entry_KEY | Invoice | on delete cascade; |
FK_Billing_Decision_Collection__Ledger_Entry__collection_source__ledger_entry_KEY | collection_source__ledger_entry_KEY | Ledger_Entry | on delete cascade; |