BST10 map
This a map of the data model for BST10. This is what the database looks like.
Most of this will be talking about Projects, Project Tasks, and Billing for these.
Tables
All of these tables will be in the [Final] schema unless otherwise specified.
Generally, all of the dimension tables like Project, Project_Task etc. will have these common columns: Id, Code, Name. Id is the GUID should always be used for table joins.
| Table | Description |
|---|---|
Project |
This has the core info for the project, including the project code and project name |
Project_Task |
Has the property ShowTaskCover if it's got "Show on Cover" ticked in the UI |
Project_BillTerm_BillTask |
Has the property ShowTaskCover if it's got "Show on Cover" ticked in the UI |
Project_BillTerm |
|
ProjectDetail |
These are project transactions. There is a fee type on this. This may be different from the project task fee type (if it's been changed in the past) |
FinalBill |
These are the final bills/invoices that have been issued to clients. It will have the amounts that was actually billed to the customer (e.g. BillAmountExcludingTaxPC). The effort accumulated on a task may not add up to what was actually billed. The Project Summary table picks up from this table for billed amounts. Has property Voided. Set Voided=0 to select only bills which were not voided. |
ProjectBillReportStructure |
This is the billing structure. The main ID key column here is BillTaskProjectTaskId (this is actually simply a concatenation/coalescence of TaskId and BillTaskId). Has property BillAsBillTaskCover to indicate what task this falls under in the UI for "Show on Cover". Has property BillFee: this is the true bill fee. |
FinalBill_BillReportTaskSummary |
This contains the history of what task has been billed and how much for. Key columns: CurrentAmount, CurrentAmountWithOverages (this one is relevant for CPM/Cost Plus Max jobs), BillControl_HISBILLTASKBILL for historical billing. Related to FinalBill via FinalBill_BillReportTaskSummary.DocumentId = FinalBill.Id. Everything here is in Project Currency (PC). Need to do manual currency exchange to get to Reporting Currency. |
ProjectSummary |
The screen in the UI picks up from this table. This is what business users rely on, and this is what they will want to reconcile with. |
Views
ExtractIW views
There are some pre-built views that have enums joined on.
| View | Description |
|---|---|
ProjectDetailExtractIW |
|
ProjectSummaryExtractIW |
Concepts
Tasks
High level, there are two types of tasks:
- Project Tasks
- Bill Tasks
Project tasks are those that people can book hours to.
Bill tasks are those that people don't book hours to, but the client will be billed for.
The table Project_BillTerm_BillTask fully enumerates all of these things.
Bill Term
A bill term is a category under which a project task/bill task sits on the invoice. This has no relation to the parent/child structure of project tasks.