Data model for the Contract Process and details of the WorkItems BusinessObject 

 

The OrixaBilling App provides a mechanism to keep track of contracts and work done. This document gives an overview of how it functions and dives into a few of the details.

Data Model

Data Model for Contract / Payment / WorkItems Process  

• Both "Contracts" and "ContractPayments" link to ContractItems.
• ContractItems link to WorkItems, and each WorkItem has a single "StaffID" to identify the person doing that piece of work.
• ContractItems are linked to a Product (such as "Developer Time") to help with costing / budgetting.
• The "Tasks" BusinessObject also links to "WorkItems". When a Task is created it has a frequency (ie Monthly or Weekly). It then automatically populates the system-diary, so staff can see work they are supposed to do at a particular date / time.

The data model is "Customers" -> "Contracts" -> "ContractItems" -> "WorkItems". Also, ContractPayments -> ContractItems, and Contracts -> ContractPayments. As WorkItems are added the system can report the Budgetted and Actual Hours on each ContractItem. ContractItems with BudgettedValue can be used for Customer Quotations. ContractPayments are then generated by manually adding a BilledHours to each ContractItem. This BilledHours amount is usually the same as the ActualHours, but does not have to be.

For a ContractPayment to be generated management need to see the Actual Hours on each ContractItem, so WorkItems have to have been entered into the system. However, simply because a WorkItem is created does not mean it always will be Billed in a ContractPayment. A WorkItem might be linked to an internal Contract (for day-to-day work in the company). Or a WorkItem might be linked to a non-Chargeable ContractItem. In these cases the hours are still documented but they are never used in computation of a ContractPayment.

Internal Contracts

Contracts marked with "Internal = true" are assumed to have been created as placeholders for unpaid work. This might be daily Admin of the business or other research and development work. For internal budgetting and cost control purposes, create a small number of "Internal" contracts. These can have names like "New Product Development" or "General Admin". These contracts can then have ContractItems with Budgetted hours. As staff do work in a particular area (ie New Product Development) they can create WorkItems under eaach heading. This can be very useful in allowing management to review whether budget areas are over or under budget in terms of the Human Resource Hours that are being used.

Chargeable / Non-Chargeable Work

ContractItems marked with "Chargeable = false" are assumed to be additional unpaid items added as an allowance for extra work done on a contract (for example preparation, sales work, lead-development). When a Billable contract is created, a small number of ContractItems should be created with "Chargeable = false" with names such as "Customer Liason Time" or "Contract Negotiation". Staff can then add WorkItems to document the work they do under these ContractItems. There are always unpaid areas of work with all Contracts. 

Usually details of Non-chargeable ContractItems and WorkItems are not shared with customers.

ContractItems marked with "Chargeable = true" will appear on Budgetting reports that can be shared with Customers. They can be used to set up a Contract which a Customer agrees to pay for. These have Budgetted Hours and therefore Costs. 

Once a Contract is agreed Staff may either simply start to work, adding WorkItems under each ContractItem, gradually using up their budget, or they may plan by adding a series of WorkItems on future dates with a number of HoursPlanned estimating how long each section of work might take. This allows staff members to schedule their work and create a time-line for its completion. Planned future WorkItems often have detailed "to-do" Comments added to them laying out the work needed.

By documenting the actual hours used on a case-by-case basis for both Chargeable and non-Chargeable ContractItems it is possible to assess the profitability of a Contract and the extent of non-chargeable costs.

By adding (and then completing) WorkItems in a process of planning / action staff can keep track of progress, and see whether ContractItems are saying on-budget or not. Note that once created a WorkItem can be edited at any time, so it can be planned for one date, but if the work is done on a different date this can be changed. Staff will update a WorkItem adding the HoursWorked as they actually do the work. WorkItems may often be split into parts. A section of work might have been planned as an 8 hour day, if 4 hours are worked on 1 day and 4 hours are worked on a second day staff can use the "Duplicate Record" Action to duplicate the record, and edit the HoursPlanned and HoursWorked accordingly.

The system allows Billing via the ContractPayments BusinessObject. For Billing to be done staff must have entered their hours with HoursWorked so this can be reviewed and updated on each ContractItem. The actual HoursWorked in WorkItems are pulled through and displayed alongside the BudgetQuantity in the ContractItem, allowing staff to update the ContractItem "BilledQuantity" to generate the Billing.

Customer Billings consist of a ContractPayment record (master) with a number of ContractItems (children). Note that since each ContractItem can only be billed once, if a WorkItems assigned to a ContractItem is not being charged for, but needs to be charged in the future, it must be linked to an ContractItem which does not yet have a ContractPaymentID. Switching a WorkItem record's ContractItemID allows the WorkItem to be moved for assignment to a future (or different) ContractPayment.

Linking from Tasks to WorkItems

Tasks are created to add "holder points" in the Orixa diary. Once a Task record is added it will appear periodically in the diary. Staff can see that it is incomplete (due to its colour) and Add WorkItem, to make a note of the time spent on the task.

Tasks provide a useful way to remind staff about repetative elements of contracts.

More details of using Tasks are covered here:

Tasks Management Business Object

Purpose and use of the WorkItems BusObj

WorkItems in use  

1. A staff member has added a WorkItems record for a specific peice of work on a specific date. This links the work to a Contract and ContractItem, and allows the staff member to give a simple name for the work done that can be used in reports.
2. The OrixaBilling Daily Activity Worksurface automatically shows WorkItems for any date, allowing users to see the work they have done. In the case shown one of the WorkItems includes a Comment (shown starting "WORK DONE") which is shown expanded so it can be read in situ.
3. Note that the Comment is also shown as a Link at the bottom of the Edit Form. Staff can add as many Comments (also FileNotes and Images) as they like to a WorkItems record. This allows them to use the record to more fully document what they have done, potentially includes links to external files (for documents they may have written) and images showing things like screen-grabs of work-done, error messages that were reported etc.

 

The WorkItems BusinessObject is intended as a time-recording tool to allow staff to keep track of the work they have done day-to-day on different Contracts. The intention is to allow staff to adequately document their time-use without relentlessly over documenting. In General WorkItems are intended to be added in blocks of 15 or 30 minutes, with shorter intervals of work either ignorred or added together into single items not exactly matched with the day/time of work.

Beyond this the WorkItem also acts as a placeholder for work. By adding links to comments, images and outside documents the WorkItem can be a really useful resource. For example if a problem is solved during work the steps of the solution (including screen-grabs etc.) can be added. Then if the problem occurs again staff can review their prior work to repeat the steps.

StatusID

The WorkItems BusinessObject has a StatusID field which is used to track the stages of its data-life. The StatusID is set automatically depending on the state of different fields in the record. The user does not edit this field.

1. Work Planned: This StatusID is assigned if a WorkItem is in the future and has positive HoursPlanned and zero HoursWorked.
   - Staff can easily see future "work planned" in their diary, and can move / reassign it as they need to.
   - During project planning "work planned" can be added to give a sense of capacity utilization and to ensure that planning is realistic.
2. Complete: This StatusID is assigned if a staff member adds HoursWorked to a WorkItem, and then the Complete field is ticked.
3. Cancelled: This StatusID is assigned if the HoursPlanned are reset to zero.
4. Used in Budget: This StatusID is assigned if the Complete field is ticked without adding HoursWorked.

ContractItems look-up list

Details of how ContractItems Display in WorkItems  

Work is tracked in the Orixa Billing System by adding "WorkItems" to the database. WorkItems details hours of work, together with notes on what was done, by whom, and can include links to on-disk files such as SQL scripts, or source-code files that were written or edited as part of the work.

To associate a WorkItem with a ContractItem a drop-down list is supplied as shown above. The user picks a Contract, and then a ContractItem. Setting the Contract re-sets the ContractItems drop-down list.

1. WorkItems edit form, for a specific planned piece of work.
2. Contracts Edit Form for the selected Contract, with the "ContractItems linked to this record" grid open.
3. The list of linked Contract Items shown in the Drop-down list. Note that the top item in the list is the same as the top line in the grid "Non-charged management time", but note that the two lists are not the same and show items in different orders.
4. If an item in the list is cancelled or complete then it is shown in the drop-down list with "zzz/" at the start. This automatically means it will drop to the bottom of the list. These items are not removed altogether as it is quite often useful to see them for reference.
5. Some items in the ContractItems list are not present in the drop-down list. This is because some ContractItems are Subscriptions, which do not include a labour-component, and so they should not be associated with WorkItems.

Creating Quotations for Customers

A number of System Resources are linked to the Contract Items (CI), under the printer icon. These include "Budget" reports, together with a "Quotation for Customer", these list the contents of a single CI, together with associated records.

The "Detailed" budget includes Contract Item, Work Item and Comments data, this report will show very detailed output, including descriptions of the work to be done within Comments linked to Work-Items. The less detailed version only includes Contract Item and Work Item records. The "Quotation" report only includes data linked to a Contract Item, so provides much more limited output. Include details in the long-text fields of the CI to make this report more useful. Note that once a project is underway, all the records related to the Contract Item may be updated and changed. Work-Item records will be edited and updated with "real" data, actual "what was done" details will be added as Comments, meaning that the data of the original report will be lost.

Staff should generate PDFs at the Quoation stage, and store these as the official record of intended work. These PDFs are frequently linked to communications to customers, so a clear communication trail should be present detailing exactly what a customer was told. It is important to understand that once a Contract Item is underway, the Work-Item data will no longer match that in the original quote, as the whole process is a "live system".

Customer's "Contract Terms"

To assist with general management of a customer a small amount of extra data is stored in Orixa in the Customer BusinessObject. This includes an Account Contact, Credit Terms and Contract Terms, as well as other fields.

A "Contract Terms" report has been added under the Customer Business Object, so that when a detailed Quotation is generated for a Customer this can also be printed, providing some more general-purpose paperwork and information.

Customer Contract Terms Report	Detailed Budgeting Report (with hours)
Billing / Invoice Report	Email with attached documents

Where documents are stored and should be stored

Finding the correct copies of all the different documents for a contract can be difficult, as it is possible for them to be stored in multiple locations. In some situations Orixa generates the documents by itself, in which case they will always be present in specific directories.

If the following rules are followed, documents should be easier to find:

1. When a new customer becomes live, a folder with the name of that customer should be created in the main Orixa data-directory structure, under this  a folder can be added for the project, and for the year (if the project extends to multiple years. "\\Work\Overseas\CustomerName\ProjectName\Year"
2. Auto-generated Orixa PDFs will be stored in the Server file-system under the model: "\\Database\Reports\PDFs\[BusinessObject]\Year-Month"
   example: "\\Database\Reports\PDFs\ContractBilling\2022-04\
   Where an auto-generated report is created with an auto-generated email, the file-name will be included in the attachments for the email.
   

Real world use of the Contracts System and its flexibiliy

The idea of the Orixa Contracts system is that it serves 3 purposes. Doing this means a lot of complexity at certain levels, but ultimately should stream-line and simplify administration.

Summary Uses of the Billing System

1. Creation of Quotations and estimates for work (external and internal). Staff create Contacts, Contract Items and Work-items. The "Estimated Hours" and "HoursPlanned" fields are completed. This allows the generation of reports giving best-estimate budgets for the work to be done. It is common for staff to add Comments to Work-items with details of the planned work, and estimated hours for these sub-sections of work, as it is impractical to add large numbers of Work-items.
2. Management of day-to-day working hours and time-sheets. As staff do work through the day, they create work-items, or find a budget work-item and edit it. They detail the hours they have worked. If there are Comments on budget Work-items, they may cut out parts of the Comments added to the budgeted item, and copy this into their actual work-item. In this way incomplete budgetted Work-items gradually see the "To-Do" Comments attached to them diminish to nothing.
3. Creation of Billing for Customers, with Work-item review by staff. Once a Contract needs to be billed, Work-items should be reviewed, and checked that they are linked to the correct Contract-item. A ContractPayment record is then created, and linked to one or more ContractItems. The "BilledQuantity" field on ContractItems must be completed manually. This is left manual so that the Hours Worked (sum of WorkItems) does not have to exactly match the Hours Billed, but users must remember to check and update this field for each ContractItem prior to actually creating an invoice.

 

Reports and dashboards exist to assist with each stage in this process. Usually one Contract will link to a set of ContractItems and a set of ContractPayments. The value of Work-items linked to the ContractItems should roughly match the value of the ContractItems, and all this value will be billed via ContractPayments. However the complex nature of the work means that exact balancing is rare. Particularly the actual hours worked rarely matches any amount billed as customers tend to request certainty in their billing, not to be billed by the hour.

The value of each billing (one CustomerPayment record) will always compute from the total "BilledValue" of all linked ContractItems. Note that this can mean if a billing is split over a period that more than one ContractItem needs to be created to allow billing to be segregated.

Over and under-spend of Contracts and ContractItems are all monitored by the system, so the profitability of different contracts can be assessed.

Once more complexity is the fact that ContractPayments may link to any ContractItem for a Customer. Since payments are frequently out of step with work, flexibility is necessary to allow for pre-payment (paying for a future contract prior to work being done) or late payment (paying for a contract item worked on a prior contract). The ContractPayment is linked to one Contract (for ease of logging) but the ContractItems on this Payment may be linked to other Contracts.