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
NameTypeConstraintsDescription
update__staff_KEYintNOT NULL
update_date_utcdatetimeNOT NULL
create_date_utcdatetimeNOT NULL
collection_source__ledger_entry_KEYintNOT NULLIdentifies 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_KEYintNOT NULLThe combination of ledger_entry_KEY and sheet_entry_KEY identifies the billing decision this collection is for.
from__ledger_entry_KEYintNOT NULLIndicates 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_KEYintNOT NULLThe combination of ledger_entry_KEY and sheet_entry_KEY identifies the billing decision this collection is for.
bill_amount_collecteddecimal (9, 2)NOT NULLThe amount collected for the bill_amount of the Billing_Decision.
surcharge_collecteddecimal (9, 2)NOT NULLThe amount collected for the surcharge of the Billing_Decision.
discount_collecteddecimal (9, 2)NOT NULLThe amount collected for the discount of the Billing_Decision.
sales_tax_collecteddecimal (9, 2)NOT NULLThe amount collected for the sales tax of the Billing_Decision.
service_tax_collecteddecimal (9, 2)NOT NULLThe amount collected for the service tax of the Billing_Decision.
collecteddecimal (9, 2)NOT NULLThe total amount collected for the Billing_Decision.
Primary key
NameColumnsDescription
PK_Billing_Decision_Collection$collection_source__ledger_entry_KEY$ledger_entry_KEY$from__ledger_entry_KEY$sheet_entry_KEYcollection_source__ledger_entry_KEY, ledger_entry_KEY, from__ledger_entry_KEY, sheet_entry_KEYCLUSTERED
Indexes
NameColumnsDescription
IX_Billing_Decision_Collection$from__ledger_entry_KEYfrom__ledger_entry_KEYIndex 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
NameColumnsReferenced tableDescription
FK_Billing_Decision_Collection__Billing_Decision__sheet_entry_KEY__ledger_entry_KEYsheet_entry_KEY, ledger_entry_KEYBilling_Decisionon delete cascade;
FK_Billing_Decision_Collection__Invoice__from__ledger_entry_KEYfrom__ledger_entry_KEYInvoiceon delete cascade;
FK_Billing_Decision_Collection__Ledger_Entry__collection_source__ledger_entry_KEYcollection_source__ledger_entry_KEYLedger_Entryon delete cascade;

Schema diagram